OraclePerformance

Top sessions by CPU used

Connected sessions ranked by total CPU seconds since they logged on. Good for finding the one batch job or report hammering the box.

Not yet verified. How scripts are tested

ora-top-cpu-sessions.sql

1CLEAR COLUMNS2SET LINESIZE 160 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN sid_serial HEADING "SID,SERIAL#" FORMAT A144COLUMN username   FORMAT A155COLUMN program    FORMAT A35 TRUNCATE6COLUMN status     FORMAT A87COLUMN sql_id     FORMAT A138COLUMN cpu_sec    HEADING "CPU SEC" FORMAT 999,999,9909 10SELECT * FROM (11  SELECT s.sid || ',' || s.serial# AS sid_serial,12         s.username, s.program, s.status, s.sql_id,13         ROUND(st.value / 100) AS cpu_sec14  FROM v$sesstat st15  JOIN v$statname n ON n.statistic# = st.statistic#16  JOIN v$session s ON s.sid = st.sid17  WHERE n.name = 'CPU used by this session'18    AND s.type = 'USER'19  ORDER BY st.value DESC20) WHERE ROWNUM <= 15;

Save it as ora-top-cpu-sessions.sql and run it with SQL> @ora-top-cpu-sessions.

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 20 SQL by elapsed timeFrom V$SQLSTATS, which is cheaper to query than V$SQL. Feed the SQL_ID into the execution plan script to see how it runs.