|
|
|
Oracle Dual table performance tips
Oracle Database Tips by Donald Burleson |
By Mike Ault
Also see
computations with
the DUAL table
The DUAL pseudotable is not
really a table.
An
alternative DUAL
There is a cost
when selecting from DUAL, even if we?re only selecting a constant value. To
demonstrate:
SQL> select 1 from dual;
Elapsed: 00:00:00.01
Execution plan
---------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'
Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
380 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
SQL> |
We see that each
select from DUAL costs 3 consistent gets. Now, if you do a lot of selecting from
DUAL your code might benefit from using an alternative DUAL, one that only
requires 1 consistent get. Here's how it's done:
create table xdual (
dummy varchar2(1) primary key
)
organization index;
insert into xdual varchar2(9);
analyze table xdual compute
statistics for table for all indexes for all indexed columns; |
The trick is to
create the XDUAL as an index-organized table and analyze it properly. This
allows the cost-based optimizer to generate the most efficient plan possible,
which requires only 1 consistent get:
SQL> select 1 from xdual;
Elapsed: 00:00:00.01
Execution plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1) 1 0 INDEX (FULL
SCAN) OF 'SYS_IOT_TOP_33973' (UNIQUE) (Cost=1
Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
380 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 |
To see how these
two compare, consider the following code example:
declare
x number;
begin
for i in 1 .. 10000 loop
select 1 into x from dual;
end loop;
end;
|
This required
about 0.32 seconds on my machine using dual, whereas if I exchanged XDUAL with
DUAL I had to wait only 0.25 seconds. Not a whole lot, but it's still about 20%
reduction. Also keep in mind that consistent gets = CPU resources so if you
reduce the required number of consistent gets you reduce the need for CPU.
Note that in
Oracle 10g this situation disappears since the DUAL table now has become a
special internal table that requires no consistent gets!
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
and include the URL for the page.
