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

 

In this part of the series, we will be looking into , how to identify the Problematic Sessions or Queries.

Before beginning, one should distinguish the question here. You may face questions like, and the answer for both is to identify either loaded sessions for a database.

1. Database is/was slow.

2. Our Session/Query is/was slow.

To make it simple, I will stick this two questions for this post now.

Question 1: Database is/was slow

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=”;