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/，如需轉載，請註明出處，否則將追究法律責任。