一、通过dba_hist_*来诊断
1、确定时间段:
select * from dba_hist_snapshot
where snap_id between &snapid1 and &snapid2
order by end_interval_time;
例如以上&snapid1 and &snapid2的值分别为10910 and 10913
其中,dba_hist_snapshot字段如下:
23:06:58 SQL> desc dba_hist_snapshot;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
STARTUP_TIME NOT NULL TIMESTAMP(3)
BEGIN_INTERVAL_TIME NOT NULL TIMESTAMP(3)
END_INTERVAL_TIME NOT NULL TIMESTAMP(3)
FLUSH_ELAPSED INTERVAL DAY(5) TO SECOND(1)
SNAP_LEVEL NUMBER
ERROR_COUNT NUMBER
SNAP_FLAG NUMBER
SNAP_TIMEZONE INTERVAL DAY(0) TO SECOND(0)
BEGIN_INTERVAL_TIME_TZ TIMESTAMP(3) WITH TIME ZONE
END_INTERVAL_TIME_TZ TIMESTAMP(3) WITH TIME ZONE
CON_ID NUMBER
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_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='';
6、查看SQL当时对应的执行计划:
select id,operation,options,object_owner,object_name,object_type,cost,cardinality,bytes,cpu_cost,io_cost
from DBA_HIST_SQL_PALN where sql_id=''
order by id;
也可以调用dbms_xplan.display_awr包来查看执行计划:
select * from table(dbms_xplan.display_awr(''));
7、也可查看此类对象的更多SQL:
select * from dba_hist_active_sess_history
where snap_id between 10910 and 10913
and sql_text like '%QRTZ_SCHEDULE%';
根据以上结果对应的SQL或等待时间进行优化。