ORACLE中seq$表更新频繁的分析
来源:互联网 发布:linux运维工程师累吗 编辑:程序博客网 时间:2024/06/05 19:21
在分析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/%E5%85%B3%E4%BA%8Eoracle-sequence%E4%B8%80%E4%BA%9B%E5%B0%8F%E6%B5%8B%E8%AF%95
- ORACLE中seq$表更新频繁的分析
- 频繁更新Oracle表更新速度问题解决
- WCDMA频繁位置更新登记的分析和优化
- 基于参考注释的RNA-seq分析
- 基于RNA-seq的基因表达分析
- 更新了java文件后,在eclipse中,调试时断点频繁停在threadpoolexecutor的解决方案
- Linux中seq命令的用法
- R语言中seq函数的用法
- 一次shell中seq的处理
- shell 中 seq的用法总结
- oracle的授创建表和视图还有session、查询、触发器 、 seq 等的权限,
- hibernate3 Annotation中使用oracle seq生成主键值
- 在频繁调用的底层函数中使用malloc的影响分析
- Oracle 用户频繁被锁的解决方法
- oracle seq 小记
- RNA-seq与miRNA-seq联合分析
- Tomcat频繁宕机的原因分析
- RNA-Seq数据分析
- hadoop 加载CSV ,建表,切分数据
- Java枚举的七种常见用法
- [Android Studio]解决Cannot merge new index 68383 into a non-jumbo instruction!的问题
- STM32——由CAN调试想到的调试过程总结
- C++之自增、自减运算符的前缀、后缀运算符区别(5)---《More Effective C++》
- ORACLE中seq$表更新频繁的分析
- 小米推送Cordova,Cordova 小米推送 ionic 小米推送
- 正则表达式匹配任意字符
- Redux源码分析之基本概念
- Java的ReentrantLock构造函数中提供了两种锁:创建公平锁和非公平锁
- MIL测试
- java中的try...catch和finally
- Android--Alertdialog对话框,设置点击其他位置不消失
- CC3200寄存器操作