| |
 |
|
Oracle Tips by Burleson |
System Events by Percent
The System Events by Percent report shows the major events in the
database and their contribution to overall response time.
Comparison of CPU usage to event wait time will help to confirm that
wait events are actually the problem. If CPU time is the major
component to the response time profile, tuning events will make
little difference to the overall performance:
* SYS_EVENTS_PCT.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
-- *************************************************
rem
rem SYS_EVENTS_PCT.SQL
rem Mike Ault
rem
rem This report shows the major events in the database and
rem their contribution to overall response time.
rem
rem
col event format a30 heading 'Event Name'
col waits format 999,999,999 heading 'Total|Waits'
col average_wait format 999,999,999 heading 'Average|Waits'
col time_waited format 999,999,999 heading 'Time Waited'
col total_time new_value divide_by noprint
col value new_value val noprint
col percent format 999.990 heading 'Percent|Of|Non-Idle Waits'
col duration new_value millisec noprint
col p_of_total heading 'Percent|of Total|Uptime' format 999.9999
set lines 132 feedback off verify off pages 50
select to_number(sysdate-startup_time)*86400*1000 duration
from v$instance
;
select
sum(time_waited) total_time
from v$system_event
where total_waits-total_timeouts>0
and event not like 'SQL*Net%'
and event not like 'smon%'
and event not like 'pmon%'
and event not like 'rdbms%'
and event not like 'PX%'
and event not like 'sbt%'
and event not in ('gcs remote message','ges remote message',
'virtual circuit status','dispatcher timer')
;
select value
from v$sysstat
where name ='CPU used when call started'
;
ttitle 'System Events Percent'
break on report
compute sum of time_waited on report
spool sys_events
select name event,
0 waits,
0 average_wait,
value time_waited,
value/(&÷_by+&&val)*100 Percent,
value/&&millisec*100 p_of_total
from v$sysstat
where name ='CPU used when call started'
union
select event,
total_waits-total_timeouts waits,
time_waited/(total_waits-total_timeouts) average_wait,
time_waited,
time_waited/(&÷_by+&&val)*100 Percent,
time_waited/&&millisec*100 P_of_total
from v$system_event
where total_waits-total_timeouts > 0
and event not like 'SQL*Net%'
and event not like 'smon%'
and event not like 'pmon%'
and event not like 'rdbms%'
and event not like 'PX%'
and event not like 'sbt%'
and event not in ('gcs remote message','ges remote message',
'virtual circuit status','dispatcher timer')
and time_waited > 0
order by percent desc
;
spool off
clear columns
ttitle off
clear computes
clear breaks
The above book excerpt is from:
Oracle
Tuning Power Scripts
With 100+ High Performance
SQL Scripts
Oracle In-Focus Series
ISBN
0-9744486-7-2
Mike Ault, Donald K.
Burleson. Harry Conway
http://www.rampant-books.com/book_2005_1_power_tuning.htm |
