首页 数据库 mysql教程 Heat Map and Automatic Data Optimization : part-1

Heat Map and Automatic Data Optimization : part-1

Jun 07, 2016 pm 04:36 PM
and data map op

oracle 12c 提供了一个新特性叫 Heat Map,它跟踪和标记数据可以达到row和block level,此特性可以在system和session级别启用。如果要使用ADO(Automatic Data Optimization)必须要在system级别启用。但是此特性 only works in a non-CDB environment,not supp

oracle 12c 提供了一个新特性叫 Heat Map,它跟踪和标记数据可以达到row和block level,此特性可以在system和session级别启用。如果要使用ADO(Automatic Data Optimization)必须要在system级别启用。但是此特性only works in a non-CDB environment,not supported with a multitenant container database (CDB),并且提供了以下视图查看

  • V$HEAT_MAP_SEGMENT:显示实时访问信息,包好object_name,object_number及容器ID
  • DBA_HEAT_MAP_SEGMENT:Displays the latest segment access time for all segments visible to the specified user
  • DBA_HEAT_MAP_SEG_HISTOGRAM:Displays access information for all segments visible to the specific user.
  • DBA_HEATMAP_TOP_OBJECTS:Displays access information for the top 1,000 object
  • DBA_HEATMAP_TOP_TABLESPACES:Displays access information for the top 100 tablespaces

Heat_map和ADO 结合使用示意图

heat_map_and_ado

CDB和non-CDB 测试

non-CDB

SQL> SELECT cdb FROM v$database ;
?
CDB
------
NO
?
--数据库NON-CDB
?
SQL> GRANT dba TO travel IDENTIFIED BY aa;
?
GRANT succeeded.
?
SQL> conn travel/aa
Connected.
?
USERNAME             INST_NAME    HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID            SADDR            PADDR
-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
TRAVEL               noncdb       localhost.localdomain     33    11       12.1.0.1.0 20140525 4286            7     4259            000000009F68A408 000000009F9865B8
?
?
SQL> ALTER system SET heat_map=ON;
?
System altered.
?
SQL> CREATE TABLE heat_test AS SELECT * FROM all_objects;
?
TABLE created.
?
SQL> INSERT /*+ append */ INTO  heat_test SELECT * FROM heat_test;
?
88955 ROWS created.
?
SQL> commit;
?
Commit complete.
?
SQL>  INSERT /*+ append */ INTO  heat_test SELECT * FROM heat_test;
?
177910 ROWS created.
?
SQL> commit;
?
Commit complete.
?
--创建一张测试表
?
SQL> ALTER SESSION SET nls_date_format='yyyy-mm-dd hh:mi:ss';
?
SESSION altered.
?
SQL> col OBJECT_NAME FOR a15
SQL> SELECT OBJECT_NAME,SEGMENT_WRITE_TIME , SEGMENT_READ_TIME, FULL_SCAN FROM dba_heat_map_segment WHERE owner='TRAVEL';
?
OBJECT_NAME     SEGMENT_WRITE_TIME  SEGMENT_READ_TIME   FULL_SCAN
--------------- ------------------- ------------------- -------------------
HEAT_TEST                                               2014-05-25 05:44:00
?
SQL> col "Segment write" format A14
SQL> col "Full Scan" format A12
SQL> col "Lookup Scan" format a12
SQL>  SELECT object_name, track_time "Tracking Time",
  2   segment_write "Segment write",
  3   full_scan "Full Scan",
  4   lookup_scan "Lookup Scan"
  5   FROM DBA_HEAT_MAP_SEG_HISTOGRAM
  6   WHERE object_name='HEAT_TEST';
?
OBJECT_NAME     Tracking TIME       Segment WRITE  FULL Scan    Lookup Scan
--------------- ------------------- -------------- ------------ ------------
HEAT_TEST       2014-05-25 05:45:03 NO             YES          NO
?
SQL> SELECT compression, compress_for FROM dba_tables WHERE TABLE_NAME = 'HEAT_TEST';
?
COMPRESSION      COMPRESS_FOR
---------------- ------------------------------------------------------------
DISABLED
?
SQL> SELECT SUM(bytes)/1048576 FROM user_segments WHERE 
  2      segment_name='HEAT_TEST';
?
SUM(BYTES)/1048576
------------------
?
?
SQL> SELECT SUM(bytes)/1048576 FROM  dba_segments WHERE segment_name='HEAT_TEST';
?
SUM(BYTES)/1048576
------------------
                48
?
--查看了数据的heat_map情况和大小
?
添加ADO策略30天内没有修改进行压缩
SQL> ALTER TABLE  travel.HEAT_TEST  ILM ADD POLICY ROW STORE 
  2  COMPRESS ADVANCED SEGMENT AFTER 30 DAYS OF NO MODIFICATION;
?
TABLE altered.
?
查看policy
SQL> 
SQL> SELECT policy_name, action_type, scope, compression_level,
  2   condition_type, condition_days
  3   FROM   dba_ilmdatamovementpolicies
  4  ORDER BY policy_name;
?
POLICY_NAME                                                                                                                                                                                                                                                      ACTION_TYPE            SCOPE          COMPRESSION_LEVEL                                            CONDITION_TYPE                               CONDITION_DAYS
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- -------------- ------------------------------------------------------------ -------------------------------------------- --------------
P1                                                                                                                                                                                                                                                               COMPRESSION            SEGMENT        ADVANCED                                                     LAST MODIFICATION TIME                                   30
?
SQL> col policy_name FOR a10
SQL> /
?
POLICY_NAM ACTION_TYPE            SCOPE          COMPRESSION_LEVEL                                            CONDITION_TYPE                               CONDITION_DAYS
---------- ---------------------- -------------- ------------------------------------------------------------ -------------------------------------------- --------------
P1         COMPRESSION            SEGMENT        ADVANCED                                                     LAST MODIFICATION TIME                                   30
?
SQL> col COMPRESSION_LEVEL FOR a20
SQL> /
?
POLICY_NAM ACTION_TYPE            SCOPE          COMPRESSION_LEVEL    CONDITION_TYPE                               CONDITION_DAYS
---------- ---------------------- -------------- -------------------- -------------------------------------------- --------------
P1         COMPRESSION            SEGMENT        ADVANCED             LAST MODIFICATION TIME                                   30
?
SQL> SELECT policy_name, object_name, inherited_from, enabled FROM dba_ilmobjects;
?
POLICY_NAM OBJECT_NAME     INHERITED_FROM                           ENABLED
---------- --------------- ---------------------------------------- --------------
P1         HEAT_TEST       POLICY NOT INHERITED                       YES
?
?
?
这里由于需要30天,所以通过修改低成表数据实现30天
?
SQL> CREATE OR REPLACE PROCEDURE set_stat (object_id      NUMBER,
  2   data_object_id NUMBER,
  3   n_days         NUMBER,
  4   p_ts#            NUMBER,
  5   p_segment_access NUMBER)
  6   AS
  7   BEGIN
  8   INSERT INTO sys.heat_map_stat$
  9   (obj#,
 10   dataobj#,
 11   track_time,
 12   segment_access,
 13   ts#)
 14   VALUES
 15   (object_id,
 16   data_object_id,
 17   sysdate - n_days,
 18   p_segment_access,
 19   p_ts# );
 20   commit;
 21   END;
 22   /
?
PROCEDURE created.
?
SQL> DECLARE
  2   v_obj# NUMBER;
  3   v_dataobj# NUMBER;
  4   v_ts#      NUMBER;
  5   BEGIN
  6   SELECT object_id, data_object_id INTO v_obj#, v_dataobj#
  7   FROM dba_objects
  8   WHERE object_name = 'HEAT_TEST'
  9   AND owner = 'TRAVEL';
 10   SELECT ts# INTO v_ts#
 11   FROM sys.ts$ a,
 12   dba_segments b
 13   WHERE  a.name = b.tablespace_name
 14   AND  b.segment_name = 'HEAT_TEST';
 15   commit;
 16   sys.set_stat
 17   (object_id         => v_obj#,
 18   data_object_id    => v_dataobj#,
 19   n_days            => 30,
 20   p_ts#             => v_ts#,
 21   p_segment_access  => 1);
 22   END;
 23   /
?
PL/SQL PROCEDURE successfully completed.
?
SQL> conn travel/aa
Connected.
?
USERNAME             INST_NAME    HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID            SADDR            PADDR
-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
TRAVEL               noncdb       localhost.localdomain     1     7        12.1.0.1.0 20140525 4916            20    4553            000000009F6CA108 000000009F994798
?
?
SQL> 
手工执行
SQL> DECLARE
  2  v_executionid NUMBER;
  3  BEGIN
  4  dbms_ilm.execute_ILM (ILM_SCOPE      => dbms_ilm.SCOPE_SCHEMA,
  5                        execution_mode => dbms_ilm.ilm_execution_offline,
  6                        task_id        => v_executionid);
  7  END;
  8  /
?
PL/SQL PROCEDURE successfully completed.
?
?
查看任务执行
SQL> SELECT task_id, start_time AS start_time FROM user_ilmtasks;
?
   TASK_ID START_TIME
---------- -----------------------------
         2 25-MAY-14 05.52.39.737942 PM
?
?
查看任务详细洗洗
SQL> SELECT task_id, policy_name, object_name, selected_for_execution, job_name
  2  FROM user_ilmevaluationdetails
  3  WHERE task_id=2;
?
   TASK_ID POLICY_NAM OBJECT_NAME     SELECTED_FOR_EXECUTION   JOB_NAME
---------- ---------- --------------- ------------------------ ---------------------------------
         2 P1         HEAT_TEST       SELECTED FOR EXECUTION   ILMJOB42
查看结果
?
SQL> SELECT task_id, job_name, job_state, completion_time completion FROM user_ilmresults;
?
   TASK_ID JOB_NAME                JOB_STATE                  COMPLETION
---------- ----------------------- -------------------------- ---------------------------------------
         2 ILMJOB42                COMPLETED SUCCESSFULLY     25-MAY-14 05.52.43.834452 PM
?
SQL> col JOB_NAME FOR a20
SQL> SELECT task_id, job_name, job_state, completion_time completion FROM user_ilmresults;
?
   TASK_ID JOB_NAME             JOB_STATE                     COMPLETION
---------- -------------------- ----------------------------- ---------------------------------------------------------------------------
         2 ILMJOB42             COMPLETED SUCCESSFULLY        25-MAY-14 05.52.43.834452 PM
?
查看表大小
SQL> SELECT SUM(bytes)/1048576 FROM user_segments WHERE segment_name='HEAT_TEST';
?
SUM(BYTES)/1048576
------------------
                13
数据压缩了35M
登录后复制

测试下CDB情况下的使用

SQL> SELECT cdb FROM v$database;
?
CDB
---
YES
?
SQL> ALTER system SET heat_map=ON;
?
System altered.
?
SQL> conn c##travel/aa
ERROR:
ORA-28001: the password has expired
?
?
Changing password FOR c##travel
NEW password: 
Retype NEW password: 
Password changed
Connected.
?
USERNAME             INST_NAME    HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID            SADDR            PADDR
-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
C##TRAVEL            orcl         localhost.localdomain     57    11       12.1.0.1.0 20140525 5370            7     5067            000000009F711DA8 000000009FA3EB88
?
?
SQL>  CREATE TABLE heat_test AS SELECT * FROM all_objects;
?
TABLE created.
?
SQL> INSERT /*+ append */ INTO  heat_test SELECT * FROM heat_test;
?
89347 ROWS created.
?
SQL> commit;
?
Commit complete.
?
SQL> ALTER TABLE  HEAT_TEST  ILM ADD POLICY ROW STORE 
  2  COMPRESS ADVANCED SEGMENT AFTER 30 DAYS OF NO MODIFICATION;
ALTER TABLE  HEAT_TEST  ILM ADD POLICY ROW STORE
*
ERROR at line 1:
ORA-38343: ADO online mode NOT supported WITH supplemental logging enabled
?
?
SQL> !oerr ora 38343
38343, 00000, "ADO online mode not supported with supplemental logging enabled"
// *Cause: An attempt was made TO perform an automatic DATA optimization (ADO)
//         operation WITH supplemental logging enabled.
// *Action: Disable supplemental logging OR switch TO ADO offline mode AND retry.
?
SQL> conn  / AS sysdba
Connected.
?
USERNAME             INST_NAME    HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID            SADDR            PADDR
-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
SYS                  orcl         localhost.localdomain     57    13       12.1.0.1.0 20140525 5455            7     5067            000000009F711DA8 000000009FA3EB88
?
?
SQL> ALTER DATABASE DROP supplemental log ;
ALTER DATABASE DROP supplemental log
                                    *
ERROR at line 1:
ORA-00905: missing keyword
?
?
SQL> ALTER DATABASE DROP supplemental log DATA;
?
DATABASE altered.
?
SQL> conn c##travel/aa
Connected.
?
USERNAME             INST_NAME    HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID            SADDR            PADDR
-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
C##TRAVEL            orcl         localhost.localdomain     57    15       12.1.0.1.0 20140525 5467            7     5067            000000009F711DA8 000000009FA3EB88
?
?
SQL> ALTER TABLE  HEAT_TEST  ILM ADD POLICY ROW STORE 
  2  COMPRESS ADVANCED SEGMENT AFTER 30 DAYS OF NO MODIFICATION;
ALTER TABLE  HEAT_TEST  ILM ADD POLICY ROW STORE
*
ERROR at line 1:
ORA-38342: heat map NOT enabled
?
?
SQL> SHOW parameter heat_map
?
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
heat_map                             string      ON
SQL>
登录后复制

证明了only works in a non-CDB environment,not supported with a multitenant container database (CDB)

本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热门文章

R.E.P.O.能量晶体解释及其做什么(黄色晶体)
2 周前 By 尊渡假赌尊渡假赌尊渡假赌
仓库:如何复兴队友
4 周前 By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒险:如何获得巨型种子
4 周前 By 尊渡假赌尊渡假赌尊渡假赌

热工具

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

SublimeText3汉化版

SublimeText3汉化版

中文版,非常好用

禅工作室 13.0.1

禅工作室 13.0.1

功能强大的PHP集成开发环境

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具

SublimeText3 Mac版

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)

springboot怎么读取yml文件中的list列表、数组、map集合和对象 springboot怎么读取yml文件中的list列表、数组、map集合和对象 May 11, 2023 am 10:46 AM

application.yml定义list集合第一种方式使用@ConfigurationProperties注解获取list集合的所有值type:code:status:-200-300-400-500编写配置文件对应的实体类,这里需要注意的是,定义list集合,先定义一个配置类Bean,然后使用注解@ConfigurationProperties注解来获取list集合值,这里给大家讲解下相关注解的作用@Component将实体类交给Spring管理@ConfigurationPropertie

Java怎么设置过期时间的map Java怎么设置过期时间的map May 04, 2023 am 10:13 AM

一、技术背景在实际的项目开发中,我们经常会使用到缓存中间件(如redis、MemCache等)来帮助我们提高系统的可用性和健壮性。但是很多时候如果项目比较简单,就没有必要为了使用缓存而专门引入Redis等等中间件来加重系统的复杂性。那么Java本身有没有好用的轻量级的缓存组件呢。答案当然是有喽,而且方法不止一种。常见的解决方法有:ExpiringMap、LoadingCache及基于HashMap的封装三种。二、技术效果实现缓存的常见功能,如过时删除策略热点数据预热三、ExpiringMap3.

Java中Map实现线程安全的方式有哪些 Java中Map实现线程安全的方式有哪些 Apr 19, 2023 pm 07:52 PM

方式1.使用HashtableMaphashtable=newHashtable();这是所有人最先想到的,那为什么它是线程安全的?那就看看它的源码,我们可以看出我们常用的put,get,containsKey等方法都是同步的,所以它是线程安全的publicsynchronizedbooleancontainsKey(Objectkey){Entrytab[]=table;inthash=key.hashCode();intindex=(hash&0x7FFFFFFF)%tab.leng

深入探讨 OP Stack 的结构层面与功能 深入探讨 OP Stack 的结构层面与功能 Jan 18, 2024 pm 05:45 PM

OPStack是由Optimism网络背后的开发团体OptimismCollective公布的一个开源区块链框架。它对于Ethereum和Optimism社区都是一个重要的工具。OPStack的主要目标是加强Optimism网络,提供关键的软件工具给OptimismMainnet,以及即将推出的OptimismSuperchain和其治理模型。通过提供一个面向开发者的环境,OPStack的核心思想是促进Ethereum领域的增长和创新。它为前沿发展铺平了道路,使区块链的创建更加简单。OPStac

Java中将对象与Map相互转换的实现方式 - 使用BeanMap Java中将对象与Map相互转换的实现方式 - 使用BeanMap May 08, 2023 pm 03:49 PM

javabean与map的转换有很多种方式,比如:1、通过ObjectMapper先将bean转换为json,再将json转换为map,但是这种方法比较绕,且效率很低,经测试,循环转换10000个bean,就需要12秒!!!不推荐使用2、通过Java反射,获取bean类的属性和值,再转换到map对应的键值对中,这种方法次之,但稍微有点麻烦3、通过net.sf.cglib.beans.BeanMap类中的方法,这种方式效率极高,它跟第二种方式的区别就是因为使用了缓存,初次创建bean时需要初始化,

Nginx服务器中map模块怎么配置与使用 Nginx服务器中map模块怎么配置与使用 May 21, 2023 pm 05:14 PM

map指令使用ngx_http_map_module模块提供的。默认情况下,nginx有加载这个模块,除非人为的--without-http_map_module。ngx_http_map_module模块可以创建变量,这些变量的值与另外的变量值相关联。允许分类或者同时映射多个值到多个不同值并储存到一个变量中,map指令用来创建变量,但是仅在变量被接受的时候执行视图映射操作,对于处理没有引用变量的请求时,这个模块并没有性能上的缺失。一.ngx_http_map_module模块指令说明map语法

优化Go语言map的性能 优化Go语言map的性能 Mar 23, 2024 pm 12:06 PM

优化Go语言map的性能在Go语言中,map是一种非常常用的数据结构,用来存储键值对的集合。然而,在处理大量数据时,map的性能可能受到影响。为了提高map的性能,我们可以采取一些优化措施来减少map操作的时间复杂度,从而提升程序的执行效率。1.预分配map的容量在创建map时,我们可以通过预分配容量来减少map扩容的次数,提高程序的性能。一般情况下,我们

深入了解OP Mainnet网络: OP Mainnet是哪种链? 深入了解OP Mainnet网络: OP Mainnet是哪种链? Jan 24, 2024 am 11:30 AM

此前Optimism官方宣布,Optimism链现已更名为OPMainnet,可能有人要问了OPMainnet是什么链?根据当前资料显示,OPMainnet是一个开源超级链,致力于以可持续方式为公共产品提供资金,众多L2链之中的一个,可与Base、ZoraNetwork、PGN、Redstone等链无缝通信,但它并不是一条单一的区块链,它代表的二十整个超级链网络。下面小编为大家详细说说这个OPMainnet,帮助大家搞懂OPMainnet网络。OPMainnet是什么链?OPMainnet是以太

See all articles