oracle 資料庫片段化管理，

oracle 資料庫片段化管理，

********************************************************************************1.資料表空間片段********************************************************************************----1.查看fsfi值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 not in('TEMPORARY','UNDO','SYSAUX')group by A.tablespace_name order by fsfi; 如果FSFI小於<30%則資料表空間片段太多.fsfi的最大可能值為100（一個理想的單檔案資料表空間）。隨著範圍的增加，fsfi值緩慢下降，而隨著最大範圍尺寸的減少，fsfi值會迅速下降。---2.查看dba_free_spacedba_free_space 顯示的是有free 空間的tablespace ，如果一個tablespace 的free 空間不連續，那每段free空間都會在dba_free_space中存在一條記錄。如果一個tablespace 有好幾條記錄，說明資料表空間存在片段，當採用字典管理的資料表空間片段超過500就需要對錶空間進行磁碟重組。select a.tablespace_name ,count(1) 片段量 from dba_free_space a, dba_tablespaces b where a.tablespace_name =b.tablespace_nameand b.contents not in('TEMPORARY','UNDO','SYSAUX')group by a.tablespace_namehaving count(1) >20order by 2;-----3.按照資料表空間顯示連續的空閑空間========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   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 should always run new scripts on a test instance initially. REM ------------------------------------------------------------------------ REM Main text of script. follows:  create table SPACE_TEMP (    TABLESPACE_NAME        CHAR(30),    CONTIGUOUS_BYTES       NUMBER)   /      declare     cursor query is 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 query into this_row;        exit when query%notfound;        if this_row.block_id = previous_row.block_id + previous_row.blocks then           total := total + 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 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",          CONTIGUOUS_BYTES "CONTIGUOUS BYTES"   from SPACE_TEMP   where CONTIGUOUS_BYTES is not null   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.表片段********************************************************************************----方法1：顯示片段率最高的200個表(基於統計資訊是否準確)col frag format 999999.99col owner format a30;col table_name format a30;select * from (select a.owner,a.table_name,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    having a.avg_row_len*a.num_rows/sum(b.bytes)<0.7    order by sum(b.bytes) desc)  where rownum<=200;---方法2：-- 收集表統計資訊exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=> 'TBLORDERS');-- 確定片段程度SELECT table_name, trunc(ROUND ((blocks * 8), 2)/1024,2) "High water levelM",   trunc(ROUND ((num_rows * avg_row_len / 1024), 2)/1024,2) "Real  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.索引片段********************************************************************************---1..查看索引高度為2並且索引大小超過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_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>=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方法(會鎖表)analyze index index_name validate structure;select del_lf_rows*100/decode(lf_rows,0,1,lf_rows) pct_deleted from index_stats;如果pct_deleted>20%說明索引片段嚴重.********************************************************************************4.automatic segment advisor ********************************************************************************資料表上頻繁的進行插入、更新和刪除動作會產生資料表空間片段。Oracle可在表或索引上執行Segment shrink。使得segment的空閑空間可用於資料表空間中的其它segment，可改善DML效能。調用Segment Advisor對指定segment執行增長趨勢分析以確定哪些Segment受益於Segment shrink。執行shrink操作，Segment Advisor推薦啟用表的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 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; /---刪除執行計畫declare name varchar2(100);begin  name:='Manual_tblorders'; DBMS_ADVISOR.DELETE_TASK (name); end; /---手動執行計畫declare name varchar2(100);begin  name:='Manual_tblorders';dbms_advisor.execute_task(name); end; /NOTE:如果執行計畫結果中已經有資料則不能直接手動執行需要刪除再執行---查看手動建立的計劃是否已經執行完成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_objects ao  where ao.task_id = af.task_id  and ao.object_id = af.object_id  and af.task_id=&task_id;----只查詢可以進行shrink操作的對象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 '%收縮%'and f.task_id=&task_idorder by f.impact desc;---查看automatic segment advisor的recommendations結果select tablespace_name, segment_name, segment_type, partition_name,recommendations, c1 fromtable(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));********************************************************************************5. 磁碟重組方法********************************************************************************------------------------------------------------*5.1資料表空間磁碟重組------------------------------------------------*alter tablespace users coalesce;------------------------------------------------*5.2表磁碟重組------------------------------------------------*---方法1:exo/imp或data pump資料泵技術---方法2:CTAScreate table newtable as select * from oldtable;drop table oldtable;rename table newtable to oldtable;----方法3:move tablespace技術alter table <table_name> move tablespace <newtablespace_name>;----方法4:shrinkalter table <table_name> enable row movement;  alter table <table_name> shrink space cascade;  --壓縮表以及相關資料區段並下調HWMalter table <table_name> shrink space compact;  --只壓縮資料不下調HWM,不影響DML操作alter table <table_name> shrink   space;  --下調HWM,影響DML操作----方法5:online redefinition--online redefinition具有的應用情境: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.---整理步驟--步驟1：檢測表是否具有按主鍵進行ONLINE REDIFINITION能力BEGINDBMS_REDEFINITION.CAN_REDEF_TABLE('scott','t1',      DBMS_REDEFINITION.CONS_USE_PK);END;/--步驟2：建立一張同結構的暫存資料表create table scott.tp1 tablespace ocpyangasselect * from scott.t1 where 1=2;--步驟3:啟動ONLINE REDIFINITIONBEGINDBMS_REDEFINITION.START_REDEF_TABLE('scott', 't1','tp1',       '',       dbms_redefinition.cons_use_pk);END;/--步驟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;/--步驟5:檢查是否除primary、constraint之外的錯誤select object_name, base_table_name, ddl_txt fromDBA_REDEFINITION_ERRORS;--步驟6：Optionally, synchronize the interim table hr.int_admin_emp.BEGIN DBMS_REDEFINITION.SYNC_INTERIM_TABLE('scott', 't1', 'tp1');END;/--步驟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.------------------------------------------------*5.3 索引磁碟重組------------------------------------------------*alter index <index_name> rebuild online parallel 4 nologging;alter table <index_name> coalesce;由於rebuild index可以線上、並行、不產生日誌方式進行.推薦使用rebuild index.********************************************************************************6.最佳實務********************************************************************************1.針對錶的片段化優先考慮shrink技術；針對索引的片段優先考慮rebuild index技術；2.如果shrink不理想則採用online redefinition技術3.如果空間不夠導致rebuild index無法實施則考慮coalesce技術4.雖然shrink和rebuild index都不影響線上應用但保險起見盡量避免在業務高峰執行5.shrink技術考慮先壓縮資料不下調HWM，然後找業務低穀時間再下調HWM並釋放空間6.建議rebuild index以非ONLINE方式執行雖然支援online.