Oracle10g新特性：Fast Dual

在Oracle10g之前，对于dual表的任何操作都需要执行一个全表扫描，即使我们只是想获得一些函数的返回值。这个全表扫描的操作是需要一定的代价的。

在Oracle9i中

SYS@gdc>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SYS@gdc>set autot traceSYS@gdc>select 1 from dual;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=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
372 bytes sent via SQL*Net to client
387 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

可以看到，对于dual表的全表扫描导致了3个一致性读。而在Oracle8i中，甚至需要5个逻辑IO。实际上我们只需要返回1而已。假如一个系统中频繁的使用了dual，那么这几个逻辑IO可能对于系统性能造成影响。

在Oracle10g中，对于dual表的访问路径进行了改进，如果不是需要查询dual表本身的数据，不再需要全表扫描。

SYS@ning>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SYS@ning>set autot traceSYS@ning>select 1 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
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
404 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

可以发现，在Oracle10g中引入了新的Fast Dual的执行路径，并且没有任何的逻辑IO产生。

这个新特性由一个新引入的隐含参数_fast_dual_enabled控制，默认为TRUE。

SYS@ning>alter session set "_fast_dual_enabled"=false;

Session altered.

SYS@ning>select user from dual;

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

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

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

可以看到，将_fast_dual_enable参数设置成false后，对于dual的访问就和9i的行为一致了。

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

转载于:http://blog.itpub.net/193161/viewspace-50339/