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.
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