ITPub博客

首页 > 数据库 > Oracle > enq: SQ - contention等待事件处理

enq: SQ - contention等待事件处理

原创 Oracle 作者:Curry_0316 时间:2015-09-08 16:20:36 0 删除 编辑
由于数据库用户序列cache设置不当,引发enq: SQ - contention等待事件严重告警,以下为处理过程。
 
1、数据库库产生大量Enq:SQ 告警
$ ora event
=============Tue Sep  1 08:38:42 EAT 2015===================

   INST_ID     EVENT# EVENT                                                              COUNT(*)
---------- ---------- ---------------------------------------------------------------- ----------
         1        258 enq: SQ - contention                                                    176
         1        142 log file sync                                                            21
         1        347 SQL*Net message to client                                                12
         1        145 db file sequential read                                                   1
         1        273 row cache lock                                                            1
....
 
2.、enq:SQ全部由 2bbh14rwtytup、dfycvr4vc0vg7 这2个SQL导致
Holder:1:2810,54399                      0          6          0 SQ          0 dfycvr4vc0vg7 enq: SQ - contention                      9
-------------------- ---------- ---------- ---------- ---------- -- ---------- ------------- ------------------------------ ------------
 Waiter:1:2148,5697      441288          0          0          6 SQ          3 dfycvr4vc0vg7 enq: SQ - contention                      2
 Waiter:1:3098,64305                     0          0          6 SQ          3 dfycvr4vc0vg7 enq: SQ - contention                      2
 Waiter:1:5522,42601                     0          0          6 SQ          2 dfycvr4vc0vg7 enq: SQ - contention                      2
 Waiter:1:5834,4859                      0          0          6 SQ          2 dfycvr4vc0vg7 enq: SQ - contention                      2
 Waiter:1:3057,20177                     0          0          6 SQ          2 dfycvr4vc0vg7 enq: SQ - contention                      1
 Waiter:1:3367,7565                      0          0          6 SQ          2 dfycvr4vc0vg7 enq: SQ - contention                      1
 Waiter:1:3956,14509                     0          0          6 SQ          2 dfycvr4vc0vg7 enq: SQ - contention                      1
 Waiter:1:2778,14589                     0          0          6 SQ          2 dfycvr4vc0vg7 enq: SQ - contention                      1
 Waiter:1:4924,12503                     0          0          6 SQ          2 dfycvr4vc0vg7 enq: SQ - contention                     12
 Waiter:1:5153,63411                     0          0          6 SQ          2 dfycvr4vc0vg7 enq: SQ - contention                      1
 Waiter:1:966,33283                      0          0          6 SQ          2 dfycvr4vc0vg7 enq: SQ - contention                      1
SESS                        ID1        ID2      LMODE    REQUEST TY      CTIME SQL_ID        EVENT                          LAST_CALL_ET
-------------------- ---------- ---------- ---------- ---------- -- ---------- ------------- ------------------------------ ------------
 Waiter:1:1304,35239     441288          0          0          6 SQ          2 dfycvr4vc0vg7 enq: SQ - contention                      1
....
 
 
3、具体sql及序列:
dfycvr4vc0vg7:
INSERT INTO tb_login_log201509 VALUES(login_log_seq.nextval,:1 ,:2 ,:3
,:4 ,:5 ,:6 )
Plan hash value: 3120637152
----------------------------------------------------------
| Id  | Operation                | Name          | Cost  |
----------------------------------------------------------
|   0 | INSERT STATEMENT         |               |     1 |
|   1 |  LOAD TABLE CONVENTIONAL |               |       |
|   2 |   SEQUENCE               | LOGIN_LOG_SEQ |       |
----------------------------------------------------------
2bbh14rwtytup
INSERT INTO tb_login_log VALUES(telinfo_seq.nextval,:1 ,:2 ,:3 ,:4 ,:5
,:6 )
Plan hash value: 4201880200
--------------------------------------------------------
| Id  | Operation                | Name        | Cost  |
--------------------------------------------------------
|   0 | INSERT STATEMENT         |             |     1 |
|   1 |  LOAD TABLE CONVENTIONAL |             |       |
|   2 |   SEQUENCE               | TELINFO_SEQ |       |
--------------------------------------------------------

SQL> set lines 200
SQL> select SEQUENCE_OWNER,SEQUENCE_NAME,INCREMENT_BY,CACHE_SIZE from dba_sequences where SEQUENCE_NAME in ('LOGIN_LOG_SEQ','TELINFO_SEQ');
SEQUENCE_OWNER                 SEQUENCE_NAME                  INCREMENT_BY CACHE_SIZE
------------------------------ ------------------------------ ------------ ----------
ECHN_UOP                       LOGIN_LOG_SEQ                             1       1000
ZYSOFT                         LOGIN_LOG_SEQ                             1       20
ZYSOFT                         TELINFO_SEQ                               1         20  <--------------序列cache设置不当
 
4、调整序列cache后,问题得到解决
 
SQL> alter sequence ZYSOFT.TELINFO_SEQ cache 1000;
Sequence altered.
SQL> 
SQL>
SQL> alter sequence ZYSOFT.LOGIN_LOG_SEQ cache 1000;
 
Sequence altered.

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

请登录后发表评论 登录
全部评论

注册时间:2014-10-11

  • 博文量
    86
  • 访问量
    246502