Plsql_performance optimization series 14_Oracle High Water Level High Level analysis, plsql14_oracle

Plsql_performance optimization series 14_Oracle High Water Level High Level analysis, plsql14_oracle

2014-10-04 BaoXinjian

I. Summary

PLSQL _ performance optimization series 14_Oracle High Water Level analysis

A high water level line is like a water level line for storing water in a reservoir. It is used to describe the expansion mode of the middle section of the database. The high water level line has a crucial impact on the Full table scan mode.

When you use delete to operate a table record, the high watermark line does not drop, resulting in the actual overhead of full table scanning.

This article describes how to reduce the impact of a high water level line on a full table scan.

 

1. What is high water level line?

As mentioned above, it is similar to the water level line in the reservoir. It is only used in the database to describe how segments are extended.

You can think of data segments or index segments as a series of blocks arranged from left to right. When no data is filled in these blocks, the high water level line is located at the leftmost (bottom) end of the block)

As the number of records increases, new blocks are constantly filled and used, and the high water level line moves to the right. Unformatted data blocks are placed above the high watermark line.

After the delete operation, the blocks under the high watermark are idle, but the high watermark does not drop until the table segment is rebuilt, truncated or shrunk.

The full table scan scans all the blocks under the high water level, including idle data blocks (performing the delete operation ).

2. Low-height waterline

Is a concept when using ASSM. That is, when using ASSM, in addition to the high-water line, there is also a low-water line. The low and high water level line must be located under the high water level line.

When a segment is managed using MSSM, there is only one high-water line.

When MMSM is used, when HWM is increased, Oracle immediately formats all the blocks and reads them safely. The data is formatted only when it is used for the first time, making it easy to read data safely.

When ASSM is used, Oracle does not immediately format all blocks when HWM is increased. The data is formatted only when it is used for the first time, making it easy to read data safely.

The use of low-height waterline can reduce the number of insecure block checks between the low-height waterline and the high waterline when the table segment is fully scanned. That is, the blocks under the low-height waterline are not checked.

 

Ii. Case study-Impact of Delete/shrink space cascade/Truncate Table on water level line

1. Create and analyze test tables and Materials

Step 1. Create a table

CREATE TABLE sh.bxj_high_water_levelAS       SELECT   ROWNUM AS id,                ROUND (DBMS_RANDOM.normal * 1000) AS val1,                DBMS_RANDOM.string ('p', 250) AS pad         FROM   DUAL   CONNECT BY   LEVEL <= 10000;

Step 2. Collect table statistics

BEGIN   DBMS_STATS.gather_table_stats ('SH',                                  'BXJ_HIGH_WATER_LEVEL',                                  cascade   => TRUE);END; ANALYZE TABLE sh.bxj_high_water_level COMPUTE STATISTICS;

Step3. table statistics and Block information

 

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. Influence of Delete Record on waterline

Step 1. Delete A Table Record

DELETE FROM sh.bxj_high_water_level WHERE ROWNUM <= 9900;

Step 2. Collect table statistics

BEGIN   DBMS_STATS.gather_table_stats ('SH',                                  'BXJ_HIGH_WATER_LEVEL',                                  cascade   => TRUE);END; ANALYZE TABLE sh.bxj_high_water_level COMPUTE STATISTICS;

Step3. table statistics and Block information

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 CASCADEInfluence on water level line

Step 1. Merge controls

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;  

Step 2. Collect table statistics

BEGIN   DBMS_STATS.gather_table_stats ('SH',                                  'BXJ_HIGH_WATER_LEVEL',                                  cascade   => TRUE);END; ANALYZE TABLE sh.bxj_high_water_level COMPUTE STATISTICS; 

Step3. table statistics and Block information

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. Influence of Truncate Table on water level line

Step1. Truncate table

TRUNCATE TABLE sh.bxj_high_water_level

Step 2. Collect table statistics

BEGIN   DBMS_STATS.gather_table_stats ('SH',                                  'BXJ_HIGH_WATER_LEVEL',                                  cascade   => TRUE);END; ANALYZE TABLE bxj_water_level COMPUTE STATISTICS;

Step3. table statistics and Block information

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

 

Vi. Case study-Summary

(1). The high waterline directly determines the I/O overhead required for full table scan.

(2) The delete operation does not reduce the high-water line, and all blocks under the high-water line are still scanned.

(3). Using truncate will reset the high water level line to 0

(4). Regular use of alter table tab_name shrink space cascade to effectively reduce I/O overhead on this object

 

 

Reference: Amy http://blog.csdn.net/leshami/article/details/6949179