Oracle Illustrated

Feb 21 2010   7:29AM GMT

Migrating from 9i to 11g – Fast dual – 10g

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Fast dual is available from Oracle 10g. FAST DUAL performs 0 consistent gets which means it does not perform any I/O. We use DUAL table to perform dummy selects or perform some calculations each time it does logical I/O but, with this new feature as many logical I/O’s are reduced. In Oracle 9i it used to perform a FULL table scan on dual and does logical I/O everytime.

Oracle 9i
Execution Plan
———————————————————-
Plan hash value: 272002086

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
————————————————————————–

Statistics
———————————————————-
24 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
414 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Second time :

Statistics
———————————————————-
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
414 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Oracle 11g
SQL> select 1 from dual;

Elapsed: 00:00:00.04

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
411 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Really nice feature – especially when we use dual in too many places !!

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: