High Water Level line and full table Scan

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 the delete operation is used
When a table is recorded, the high watermark line does not drop, resulting in the actual overhead of full table scan not decreasing. This article describes how to reduce the high water level line and the high water level line.
The effect of location line on full table scan.

 

I. 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 ).

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. high water level line and full table Scan

SQL> Create Table t --> Create test table <br/> 2 as <br/> 3 select rownum as ID, <br/> 4 round (dbms_random.normal * 1000) as val1, <br/> 5 dbms_random.string ('P', 250) as pad <br/> 6 from dual <br/> 7 connect by level <= 10000; </P> <p> table created. </P> <p> SQL> exec dbms_stats.gather_table_stats ('Scott ', 't', cascade => true ); --> collect statistical information </P> <p> SQL> @ tab_stat --> obtain the statistical information of table objects from dba_tab_statistics, no empty_blocks Information <br/> enter value for input_table_name: T <br/> enter value for input_owner: scott </P> <p> num_rows blks em_blks avg_space chain_cnt ← sta <br/> ---------- ----------- then --------- <br/> 10000 387 0 0 0 259 26 03-nov-11 no </P> <p> /***************************** * *******************/<br/>/* Author: robinson Cheng */<br/>/* blog: http://blog.csdn.net/robinson_0612 */<br/>/* MSN: robinson_0612@hotmail.com */<br/>/* QQ: 645746311 */<br/> /******************************** * *****************/</P> <p> SQL> analyze table t compute statistics; --> execute analyze </P> <p> SQL> @ tab_stat --> the empty_blocks value is 125 <br/> enter value for input_table_name: T <br/> enter value for input_owner: scott </P> <p> num_rows blks em_blks avg_space chain_cnt ← sta <br/> ---------- ----------- then --------- <br/> 10000 387 125 920 0 262 26 03-nov-11 no </P> <p> SQL> Col segment_name format A15 <br/> SQL> select segment_name, segment_type, blocks, extents from dba_segments --> View the block information on the table segment <br/> 2 where segment_name = 'T' and owner = 'Scott '; </P> <p> segment_name segment_type blocks extents --> The number of blocks recorded in this data dictionary is 512 (including used and idle blocks) <br/> --------------- ------------------ ---------- <br/> T table 512 19 </P> <p> SQL> set autotrace traceonly; --> enable autotrace <br/> SQL> select count (*) from t; --> the execution plan of the SQL statement is full table scan (partial information in the execution plan is omitted) </P> <p> execution plan <br/> ---------------------------------------------------------- <br/> plan hash value: 2966233522 </P> <p> ----------------------------------------------------------------- <br/> | ID | operation | Name | rows | cost (% CPU) | time | <br/> ----------------------------------------------------------------- <br/> | 0 | SELECT statement | 1 | 86 (0) | 00:00:02 | <br/> | 1 | sort aggregate | 1 | <br/> | 2 | table access full | T | 10000 | 86 (0) | 00:00:02 | <br/> Statistics </P> <p> Statistics <br/> 1 recursive CILS <br/> 0 dB block gets <br/> 375 consistent gets --> the value of consistent gets is 375 <br/> 0 physical reads </P> <p> SQL> set autotrace off; <br/> SQL> Delete from t where rownum <= 9900; --> delete most records. After deletion, the remaining record values are 100 </P> <p> 9900 rows deleted. <br/> SQL> commit; </P> <p> SQL> exec dbms_stats.gather_table_stats ('Scott ', 't', cascade => true ); --> collect statistics </P> <p> SQL> analyze table t compute statistics; --> collect statistics </P> <p> SQL> @ tab_stat --> at this time, the statistics on the object do not change, that is, the height line has not changed. <br/> enter value for input_table_name: T <br/> enter value for input_owner: scott </P> <p> num_rows blks em_blks avg_space chain_cnt ← sta <br/> ---------- ----------- then --------- <br/> 100 387 125 7921 0 262 0 03-nov-11 no </P> <p> SQL> set autotrace traceonly <br/> SQL> select count (*) from t; --> The estimated SQL Execution Plan value is accurate, which is 100 rows </P> <p> execution plan <br/> ------------------------------------------------------ <br/> plan hash value: 2966233522 </P> <p> ----------------------------------------------------------------- <br/> | ID | operation | Name | rows | cost (% CPU) | time | <br/> ----------------------------------------------------------------- <br/> | 0 | SELECT statement | 1 | 86 (0) | 00:00:02 | <br/> | 1 | sort aggregate | 1 | <br/> | 2 | table access full | T | 100 | 86 (0) | 00:00:02 | <br/> Statistics </P> <p> Statistics <br/> 1 recursive CILS <br/> 0 dB block gets <br/> 375 consistent gets --> the value of consistent gets is still 375, <br/> 0 physical reads </P> <p> SQL> set autotrace off; <br/> SQL> ALTER TABLE t enable row movement; --> enable row movement </P> <p> SQL> ALTER TABLE t shrink space cascade; --> implement shrink space </P> <p> SQL> alter table t disable row movement; </P> <p> SQL> exec dbms_stats.gather_table_stats ('Scott ', 'T'); </P> <p> SQL> analyze table t compute statistics; </P> <p> SQL> @ tab_stat --> at this time, the statistical information on the object has changed, and four blocks are used, four idle blocks <br/> enter value for input_table_name: T <br/> enter value for input_owner: scott <br/> num_rows blks em_blks avg_space chain_cnt contains zookeeper sta <br/> ---------- ----------- --------- --- <br/> 100 4 7921 0 259 25 No </P> <p> SQL> set autotrace traceonly <br/> SQL> select count (*) from t; </P> <p> execution plan <br/> ---------------------------------------------------------- <br/> plan hash value: 2966233522 </P> <p> ----------------------------------------------------------------- <br/> | ID | operation | Name | rows | cost (% CPU) | time | <br/> ----------------------------------------------------------------- <br/> | 0 | SELECT statement | 1 | 3 (0) | 00:00:01 | <br/> | 1 | sort aggregate | 1 | <br/> | 2 | table access full | T | 100 | 3 (0) | 00:00:01 | <br/> Statistics </P> <p> Statistics <br/> 1 recursive CILS <br/> 0 dB block gets <br/> 6 consistent gets --> after the table segment is reduced, consistent gets dropped from 375 to 6 <br/> 0 physical reads </P> <p> SQL> truncate table t; --> Use table truncation technology (turncate table) </P> <p> table truncated. </P> <p> SQL> exec dbms_stats.gather_table_stats ('Scott ', 'T'); --> collect statistics </P> <p> PL/SQL procedure successfully completed. </P> <p> SQL> select count (*) from T; --> in this case, the rows in the execution plan change to 1 </P> <p> execution plan <br/> -------------------------------------------- <br/> plan hash value: 2966233522 </P> <p> ----------------------------------------------------------------- <br/> | ID | operation | Name | rows | cost (% CPU) | time | <br/> ----------------------------------------------------------------- <br/> | 0 | SELECT statement | 1 | 2 (0) | 00:00:01 | <br/> | 1 | sort aggregate | 1 | <br/> | 2 | table access full | T | 1 | 2 (0) | 00:00:01 | <br/> Statistics </P> <p> Statistics <br/> 1 recursive CILS <br/> 0 dB block gets <br/> 3 consistent gets --> the value of consistent gets is reduced to 3 <br/> 0 physical reads </P> <p>

Iii. Summary
1. The high waterline directly determines the I/O overhead required for full table scan.
2. The delete operation will not reduce the high watermark line, and all blocks under the high watermark line will still be scanned.
3. Using truncate will reset the high water level line to 0
4. Regular use of alter table tab_name shrink space cascade effectively reduces I/O overhead on this object

Iv. Extension reference
Shrink table segments (shrink space)
Use of the display_cursor function of dbms_xplan
Use of the display function of dbms_xplan
Description of each field module in the execution plan
Oracle variable binding
Oracle adaptive shared cursor
Oracle rowid