Oracle Database fragmentation management,

Oracle Database fragmentation management,

**************************************** **************************************** 1. tablespace fragments ************************************** **************************************** ** ---- 1. view the fsfi value select. tablespace_name, trunc (sqrt (max (blocks)/sum (blocks) * (100/sqrt (count (blocks), 2) fsfi from dba_free_space, dba_tablespaces bwhere. tablespace_name = B. tablespace_nameand B. contents not in ('temporary ', 'undo ', 'Sysaux') group by. tablespace_name order by fsfi; If FSFI is smaller than <30%, there are too many tablespace fragments. the maximum possible value of fsfi is 100 (an ideal single-file tablespace ). As the range increases, the fsfi value decreases slowly, while the maximum range size decreases, the fsfi value decreases rapidly. --- 2. Check that dba_free_spacedba_free_space displays the tablespace with free space. If the free space of a tablespace is not continuous, a record exists in dba_free_space for each free space segment. If a table space has several records, it indicates that there are fragments in the table space. When the number of tablespace fragments managed by the dictionary exceeds 500, the table space must be fragmented. Select. tablespace_name, count (1) fragmentation from dba_free_space a, dba_tablespaces B where. tablespace_name = B. tablespace_nameand B. contents not in ('temporary ', 'undo', 'sysaux ') group by. tablespace_namehaving count (1)> 20 order by 2; ----- 3. display consecutive idle space by tablespace ====== 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 will determine how many extents REM of contiguous free space you have in Oracle as well as the REM total amount of free space you have in each tablespace. from REM these results you can detect how fragmented Your tablespace is. REM The ideal situation is to have one large free extent in your REM tablespace. the more extents of free space there are in the REM tablespace, the more likely you will run into fragmentation REM problems. the size of the free extents is also very important. REM If you have a lot of small extents (too small for any next REM extent size) but the total bytes of free space is Large, then REM you may want to consider defragmentation options. REM ------------------------------------------------------------------------ rem disclaimer: REM This script. is provided for educational purposes only. it is not rem supported by Oracle World Wide Technical Support. REM The script. has been tested and appears to work as intended. REM You shoshould always run new scripts on a test ins Tance initially. REM ------------------------------------------------------------------------ REM Main text of script. follows: create table SPACE_TEMP (TABLESPACE_NAME CHAR (30), limit NUMBER)/declare cursor query is select * from dba_free_space order by tablespace_name, block_id; this_row query % rowtype; previus_row query % rowtype; total number; begin open query; fetch query into thi S_row; previus_row: = this_row; total: = bytes; loop fetch query into this_row; exit when query % notfound; if this_row.block_id = bytes + average then total: = total + this_row.bytes; insert into SPACE_TEMP (tablespace_name) values (previous_roviotablespace_name); else insert into SPACE_TEMP values (previous_row.tablespace_name, total); total: = this_row.byt Es; end if; previus_row: = this_row; end loop; insert into SPACE_TEMP values (previous_row.tablespace_name, total); end ;. /set pagesize 60 set newpage 0 set echo off ttitle center 'contiguous Extents report' skip 3 break on "tablespace 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" noprint new_value new_today format a1 rem select TABLESPACE_NAME "tablespace name", comment "contiguous bytes" from SPACE_TEMP where limit is not null order by TABLESPACE_NAME, limit desc; select tablespace_name, count (*) "# of extents", sum (contiguous_bytes) "total bytes" from space_temp group by tablespace_name; spool off drop table SPACE_TEM P /************************************** **************************************** ** 2. table fragment ************************************** **************************************** ** ---- Method 1: displays the 200 tables with the highest fragmentation rate (based on whether the statistics are accurate) col frag format 999999.99col owner format a30; col table_name format a30; select * from (select. owner,. table_name,. num_rows,. avg_row_len *. num_rows total_bytes, sum (B. bytes), trunc (. avg_row_len *. Num_rows)/sum (B. bytes), 2) * 100 | '%' fragfrom dba_tables a, dba_segments bwhere. table_name = B. segment_nameand. owner = B. ownerand. owner not in ('sys ', 'system', 'outln', 'dmsys ', 'tsmsys', 'dbsnmp ', 'wmsys', 'exfsys ', 'ctxsys ', 'xdb', 'olapsys ', 'ordsys', 'mdsys ', 'sysmance') group by. owner,. table_name,. avg_row_len,. num_rows having. avg_row_len *. num_rows/sum (B. bytes) <0.7 order by sum (B. bytes) desc) whe Re rownum <= 200; --- Method 2: -- Collect table statistics exec dbms_stats.gather_table_stats (ownname => 'Scott ', tabname => 'tblorders'); -- determine the degree of fragmentation SELECT table_name, trunc (ROUND (blocks * 8), 2)/1024, 2) "High water levelM", trunc (ROUND (num_rows * avg_row_len/), 2)/, 2) "Real used spaceM", trunc (ROUND (blocks * 10/100) * 8, 2)/, 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 the select id of an index with the index height of 2 and the index size exceeds 20 mb. tablespace_name, id. owner, id. index_name, id. blevel, sum (sg. bytes)/1024/1024, sg. bloc Ks, sg. extentsfrom dba_indexes id, dba_segments 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> = 2 group 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 (locking the table) analyze ind Ex index_name validate structure; select del_lf_rows * 100/decode (lf_rows, 20%, lf_rows) pct_deleted from index_stats; If pct_deleted>, the index fragmentation is serious. **************************************** **************************************** 4. automatic segment advisor ************************************* **************************************** * ** frequent insertion, update, and deletion operations on a data table may result in tablespace fragments. Oracle can execute Segment shrink on tables or indexes. This allows the segment free space to be used for other segments in the tablespace to improve DML performance. Call the Segment Advisor to perform a growth trend analysis on the specified segment to determine which Segment will benefit from the Segment shrink. Run the shrink operation. Segment Advisor recommends that you enable row movementsql> alter table scott. tblorders enable row movement; variable id number; begin declare name varchar2 (100); descr varchar2 (500); obj_id number; begin name: = 'Manual _ tblorders '; descr: = 'segment Advisor Example '; dbms_advisor.create_task (advisor_name => 'segment ad', 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); values (task_name => name, parameter => 'recommend _ all', value => 'true'); dbms_advisor.execute_task (name ); end;/--- Delete execution plans declare name varchar2 (100); begin name: = 'Manual _ tblorders '; DBMS_ADVISOR.DELETE_TASK (name); end; /--- manual execution plan declare name varchar2 (100); begin name: 'manual_tblorders'mongodbms_advisor.exe cute_task (name); end;/NOTE: if data already exists in the execution plan result, you cannot manually execute the task and delete it before executing it. --- check whether the manually created plan has been executed. select task_id, task_name, status, advisor_name, created from dba_advisor_taskswhere owner = 'sys 'and task_name = 'Manual _ tblorders' and advisor_name = 'segment ad'; select af. task_name, ao. attr2 segname, ao. attr3 partition, ao. type, af. message from dba_advisor_findings af, dba_advisor_objects ao where ao. task_id = af. task_id and ao. object_id = af. object_id and af. task_id = & task_id; ---- only query the select 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 '% shrink %' and f. task_id = & task_idorder by f. impact desc; --- view the recommendations result select tablespace_name, segment_name, segment_type, partition_name, recommendations, c1 fromtable (values ('false', 'false ', 'false ')); **************************************** **************************************** 5. fragment method ************************************* **************************************** * ** ------------------------------------------------ * 5.1 tablespace fragmentation ------------------------------------------------ * alter tablespace users coalesce; partition * 5.2 table fragmentation ------------------------------------------------ * --- Method 1: exo/imp or data pump Technology --- Method 2: CTAScreate table newtable as select * from oldtable; drop table oldtable; rename table newtable to oldtable; ---- method 3: move tablespace technology alter table <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 compress data without downgrading HWM, DML alter table <table_name> shrink space; -- reduces HWM and affects DML operations ---- Method 5: online redefinition -- online redefinition scenarios: 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 object columns, or do the reverse.11 ). convert an object table into a relational table or a table with object columns, or do the reverse. --- sorting steps -- Step 1: Check whether the table has the online redifinition capability by primary key BEGINDBMS_REDEFINITION.CAN_REDEF_TABLE ('Scott ', 't1', DBMS_REDEFINITION.CONS_USE_PK); END; // -- step 2: create table scott. tp1 tablespace ocpyangasselect * from scott. t1 where 1 = 2; -- Step 3: start ONLINE subscription ('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; values ('Scott ', 't1', 'tp1', DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, num_errors); END;/-- Step 5: check whether the select object_name, base_table_name, ddl_txt values are incorrect except primary and constraint; -- Step 6: Optionally, synchronize the interim table hr.int _ admin_emp.BEGIN values ('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 in the exclusive mode only for a small window toward the end of this step. after this call the table scott. tblorders is redefined such that it has all the attributes of the scott. tptblorders table. except * 5.3 index fragmentation sorting using * alter index <index_name> rebuild online parallel 4 nologging; alter table <index_name> coalesce; Because rebuild index can be performed online, in parallel, without generating logs. rebuild index is recommended. **************************************** **************************************** 6. best Practice ************************************** **************************************** ** 1. shrink technology is preferred for table fragmentation; rebuild index technology is preferred for index fragmentation; 2. if shrink is not ideal, the online redefinition technology is used. 3. if the space is insufficient and the rebuild index cannot be implemented, consider coalesce technology 4. although both shrink and rebuild index do not affect online applications, we recommend that you avoid running them during business peaks. shrink technology considers compressing data first without lowering the HWM, then seeking for a low business time, then lowering the HWM and releasing space 6. it is recommended that the rebuild index be executed in non-ONLINE mode, although online is supported.