For ADO to work, access to and modification of data needs to be constantly tracked. This is done by enabling a heat map in your database:
alter system set heat_map=on scope=both;The database will immediately start sampling information. After a while, the heat map will contain information about how your objects are being used.
My table contains 211 partitions. I would like to add an ADO policy to one of them to illustrate how ADO policies works.
Let's list the 3 largest partitions:
select p.TABLE_NAME, s.partition_name,s.tablespace_name, p.compression,p.num_rows,Round(sum(s.bytes)/1024/1024/1024,1) "GB" from dba_segments S join dba_tab_partitions p on (s.partition_name = p.partition_name) where s.segment_name='ENTITIY_PR' and s.owner='SSB' group by p.table_name,s.partition_name,s.tablespace_name,p.compression,p.num_rows order by 5 desc fetch first 3 rows only;
| TABLE_NAME | PARTITION_NAME | TABLESPACE_NAME | COMPRESSION | NUM_ROWS | GB |
|---|---|---|---|---|---|
| ENTITIY_PR | SYS_P5055 | SSB | DISABLED | 43448193 |
3,3 |
| ENTITIY_PR | SYS_P4518 | SSB | DISABLED | 43447546 |
3,4 |
| ENTITIY_PR | SYS_P4709 | SSB | DISABLED | 43217045 |
3,3 |
Add a policy to have Oracle automatically compress the partition segment after one day without any access (read and write):
alter table SSB.ENTITIY_PR
modify partition SYS_P5055 ilm add policy
row store compress advanced segment after 1 days of no access;
In addition to no access, you could also use low access, no modification or creation to express activity type
Verify that the ADO policy is in place:
SELECT policy_name,
object_owner,
object_name,
subobject_name,
object_type,
inherited_from,
enabled,
deleted
FROM dba_ilmobjects
WHERE object_owner='SSB'
AND object_name='ENTITIY_PR'
AND subobject_name='SYS_P5055'
ORDER BY 1;
| POLICY_NAME | OBJECT_OWNER | OBJECT_NAME | SUBOBJECT_NAME | OBJECT_TYPE | INHERITED_FROM | ENABLED | DELETED |
|---|---|---|---|---|---|---|---|
| P22 | SSB | ENTITIY_PR | SYS_P5055 | TABLE PARTITION | POLICY NOT INHERITED | YES | NO |
What does the heat map say about this particular partition?
select * from dba_heat_map_segment where owner='SSB' and object_name='ENTITIY_PR' AND subobject_name='SYS_P5055';No rows returned.
So this partitioned hasn't actually been modified or accessed since the heat map was enabled.
Let's access the partition with a query, and see if this will be registered in the heat map.
SELECT SYSTIMESTAMP FROM DUAL; --> 25-JUN-21 10.04.30 SELECT * FROM SSB.ENTITIY_PR PARTITION (SYS_P5055) fetch first 10 rows only;Run the query against dba_heat_map_segment again, and there is now an entry in the heat map for my visit to the table:
| OWNER | OBJECT_NAME | SUBOBJECT_NAME | SEGMENT_WRITE_TIME | SEGMENT_READ_TIME | FULL_SCAN | LOOKUP_SCAN |
|---|---|---|---|---|---|---|
| SSB | ENTITIY_PR | SYS_P5055 | 25.06.2021 10:04:34 | 25.06.2021 10:04:34 |
Let's try to send a query to the database that will require an PK index lookup:
SELECT * FROM SBB.ENTITIY_PR PARTITION (SYS_P5055) WHERE systemid = 1145708618;Run the query against dba_heat_map_segment again, and you can now see that the lookup was recorded in the heat map:
| OWNER | OBJECT_NAME | SUBOBJECT_NAME | SEGMENT_WRITE_TIME | SEGMENT_READ_TIME | FULL_SCAN | LOOKUP_SCAN |
|---|---|---|---|---|---|---|
| SBB | ENTITIY_PR | SYS_P5055 | 25.06.2021 10:25:33 | 25.06.2021 10:25:33 | 25.06.2021 10:25:33 |
set lines 200
spool purge.sql
set heading off
set feedback off
set verify off
set echo off
set pages 0
set trimspool on
select 'exec DBMS_SHARED_POOL.PURGE (''' || ADDRESS ||','|| HASH_VALUE || ''',''C'');'
from V$SQLAREA where SQL_ID IN (SELECT SQL_ID FROM v$sqlarea where sql_text like 'SELECT * FROM%SBB.ENTITIY_PR%SYS_P5055%');
select 'exit' from dual;
exit
Run the script and it will spool a file "purge.sql" to your current directory, which looks like this in my case:
exec DBMS_SHARED_POOL.PURGE ('00000000BF56D560,1541949375','C');
exec DBMS_SHARED_POOL.PURGE ('00000000C67994B8,3904135316','C');
exec DBMS_SHARED_POOL.PURGE ('00000000C67973D8,27827680','C');
Run these statements, and the cursors refering to ENTITY_PR will be removed from memory, and the incrementing timestamps will stop.
Obviously, if other users are actually using the object, you would need to be careful about purging the shared pool.
After one full day of no access, let's check if the ADO policy has done its work:
select p.TABLE_NAME, s.partition_name,s.tablespace_name, p.compression,p.num_rows,Round(sum(s.bytes)/1024/1024/1024,1) "GB" from dba_segments S join dba_tab_partitions p on (s.partition_name = p.partition_name) where s.segment_name='ENTITIY_PR' and s.owner='SSB' and s.partition_name in ( 'SYS_P5055') and s.segment_name = p.table_name group by p.table_name,s.partition_name,s.tablespace_name,p.compression,p.num_rows order by 5 desc;The output confirms that the partition has been compressed, and if you check the output from the same query earlier in this post, you'll notice that the size of the partition has dropped from 3,3G to 1,3G, a 60% reduction:
| TABLE_NAME | PARTITION_NAME | TABLESPACE_NAME | COMPRESSION | NUM_ROWS | GB |
|---|---|---|---|---|---|
| ENTITY_PR | SYS_P5055 | SSB | ENABLED | 43448193 |
1,3 |
SELECT policy_name,
object_owner,
object_name,
subobject_name,
object_type,
inherited_from,
enabled,
deleted
FROM dba_ilmobjects
WHERE object_owner='SSB'
AND object_name='ENTITY_PR'
AND subobject_name='SYS_P5055'
ORDER BY 1;
| POLICY_NAME | OBJECT_OWNER | OBJECT_NAME | SUBOBJECT_NAME | OBJECT_TYPE | INHERITED_FROM | ENABLED | DELETED |
|---|---|---|---|---|---|---|---|
| P22 | SAKSINFO_SKFIN | ENTITET_EGENSKAP | SYS_P5055 | TABLE PARTITION | POLICY NOT INHERITED | NO | NO |
Further reading:
The official Oracle 19c ADO documentation As always, the excellently written tutorials from Tim Hall I also used examples from the book "Oracle Database 12c New Features", Chapter 8, by Robert G. Freeman published by Oracle Press.
No comments:
Post a Comment