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