What is happening in DB
select event, state, count(*) from v$session_wait group by event, state order by 3 desc;
select count(*),
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END AS state,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END AS sw_event
FROM v$session -- use session_wait to include background processes and idle sessions
where type = 'USER' AND status = 'ACTIVE' -- exclude background prcocesses and idle sessions
GROUP BY CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END
ORDER BY 1 DESC, 2 DESC;
Long Running Queries - SQL Monitor
set lines 3000
col username form a15
clear columns
SELECT *
FROM
(SELECT status,
username,
sql_id,
sql_exec_id,
PX_SERVERS_ALLOCATED Parallelism,
TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
ROUND(elapsed_time/1000000) AS "Elapsed (s)",
ROUND(cpu_time /1000000) AS "CPU (s)",
buffer_gets,
ROUND(physical_read_bytes /(1024*1024)) AS "Phys reads (MB)",
ROUND(physical_write_bytes/(1024*1024)) AS "Phys writes (MB)",
RM_CONSUMER_GROUP
FROM v$sql_monitor
ORDER BY elapsed_time DESC
)
WHERE rownum<=20;
Long Running Operations
select * from v$session_longops
where time_remaining > 0;
select OPNAME, SOFAR, TOTALWORK, SOFAR/TOTALWORK*100, TIME_REMAINING
from v$session_longops
where time_remaining > 0
order by TIME_REMAINING
Identify slow SQL in Shared Pool
select sql_id, child_number, executions,
elapsed_time/executions/1000000 elap_sec_per_exec
from v$sql
where executions > 0
and last_active_time > (sysdate - 1/24)
and elapsed_time/executions/1000000 > 10;
Show the Bind Variable for a Given SQLID
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN sql_text FORMAT A120
COLUMN sql_id FORMAT A13
COLUMN bind_name FORMAT A10
COLUMN bind_value FORMAT A26
SELECT
sql_id,
t.sql_text sql_text,
b.name bind_name,
b.value_string bind_value
FROM
v$sql t
JOIN
v$sql_bind_capture b using (sql_id)
WHERE
b.value_string is not null
AND
sql_id='&sqlid'
HOME