OraclePerformance

Top 20 SQL by elapsed time

From V$SQLSTATS, which is cheaper to query than V$SQL. Feed the SQL_ID into the execution plan script to see how it runs.

Not yet verified. How scripts are tested

b-ora-topsql.sql

1CLEAR COLUMNS2SET LINESIZE 200 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN sql_id      FORMAT A134COLUMN executions  HEADING "EXECS"       FORMAT 999,999,9995COLUMN elapsed_sec HEADING "ELAPSED SEC" FORMAT 999,999,9906COLUMN avg_ms      HEADING "AVG MS"      FORMAT 999,999,990.97COLUMN buffer_gets HEADING "BUFFER GETS" FORMAT 999,999,999,9998COLUMN disk_reads  HEADING "DISK READS"  FORMAT 999,999,999,9999COLUMN sql_text    FORMAT A50 WORD_WRAPPED10 11SELECT * FROM (12  SELECT sql_id, executions,13         ROUND(elapsed_time / 1e6) AS elapsed_sec,14         ROUND(elapsed_time / NULLIF(executions, 0) / 1e3, 1) AS avg_ms,15         buffer_gets, disk_reads,16         SUBSTR(sql_text, 1, 120) AS sql_text17  FROM v$sqlstats18  ORDER BY elapsed_time DESC19) WHERE ROWNUM <= 20;

Save it as b-ora-topsql.sql and run it with SQL> @b-ora-topsql.

Open in denrepo

Part of these runbooks

The database is slow

More Oracle scripts: Performance

Load right now (last 60 seconds)Host CPU, average active sessions, transactions, redo and I/O rates from V$SYSMETRIC. Compare average active sessions with your CPU count to judge…
What active sessions are doing right nowActive user sessions grouped by wait event, with CPU shown separately. A quick live picture before you reach for ASH.
Top wait events since startupThe 15 non-idle waits with the most total time since the instance started, with average wait in milliseconds. Averages matter: 'db file sequential…
Top sessions by CPU usedConnected sessions ranked by total CPU seconds since they logged on. Good for finding the one batch job or report hammering the box.