Home > Oracle Database / Applications Tips > Oracle Database Administrator > What is the DUAL table costing you?
Oracle Tips:
EMAIL THIS
 TIPS & NEWSLETTERS TOPICS 

ORACLE DATABASE ADMINISTRATOR

What is the DUAL table costing you?


Jeffrey R. McCormick
08.31.2006
Rating: -4.17- (out of 5)


Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us   


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


Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us   


RELATED CONTENT
Oracle Database Administrator
Understanding SQL string functions
What is the difference between a database engineer, architect and administrator?
Import on one table from dump file
Error during RMAN backup
Can I drop a column in SYS schema?
STATSPACK tool: transaction vs. execution measurement
Should I port from Microsoft Access?
How can I find statistics on total memory usage and database connections?
Installing multiple Oracle homes
Modifying SYS password in a RAC environment

Oracle database performance problems and tuning
Oracle New Year's resolutions, part 1: Advice for navigating 2009
Solving common Oracle errors guide
Oracle 11g data compression
Varchar or number for better performance?
Do statistics on SYS-owned objects hurt performance in 10g?
Inside the Oracle 11g SQL Performance Advisor, part 1
Inside the Oracle 11g SQL Performance Advisor, part 2
Difference between driving table and driver table in Oracle
Best design for E-Business Suite on hard drive
Using the cost-based optimizer to improve Database 10g performance

Oracle database design and architecture
Can I download DBCA for Oracle Express Edition?
How to recreate an Oracle index in a new schema with the CREATE command
Using Oracle Universal Installer to install Oracle with Pro*C
Defining Oracle database repository vs. information repository
Can I create multiple schemas in Oracle for one user?
ORA-12514 error when connecting to the Oracle database through Toad
Solving the ORA-00904 error: invalid identifier in Oracle
How to tune SQL UPDATE statements for an Oracle 10g upgrade
Will queries run slower in a smaller Oracle buffer cache?
Using a database link to connect two Oracle apps instances

RELATED GLOSSARY TERMS
Terms from Whatis.com − the technology online dictionary
E. F. Codd  (SearchOracle.com)
extent  (SearchOracle.com)
flexfield  (SearchOracle.com)
foreign key  (SearchOracle.com)
multidimensional database  (SearchOracle.com)
object-oriented database management system  (SearchOracle.com)
quad tree  (SearchOracle.com)
relational online analytical processing  (SearchOracle.com)
row  (SearchOracle.com)
splay tree  (SearchOracle.com)

RELATED RESOURCES
2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
Search Bitpipe.com for the latest white papers and business webcasts
Whatis.com, the online computer dictionary


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.

Rate this Tip
To rate tips, you must be a member of SearchOracle.com.
Register now to start rating these tips. Log in if you are already a member.


Submit a Tip




DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.



Oracle Development Solutions - SQL, J2EE, XML, SOA
HomeNewsTopicsTipsAsk the ExpertsMultimediaWhite PapersProductsBlogs
About Us  |  Contact Us  |  For Advertisers  |  For Business Partners  |  Site Index  |  RSS
SEARCH 
TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of technology-specific websites, events and online magazines.

TechTarget Corporate Web Site  |  Media Kits  |  Site Map




All Rights Reserved, Copyright 2003 - 2009, TechTarget | Read our Privacy Policy
  TechTarget - The IT Media ROI Experts