Performance Troubleshooting Series : Identifying Problematic Sessions or Queries (Preface)

First lets see how to determine is the database currently running slow.

1. AAS:- Average Active Sessions , As written here you can find the AAS as a measurement to understand the database is loaded or not. 

Part 1 and Part 2 of the Series

2.Identify the most resource Consumption processes:-

But if you see the database has load i.e AAS is more then

Part 3 of the series

As by now , you might have determined the database load, by AAS, with yard stick cpu count of the database, if your AAS is less than CPU COUNT then you can simply say that database is not loaded, that means you do not have that many concurrent sessions occupying CPU (leaving other factors like IO/Memory)

Then, why they reporting slow. It might be the case that their specific session or process taking time to complete or waiting or running slow.

So you need to ask few questions now.

1. Provide more details on SID, Machine, SQL statement,, at what time the session logged on.

With the details above you can identify or pin point to session level troubleshooting..

Question 2:- Session / Query Running Slow:-

3. As you identify the session which causing issue, now look at those sessions top events.

There are two Methods and a systematic approach.

Method 1. Session Wait Event Approach:- In this approach we will try to identify from the problematic session what its waiting for , why its waiting, what can be done to resolve the problem.

Method 2. Session SQL ID Run History Approach:- In this approach we shall need to check , if the query for the session that running long has been ran before, if so ran, what is the plan before and what is the plan currently and is there any change, if so any change why it changed what caused to be changed etc.

SQL> select sid,username,status,event,sql_id,prev_sql_id,blocking_session,row_wait_obj# from v$session where sid='';

(you can replace any condition like username, machine in where clause with the information you have got from application teams)

Alternatively, you can find the series of information from v$active_session_history of every second sample for the same session using below query

SQL> select sample_time,session_id,username,event,sql_id,sql_id,prev_sql_id,blocking_session,blocking_instance,plan_hash_value from v$active_session_history where session_id='' or username='';