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了！

老师用的什么版本的？
SQL> select sysdate from dual;

SYSDATE
---------
23-JAN-11


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
        519  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
我的10.2.0.3 执行计划是这个样子的 是不是10 g之后就不用再这样优化？