Very good information on database hang. But i have few questions on this:
-What does p1,p2,p3 represents in v$session_wait and what are their significanse here. - I have faced this situation many a times but i was not able to login to database when the hang occured, so what to do in that case, i.e how to generate dumps.
Aarya,You can use P1,P2,P3 values to diagnose the parameters for wait event. e.g You see event ‘latch free’ as considerable wait event, then
P1 = Latch address
P2 = Latch number
Now you can identify the latch name from V$LATCHNAME
SELECT * FROM v$latchname WHERE latch# =P2;
Moreover there can be situation where many sessions are waiting on same latch anf their P1 and P2 values are same say it points to Shared Pool Latch. From this we can be sure that some latching issue is causing the Database Hang.I will try to take up a case study sometime so that it can be more clear.And for your second question, you can refer to
Note 121779.1-Taking a SYSTEMSTATE dump when you cannot CONNECT to Oracle.
It is already mentioned in the post.In case of 10g, you can use prelim to connect to database.
e.g
sqlplus -prelim “/ as sysdba”
Amit
Saurabh Sood
leosanil
Hi Piyush,
Very good information on database hang.
But i have few questions on this:
-What does p1,p2,p3 represents in v$session_wait and what are their significanse here.
- I have faced this situation many a times but i was not able to login to database when the hang occured, so what to do in that case, i.e how to generate dumps.
Posted by Aarya | March 21, 2008, 2:36 pmAarya,You can use P1,P2,P3 values to diagnose the parameters for wait event. e.g You see event ‘latch free’ as considerable wait event, then
P1 = Latch address
P2 = Latch number
Now you can identify the latch name from V$LATCHNAME
SELECT * FROM v$latchname WHERE latch# =P2;
Moreover there can be situation where many sessions are waiting on same latch anf their P1 and P2 values are same say it points to Shared Pool Latch. From this we can be sure that some latching issue is causing the Database Hang.I will try to take up a case study sometime so that it can be more clear.And for your second question, you can refer to
Note 121779.1-Taking a SYSTEMSTATE dump when you cannot CONNECT to Oracle.
It is already mentioned in the post.In case of 10g, you can use prelim to connect to database.
e.g
sqlplus -prelim “/ as sysdba”
Cheers
Amit
Posted by Amit | April 5, 2008, 6:58 am