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 ;





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

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('scott', 'emp', 2);



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

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('scott', 'emp', 'emp_work', NULL, 2);

Sample : 
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => 'SMS',
orig_table => 'SMS_TRANSACTION',
int_table => 'INT_SMS_TRANSACTION');
END;
/



---------------------------------------------------------------------
-- COPY THE TABLE DEPENDENTS FROM THE ORIGINAL TABLE TO THE INTERIM TABLE
---------------------------------------------------------------------

DECLARE
   error_count pls_integer := 0;
BEGIN
   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('scott', 'emp', 'emp_work', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
   DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
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;







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)

Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more