Introduction
The DUAL table is one of the first things we learn as Oracle professionals. It is created by default as a single row table, understood by the optimizer and available to everyone for selection. We typically use this table to select the name of the current user, ping the database or to generate the next sequence number for a surrogate key. Chances are, few of us consider the impact of querying this table on our applications. This article will explore the cost of querying the DUAL table and offer some less expensive options to optimize application processing.
Measuring the cost of DUAL
One way to measure the cost of DUAL is to look at CPU consumption in terms of logical I/O. We measure logical I/O as 'consistent gets.' A consistent get is when Oracle gets the data in a block from the buffer cache which is consistent with a given point in time, or SCN.
The SQL*Plus autotrace command will help us display the value of an executed statement's consistent gets. Note that the SQL*Plus array size has no affect on this statistic for single row fetches.
Cost prior to Oracle 10g
Prior to 10g the cost of querying the DUAL table is relatively expensive. The following diagram shows the access path from the optimizer's execution plan as a full scan of the DUAL table. The SQL statement execution statistics show querying the DUAL table will cost a minimum of three consistent gets.
You can create your own less expensive DUAL as an indexed-organized table (IOT). In the following diagram, the access path from the optimizer's execution plan shows an index (FULL SCAN) of the BETTER_DUAL table. The SQL statement execution statistics show the number of consistent gets have dropped from three to one.
If you have "SYSDBA" authority you can reduce consistent gets to zero by caching the dual table.
(Reference: http://www.dba-oracle.com/t_dual_table_cache_caching.htm, Don Burleson)
Cost in Oracle 10g
In Ora
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
cle 10g the overhead of performing logical I/O for our query has been eliminated. The access path from the optimizer's execution plan shows a new fast dual operation. SQL statement execution statistics show consistent gets are zero.
Note: A 'select * from dual' SQL statement still behaves as it did prior to 10g and requires three consistent gets to satisfy the query. Changing your code to 'select 1 from dual' will eliminate consistent gets.
Key generation
Let's say we have an application which uses sequences to generate primary keys. This seems to work fine for low-volume OLTP processing. However, batch performance, which adds millions of rows to the database, is poor. Our task is to measure the cost of key generation and introduce a more efficient way to create unique keys for our batch processing.
We'll compare two methods of generating numbers using TIME as our common scale. One method uses a sequence by querying the DUAL table and another generates keys right in the PL/SQL code. The Oracle dbms_profiler package will help us calculate the time it takes for each procedure to generate one million keys.
Generation using DUAL
This method uses the 'nextval' function on a sequence object to generate the next number in a sequence. Use of a sequence object requires one consistent get. Note that caching sequence numbers will eliminate the "recursive calls" and "db block gets" for cached numbers.
The following procedure generates 1 million numbers using a sequence object.
Generation using PL/SQL code
The following procedure generates 1 million numbers directly in PL/SQL code.
Performance statistics
Querying the dbms_profiler tables we can see some remarkable statistics. For 1 million rows, the statement generating a sequence took 133 times longer to run as compared to the PL/SQL code. The performance difference will be even greater for resourced constrained systems.
Optimizing key generation
Now that we've seen a less expensive batch key generation solution, we'll need to figure out how this can co-exist with OLTP sequences. One way is to start sequences at a number greater than one. Setting the sequence "minvalue" to 100,0000,000 effectively reserves 1-999,999,999 for batch processing. Another way is to start the batch numbering higher than the sequence "maxvalue". In either case, the start of the batch execution should query the database to determine the starting key value. This optimization technique allows for concurrent batch and OLTP number generation, guaranteeing unique surrogate keys.
Conclusion
We've seen that taking the Oracle DUAL table for granted may be costing you more than you thought. Whether you're on Oracle 9i or 10g you should rethink your use of the DUAL table and optimize processing by considering some of the alternatives we introduced in this article!
About the author
Jeff McCormick is a senior data architect at a financial services company and executive director of the Connecticut Oracle User Group. Jeff has worked in IT for almost 20 years as a system, storage and database architect/administrator and has over 15 years of experience with DB2, Sybase, SQL Server, MySQL and Oracle relational database technology. He holds several certifications including Oracle Database 10g Administrator Certified Professional, Microsoft Certified Product (SQL Server) and Certified Sybase Professional Database Administrator. Jeff has performed extensive work in the area of high availability and disaster recovery, speaking and authoring several papers on availability architecture.
