-- ----------------------------------------------------------------------
# Step by Step: How to troubleshoot a slow running query in Oracle
- what are sessions doing
set lines 999
col osuser form a10
col username form a10
col spid form a10
select a.sid, a.serial#,
spid ,
sql_id,
a.inst_id,
a.osuser,
a.username, status,
substr(to_char(logon_time,'dd-mon-yy hh24:mi'),1,18) logon_time,
substr(to_char(sql_exec_start,'dd-mon-yy hh24:mi'),1,18) sql_exec_start,
substr(a.module,1,18) module ,
substr(machine,1,17) machine,
event
-- substr(a.program,1,16) program
from gv$session a, gv$process b
where a.username not in (' ','PATROL','SYS','DBSNMP')
and b.addr=a.paddr
and b.inst_id=a.inst_id
and a.status='ACTIVE'
-- and event='inactive transaction branch'
order by status desc, logon_time
/
-- blockers in standalone
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ') is blocking'
|| s2.username || '@' || s2.machine || '( SID=' || s2.sid || ')' from
v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.block=1 and l2.request > 0
and l1.id1=l2.id1
and l2.id2=l2.id2;
-- blockers in rac
set pages 1000
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' Serial#= '||s1.serial# ||' Inst=' ||s1.INST_ID ||' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
-- top
get pid
-- get sid using pid
select sid
from v$session s, v$process p
where p.spid = &pid
and s.paddr = p.addr;
-- get sqlId using sid
select sql_id
from v$session
where sid = &sid;
- get sql using sid
select sql_fulltext
from v$sql l, v$session s
where s.sid = &sid
and l.sql_id = s.sql_id;
# Check for the wait events:
-- Check for the particular user and session.
col "Description" format a50
select sid,
decode(state, 'WAITING','Waiting',
'Working') state,
decode(state,
'WAITING',
'So far '||seconds_in_wait,
'Last waited '||
wait_time/100)||
' secs for '||event
"Description"
from v$session
where username = 'GOS_USER';
select SID, osuser, machine, terminal, service_name,
logon_time, last_call_et
from v$session
where username = 'GOS_USER';
-- Session waits for a specific machine
col username format a5
col program format a10
col state format a10
col last_call_et head 'Called|secs ago' format 999999
col seconds_in_wait head 'Waiting|for secs' format 999999
col event format a50
select sid, username, program,
decode(state, 'WAITING', 'Waiting',
'Working') state,
last_call_et, seconds_in_wait, event
from v$session
where sid = &sid
/
History of wait events in a specific session
set lines 120 trimspool on
col event head "Waited for" format a30
col total_waits head "Total|Waits" format 999,999
col tw_ms head "Waited|for (ms)" format 999,999.99
col aw_ms head "Average|Wait (ms)" format 999,999.99
col mw_ms head "Max|Wait (ms)" format 999,999.99
select event, total_waits, time_waited*10 tw_ms,
average_wait*10 aw_ms, max_wait*10 mw_ms
from v$session_event
where sid = &sid
/
-- check data access isue
select event
from v$session
where sid = &sid;
-- check data access waits
select SID, state, event, p1, p2
from v$session
where sid = &sid;
-- run sql advisor
1. Tuning task created for specific Sql id:
SET SERVEROUTPUT ON
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => 'bxa13by3718uw',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'bxa13by3718uw_tuning_task',
description => 'Tuning task for statement bxa13by3718uw.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
2. Executing the tuning task:
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'bxa13by3718uw_tuning_task');
3. Displaying the recommendations:
Once the tuning task has executed successfully the recommendations can be displayed using the REPORT_TUNING_TASK function.
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('bxa13by3718uw_tuning_task') AS recommendations FROM dual;
SET PAGESIZE 24
Based on the tuning advisor recommendation we have to take corrective actions. These recommendation could be:
1) Gather Statistics
2) Create Index
3) Drop Index
4) Join orders
5) Create sql profiles and many more
After corrective action from tuning advisor run the SQL again and see the improvement.
