Hello. This Question is my first thing in AskTom in my life.
I will expect a good Answer.
Please understand my poor English.
----------------------------------------------------
The "EMPLOYEE" table has 1 Extent that having 8 blocks.
8 blocks consist of "Level 1 Bitmap Block", "Level 2 Bitmap Block", "Segment Header Block", "Data Block X 5".
I understood that Level 1,2 Bitmap exists because of ASSM(Automatic Segment Space Management) through this study.
I searched "EMPLOYEE" table with 20 Rows by FTS (Full Table Scan).
I noticed that 20 Rows are in 3094693 Blocks through other sql.
and FTS read all Blocks in all Extent in the Segment.
And I checked x$bh.
OBJECT_ID OBJECT_NAME DBABLK CLASS CLASS_NAME STATE STATE_NAME FLAG
76056 EMPLOYEES 3094690 4 SEGMENT HEADER 1 XCUR 0
76056 EMPLOYEES 3094691 1 DATA BLOCK 1 XCUR 524288
76056 EMPLOYEES 3094692 1 DATA BLOCK 1 XCUR 524288
76056 EMPLOYEES 3094693 1 DATA BLOCK 1 XCUR 524288
76056 EMPLOYEES 3094694 1 DATA BLOCK 1 XCUR 524288
76056 EMPLOYEES 3094695 1 DATA BLOCK 1 XCUR 524288
Level 1,2 Bitmap Block is nothing.
So I understood "Meta Blocks" are not needed to search normal task data.
and I checked dbms_xplan.
PLAN_TABLE_OUTPUT RESULT
SQL_ID gyjgf0yyny787, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ /* KTH2 */ * FROM EMPLOYEES
Plan hash value: 1445457117
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 20 |00:00:00.01 | 7 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 20 | 1340 | 3 (0)| 00:00:01 | 20 |00:00:00.01 | 7 |
-------------------------------------------------------------------------------------------------
Buffers : 7
My question : The block count in x$bh is 6, the buffer number of the dbms_xplan is 7.
Why is it 7 instead of 6 ?
What is the difference of 1?
To be honest I've never really dived into the details because its not really relevant to general database operations.
However, if I had to hypothesize, perhaps
a) the segment header was perhaps read twice, or
b) we read an undo segment header to ensure see if the transaction details are valid
c) any one of 100 different possible reasons.
I wouldn't lose sleep over it.
If you really want to get into the weeds, you could try setting event 10200 to trace the logical IOs.