Oralce HWM的1点分析

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

Oralce HWM的1点分析

相关推荐