Heat,Map,and,Automatic,Data,Op
数据库-mysql教程
2345网页源码,怎样配置vscode编译,ubuntu开机死,查询tomcat 位数,sqlite3 修改工具,h5手机app常用前端框架,爬虫爬取音乐信息网站,php在线教育,专业seo优化技巧,在线解压rar网站,网页顶部固定代码,图书商城售后服务模板lzw
上一篇测试了ADO的压缩功能 Heat Map and Automatic Data Optimization : part-1 下面测下ADO的存储层功能 简单的说下就是使用ado move 表、分区操作，把不是热数据的数据移动到性能低下的存储上 下面是工作的示意图 准备环境 SQL conn travel/aaConnected.?导航网 源码 hao123,ubuntu下多显卡,抖音数据爬虫,sublime php中文,成都seo计费lzw
sys驱动源码,vscode 格式化缩进,启动网卡ubuntu,tomcat本机启动失败,地图爬虫工具,php怎么引用文件,湖北餐饮seo推广哪里好,谁有简单的asp网站源码测试用,易语言键鼠模板lzw
上一篇测试了ADO的压缩功能 Heat Map and Automatic Data Optimization : part-1下面测下ADO的存储层功能
简单的说下就是使用ado move 表、分区操作，把不是热数据的数据移动到性能低下的存储上
下面是工作的示意图
准备环境
SQL> conn travel/aaConnected.?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 20140526 3209 7 2927 000000009F6CA108 000000009F9865B8??SQL> SELECT * FROM tab;?TNAME TABTYPE CLUSTERID----------------------------------- -------------- ----------HEAT_TEST TABLE?SQL> SQL> conn / AS sysdbaConnected.?USERNAME INST_NAME HOST_NAME SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------SYS noncdb localhost.localdomain 1 9 12.1.0.1.0 20140526 3234 7 2927 000000009F6CA108 000000009F9865B8??SQL> CREATE tablespace ado_t1 datafile '/oradata/noncdb/ado_t1.dbf' SIZE 200M;?Tablespace created.??SQL> CREATE tablespace ado_t2 datafile '/oradata/noncdb/ado_t2.dbf' SIZE 200M;?Tablespace created.?SQL> ?SQL> CREATE TABLE ado_move tablespace ado_t1 AS SELECT * FROM dba_objects;?TABLE created.?SQL> INSERT INTO ado_move SELECT * FROM ado_move;?90764 ROWS created.?SQL> commit;?Commit complete.?SQL> INSERT INTO ado_move SELECT * FROM ado_move;?181528 ROWS created.?SQL> commit;?Commit complete.?SQL> INSERT INTO ado_move SELECT * FROM ado_move;?363056 ROWS created.?SQL> INSERT INTO ado_move SELECT * FROM ado_move;?726112 ROWS created.?SQL> commit;?Commit complete.?SQL> @dba_tablespaces?+------------------------------------------------------------------------+| Report : Tablespaces || Instance : noncdb || USER : TRAVEL |+------------------------------------------------------------------------+?STATUS Tablespace Name TS TYPE Ext. Mgt. Seg. Mgt. Tablespace SIZE Used (IN bytes) Pct. Used--------- ------------------------- --------------- ---------- ---------- ------------------ ------------------ ---------ONLINE ADO_T1 PERMANENT LOCAL AUTO 209,715,200 202,375,168 97ONLINE ADO_T2 PERMANENT LOCAL AUTO 209,715,200 1,048,576 1ONLINE SYSAUX PERMANENT LOCAL AUTO 765,460,480 760,086,528 99ONLINE SYSTEM PERMANENT LOCAL MANUAL 817,889,280 811,401,216 99ONLINE TEMP TEMPORARY LOCAL MANUAL 91,226,112 90,177,536 99ONLINE UNDOTBS1 UNDO LOCAL MANUAL 152,043,520 151,257,088 99ONLINE USERS PERMANENT LOCAL AUTO 66,846,720 15,400,960 23 ------------------ ------------------ ---------avg 74SUM 2,312,896,512 2,031,747,072?7 ROWS selected.
上面创建了2个表空间，并在表空间ADO_T1上创建了一个张表，插入大量数据，是空间使用率得到97%
下面查看下表的Heat map情况
SQL> ALTER system SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' 2 SQL> col owner FOR a20SQL> col object_name FOR a20SQL> col "Tracking Time" FOR a40SQL> col "Seg write" FOR a20SQL> SQL> pro DBA_HEAT_MAP_SEG_HISTOGRAMDBA_HEAT_MAP_SEG_HISTOGRAMSQL> SELECT object_name, to_char(track_time,'YYYY-MM-DD HH:MI:SS') "Tracking Time", 2 segment_write "Seg write", 3 FULL_SCAN "Full Scan", 4 lookup_scan "Lookup Scan" 5 FROM DBA_HEAT_MAP_SEG_HISTOGRAM 6 WHERE object_name=UPPER('ado_move');?OBJECT_NAME Tracking TIME Seg WRITE FULL S Lookup-------------------- ---------------------------------------- -------------------- ------ ------ADO_MOVE 2014-05-26 11:26:52 YES YES NO?SQL> SQL> SQL> pro DBA_HEAT_MAP_SEGMENTDBA_HEAT_MAP_SEGMENTSQL> SQL> SELECT owner,object_name,SEGMENT_WRITE_TIME,SEGMENT_READ_TIME,FULL_SCAN,LOOKUP_SCAN 2 FROM DBA_HEAT_MAP_SEGMENT 3 WHERE object_name=UPPER('ado_move');?OWNEROBJECT_NAME SEGMENT_WRITE_TIM SEGMENT_READ_TIME FULL_SCAN LOOKUP_SCAN-------------------- -------------------- ----------------- ----------------- ----------------- -----------------TRAVEL ADO_MOVE 20140526 11:26:53 20140526 11:26:53?SQL> ?SQL> SQL> SELECT OBJECT_NAME, TRACK_TIME, SEGMENT_WRITE "Seg_write", SEGMENT_READ "Seg_read", FULL_SCAN, LOOKUP_SCAN 2 FROM v$heat_map_segment 3 WHERE object_name=UPPER('ado_move');?OBJECT_NAME TRACK_TIME Seg_wr Seg_read FULL_S LOOKUP-------------------- ----------------- ------ -------------------- ------ ------ADO_MOVE 20140526 11:28:49 YES NO YES NO创建策略SQL> ALTER TABLE ADO_MOVE ILM ADD POLICY TIER TO ADO_T2;?TABLE altered.查看策略SQL> COL policy_name format A12SQL> COL TIER_TBS format A20SQL> SELECT policy_name, action_type, scope, 2 tier_tablespace "TIER_TBS" 3 FROM user_ilmdatamovementpolicies 4 ORDER BY policy_name;?POLICY_NAME ACTION_TYPE SCOPE TIER_TBS------------ ---------------------- -------------- --------------------P1 COMPRESSION SEGMENTP21 STORAGESEGMENT ADO_T2?SQL> SELECT policy_name, object_name, inherited_from, enabled FROM user_ilmobjects;?POLICY_NAME OBJECT_NAME INHERITED_FROM ENABLE------------ -------------------- ---------------------------------------- ------P1 HEAT_TEST POLICY NOT INHERITED NOP21 ADO_MOVE POLICY NOT INHERITED YES?SQL> SELECT * FROM dba_ilmparameters;?Tablespace NameVALUE------------------------- ----------ENABLED 1JOB LIMIT 10EXECUTION MODE 3EXECUTION INTERVAL15TBS PERCENT USED 85TBS PERCENT FREE 25?6 ROWS selected.?执行操作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> @dba_tablespaces?+------------------------------------------------------------------------+| Report : Tablespaces || Instance : noncdb || USER : TRAVEL |+------------------------------------------------------------------------+?STATUS Tablespace Name TS TYPE Ext. Mgt. Seg. Mgt. Tablespace SIZE Used (IN bytes) Pct. Used--------- ------------------------- --------------- ---------- ---------- ------------------ ------------------ ---------ONLINE ADO_T1 PERMANENT LOCAL AUTO 209,715,200 202,375,168 97ONLINE ADO_T2 PERMANENT LOCAL AUTO 209,715,200 1,048,576 1ONLINE SYSAUX PERMANENT LOCAL AUTO 807,403,520 763,428,864 95ONLINE SYSTEM PERMANENT LOCAL MANUAL 817,889,280 811,401,216 99ONLINE TEMP TEMPORARY LOCAL MANUAL 91,226,112 90,177,536 99ONLINE UNDOTBS1 UNDO LOCAL MANUAL 152,043,520 151,846,912 100ONLINE USERS PERMANENT LOCAL AUTO 66,846,720 15,400,960 23 ------------------ ------------------ ---------avg 73SUM 2,354,839,552 2,035,679,232?7 ROWS selected.???SQL> COL job_name format A20SQL> COL object_name format A8SQL> COL task_id format 99999SQL> SQL> SELECT task_id, state FROM user_ilmtasks;?TASK_ID STATE------- ------------------ 2 COMPLETED 62 COMPLETED?SQL> SQL> SQL> COL object_name format A20SQL> col POLICY_NAME FOR a10SQL> col SELECTED_FOR_EXECUTION FOR a80SQL> SELECT TASK_ID, POLICY_NAME, OBJECT_NAME, 2 SELECTED_FOR_EXECUTION, JOB_NAME 3 FROM user_ilmevaluationdetails;?TASK_ID POLICY_NAM OBJECT_NAME SELECTED_FOR_EXECUTION JOB_NAME------- ---------- -------------------- -------------------------------------------------------------------------------- -------------------- 62 P21 ADO_MOVE SELECTED FOR EXECUTION ILMJOB122 62 P1 HEAT_TEST POLICY DISABLED 2 P1 HEAT_TEST SELECTED FOR EXECUTION ILMJOB42?SQL> SQL> COL job_name format A20SQL> COL object_name format A8SQL> COL task_id format 99999SQL> SQL> SQL> SELECT task_id, job_name, job_state FROM user_ilmresults;?TASK_ID JOB_NAME JOB_STATE------- -------------------- ---------------------------------------------------------------------- 2 ILMJOB42 COMPLETED SUCCESSFULLY 62 ILMJOB122 FAILED--发现任务失败，查看失败原因???SQL> col COMMENTS FOR a80SQL> SELECT task_id, job_name, job_state,COMMENTS FROM user_ilmresults;?TASK_ID JOB_NAME JOB_STATE COMMENTS------- -------------------- ---------------------------------------------------------------------- -------------------------------------------------------------------------------- 2 ILMJOB42 COMPLETED SUCCESSFULLY 62 ILMJOB122 FAILED ORA-01652: unable TO extend temp segment BY 1024 IN tablespace ADO_T2 ORA-06512: at line 1? 82 ILMJOB162 FAILED ORA-01652: unable TO extend temp segment BY 1024 IN tablespace ADO_T2 ORA-06512: at line 1原因为表空间存储空间不够。。。。?SQL> ??增大数据文件SQL> ALTER DATABASE datafile '/oradata/noncdb/ado_t2.dbf' resize 400m;?DATABASE altered.??在此执行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> SQL> SELECT task_id, job_name, job_state,COMMENTS FROM user_ilmresults;?TASK_ID JOB_NAME JOB_STATE COMMENTS------- -------------------- ---------------------------------------------------------------------- -------------------------------------------------------------------------------- 2 ILMJOB42 COMPLETED SUCCESSFULLY 62 ILMJOB122 FAILED ORA-01652: unable TO extend temp segment BY 1024 IN tablespace ADO_T2 ORA-06512: at line 1? 82 ILMJOB162 FAILED ORA-01652: unable TO extend temp segment BY 1024 IN tablespace ADO_T2 ORA-06512: at line 1? 103 ILMJOB242 COMPLETED SUCCESSFULLY?成功完成SQL> col TABLE_NAME FOR a20SQL> /?TABLE_NAME TABLESPACE_NAME-------------------- ------------------------------------------------------------ADO_MOVE ADO_T2HEAT_TEST USERS
原文地址：Heat Map and Automatic Data Optimization : part-2, 感谢原作者分享。