189 8069 5689
在Oracle10g中增加了dba_his_*类统计信息表，在瓶颈时间过时了的时候，可以参考这些表来诊断瓶颈来源。
创新互联成立与2013年，先为城口等服务建站，城口等地企业，进行企业商务咨询服务。为城口企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。
1、确定时间段：
select * from dba_hist_snapshot
where snap_idbetween&snapid1 and&snapid2
order by end_interval_time;
例如以上&snapid1 and&snapid2的值分别为10910 and 10913
2、对瓶颈时间段的等待时间进行汇总排序：
select event,count(*) from dba_hist_active_sess_history
where snap_id between 10910 and 10913
group by event
order by 2;
[@more@]3、根据排序情况，确定等待时间并根据确定等待时间，进一步观察相关字段内容：
select * from dba_hist_active_sess_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='fhdxrqd4stwqk';
6、查看SQL当时对应的执行计划：
select id,operation, options,object_owner,object_name,object_type,cost,cardinality,bytes,cpu_cost,io_cost
from DBA_HIST_SQL_PLAN where sql_id='djpvmvjddy8av'
order by id;
也可以调用dbms_xplan.display_awr包来查看执行计划：
SQL> select * from table(dbms_xplan.display_awr('djpvmvjddy8av'));
7、也可查看类此对象的更多SQL：
select * from dba_hist_active_sess_history
where snap_id between 10910 and 10913
and sql_text like '%QRTZ_SCHEDULE%';
根据以上结果对相应的SQL或等待时间进行优化。