query is running slow --session wait query

Active session

select sid, serial#, username, status, sql_id,to_char(LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS') AS LOGON, event from v$session where username='&username';

SELECT sql_id, status, sql_text FROM v$sql_monitor WHERE  username='&username' and status not like '%DONE%';

monitor the sql execution plan whether its hangs or not .. execute two or three times to check the rows are changing

column plan_line_id format 9999 heading 'LINE'

column plan_options format a10 heading 'OPTIONS'

column status format a10

column output_rows heading 'ROWS'

break on sid on sql_id on status

SELECT sid, sql_id, status, plan_line_id,

plan_operation || ' ' || plan_options operation, output_rows

FROM v$sql_plan_monitor

WHERE status not like '%DONE%' and sid='&sid' <---

ORDER BY 1,4;

-- OR ---

column plan_line_id format 9999 heading 'LINE'

column plan_options format a10 heading 'OPTIONS'

column status format a10

column output_rows heading 'ROWS'

break on sid on sql_id on status

SELECT sid, sql_id, status, plan_line_id,

plan_operation || ' ' || plan_options operation, output_rows

FROM v$sql_plan_monitor

WHERE status not like '%DONE%'

ORDER BY 1,4;

Oracle current session wait

SET TIME ON VERIFY OFF
set linesize 200
COLUMN event FORMAT A30 HEADING 'Event' TRUNCATE
COLUMN wait_state FORMAT A7 HEADING 'State'
COLUMN seconds_in_wait FORMAT 999999999 HEADING 'Time|Waiting'
COLUMN wait_time FORMAT 999999 HEADING 'Time|Waited'
COLUMN p1 FORMAT A20 HEADING 'P1' TRUNCATE
COLUMN p2 FORMAT A20 HEADING 'P2' TRUNCATE
COLUMN p3 FORMAT A20 HEADING 'P3' TRUNCATE
col sid_serial format a15
col username format a20

SELECT vs.sid||','||vs.serial# sid_serial,
vs.username username,
vs.event,
CASE WHEN vs.state = 'WAITING' THEN vs.state
ELSE 'WAITED'
END wait_state,
vs.seconds_in_wait,
vs.wait_time,
DECODE(vs.p1text, NULL, NULL, vs.p1text||'='||vs.p1) p1,
DECODE(vs.p2text, NULL, NULL, vs.p2text||'='||vs.p2) p2,
DECODE(vs.p3text, NULL, NULL, vs.p3text||'='||vs.p3) p3
FROM v$session vs



3) REPORT_SQL_MONITOR in HTML (OR) TEXT format

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL report_sql_monitor.txt
SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => 'jjxdhjkk3vp', <--- SQLID
  type         => 'TEXT',         <--- HTML
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF




Oracle Response Time report


SET LINESIZE 132 PAGESIZE 100 FEEDBACK OFF
COLUMN time_sum NOPRINT
COLUMN total_time NOPRINT
COLUMN wait_class FORMAT A15 HEADING 'Class'
COLUMN event FORMAT A40 HEADING 'Event'
COLUMN wait_seconds FORMAT 999,999,999,999.99 HEADING 'Wait Seconds'
COLUMN wait_pct FORMAT 99.999999 HEADING '% of T' NOPRINT
COLUMN nonidle_pct FORMAT 99.999999 HEADING '% of R'

BREAK ON wait_class SKIP PAGE
COMPUTE SUM OF wait_seconds nonidle_pct ON wait_class

TTITLE LEFT "*** Response Time Components by Class ***"

SELECT vswc.time_waited time_sum,
ven.wait_class,
vse.event,
ROUND((vse.time_waited_micro/1000000),4) wait_seconds,
instime.time_waited total_time,
(ROUND((vse.time_waited_micro/1000000),6)/(instime.time_waited/100))*100 wait_pct,
TO_NUMBER(DECODE(vswc.wait_class, 'Idle', NULL, 'Network', NULL,
(ROUND((vse.time_waited_micro/1000000),6)/
((instime.time_waited - idletime.time_waited)/100))*100)) nonidle_pct
FROM v$system_event vse,
v$event_name ven,
v$system_wait_class vswc,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) instime,
(SELECT SUM(time_waited) time_waited
FROM v$system_wait_class
WHERE wait_class IN ('Idle', 'Network')) idletime
WHERE ven.event_id = vse.event_id
AND vswc.wait_class = ven.wait_class
UNION
SELECT vss.value time_sum,
'CPU Utilization' wait_class,
NULL,
vss.value/100 wait_seconds,
instime.time_waited total_time,
(ROUND((vss.value/100),6)/(instime.time_waited/100))*100 wait_pct,
((ROUND((vss.value/100),6)/((instime.time_waited - idletime.time_waited)/100))*100) nonidle_pct
FROM v$sysstat vss,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) instime,
(SELECT SUM(time_waited) time_waited
FROM v$system_wait_class
WHERE wait_class IN ('Idle', 'Network')) idletime
WHERE vss.name = 'CPU used when call started'
ORDER BY time_sum, wait_class, wait_seconds DESC
/

CLEAR BREAK
CLEAR COMPUTE

TTITLE LEFT "*** Response Time Components by Time ***"

SELECT vse.event,
ROUND((vse.time_waited_micro/1000000),4) wait_seconds,
(ROUND((vse.time_waited_micro/1000000),6)/((nonidletime.time_waited)/100))*100 nonidle_pct
FROM v$system_event vse,
v$event_name ven,
v$system_wait_class vswc,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
WHERE wait_class NOT IN ('Idle', 'Network')
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) nonidletime
WHERE ven.event_id = vse.event_id
AND vswc.wait_class = ven.wait_class
AND vswc.wait_class NOT IN ('Idle', 'Network')
UNION
SELECT 'CPU Utilization' wait_class,
vss.value/100 wait_seconds,
(ROUND((vss.value/100),6)/((nonidletime.time_waited)/100))*100 nonidle_pct
FROM v$sysstat vss,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
WHERE wait_class NOT IN ('Idle', 'Network')
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) nonidletime
WHERE vss.name = 'CPU used when call started'
ORDER BY wait_seconds DESC
/