序列的cache的设置对数据库性能的影响

1.1     序列的cache为0

 

SQL> select sid from v$mystat where rownum=1;

 

       SID

----------

        52

 

SQL>    SET timing ON;

SQL>    --清除数据

SQL>    TRUNCATE TABLE tb_seq;

 

表已截掉。

 

已用时间:  00: 00: 00.00

SQL>    --删除序列

SQL>    DROP SEQUENCE seq_test;

 

序列已丢弃。

 

已用时间:  00: 00: 00.00

SQL>    --重建序列

SQL>    CREATE SEQUENCE seq_test START WITH 1 NOCACHE;

 

序列已创建。

 

已用时间:  00: 00: 00.00

SQL>    --插入数据

SQL>    INSERT INTO tb_seq(f,seq)

  2     SELECT 'aaaaa',seq_test.NEXTVAL

  3     FROM dba_objects;

 

已创建52505行。

 

已用时间:  00: 00: 10.08

SQL>    --commit;

SQL>    COMMIT;

 

提交完成。

 

已用时间:  00: 00: 00.00

SQL>    --看日志量

SQL>    SELECT VALUE

  2     FROM v$mystat v1,v$statname v2

  3     WHERE v1.STATISTIC#=v2.STATISTIC#

  4     AND   NAME='redo size';

 

     VALUE

----------

  39，205，952

 

已用时间:  00: 00: 00.00

SQL>

1.2     序列的cache为10

SQL>    --看当前的SID

SQL>    SELECT SID FROM V$MYSTAT WHERE ROWNUM=1;

 

       SID

----------

        68

 

SQL>    --记录时间

SQL>    SET timing ON;

SQL>    --清楚数据

SQL>    TRUNCATE TABLE tb_seq;

 

表已截掉。

 

已用时间:  00: 00: 00.00

SQL>    --删除序列

SQL>    DROP SEQUENCE seq_test;

 

序列已丢弃。

 

已用时间:  00: 00: 00.00

SQL>    --重建序列

SQL>    CREATE SEQUENCE seq_test START WITH 1 CACHE 10;

 

序列已创建。

 

已用时间:  00: 00: 00.00

SQL>    --插入数据

SQL>    INSERT INTO tb_seq(f,seq)

  2     SELECT 'aaaaa',seq_test.NEXTVAL

  3     FROM dba_objects;

 

已创建52505行。

 

已用时间:  00: 00: 01.05

SQL>    --commit;

SQL>    COMMIT;

 

提交完成。

 

已用时间:  00: 00: 00.00

SQL>    --看日志量

SQL>    SELECT VALUE

  2     FROM v$mystat v1,v$statname v2

  3     WHERE v1.STATISTIC#=v2.STATISTIC#

  4     AND   NAME='redo size';

 

     VALUE

----------

   4,998,356

 

已用时间:  00: 00: 00.00

1.3     序列的cache为50

 

SQL*Plus: Release 9.2.0.1.0 - Production on 星期二 3月 29 16:39:35 2011

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

SQL> conn db40/db40_testyou@151

已连接。

SQL>    --看当前的SEQ

SQL>    SELECT SID FROM V$MYSTAT WHERE ROWNUM=1;

 

       SID

----------

        67

 

SQL>    --记录时间

SQL>    SET timing ON;

SQL>    --清楚数据

SQL>    TRUNCATE TABLE tb_seq;

 

表已截掉。

 

已用时间:  00: 00: 00.00

SQL>    --删除序列

SQL>    DROP SEQUENCE seq_test;

 

序列已丢弃。

 

已用时间:  00: 00: 00.00

SQL>    --重建序列

SQL>    CREATE SEQUENCE seq_test START WITH 1 CACHE 50;

 

序列已创建。

 

已用时间:  00: 00: 00.00

SQL>    --插入数据

SQL>    INSERT INTO tb_seq(f,seq)

  2     SELECT 'aaaaa',seq_test.NEXTVAL

  3     FROM dba_objects;

 

已创建52505行。

 

已用时间:  00: 00: 00.07

SQL>    --commit;

SQL>    COMMIT;

 

提交完成。

 

已用时间:  00: 00: 00.00

SQL>    --看日志量

SQL>    SELECT VALUE

  2     FROM v$mystat v1,v$statname v2

  3     WHERE v1.STATISTIC#=v2.STATISTIC#

  4     AND   NAME='redo size';

 

     VALUE

----------

   1，958，372

 

已用时间:  00: 00: 00.00

1.4     不用SEQ

为减少对实验效果的影响，先在其他会话创建相同结构的表（因为创建表也有日志），然后再进行插入数据的试验。

--在另一个session（暂定SESSION_85,这个会话不用关掉，下面还有用到）中创建相同结构的表

SQL>    --看当前的SID

SQL>    SELECT SID FROM V$MYSTAT WHERE ROWNUM=1;

 

       SID

----------

        85

SQL>    --记录时间

SQL>    SET timing ON;

SQL> create table tb_seq_copy as

  2  select * from tb_seq where 1=2;

 

表已创建。

 

已用时间:  00: 00: 00.00

--换一个会话插入数据

SQL>    --看当前的SID

SQL>    SELECT SID FROM V$MYSTAT WHERE ROWNUM=1;

 

       SID

----------

        68

 

SQL>    --记录时间

SQL>    SET timing ON;

SQL> insert into tb_seq_copy select * from tb_seq ;

 

已创建52505行。

 

已用时间:  00: 00: 00.00

SQL> commit;

 

提交完成。

 

已用时间:  00: 00: 00.00

SQL>    SELECT VALUE

  2     FROM v$mystat v1,v$statname v2

  3     WHERE v1.STATISTIC#=v2.STATISTIC#

  4     AND   NAME='redo size';

 

     VALUE

----------

   1，171，264

 

已用时间:  00: 00: 00.00

SQL>

注：这里为什么不用create table tb_seq_copy as select * from tb_seq?因为用这样的方式等价于先create table，然后再

Insert/*+append*/ into tb_seq_copy select * from tb_seq;

这样产生的日志量和上述的方法是不一样的，具体原因这里不做详述！

不信这里我们试验下看看：

先在SESSION_85窗口drop该表tb_seq_copy

SQL> drop table tb_seq_copy;

 

表已丢弃。

然后新开一个会话与上面进行比较：

SQL>    --看当前的SID

SQL>    SELECT SID FROM V$MYSTAT WHERE ROWNUM=1;

 

       SID

----------

        52

 

SQL>    --记录时间

SQL>    SET timing ON;

SQL> CREATE TABLE TB_SEQ_COPY AS SELECT * FROM TB_SEQ;

 

表已创建。

 

已用时间:  00: 00: 00.02

SQL> SELECT VALUE

  2  FROM v$mystat v1,v$statname v2

  3  WHERE v1.STATISTIC#=v2.STATISTIC#

  4  AND   NAME='redo size';

 

     VALUE

----------

    984，340—日志量比先create table再insert into的要少!

1.5     调用序列的NEXTVAL后台做了什么

通过10046可以看到后台调用了如下

update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,
cache=:7,highwater=:8,audit$=:9,flags=:10
where
obj#=:1

………………

PARSING IN CURSOR #2 len=129 dep=1 uid=0 ct=6 lid=0 tim=1270888389400866 hv=371

6207873 ad='ce480e70'

update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=

:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1

END OF STMT

…………………

1.6     个人看法：

在使用SEQ.NEXTVAL的时候，当cache的N个值用光以后，数据库需用重新“cache”一次，“cache”的时候要对表seq$做update操作，当创建序列时指定nocache选项时，类似DML操作的单条记录更新提交，当指定cache为N的时候，后台对SEQ$进行了批量提交，所以产生的日志量比较少！关于单条记录的提交与批量提交产生的日志量请参考:http://hi.baidu.com/fly_ch/blog/item/bad11245aa45422a869473d1.html

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