Oracle SQL Troubleshooting Queries



When a performance issue arises, these queries help DBAs trace SQL activity from the session to the actual SQL text and plan.

Find Sessions Running in Database

-- Active sessions only
SELECT sid, serial#, username, status, machine, program, sql_id
FROM   v$session
WHERE  status = 'ACTIVE'
AND    username IS NOT NULL;

Find SQL_ID for a Given SID
SELECT sql_id, sql_child_number, sql_hash_value
FROM   v$session
WHERE  sid = &SID;

Get SQL Text for a SQL_ID

SET LONG 100000
SET PAGESIZE 1000
SET LINESIZE 200
SET TRIMOUT ON
SET TRIMSPOOL ON

COLUMN sql_fulltext FORMAT A200 WORD_WRAPPED
SELECT sql_fulltext
FROM   v$sql
WHERE  sql_id = '&SQL_ID';

Get Execution Plan for SQL_ID
SELECT * 
FROM   table(DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID', NULL, 'ALLSTATS LAST'));

Get Bind Variables Used by a SQL

SELECT name, position, datatype_string, value_string
FROM   v$sql_bind_capture
WHERE  sql_id = '&SQL_ID'
ORDER  BY position;

Get Currently Running SQLs with Elapsed Time

SELECT s.sid, s.serial#, s.username, s.sql_id, q.sql_text,
       q.elapsed_time/1000000 elapsed_sec, q.executions
FROM   v$session s
JOIN   v$sql q ON s.sql_id = q.sql_id
WHERE  s.status = 'ACTIVE'
AND    s.username IS NOT NULL;


Find Historical SQL (from AWR / DBA_HIST)
SELECT sql_id, parsing_schema_name, elapsed_time_total/1000000 
elapsed_sec,
       cpu_time_total/1000000 cpu_sec, executions_total, module
FROM   dba_hist_sqlstat
WHERE  sql_id = '&SQL_ID'
ORDER  BY snap_id DESC;


Find Blocking Sessions and Their SQL
SELECT s.sid, s.serial#, s.username, s.blocking_session,
       s.sql_id, q.sql_text
FROM   v$session s
LEFT JOIN v$sql q ON s.sql_id = q.sql_id
WHERE  s.blocking_session IS NOT NULL;


Find Top SQL by Resource Usage
SELECT sql_id, plan_hash_value,
       executions, buffer_gets, disk_reads, cpu_time/1000000 cpu_sec,
       elapsed_time/1000000 elapsed_sec, sql_text
FROM   v$sqlarea
WHERE  executions > 0
AND rownum <11
ORDER  BY elapsed_time DESC;



Kill a Session (if required)
ALTER SYSTEM KILL SESSION '&SID,&SERIAL#' IMMEDIATE;






Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment