| | To further troubleshoot "Stuck" sessions, you will have several options. Choose one. | | | |
| | | Use previously generated trace files | | | | Troubleshooting "Stuck" sessions can be done by taking a closer look at the data (hanganalyze, systemstate, and errorstack dumps) generated in the Identify the Issue > Data Collection and Analysis steps. Hanganalyze, systemstate, and errorstack dumps are great for collecting hang related data quickly, but understanding how to read these files can be complex. It is not really possible to present all methods for interpreting these files, but examples will be provided in the Determine a Cause > Analysis portion of the guide. In order to use this approach, you will need to have collected all the hanganalyze, systemstate, and errorstack dumps as requested in the Identify the Issue > Data Collection and Analysis steps. These dumps also must have been collected at the time that the sessions were stuck. No additional data needs to be collected. Continue to the Determine a Cause > Analysis step. Continue to the Determine a Cause > Analysis | | |
| | |
| | | Execute Queries on V$ Views | | | | Troubleshooting "Stuck" sessions can be done by using hanganalyze dumps generated in the Identify the Issue > Data Collection and Analysis steps in conjunction with data from V$ views. Some of the data gathered from V$ views is based on the blocker and waiter sessions. These blockers and waiter sessions were identified in the Identify the Issue > Analysis step. The stuck session must still be stuck when the data from the V$ views is collected. The queries below will prompt you for a SIDLIST. Use the blocker and waiter SIDs identified in the Identify the Issue > Analysis step when prompted. See the queries below for an example of how to enter this information when prompted. If you plan to continue with this approach do the following: | SPOOL v_lock.log; set linesize 200
column SID format 9999
column EVENT format a30
column P1 format a30
column P2 format a30
column P3 format a30
column WAIT_T format 9999
select SID,EVENT,P1TEXT||' '||P1 P1,P2TEXT||' '||P2 P2,P3TEXT||' '||P3 P3,SECONDS_IN_WAIT SEC_IN_WAIT
from v$session_wait
where sid in (&SIDLIST);
--example sidlist entry: 100,118,125 set linesize 80
select s.username, s.sid, s.module, t.sql_text
from v$session s, v$sql t
where s.sql_address =t.address and s.sql_hash_value =t.hash_value
and s.sid in (&SIDLIST);
--example sidlist entry: 100,118,125 set linesize 200
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request; SPOOL OFF; |
Verify that a file was creating in the current directory called v_lock.log. Examine the file to ensure that there is output from each query. Continue to the Determine a Cause > Analysis | | |
| | |
| | | Use OEM (10gR2) | | | | Troubleshooting "Stuck" sessions can be done by using the several OEM features. The stuck sessions must still be stuck when viewing the data via OEM. The stuck sessions must remain stuck until troubleshooting is complete, or screen shots should be saved to reference later in the Determine a Cause > Analysis step. Choose one of the features below as a means for locating specific information related to "Stuck" sessions. | | |
| | - Instance Locks
Use the Instance Locks page to display a list of all sessions currently blocking other sessions. The Instance Locks page displays a table of sessions listed by Username, Sessions Blocked, Session ID, and Session Serial Number. Use the Instance Locks page to: - View blocking locks, user locks, or all database locks.
- Kill the current session.
- Access the Session Details page.
- Access the SQL Details page.
- View object details.
Do the following to access the the Instance Locks: - click the "Performance" tab from the home page
- from the "Performance" page, click the "Instance Locks" link under "Additional Monitoring Links"
Use the "Help" feature in OEM for more details about each page. The Instance Locks page shows: - SID
- PID
- SQL Hash Value which links to the "SQL Details" page, Lock Type
- Mode Held
- Mode Requested
- Object Type
- Object Owner
- Object Name
You can click on the SID to navigate to the "Session Detail" page. The "Session Detail" page shows: - OSPID
- Current/Previous SQL Hash Value which links to the "SQL Details" page
- Blocking Session
- Current Wait Event
- P1,P2,P3
- Object owner.name
Later in the Determine a Cause > Analysis step we will focus on the "Session Detail" and "SQL Details" pages. Continue to the Determine a Cause > Analysis |
|
| | - Blocking Sessions
Use the Blocking Sessions page to display a list of all sessions currently blocking other sessions. The Blocking Sessions page displays a table of sessions listed by Sessions Blocked, Session ID, and Session Serial Number. Do the following to access the the Instance Locks: - click the "Performance" tab from the home page
- from the "Performance" page, click the "Blocking Sessions" link under "Additional Monitoring Links"
Use the "Help" feature in OEM for more details. The "Blocking Sessions" page shows: - SID
- SQL Hash Value which links to the "SQL Details" page
- Wait Event
- P1, P2, P3
You can click on the SID to navigate to the "Session Detail" page. The "Session Detail" page shows: - OSPID
- Current/Previous SQL Hash Value which links to the "SQL Details" page
- Blocking Session
- Current Wait Event
- P1,P2,P3
- Object owner.name
Later in the Determine a Cause > Analysis step we will focus on the "Session Detail" and "SQL Details" pages. Continue to the Determine a Cause > Analysis |
|
| | |
| | | Use utllockt.sql Script | | | | The utllockt.sql script. displays, in a tree fashion, the sessions in the system that are waiting for locks and the locks that they are waiting for. The location of this script. file is operating system dependent. (You must have run the CATBLOCK.SQL script. before using UTLLOCKT.SQL.) . See MetaLink Note 166534.1 for more information. utllockt.sql will display output to the screen. It is a good idea to spool the output to the file "utllockt.out" prior to running utllockt.sql. This produces a file that can be referred to in later steps. If you choose to spool the output, verify that a file is created and examine the file to ensure that the output is similar to the example. WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2
---------------- ---------- -------------- ---------- --------- --------
blocker-> 100 None
waiter ---> 118 DML Exclusive Exclusive 51148 0
waiter ---> 125 DML Row-X (SX) Exclusive 51148 0
|
Once you have identified the blocking and waiting sessions using utllockt.sql, run the following queries. The queries below will prompt you for a SIDLIST. Use the blocker and waiter SIDs identified in the Identify the Issue > Analysis step when prompted. See the queries below for an example of how to enter this information when prompted. | SPOOL v_lock.log; set linesize 200
column SID format 9999
column EVENT format a30
column P1 format a30
column P2 format a30
column P3 format a30
column WAIT_T format 9999
select SID,EVENT,P1TEXT||' '||P1 P1,P2TEXT||' '||P2 P2,P3TEXT||' '||P3 P3,SECONDS_IN_WAIT SEC_IN_WAIT
from v$session_wait
where sid in (&SIDLIST);
--example sidlist entry: 100,118,125 set linesize 80
select s.username, s.sid, s.module, t.sql_text
from v$session s, v$sql t
where s.sql_address =t.address and s.sql_hash_value =t.hash_value
and s.sid in (&SIDLIST);
--example sidlist entry: 100,118,125 SPOOL OFF; |
Verify that a file was created in the current directory called v_lock.log. Examine the file to ensure that there is output from each query. Continue to the Determine a Cause > Analysis | | |
| | |
| |