Essential Oracle SQL Queries for Performance Monitoring and Troubleshooting

1. Identify tables with high fragmentation

Tables with many allocated blocks but low actual usage are flagged by filtering USER_TABLES where BLOCKS > 100 and the calculated usage ratio is below 30%.

SELECT TABLE_NAME,
       (BLOCKS*8192/1024/1024) "Theoretical Size MB",
       (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9) "Actual Size MB",
       ROUND((NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024),3)*100||'%' "Actual Usage %"
FROM USER_TABLES
WHERE BLOCKS > 100
  AND (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024) < 0.3
ORDER BY (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024) DESC;

2. Check index fragmentation percentage

SELECT name,
       del_lf_rows,
       lf_rows,
       ROUND(del_lf_rows/DECODE(lf_rows,0,1,lf_rows)*100,0)||'%' frag_pct
FROM index_stats
WHERE ROUND(del_lf_rows/DECODE(lf_rows,0,1,lf_rows)*100,0) > 30;

3. Find tables with high clustering factor

A clustering factor close to the number of rows indicates poor index selectivity and may cause full‑table scans.

SELECT tab.table_name,
       tab.blocks,
       tab.num_rows,
       ind.index_name,
       ind.clustering_factor,
       ROUND(NVL(ind.clustering_factor,1)/DECODE(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "Clustering Factor vs Rows"
FROM user_tables tab
JOIN user_indexes ind ON tab.table_name = ind.table_name
WHERE tab.blocks > 100
  AND NVL(ind.clustering_factor,1)/DECODE(tab.num_rows,0,1,tab.num_rows) BETWEEN 0.35 AND 3;

4. Map SID to SPID (and vice‑versa)

SELECT s.sid, s.serial#, s.logon_time, s.machine, p.spid, p.terminal
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.sid = :sid OR p.spid = :spid;

5. Retrieve the SQL text for a given SID

SELECT username,
       sql_text,
       machine,
       osuser
FROM v$session a
JOIN v$sqltext_with_newlines b ON DECODE(a.sql_hash_value,0,prev_hash_value,sql_hash_value) = b.hash_value
WHERE a.sid = :sid
ORDER BY piece;

6. Retrieve the SQL text for a given SPID

SELECT ss.sid,
       pr.spid,
       ss.action,
       sa.sql_fulltext,
       ss.terminal,
       ss.program,
       ss.serial#,
       ss.username,
       ss.status,
       ss.osuser,
       ss.last_call_et
FROM v$process pr
JOIN v$session ss ON pr.addr = ss.paddr
JOIN v$sqlarea sa ON ss.sql_address = sa.address AND ss.sql_hash_value = sa.hash_value
WHERE pr.spid = :spid
  AND ss.status = 'ACTIVE'
  AND ss.username IS NOT NULL;

7. Find the IP address of a historical session

Use V$ACTIVE_SESSION_HISTORY to locate the machine name, then resolve it via OS tools or network queries.

SELECT a.sql_id,
       a.machine,
       a.*
FROM v$active_session_history a
WHERE a.session_id = :sid
  AND a.session_serial# = :serial#;

8. Locate DML lock sessions and their blockers

SELECT sid,
       blocking_session,
       logon_time,
       sql_id,
       status,
       event,
       seconds_in_wait,
       state,
       blocking_session_status
FROM v$session
WHERE event LIKE 'enq%'
  AND state = 'WAITING'
  AND blocking_session_status = 'VALID';

Blocker sessions typically have an earlier LOGON_TIME. In RAC environments, query GV$SESSION and kill the session on the appropriate instance.

9. Query DDL lock information

SELECT sid,
       event,
       p1raw,
       seconds_in_wait,
       wait_time
FROM sys.v_$session_wait
WHERE event LIKE 'library cache %';

SELECT s.sid,
       kglpnmod "Mode",
       kglpnreq "Req",
       s.logon_time
FROM x$kglpn p
JOIN v$session s ON p.kglpnuse = s.saddr
WHERE kglpnhdl = :handle;

10. Find DDL objects currently locked

SELECT d.session_id,
       s.serial#,
       d.name
FROM dba_ddl_locks d
JOIN v$session s ON d.owner = 'MKLMIGEM' AND d.session_id = s.sid;

11. List currently executing SQL statements

SELECT s.sid,
       s.serial#,
       s.username,
       p.spid,
       v$sql.sql_id,
       s.machine,
       s.terminal,
       s.program,
       sql_text
FROM v$process p
JOIN v$session s ON p.addr = s.paddr
JOIN v$sql ON s.sql_id = v$sql.sql_id
WHERE s.sql_hash_value = v$sql.hash_value;

12. Show running Scheduler jobs

SELECT owner,
       job_name,
       sid,
       b.serial#,
       b.username,
       p.spid
FROM all_scheduler_running_jobs j
JOIN v$session b ON j.session_id = b.sid
JOIN v$process p ON b.paddr = p.addr;

13. Show running DBMS_JOB jobs

SELECT job,
       b.sid,
       b.serial#,
       b.username,
       p.spid
FROM dba_jobs_running a
JOIN v$session b ON a.sid = b.sid
JOIN v$process p ON b.paddr = p.addr;

14. Calculate average memory consumption per session/process

SELECT ROUND(SUM(pga_used_mem)/1024/1024,0) total_used_M,
       ROUND(SUM(pga_used_mem)/COUNT(1)/1024/1024,0) avg_used_M,
       ROUND(SUM(pga_alloc_mem)/1024/1024,0) total_alloc_M,
       ROUND(SUM(pga_alloc_mem)/COUNT(1)/1024/1024,0) avg_alloc_M
FROM v$process;

15‑18. Top‑10 SQL statements by executions, physical reads, logical reads, and CPU time

-- Executions
SELECT *
FROM (SELECT executions,
             username,
             parsing_user_id,
             sql_id,
             sql_text
      FROM v$sql dba_users u
      WHERE u.user_id = parsing_user_id
      ORDER BY executions DESC)
WHERE ROWNUM <= 5;

-- Physical reads
SELECT *
FROM (SELECT disk_reads,
             username,
             parsing_user_id,
             sql_id,
             elapsed_time/1000000,
             sql_text
      FROM v$sql dba_users u
      WHERE u.user_id = parsing_user_id
      ORDER BY disk_reads DESC)
WHERE ROWNUM <= 5;

-- Logical reads
SELECT *
FROM (SELECT buffer_gets,
             username,
             parsing_user_id,
             sql_id,
             elapsed_time/1000000,
             sql_text
      FROM v$sql dba_users u
      WHERE u.user_id = parsing_user_id
      ORDER BY buffer_gets DESC)
WHERE ROWNUM <= 5;

-- CPU time
SELECT *
FROM (SELECT cpu_time/1000000,
             username,
             parsing_user_id,
             sql_id,
             elapsed_time/1000000,
             sql_text
      FROM v$sql dba_users u
      WHERE u.user_id = parsing_user_id
      ORDER BY cpu_time/1000000 DESC)
WHERE ROWNUM <= 5;

19. List wait events and their frequencies

SELECT event,
       SUM(DECODE(wait_time,0,0,1)) "Previous Wait Count",
       SUM(DECODE(wait_time,0,1,0)) "Current Wait Count",
       COUNT(*)
FROM v$session_wait
GROUP BY event
ORDER BY 4 DESC;

20. Find SQL statements consuming temporary tablespace

SELECT DISTINCT se.username,
                se.sid,
                su.blocks * TO_NUMBER(RTRIM(p.value))/1024/1024 AS space_G,
                su.tablespace,
                sql_text
FROM v$tempseg_usage su
JOIN v$parameter p ON p.name = 'db_block_size'
JOIN v$session se ON su.session_addr = se.saddr
JOIN v$sql s ON su.sqlhash = s.hash_value AND su.sqladdr = s.address;

21. Detect frequently executed SQL that should use bind variables

Two approaches are provided: counting identical statements and using FORCE_MATCHING_SIGNATURE to locate statements executed more than ten times.

-- Approach 1
SELECT * FROM (
  SELECT COUNT(*), sql_id, SUBSTR(sql_text,1,40)
  FROM v$sql
  GROUP BY sql_id, SUBSTR(sql_text,1,40)
  HAVING COUNT(*) > 10
  ORDER BY COUNT(*) DESC)
WHERE ROWNUM < 10;

-- Approach 2
SELECT sql_id,
       FORCE_MATCHING_SIGNATURE,
       sql_text
FROM v$sql
WHERE FORCE_MATCHING_SIGNATURE IN (
  SELECT /*+ unnest */ FORCE_MATCHING_SIGNATURE
  FROM v$sql
  WHERE FORCE_MATCHING_SIGNATURE > 0
    AND FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
  GROUP BY FORCE_MATCHING_SIGNATURE
  HAVING COUNT(1) > 10);

22‑25. Tablespace and datafile usage statistics

-- Datafile size and free space (22)
SELECT b.file_id,
       b.tablespace_name,
       b.file_name,
       b.autoextensible,
       ROUND(b.bytes/1024/1024/1024,2) ||'G' "Total Size",
       ROUND((b.bytes - SUM(NVL(a.bytes,0)))/1024/1024/1024,2) ||'G' "Used",
       ROUND(SUM(NVL(a.bytes,0))/1024/1024/1024,2) ||'G' "Free",
       ROUND(SUM(NVL(a.bytes,0))/b.bytes,2)*100||'%' "Free %"
FROM dba_free_space a,
     dba_data_files b
WHERE a.file_id = b.file_id
GROUP BY b.tablespace_name,b.file_name,b.file_id,b.bytes,b.autoextensible
ORDER BY b.tablespace_name;

-- Tablespace free % (24)
SELECT b.tablespace_name,
       a.total,
       b.free,
       ROUND((b.free/a.total)*100) "% Free"
FROM (SELECT tablespace_name, SUM(bytes/(1024*1024)) total FROM dba_data_files GROUP BY tablespace_name) a,
     (SELECT tablespace_name, ROUND(SUM(bytes/(1024*1024))) free FROM dba_free_space GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
ORDER BY "% Free";

-- Temporary tablespace usage (25)
SELECT temp_used.tablespace_name,
       total,
       used,
       total - used AS "Free",
       ROUND(NVL(total-used,0) * 100/total,3) "Free percent"
FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used FROM gv_$temp_space_header GROUP BY tablespace_name) temp_used,
     (SELECT tablespace_name, SUM(bytes)/1024/1024 total FROM dba_temp_files GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name;

26. Undo tablespace usage

SELECT tablespace_name,
       status,
       SUM(bytes)/1024/1024 M
FROM dba_undo_extents
GROUP BY tablespace_name,status;

27. ASM disk group utilization

SELECT name,
       ROUND(total_mb/1024) "Total GB",
       ROUND(free_mb/1024) "Free GB",
       ROUND((free_mb/total_mb)*100) "Free %"
FROM gv$asm_diskgroup;

28. Per‑user tablespace consumption

SELECT c.owner "User",
       a.tablespace_name "Tablespace",
       total/1024/1024 "Size MB",
       free/1024/1024 "Free MB",
       (total - free)/1024/1024 "Used MB",
       ROUND((total - free)/total,4)*100 "Usage %",
       c.schemas_use/1024/1024 "User Size MB",
       ROUND(c.schemas_use/total,4)*100 "User %"
FROM (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a,
     (SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b,
     (SELECT owner, tablespace_name, SUM(bytes) schemas_use FROM dba_segments GROUP BY owner, tablespace_name) c
WHERE a.tablespace_name = b.tablespace_name
  AND a.tablespace_name = c.tablespace_name
ORDER BY "User","Tablespace";

29. Flashback/recovery area usage

SELECT SUM(percent_space_used)||'%' "Used %" FROM v$recovery_area_usage;

30. Detect zombie processes

Two queries: one finds processes without a matching session, the other finds processes whose session status is KILLED.

-- Processes without a session
SELECT * FROM v$process WHERE addr NOT IN (SELECT paddr FROM v$session) AND pid NOT IN (1,17,18);

-- Sessions marked KILLED but still present
SELECT * FROM v$process WHERE addr IN (SELECT paddr FROM v$session WHERE status='KILLED');

31. Find tables with row migration or chaining

SELECT * FROM dba_tables WHERE NVL(chain_cnt,0) <> 0;

32. Buffer cache hit ratio

SELECT ROUND(100*(1 - c.value/(a.value+b.value)),2)||'%' hit_ratio
FROM v$sysstat a,
     v$sysstat b,
     v$sysstat c
WHERE a.name='db block gets'
  AND b.name='consistent gets'
  AND c.name='physical reads';

-- Alternative
SELECT DB_BLOCK_GETS+CONSISTENT_GETS Logical_reads,
       PHYSICAL_READS phys_reads,
       ROUND(100*(1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS))),2)||'%' "Hit Ratio"
FROM v$buffer_pool_statistics
WHERE name='DEFAULT';

33. Library cache hit ratio

SELECT SUM(pinhits)/SUM(pins)*100 FROM v$librarycache;
SELECT SUM(pinhits-reloads)/SUM(pins)*100 FROM v$librarycache;

34. Archive log switch frequency

SELECT sequence#,
       TO_CHAR(first_time,'yyyymmdd_hh24:mi:ss') first_time,
       ROUND((first_time - LAG(first_time) OVER (ORDER BY first_time))*24*60,2) minutes
FROM v$log_history
WHERE first_time > SYSDATE - 3
ORDER BY first_time, minutes;

35. LGWR write latency per operation

SELECT event,
       state,
       seq#,
       seconds_in_wait,
       program
FROM v$session
WHERE program LIKE '%LGWR%'
  AND state='WAITING';

36. Locate tables without indexes

SELECT table_name FROM user_tables WHERE table_name NOT IN (SELECT table_name FROM user_indexes);
SELECT table_name FROM user_tables WHERE table_name NOT IN (SELECT table_name FROM user_ind_columns);

37. DB time for the last 7 days

WITH sysstat AS (
  SELECT sn.begin_interval_time,
         sn.end_interval_time,
         ss.stat_name,
         ss.value e_value,
         LAG(ss.value,1) OVER (ORDER BY ss.snap_id) b_value
  FROM dba_hist_sysstat ss
  JOIN dba_hist_snapshot sn ON ss.snap_id = sn.snap_id
  WHERE TRUNC(sn.begin_interval_time) >= SYSDATE - 7
    AND ss.dbid = (SELECT dbid FROM v$database)
    AND ss.instance_number = (SELECT instance_number FROM v$instance)
    AND ss.stat_name = 'DB time')
SELECT TO_CHAR(begin_interval_time,'mm-dd hh24:mi')||' '||TO_CHAR(end_interval_time,'hh24:mi') date_time,
       stat_name,
       ROUND((e_value - NVL(b_value,0)) / (
         EXTRACT(DAY FROM (end_interval_time - begin_interval_time))*24*60*60 +
         EXTRACT(HOUR FROM (end_interval_time - begin_interval_time))*60*60 +
         EXTRACT(MINUTE FROM (end_interval_time - begin_interval_time))*60 +
         EXTRACT(SECOND FROM (end_interval_time - begin_interval_time))
       ),0) per_sec
FROM sysstat
WHERE (e_value - NVL(b_value,0)) > 0
  AND NVL(b_value,0) > 0;

38. Identify objects causing hot blocks

SELECT e.owner,
       e.segment_name,
       e.segment_type
FROM dba_extents e,
     (SELECT * FROM (
        SELECT addr, ts#, file#, dbarfil, dbablk, tch
        FROM x$bh
        ORDER BY tch DESC)
      WHERE ROWNUM < 11) b
WHERE e.relative_fno = b.dbarfil
  AND e.block_id <= b.dbablk
  AND e.block_id + e.blocks > b.dbablk;

39. Export AWR report

SELECT * FROM dba_hist_snapshot;
SELECT * FROM TABLE(dbms_workload_repository.awr_report_html(DBID, INSTANCE_NUMBER, start_snap_id, end_snap_id));
SELECT * FROM TABLE(dbms_workload_repository.awr_diff_report_html(DBID, INSTANCE_NUMBER, start_snap_id, end_snap_id, DBID, INSTANCE_NUMBER, start_snap_id, end_snap_id));

40. Retrieve execution plan for a specific SQL

SELECT a.hash_value, a.* FROM v$sql a WHERE sql_id = '0n4qfzbqfsjm3';
SELECT * FROM TABLE(dbms_xplan.display_cursor(v$sql.hash_value,0,'advanced'));

-- Alternative syntax
SELECT * FROM TABLE(xplan.display_cursor('v$sql.sql_id',0,'advanced'));

-- Create XPLAN package if missing
CREATE PUBLIC SYNONYM XPLAN FOR SYS.XPLAN;
GRANT EXECUTE ON sys.xplan TO PUBLIC;