Tuesday, July 16, 2024

Oracle options to check fragmentation and perform Table and Index reorganization

 

There were many   request  coming in for Reorg  Hence  thought of documenting handy article to check fragmentation and perform reorg .

After performing Reorganization we need to also gather fresh statistics  .   Check invalid objects pre and post Reorganization . 

Article  will have 2  main topics mainly 
1)  Ways to check Fragmentation 
2)  Performing Reorganization .



For Lob    i have  documented in my  previous Blog  below 

https://abdul-hafeez-kalsekar.blogspot.com/2024/01/oracle-database-lob-maintenance.html




Ways to  check Fragmentations : 



==> High water mark(HWM)  check query :

set verify off
     column owner format a10
     column alcblks heading 'Allocated|Blocks' just c
     column usdblks heading 'Used|Blocks'      just c
     column hgwtr heading 'High|Water'         just c
     break on owner skip page
     select a.owner,a.table_name,b.blocks alcblks,a.blocks usdblks,(b.blocks-a.empty_blocks-1)
     hgwtr from dba_tables a,dba_segments b where a.table_name=b.segment_name
     and a.owner=b.owner and a.owner not in('SYS','SYSTEM') and a.blocks <> (b.blocks-a.empty_blocks-1)
     and a.owner like upper('&owner')||'%'and a.table_name like upper('&table_name')||'%'
     order by 1,2;

Enter value for owner:  ABDUL 
Enter value for table_name: TABLE1



==> Check  Fragmented Table 


col TABLE_NAME for a30
col fragmented_size for a15
col table_size for a15
col Used_size for a15
select table_name,round(((blocks*8)/1024/1024),2)||'GB' "table_size",round((num_rows*avg_row_len/1024/1024/1024),2)||'GB' "Used_size",
(round(((blocks*8/1024/1024)),2)-round((num_rows*avg_row_len/1024/1024/1024),2))|| 'GB' "fragmented_size" from dba_tables
where owner not in ('SYS','SYSTEM','PERFSTAT') and last_analyzed is not null and num_rows>0 and table_name='SECURE' and owner='HAFEEZ'
and blocks >0
order by 4 asc
/




==> Checking Partition table fragmentation : 

  SELECT table_name,
         partition_name,
         ROUND ( (blocks / 1024 * 16), 2) "size (mb)",
         ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (mb)",
         (  ROUND ( (blocks / 1024 * 16), 2)
          - ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
            "wasted_space (mb)"
    FROM dba_tab_partitions
   WHERE     (ROUND ( (blocks / 1024 * 16), 2) >
                 ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
         AND table_name = 'TBK_XXX'
ORDER BY 5 DESC;

 select 
  table_owner
  ,table_name
  ,partition_name
  ,high_value
  ,compression
  ,compress_for
  ,avg_row_len
  ,round(((blocks*16/1024)),2)/1024 "TOTAL_SIZE_GB" --The amount of space used by the partition in gigabytes.
  ,round((num_rows*avg_row_len/1024/1024),2)/1024 "ACTUAL_SIZE_GB" -- The amount of space used by the partition in gigabytes, calculated based on the number of rows and the average row length.
  ,round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/1024 "FRAGMENTED_SPACE_GB" --The amount of space that is not used by the partition, in gigabytes.
  ,decode(round(((blocks*16/1024)),2),0,0, (round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100) "percentage" --The percentage of unused space in the partition, calculated by dividing the unused space by the total space and multiplying by 100. 
from dba_tab_partitions 
WHERE 
1=1
and table_owner='DEV_DW'
and table_name='TEST_TABLE'
and partition_name='SYS_P21822227'
and round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/1024 > 0
order by 6 desc
;






==>  Checking Top Fragmented Tables 

select t1.owner,t1.table_name,nvl(t1.blocks*8,1) "size fragmented KB",round(nvl((t1.num_rows*t1.avg_row_len/1024),1),2) "actaul size KB",round(nvl(t1.blocks*8,1) - nvl((t1.num_rows*t1.avg_row_len/1024),1),2) "fragmentation KB"
from dba_tables t1
order by t1.owner,round(nvl(t1.blocks*8,1) - nvl((t1.num_rows*t1.avg_row_len/1024),1),2) desc ; 



select blocks,last_analyzed,owner,table_name,round((blocks*8),2) "size (kb)" , 
                            round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
                            (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
from dba_tables
where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
and owner in ('HAFEEZ')
order by 4  ;




==>  Checking  Index fragmentation : 

You can run the ANALYZE INDEX <index> VALIDATE STRUCTURE command on the affected indexes - each invocation of this command creates a single row in the INDEX_STATS view. This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE. The 'badness' of the index can then be judged by the ratio of 'DEL_LF_ROWS' to 'LF_ROWS'.

you may decide that index should be rebuilt if more than 20% of its rows are deleted:


ANALYZE INDEX &&index_name VALIDATE STRUCTURE;
 
    col name         heading 'Index Name'          format a30
    col del_lf_rows  heading 'Deleted|Leaf Rows'   format 99999999
    col lf_rows_used heading 'Used|Leaf Rows'      format 99999999
    col ibadness     heading '% Deleted|Leaf Rows' format 999.99999
     SELECT name,
       del_lf_rows,
       lf_rows - del_lf_rows lf_rows_used,
       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
    FROM index_stats
       where name = upper('&index_name');
    undefine index_name 
 

select del_lf_rows * 100 / decode(lf_rows,0,1,lf_rows) from index_stats
where name = 'index_ name';






==>  Checking Fragmentation  using segment space advisor  

set echo off
set feedback off
set verify off
set linesize 80
set serveroutput on size unlimited

spool /home/oracle/scripts/segment_advisor.txt

declare
 v_task_name varchar2(100);
 v_task_desc varchar2(500);
 v_objid number;
begin
 begin
 v_task_name := 'SEGMENT_ADVISOR_RUN';
 v_task_desc := 'MANUAL SEGMENT ADVISOR RUN';


-- Create Segment Advisor task.

 dbms_advisor.create_task
 (
 advisor_name => 'Segment Advisor',
 task_name => v_task_name,
 task_desc => v_task_desc
 );

-- Add all segments to the task.

 for s in (select segment_name, segment_type
 from DBA_segments
 where segment_type in ('TABLE', 'INDEX', 'LOB')
 and owner != 'SYS' and owner != 'SYSTEM' and owner != 'OLAPSYS' and owner != 'SYSMAN' and owner != 'ODM' and owner != 'RMAN' and owner != 'ORACLE_OCM' and owner != 'EXFSYS' and owner != 'OUTLN' and owner != 'DBSNMP' and owner != 'OPS' and owner != 'DIP' and owner != 'ORDSYS' and owner != 'WMSYS' and owner != 'XDB' and owner != 'CTXSYS' and owner != 'DMSYS' and owner != 'SCOTT' and owner != 'TSMSYS' and owner != 'MDSYS' and owner != 'WKSYS' and owner != 'ORDDATA' and owner != 'OWBSYS' and owner != 'ORDPLUGINS' and owner != 'SI_INFORMTN_SCHEMA' and owner != 'PUBLIC' and owner != 'OWBSYS_AUDIT' and owner != 'APPQOSSYS' and owner != 'APEX_030200' and owner != 'FLOWS_030000' and owner != 'WK_TEST' and owner != 'SWBAPPS' and owner != 'WEBDB' and owner != 'OAS_PUBLIC' and owner != 'FLOWS_FILES' and owner != 'QMS')
 loop
 dbms_advisor.create_object
 (
 task_name => v_task_name,
 object_type => s.segment_type,
 attr1 => user,
 attr2 => s.segment_name,
 attr3 => null,
 attr4 => null,
 attr5 => null,
 object_id => v_objid
 );
 end loop;


-- Set task parameter to recommend all.

 dbms_advisor.set_task_parameter
 (
 task_name => v_task_name,
 parameter => 'RECOMMEND_ALL',
 value => 'TRUE'
 );


-- Run Segment Advisor.

 dbms_advisor.execute_task(v_task_name);

exception when others then
 dbms_output.put_line('Exception: ' || SQLERRM);
 end;


-- Output findings.

 dbms_output.put_line(chr(10));
 dbms_output.put_line('Segment Advisor Recommendations');
 dbms_output.put_line('--------------------------------------------------------------------------------');

for r in (select segment_owner, segment_name, segment_type, partition_name,
 tablespace_name, allocated_space, used_space,
 reclaimable_space, chain_rowexcess, recommendations, c1, c2, c3
 from table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'))
 where segment_owner != 'SYS' and segment_owner != 'SYSTEM' and segment_owner != 'OLAPSYS' and segment_owner != 'SYSMAN' and segment_owner != 'ODM' and segment_owner != 'RMAN' and segment_owner != 'ORACLE_OCM' and segment_owner != 'EXFSYS' and segment_owner != 'OUTLN' and segment_owner != 'DBSNMP' and segment_owner != 'OPS' and segment_owner != 'DIP' and segment_owner != 'ORDSYS' and segment_owner != 'WMSYS' and segment_owner != 'XDB' and segment_owner != 'CTXSYS' and segment_owner != 'DMSYS' and segment_owner != 'SCOTT' and segment_owner != 'TSMSYS' and segment_owner != 'MDSYS' and segment_owner != 'WKSYS' and segment_owner != 'ORDDATA' and segment_owner != 'OWBSYS' and segment_owner != 'ORDPLUGINS' and segment_owner != 'SI_INFORMTN_SCHEMA' and segment_owner != 'PUBLIC' and segment_owner != 'OWBSYS_AUDIT' and segment_owner != 'APPQOSSYS' and segment_owner != 'APEX_030200' and segment_owner != 'FLOWS_030000' and segment_owner != 'WK_TEST' and segment_owner != 'SWBAPPS' and segment_owner != 'WEBDB' and segment_owner != 'OAS_PUBLIC' and segment_owner != 'FLOWS_FILES' and segment_owner != 'QMS'
 order by reclaimable_space desc)
 loop
 dbms_output.put_line('');
 dbms_output.put_line('Owner : ' || r.segment_owner);
 dbms_output.put_line('Segment : ' || r.segment_name);
 dbms_output.put_line('Segment Type : ' || r.segment_type);
 dbms_output.put_line('Partition Name : ' || r.partition_name);
 dbms_output.put_line('Tablespace : ' || r.tablespace_name);
 dbms_output.put_line('Allocated Space : ' || r.allocated_space);
 dbms_output.put_line('Used Space : ' || r.used_space);
 dbms_output.put_line('Reclaimable Space in MB : ' || r.reclaimable_space/1024/1024);
 dbms_output.put_line('Chain Rowexcess : ' || r.chain_rowexcess);
 dbms_output.put_line('Recommendations : ' || r.recommendations);
 dbms_output.put_line('Run First : ' || r.c3);
 dbms_output.put_line('Run Second : ' || r.c2);
 dbms_output.put_line('Run Third : ' || r.c1);
 dbms_output.put_line('--------------------------------------------------------------------------------');
 end loop;

-- Remove Segment Advisor task.
 dbms_advisor.delete_task(v_task_name);
end;
/


spool off;






Ways  to   remove Fragmentations : 



Main ways commonly  followed  are  : 

Shrink 
Alter table Move
CTAS method
Datapump or exp/imp




==>   Options  to Perform Reorg of  Table 

SQL> ALTER TABLE table name ENABLE ROW MOVEMENT;
SQL> ALTER TABLE table name SHRINK SPACE CASCADE;
SQL> ALTER TABLE table name DISABLE ROW MOVEMENT;


ALTER TABLE mytable MOVE PARALLEL (DEGREE 8) ONLINE UPDATE INDEXES




==>  Options  to  Perform  Reorg of  Partition Table  
 

ALTER TABLE TEST_TABLE enable row movement;
ALTER TABLE order MODIFY PARTITION SYS_P21674395 SHRINK SPACE;  

ALTER TABLE order MOVE PARTITION SYS_P21674395 UPDATE INDEXES;
ALTER TABLE order MOVE PARTITION SYS_P21674395 ONLINE UPDATE INDEXES;

ALTER TABLE  TEST_TABLE move PARTITION SYS_P21822227 NOCOMPRESS UPDATE INDEXES;  

ALTER TABLE  order MOVE PARTITION SYS_P21674395  COMPRESS FOR QUERY HIGH UPDATE INDEXES;
 




==>  Options  to  Perform rebuild of index / Remove Index Fragmentation 


ALTER TABLE four_seasons MODIFY PARTITION quarter_two REBUILD UNUSABLE LOCAL INDEXES;

ALTER INDEX sales_area_ix REBUILD PARTITION jan99_ix;
 
 ALTER INDEX index name SHRINK SPACE;

ALTER INDEX SCOTT.EMP_IDX REBUILD ONLINE parallel 10 ;






Check the progress of Alter Shrink Space Command

-- Following command will search alter table command and give you output how much it covered according to table size in first command.
-- GB_read give you how much it covered yet.
select a.event, a.WAIT_TIME, c.SQL_TEXT,
c.PHYSICAL_READ_BYTES / 1024 / 1024 / 1024 "GB_READ",
c.PHYSICAL_WRITE_BYTES / 1024 / 1024 / 1024 "GB_WRITE"
from v$session_wait a , v$session b , v$sql c
where UPPER(c.SQL_TEXT) like UPPER('%ALTER TABLE%')
and a.sid = b.sid
and b.SQL_ID = c.SQL_ID;



--If you have session id of session from which command running then use following command:
select a.event, a.WAIT_TIME, c.SQL_TEXT,
c.PHYSICAL_READ_BYTES / 1024 / 1024 / 1024 "GB_READ",
c.PHYSICAL_WRITE_BYTES / 1024 / 1024 / 1024 "GB_WRITE"
from v$session_wait a , v$session b , v$sql c
where a.SID =
and a.sid = b.sid
and b.SQL_ID = c.SQL_ID;








Online Redefinition  to perform Reorg  : 



A primary key is mandatory since materialized views and logs are created during the start of redefinition.
 
The user performing the re-organization requires the following
privileges:

* CREATE ANY TABLE
* ALTER ANY TABLE
* DROP ANY TABLE
* LOCK ANY TABLE
* SELECT ANY TABLE
* CREATE ANY INDEX
* CREATE ANY TRIGGER



GENERAL STEPS TO ONLINE REDEFINE A TABLE

1) Determine if the table to be moved can be redefined online (DBMS_REDEFINITION.CAN_REDEF_TABLE)

2) Create the interim table
The interim table need not be the same 'shape' (have similar structure) as the original table

3) Start the redefinition of the table (DBMS_REDEFINITION.START_REDEF_TABLE)
4) Copy the dependents from the original table to the interim table (DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS)
5) Execute a final synchronization between the original and interim tables (DBMS_REDEFINITION.SYNC_INTERIM_TABLE)
  This step will minimize the amount of time needed to execute the finish of the redefinition

6) Finish the redefinition (DBMS_REDEFINITION.FINISH_REDEF_TABLE)
7) Verify that the redefinition succeeded by comparing the original table (formerly interim) to the interim table (formerly original)
8) Drop the interim table




---------------------------------------------------------------------
-- Test if table can be redefined...
---------------------------------------------------------------------

 SET SERVEROUTPUT ON
begin
DBMS_REDEFINITION.CAN_REDEF_TABLE
(
uname=>'SCOTT',
tname=>'emp' );
end;
/




---------------------------------------------------------------------
-- CREATE THE INTERIM TABLE
---------------------------------------------------------------------

create table emp_work (
COL1 NUMBER,
COL2 VARCHAR2(1000),
COL3 VARCHAR2(1000),
COL4 VARCHAR2(1000));



---------------------------------------------------------------------
-- Start table redefinition...
---------------------------------------------------------------------

During this phase Oracle will copy (and transform) the data from the production table to the interim table. Oracle will also create a materialized view (snapshot) log on the table to track DML changes.

SET SERVEROUTPUT ON
 begin
 DBMS_REDEFINITION.start_redef_table( 
 uname=>'SCOTT',
 orig_table=>'emp',
 int_table=>'emp_work');
 end;
 /
 



---------------------------------------------------------------------
-- COPY THE TABLE DEPENDENTS FROM THE ORIGINAL TABLE TO THE INTERIM TABLE
---------------------------------------------------------------------
  
 SET SERVEROUTPUT ON
   DECLARE
  l_num_errors PLS_INTEGER;
    begin
   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
   uname=>'SCOTT',
   orig_table=>'emp',
   int_table=>'emp_work',
  copy_indexes => DBMS_REDEFINITION.cons_orig_params,
   copy_triggers => TRUE,
  copy_constraints => TRUE,
  copy_privileges => TRUE,
   ignore_errors => FALSE,
    num_errors => l_num_errors);
  end;
  /


--------------------------------------------------------------------
-- Do  necessary changes:  here we Create constraints on the interim table   (  skip thisif only reorganization is needec ) 
---------------------------------------------------------------------

SQL> alter table emp_work   add constraint int_empx_pk primary key(empno);
SQL> alter table emp_work add constraint 2 int_empx_fk foreign key(deptno) references dept(deptno);
SQL> alter table emp_work MODIFY CONSTRAINT int_empx_fk 2 DISABLE KEEP INDEX;



---------------------------------------------------------------------
-- Sync intermediate changes to interim table (optional)
---------------------------------------------------------------------

This step will apply changes captured in the materialized view log to the interim table. Perform this step frequently for high transaction tables.

SQL> exec dbms_redefinition.sync_interim_table('scott', 'emp', 'emp_work');




---------------------------------------------------------------------
-- Finish the redefinition process (this will swap the two tables)...
---------------------------------------------------------------------
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('scott', 'emp', 'emp_work');



---------------------------------------------------------------------
-- Drop the interim working table...
---------------------------------------------------------------------
DROP TABLE emp_work;







Redefine  table  without  primary Key : 


The Reason the table failed is because the Online redefinition happens either by using a primary key or using ROWID,
The Default is the Primary Key, 

If we dont  haveprimary key we need to use rowid  option . For    that "options_flag=>DBMS_REDEFINITION.cons_use_rowid"   needs to be added in   DBMS_REDEFINITION.CAN_REDEF_TABLE  and  DBMS_REDEFINITION.start_redef_table  function 

Eg 

 SET SERVEROUTPUT ON
begin
DBMS_REDEFINITION.CAN_REDEF_TABLE(
uname=>'SCOTT',
tname=>'emp',
options_flag=>DBMS_REDEFINITION.cons_use_rowid);
end;
/


SET SERVEROUTPUT ON
 begin
 DBMS_REDEFINITION.start_redef_table( 
 uname=>'SCOTT',
 orig_table=>'emp',
 int_table=>'emp_work',
 options_flag=>DBMS_REDEFINITION.cons_use_rowid);
 end;
 /
 


 
-- To perform online redefinition in parallel 
 
alter session force parallel dml parallel 32;
alter session force parallel query parallel 32;







References : 

https://oracle-base.com/articles/12c/online-move-partitions-and-subpartitions-12cr1#google_vignette

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/maintenance-partition-tables-indexes.html#GUID-CDAA2363-B83A-408C-82C9-3E3FA3928D2D
Or 
https://docs.oracle.com/en/database/oracle/oracle-database/18/vldbg/maintenance-partition-tables-indexes.html#GUID-BAFFE31C-07A2-4ED6-BDCF-8ECB79D7FE7D


Attempt to reduce the size of a LOB segment after the table / LOB becomes sparse -> How to Shrink (make less sparse) a LOB (BASICFILE or SECUREFILE)? (Doc ID 1451124.1)

 How to Re-Organize a Table Online (Doc ID 177407.1)

How to release unused space (SHRINK) occupied by a LOB segment by adding / dropping columns? (Doc ID 1417697.1) 

How to Shrink (make less sparse) a LOB (BASICFILE or SECUREFILE)? (Doc ID 1451124.1)

1 comment: