some tests about block cleanout

SQL> SELECT HEADER_FILE,HEADER_BLOCK FROM DBA_SEGMENTS where segment_name='EMP';

HEADER_FILE HEADER_BLOCK

----------- ------------

4 27

SQL> select file_id,block_id from dba_extents where segment_name='EMP';

FILE_ID BLOCK_ID

---------- ----------

4 25

SQL> SELECT HEADER_FILE,HEADER_BLOCK FROM DBA_SEGMENTS where segment_name='PK_EMP';

HEADER_FILE HEADER_BLOCK

----------- ------------

4 35

SQL> select file_id,block_id from dba_extents where segment_name='PK_EMP';

FILE_ID BLOCK_ID

---------- ----------

4 33

SQL> select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from scott.emp;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

------------------------------------

31

31

31

31

31

31

31

31

31

31

31

31

31

31

14 rows selected.

before update, the ITL info:

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0007.013.00000251 0x00800a40.03e1.31 C-U- 0 scn 0x0000.00284291

0x02 0x0003.020.0000034f 0x00800412.039c.0c C-U- 0 scn 0x0000.00284507

0x03 0x0001.010.00000262 0x00800aa2.0270.16 C-U- 0 scn 0x0000.0028467d

0x04 0x000a.02e.00000251 0x0080009d.0361.01 C--- 0 scn 0x0000.0027dbda

SQL> update emp set sal=sal*2 where job='MANAGER';

3 rows updated.

alter system flush buffer_cache;

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0007.013.00000251 0x00800a40.03e1.31 C-U- 0 scn 0x0000.00284291

0x02 0x0003.020.0000034f 0x00800412.039c.0c C-U- 0 scn 0x0000.00284507

0x03 0x0001.010.00000262 0x00800aa2.0270.16 C-U- 0 scn 0x0000.0028467d

0x04 0x0001.022.000002cb 0x0080000f.02c1.1c ---- 3 fsc 0x0002.00000000

after commit, the Lck info still exists

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0007.013.00000251 0x00800a40.03e1.31 C-U- 0 scn 0x0000.00284291

0x02 0x0003.020.0000034f 0x00800412.039c.0c C-U- 0 scn 0x0000.00284507

0x03 0x0001.010.00000262 0x00800aa2.0270.16 C-U- 0 scn 0x0000.0028467d

0x04 0x0001.022.000002cb 0x0080000f.02c1.1c ---- 3 fsc 0x0002.00000000

query this table again,

SQL> select * from emp;

Execution Plan

----------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

9 consistent gets

6 physical reads

116 redo size

1363 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

ITL info after query again:

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0007.013.00000251 0x00800a40.03e1.31 C-U- 0 scn 0x0000.00284291

0x02 0x0003.020.0000034f 0x00800412.039c.0c C-U- 0 scn 0x0000.00284507

0x03 0x0001.010.00000262 0x00800aa2.0270.16 C-U- 0 scn 0x0000.0028467d

0x04 0x0001.022.000002cb 0x0080000f.02c1.1c C--- 0 scn 0x0000.002ff461

select * from emp where empno=7369;

SQL> select dbarfil,dbablk,flag,state,tch from x$bh where dbarfil=4;

DBARFIL DBABLK FLAG STATE TCH

---------- ---------- ---------- ---------- ----------

4 212 0 1 1

4 31 0 1 1

4 2 8192 1 2

4 36 0 1 1

alter system flush buffer_cache;

SQL> set autot trace stat

SQL> select * from emp where empno=7369;

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

0 bytes sent via SQL*Net to client

0 bytes received via SQL*Net from client

0 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> set autot on

SQL> select * from emp where empno=7369;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 333 20

Execution Plan

----------------------------------------------------------

Plan hash value: 2949544139

--------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("EMPNO"=7369)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

0 bytes sent via SQL*Net to client

0 bytes received via SQL*Net from client

0 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7548/viewspace-1050346/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7548/viewspace-1050346/