HWM:
场景:
big table :select count(*) from table ; cost
delete from table ; select count(*) from table ; cost
truncate table ; select count(*) from table cost
SQL> create table ttt as select * from all_objects;
Table created.
SQL> select count(*) from ttt;
COUNT(*)
----------
54715
Execution Plan
----------------------------------------------------------
Plan hash value: 2073541258
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 101 (0)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TTT | 44229 | 101 (0)| 00:00:03 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
822 consistent gets
758 physical reads
0 redo size
413 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> delete from ttt;
54715 rows deleted.
Execution Plan
----------------------------------------------------------
Plan hash value: 1731860563
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 101 (0)| 00:00:03 |
| 1 | DELETE | TTT | | | |
| 2 | TABLE ACCESS FULL| TTT | 1 | 101 (0)| 00:00:03 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
72 recursive calls
61043 db block gets
843 consistent gets
1584 physical reads
20308908 redo size
672 bytes sent via SQL*Net to client
548 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
54715 rows processed
SQL> select count(*) from ttt;
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 2073541258
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 101 (0)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TTT | 1 | 101 (0)| 00:00:03 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
763 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> truncate table ttt;
Table truncated.
SQL> select count(*) from ttt;
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 2073541258
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TTT | 1 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
1 recursive calls
1 db block gets
6 consistent gets
0 physical reads
52 redo size
410 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
delete --->当删除了表的行记录后,大量数据库都是空块,可以在使用
但是hwm高水位线不调整,全表扫描比许扫描hwm以下的所有块,所以导致全表扫描消耗成本很高
当我们truncate table 后,高位位线归零,再次全表扫描,成本将下来了
delete ---->dml not hwm
truncate --->ddl hwm
理解高水位线 及模拟HWM
最新推荐文章于 2021-05-02 11:32:14 发布