eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« Max Extents越界导致故障的Oracle数据库恢复 | Blog首页 | 2011年4月 ACOUG Exadata技术交流会 »

DELETE、TRUNCATE与高水位线(HWM)

我们知道,当通过DELETE方式删除表数据时,表段的HWM不会降低,Truncate缺省的会回收空间,降低HWM。

以下是一则案例,用户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

供参考。


历史上的今天...
    >> 2012-04-21文章:
    >> 2010-04-21文章:
    >> 2009-04-21文章:
    >> 2008-04-21文章:
           Resin、Apache、PHP与404错误
           美丽的京剧 美丽的晋剧
    >> 2007-04-21文章:
           在Oracle University大学演讲
    >> 2006-04-21文章:
           李白手书真迹-上阳台
    >> 2005-04-21文章:
           Control SCN of Undo Segments
           升级MT到3.1.6版本

无觅

By eygle on 2011-04-21 15:30 | Comments (2) | FAQ | 2787 |

2 Comments

把show_space脚本贴一下啊!

这个脚本是TOM的脚本,随便一搜就有啊


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com