oracle中的wait obj,Oracle Wait Event - Tuning

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_historyexperiences 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.