通過dba_hist_*來進行診斷

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

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、根據(jù)排序情況，確定等待時間并根據(jù)確定等待時間，進一步觀察相關(guān)字段內(nèi)容：

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、明確該等待時間相關(guān)的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、根據(jù)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當時對應(yīng)的執(zhí)行計劃：

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;

也可以調(diào)用dbms_xplan.display_awr包來查看執(zhí)行計劃：

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%';

根據(jù)以上結(jié)果對相應(yīng)的SQL或等待時間進行優(yōu)化。