http://www.praetoriate.com/t_oracle_wait_tuning.htm
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;
