Home > Oracle, Performance > enq: SQ – contention

enq: SQ – contention


Yesterday, I have seen huge waits “enq SQ – contention’”  – in every snapshot there were thousands of waits.  But the fix was so simple!  Here is the root cause of the issue –

When you select from a sequence, the NEXTVAL generated from a the seq$ table if it is not cached.  If it is cached, it will be available in a memory structure and no need to generate the value which is a costly affair under the wait enq SQ – contention.  Below test shows what will happen for this wait from NOCACHE to CACHE 1000 NEXTVALs.

I have executed the below SQL from 20 concurrent sessions..

<pre>
declare

 col1 number;

begin

 for a in 1..20000 loop

  select s.nextval into col1 from dual;

 end loop;

end;

/

Used below statements to run this SQL from 20 different sessions.

<pre>nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

nohup sqlplus abc/abc @s &

With NOCACHE


SQL> create sequence s;

select count(*),sum(time_waited)

from v$active_session_history

where event='enq: SQ - contention' and

to_char(SAMPLE_TIME,'DDMONHH24')='09JAN04' and to_char(SAMPLE_TIME,'MI') in (14,15,16,17,18,19,20);


  COUNT(*) SUM(TIME_WAITED)

---------- ----------------

      2336       1677503813

 

With CACHE 50

 


SQL> alter sequence s cache 50;

Sequence altered.


SQL> select count(*),sum(time_waited)

from v$active_session_history

where event='enq: SQ - contention' and

to_char(SAMPLE_TIME,'DDMONHH24')='09JAN04' and to_char(SAMPLE_TIME,'MI') in (21,22,23,24,25,26,27);



  COUNT(*) SUM(TIME_WAITED)

---------- ----------------

       701        459026341

 

With CACHE 500


SQL> alter sequence s cache 500;



Sequence altered.



SQL>

select count(*),sum(time_waited)

from v$active_session_history

where event='enq: SQ - contention' and

to_char(SAMPLE_TIME,'DDMONHH24')='09JAN04' and to_char(SAMPLE_TIME,'MI') in (23,24,25,26,27);



  COUNT(*) SUM(TIME_WAITED)

---------- ----------------

       192         96392664

 

 

With CACHE 1000


SQL>  alter sequence s cache 1000 ;



Sequence altered.



SQL> select count(*),sum(time_waited)

from v$active_session_history

where event='enq: SQ - contention' and

to_char(SAMPLE_TIME,'DDMONHH24')='09JAN04' and to_char(SAMPLE_TIME,'MI') in (29,30,31,32,33,34,35,36,37);



  COUNT(*) SUM(TIME_WAITED)

---------- ----------------

        84         23352139

 

 

With huge concurrency, the number of waits comes down from 2336 (time:1677503813 ) to 84 (time: 23352139)  by adjusting the sequence CACHE.

 

Version:

 


SQL> select * from v$version;



BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Solaris: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production



Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: