通過dba_hist_*來進行診斷

在Oracle10g中增加了dba_his_*類統計信息表，在瓶頸時間過時了的時候，可以參考這些表來診斷瓶頸來源。

1、確定時間段：

select * from dba_hist_snapshot

where snap_id between &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;

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或等待時間進行優化。