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).
