Oracle案例10——HWM（高水位線）效能優化

Rangle發表於2018-07-03
Oracle優化

最近BI同事反饋說一張表的資料查詢非常慢，這個表資料總共不到1W行資料，這麼一說我們首先想到的是高水位帶來的效能問題，即高水位線下佔用過多資料塊，而這些資料塊其實是部分資料佔用，大多數是空閒的資料塊。

我們知道高水位線下的資料塊在全表掃描時都要做，所以掃描的資料塊可能遠遠多於實際的存資料的資料塊。

一、表統計資訊收集

要想得到準確的高水位資訊，必須先收集統計資訊，這樣得到的才相對比較準確。

ANALYZE TABLE  table_name ESTIMATE STATISTICS;

ANALYZE TABLE  table_name  COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;

execute dbms_stats.gather_table_stats(ownname => 'OWNER', tabname => 'TABLE_NAME' , estimate_percent => null ,method_opt => 'for all indexed columns' ,cascade => true);

二、表資訊檢視

檢視錶的塊、行資訊

select t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.empty_blocks,t.LAST_ANALYZED from dba_tables t where table_name in ('TABLE_NAME');

SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) USED_BLOCK FROM  TABLE_NAME;

上述查詢結果顯示，當前錶行數是9651行，有716119個資料塊被使用（HWM下的資料塊），有0個未使用的資料塊（HWM上的資料塊）

實際資料佔用的資料塊數量為：152

綜合可以看出，高水位線下其實有716119-152個資料塊可以釋放，這樣每次全表掃描只需要掃描152個資料塊即可。

通過檢視段大小佐證記錄數和表大小關係是否一致，通過下面的檢視段大小為5.5G，記錄9651行幾乎不可能達到這個大小，所以基本可以斷定個裡面有很多空閒的塊。

select segment_name,bytes/1024/1024/1024 TSize_GB from dba_segments where segment_name='table_name' ---5876219904

三、問題原因

什麼情況會導致上面的問題呢，即高水位下存在很多未使用的資料塊？一般是大表（插入很多記錄後），經過批量刪除delete操作，未釋放高水位導致的。

1.全表掃描要讀取高水位線下的所有資料塊，無論是否含有資料。
2.如果在插入資料的時候使用了append關鍵字，即使高水位線下有空閒的資料庫，也會從高水位線上面的資料庫做分配，也就是高水位線會上升。

四、降低高水位方法

1. alter table table_name move;
此方法可釋放高水位，但需要重建索引
2.alter table table_name shrink space;
此方法可釋放高水位，但執行前需要開啟行移動，alter table table_name enable row movement;
3.emp/imp的方式重建表資料
4.drop/create方式重建表
5.truncate表
6.alter   table  table_name  deallocate   unused  
DEALLOCATE UNUSED為釋放HWM上面的未使用空間,但是並不會釋放HWM下面的自由空間,也不會移動HWM的位置.
五、高水位調整實施
1.統計資訊收集（如上）
2.執行計劃檢視

SQL> set autotrace trace ;
SQL> set timing on;
SQL> SELECT count(*) FROM TABLE_NAME;

3.表移動

alter table table_name move;

報錯：ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
檢視被鎖物件：
select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid;

執行後再檢視執行計劃統計資訊

看到統計資訊訪問的資料塊已經降下來了，然後執行全表掃描，速度也是飛快。

4.索引重建

alter index  index_name  rebuild  online;

六、庫高水位物件統計

①比較表的行數和表的大小關係。如果行數為0，而表的當前佔用大小減去初始化時的大小（INITIAL_EXTENT）後依然很大，那麼說明該表有高水位。

②行數和塊數的比率，即檢視一個塊可以儲存多少行資料。如果一個塊儲存的行數少於5行甚至更少，那麼說明有高水位。注意，這兩種方法都不是十分準確，需要再對查詢結果進行篩選。需要注意的是，在查詢表的高水位時，首先需要分析表，以得到最準確的統計資訊。

SELECT D.OWNER,
       ROUND(D.NUM_ROWS / D.BLOCKS, 2),
       D.NUM_ROWS,
       D.BLOCKS,
       D.TABLE_NAME,
 ROUND((d.BLOCKS*8-D.INITIAL_EXTENT/1024)/1024)  t_size
  FROM DBA_TABLES D
 WHERE D.BLOCKS > 10
   AND ROUND(D.NUM_ROWS / D.BLOCKS, 2) < 5
 AND d.OWNER NOT LIKE '%SYS%' ;
或：
SELECT OWNER,
       SEGMENT_NAME TABLE_NAME,
       SEGMENT_TYPE,
       GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0) /
                      GREATEST(NVL(HWM, 1), 1)),
                      2),
                0) WASTE_PER
  FROM (SELECT A.OWNER OWNER,
               A.SEGMENT_NAME,
               A.SEGMENT_TYPE,
               B.LAST_ANALYZED,
               A.BYTES,
               B.NUM_ROWS,
               A.BLOCKS BLOCKS,
               B.EMPTY_BLOCKS EMPTY_BLOCKS,
               A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
               DECODE(ROUND((B.AVG_ROW_LEN * NUM_ROWS *
                            (1 + (PCT_FREE / 100))) / C.BLOCKSIZE,
                            0),
                      0,
                      1,
                      ROUND((B.AVG_ROW_LEN * NUM_ROWS *
                            (1 + (PCT_FREE / 100))) / C.BLOCKSIZE,
                            0)) + 2 AVG_USED_BLOCKS,
               ROUND(100 *
                     (NVL(B.CHAIN_CNT, 0) / GREATEST(NVL(B.NUM_ROWS, 1), 1)),
                     2) CHAIN_PER,
               B.TABLESPACE_NAME O_TABLESPACE_NAME
          FROM SYS.DBA_SEGMENTS A, SYS.DBA_TABLES B, SYS.TS$ C
         WHERE A.OWNER = B.OWNER
           AND SEGMENT_NAME = TABLE_NAME
           AND SEGMENT_TYPE = 'TABLE'
           AND B.TABLESPACE_NAME = C.NAME)
 WHERE GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0) /
                      GREATEST(NVL(HWM, 1), 1)),
                      2),
                0) > 50
   AND OWNER NOT LIKE '%SYS%'
   AND BLOCKS > 100
 ORDER BY WASTE_PER DESC;

 

相關文章