Heat Map and Automatic Data Optimization : part-1
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 结合使用示意图
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)
原文地址:Heat Map and Automatic Data Optimization : part-1, 感谢原作者分享。

热AI工具

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

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

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

AI Hentai Generator
免费生成ai无尽的。

热门文章

热工具

记事本++7.3.1
好用且免费的代码编辑器

SublimeText3汉化版
中文版,非常好用

禅工作室 13.0.1
功能强大的PHP集成开发环境

Dreamweaver CS6
视觉化网页开发工具

SublimeText3 Mac版
神级代码编辑软件(SublimeText3)

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

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

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

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

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

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

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

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