| 9i Dual表的优化 | DBA之路：OoNiceDream |

9i Dual表的优化

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2008/12/9i-dual.html

9i中由于没有采用FAST DUAL，所以通过DUAL表进行的计算，都会产生逻辑读。而应用频繁地运行
select 1 from dual;
等操作，常会造成对dual表的争用，引起Buffer Busy Wait,Latch Free等。

最近生产系统对DUAL表进行了一些调整，在每个频繁访问DUAL表的用户下都建了一张自己DUAL表，对减少逻辑读及系统DUAL表的争用，有一定的作用。

简单模拟如下：

sys@TADBA>select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for HPUX: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
 
5 rows selected.
 
sys@TADBA>
sys@TADBA>set autotrace on 
sys@TADBA>select 1 from dual;
 
         1
----------
         1
 
1 row selected.
 
 
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
        511  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

9i中一般对dual进行的访问都会产生3-4个逻辑读。下面来看看采取在用户下创建dual表的方法：

sys@TADBA>create table perfstat.my_dual (x number primary key) organization index;
 
Table created.
 
sys@TADBA>insert into perfstat.my_dual values (1);
 
1 row created.
 
sys@TADBA>commit;
 
Commit complete.
 
sys@TADBA>exec dbms_stats.gather_table_stats(ownname=>'PERFSTAT',tabname=>'MY_DUAL',cascade=>TRUE);
 
PL/SQL procedure successfully completed.
 
sys@TADBA>create view perfstat.dual as select * from perfstat.my_dual;
 
View created.
 
sys@TADBA>set autotrace on
sys@TADBA>select 1 from dual;
 
         1
----------
         1
 
1 row selected.
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
   1    0   INDEX (FULL SCAN) OF 'SYS_IOT_TOP_6734' (UNIQUE) (Cost=1 Card=1)
 
 
 
 
Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        511  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
sys@TADBA>select 1 from dual;
 
         1
----------
         1
 
1 row selected.
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
   1    0   INDEX (FULL SCAN) OF 'SYS_IOT_TOP_6734' (UNIQUE) (Cost=1 Card=1)
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        511  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed