Instance Wait Tuning

The use of the Active Session History (ASH) data collection within Oracle 10g provides a wealth of excellent instance tuning opportunities. The dba_hist_sys_time_model table can be queried to locate aggregate information on where Oracle sessions are spending most of their time.

The v$active_session_history table can be used to view specific events with the highest resource waits.

select

ash.event,

sum(ash.wait_time +

ash.time_waited) ttl_wait_time

from

v$active_session_history ash

where

ash.sample_time between sysdate - 60/2880 and sysdate

group by

ash.event

order by 2;

The following is sample output from this script.:

EVENT TTL_WAIT_TIME

-------------------------------------- -------------

SQL*Net message from client 218

db file sequential read 37080

control file parallel write 156462

jobq slave wait 3078166

Queue Monitor Task Wait 5107697

rdbms ipc message 44100787

class slave wait 271136729

The v$active_session_history table can be used to view users, and see which users are waiting the most time for database resources:

col wait_time format 999,999,999

select

sess.sid,

sess.username,

sum(ash.wait_time + ash.time_waited) wait_time

from

v$active_session_history ash,

v$session sess

where

ash.sample_time > sysdate-1

and

ash.session_id = sess.sid

group by

sess.sid,

sess.username

order by 3;

The following is sample output from this script.:

SID USERNAME WAIT_TIME

---------- ------------------------------ ----------

140 OPUS 30,055

165 30,504

169 9,234,463

167 27,089,994

160 34,145,401

168 40,033,486

152 45,162,031

159 81,921,987

144 OPUS 129,249,875

150 SYS 134,263,687

142 163,752,689

166 170,700,889

149 OPUS 195,664,013

163 199,860,105

170 383,992,930

For a given session, an Oracle user may issue multiple SQL statements and it is the interaction between the SQL and the database that determines the wait conditions. The v$active_session_history table can be joined into the v$sqlarea and dba_users to quickly see the top SQL waits as well as the impacted user and session with which they are associated:

select

ash.user_id,

u.username,

sqla.sql_text,

sum(ash.wait_time + ash.time_waited) wait_time

from

v$active_session_history ash,

v$sqlarea sqla,

dba_users u

where

ash.sample_time > sysdate-1

and

ash.sql_id = sqla.sql_id

and

ash.user_id = u.user_id

group by

ash.user_id,

sqla.sql_text,

u.username

order by 4;

The following is sample output from this script.:

USER_ID USERNAME

---------- ------------------------------

SQL_TEXT

--------------------------------------------------------------------------------

WAIT_TIME

----------

54 SYSMAN

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN :

= FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date

; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

0

58 DABR

select tbsp , reads "Reads" , rps "Reads / Second" , atpr "Avg

Reads (ms)" , bpr "Avg Blks / Read" , writes "Writes" , wps

"Avg Writes / Second" , waits "Buffer Waits" , atpwt"Avg Buf Wait (m

s)" From ( select e.tsname tbsp , sum (e.phyrds - nvl(b.phyrds,0))

reads , Round(sum (e.phyrds - nvl(b.phyrds,0))/awr101.getEla( :

pDbId,:pInstNum,:pBgnSnap,:pEndSnap,'NO' ),3) rps , Round(decode( sum(e.p

hyrds - nvl(b.phyrds,0)) , 0, 0 , (sum(e.readtim - nvl

(b.readtim,0)) / sum(e.phyrds - nvl(b.phyrds,0)))*10),3)

atpr , Round(decode( sum(e.phyrds - nvl(b.phyrds,0)) , 0, to_n

umber(NULL) , sum(e.phyblkrd - nvl(b.phyblkrd,0)) /

sum(e.phyrds - nvl(b.phyrds,0)) ),3) bpr , sum (e.phywrts - n

vl(b.phywrts,0)) writes , Round(sum (e.phywrts - nvl(b.ph

ywrts,0))/awr101.getEla( :pDbId,:pInstNu

174

58 DABR

select e.stat_name "E.STAT_NAME" , (e.value - b.value

)/1000000 "Time (s)" , decode( e.stat_name,'DB time' ,

to_number(null) , 100*(e.value - b.value) )/awr101.get

DBTime(:pDbId,:pInstNum,:pBgnSnap,:pEndSnap) "Percent of Total DB Time" from d

ba_hist_sys_time_model e , dba_hist_sys_time_model b where b.snap_id

= :pBgnSnap and e.snap_id = :pEndSnap and b.dbid

= :pDbId and e.dbid = :pDbId and b.ins

tance_number = :pInstNum and e.instance_number = :pInstNum a

nd b.stat_id = e.stat_id and e.value - b.value > 0 order by 2

desc

Once the SQL details have been identified, the DBA can drill down deeper by joining v$active_session_history with dba_objects and find important information about the interaction between the SQL and specific tables and indexes. What follows is an ASH script. that can be used to show the specific events that are causing the highest resource waits. Also, remember that some contention is NOT caused by SQL but by faulty network, slow disk or some other external causes. Also, frequent deadlocks may be caused by improperly indexed foreign keys.

· ash_obj_waits.sql

select

obj.object_name,

obj.object_type,

ash.event,

sum(ash.wait_time + ash.time_waited) wait_time

from

v$active_session_history ash,

dba_objects obj

where

ash.sample_time > sysdate -1

and

ash.current_obj# = obj.object_id

group by

obj.object_name,

obj.object_type,

ash.event

order by 4 desc;

The following is sample output from this script.:

OBJECT_NAME OBJECT_TYPE EVENT WAIT_TIME

-------------------- ------------- ------------------------------ -------

SCHEDULER$_CLASS TABLE rdbms ipc message 199,853,456

USER$ TABLE rdbms ipc message 33,857,135

USER$ TABLE control file sequential read 288,266

WRI$_ALERT_HISTORY TABLE db file sequential read 26,002

OL_SCP_PK INDEX db file sequential read 19,638

C_OBJ# CLUSTER db file sequential read 17,966

STATS$SYS_TIME_MODEL TABLE db file scattered read 16,085

WRI$_ADV_DEFINITIONS INDEX db file sequential read 15,995

It is apparent that table wri$_alert_history experiences a high wait time on db file sequential read wait event. Based on this fact, the DBA can further investigate causes of such behavior. in order to find the primary problem. It could be, for example, a non-optimal SQL query that performs large full table scans on this table.

Now that it’s been shown how ASH information can enlighten DBAs about specific wait events for active session, it is time to return to the detailed information on instance wide tuning and see how to optimize the Oracle data buffer pools.

Oracle Wait Event - Tuning

相關文章