Oracle 高水位测试(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