Hi Guru's
DB Version: 11.2.0.2
OS: RHEL5
May be this question asked several times in this forum. As far i came to know the sequence which we need to follow for finding out the solution for slow running query in the database is
1) Check with end user which part of thier SQL/module/component it running slow.
2) Once identified focus on those priority SQLs
3) If it cannot be identified then check from AWR report. Check for TOP consumer in SQL oredered by% section of AWR to know which SQLs are consuming high resources. And verify with end users if these are the queires they want to tune.
4) Once SQL are identifed check what you can do to tune them
a) check explain plan and compare it with old one(if you have)
b) check statistics are upto date. check last_analyzed columns of dba_tables view.
c) check the selectivity and the cardinalities are correct
d) Further if required. trace it with 10053 or 10046 evets
But for 11gR2 Still the approach is same of there is any new approach to be followed means.
1) Executing the EXPLAIN PLAN to get the execution path of the SQL Query.
2) Executing the TKPROF utililty to get the more details information of SQL Query that are running slow
3) Comparing the o/p of EXPLAIN PLAN and TKPROF utility with the previous EXPLAIN PLAN and TKPROF reports when the db is running good.
OR
I need to follwo REAL TIME PERFORMANCE MONITORING Using OEM which tells about the Active Database Activity on real time bassis
Kinldy guide over Sequence of steps need to follow
Thanks in advance