Dual 表的性能优化

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2007-02-07 14:50:13

分享到 

Dual表示一个特殊的系统，它只有一个字段、一条记录。在我们的代码中，经常会利用它产生一些特殊值，比如系统时间： select sysdate from dual;     正常情况下，以上语句会对dual表做一次全表扫描，产生3 consistent gets。这是一个很小的数值。但是如果放在一个大循环内，就会产生非常大的consistent gets（实际上，我们的系统就出现过这样的问题）。     如何来优化它呢，consistent gets即时只降低1，在一个大循环中都将会是一个非常大值。首先看一下sql trace: SQL> select sysdate 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 ... ...           1  rows processed     发现对表进行了全表扫描。是否可以考虑通过一个索引来避免呢？ SQL> connect sys/sys as sysdba Connected. SQL> SQL> create table T_DUAL(DUMMY VARCHAR2(1));   Table created.   SQL> SQL> insert into t_dual values ('X');   1 row created.   SQL> commit;   Commit complete.   SQL> SQL> create index t_dual_idx on t_dual(dummy);   Index created.   SQL> analyze table t_dual compute statistics for table for all indexes for all indexed columns;   Table analyzed.   SQL> create view x_$dual as select * from t_dual where dummy='X';   View created.   SQL> grant select on x_$dual to public;   Grant succeeded.   SQL> connect demo/demo Connected. SQL> create synonym DUAL for sys.x_$dual ;   Synonym created.   看看优化结果： SQL> set autot trace SQL> select sysdate from dual;     Execution Plan ----------------------------------------------------------    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=1)    1    0   INDEX (RANGE SCAN) OF 'T_DDUAL_IDX' (NON-UNIQUE) (Cost=1 Card=1 Bytes=1)   Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets           1  consistent gets ... ...           1  rows processed       效果不错！从3降到了1！     但是，为了使用到索引，在视图定义中使用了” where dummy='X'”的条件(哈哈，注意，dummy为not null时不要条件也能使用到索引)。这会给我们的程序带来一定风险。能否避免它呢？当然可以！建立索引组织表试试： SQL> set autot off SQL> drop synonym DUAL;   Synonym dropped.   SQL> connect sys/sys as sysdba Connected. SQL> SQL> drop view x_$dual;   View dropped.   SQL> SQL> drop table T_DUAL;   Table dropped.   SQL> create table t_dual(dummy VARCHAR2(1), constraint t_dual_pk primary key(dummy)) ORGANIZATION INDEX;   Table created.   SQL> insert into t_dual values ('X');   1 row created.   SQL> commit;   Commit complete.   SQL> analyze table t_dual compute statistics for table for all indexes for all indexed columns;   Table analyzed.   SQL> SQL> create view x_$dual as select * from t_dual;   View created.   SQL> SQL> connect demo/demo Connected. SQL> create synonym DUAL for sys.x_$dual ;   Synonym created.       Ok。看看结果： SQL> conn demo/demo Connected. SQL> create synonym DUAL for sys.x_$dual ;   Synonym created.   SQL> set autot trace SQL> select sysdate from dual;   Execution Plan ----------------------------------------------------------    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)    1    0   INDEX (FULL SCAN) OF 'T_DUAL_PK' (UNIQUE) (Cost=1 Card=1)   Statistics ----------------------------------------------------------           0  recursive calls          0  db block gets           1  consistent gets ... ...           1  rows processed       嗯。达到了同样的效果。       那还能不能做进一步优化，把consistent gets降到0呢？试试把对象cache到keep pool中去（当然必须保证keep pool最少大于0）！ SQL> set autot off SQL> drop synonym DUAL;   Synonym dropped.   SQL> connect sys/sys as sysdba Connected. SQL> drop view x_$dual;   View dropped. SQL> show parameter cache   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ ... ... db_cache_size                        big integer 25165824 db_keep_cache_size                   big integer 0 ... ...   SQL> alter system set db_keep_cache_size=1M; alter system set db_keep_cache_size=1M * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00384: Insufficient memory to grow cache     SQL> alter system set db_cache_size=20M;   System altered.   SQL> alter system set db_keep_cache_size=1M;   System altered.   SQL> show parameter cache;   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ ... ... db_cache_size                        big integer 20971520 db_keep_cache_size                   big integer 4194304 ... ...       上面过程中有一些有趣的事件发生，不知道你留意到了没有（最少2处）？这里就不做解释了（卖个关子吧）。 SQL> create view x_$dual as select * from x$dual;   View created.   SQL> grant select on x_$dual to public;   Grant succeeded.   SQL> alter table dual cache storage(buffer_pool keep);   Table altered.   SQL> connect demo/demo Connected. SQL> create synonym DUAL for sys.x_$dual;   Synonym created.       看看结果： SQL> set autot trace SQL> select sysdate from dual;     Execution Plan ---------------------------------------------------------- ERROR: ORA-01039: insufficient privileges on underlying objects of the view     SP2-0612: Error generating AUTOTRACE EXPLAIN report   Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets           0  consistent gets ... ...           1  rows processed       哈哈！consistent gets降为0了！