Dual 表的性能优化

最新推荐文章于 2022-10-15 12:19:11 发布

cqwf95925

最新推荐文章于 2022-10-15 12:19:11 发布

阅读量280

点赞数

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9650775/viewspace-920339/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9650775/viewspace-920339/

关注