Top 20 Most Expensive Oracle SQL with v$sqlstats

Shows costliest SQL by elapsed time with executions, CPU and I/O. Tells you which SQL_ID to investigate for tuning.

#v$sqlstats#top-sql#elapsed-time

oracle · sql

SELECT * FROM (
  SELECT sql_id,
         executions,
         ROUND(elapsed_time / 1e6, 1) AS elapsed_sec,
         ROUND(elapsed_time / NULLIF(executions, 0) / 1e3, 2) AS avg_ms,
         ROUND(cpu_time / 1e6, 1) AS cpu_sec,
         buffer_gets, disk_reads, rows_processed
  FROM   v$sqlstats
  ORDER  BY elapsed_time DESC
) WHERE ROWNUM <= 20;

Operational Notes & Remediation Guidance

Verify in a staging environment prior to running in production. This is a read-only diagnostic query; it does not acquire exclusive locks.

Production Safety & Execution Standards

This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
Prior to executing on production systems under high load, verify the target database version (Min. Version: 10g).
Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.

Engineering Triad: Related Runbooks & Incident Response

Which production incidents and emergency drills execute this script as a diagnosis step?

Runbook Library (63)

#39 · PerformanceSEV2

Oracle High DB Time and AAS: Query and Wait Triage

Splits high active-session load into CPU, I/O, locks and network waits, then identifies expensive SQL and a safe tuning plan.

15-30 dakika / 15-30 minsOpen Runbook Procedure

#45 · PerformanceSEV2

Oracle Hard Parse Spike and Shared Pool Pressure: Root Cause

Measures hard-parse rate and cursor proliferation to separate bind-variable, plan and statistics issues without flushing the shared pool.

15-30 dakika / 15-30 minsOpen Runbook Procedure

Related Alerts & Metrics

M40Oracle Average Active Sessions (AAS)Oracle

M46Oracle Hard Parse RatioOracle

M48Oracle Buffer Cache Hit RatioOracle

Other ORACLE Diagnostic Scripts

View All (100)

#61Performance

Oracle Active Sessions and Wait Events

Lists active user sessions with the running SQL_ID and wait event. Shows the live picture during performance issues.

View Script

#62Locking

Find Blocking Sessions in Oracle

Shows sessions that make others wait, with wait times. Identifies the root session to terminate in lock incidents.

View Script

#63Storage

Oracle Tablespace Usage Including Autoextend

Calculates tablespace usage against the real maximum, honoring autoextend limits. Catches space errors like ORA-01653 before they happen.

View Script

#65Maintenance

Find Invalid Objects in Oracle

Lists INVALID objects in user schemas. Essential after upgrades and patching.

View Script