V$BH & how to get the block number of the empty block? [message #549513] |
Sat, 31 March 2012 10:59 |
|
lonion
Messages: 97 Registered: August 2011 Location: shenzhen,China
|
Member |
|
|
①SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='T2012';
OBJECT_ID
---------
57082
②SQL> SELECT HEADER_BLOCK,BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'T2012';
HEADER_BLOCK BLOCKS
------------- --------
683 8
③SQL> SELECT DBMS_ROWID.rowid_block_number(ROWID)USED_BLOCK_NUMBER FROM SCOTT.T2012;
USED_BLOCK_NUMBER
----------------
684
④SQL> SHUTDOWN IMMEDIATE;
⑤SQL> STARTUP;
⑥SQL> SELECT BLOCK#,CLASS# FROM V$BH WHERE OBJD = '57082';
no data found
⑦SQL> SELECT * FROM SCOTT.T2012;
ID
-----
1
⑧SQL> SELECT BLOCK#,CLASS# FROM V$BH WHERE OBJD='57082';
BLOCK# CLASS#
------- ----------
686 1
684 1
687 1
685 1
688 1
683 4
⑨SQL> SELECT EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='T2012';
EMPTY_BLOCKS
------------
3
QUESTIONS ONE:
in the ⑧ step,why block#685,block#686,block#687,block#688 in the buffer cache after i query data from scott.T2012?
QUESTIONS TWO:
in the ⑨ step,what's the block number of the empty block?just like DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID).
|
|
|
Re: V$BH & how to get the block number of the empty block? [message #549515 is a reply to message #549513] |
Sat, 31 March 2012 11:15 |
John Watson
Messages: 8962 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
The high water mark of a segment is pushed up in units of 5 blocks. I cannot give a reference for this, but I read it in a reputable source (somewhere in the docs? Ask Tom??) many years ago, and you have just proved this: your query scanned the segment, up to the HWM. The empty blocks will be the next consecutive block numbers: an extent is adjacent blocks.
|
|
|
Re: V$BH & how to get the block number of the empty block? [message #549517 is a reply to message #549515] |
Sat, 31 March 2012 12:35 |
|
Michel Cadot
Messages: 68729 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Note that the step numbers are unreadable for us and appears as square so I answer to the questions without knowing the number you pointed to, next time please use figures from 1 to 9 (ascii 49 to 57).
Q1. When you read via (full) table scan operation Oracle reads several blocks in a shot and not just one block this is why you have 5 data blocks in your cache. As John says each time the HWM increases it increases by 5 blocks (unless there is not 5 blocks till the end of the extend; this change happened between 7.<something> and 7.<something>+1).
Q2. Use dbms_space_admin to know the number of the empty block(s).
Note that the mechanism is different if you use ASSM or MSSM (Automatic/Manual Segment Space Management).
Regards
Michel
|
|
|