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

創(chuàng)新互聯(lián)成立與2013年，先為城口等服務(wù)建站，城口等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢服務(wù)。為城口企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問(wèn)題。

1、確定時(shí)間段：

select * from dba_hist_snapshot

where snap_idbetween&snapid1 and&snapid2

order by end_interval_time;

例如以上&snapid1 and&snapid2的值分別為10910 and 10913

2、對(duì)瓶頸時(shí)間段的等待時(shí)間進(jìn)行匯總排序：

select event,count(*) from dba_hist_active_sess_history

where snap_id between 10910 and 10913

group by event

order by 2;

3、根據(jù)排序情況，確定等待時(shí)間并根據(jù)確定等待時(shí)間，進(jìn)一步觀察相關(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、明確該等待時(shí)間相關(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語(yǔ)句：

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當(dāng)時(shí)對(duì)應(yīng)的執(zhí)行計(jì)劃：

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包來(lái)查看執(zhí)行計(jì)劃：

SQL> select * from table(dbms_xplan.display_awr('djpvmvjddy8av'));

7、也可查看類此對(duì)象的更多SQL：

select * from dba_hist_active_sess_history

where snap_id between 10910 and 10913

and sql_text like '%QRTZ_SCHEDULE%';

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