sysprof.sql

Oracle optimizing performance things

/* $Header: /home/cvs/cvm-book1/sql/sysprof.sql,v 1.2 2003/04/24 05:19:20 cvm Exp $
Cary Millsap ([e-mail])
Copyright (c) 2002 by Hotsos Enterprises, Ltd. All rights reserved.

This program creates an approximate resource profile for a system. Note,
however, that the very concept of attributing time_waited as a proportion
of instance uptime makes no sense, because it doesn't take into account
the varying number of sessions that are active at different times in the
history of the instance.
*/

set echo off feedback on termout on linesize 75 pagesize 66
clear col break compute
undef instance_uptime cpu_consumption event_duration delta

/* compute total instance uptime */
col td format 999,999,999,990 new_value instance_uptime
select (sysdate-startup_time)*(60*60*24) td from v$instance;

/* compute total Oracle kernel CPU consumption */
col cd format 999,999,999,990 new_value cpu_consumption
select value/100 cd from v$sysstat
where name = 'CPU used by this session';

/* compute total event duration */
col ed format 999,999,999,990 new_value event_duration
select sum(time_waited)/100 ed from v$system_event;

/* compute unaccounted-for duration */
col dd format 999,999,999,990 new_value delta
select &instance_uptime - (&cpu_consumption + &event_duration) dd
from dual;

/* compute the resource profile */
col e format a30                 head 'Event'
col t format 99,999,990.00       head 'Duration'
col p format 990.9               head '%'
col w format 999,999,999,999,990 head 'Calls'
break on report
compute sum label TOTAL of w t p on report
select
  'CPU service' e,
   &cpu_consumption t,
   (&cpu_consumption)/(&instance_uptime)*100 p,
   (select value from v$sysstat where name = 'user calls') w
from dual
union
select
  'unaccounted for' e,
  &delta t,
  (&delta)/(&instance_uptime)*100 p,
  NULL w
from dual
union
select
  e.event e,
  e.time_waited/100 t,
  (e.time_waited/100)/(&instance_uptime)*100 p,
  e.total_waits w
from v$system_event e
order by t desc
/