2014-10-04 BaoXinjian
一、摘要
PLSQL_性能優化系列14_Oracle High Water Level高水位分析
高水位線好比水庫中儲水的水位線,用於描述數據庫中段的擴展方式。高水位線對全表掃描方式有著至關重要的影響。
當使用delete 操作表記錄時,高水位線並不會下降,隨之導致的是全表掃描的實際開銷並沒有任何減少。
本文給出高水位線的描述,如何降低高水位線,以及高水位線對全表掃描的影響。
1. 何謂高水位線
如前所述,類似於水庫中儲水的水位線。只不過在數據庫中用於描述段的擴展方式。
可以將數據段或索引段等想象為一個從左到右依次排開的一系列塊。當這些塊中未填充任何數據時,高水位線位於塊的最左端(底端)
隨著記錄的不斷增加,新塊不斷地被填充並使用,高水位線隨之向右移動。高水位線之上為未格式化的數據塊。
刪除(delete)操作之後,高水位線之下的塊處於空閒狀態,但高水位線並不隨之下降,直到重建,截斷或收縮表段。
全表掃描會掃描高水位線之下的所有塊,包括空閒數據塊(執行了delete操作)。
2. 低高水位線
是在使用ASSM時的一個概念。即使用ASSM時除了高水位線之外,還包括一個低高水位線。低高水位線一定是位於高水位線之下。
當段使用MSSM管理方式時只有一種情況即只存在一個高水位線。
使用MMSM時,當HWM升高時,Oracle立即格式化所有塊且有效,並可以安全讀取。僅當第一次使用時完成格式化,便於安全讀取數據。
使用ASSM時,當HWM升高時,Oracle並不會立即格式化所有塊。僅當第一次使用時完成格式化,便於安全讀取數據。
使用低高水位線可以減少當全面掃描表段時,低高水位線與高水位線之間不安全塊的檢查數量。即低高水位線之下的塊不再檢查。
二、案例 - Delete / SHRINK SPACE CASCADE / Truncate Table 對水位線的影響
1. 創建測試表和資料,並分析
Step1. 創建表
CREATE TABLE sh.bxj_high_water_level AS SELECT ROWNUM AS id, ROUND (DBMS_RANDOM.normal * 1000) AS val1, DBMS_RANDOM.string ('p', 250) AS pad FROM DUAL CONNECT BY LEVEL <= 10000;
Step2. 收集表的統計信息
BEGIN DBMS_STATS.gather_table_stats ('SH', 'BXJ_HIGH_WATER_LEVEL', cascade => TRUE); END; ANALYZE TABLE sh.bxj_high_water_level COMPUTE STATISTICS;
Step3. 表的統計信息和Block信息
SQL> select count(*) from sh.bxj_high_water_level; Execution Plan ---------------------------------------------------------- Plan hash value: 4214873579 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 107 (0)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| BXJ_HIGH_WATER_LEVEL | 10000 | 107 (0)| 00:00:02 | ----------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 375 consistent gets 0 physical reads 0 redo size 422 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
2. Delete Record 對水位線的影響
Step1. 刪除表中記錄
DELETE FROM sh.bxj_high_water_level WHERE ROWNUM <= 9900;
Step2. 收集表的統計信息
BEGIN DBMS_STATS.gather_table_stats ('SH', 'BXJ_HIGH_WATER_LEVEL', cascade => TRUE); END; ANALYZE TABLE sh.bxj_high_water_level COMPUTE STATISTICS;
Step3. 表的統計信息和Block信息
SQL> select count(*) from sh.bxj_high_water_level; Execution Plan ---------------------------------------------------------- Plan hash value: 4214873579 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 107 (0)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| BXJ_HIGH_WATER_LEVEL | 100 | 107 (0)| 00:00:02 | ----------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 375 consistent gets 0 physical reads 0 redo size 422 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
3. SHRINK SPACE CASCADE 對水位線的影響
Step1。合並控件
ALTER TABLE sh.bxj_high_water_level ENABLE ROW MOVEMENT; ALTER TABLE sh.bxj_high_water_level SHRINK SPACE CASCADE; ALTER TABLE sh.bxj_high_water_level DISABLE ROW MOVEMENT;
Step2. 收集表的統計信息
BEGIN DBMS_STATS.gather_table_stats ('SH', 'BXJ_HIGH_WATER_LEVEL', cascade => TRUE); END; ANALYZE TABLE sh.bxj_high_water_level COMPUTE STATISTICS;
Step3. 表的統計信息和Block信息
SQL> select count(*) from sh.bxj_high_water_level; Execution Plan ---------------------------------------------------------- Plan hash value: 4214873579 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| BXJ_HIGH_WATER_LEVEL | 100 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 422 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
4. Truncate Table 對水位線的影響
Step1. Truncate 表
TRUNCATE TABLE sh.bxj_high_water_level
Step2. 收集表的統計信息
BEGIN DBMS_STATS.gather_table_stats ('SH', 'BXJ_HIGH_WATER_LEVEL', cascade => TRUE); END; ANALYZE TABLE bxj_water_level COMPUTE STATISTICS;
Step3. 表的統計信息和Block信息
SQL> select count(*) from sh.bxj_high_water_level; Execution Plan ---------------------------------------------------------- Plan hash value: 4214873579 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| BXJ_HIGH_WATER_LEVEL | 1 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 421 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
六、案例 - 總結
(1). 高水線直接決定了全表掃描所需要的I/O開銷
(2). delete操作不會降低高水位線,高水位線之下的所有塊依然被掃描
(3). 使用truncate 會重置高水位線到0位
(4). 定期使用alter table tab_name shrink space cascade 有效減少該對象上的I/O開銷
參考:了沙彌 http://blog.csdn.net/leshami/article/details/6949179