10G FAST DUAL 的优化

FAST DUAL 引入
在10G之前，每次都要调用dual都会产生逻辑IO，如果一个系统大量的调用DUAL，会产生的逻辑IO会非常惊人。
到了10G，对于DUAL有优化。
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

SQL> set autotrace traceonly
--注意优化仅仅是针对特定的DUAL语句，如果是查看dual的数据，那是没有变化的。
SQL> select * from dual;

Execution Plan
----------------------------------------------------------
Plan hash value: 3543395131

--------------------------------------------------------------------------
| 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
          7  consistent gets
          3  physical reads
          0  redo size
        511  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select sysdate from dual;

Execution Plan
----------------------------------------------------------
Plan hash value: 1546270724

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

这是9I的TRACE 结果：
SQL> select * from v$version where rownum < 2;

BANNER
----------------------------------------------------------------
Oracle9i Release 9.2.0.4.0 - Production
SQL> set autotrace traceonly
SQL> select sysdate from dual;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=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
        383  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

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/40239/viewspace-620891/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/40239/viewspace-620891/