 |
 |
 |
 |
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
|
|||
 |
|||
|
|||
 |
|||
|
|||
|
|
 |
Donald K. Burleson Oracle Tips |
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.
SEE CODE DEPOT FOR FULL SCRIPTS
 |
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts: |
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
|
|
Copyright © 1996 - 2011 by
Burleson Enterprises. All rights reserved.
Oracle® is the registered trademark
of Oracle Corporation.