程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> PLSQL_性能優化系列14_Oracle High Water Level高水位分析,plsql14_oracle

PLSQL_性能優化系列14_Oracle High Water Level高水位分析,plsql14_oracle

編輯:Oracle教程

PLSQL_性能優化系列14_Oracle High Water Level高水位分析,plsql14_oracle


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




  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved