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