ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle10g新特性:Fast Dual

Oracle10g新特性:Fast Dual

原创 Linux操作系统 作者:NinGoo 时间:2019-06-23 07:36:06 0 删除 编辑

在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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: redo internal
请登录后发表评论 登录
全部评论

注册时间:2004-12-07

  • 博文量
    200
  • 访问量
    131835