最近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;