Saturday, August 8, 2015

User complain that query is running very slow in oracle 11g. How to trouble shoot?

Performance issue on 11g.
One day user suddenly started complaining that they are experiening slowness. So I have started troubleshooitng  like below.
Luckly I have OEM, So first I look into performance section to find out how it is currently. I found below to in the OEM.

It is found that select statement is the top SQL and the wait event is db file sequential read. So I had to find out what is the object on which this wait is happening. I found the object information by below queries.

In order to overcome this I gather statistics for that table and flush shared pool.

I Monitor the performance home page and found the performance has improved.

I also cross checked the execution time and elapsed time for the execution.

Question is why I took these steps?
Answer:- This had happen in UAT environment. So most probably states may be old. Sometimes, the stats may not be fresh and it leads to costly execution plans as in this case I had seen the execution and elapse time was high. In such case, immediately stats collection and a flush from shared pool will give new hash value for the sql_id with new plan

1 comment: