|
Finding Database Bottlenecks
Every Oracle database has at least one physical constraint, and
it is not always disk. The best way to isolate the constraints
in the system is to analyze the top five wait events for the
database and look for external waits associated with disk, CPU
and network.
The best way to see system-level wait summaries is to run the
awrrpt.sql script from the $ORACLE_HOME/rdbms/admin directory.
This standard Oracle time-series report will show all
performance aspects on the database during the specified time
period, and most important of all, yield the top 5 timed events
for the specific interval between AWR snapshots.
Oracle Bottlenecks are an onerous form of bondage
The script below, wait_time_detail.sql, compares the wait event
values from dba_hist_waitstat and dba_hist_active_sess_history.
This approach allows the identification of the exact objects
that are experiencing wait events.
wait_time_detail.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
set pages 999
set lines 80
break on snap_time skip 2
col snap_time heading 'Snap|Time' format a20
col file_name heading 'File|Name' format a40
col object_type heading 'Object|Type' format a10
col object_name heading 'Object|Name' format a20
col wait_count heading 'Wait|Count' format 999,999
col time heading 'Time' format 999,999
select
to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') snap_time,
-- file_name,
object_type,
object_name,
wait_count,
time
from
dba_hist_waitstat wait,
dba_hist_snapshot snap,
dba_hist_active_sess_history ash,
dba_data_files df,
dba_objects obj
where
wait.snap_id = snap.snap_id
and
wait.snap_id = ash.snap_id
and
df.file_id = ash.current_file#
and
obj.object_id = ash.current_obj#
and
wait_count > 50
order by
to_char(begin_interval_time,'yyyy-mm-dd hh24:mi'),
file_name
;
|
