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

5 comments:

  1. I love Kidandali Music so much. My father and I listen to this song every day. I was thinking to go to the concerts. Can anybody tell me how can get this concert ticket? My friend told me to visit concerts near me. Here I can find the best tickets at a low price.

    ReplyDelete
  2. Thanks for posting this info. I just want to let you know that I just check out your site and I find it very interesting and informative. I can't wait to read lots of your posts. Royalty Free Music

    ReplyDelete
  3. Thanks For sharing this Superb article.I use this Article to show my assignment in college.it is useful For me Great Work. Music School In Kolkata

    ReplyDelete
  4. Not every one of the songs accessible here are free, yet there are a few songs that can be purchased as well if necessary.
    https://www.spotifyfame.com/

    ReplyDelete