ORACLE中seq$表更新频繁的分析
前端之家收集整理的这篇文章主要介绍了
ORACLE中seq$表更新频繁的分析，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
在分析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;
USERis "TEST"
sql> altersessionset events '10046 trace name context forever,level 4';
Session altered.
sql> createsequence my_sequence_test
2 startwith 1
3 increment by 1
4 maxvalue 999999999
5 nocache;
Sequence created.
sql> altersessionset events '10046 trace name context off';
sql> SELECT a.VALUE
2 || b.symbol
3 || LOWER(c.instance_name)
4 || '_ora_'
5 || d.spid
6 || '.trc' trace_file
7 FROM (SELECTVALUE
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,monospace; width:100%; border-bottom-style:none; color:black; padding-bottom:0px; direction:ltr; text-align:left; padding-top:0px; border-right-style:none; padding-left:0px; margin:0em; border-left-style:none; line-height:11pt; padding-right:0px; background-color:white"> 13 (SELECT instance_name
14 FROM v$instance) c,monospace; width:100%; border-bottom-style:none; color:black; padding-bottom:0px; direction:ltr; text-align:left; padding-top:0px; border-right-style:none; padding-left:0px; margin:0em; border-left-style:none; line-height:11pt; padding-right:0px; background-color:white"> 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等等）
USERis "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>
select my_sequence_test.currval,my_sequence_test.nextval
from dual;
CURRVAL NEXTVAL
---------- ----------
1 1
sql> altersessionset sql_trace=true;
2 2
3 3
4 4
sql> altersessionset sql_trace=false;
sql>
在跟踪文件中（具体过程跟上面查看跟踪文件类似，在此忽略具体过程），你会看到也对seq$做了三次更新，更新HIGHWATER的值。
update seq$ set increment$=:2,
cache=:7,flags=:10
where
obj#=:1
那么我们接下来，我们修改序列CACHE属性的值，然后重复上面操作，如下所示，在跟踪文件里面，你会看到只更新了seq$一次，其实更新seq$的更新次数是跟CACHE的值有关系的。所以适当的使用CACHE，是可以减少更新seq$数据字典表的次数。
sql>
altersequence my_sequence_test cache 10;
Sequence altered.
5 5
6 6
7 7
sql>
那么我们接下来创建一个表，然后循环递归调用序列，然后生成对应时间段的AWR报告，我们来重现一下生产环境遇到的问题：
sql>
createtable 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;
/
sql>
altersequence my_sequence_test nocache;
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>
