oracle dba_hist_snapshot,通过dba_hist_*来进行诊断

在Oracle10g中增加了dba_his_*类统计信息表，在瓶颈时间过时了的时候，可以参考这些表来诊断瓶颈来源。

1、确定时间段：

select * from

dba_hist_snapshot

where snap_idbetween&snapid1and&snapid2

order by end_interval_time;

例如以上&snapid1and&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或等待时间进行优化。