Wednesday, October 24, 2007

Contention on SYS.DUAL table-- - - From Vivek Sharma

Contention on sys.dual

SYS owned dual table was introduced by Oracle long back and since then the Application
Development team across the globe has been using this table for some internal processing.
Some of the common queries on DUAL table used by the application team are :

select sysdate from dual;
select sequence.nextval from dual;
select sys_context('USERENV','SESSION_USER') from dual;

DUAL being a magic table contains only one row and hence returns a single row for a given
sql statement. Any inputs passed to dual table returns the desired output and hence is widely
used by the applications.

Originally, this table was introduced by Oracle for its internal processing, for example, RMAN
uses this table to fetch the current date for a backup. Contention on this table can occur, if
application uses this table frequently and concurrently, with common waits like Buffer Busy Wait,
Latch Free (Cache Buffer Chain / Cache Buffer LRU chain). The behaviour of this table is changed in
Oracle database version 10g and hence, this note is only applicable for database versions 9i and below.


This article focusses on eliminating contention on sys owned dual table and an alternative to this table
without altering the original table. Oracle strongly recommends that sys owned dual table should not be
altered in any way.

In this Article, we will create our own dual table and will force the application to use our table rather
than using sys owned dual table. This will be done without making any change in the application which was a
big challenge when it comes to Customized applications developed by third party.

Any single call to a sys owned dual table does 3-4 logical i/o’s, thus concurrent and multiple calls on this
table will increase these I/O’s thus causing contention.
As an Example :

SQL> column user_info format a30
SQL> select sys_context('USERENV','SESSION_USER') user_info from dual;

USER_INFO
------------------------------
VIVEK


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Now, if I create my own table in my production schema, the same query used above
can be forced to use our table without making change in the code. The process would
be :

SQL> create table my_dual (x number primary key) organization index;

Table created.

SQL> insert into my_dual values (1);

1 row created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'VIVEK',tabname=>'MY_DUAL',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> create view dual as select * from my_dual;

View created.

SQL> set autot on
SQL> select sys_context('USERENV','SESSION_USER') user_info from dual;

USER_INFO
------------------------------
VIVEK


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 INDEX (FULL SCAN) OF 'SYS_IOT_TOP_32339' (UNIQUE) (Cost=1
Card=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

1 Logical I/O against 4 Logical I/O’s which is 75% performance improvement. The improvement is
clearly visible when this table is accessed concurrently and will eliminate the contention,
thus improving buffer cache efficiency.

Thus, if statspack shows high logical I/O’s or disk reads on SYS.Dual table, this solution can be
implemented to reduce these I/O’s. Less the number of I/O’s, less is the contention for latches.
Implementing this solution does not alter the original dual table and Oracle internally can use its
own table whenever required while our application (without any modification) uses our own table
efficiently.

As mentioned earlier, the behaviour of dual table is changed in Oracle 10g. Oracle 10g introduces
FAST DUAL concept and does not visits data blocks to get the desired output. Hence, the recommendations
suggested in this note is not required in all versions of 10g databases. For example,


Orcl10g> SQL> select sys_context('USERENV','SESSION_USER') user_info from dual;

USER_INFO
-------------
VIVEK


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
415 bytes sent via SQL*Net to client

13 comments:

Anonymous said...

Great post! We are linking to this great content on our site.
Keep up the good writing.

Have a look at my weblog :: healthy diet

Anonymous said...

Many homes have fireplaces for both warmth and decor.
The heat from the air moves into the cooled refrigerant. Programmable thermostats are just the
latest wave of new technology in the HVAC field.



My webpage; nest thermostat

Anonymous said...

The only clicks that count toward the monthly totals come from web searches like Google
or from visitors who have clicked on a laptop after visiting our
"Notebook Database" or inside our forums. With release
of the handset comes Samsung galaxy s3 deals.
This is not only handy for presenting you with updates all in one place but it also merges with all of your contacts and calendars that are stored within these accounts.

Anonymous said...

9 fps while battery life is rated at about 850 shots.

National Space News Examiner - National Photography
Examiner - Cleveland Astronomy Examiner. The EOS 5D Mark II naturally characteristics a total-frame sensor,
but the resolution enjoys a considerable increase through
the 12.

Feel free to visit my web site 5d mark iii

Anonymous said...

Another new aspect is on the front, right
above the display on the upper right corner. This battery really surprised me with how much work I could get done in a single day.
When you finish with the app, most people just hit the Home button
or the back arrow button go to the main menu.

Here is my web-site: samsung galaxy note 10.1

Anonymous said...

Simply want to say your article is as astounding. The clarity in your post is just
cool and i could assume you are an expert on
this subject. Fine with your permission let me to grab your feed
to keep up to date with forthcoming post. Thanks a million and please carry on the gratifying work.


Feel free to visit my web blog - Beaute MD\Beaute MD Review

Anonymous said...

Simply click on the program that you want to watch and go directly to the channel it's playing on. Could it be that the big media companies want to keep the two seperate on purpose. Twonky: Streams music, photos and videos to compatible devices in the home.

my web site :: roku

Anonymous said...

It's actually a nice and helpful piece of information. I'm happy that you shared
this helpful information with us. Please stay us informed like
this. Thanks for sharing.

My site ... Auravie Skin Care Review

Anonymous said...

This text is worth everyone's attention. Where can I find out more?

Have a look at my webpage; pure green coffee

Anonymous said...

I am not sure where you are getting your info, but great topic.
I needs to spend some time learning much more or understanding more.
Thanks for great info I was looking for this information for my mission.


Here is my web page - Slim XS Diet

Anonymous said...

I have read so many posts concerning the blogger
lovers except this paragraph is truly a good
piece of writing, keep it up.

my blog post Equinox Anti Aging Formula

raybanoutlet001 said...

baltimore ravens jerseys
christian louboutin shoes
clippers jersey
nike huarache
ugg outlet
ralph lauren
skechers shoes
colts jerseys
nike huarache
green bay packers jerseys

jeje said...

lebron 13
converse outlet store
adidas yeezy
adidas superstar
longchamp bags
adidas ultra boost 3.0
james harden shoes
michael kors outlet
yeezy boost 350
links of london