happydba

Oracle 10g has greater enhancements w.r.t performance tunings.

AWR in 10g is enhanced version of statspack and we can get more information about
instance/database performance.

When ever we see any performance issues with a SQL query first we need to identify
if the plan for the query is changed or not.

Following query can be used to find the SQL Plan and other details:

select tawr.*
from
 dba_hist_sqltext dhst,
 table(dbms_xplan.display_awr(dhst.sql_id,null, null,  'ALL' )) tawr
where dhst.sql_id='<sql id>' ;

select
   dhs.snap_id, dhsn.begin_interval_time, dhs.plan_hash_value,
   dhs.optimizer_cost, dhs.elapsed_time_total
from
   DBA_HIST_SQLSTAT dhs, DBA_HIST_SNAPSHOT dhsn
where
   dhs.snap_id=dhsn.snap_id and sql_id='<sql id>' order by 2;