Because this session waiting for some event. First of all we go to tune the Query using EXPLAIN PLAN/EXECUTION PLAN.
After that also we faced same issue, we approach the below steps.
Steps:
1. We will check any objects are locked by particular sessions?
Select * from dba_waiters;
Select * from dba_blockers;
2. Respective query return value, we know easily particular session holding & waiting for what type of objects?
3. Pass the holding Sid, to find out what are the queries running by this Sid?
SELECT sql_text
FROM v$sqltext_with_newlines
WHERE address = (SELECT prev_sql_addr
FROM v$session
WHERE username = &uname AND SID = &snum);
ORDER BY piece;
Note: Pass the Username & holding Sid value.
4 Now we decide, "Kill the holding session or waiting session"? (Based on our business needs.)
Example: I have run the one EOD application. In this EOD application contains large number of DML operation.At the end of the EOD we have given the COMMIT. So due to COMMIT, while running the EOD application large number of Objects are locked. Once EOD completed objects are released. (Mostly EOD applications are running on non-peak hours).
Now I have executed the EOD application at 10 PM. It took 5 hour. At 12 AM another user connected to database try to update the some objects holded by EOD application session. (Now user session is hanging)
At that time I have checked any objects are locked. I have found out the Sid & decided which sessions to be killed. (EOD session or another session). I have checked out the EOD application will complete within 1 hour. Already it took 4hours. So I have decided to kill the user session. (Remember depend upon your business needs).
5. No locked objects belong to our query objects. So we find out how many sessions access my objects?
Example: I have executed one procedure PROC_TEST. How many users to access the PROC_TEST?
Using the below to find out the user.
Select
V$S.LOGON_TIME,
V$S.SID,
V$S.SERIAL#,
V$S.USERNAME,
V$S.PROCESS,
V$P.SPID,
V$S.STATUS,
V$S.MACHINE,
V$S.CLIENT_INFO
from v$session V$S,
v$process V$P
where V$S.PADDR = V$P.ADDR
and V$S.SID in
(Select distinct sid from v$access where object like ('%&obj%'))
order by
V$S.PROCESS,
V$S.SID;
6. We couldn’t identify the potential cause root of the query so we will go to the SQL TRACE.
7. Generate the Trace file of the query.
8. Using TKPROF to interpret the output for readability.
************************************************************
Last month, one of our clients reported to our Application team "particular screen response time is very slow"?
It’s a live database. They access the database through the front-end application (Lotus Domino).
Application team asks to DBA team "we need help to troubleshoot the issue".
So we decided to check the same screen in production like environment.
When a user enters some data & tries to save this data in same screen. At that we feel the response time is very slow.
We don't know what are the Queries used behind the screen?
When a user login through the front end application. At that we captured the SID (from v$SESSION) from database side.
Using SID to generate the trace file for the user.
Steps:
1. Login as front-end application user.
2. Generate the trace file for this session.
NOTE:
DBA's can use "DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (SID, SERIAL#, TRUE)" to trace problematic database sessions.
3. Enable the trace file Problematic database session.
Execute dbms_system.set_sql_trace_in_session (8, 13607, true);
4. Using TKPROF to identify the root cause.
A second way to set trace in another session - This time setting trace level as well
Next we can again use the DBMS_SYSTEM interface but this time uses the set event syntax.
This allows us to set any event in the database.
EXEC DBMS_System.Set_Ev --Used to set trace on for a specific event.
EXEC DBMS_System.Set_Ev (sid, serial#, event, level, name);
Steps:
EXEC DBMS_System.Set_Ev(31, 97, 10046, 4, '');
SQL> -- execute some code
Where level indicates the following levels of trace:
Level 0 = No statistics generated
Level 1 = standard trace output including parsing, executes and fetches plus more.
Level 2 = same as level 1.
Level 4 = same as level 1 but includes bind information
Level 8 = same as level 1 but includes wait's information
Level 12 = same as level 1 but includes binds and waits
This procedure allows you to set an event in any session for debugging purposes.
Common events to enable debugging are:
10032 sorts
10033 large sorts
10046 sql_trace / SQL statements (at level 1, equivalent to SQL_TRACE=TRUE)
10053 cost-based optimizer tracing
10104 hash join debugging
I Hope this article helped you to understand a session hang.Suggestions are welcome.
