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
nice
ReplyDeleteI 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.
ReplyDeleteThanks 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