oracle效能調整的九大要點：八提升block的效率

    1、避免動態分配的缺陷
    建立本地管理的資料表空間；
    合理設定segment的大小；
    監控將要擴充的segment：
    SELECT owner, table_name, blocks, empty_blocks FROM dba_tables WHERE empty_blocks / (blocks+empty_blocks) < .1;

    2、high water mark
    記錄在segment header block中，在segment建立的時候設定在segment的起始位置，當記錄被插入的時候以5個block的增量增加，truncate可以重設high water mark的位置，但delete不能。
    在full table scan中，oracle會讀取high water mark以下的所有的資料區塊，所以high water mark以上的塊也許會浪費儲存空間，但不會降低效能。

    可以通過下列方法收回表中high water mark以上的塊：
    Alter table_name deallocate unused；
    對於high water mark以下的塊：
    使用import/export工具：export資料；drop或truncate表；import資料。或者利用alter table tanle_name move命令去移動表的儲存位置（此時需要重建索引）。

    3、表統計
    用analyize命令產生表統計，然後到dba_table查詢相關資訊。
    ANALYZE TABLE ndls.t_wh_shipping_bill COMPUTE STATISTICS;
    SELECT num_rows, blocks, empty_blocks as empty,avg_space, chain_cnt, avg_row_len FROM dba_tables WHERE owner ='NDLS' AND table_name='T_WH_SHIPPING_BILL';

    Columns Description
    NUM_ROWS Number of rows in the table
    BLOCKS Number of blocks below the table high-water mark
    EMPTY_BLOCKS Number of blocks above the table high-water mark
    AVG_SPACE Average free space in bytes in the blocks below high-water mark
    AVG_ROW_LEN Average row length, including row overhead
    CHAIN_CNT Number of chained or migrated rows in the table

    4、block size
    通過下列方法可以最小化block的訪問次數：
    使用更大的block size；緊密壓縮行；阻止行鏡像。後兩者存在衝突，越多的行被壓縮在一個block裡，越容易產生鏡像。Block size 在資料庫建立的時候設定，不能被輕易改變，是讀取資料檔案時最小的IO單元，大小範圍是2K－64K，應該設定成OS塊的整數倍，小於或等於OS IO時能讀取的儲存地區。

    較小的block size的優點：極少block競爭；有利於較小的行和隨機訪問。缺點是存在相當高的成本，每個block的行數更少，可能需要讀取更多的index塊。Block size的選擇影響系統的效能，在一個OLTP環境中，較小的block size更合適，而在DSS環境中，適宜選擇較大的block size。

    5，PCTFREE,PCTUSED 

    1）PCTFREE、PCTUSED使你能控制一個segment裡所有資料區塊裡free space的使用。
    PCTFREE：一個資料區塊保留的用於塊裡已有記錄的可能更新的自由空間佔block size的最小比例。
    PCTUSED：在新記錄被插入block裡之前這個block可以用於儲存行資料和其他資訊的空間所佔的最小比率。

    2）這兩個參數的使用
    如果建立表的時候指定pctfree＝20％，oracle會在這個表的data segment的每個block都保留20％的空間用於已有記錄的更新。Block的已使用空間上升到整個block size的80％時，這個block將移出free list；在提交了delete、update之後，oracle server處理這條語句並檢查對應block的已使用空間是否低於PCTUSED，如果是，則這個block放進free list。

    3）PCTFREE、PCTUSED的設定
    • PCTFREE
    – Default 10
    – Zero if no UPDATE activity
    – PCTFREE = 100 × upd / (average row length)
    • PCTUSED
    – Default 40
    – Set if rows deleted
    – PCTUSED = 100 – PCTFREE – 100 × rows × (average row length) / blocksize
    其中，upd ： the average amount added by updates, in bytes。This is determined by subtracting the average row length of intercurrent average row length;
    average row length：在運行了analyize命令之後，這個值可以從dba_tables中的avg_row_len列中獲得。
    rows ： the number of rows to be deleted before free list maintenance occurs。

    4）Delete、update可以增加block的自由空間，但是釋放出來的空間有可能是不連續的，oracle在下列情況下會對片段進行整理：一個block有足夠的自由空間容納row piece，但是由於每個片段都較小以至這個row piece不能存放在一個連續的section中。

    6、Migration和Chaining

    1）如果一行的資料太大以至一個單獨的block容納不下，會產生兩種現象：
    A、Chaining：行資料太大以至一個空block容納不下，oracle會將這一行的資料存放在一個或多個block 組成的block chain中，insert、update都可能導致這個問題，在某些情況下row chaining是不能避免的。
    B、Migration：一次update操作可能導致行資料增大，以至它所在的block容納不下，oracle server會去尋找一個有足夠自由空間容納整行資料的block，如果這樣的block存在，oracle server把整行移到新的block，在原位置儲存一個指向新存放位置的鏡像行，鏡像行的rowid和原來的rowid一致。
    Chaining、Migration的弊端：insert、update的效能降低，索引查詢增加了IO次數。

    2）檢測migration和chaining：
    Analyize table table_name compute statistics；
    Select num_rows,chain_cnt from dba_tables where table_name=’...’;
    查詢鏡像行：
    Analyize table table_name list chained rows；
    Select owner_name,table_name,head_rowid from chained_rows where table_name=’...’;
    產生Migration的原因可能是由於PCTFREE設定的太低以至沒有保留足夠的空間用於更新。
    可以通過增加PCTFREE的值避免行鏡像產生。

    3）消除鏡像行的步驟：
    運行analyize table ... list chained rows;
    複製鏡像行到另一個表tmp；
    從源表中刪除這些行；
    從tmp中將這些行插回到源表中。
    指令碼：
    /* Get the name of the table with migrated rows */
    accept table_name prompt ’Enter the name of the table with migrated rows: ’
    /* Clean up from last execution */
    set echo off
    drop table migrated_rows;
    drop table chained_rows;
    /* Create the CHAINED_ROWS table */
    @?/rdbms/admin/utlchain
    set echo on
    spool fix_mig
    /* List the chained & migrated rows */
    analyze table &table_name list chained rows;
    /* Copy the chained/migrated rows to another table */
    create table migrated_rows as
    select orig.* from &table_name orig, chained_rows cr
    where orig.rowid = cr.head_rowid
    and cr.table_name = upper(’&table_name’);
    /* Delete the chained/migrated rows from the original table */
    delete from &table_name
    where rowid in ( select head_rowid from chained_rows );
    /* Copy the chained/migrated rows back into the original table */
    insert into &table_name select * from migrated_rows;
    spool off
    使用這個指令碼時，必須將涉及到的外鍵約束去掉。

    7、索引重組

    在一個不穩定的表上建索引會影響效能，一個索引block只有完全空時才能進入free list，即使一個索引block裡只含有一個條目，它也必須被維護，因此索引需要進行階段性的重建。

    1）檢查索引是否需要重組
    A、收集一個index的使用統計
    ANALYZE INDEX acct_no_idx VALIDATE STRUCTURE;
    B、查看收集的統計資料
    SELECT NAME,(DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage FROM index_stats;

    Column Description
    LF_ROWS Number of values currently in the index
    LF_ROWS_LEN Sum in bytes of the length of all values
    DEL_LF_ROWS Number of values deleted from the index
    DEL_LF_ROWS_LEN Length of all deleted values

    C、如果浪費超過20％則索引需要重建
    ALTER INDEX acct_no_idx REBUILD;
    D、或者對索引進行整理
    Alter index acct_no_idx coalesce;

    2）標記未使用的索引
    A、 開始監測索引的使用
    Alter index hr.emp_name_ix monitoring usage;
    B、 停止監測索引的使用
    Alter index hr.emp_name_ix nomonitoring usage;
    C、 查詢索引的使用方式
    Select index_name,used from v$object_usage;
    刪除未使用過的索引，可以降低DML操作的成本，從而提升系統效能。

    為了儘可能經濟的利用block，應對存在較多空block、鏡像行的表進行重建，對建立不穩定表上的索引應有規律的進行重建，並儘可能建立本地管理的資料表空間。