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;
/