« Max Extents越界导致故障的Oracle数据库恢复 | Blog首页 | 2011年4月 ACOUG Exadata技术交流会 »
DELETE、TRUNCATE与高水位线(HWM)
作者:eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接:https://www.eygle.com/archives/2011/04/delete_truncate_hwm.html
我们知道,当通过DELETE方式删除表数据时,表段的HWM不会降低,Truncate缺省的会回收空间,降低HWM。链接:https://www.eygle.com/archives/2011/04/delete_truncate_hwm.html
以下是一则案例,用户Delete了表数据,通过show_space来查看空间,发现空间大部分已经是空闲的,都位于75%~100%的范围:
SQL> exec show_space('RSBERRORLOG','SAPSR3');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 1,667,598
Full Blocks ..................... 1
Total Blocks............................ 1,675,904
Total Bytes............................. 13,729,005,568
Total MBytes............................ 13,093
Unused Blocks........................... 6,144
Unused Bytes............................ 50,331,648
Last Used Ext FileId.................... 54
Last Used Ext BlockId................... 924,425
Last Used Block......................... 2,048
但是查询时可以发现,需要全索引扫描,消耗大量的逻辑读,返回结果为0:
SQL> set autotrace on
SQL> set timing on
SQL> set linesize 120
SQL> select count(*) from sapsr3.RSBERRORLOG;
COUNT(*)
----------
0
Elapsed: 00:00:17.41
Execution Plan
----------------------------------------------------------
Plan hash value: 3944197743
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97583 (1)| 00:19:32 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| RSBERRORLOG~001 | 68M| 97583 (1)| 00:19:32 |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
418847 consistent gets
33439 physical reads
0 redo size
514 bytes sent via SQL*Net to client
463 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
将表TRUNCATE之后,可以发现查询的逻辑读降低到3,表和索引的HWM降低,空间释放出来:
SQL> truncate table sapsr3.RSBERRORLOG;
Table truncated.
Elapsed: 00:00:06.14
SQL> set autotrace on
SQL> select count(*) from sapsr3.RSBERRORLOG;
COUNT(*)
----------
0
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3944197743
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97583 (1)| 00:19:32 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| RSBERRORLOG~001 | 68M| 97583 (1)| 00:19:32 |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
514 bytes sent via SQL*Net to client
463 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
但是注意以上执行计划评估索引ROWS仍然有68M行,这是由于统计信息没有更新的缘故。
对表收集统计信息,再次查询,记录ROWS显示正常:
SQL> exec dbms_stats.gather_table_stats('SAPSR3','RSBERRORLOG');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.17
SQL> select count(*) from sapsr3.RSBERRORLOG;
COUNT(*)
----------
0
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3213820690
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| RSBERRORLOG~0 | 1 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
514 bytes sent via SQL*Net to client
463 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SHOW_SPACE显示空间使用情况:
SQL> exec show_space('RSBERRORLOG','SAPSR3');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 0
Total Blocks............................ 8
Total Bytes............................. 65,536
Total MBytes............................ 0
Unused Blocks........................... 5
Unused Bytes............................ 40,960
Last Used Ext FileId.................... 5
Last Used Ext BlockId................... 97,761
Last Used Block......................... 3
供参考。
历史上的今天...
>> 2020-04-21文章:
>> 2012-04-21文章:
>> 2010-04-21文章:
>> 2009-04-21文章:
>> 2008-04-21文章:
>> 2007-04-21文章:
>> 2006-04-21文章:
>> 2005-04-21文章:
By eygle on 2011-04-21 15:30 | Comments (2) | FAQ | 2787 |
把show_space脚本贴一下啊!
这个脚本是TOM的脚本,随便一搜就有啊