Beware of Oracle Dual Table Costs

Beware of Oracle Dual Table Costs!!
If you have ever used Oracle then you must be aware of the DUAL tables. The DUAL table is one of the first things we learn as Oracle professionals. Basically a DUAL table is a table with a single column and single row owned by the SYS user and accessible to all users. It is used to evaluate constant expression in the SELECT statements, select the name of the current user, ping the database or to generate the next sequence number for a surrogate key. In this article we will help you reveal the cost of querying the DUAL table and offer some less expensive options to optimize application processing.
Cost of DUAL:
You can measure the cost of DUAL by looking at CPU consumption in terms of logical I/O. The logical I/O can be measured as consistent gets. You can use the AUTOTRACE command in SQL*Plus to display the value of consistent gets of an executed statement.
The advent of Oracle 10g eliminated the overhead of performing logical I/O for the query has been eliminated and a new FAST DUAL operation is shown in the access path from the execution plan. The consistent gets are shown as zero in the SQL statement execution statistics. However the behavior of SELECT * DUAL is the same as it had been in prior releases of Oracle and you need to have 3 consistent gets so as to satisfy your query. You can eliminate the consistent gets by changing your code to SELECT 1 FROM DUAL.
SET AUTOTRACE TRACEONLY SELECT USER FROM DUAL; 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 ---------------------------------------------------------- 1 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 331 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
DUAL Tables in Action:
Consider an application where primary keys are generated with the help of sequences. Such can approach can cause poor batch performance due to addition of millions of rows to the database. Our task is to measure the cost of key generation and introduce a more efficient way to create unique keys for our batch processing.
Using DUAL for generating Sequences:
Now we will generate next number in a sequence by using NEXTVAL function. Using sequence object requires only one consistent get and db block gets and recursive calls will be eliminated by caching the sequence numbers.
Create Sequence:
CREATE SEQUENCE MYSEQ START WITH 1000000000 INCREMENT BY 1 MINVALUE 1000000000 MAXVALUE 5000000000 NOCYCLE NOCACHE ORDER;
Set AUTOTRACE:
SET AUTOTRACE TRACEONLY
NEXTVAL:
SELECT MYSEQ.NEXTVAL FROM dual;
Execution Plan:
Execution Plan ---------------------------------------------------------- Plan hash value: 3108674772---------------------------------------------------------
Id Operation Name Row Bytes Cost (%CPU) Time ------------------------------------------------------------------------------------------------ 0 SELECT STATEMENT 1 2 (0) 00:00:01 1 SEQUENCE MYSEQ 2 FAST DUAL 1 2 (0) 00:00:01 ---------------------------------------------------------------------------
SQL Statement Execution Statistics:
Statistics ---------------------------------------------------------- 15 recursive calls 3 db block gets 1 consistent gets 0 physical reads 644 redo size 217 bytes sent via SQL*Net to client 235 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
Generate Numbers using Sequence Object:
Here we will use a sequence to generate two million numbers.
CREATE OR REPLACE PROCEDURE GENERATE_SEQ_MYSEQ AS MYNUMBER NUMBER := 0; BEGIN FOR I IN 1..1000000 LOOP SELECT MYSEQ.NEXTVAL INTO MYNUMBER FROM DUAL; END LOOP ; END; / EXECUTE DBMS_PROFILER.START_PROFILER(RUN_COMMENT=>'SEQUENCE'); EXECUTE GENERATE_SEQ_MYSEQ; EXECUTE DBMS_PROFILER.STOP_PROFILER;
Generate Numbers using PL/SQL code :
Here we will use a PL/SQL code to generate two million numbers.
CREATE OR REPLACE PROCEDURE GENERATE_SEQ_MY_CODE AS MYNUMBER NUMBER := 0; BEGIN FOR I IN 1..2000000 LOOP MYNUMBER := MYNUMBER + 1; END LOOP ; END; / EXECUTE DBMS_PROFILER.START_PROFILER(RUN_COMMENT=>'CODE'); EXECUTE GENERATE_SEQ_MY_CODE; EXECUTE DBMS_PROFILER.STOP_PROFILER;
Analyzing Performance:
Now we will query Oracle DBMS_PROFILER tables so as to determine the performance statistics of the approaches we used. There is remarkable difference in the statistics obtained by both approaches. The performance difference will be even greater for resourced constrained systems. Setting the sequence minvalue to 100,0000,000 has reserved 1-999,999,999 for batch processing. Another way is to start the batch numbering higher than the sequence "maxvalue". Similarly you can o ptimize the key generation by starting the batch numbering > maxvalue. Unique surrogate keys are guaranteed by making the start of the batch execution query the database to determine the starting key value.
SELECT PPU.UNIT_NAME "MYPROCEDURE" ,LPAD(SUBSTR(PPD.LINE#,1,6),6) "LINE#" , LPAD(SUBSTR(PPD.TOTAL_OCCUR,1,7),7) "OCCUR" , LPAD(SUBSTR(ROUND(PPD.TOTAL_TIME/1000000000,2),1,7),7) "SECONDS" ,SUBSTR(SRC.TEXT,1,100) "SQL" FROM PLSQL_PROFILER_RUNS PPR ,PLSQL_PROFILER_UNITS PPU ,PLSQL_PROFILER_DATA PPD ,DBA_SOURCE SRC WHERE PPR.RUNID = PPU.RUNID AND PPU.RUNID = PPD. AND PPU.UNIT_NUMBER = PPD.UNIT_NUMBER AND PPU.UNIT_OWNER = SRC.OWNER AND PPU.UNIT_NAME = SRC.NAME PPD.LINE# = SRC.LINE ORDER BY PPD.RUNID, PPD.UNIT_NUMBER,LINE#;
MYPROCEDURE LINE# OCCUR SECONDS SQL ---------------------------------------- ---------- ------------ ------------- -------------------------------------------------------------------- GENERATE_SEQ_MYSEQ 1 0 0 PROCEDURE GENERATE_SEQ_MYSEQ GENERATE_SEQ_MYSEQ 3 1 0 MYNUMBER NUMBER := 0; GENERATE_SEQ_MYSEQ 5 2000001 2.25 FOR I IN 1..2000000 LOOP GENERATE_SEQ_MYSEQ 6 2000000 289.12 SELECT MYSEQ.NEXTVAL INTO MYNUMBER FROM DUAL; GENERATE_SEQ_MYSEQ 8 1 0 END; GENERATE_SEQ_MY_CODE 1 0 0 PROCEDURE GENERATE_SEQ_MY_CODE GENERATE_SEQ_MY_CODE 3 1 0 MYNUMBER NUMBER := 0; GENERATE_SEQ_MY_CODE 5 2000001 2.15 FOR I IN 1..2000000 LOOP GENERATE_SEQ_MY_CODE 6 2000000 2.17 MYNUMBER := MYNUMBER + 1; GENERATE_SEQ_MY_CODE 8 1 0 END;
Final Words:
To conclude I would say that before using Oracle DUAL table, you should keep its cost in mind as if you take it for-granted then it will cost more than your imagination. Also you should adapt some alternatives so as to optimize the processing of DUAL table.