Q
Problem solve Get help with specific problems with your technologies, process and projects.

Understanding X$ tables

I'm trying to use the Internal Oracle table:X$BH to find out the correct size of the data buffer cache. Unfortunately I can't understand the column meaning. Does "block status available" mean that those buffers are free and can be used?

The size of the db block is 8 KB. The size of the data buffer cache is: db_block_buffers = 94200. I'm on Oracle version 8.0.6.2. This is the query that I run:

 SQL> SELECT DECODE(STATE,0,'FREE', 1,DECODE(LRBA_SEQ,0,'AVAILABLE','BEING USED'), 3,'BEING USED',STATE) "BLOCK STATUS", COUNT(*) FROM X$BH GROUP BY DECODE(STATE,0,'FREE', 1,DECODE(LRBA_SEQ,0,'AVAILABLE','BEING USED'), 3,'BEING USED',STATE); BLOCK STATUS COUNT(*) --------------------------- AVAILABLE 92690 BEING USED 1508 FREE 2

As I understand, the status means: 0 - never used , even though I see that this value changes when Oracle is up and the changes are up and down. It depends on the value of the column: LRBA_SEQ. If the value of : LRBA_SEQ = 0, then the buffer is available. If the value of : LRBA_SEQ <> 0, then the buffer is is being used. 3 - The buffers are being used right now.

Here is some reference to the FLAG column. This column is not explaind in the articles and might impact the understanding of the result from the query.

 SQL> SELECT STATE , FLAG , COUNT(*) FROM X$BH GROUP BY STATE , FLAG; STATE FLAG COUNT(*) --------- --------- 0 0 61 1 0 74731 1 8192 644 1 8209 2 1 524288 10444 1 33554432 1 1 33562624 6056 1 33562625 536 1 33562632 40 1 33562633 30 1 34078720 4 3 0 4 3 8192 1 3 524288 17 3 524289 1339 3 33562624 11 3 33562625 278 3 33562633 1

18 rows selected.

Example from queries 22/10/03

 ================== SQL> SELECT DECODE(STATE,0,'FREE', 1,DECODE(LRBA_SEQ,0,'AVAILABLE','BEING USED'), 3,'BEING USED',STATE) "BLOCK STATUS", COUNT(*) FROM X$BH GROUP BY DECODE(STATE,0,'FREE', 1,DECODE(LRBA_SEQ,0,'AVAILABLE','BEING USED'), 3,'BEING USED',STATE); BLOCK STATUS COUNT(*) -------------------------------------- AVAILABLE 92544 BEING USED 1597 FREE 59 SQL> SELECT STATE , FLAG , COUNT(*) FROM X$BH GROUP BY STATE , FLAG; STATE FLAG COUNT(*) --------- --------- --------- 0 0 59 1 0 80664 1 8192 651 1 8209 1 1 524288 7041 1 33554432 12 1 33562624 4117 1 33562625 492 1 33562632 27 1 33562633 26 1 34078720 1 3 0 4 3 524288 14 3 524289 878 3 33562624 6 3 33562625 205 3 33562633 2

17 rows selected. ==================================================

I would be very grateful if you would help me since Oracle won't reveal its docs on this table.
Oracle Corp reveals very little about the X$ tables, and for the most part, one never really needs this information. The information in X$BH was one of those views that contained information that wasn't available elsewhere so many people queried this view for that information (the contents of the buffer cache). But even Oracle Corp. realized that this information should be at the DBAs disposal, so they created the V$BH view to help you out. Quickly querying one of the V$BH views in my instance, I have the following STATUS values:

 SQL> select distinct status from v$bh; STATU ----- cr free xcur

The Oracle docs, particularly the Reference Guide, does give information on these values. And if you want more information on X$ views, one of the best Web sites is run by Steve Adams (http://www.ixora.com.au). But on to answering your question:

A status from X$BH of 0 (STATE=0) means that the buffer is free to be used. This does not mean that that particular buffer slot has never been used though. It is possible for a buffer to have been used and then become free. This happens when DBWn dumps a dirty block back to disk. It is then free to be used by other processes. And very shortly, that slot will no longer be free as another process has placed a block in that buffer.

If STATE=1, this essentially means that the buffer is not free. In fact, the data in this buffer has been modified, i.e. it is a dirty block. If a block needs to be placed into the buffer cache and there are no free slots, then a dirty block must be written to disk. Which dirty blocks can be written to disk? That all depends on the value in LRBA_SEQ. If LRBA_SEQ=0 and STATE=1, then this block is dirty, but no one is actively using it, so it can be written to disk. If LRBA_SEQ > 0 and STATE=1, then this block is dirty and it is currently being used by an active transaction.

If STATE=2, then the buffer slot contains data that has never been modified. Should this slot need to be used by a new block, then one only needs to set STATE=0 and the slot is free to use. This differs from STATE=1 which needs DBWn to write the dirty buffer to disk first, before the STATE can be set to zero.

If STATE=3, then the data in the slot is currently being read into the buffer by the server process. As soon as the read call is finished, the STATE will change to 2.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close