History | Log In     View a printable version of the current page.  
Issue Details (XML | Word | Printable)

Key: QA-15
Type: Oracle - Database Tuning Oracle - Database Tuning
Status: Closed Closed
Resolution: Answered
Priority: Major Major
Assignee: ubTools Support
Reporter: ubTools Support
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Questions & Answers

SQ enqueue problem.

Created: 15/Jul/07 02:18 PM   Updated: 16/Sep/07 04:28 PM
Return to search
Fix Version/s: None

Product Version: 10.1.0.3
Operating System: Generic


 Description  « Hide
Other than SYSDBA, no new connections allowed to the database.

 All   Comments   Change History      Sort Order: Ascending order - Click to sort in descending order
ubTools Support - 15/Jul/07 02:32 PM

An excerpt from SYSTEMSTATE dump:

The SYSTEMSTATE dump was generated in USER_DUMP_DEST as below:

 
SQL> connect / as sysdba
SQL> alter session set max_dump_file_size=UNLIMITED;
SQL> alter session set events 'IMMEDIATE trace name SYSTEMSTATE level 10';
-- 2 or 3 minutes later
SQL> alter session set events 'IMMEDIATE trace name SYSTEMSTATE level 10';

There are many sessions in SYSTEMSTATE dump waiting for enq: SQ - contention as below:

(session) trans: 0, creator: 41b5c31f8, flag: (e1) USR/- BSY/////-
DID: 0001-0057-00000021, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 0, prv: 0, sql: 0, psql: 0, user: 0/SYS
O/S info: user: , term: , ospid: , machine:
program:
waiting for 'enq: SQ - contention' blocking sess=0x4234f5b68 seq=1 wait_time=0
name|mode=53510006, object #=8e, 0=0
Dumping Session Wait History
for 'enq: SQ - contention' count=1 wait_time=3007641
name|mode=53510006, object #=8e, 0=0
for 'enq: SQ - contention' count=1 wait_time=3007783
...
SO: 40e830a70, type: 54, owner: 4234e5f20, flag: INIT///0x00
LIBRARY OBJECT LOCK: lock=40e830a70 handle=41985e8b8 mode=N
call pin=41a6d2740 session pin=0 hpc=0000 hlc=0000
htl=40e830ae0[40e8308e8,40e6833d8] htb=40e8d2cc8
user=4234e5f20 session=4234e5f20 count=1 flags=PNC/[0400] savepoint=2
LIBRARY OBJECT HANDLE: handle=41985e8b8
name=SYS.AUDSES$
hash=deaeba50687d3d62c586aafe9b84f98c timestamp=07-20-2004 15:34:57
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=S latch#=34 hpc=022e hlc=022e

The blocking session(0x4234f5b68):

SO: 4234f5b68 , type: 4, owner: 41b5c8c60, flag: INIT///0x00
(session) trans: 41cde12f8, creator: 41b5c8c60, flag: (e1) USR/- BSY/////-
DID: 0001-0056-00000020, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 0, prv: 0, sql: 0, psql: 0, user: 0/SYS
O/S info: user: , term: , ospid: , machine:
program:
waiting for 'gc cr request' blocking sess=0x0 seq=2 wait_time=0
file#=1, block#=1ea, class#=1
Dumping Session Wait History
for 'gc cr request' count=1 wait_time=1230415
file#=1, block#=1ea, class#=1
for 'gc cr request' count=1 wait_time=1230287
file#=1, block#=1ea, class#=1
for 'gc cr request' count=1 wait_time=1220829
file#=1, block#=1ea, class#=1
for 'gc cr request' count=1 wait_time=1230501
file#=1, block#=1ea, class#=1
for 'gc cr request' count=1 wait_time=1230312
file#=1, block#=1ea, class#=1
for 'gc cr request' count=1 wait_time=1230295
file#=1, block#=1ea, class#=1
for 'gc cr request' count=1 wait_time=1230618
file#=1, block#=1ea, class#=1
for 'gc cr request' count=1 wait_time=1230445
file#=1, block#=1ea, class#=1
for 'gc cr request' count=1 wait_time=1229421
file#=1, block#=1ea, class#=1
for 'gc cr request' count=1 wait_time=1231336
file#=1, block#=1ea, class#=1
temporary object counter: 0

Problem interpretation:

The blocker session waited for a RAC related wait event named gc cr request for the same file# and block#. Unfortunately, at the time of the problem happened, no SYSTEMSTATE dumps were generated for the other nodes. So, it was not possible to diagnose the root blocker on the other node to find why it holds same buffer too long.

The sessions were waiting for SQ enqueue on SYS.AUDSES$ sequence. During connection, the value of V$SESSION.AUDSID is obtained from SYS.AUDSES$ sequence. SYSDBA doesn't use this sequence in connection. So, it was not blocked.

Solution:

The default cache size of SYS.AUDSES$ was 20. It has been increased to 1000.