Dual 表的性能优化
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)) ORGANIZATIO
N 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了！