The v$active_session_history XE "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;

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 XE "v$active_session_history" table can be joined into the v$sqlarea XE "v$sqlarea" and dba_users XE "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;

Once the SQL details have been identified, the DBA can drill down deeper by joining v$active_session_history XE "v$active_session_history" with dba_objects XE "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;

ZT：instance Wait Tuning

相關文章