enq: SQ - contention等待事件处理

由于数据库用户序列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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29953799/viewspace-1794490/