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/