Wednesday, September 16, 2009

Oracle import hang with "latch free" wait event more than 8 hours

Facts:

Oracle 8.1.7.0 + Solaris 9

SQL> select event,p1raw,p2raw,seconds_in_wait from v$session_wait where sid=11;

EVENT P1RAW P2RAW SECONDS_IN_WAIT
------------------------------ -------- -------- ---------------
latch free 80002B20 00000002 29386


Findings


SQL> select sql_hash_value,sql_address from v$session where sid=11;

SQL_HASH_VALUE SQL_ADDR
-------------- --------
2329822092 99765838

SQL> select sql_text from v$sqlarea where hash_value='2329822092'
2 and address='99765838';

SQL_TEXT
--------------------------------------------------------------------------------
AUDIT AUDIT,COMMENT,DELETE,GRANT,INSERT,LOCK,RENAME,SELECT,UPDATE ON "VIEW_IMXX
P_WORKLISTS" BY ACCESS



SQL> select name,'child 'child# child,gets,misses,sleeps from v$latch_children
2 where addr='&p1raw'
3 union
4 select name,null,gets,misses,sleeps from v$latch
5 where addr='&p1raw';
Enter value for p1raw: 80002B20
old 2: where addr='&p1raw'
new 2: where addr='80002B20'
Enter value for p1raw: 80002B20
old 5: where addr='&p1raw'
new 5: where addr='80002B20'

NAME CHILD GETS MISSES SLEEPS
------------------------------ ---------- ---------- ---------- ----------
session allocation 19579180 121 24


How to resolve:

I tried to analyze why "session allocation" latch has requested 19579180+121 times, but no root cause discovered.

Then i realize this is a view, and many other views are invalid. I recomplied all the invalid views, including this one. After that, problem disappears!

Franking speaking, I do not have the answer, and it's not re-producible.

The End:


SQL> select event,p1raw,p2raw,seconds_in_wait from v$session_wait where sid=11;

EVENT P1RAW P2RAW SECONDS_IN_WAIT
------------------------------ -------- -------- ---------------
log file sync 00000EED 00 0