I really thank you if the title has brought you here to explore something new.I will try to share my experience as much as possible from this post.
Problem statement: Lets assume you have a very big partitioned table of size more than 20 TB which has a local partitioned index of same size. Even though purge routine from application is in place but due to some regulatory compliance you need to maintain certain number of years of old data. Application hardly makes any read/write data which is older than 24 months. Database size has been growing and you are about to reach 85% warning limit of storage. There is no option to expand storage as per Oracle database appliance aka ODA configuration
Current environment: ODA X8-2 with GI version as 19.18
Technology constraints: You are not ready to migrate database and apps to cloud and that is a big project which requires lots of capital investment. Also it involves lots of technical complexity.
Jump to a solution: Buy on-prem exadata or go to exadata cloud at customer
Problem solved right. What if your company/client does not have that budget and you need to act very quickly.
Alternative solution: We can compress all older partition using Hybrid columnar compression using highest level of compression as archive high.
This can be an option as HCC is readily available in Exadata/ODA.
Thought process started...How will you compress an existing partitioned table of such a large size?
Prepare SQL script for compression:-
CREATE TABLE ITRONADM.READING
(
NODEID NUMBER (10) NOT NULL,
ENDTIME TIMESTAMP (0) NOT NULL,
DATAVALUE BINARY_DOUBLE DEFAULT (0) NOT NULL,
STATUSID NUMBER (6) NOT NULL,
READINGGROUPALTERNATEKEY NUMBER (19) NOT NULL,
ENDTIMESECONDS NUMBER (3)
)
NOCOMPRESS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (BUFFER_POOL DEFAULT)
LOGGING
PARTITION BY RANGE (ENDTIME)
SUBPARTITION BY HASH (NODEID)
PARTITION READING_Y2016_M10_P2 VALUES LESS THAN (TIMESTAMP' 2016-10-17 00:00:01')
NOCOMPRESS
TABLESPACE READING_Y2016_M10
PCTFREE 10
INITRANS 56
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
( SUBPARTITION
TABLESPACE READING_Y2016_M10,
.....so on
Before we dive into solution lets capture some data before compression so that it helps you to compare after compression.
- Capture asm storage snapshot before compression using asmcmd lsdg command
State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 512 4096 4194304 316477440 94289016 6593280 43847868 0 Y DATA/
MOUNTED NORMAL N 512 512 4096 4194304 35143680 31679576 732160 15473708 0 N RECO/
- Capture size of the tablespace before compression
Size
----------
465.58078
- Capture size of all segments under tablespace
- Capture free space in the tablespace
select 'ALTER TABLE ITRONADM.'||segment_name||' MOVE SUBPARTITION '||partition_name||' COMPRESS FOR ARCHIVE HIGH UPDATE INDEXES;' from dba_segments where tablespace_name like 'READING_Y2021_M01%' and segment_type='TABLE SUBPARTITION' order by partition_name;
Sample statement...
ALTER TABLE ITRONADM.REGISTERREADING MOVE SUBPARTITION SYS_SUBP48553 COMPRESS FOR ARCHIVE HIGH UPDATE INDEXES;
Create a .sql file with all above SQL statements found in previous statement and below line to spool and enable parallel DDL to make it faster.
spool reading_y2021_m01.log
select 'compression started at '||sysdate from dual;
alter session force parallel ddl;
--SQL statements
spool off
SQL> select segment_type,sum(bytes)/(1024*1024*1024) "Size" from dba_segments where tablespace_name='READING_Y2021_M01' group by segment_type;
SEGMENT_TYPE Size
------------------ ----------
TABLE SUBPARTITION 7.92102051
INDEX SUBPARTITION 223.354309
So table subpartition has reduced from 238 GB to 7.9 GB.
So compression ratio=238/7.9 approximately 30x
Wow! isn't that amazing
- Capture free space in the tablespace post compression
- Resolve this fragmentation
Create below tablespaces to move index sub-partitions
set serverout on
declare
vSQL
varchar2(4000);
begin
for
tblspc_name in (select tablespace_name from dba_tablespaces where
tablespace_name like 'READING_Y2021_M01%' order by tablespace_name)
loop
vSQL :='CREATE TABLESPACE
IND_'||tblspc_name.tablespace_name|| ' DATAFILE SIZE 1G AUTOEXTEND ON MAXSIZE
32767M;';
dbms_output.put_line(vSQL);
end
loop;
end;
/
Add additional datafile to tablespaces
set serverout on
declare
vCOUNT
number;
begin
for
tblspc in (select tablespace_name,ceil(sum(bytes)/(1024*1024*1024)/32) as Count
from dba_segments where tablespace_name like 'READING_Y2021_M01%' and
segment_type='INDEX SUBPARTITION' group by tablespace_name)
loop
vCOUNT := 2;
while vCOUNT<=tblspc.Count
loop
dbms_output.put_line('ALTER
TABLESPACE IND_'||tblspc.tablespace_name||' ADD DATAFILE SIZE 1G AUTOEXTEND ON
MAXSIZE 32767M;') ;
vCOUNT :=vCOUNT+1;
end loop;
end
loop;
end;
Now use below PL/SQL block to generate SQL required to move index sub-partition
set serverout on
exec
dbms_output.enable('1000000000');
declare
vSQL
varchar2(4000);
begin
for
tblspc_name in (select tablespace_name from dba_tablespaces where
tablespace_name like 'READING_Y2021_M01%' order by tablespace_name)
loop
for cseg_name in (select
segment_name,partition_name from dba_segments where
tablespace_name=tblspc_name.tablespace_name and segment_type='INDEX
SUBPARTITION' order by partition_name)
loop
vSQL :='ALTER INDEX
ITRONADM.'||cseg_name.segment_name|| ' REBUILD SUBPARTITION '||cseg_name.partition_name||'
TABLESPACE IND_'||tblspc_name.tablespace_name|| ' PARALLEL 8;';
dbms_output.put_line(vSQL);
end loop;
end loop;
end;
Thank you very much if you have read upto this.We are almost at the end....
We will use below query to see the savings
set pagesize 4000
set line 200
set pagesize 400
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
alter session set result_cache_mode='FORCE';
select value from v$parameter where name = 'db_block_size';
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.tablespace_name like 'READING_Y2021_M01%' and a.file_id = b.file_id(+) and ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )>0 order by savings desc
Now you can see saving as 40 GB.So still not worth that means still you have lots of fragmentation.
Final step is now to rebuild those table sub-partition again and lets see the impact.
Use below PL/SQL to reorg table sub-partition
exec dbms_output.enable('1000000000');
declare
vSQL varchar2(4000);
begin
for tblspc_name in (select tablespace_name from dba_tablespaces where tablespace_name like 'READING_Y2021_M01%' order by tablespace_name)
loop
for cseg_name in (select segment_name,partition_name from dba_segments where tablespace_name=tblspc_name.tablespace_name and segment_type='TABLE SUBPARTITION' order by partition_name)
loop
vSQL :='ALTER TABLE ITRONADM.'||cseg_name.segment_name|| ' MOVE SUBPARTITION '||cseg_name.partition_name||' PARALLEL 8 UPDATE INDEXES;';
dbms_output.put_line(vSQL);
end loop;
end loop;
end;
After this exercise I reran the previous SQL was able to reclaim around 182 GB of space back to asm.
State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 512 4096 4194304 316477440 94653116 6593280 44029918 0 Y DATA/
MOUNTED NORMAL N 512 512 4096 4194304 35143680 31621056 732160 15444448 0 N RECO/
Lesson learnt:Do not think HCC will do the magic for you.You need to act to remove the fragmentation in the tablespace to see actual benefit out of HCC.
Thank you very much for all your patience and happy learning....
