When someone asks you to take a quick look into database performance and for whatever reason you can’t run your usual scripts or performance tools on there, ), then what query would you run first?
Yeah sometimes I’ve been not allowed to run custom scripts nor even touch the keyboard due security policies in effect.

Whenever you’re in such situation you want to be the command both short and effective for showing the database state.

The simplest query for determining database state performance wise would be this:

SQL> select event, state, count(*) from v$session_wait group by event, state order by 3 desc;

EVENT                                                            STATE                 COUNT(*)
---------------------------------------------------------------- ------------------- ----------
rdbms ipc message                                                WAITING                      9
SQL*Net message from client                                      WAITING                      8
log file sync                                                    WAITING                      6
gcs remote message                                               WAITING                      2
PL/SQL lock timer                                                WAITING                      2
PL/SQL lock timer                                                WAITED KNOWN TIME            2
Streams AQ: qmn coordinator idle wait                            WAITING                      1
smon timer                                                       WAITING                      1
log file parallel write                                          WAITING                      1
ges remote message                                               WAITING                      1
SQL*Net message to client                                        WAITED SHORT TIME            1
DIAG idle wait                                                   WAITING                      1
pmon timer                                                       WAITING                      1
db file sequential read                                          WAITING                      1
Streams AQ: waiting for messages in the queue                    WAITING                      1
rdbms ipc message                                                WAITED KNOWN TIME            1
jobq slave wait                                                  WAITING                      1
Streams AQ: qmn slave idle wait                                  WAITING                      1
Streams AQ: waiting for time management or cleanup tasks         WAITING                      1

19 rows selected.

It uses the Oracle wait interface to report what all database sessions are currently doing wait/CPU usage wise. Whenever there’s a systemic issue (like extremely slow log file writes) this query will give good hint towards the cause of problem. Of course just running couple of queries against wait interface doesn’t give you the full picture (as these kinds of database wide “healthchecks” can be misleading as we should be really measuring end user response time breakdown at session level and asking questions like what throughput/response time do you normally get) but nevertheless, if you want to see an instance sessions state overview, this is the simplest query I know.

Interpreting this query output should be combined with reading some OS performance tool output (like vmstat or perfmon), in order to determine whether the problem is induced by CPU overload. For example, if someone is running a parallel backup compression job on the server which is eating all CPU time, some of these waits may be just a side-effect of CPU overload).

Below is a cosmetically enhanced version of this command, as one thing I decode the “WAITED FOR xyz TIME” wait states to “WORKING” and “On CPU / runqueue” as event name as otherwise it’s easy to miss by accident that some sessions are not actually waiting on previous event anymore:

[Read more...]