在分析ORACLE的AWR报告时，发现SQL ordered by Executions（记录了按照SQL的执行次数排序的TOP SQL。该排序可以看出监控范围内的SQL执行次数）下有一个SQL语句执行非常频繁，一个小时执行了上万次：
update seq$ set increment$=:2, minvalue=:3, maxvalue=:4, cycle#=:5, order$=:6, cache=:7, highwater=:8, audit$=:9, flags=:10 where obj#=:1
那么seq$这个数据字典表是做什么用的呢？ 其实这个数据字典表是保存的是数据库下序列对象（SEQUENCE）的相关信息，而且它用来维护序列的变化。如下所示，我们通过实验来验证一下，我们启用10046事件，跟踪一下会话（level=4 表示启用SQL_TRACE并捕捉跟踪文件中的绑定变量），我们跟踪会话创建序列的过程。下面测试环境为Oracle 11g
SQL> show user; USER is "TEST" SQL> alter session set events '10046 trace name context forever, level 4'; Session altered. SQL> create sequence my_sequence_test 2 start with 1 3 increment by 1 4 maxvalue 999999999 5 nocache; Sequence created. SQL> alter session set events '10046 trace name context off'; Session altered. SQL> SELECT a.VALUE 2 || b.symbol 3 || LOWER(c.instance_name) 4 || '_ora_' 5 || d.spid 6 || '.trc' trace_file 7 FROM (SELECT VALUE 8 FROM v$parameter 9 WHERE NAME = 'user_dump_dest') a, 10 (SELECT SUBSTR (VALUE, -6, 1) symbol 11 FROM v$parameter 12 WHERE NAME = 'user_dump_dest') b, 13 (SELECT instance_name 14 FROM v$instance) c, 15 (SELECT spid 16 FROM v$session s, v$process p, v$mystat m 17 WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d 18 / TRACE_FILE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/gsp/gsp/trace/gsp_ora_28201.trc
[oracle@DB-Server trace]$ tkprof gsp_ora_28201.trc anay_out_28201.txt aggreage=yes; LRM-00101: unknown parameter name 'aggreage' error during command line parsing, cannot continue. [oracle@DB-Server trace]$ tkprof gsp_ora_28201.trc anay_out_28201.txt aggregate=yes; TKPROF: Release 11.2.0.1.0 - Development on Tue Aug 29 22:52:08 2017 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
使用tkprof将跟踪文件转换成可读格式的文件后，你会注意到：在创建序列时，会往数据字典表seq$中插入一条记录（其实创建序列的本质就是在seq$和obj$中插入了一条记录），如下截图所示：
tkprof格式化后的输出文件里面，没有绑定变量，在原始跟踪文件gsp_ora_28201.trc中，你可以看到对应绑定变量的值
使用下面脚本，你就会发现这个都是对应序列对象的一些信息（序列对象的OBJECT_ID、MINVALUE、MAXVALUE、CACHE等等）
SQL> show user; USER is "SYS" SQL> select obj#,increment$,minvalue,maxvalue,cycle#,cache,highwater 2 from seq$ 3 where obj#=97570; OBJ# INCREMENT$ MINVALUE MAXVALUE CYCLE# CACHE HIGHWATER ---------- ---------- ---------- ---------- ---------- ---------- ---------- 97570 1 1 999999999 0 0 1 SQL> select object_type,object_name from dba_objects 2 where object_id=97570; OBJECT_TYPE OBJECT_NAME ------------------- ----------------------------------------------- SEQUENCE MY_SEQUENCE_TEST SQL> select * from dba_sequences where sequence_name='MY_SEQUENCE_TEST'; SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER -------------- ---------------- ---------- ---------- ------------ - - ---------- ----------- TEST MY_SEQUENCE_TEST 1 999999999 1 N N 0 1 SQL>
那么，我们接下来使用SQL TRACE看看使用SEQUENCE时，会对seq$表有啥操作。如下所示，我们在启用SQL_TRACE后，执行3次该SQL语句
SQL> show user; USER is "TEST" SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual; CURRVAL NEXTVAL ---------- ---------- 1 1 SQL> alter session set sql_trace=true; Session altered. SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual; CURRVAL NEXTVAL ---------- ---------- 2 2 SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual; CURRVAL NEXTVAL ---------- ---------- 3 3 SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual; CURRVAL NEXTVAL ---------- ---------- 4 4 SQL> alter session set sql_trace=false; Session altered. SQL>
在跟踪文件中（具体过程跟上面查看跟踪文件类似，在此忽略具体过程），你会看到也对seq$做了三次更新，更新HIGHWATER的值。
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,
cache=:7,highwater=:8,audit$=:9,flags=:10
where
obj#=:1
那么我们接下来，我们修改序列CACHE属性的值，然后重复上面操作，如下所示，在跟踪文件里面，你会看到只更新了seq$一次，其实更新seq$的更新次数是跟CACHE的值有关系的。所以适当的使用CACHE，是可以减少更新seq$数据字典表的次数。
SQL> alter sequence my_sequence_test cache 10; Sequence altered. SQL> alter session set sql_trace=true; Session altered. SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual; CURRVAL NEXTVAL ---------- ---------- 5 5 SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual; CURRVAL NEXTVAL ---------- ---------- 6 6 SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual; CURRVAL NEXTVAL ---------- ---------- 7 7 SQL> alter session set sql_trace=false; Session altered. SQL>
那么我们接下来创建一个表，然后循环递归调用序列，然后生成对应时间段的AWR报告，我们来重现一下生产环境遇到的问题：
SQL> create table test(id number); Table created. begin for row_num in 1 .. 50000 loop insert into test select my_sequence_test.nextval from dual; commit; end loop; end; /
如下所示，你看到INSERT语句执行了50000次，而更新seq$执行了5000次，因为上面测试将序列的CACHE设置为10了，如果没有设置CACHE，那么序列被调用50000次，更新seq$对象也将更新50000次。
另外，调用序列也会有一些redo log开销，如下测试所示，我们先将序列设置为NOCACHE，然后测试过程发现，每次执行都有900多大小的redo log生成。
SQL> alter sequence my_sequence_test nocache; Sequence altered. SQL> set autotrace on; SQL> select my_sequence_test.nextval from dual; NEXTVAL ---------- 50015 Execution Plan ---------------------------------------------------------- Plan hash value: 1070122491 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | SEQUENCE | MY_SEQUENCE_TEST | | | | | 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 30 recursive calls 3 db block gets 3 consistent gets 0 physical reads 908 redo size 527 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select my_sequence_test.nextval from dual; NEXTVAL ---------- 50016 Execution Plan ---------------------------------------------------------- Plan hash value: 1070122491 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | SEQUENCE | MY_SEQUENCE_TEST | | | | | 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 14 recursive calls 4 db block gets 1 consistent gets 0 physical reads 908 redo size 527 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL>
如果使用CACHE的sequence对象而言，redo size生成的频率显然是低得多。如下所示，测试三次，只有第一次生成了redo log， 当然这个是跟序列的CACHE值有关，当缓存的序列值使用完了，生成新的序列值缓存时，也会产生redo log。
SQL> alter sequence my_sequence_test cache 10; Sequence altered. SQL> set autotrace on; SQL> select my_sequence_test.nextval from dual; NEXTVAL ---------- 50017 Execution Plan ---------------------------------------------------------- Plan hash value: 1070122491 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | SEQUENCE | MY_SEQUENCE_TEST | | | | | 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 30 recursive calls 3 db block gets 3 consistent gets 0 physical reads 908 redo size 527 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select my_sequence_test.nextval from dual; NEXTVAL ---------- 50018 Execution Plan ---------------------------------------------------------- Plan hash value: 1070122491 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | SEQUENCE | MY_SEQUENCE_TEST | | | | | 2 | 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 527 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select my_sequence_test.nextval from dual; NEXTVAL ---------- 50019 Execution Plan ---------------------------------------------------------- Plan hash value: 1070122491 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | SEQUENCE | MY_SEQUENCE_TEST | | | | | 2 | 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 527 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
另外一个问题就是，如果序列是NOCACHE，并发调用序列时， 那么也会产生row lock contention， 所以给序列设置一个合适的CACHE值是有很大好处的，既能减少redo log的产生，也能避免减少row lock contention(并发更新seq$同一行记录)。但是序列设置了CACHE后，也有可能遇到跳号问题。那么这个就需要根据实际情况酌情考虑处理了。
参考资料：
https://asktom.oracle.com/pls/asktom/f?p=100:11:451611870226342::::P11_QUESTION_ID:2985886242221
http://www.xifenfei.com/forum/performance/关于oracle-sequence一些小测试
