A journey towards space

 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:-

Following is the table structure where you can see table is partitioned by endtime column and sub-partitioned by nodeid column.

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/

Around 43.8 TB usable space is left before compression.

• Capture size of the tablespace before compression

SQL> select sum(bytes)/(1024*1024*1024) "Size" from dba_data_files where tablespace_name='READING_Y2021_M01';

      Size
----------
 465.58078

• Capture size of all segments under tablespace

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 238.781311

INDEX SUBPARTITION 226.695374

• Capture free space in the tablespace

SQL> select sum(bytes)/(1024*1024) "MB" from dba_free_space where tablespace_name='READING_Y2021_M01';

        MB

----------

   96.6875

Solution part:

Please mind that we can not compress index segment using HCC.So our target here is to compress all table sub-partition under the tablespace READING_Y2021_M01.

Since application will not read/write partition/sub-partition older than 24 months so we will use highest level of compression,which is archive high.

Please see below link for documentation

https://www.oracle.com/a/ocom/docs/database/hybrid-columnar-compression-brief.pdf

Use below SQL to compress it.

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

Capture size of all segments under tablespace post compression

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

SQL> select sum(bytes)/(1024*1024) "MB" from dba_free_space where tablespace_name='READING_Y2021_M01';

     MB

----------

239918.875

So free space has increased from 96 MB to 239918 MB or 239 GB.

Till now we have seen it has compressed but space is not released back to ASM.So we need to shrink datafiles of that tablespace upto highwater mark.

Use below script to resize datafiles upto high water mark.

set verify off

set echo on

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 'alter database datafile '''|| file_name|| ''' resize '|| ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )||'M;'

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( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 10

Execute SQL found from the above query.

Execute above sql and see changes in Usable_file_MB in lsdg report

After datafile shrink following is the output of lsdg

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  94381496          6593280        43894108              0             Y  DATA/

MOUNTED  NORMAL  N         512             512   4096  4194304   35143680  31708876           732160        15488358              0             N  RECO/

So difference is 43894108 -43847868 =46240 MB or approximately 46 GB

We have identified that we have 239 GB of free space. What is the use of HCC if we cant not reclaim the space back to ASM? Then where is the space going?

This is only possible if tablespace has lots of fragmentation means we have many small free extents residing in the datafile resulting not to release that space.

Use below script found in metalink

Script to Detect Tablespace Fragmentation (Doc ID 1020182.6)

When I execute the script I see it has indeed lots of free space

At the end I see following

TABLESPACE_NAME                # OF EXTENTS          TOTAL BYTES

------------------------------ ------------ --------------------

READING_Y2021_M01                     20166      203,056,939,008

So there are 20166 extents sitting in the tablespace which are not contiguous and hiding in your tablespace consuming 203 GB

This is exactly matching with this simple query

SQL>  select sum(bytes) from dba_free_space where tablespace_name='READING_Y2021_M01';

                            Contiguous Extents Report

   SUM(BYTES)

-------------

 203056939008

• 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 echo on
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

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='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/

Space reclaimed 44029918 -43847868 MB= 182 GB

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....