Oracle Database Fragmentation Management

1. Tablespace Fragmentation *********************** ----1. View FSFI values Select A.tablespace_name,trunc (sqrt (max ( Blocks)/sum (blocks)) * (100/sqrt (sqrt (count (blocks))), 2) Fsfi from Dba_free_space a,dba_tablespaces bwhere A.tablespace_name=b.tablespace_nameand b.contents (' temporary ', ' UNDO ', ' Sysaux ') GROUP by A.tablespace_name Order BY FSFI; If FSFI is less than <30%, there are too many tablespace fragments. The maximum possible value for FSFI is 100 (an ideal single-file tablespace). As the range increases, the FSFI values slow down, and the FSFI value drops rapidly as the maximum range size decreases. ---2. View Dba_free_spacedba_free_space shows a tablespace that has free space, and if a tablespace is not contiguous, then each space will be dba_free_ There is a record in space. If a tablespace has several records indicating that the tablespace is fragmented, the tablespace will need to be defragmented when more than 500 of the table space fragments are managed by the dictionary. Select A.tablespace_name, COUNT (1) fragment amount from Dba_free_space A, dba_tablespaces b where A.tablespace_name =b.tablespace_nam Eand b.contents (' temporary ', ' UNDO ', ' Sysaux ') group by a.tablespace_namehaving Count (1) >20order by 2;-----3. Displays contiguous free space by table space ========scripT. Tfstsfgm========set ECHO off REM name:tfstsfrm. SQL rem USAGE: "@path/tfstsfgm" rem------------------------------------------------------------------------rem Requirements:rem SELECT on Dba_free_space REM--------------------------------------------------------------------- ---REM purpose:rem The following is a script. That would determine how many extents rem of contiguous free space you had in Oracle as well as the REM total amount O F free space is a tablespace.  From REM These results you can detect how fragmented your tablespace are. REM REM the ideal situation is to has one large free extent in your REM tablespace.    The more extents of free space there is in the REM tablespace, the more likely you'll run into fragmentation REM Problems.  The size of the free extents is also very important. REM If you had a lot of small extents (too small for any next REM extent size) but the total bytes of free space is large, then REM   Want to consider defragmentation options. REM------------------------------------------------------------------------rem disclaimer:rem this script. is provided for educational purposes only. It is not a REM supported by Oracle World Wide Technical support. REM the script. Have been tested and appears to work as intended. REM should always run new scripts on a test instance initially. REM------------------------------------------------------------------------rem Main text of script.      Follows:create table Space_temp (tablespace_name CHAR (+), contiguous_bytes number)/DECLARE     Cursor query is a SELECT * from Dba_free_space ORDER by Tablespace_name, block_id;     This_row Query%rowtype;   Previous_row Query%rowtype;      Total number;     Begin open Query;     Fetch query into This_row;     Previous_row: = This_row;     Total: = Previous_row.bytes; Loop Fetch Queryinto This_row;        Exit when Query%notfound;           If this_row.block_id = previous_row.block_id + previous_row.blocks then total: = all + this_row.bytes;        Insert into Space_temp (tablespace_name) values (previous_row.tablespace_name);           Else insert into space_temp values (Previous_row.tablespace_name, total);        Total: = This_row.bytes;   End If;     Previous_row: = This_row;     End Loop;   INSERT into space_temp values (Previous_row.tablespace_name, total);   End   . /set pagesize set NewPage 0 set echo off Ttitle Center ' contiguous extents report ' Skip 3 break on ' tabl   ESPACE NAME "Skip page duplicate spool contig_free_space.lis rem column" contiguous BYTES "format 999,999,999 Column "COUNT" format 999 column "Total BYTES" format 999,999,999 column "TODAY" Nopri NT new_value new_today format A1 REM  Select Tablespace_name "Tablespace NAME", contiguous_bytes "contiguous BYTES" from Space_temp where CONTIG      Uous_bytes is not NULL for order by tablespace_name, Contiguous_bytes desc;  Select Tablespace_name, COUNT (*) "# of Extents", SUM (contiguous_bytes) "Total bytes" from Space_temp Group      by Tablespace_name; Spool off drop table Space_temp/******************************************************************************** 2. Table Fragment ********************************************************************************----Method 1: Shows the 200 tables with the highest fragmentation rate ( Based on statistical accuracy) Col frag format 999999.99col owner format A30;COL table_name format A30;select * FROM (select A.owner,a.table_n Ame,a.num_rows,a.avg_row_len * a.num_rows total_bytes,sum (b.bytes), Trunc ((a.avg_row_len*a.num_rows)/sum (b.bytes), 2) *100| | ' % ' Fragfrom dba_tables a,dba_segments bwhere a.table_name=b.segment_nameand a.owner=b.ownerand a.owner not in (' SYS '    , ' SYSTEM ', ' outln ', ' Dmsys ', ' Tsmsys ', ' dbsnmp ', ' Wmsys ',   ' Exfsys ', ' Ctxsys ', ' XDB ', ' Olapsys ', ' Ordsys ', ' Mdsys ', ' Sysman ') GROUP by a.owner,a.table_name,a.avg_row_len,a.num_  Rows have A.avg_row_len*a.num_rows/sum (b.bytes) <0.7 ORDER by sum (b.bytes) desc) where rownum<=200;---method 2:-- Collect table Statistics exec dbms_stats.gather_table_stats (ownname=> ' SCOTT ',tabname=> ' tblorders ');--Determine fragmentation degree select table_name , Trunc (ROUND ((blocks * 8), 2)/1024,2) "High Water Levelm", Trunc (ROUND ((num_rows * avg_row_len/1024), 2)/1024,2) "R  EAL used Spacem ", Trunc (ROUND ((blocks * 10/100) * 8, 2)/1024,2)" Reserve Space (Pctfree) M ", Trunc (ROUND (blocks * 8-(Num_rows * avg_row_len/1024)-Blocks * 8 * 10/100), 2)/1024, 2) "Waste Spacem" from dba_tables WHERE table_name = ' tblorders '; ***************************************************** 3. Index Fragmentation ***************************************************************************** ---1: View index with index height of 2 and index size greater than 20M select Id.tablespacE_name, Id.owner, Id.index_name, Id.blevel, sum (sg.bytes)/1024/1024, sg.blocks, Sg.extentsfrom dba_indexes id,dba_se Gments sgwhere Id.owner=sg.ownerand Id.index_name=sg.segment_nameand Id.tablespace_name=sg.tablespace_nameand Id.owner not in (' SYS ', ' SYSTEM ', ' USER ', ' dbsnmp ', ' Ordsys ', ' outln ') and Sg.extents>100and Id.blevel>=2group by ID. Tablespace_name,id.owner,id.index_name,id.blevel,sg.blocks,sg.extentshaving sum (sg.bytes)/1024/1024>20;---2. Analyze index method (lock table) analyze index index_name validate Structure;select del_lf_rows*100/decode (lf_rows,0,1,lf_rows) Pct_deleted from Index_stats; if pct_deleted>20% indicates that index fragmentation is serious. ****************************************************** 4.automatic Segment Advisor *********************************************************** Frequent insert, update, and delete actions on a data table produce table space fragmentation. Oracle can perform segment shrink on a table or index. Makes segment's free space available to other segment in the tablespace, improving DML performance. Call Segment Advisor to perform a growth trend analysis on the specified segment to determine which segment benefit from segment shrink. Performing Shrink operations, Segment Advisor recommends the Enable table for row movementsql> ALTER TABLE scott.tblorders enable row movement;variable ID Number;begin declar  E name VARCHAR2 (100);  DESCR VARCHAR2 (500);  OBJ_ID number;  Begin name:= ' manual_tblorders ';  descr:= ' Segment Advisor Example ';        Dbms_advisor.create_task (advisor_name = ' Segment advisor ', TASK_ID +: ID, task_name  = = Name, Task_desc = DESCR); Dbms_advisor.create_object (task_name = name, object_type = ' TABLE ', attr1 =            ' SCOTT ', attr2 = ' tblorders ', ATTR3 = null, ATTR4 = NULL, ATTR5  = NULL, object_id = obj_id);            Dbms_advisor.set_task_parameter (task_name = name, parameter = ' Recommend_all ', value  = ' TRUE ');  Dbms_advisor.execute_task (name); End;end; /---Delete execution plan declare name VARCHAR2; begin name:= ' manual_tblorders '; Dbms_advisor. DeletE_task (name); End /---Manual execution plan declare name VARCHAR2, begin name:= ' Manual_tblorders ';d bms_advisor.execute_task (name); End /note: If there is already data in the execution plan results, you cannot do it manually, you need to remove it---see if the manually created plan has been completed select task_id, Task_name, status,advisor_name,created From Dba_advisor_taskswhere owner = ' SYS ' and task_name= ' manual_tblorders ' and advisor_name = ' Segment advisor '; select Af.task_name, ao.attr2 segname, AO.ATTR3 partition, Ao.type, af.message from Dba_advisor_findings af, Dba_advisor_object s ao where ao.task_id = af.task_id and ao.object_id = af.object_id and af.task_id=&task_id;----querying only objects that can be shrink operations SE Lect F.task_name, o.attr2 segname, O.ATTR3 partition, O.type, f.message from Dba_advisor_findings F, dba_advisor_objects Owhere o.object_id = F.object_idand o.task_name=f.task_name--and f.message like '%shrink% ' and f.message like '% contraction% ' and f. Task_id=&task_idorder by f.impact DESC;---View recommendations results for automatic segment Advisor Select Tablespace_name, Segment_name, Segment_type, Partition_Name,recommendations, C1 fromtable (Dbms_space.asa_recommendations (' false ', ' false ', ' false ')); ******************** 5. Defrag Method ********************************************************************************------------------------- -----------------------*5.1 table Space defragmentation------------------------------------------------*alter tablespace users COALESCE;------------------------------------------------* 5.2 Table Defragmentation------------------------------------------------*---method 1:exo/imp or data pump---method of the pump technology 2:ctascreate table NewTable as SELECT * from Oldtable;drop table Oldtable;rename table newtable to oldtable;----Method 3:move tablespace technology Alter TA ble <table_name> Move tablespace <newtablespace_name>;----method 4:shrinkalter table <table_name> Enable  Row movement;  ALTER TABLE <table_name> shrink space cascade;  --Compress tables and related data segments and downgrade Hwmalter table <table_name> shrink Space Compact; --only compressed data does not downgrade HWM, does not affect DML operations ALTER TABLE <table_name> ShriNK Space; --Lowering the HWM, affecting the DML operation----method 5:online Redefinition--online redefinition has the application scenario: 1). Online table redefinition enables you to:2). Modify the storage parameters of a table or Cluster3). Move a table or cluster to a different tablespace4). ADD or Drop partitioning support (non-clustered tables only) 5). Change partition Structure6). Change physical properties of a single table partition, including moving it to a different tablespace in the same schema7) . Change physical properties of a materialized view log or an Oracle Streams Advanced queueing queue Table8). ADD support for parallel QUERIES9). Re-create a table or cluster to reduce FRAGMENTATION10). Convert a relational table into a table with an object columns, or do the reverse.11). Convert an object table to a relational table or a table with object columns, or do the reverse.---Finishing steps-Step 1: Check if the table has a primary key in Line online redifinition ability Begindbms_redefinition. Can_redef_table (' Scott ', ' T1 ', dbms_redefinition. CONS_USE_PK); end;/--Step 2: Create a new temporary table of the same structure create table SCOTT.TP1 tablespace Ocpyangasselect * from SCOTT.T1 where 1=2;--Step 3: Start online redifinitionbegindbms_redefinition. Start_redef_table (' Scott ', ' T1 ', ' TP1 ', ', dbms_redefinition.cons_use_pk '); end;/--Step 4:copy dependent objects. (Automatically create any triggers, indexes, materialized view logs, grants, and constraints on scott.tblorders.) Declarenum_errors Pls_integer; Begindbms_redefinition. Copy_table_dependents (' Scott ', ' T1 ', ' TP1 ', dbms_redefinition. Cons_orig_params, True, True, true, true, num_errors); end;/--Step 5: Check for errors other than primary, constraint select object_name, Base_table_name, Ddl_txt fromdba_redefinition_errors;-- Step 6:optionally, synchronize the interim table hr.int_admin_emp. BEGIN dbms_redefinition. Sync_interim_table (' Scott ', ' T1 ', ' TP1 '); end;/--step 7:complete the redefinition. Begindbms_redefinition. Finish_redef_table (' Scott ', ' T1 ', ' TP1 '); End;/note:the table Scott.tblorders is locked with the exclusive mode only for a small windows toward the end of this step. After the Table scott.tblorders is redefined such, it has all the attributes of the Scott.tptblorders table.------------------- -----------------------------*5.3 Index defragmentation------------------------------------------------*alter index <index_ name> Rebuild Online parallel 4 nologging;alter table <index_name> coalesce; Because rebuild index can be online, parallel, Do not generate log mode. Rebuild Index.********************************************************************************6 is recommended. Best practices ********************************************************************************1. Fragmentation of tables takes precedence over shrink technology The fragmentation of the index takes precedence over the rebuild index technology; 2. If the shrink is not ideal, use the online redefinition technology 3. If the space is not enough to cause rebuild Index cannot be implemented then consider coalesce Technology 4. Although shrink and rebuild The index does not affect the online application but the insurance is to avoid in the peak of business execution 5.shrink Technology consider compressing the data does not downgrade HWM, and then looking for business trough time to reduce HWM and free space 6. Recommendation rebuild Index is performed in a non-online manner, although online is supported.

Oracle Database Fragmentation Management