一、通过dba_hist_*来诊断

1、确定时间段：

select  *  from  dba_hist_snapshot
where  snap_id  between &snapid1  and  &snapid2
order by end_interval_time;

例如以上&snapid1 and &snapid2的值分别为10910 and 10913

其中，dba_hist_snapshot字段如下：

23:06:58 SQL> desc dba_hist_snapshot;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SNAP_ID                                   NOT NULL NUMBER
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 STARTUP_TIME                              NOT NULL TIMESTAMP(3)
 BEGIN_INTERVAL_TIME                       NOT NULL TIMESTAMP(3)
 END_INTERVAL_TIME                         NOT NULL TIMESTAMP(3)
 FLUSH_ELAPSED                                      INTERVAL DAY(5) TO SECOND(1)
 SNAP_LEVEL                                         NUMBER
 ERROR_COUNT                                        NUMBER
 SNAP_FLAG                                          NUMBER
 SNAP_TIMEZONE                                      INTERVAL DAY(0) TO SECOND(0)
 BEGIN_INTERVAL_TIME_TZ                             TIMESTAMP(3) WITH TIME ZONE
 END_INTERVAL_TIME_TZ                               TIMESTAMP(3) WITH TIME ZONE
 CON_ID                                             NUMBER

2、对瓶颈时间段的等待时间进行汇总排序：

select  event,count(*)  from  dba_hist_active_sess_history
where  snap_id  between  10910  and  10913
group  by  event
order  by  2;

3、根据排序情况，确定等待时间并根据确定的等待时间，进一步观察相关字段的内容：

select  *  from  dba_hist_active_history
where  snap_id  between  10910  and  10913
and  event='enq:TX - row lock contention'
order by sample_time;

4、明确该等待时间相关的SQL_ID：

select  sql_id,  count(*)   from  dba_hist_active_sess_history
where  snap_id  between  10910  and  10913
and event='enq:TX - row lock contention'
group  by  sql_id;

5、根据sql_id找出SQL语句：

select  *  from  dba_hist_active_sess_history
where  snap_id  between  10910  and  10913
and  event='enq:TX - row lock contention'
and  sql_id='';

6、查看SQL当时对应的执行计划：

select  id,operation,options,object_owner,object_name,object_type,cost,cardinality,bytes,cpu_cost,io_cost 
from  DBA_HIST_SQL_PALN where  sql_id=''
order  by  id;

也可以调用dbms_xplan.display_awr包来查看执行计划：

select  *  from  table(dbms_xplan.display_awr(''));

7、也可查看此类对象的更多SQL：

select  *  from  dba_hist_active_sess_history
where  snap_id  between 10910 and 10913
and  sql_text  like  '%QRTZ_SCHEDULE%';

根据以上结果对应的SQL或等待时间进行优化。

学习来源：通过dba_hist_*来进行诊断程序员编程网-最新程序员编程前沿技术程序员编程网