What is Going on Inside My Database

What is Going on Inside My Database?

The simplest query to determine performance at database level is to query v$session_wait and take a lead from there.

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


It uses the Oracle wait interface to report what all database sessions are currently waiting and doing CPU activity.

Whenever there is an issue on Database System, 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 but nevertheless, if you want to see an instance sessions state overview, this is the simplest query you can use.

Interpreting this query output should be combined with reading some OS performance tool output (like vmstat or perfmon), 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).

Sometimes you might want to exclude the background processes and idle sessions from the picture. On that scenario use the SQL Scripts provided below:

sqlplus '/as sysdba'
SET LINESIZE 200;
COL SW_EVENT FORMAT A90;
SQL> select count(*), CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END AS state, CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END AS sw_event FROM v$session WHERE type = 'USER' AND status = 'ACTIVE' GROUP BY CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END, CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END ORDER BY 1 DESC, 2 DESC;


Sometimes you might want to exclude the background processes and idle sessions from the picture. On that scenario use the SQL Scripts provided below:
This is something you get in ASH as well, instance performance graph which shows you the instance wait summary. ASH nicely puts the CPU count of server into the graph as well (that you would be able to put the number of “On CPU” sessions into perspective).

If you wish to include the background processes and idle sessions, use following query:

SQL> select count(*), CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END AS state, CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END AS sw_event FROM v$session_wait GROUP BY CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END, CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END ORDER BY 1 DESC, 2 DESC;

You can use similar technique for easily viewing the instance activity from other perspectives and dimensions as well, like which SQL is being executed.

SQL> select sql_hash_value, count(*) from v$session where status = 'ACTIVE' group by sql_hash_value order by 2 desc;
SQL> select sql_text,users_executing from v$sql where hash_value = <HashValue-PreviousCommand>;