OraclePerformance

What active sessions are doing right now

Active user sessions grouped by wait event, with CPU shown separately. A quick live picture before you reach for ASH.

Not yet verified. How scripts are tested

ora-waits-now.sql

1CLEAR COLUMNS2SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN activity   FORMAT A454COLUMN wait_class HEADING "WAIT CLASS" FORMAT A155COLUMN sessions   FORMAT 9,9906 7SELECT CASE WHEN state = 'WAITING' THEN event ELSE 'ON CPU' END AS activity,8       CASE WHEN state = 'WAITING' THEN wait_class ELSE 'CPU' END AS wait_class,9       COUNT(*) AS sessions10FROM v$session11WHERE status = 'ACTIVE'12  AND type = 'USER'13  AND (state <> 'WAITING' OR wait_class <> 'Idle')14GROUP BY CASE WHEN state = 'WAITING' THEN event ELSE 'ON CPU' END,15         CASE WHEN state = 'WAITING' THEN wait_class ELSE 'CPU' END16ORDER BY sessions DESC;

Save it as ora-waits-now.sql and run it with SQL> @ora-waits-now.

Open in denrepo

Helps with

ORA-04021: timeout occurred while waiting to lock object

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…
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.
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.