Heat Map and Automatic Data Optimization : part-2
上一篇测试了ADO的压缩功能 Heat Map and Automatic Data Optimization : part-1 下面测下ADO的存储层功能 简单的说下就是使用ado move 表、分区操作，把不是热数据的数据移动到性能低下的存储上 下面是工作的示意图 准备环境 SQL conn travel/aaConnected.?
上一篇测试了ADO的压缩功能 Heat Map and Automatic Data Optimization : part-1
下面测下ADO的存储层功能
简单的说下就是使用ado move 表、分区操作，把不是热数据的数据移动到性能低下的存储上
下面是工作的示意图
准备环境
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 20140526 3209 7 2927 000000009F6CA108 000000009F9865B8
?
?
SQL> SELECT * FROM tab;
?
TNAME TABTYPE CLUSTERID
----------------------------------- -------------- ----------
HEAT_TEST TABLE
?
SQL>
SQL> conn / AS sysdba
Connected.
?
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 97
ONLINE ADO_T2 PERMANENT LOCAL AUTO 209,715,200 1,048,576 1
ONLINE SYSAUX PERMANENT LOCAL AUTO 765,460,480 760,086,528 99
ONLINE SYSTEM PERMANENT LOCAL MANUAL 817,889,280 811,401,216 99
ONLINE TEMP TEMPORARY LOCAL MANUAL 91,226,112 90,177,536 99
ONLINE UNDOTBS1 UNDO LOCAL MANUAL 152,043,520 151,257,088 99
ONLINE USERS PERMANENT LOCAL AUTO 66,846,720 15,400,960 23
------------------ ------------------ ---------
avg 74
SUM 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 a20
SQL> col object_name FOR a20
SQL> col "Tracking Time" FOR a40
SQL> col "Seg write" FOR a20
SQL>
SQL> pro DBA_HEAT_MAP_SEG_HISTOGRAM
DBA_HEAT_MAP_SEG_HISTOGRAM
SQL> 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_SEGMENT
DBA_HEAT_MAP_SEGMENT
SQL>
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');
?
OWNER OBJECT_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 A12
SQL> COL TIER_TBS format A20
SQL> 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 SEGMENT
P21 STORAGE SEGMENT 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 NO
P21 ADO_MOVE POLICY NOT INHERITED YES
?
SQL> SELECT * FROM dba_ilmparameters;
?
Tablespace Name VALUE
------------------------- ----------
ENABLED 1
JOB LIMIT 10
EXECUTION MODE 3
EXECUTION INTERVAL 15
TBS PERCENT USED 85
TBS 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 97
ONLINE ADO_T2 PERMANENT LOCAL AUTO 209,715,200 1,048,576 1
ONLINE SYSAUX PERMANENT LOCAL AUTO 807,403,520 763,428,864 95
ONLINE SYSTEM PERMANENT LOCAL MANUAL 817,889,280 811,401,216 99
ONLINE TEMP TEMPORARY LOCAL MANUAL 91,226,112 90,177,536 99
ONLINE UNDOTBS1 UNDO LOCAL MANUAL 152,043,520 151,846,912 100
ONLINE USERS PERMANENT LOCAL AUTO 66,846,720 15,400,960 23
------------------ ------------------ ---------
avg 73
SUM 2,354,839,552 2,035,679,232
?
7 ROWS selected.
?
?
?
SQL> COL job_name format A20
SQL> COL object_name format A8
SQL> COL task_id format 99999
SQL>
SQL> SELECT task_id, state FROM user_ilmtasks;
?
TASK_ID STATE
------- ------------------
2 COMPLETED
62 COMPLETED
?
SQL>
SQL>
SQL> COL object_name format A20
SQL> col POLICY_NAME FOR a10
SQL> col SELECTED_FOR_EXECUTION FOR a80
SQL> 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 A20
SQL> COL object_name format A8
SQL> COL task_id format 99999
SQL>
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 a80
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
原因为表空间存储空间不够。。。。
?
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 a20
SQL> /
?
TABLE_NAME TABLESPACE_NAME
-------------------- ------------------------------------------------------------
ADO_MOVE ADO_T2
HEAT_TEST USERS原文地址：Heat Map and Automatic Data Optimization : part-2, 感谢原作者分享。
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How does springboot read lists, arrays, map collections and objects in yml files?
May 11, 2023 am 10:46 AM
application.yml defines the list collection. The first way is to use the @ConfigurationProperties annotation to obtain all the values of the list collection type:code:status:-200-300-400-500. Write the entity class corresponding to the configuration file. What needs to be noted here is that defining the list Collection, first define a configuration class Bean, and then use the annotation @ConfigurationProperties annotation to obtain the list collection value. Here we will explain the role of the relevant annotations. @Component hands over the entity class to Spring management @ConfigurationPropertie
How to set expiration time map in Java
May 04, 2023 am 10:13 AM
1. Technical background In actual project development, we often use caching middleware (such as redis, MemCache, etc.) to help us improve the availability and robustness of the system. But in many cases, if the project is relatively simple, there is no need to specifically introduce middleware such as Redis to increase the complexity of the system in order to use caching. So does Java itself have any useful lightweight caching components? The answer is of course yes, and there is more than one way. Common solutions include: ExpiringMap, LoadingCache and HashMap-based packaging. 2. Technical effects to realize common functions of cache, such as outdated deletion strategy, hotspot data warm-up 3. ExpiringMap3.
What are the ways to implement thread safety for Map in Java?
Apr 19, 2023 pm 07:52 PM
Method 1. Use HashtableMapashtable=newHashtable(); This is the first thing everyone thinks of, so why is it thread-safe? Then take a look at its source code. We can see that our commonly used methods such as put, get, and containsKey are all synchronous, so it is thread-safe publicsynchronizedbooleancontainsKey(Objectkey){Entrytab[]=table;inthash=key.hashCode( );intindex=(hash&0x7FFFFFFF)%tab.leng
An in-depth exploration of the structural aspects and functions of OP Stack
Jan 18, 2024 pm 05:45 PM
OPStack is an open source blockchain framework released by Optimism Collective, the development group behind the Optimism Network. It is an important tool for both the Ethereum and Optimism communities. The main goal of OPStack is to strengthen the Optimism Network, providing key software tools to the Optimism Mainnet, as well as the upcoming Optimism Superchain and its governance model. By providing a developer-oriented environment, the core idea of OPStack is to promote growth and innovation in the Ethereum space. It paves the way for cutting-edge developments and makes blockchain creation simpler. OPStac
How to configure and use the map module in Nginx server
May 21, 2023 pm 05:14 PM
The map directive uses the ngx_http_map_module module. By default, nginx loads this module unless artificially --without-http_map_module. The ngx_http_map_module module can create variables whose values are associated with the values of other variables. Allows classification or simultaneous mapping of multiple values to multiple different values and storage in a variable. The map directive is used to create a variable, but only performs the view mapping operation when the variable is accepted. For processing requests that do not reference variables, this The module has no performance shortcomings. 1.ngx_http_map_module module instruction description map syntax
How to convert objects to Maps in Java - using BeanMap
May 08, 2023 pm 03:49 PM
There are many ways to convert javabeans and maps, such as: 1. Convert beans to json through ObjectMapper, and then convert json to map. However, this method is complicated and inefficient. After testing, 10,000 beans were converted in a loop. , it takes 12 seconds! ! ! Not recommended. 2. Obtain the attributes and values of the bean class through Java reflection, and then convert them into the key-value pairs corresponding to the map. This method is the second best, but it is a little more troublesome. 3. Through net.sf.cglib.beans.BeanMap Method in the class, this method is extremely efficient. The difference between it and the second method is that because of the use of cache, the bean needs to be initialized when it is first created.
Optimize the performance of Go language map
Mar 23, 2024 pm 12:06 PM
Optimizing the performance of Go language map In Go language, map is a very commonly used data structure, used to store a collection of key-value pairs. However, map performance may suffer when processing large amounts of data. In order to improve the performance of map, we can take some optimization measures to reduce the time complexity of map operations, thereby improving the execution efficiency of the program. 1. Pre-allocate map capacity. When creating a map, we can reduce the number of map expansions and improve program performance by pre-allocating capacity. Generally, we
In-depth understanding of the OP Mainnet network: What kind of chain is OP Mainnet?
Jan 24, 2024 am 11:30 AM
Previously, Optimism officially announced that the Optimism chain has been renamed OPMainnet. Some people may ask what chain OPMainnet is? According to current information, OPMainnet is an open source super chain dedicated to funding public goods in a sustainable manner. It is one of many L2 chains that can communicate seamlessly with Base, ZoraNetwork, PGN, Redstone and other chains, but it does not It is not a single blockchain, it represents twenty entire super chain networks. The editor below will talk about this OPMainnet in detail to help you understand the OPMainnet network. What chain is OPMainnet? OPMainnet is Ethernet
