关于HWM的一些测试

原创

lyzflg 2012-05-08 17:02:23 ©著作权

文章标签 oracle create default 测试表 identified 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者lyzflg的原创作品，请联系作者获取转载授权，否则将追究法律责任

#############################################创建测试表空间和用户###################################
create tablespace hwm
datafile '/db/oracle/oradata/orcl/hwm.dbf'
size 10M;

create user hwm identified by hwm default tablespace hwm temporary tablespace temp;

grant connect,resource,dba to hwm;

conn hwm/hwm;

#############################################创建测试表#############################################
SQL> CREATE TABLE TEST_HWM(
2 C1 NUMBER(10),
3 C2 VARCHAR(100)
4 );

Table created.
###########################################未插入数据前DUMP#########################################
SQL> COLUMN OBJECT_NAME FORMAT A20
SQL>
SQL> SELECT OBJECT_ID,OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_NAME='TEST_HWM';

OBJECT_ID OBJECT_NAME
---------- --------------------
52513 TEST_HWM

SQL> SELECT EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS FROM DBA_EXTENTS WHERE SEGMENT_NAME='TEST_HWM';

EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS
---------- ---------- ------------ ---------- ----------
0 6 6 265 8

SQL> ALTER SYSTEM DUMP DATAFILE 6 BLOCK 265;

###########################################DUMP 结果########################################################
[oracle@ccitca udump]$ cat orcl_ora_3546.trc
/db/oracle/admin/orcl/udump/orcl_ora_3546.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /db/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      ccitca
Release:        2.6.18-164.el5
Version:        #1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine:        i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 3546, p_w_picpath: oracle@ccitca (TNS V1-V3)

*** 2012-05-09 15:25:57.526
*** ACTION NAME:() 2012-05-09 15:25:57.526
*** MODULE NAME:(SQL*Plus) 2012-05-09 15:25:57.526
*** SERVICE NAME:(SYS$USERS) 2012-05-09 15:25:57.526
*** SESSION ID:(159.5) 2012-05-09 15:25:57.526
Start dump data blocks tsn: 7 file#: 6 minblk 265 maxblk 265
buffer tsn: 7 rdba: 0x01800109 (6/265)
scn: 0x0000.00092080 seq: 0x01 flg: 0x00 tail: 0x20802001
frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0DCCA400 to 0x0DCCC400
DCCA400 0000A220 01800109 00092080 00010000 [ ........ ......]
DCCA410 00000000 00000000 00000000 00000000 [................]
        Repeat 2 times
DCCA440 00000000 00000000 00000000 00000004 [................]
DCCA450 FFFFFFFF 00000005 00000003 00000008 [................]
DCCA460 00010001 00000000 00000000 00000000 [................]
DCCA470 00000000 00000003 00000000 00000000 [................]
DCCA480 00000000 00000000 00000000 00000000 [................]
DCCA490 0180010A 00000000 00000000 00000003 [................]
DCCA4A0 00000008 0180010C 00000000 00000000 [................]
DCCA4B0 00000000 00000000 00000000 00000001 [................]
DCCA4C0 0000CD21 00000000 00000000 01800109 [!...............]
DCCA4D0 00000008 00000000 00000000 00000000 [................]
DCCA4E0 00000000 00000000 00000000 00000000 [................]
        Repeat 9 times
DCCA580 00000000 00000000 00000000 00001011 [................]
DCCA590 00000000 00000000 00000000 00000000 [................]
        Repeat 485 times
DCCC3F0 00000000 00000000 00000000 20802001 [............. . ]
Dump of First Level Bitmap Block
--------------------------------
   nbits : 4 nranges: 1         parent dba: 0x0180010a   poffset: 0
   unformatted: 5       total: 8         first useful block: 3
   owning instance : 1
   instance ownership changed at
   Last successful Search
   Freeness Status: nf1 0      nf2 0      nf3 0      nf4 0

   Extent Map Block Offset: 4294967295
   First free datablock : 3
   Bitmap block lock opcode 0
   Locker xid:     : 0x0000.000.00000000
   Inc #: 0 Objd: 52513
HWM Flag: HWM Set
      Highwater:: 0x0180010c ext#: 0      blk#: 3      ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
   0x01800109 Length: 8      Offset: 0

   0:Metadata   1:Metadata   2:Metadata   3:unformatted
   4:unformatted   5:unformatted   6:unformatted   7:unformatted
--------------------------------------------------------
End dump data blocks tsn: 7 file#: 6 minblk 265 maxblk 265
[oracle@ccitca udump]$

########################################插入1000条数据再DUMP#####################################
SQL> DECLARE
2 I NUMBER(10);
3 BEGIN
4 FOR I IN 1..1000 LOOP
5 INSERT INTO TEST_HWM VALUES(I,'TEST_HWM');
6 END LOOP;
7 COMMIT;
8 END;
9 /

PL/SQL procedure successfully completed.

SQL> ALTER SYSTEM DUMP DATAFILE 6 BLOCK 265;

[oracle@ccitca udump]$ cat orcl_ora_3747.trc
/db/oracle/admin/orcl/udump/orcl_ora_3747.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /db/oracle/product/10.2.0/db_1
System name:    Linux
Node name: ccitca
Release:   2.6.18-164.el5
Version:   #1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine:   i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 3747, p_w_picpath: oracle@ccitca (TNS V1-V3)

################################插入2000条数据再DUMP##################################
SQL> DECLARE
2 I NUMBER(10);
3 BEGIN
4 FOR I IN 1001..2000 LOOP
5 INSERT INTO TEST_HWM VALUES(I,'TEST_HWM_2000');
6 END LOOP;
7 COMMIT;
8 END;
9 /

PL/SQL procedure successfully completed.

SQL> ALTER SYSTEM DUMP DATAFILE 6 BLOCK 265;

*** 2012-05-09 15:54:32.959
*** ACTION NAME:() 2012-05-09 15:54:32.958
*** MODULE NAME:(SQL*Plus) 2012-05-09 15:54:32.958
*** SERVICE NAME:(SYS$USERS) 2012-05-09 15:54:32.958
*** SESSION ID:(159.7) 2012-05-09 15:54:32.958
Start dump data blocks tsn: 7 file#: 6 minblk 265 maxblk 265
buffer tsn: 7 rdba: 0x01800109 (6/265)
scn: 0x0000.000924b3 seq: 0x01 flg: 0x00 tail: 0x24b32001
frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D946600 to 0x0D948600
D946600 0000A220 01800109 000924B3 00010000 [ ........$......]
D946610 00000000 00000000 00000000 00000000 [................]
        Repeat 2 times
D946640 00000000 00000000 00000000 00000004 [................]
D946650 FFFFFFFF 00000000 00000003 00000008 [................]
D946660 00010001 00000000 00000000 00000001 [................]
D946670 00000002 00000004 4FAA224A 4FAA224A [........J".OJ".O]
D946680 00000000 00000000 00000000 00000000 [................]
D946690 0180010A 00000000 00000000 00000008 [................]
D9466A0 00000008 01800111 00000000 00000000 [................]
D9466B0 00000000 00000005 00000000 00000001 [................]
D9466C0 0000CD21 00000000 00000000 01800109 [!...............]
D9466D0 00000008 00000000 00000000 00000000 [................]
D9466E0 00000000 00000000 00000000 00000000 [................]
        Repeat 9 times
D946780 00000000 00000000 00000000 51451111 [..............EQ]
D946790 00000000 00000000 00000000 00000000 [................]
        Repeat 485 times
D9485F0 00000000 00000000 00000000 24B32001 [............. .$]
Dump of First Level Bitmap Block
--------------------------------
   nbits : 4 nranges: 1         parent dba: 0x0180010a   poffset: 0
   unformatted: 0       total: 8         first useful block: 3
   owning instance : 1
   instance ownership changed at 05/09/2012 15:52:42
   Last successful Search 05/09/2012 15:52:42
   Freeness Status: nf1 0      nf2 0      nf3 1      nf4 2

   Extent Map Block Offset: 4294967295
   First free datablock : 4
   Bitmap block lock opcode 0
   Locker xid:     : 0x0000.000.00000000
   Inc #: 0 Objd: 52513
HWM Flag: HWM Set
      Highwater:: 0x01800111 ext#: 0      blk#: 8      ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 5
mapblk 0x00000000 offset: 0
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
   0x01800109 Length: 8      Offset: 0

   0:Metadata   1:Metadata   2:Metadata   3:FULL
   4:50-75% free   5:75-100% free   6:75-100% free   7:FULL
--------------------------------------------------------
End dump data blocks tsn: 7 file#: 6 minblk 265 maxblk 265
*** 2012-05-09 15:59:51.677
Start dump data blocks tsn: 7 file#: 6 minblk 265 maxblk 265
buffer tsn: 7 rdba: 0x01800109 (6/265)
scn: 0x0000.00092566 seq: 0x02 flg: 0x00 tail: 0x25662002
frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D946600 to 0x0D948600
D946600 0000A220 01800109 00092566 00020000 [ .......f%......]
D946610 00000000 00000000 00000000 00000000 [................]
        Repeat 2 times
D946640 00000000 00000000 00000000 00000004 [................]
D946650 FFFFFFFF 00000000 00000003 00000010 [................]
D946660 00010002 00000000 00000001 00000000 [................]
D946670 00000007 00000008 4FAA23AC 4FAA23AC [.........#.O.#.O]
D946680 00000000 00000000 00000000 00000000 [................]
D946690 0180010A 00000000 00000001 00000008 [................]
D9466A0 00000008 01800119 00000000 00000001 [................]
D9466B0 00000000 0000000D 00000000 00000001 [................]
D9466C0 0000CD21 00000000 00000000 01800109 [!...............]
D9466D0 00000008 00000000 01800111 00000008 [................]
D9466E0 00000008 00000000 00000000 00000000 [................]
D9466F0 00000000 00000000 00000000 00000000 [................]
        Repeat 8 times
D946780 00000000 00000000 00000000 11111111 [................]
D946790 53555555 00000000 00000000 00000000 [UUUS............]
D9467A0 00000000 00000000 00000000 00000000 [................]
        Repeat 484 times
D9485F0 00000000 00000000 00000000 25662002 [............. f%]
Dump of First Level Bitmap Block
--------------------------------
   nbits : 4 nranges: 2         parent dba: 0x0180010a   poffset: 0
   unformatted: 0       total: 16        first useful block: 3
   owning instance : 1
   instance ownership changed at 05/09/2012 15:58:36
   Last successful Search 05/09/2012 15:58:36
   Freeness Status: nf1 0      nf2 1      nf3 0      nf4 7

   Extent Map Block Offset: 4294967295
   First free datablock : 8
   Bitmap block lock opcode 0
   Locker xid:     : 0x0000.000.00000000
   Inc #: 0 Objd: 52513
HWM Flag: HWM Set
      Highwater:: 0x01800119 ext#: 1      blk#: 8      ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 13
mapblk 0x00000000 offset: 1
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
   0x01800109 Length: 8      Offset: 0
   0x01800111 Length: 8      Offset: 8

   0:Metadata   1:Metadata   2:Metadata   3:FULL
   4:FULL   5:FULL   6:FULL   7:FULL
   8:75-100% free   9:75-100% free   10:75-100% free   11:75-100% free
   12:75-100% free   13:75-100% free   14:75-100% free   15:25-50% free
--------------------------------------------------------
End dump data blocks tsn: 7 file#: 6 minblk 265 maxblk 265
[oracle@ccitca udump]$

#####################################查看执行计划###################################
SQL> SET AUTOTRACE TRACEONLY
SQL> SET TIMING ON
SQL> SELECT COUNT(*) FROM TEST_HWM;

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------

Plan hash value: 2326001506

-----------------------------------------------------------------------
| Id | Operation          | Name     | Rows | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     5   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_HWM | 2000 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
         16 consistent gets
          0 physical reads
          0 redo size
        411 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

########################################分析表####################################################
SQL>
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'HWM',
3 TABNAME=>'TEST_HWM',
4 PARTNAME=>NULL);
5 END;
6 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.73
SQL> SET LINE 140
SQL> SELECT NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='TEST_HWM';
Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3561033063

-------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name           | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |     2 |   324 |    28   (4)| 00:00:01 |
|* 1 | HASH JOIN                           |                |     2 |   324 |    28   (4)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN               |                |     2 |   298 |    27   (0)| 00:00:01 |
|   3 |    NESTED LOOPS OUTER                |                |     2 |   188 |    27   (0)| 00:00:01 |
|   4 |     NESTED LOOPS OUTER               |                |     2 |   182 |    25   (0)| 00:00:01 |
|   5 |      NESTED LOOPS OUTER              |                |     2 |   168 |    23   (0)| 00:00:01 |
|   6 |       NESTED LOOPS OUTER             |                |     2 |   160 |    23   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                  |                |     2 |   138 |    21   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                 |                |     2 |   132 |    19   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                |                |     2 |    62 |    18   (0)| 00:00:01 |
|* 10 |           TABLE ACCESS BY INDEX ROWID| OBJ$           |     2 |    56 |    16   (0)| 00:00:01 |
|* 11 |            INDEX SKIP SCAN           | I_OBJ2         |     2 |       |    15   (0)| 00:00:01 |
| 12 |           TABLE ACCESS CLUSTER       | USER$          |     1 |     3 |     1   (0)| 00:00:01 |
|* 13 |            INDEX UNIQUE SCAN         | I_USER#        |     1 |       |     0   (0)| 00:00:01 |
|* 14 |          TABLE ACCESS CLUSTER        | TAB$           |     1 |    35 |     1   (0)| 00:00:01 |
|* 15 |           INDEX UNIQUE SCAN          | I_OBJ#         |     1 |       |     0   (0)| 00:00:01 |
| 16 |         TABLE ACCESS CLUSTER         | TS$            |     1 |     3 |     1   (0)| 00:00:01 |
|* 17 |          INDEX UNIQUE SCAN           | I_TS#          |     1 |       |     0   (0)| 00:00:01 |
| 18 |        TABLE ACCESS CLUSTER          | SEG$           |     1 |    11 |     1   (0)| 00:00:01 |
|* 19 |         INDEX UNIQUE SCAN            | I_FILE#_BLOCK# |     1 |       |     0   (0)| 00:00:01 |
|* 20 |       INDEX UNIQUE SCAN              | I_OBJ1         |     1 |     4 |     0   (0)| 00:00:01 |
| 21 |      TABLE ACCESS BY INDEX ROWID     | OBJ$           |     1 |     7 |     1   (0)| 00:00:01 |
|* 22 |       INDEX UNIQUE SCAN              | I_OBJ1         |     1 |       |     0   (0)| 00:00:01 |
| 23 |     TABLE ACCESS CLUSTER             | USER$          |     1 |     3 |     1   (0)| 00:00:01 |
|* 24 |      INDEX UNIQUE SCAN               | I_USER#        |     1 |       |     0   (0)| 00:00:01 |
| 25 |    BUFFER SORT                       |                |     1 |    55 |    26   (0)| 00:00:01 |
|* 26 |     FIXED TABLE FULL                 | X$KSPPI        |     1 |    55 |     0   (0)| 00:00:01 |
| 27 |   FIXED TABLE FULL                   | X$KSPPCV       |   100 | 1300 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("KSPPI"."INDX"="KSPPCV"."INDX")
10 - filter(BITAND("O"."FLAGS",128)=0)
11 - access("O"."NAME"='TEST_HWM')
       filter("O"."NAME"='TEST_HWM')
13 - access("O"."OWNER#"="U"."USER#")
14 - filter(BITAND("T"."PROPERTY",1)=0)
15 - access("O"."OBJ#"="T"."OBJ#")
17 - access("T"."TS#"="TS"."TS#")
19 - access("T"."TS#"="S"."TS#"(+) AND "T"."FILE#"="S"."FILE#"(+) AND
              "T"."BLOCK#"="S"."BLOCK#"(+))
20 - access("T"."BOBJ#"="CO"."OBJ#"(+))
22 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
24 - access("CX"."OWNER#"="CU"."USER#"(+))
26 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
         45 consistent gets
          0 physical reads
          0 redo size
        534 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL>

SQL> SELECT NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='TEST_HWM';

NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
2000 13 0

##########################################删除数据###########################################
SQL> DELETE FROM TEST_HWM;

2000 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT NUM_ROWS,BLOCKS,EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='TEST_HWM';

NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
2000 13 0

SQL> SELECT COUNT(*) FROM TEST_HWM;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2326001506

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
         16 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

############################# 删除数据库后查看表空间段使用情况##################################################
VAR TOTAL_BLOCKS NUMBER
VAR TOTAL_BYTES NUMBER
VAR UNUSED_BLOCKS NUMBER
VAR UNUSED_BYTES NUMBER
VAR LAST_USED_EXTENT_FILE_ID NUMBER
VAR LAST_USED_EXTENT_BLOCK_ID NUMBER
VAR LAST_USED_BLOCK NUMBER
EXEC DBMS_SPACE.UNUSED_SPACE

('HWM','TEST_HWM','TABLE',:TOTAL_BLOCKS,:TOTAL_BYTES,:UNUSED_BLOCKS,:UNUSED_BYTES,:LAST_USED_EXTENT_FILE_ID,:LAST_USED_EXTENT_BLOCK_ID,:LAST_USED_BLOCK);
PRINT TOTAL_BLOCKS
PRINT TOTAL_BYTES
PRINT UNUSED_BLOCKS
PRINT UNUSED_BYTES
PRINT LAST_USED_EXTENT_FILE_ID
PRINT LAST_USED_EXTENT_BLOCK_ID
PRINT LAST_USED_BLOCK

SQL>
TOTAL_BLOCKS
------------
16

SQL>
TOTAL_BYTES
-----------
131072

SQL>
UNUSED_BLOCKS
-------------
0

SQL>
UNUSED_BYTES
------------
0

SQL>
LAST_USED_EXTENT_FILE_ID
------------------------
6

SQL>
LAST_USED_EXTENT_BLOCK_ID
-------------------------
273