Oralce HWM的一点分析

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

 

 

---1.创建测试表
CREATE TABLE T1 TABLESPACE TEST AS SELECT * FROM DBA_OBJECTS;

--2 查询segment 头块
SQL> SELECT HEADER_FILE,HEADER_BLOCK FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'T1';

HEADER_FILE HEADER_BLOCK
----------- ------------
         22          795

--3 DUMP segment 头块

ALTER SYSTEM DUMP DATAFILE 22 BLOCK 795;

--4 查看结尾为2492的trace文件 (ORAHOME\product\10.2.0\admin\orcl\udump)

SELECT SPID
  FROM V$PROCESS
 WHERE ADDR = (SELECT PADDR
                 FROM V$SESSION
                WHERE SID = (SELECT DISTINCT SID FROM V$MYSTAT));
SPID
------------
2492

--HWM 为 0x058008fb
Highwater::  0x058008fb   

--5 计算HWM 对应的文件编号和块
SQL> SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(TO_NUMBER('058008FB','XXXXXXXX')) AS HWM_FILEID FROM DUAL;

HWM_FILEID
----------
        22

SQL> SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(TO_NUMBER('058008FB','XXXXXXXX')) AS HWM_BLOCKID FROM DUAL;

HWM_BLOCKID
-----------
       2299
       
--6. 分析表，查看sgemnt空块的数及最大使用的Block ID
SQL> SELECT FILE_ID,MIN(BLOCK_ID),MAX(BLOCK_ID+BLOCKS) FROM DBA_EXTENTS WHERE SEGMENT_NAME='T1' GROUP BY FILE_ID;

   FILE_ID MIN(BLOCK_ID) MAX(BLOCK_ID+BLOCKS)
---------- ------------- --------------------
        22           793                 2313
        
ANALYZE TABLE T1 COMPUTE STATISTICS；

SQL> SELECT EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME = 'T1';

EMPTY_BLOCKS
------------
          14
SQL> SELECT 2313 -14 FROM DUAL;--等于HWM_BLOCKID

   2313-14
----------
      2299
--7.记录下delete数据前count(*)的统计信息
SQL> set autotrace traceonly;
SQL> select count(*) from T1;


执行计划
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   169   (2)| 00:00:03 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 53176 |   169   (2)| 00:00:03 |
-------------------------------------------------------------------


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        739  consistent gets
          0  physical reads
          0  redo size
        410  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

--8 删除数据
SQL> set autotrace off;
SQL> select count(*) from T1;

  COUNT(*)
----------
     53176
     
SQL> delete T1 where rownum <= 30000;

已删除30000行。

SQL> COMMIT;

提交完成。

--9 重新dump 数据头,发现Highwater仍然是原来的值。说明DELETE 并不能使HWM下降。
ALTER SYSTEM DUMP DATAFILE 22 BLOCK 795;

Highwater::  0x058008fb

--10. 分析删除数据后，count(*)的统计信息，发现执行的消耗与删除前一样。739个逻辑读
SQL> set autotrace on;
SQL> select count(*) from T1;

  COUNT(*)
----------
     23176


执行计划
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   169   (2)| 00:00:03 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 23176 |   169   (2)| 00:00:03 |
-------------------------------------------------------------------


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        739  consistent gets
          0  physical reads
          0  redo size
        410  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed