摘录于SAP有关分析ORACLE数据性能事件的文档。
1、A check for the distribution of relevant Oracle server time revealed:
有关Oracle服务器时间分布的检查显示:
SELECTEVENT,
TOTAL_WAITS,
TIME_WAITED,
AVG_MS,ROUND(RATIO_TO_REPORT(TIME_WAITED) OVER () * 100) PERCENT
FROM(SELECTSUBSTR(EVENT,1, 30) EVENT,
TOTAL_WAITS,
TIME_WAITED,ROUND(TIME_WAITED_MICRO / TOTAL_WAITS / 1000, 2) AVG_MSFROMV$SYSTEM_EVENTWHEREWAIT_CLASS!= ‘Idle‘ ANDEVENTNOT IN(‘db file parallel write‘, ‘log file parallel write‘,‘log file sequential read‘, ‘control file parallel write‘,‘control file sequential read‘, ‘Log archive I/O‘)UNION
SELECT ‘CPU‘ EVENT, NULL, VALUE, NULL
FROMV$SYSSTATWHERE STATISTIC# = 12
ORDER BY 3 DESC)WHERE ROWNUM <= 10;
?? This check is a very useful step to get a first impression what happens
inside a database in terms of performance.
?? The “EVENT NOT IN” condition is necessary because some events are
additional idle events from an SAP perspective.
?? The new Oracle 10g categorization WAIT_CLASS = Idle was used in
order to make sure that uncritical idle wait events are not considered.
?? The results indicate that, since database startup, 16 % of the relevant
Oracle server time was caused by TX enqueues.
?? This is much more than we usually want to see and can have significant
impact on the database performance for specific transactions or during
specific time frames.
2、In the following we check when there was an enqueue peak time:
SELECTTO_CHAR(END_INTERVAL_TIME,‘YYYY-MM-DD HH24:MI:SS‘)
END_INTERVAL_TIME,
TOTAL_WAITS,
TIME_WAITED_MICRO,ROUND(DECODE(TOTAL_WAITS, 0, 0, TIME_WAITED_MICRO /TOTAL_WAITS/ 1000), 2) AVG_WAIT_MSFROM(SELECTHSS.END_INTERVAL_TIME,
HSE.EVENT_NAME,
HSE.TOTAL_WAITS- LAG(HSE.TOTAL_WAITS, 1) OVER(ORDER BYHSS.SNAP_ID) TOTAL_WAITS,
HSE.TIME_WAITED_MICRO- LAG(HSE.TIME_WAITED_MICRO, 1) OVER(ORDER BYHSS.SNAP_ID) TIME_WAITED_MICROFROMDBA_HIST_SYSTEM_EVENT HSE, DBA_HIST_SNAPSHOT HSSWHEREHSE.SNAP_ID= HSS.SNAP_ID ANDHSE.EVENT_NAME= ‘enq: TX - row lock contention‘
ORDER BY
HSS.SNAP_ID DESC
)
WHERE
TOTAL_WAITS >= 0;
?? DBA_HIST_SYSTEM_EVENT can show you (per default) on an hourly
basis which wait events were active, to what extent, and with which
average wait time.
?? Between 3 and 5 p.m. on 9th of May there is an obvious peak in total and
average wait time for TX enqueues. So we should now focus on this time
frame.
3、Now let’s see which enqueue waits happened during the peak time:
SELECTTO_CHAR(ASH.SAMPLE_TIME,‘YYYY-MM-DD HH24:MI:SS‘)
SAMPLE_TIME,
ASH.SESSION_ID,
ASH.BLOCKING_SESSION,
O.OBJECT_NAME,
S.SQL_TEXTFROMDBA_HIST_ACTIVE_SESS_HISTORY ASH,
DBA_HIST_SQLTEXT S,
DBA_OBJECTS OWHEREASH.SQL_ID= S.SQL_ID (+) ANDASH.CURRENT_OBJ#= O.OBJECT_ID (+) ANDASH.EVENTlike ‘enq: TX - row lock contention‘ ANDASH.SAMPLE_TIMEBETWEENTO_TIMESTAMP(‘09.05.2007 15:30:00‘, ‘dd.mm.yyyy hh24:mi:ss‘) ANDTO_TIMESTAMP(‘09.05.2007 16:30:00‘, ‘dd.mm.yyyy hh24:mi:ss‘) ANDASH.SESSION_STATE= ‘WAITING‘
ORDER BYSAMPLE_TIMEDESC;
?? DBA_HIST_ACTIVE_SESS_HISTORY contains information about active
sessions on a 10 seconds basis for the last 7 days.
?? It is a history view for V$ACTIVE_SESSION_HISTORY where information
about active sessions is stored on a 1 second basis for the last few hours.
4、What’s the “big picture” of the Oracle enqueue wait situation?
5、Let’s check the lock holders in the relevant time frame:
SELECTTO_CHAR(ASW.SAMPLE_TIME,‘YYYY-MM-DD HH24:MI:SS‘)
SAMPLE_TIME,
ASW.BLOCKING_SESSION SESSION_ID,COUNT(*) "#WAITERS",
ASH.BLOCKING_SESSION,
ASH.TIME_WAITED,
DECODE(ASH.SESSION_STATE,NULL, ‘INACTIVE‘,‘WAITING‘, ASH.EVENT, ‘CPU‘) ACTION,
TO_CHAR(SUBSTR(HST.SQL_TEXT,1, 4000)) SQL_TEXTFROMDBA_HIST_ACTIVE_SESS_HISTORY ASH,
DBA_HIST_ACTIVE_SESS_HISTORY ASW,
DBA_HIST_SQLTEXT HSTWHEREASH.SQL_ID= HST.SQL_ID (+) ANDASH.SAMPLE_TIME (+) = ASW.SAMPLE_TIME ANDASH.SESSION_ID (+) = ASW.BLOCKING_SESSION ANDASW.EVENT= ‘enq: TX - row lock contention‘ ANDASW.SESSION_STATE= ‘WAITING‘
GROUP BYTO_CHAR(ASW.SAMPLE_TIME,‘YYYY-MM-DD HH24:MI:SS‘),
ASW.BLOCKING_SESSION,
ASH.BLOCKING_SESSION,
ASH.TIME_WAITED,
DECODE(ASH.SESSION_STATE,NULL, ‘INACTIVE‘,‘WAITING‘, ASH.EVENT, ‘CPU‘),
TO_CHAR(SUBSTR(HST.SQL_TEXT,1, 4000))ORDER BYTO_CHAR(ASW.SAMPLE_TIME,‘YYYY-MM-DD HH24:MI:SS‘)
What do we know now?
1、We have a lot of information about the Oracle side lock dependencies.
2、 But we don‘t know what happened on the SAP side at this point, because there is no
“Active Session History” available for the SAP work processes.
3、 So we can‘t say what the lock holding session 1110 was doing all the time.
4、 This is a limiting factor for our analysis.
5、 In this particular case we’re in luck because one SM66 snapshot was taken on the
SAP side showing the work process activities (see next page).
6、 Via V$SESSION we were able to map Oracle session 1110 to client process 233476
(column PROCESS).
7、 Unfortunately Oracle doesn‘t store the PROCESS column in
V$ACTIVE_SESSION_HISTORY, so that a mapping “work process Oracle
session” is difficult if work processes were restarted since the problem happened.
原文:https://www.cnblogs.com/windtalker/p/9036542.html