JL Computer Consultancy

Dual – SYS – and the not so obvious

Apr 2005


Update: 16th July 2007


A little while ago, I came across a website that made the following claim:

Oracle10g has introduced a new method for accessing dual – using the x$dual virtual table - that reduces the logical I/O cost from three CR gets to zero. Here’s how you can reduce your logical I/O from three to zero in earlier versions of Oracle.

If you haven’t yet allocated a keep pool  (db_keep_cache_size) then set one up in your system, and move the dual table into the keep pool giving it the cache option:

        alter table dual cache storage(buffer_pool keep);

When I did this, my consistent reads on dual dropped from three to zero.

At first sight it should be obvious that this has got to be wrong. In fact, after thinking about it for a minute, it’s still got to be wrong. On the other hand, dual is a very funny table – so maybe the claim right, and it’s worth a test.  (By the way, if you do mess with dual, Oracle Corp. could refuse to support your database because you’ve hacked the data dictionary without permission, so you had better have a very good reason for using any of the well-known dirty tricks on dual).

The author didn’t say anything about how they measured the number of logical I/Os, so I had to make up the test case myself – and a couple of interesting points dropped out when I did. Let’s start shrinking the default pool down and making a keep pool (This is 9.2.0.6 on XP Pro with an 8K block size).

sqlplus /nolog

connect / as sysdba

alter system set db_cache_size = 8m;

alter system set db_keep_cache_size = 8m;

alter table dual cache storage (buffer_pool keep);

 

rem

rem     Just check that the table is assigned to the KEEP pool

rem

 

select  buffer_pool, cache

from    dba_tables

where

    owner = 'SYS'

and table_name = 'DUAL'

;

 

BUFFER_ CACHE

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

KEEP        Y

 

startup force

 

SQL> select name, buffers from v$buffer_pool where block_size =  8192;

 

NAME                    BUFFERS

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

KEEP                       1001

RECYCLE                    1001

DEFAULT                    1001

 

select name, db_block_gets, consistent_gets
from v$buffer_pool_statistics
where block_size = 8192;

NAME                 DB_BLOCK_GETS CONSISTENT_GETS
-------------------- ------------- ---------------
KEEP                             0               0
RECYCLE                          0               0
DEFAULT                        685            7258

declare

    m_v varchar2(32);

begin

    for i in 1..100 loop

        -- This does 100 selects on dual

        m_v := user;

    end loop;

end;

/

 

select name, db_block_gets, consistent_gets
from v$buffer_pool_statistics
where block_size = 8192;

 

NAME                 DB_BLOCK_GETS CONSISTENT_GETS

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

KEEP                             0               0

RECYCLE                          0               0

DEFAULT                        685            7976

Fantastic – the consistent gets on the keep pool haven’t changed – we’ve dropped our logical I/Os on dual from the normal three to zero! 

But hang on a second, the consistent gets on the default pool have jumped by a few hundred – and when I re-executed the anonymous block, they jumped by exactly 300 more – has dual not gone into the keep pool ? Let’s just check up on those buffer pools:

select

    x.addr,

    v.lo_setid,

    v.hi_setid,

    v.name

from

    v$buffer_pool   v,

    x$kcbwds        x

where

    v.block_size = 8192

and x.set_id between v.lo_setid and v.hi_setid

;

 

ADDR       LO_SETID   HI_SETID NAME

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

1BE844F4          1          1 KEEP

1BE849D0          2          2 RECYCLE

1BE84EAC          3          3 DEFAULT

The addr column from x$kcbwds (Working Data Set) appears as the set_ds column in x$bh (Buffer Headers, possibly Buffer Hashchain), so let’s find out which working data set the blocks from dual are in.  (We check the data_object_id in dba_objects, because that’s what appears in x$bh under column obj, although you will still see scripts floating around that assume it is the object_id).

select

    object_id, data_object_id

from

    dba_objects

where

    owner = 'SYS'

and object_type = 'TABLE'

and object_name = 'DUAL'

;

 

 OBJECT_ID DATA_OBJECT_ID

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

222                  222

 

select set_ds, obj, dbarfil, dbablk from x$bh where obj= 222;

 

SET_DS          OBJ    DBARFIL     DBABLK

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

1BE84EAC        222          1       1617

1BE84EAC        222          1       1618

Check the addresses: 1BE84EAC – the blocks from the dual table have got into the default pool. Strange that, but that’s what happens as Oracle starts up. But let’s see if we can make dual move into the keep pool. First, of course, we have to flush it from the default pool – the following query worked for me because I had a suitably small default pool, and the database hadn’t been busy enough to cause the LRU/TCH algorithms to promote dual’s segment header block into the hot half of the cache buffer LRU chain.

select count(*)

from    (

        select /*+ no_merge index(s i_source1) */

            source

        from source$ s

        where source is not null

    )

;

 

SQL> select set_ds, obj, dbarfil, dbablk , tch from x$bh where obj= 222;

 

no rows selected

 

SQL > select user from dual;

 

USER

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

SYS

 

SQL> select set_ds, obj, dbarfil, dbablk , tch from x$bh where obj= 222;

 

SET_DS          OBJ    DBARFIL     DBABLK        TCH

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

1BE84EAC        222          1       1617          1

1BE84EAC        222          1       1618          0

 

2 rows selected.

And it’s still comes back to the default pool!

Of course, there is one little detail I omitted before querying dual again – Oracle ‘knows’ that dual belongs in the default pool because that’s what it says in the dictionary cache. So let’s flush the buffer cache again then do ‘alter system flush shared_pool’ before we query dual again, then see where the blocks are.

This is what we see:

SQL> select set_ds, obj, dbarfil, dbablk , tch from x$bh where obj= 222;

 

SET_DS          OBJ    DBARFIL     DBABLK        TCH

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

1BE844F4        222          1       1617          1

1BE84EAC        222          1       1618          0

 

2 rows selected.

How about that – we have the segment header block in the keep pool and the data block in the default pool. At least, we’ve managed to get half way there. I don’t know if it’s a bug, or a cunning design feature intended to do something clever for RAC, but it looks as if tables owned by sys don’t get their data blocks into the keep pool (and may not even get the segment header block into the keep pool unless the table meets some ‘smallness’ criterion that I haven’t tested for yet – I can see a need for several more tests of the keep pool, the cache option, object sizes, object ownership and accesses paths).

Still, now that we’ve got the dual segment header block into the keep pool, do we reduce the logical I/O when we query it?

select name, db_block_gets, consistent_gets
from v$buffer_pool_statistics
where block_size = 8192;

NAME                 DB_BLOCK_GETS CONSISTENT_GETS
-------------------- ------------- ---------------
KEEP                             0             205

RECYCLE                          0               0

DEFAULT                        700           22730

declare

    m_v varchar2(32);

begin

    for i in 1..100 loop

        m_v := user;

    end loop;

end;

/

 

select name, db_block_gets, consistent_gets
from v$buffer_pool_statistics
where block_size = 8192;

 

NAME                 DB_BLOCK_GETS CONSISTENT_GETS

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

KEEP                             0             405

RECYCLE                          0               0

DEFAULT                        700           22830

So that’s one hundred accesses to dual – and 200 CR gets on the keep pool and 100 on the default pool for a total of 300, or three per access - just as it always is.

I said at the start of this article that it was obvious that the originator of this suggestion had to be wrong – after all, if a block is in the keep pool, it is on a cache buffers chain, and any one cache buffers chain could have blocks from any of the available pools. If you do manage to put dual into the keep pool, you still have to hit the right cache buffers chains latch to access the buffer – and that’s essentially the action that determines that a block visit is a “get” (whether consistent or current).


Conclusion

It’s worth taking a little time to check peculiar cases – dual is a very special case and a very specific irritant. It was within the realms of possibility (though extremely unlikely) that dual would behave differently if put into the keep pool and cached - but it doesn’t.

One important thought to bear in mind about claims like this, though: hacking the sys objects for a performance benefit is not a good idea unless you can get approval from Oracle. But if you are going to risk hacking sys objects, you should at least make sure you’ve got some proper proof that the performance benefit exists.


Update (16th July 2007): In a recent discussion on the Oracle forums someone supplied a pointer to this page and when I followed the link to get here I was reminded that I had originally taken the example from a page by Don Burleson (though I has tried to conceal the fact that I was criticizing him again by re-arranging the text which, as you will see, is not an exact copy of the original). Since Burleson complained on a recent Oracle forum that I had copied “loads” of his material without attribution I have taken the opportunity to rectify this omission.

Note – Burleson’s other suggestion (that you create a local synonym dual for a view on x$dual) would result in a dual that didn’t need three logical I/Os when you accessed it. (In certain cases, though, there would be side-effects in the library cache that you would need to consider).


Back to Index of Topics