Looking for load and bottlenecks on a single instance database

When people give you no information on what and where the performance problem is... Sit down with them and keep asking. And if you still get nothing, lets take a look at some guess work.

These sql statements don't use ASH or the AWR.

We can use the V$WAITCLASSMETRIC_HISTORY to see time waited for wait class for the last hour. The below query determines the average time waited for the wait classes. You might be able to see in the last hour if there has been a change in wait class response times

select begin_time , n.wait_class, 10*m.time_waited/nullif(m.wait_count,0) avgms from V$WAITCLASSMETRIC_HISTORY m, v$system_wait_class n where m.wait_class_id=n.wait_class_id and n.wait_class != 'Idle';

As for what is happening right now. We can count the sessions and group them by the wait events. This will tell us where sessions are spending the time right now.

select count(*) count, CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END state, CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END 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;

If we want to see what sessions are working in the database, we could use V$SESSTAT and DB time. Below will show us the top 10 sessions based on the DB Time statistic. The query ignores background sessions and only shows sessions active in the last 60 minutes.

select * from ( select ss.sid, s.serial#, s.username, n.name, ss.value from v$sesstat ss, v$statname n, v$session s where ss.statistic# = n.statistic# and n.name = 'DB time' and ss.sid = s.sid and s.username is not null AND s.last_call_et < 3600 order by value desc ) where rownum <=10;

******************************************
keywords: load
******************************************
rdbms version: 12c
******************************************