|
从不sequential的sequence(2)
Sequence created.
SQL> create table test_tab ( x int) ;
Table created.
SQL> insert into test_tab values (test_seq.nextval) ;
1 row created.
SQL> insert into test_tab values (test_seq.nextval) ;
1 row created.
SQL> insert into test_tab values (test_seq.nextval) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_tab ;
X
----------
1
2 3
SQL> conn / as sysdba;
Connected.
SQL> alter system flush shared_pool ;
System altered.
SQL> conn user1/user1
Connected.
SQL> insert into test_tab values (test_seq.nextval) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_tab ;
X
----------
1
2 3
21 从试验中可以看出，在缺省情况下，我们建立的是带有Cache选项的Sequence (缺省值是20), 它的作用是预先将一定数量的序列值存放在SGA中，便于快速访问。可是它的副作用就是这部分数值可能会被清除, 当下一次获取NEXTVAL时，就会不可避免地造成序列值丢失。 总结一下，在以下情况下，序列值会丢失： 1． 数据库关闭或重起 ，由于整个SGA会被清除，所以Cached的序列值同样会被清除。 2. 类似于普通的Data Block ,当SGA中需要放置新的数据，Cached的序列值可能会按照SGA的数据存放规则被清除。 读到这里，细心的读者也许会问，如果在创建Sequence时，有意不选用Cache选项，问题不就解决了吗？且慢，还有两点需要注意： 1． 访问效率降低，没有Cache功能的Sequence取值将无法直接访问内存 2． 不论是Nocache还是Cache , 每次访问NEXTVAL的过程都是不可逆的，在同一session中，在执行一系列DML和Sequence的操作后，用户执行Rollback,希望将操作回滚，但是Sequence此时就显得异常顽固，用掉的NEXTVAL将无法被重现。当下一次试图读取NEXTVAL时，Sequence的指针又移动到下一位了。 看来Oracle真是一个海洋，每个细小的知识点都是那么饶有趣味，值得我们去努力专研啊。 备注：使用Cache功能对Sequence读取效率的影响 Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.1 - ProdUCtion With the Partitioning option JServer Release 8.1.7.4.1 - Production SQL> set timing on SQL> set autotrace traceonly statistiCS SQL> SELECT * FROM ALL_OBJECTS; 14302 rows selected. Elapsed: 00:00:13.05 Statistics ---------------------------------------------------------- 7 recursive calls 4 db block gets 146635 consistent gets 0 physical reads 0 redo size 1633344 bytes sent via SQL*Net to client 117520 bytes received via SQL*Net from client 956 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14302 rows processed SQL> SQL> -- 测试带有CACHE选项的Sequence: SQL> SQL> CREATE SEQUENCE test_seq1 CACHE 1000; Sequence created. Elapsed: 00:00:00.00 SQL> SELECT x.*, test_seq1.NEXTVAL FROM ALL_OBJECTS x; 14303 rows selected. Elapsed: 00:00:13.09 Statistics ---------------------------------------------------------- 202 recursive calls 64 db block gets 146636 consistent gets 0 physical reads 10468 redo size 1752002 bytes sent via SQL*Net to client 117543 bytes received via SQL*Net from client 956 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14303 rows processed SQL> SQL> --测试不带有CACHE选项的Sequence SQL> SQL> DROP SEQUENCE test_seq1; Sequence dropped. Elapsed: 00:00:00.00 SQL> CREATE SEQUENCE test_seq1 NOCACHE; Sequence created. Elapsed: 00:00:00.00 SQL> SELECT x.*, test_seq1.NEXTVAL FROM ALL_OBJECTS x; 14303 rows selected. Elapsed: 00:00:32.02 （执行时间明显长了） Statistics ---------------------------------------------------------- 185946 recursive calls 57216 db block gets 160925 consistent gets 0 physical reads 10004008 redo size 1752002 bytes sent via SQL*Net to client 117543 bytes received via SQL*Net from client 956 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14303 rows processed
BLACK_SNAIL 欢迎交流，转载注明 ligang1000@hotmail.com/ligang@fujitsu.sh.cn
|
