#!/bin/csh
#
#           Name:   sesscpu.csh
#
#           Author: Mark Gurry
#
#           This script lists all of the major CPU users over a 30 second period. It should be run at   #           peak usage times on your database, particularly when performance is not satisfactory, to #           determine who the largest resource users are and what statements they are running.
#           You will have to modify user/pass with the username and password for your site.
#
sqlplus user/pass                      << END

set heading off
set pagesize 999

drop table highest_cpu_usage;
drop table user_hit_ratio_start;  

create table user_hit_ratio_start as
  select  se.username||'('|| se.sid||')' "User Session",
         sum(decode(name, 'consistent gets',value, 0))  "Consis Gets",
          sum(decode(name, 'db block gets',value, 0))  "DB Blk Gets",
          sum(decode(name, 'physical reads',value, 0))  "Phys Reads",
         (sum(decode(name, 'consistent gets',value, 0))  +
          sum(decode(name, 'db block gets',value, 0))  -
          sum(decode(name, 'physical reads',value, 0)))
  		       /
         (sum(decode(name, 'consistent gets',value, 0))  +
          sum(decode(name, 'db block gets',value, 0))  )  * 100 "Hit Ratio" 
    from  v\$sesstat ss, v\$statname sn, v\$session se
  where   ss.sid    = se.sid
    and   sn.statistic# = ss.statistic#
    and   value != 0
    and   sn.name in ('db block gets', 'consistent gets', 'physical reads')
  group by se.username, se.sid
/

create index  user_hit_ratio_start_ndx1 on  user_hit_ratio_start ("User Session");

create table highest_cpu_usage (user_session ,  cpu_usage , sql_address, sql_hash_value )
as
select   ss.username||'('||se.sid||') ' user_process, value, sql_address, sql_hash_value
  from v\$session ss, v\$sesstat se, v\$statname sn
 where  se.statistic# = sn.statistic#
   and  name  like '%CPU used by this session%'
   and  se.sid = ss.sid
/

create index  highest_cpu_usage_ndx1 on  highest_cpu_usage (user_session);
exit 
END

sleep 30

sqlplus user/pass                      << END

set heading off
set pagesize 999

drop table highest_end_cpu_usage;

create table highest_end_cpu_usage (user_session ,  cpu_usage, sql_address, sql_hash_value )
as
select   ss.username||'('||se.sid||') ' user_process, value, sql_address, sql_hash_value  
  from v\$session ss, v\$sesstat se, v\$statname sn
 where  se.statistic# = sn.statistic#
   and  name  like '%CPU used by this session%'
   and  se.sid = ss.sid
/
create index  highest_end_cpu_usage_ndx1 on  highest_end_cpu_usage (user_session);
column "CPU Usage" format 999.99
column user_session format a20
column nl newline;

drop   table highest_cpu_users_now ;

drop table user_hit_ratio_end;         

create table user_hit_ratio_end as
  select  se.username||'('|| se.sid||')' "User Session",
         sum(decode(name, 'consistent gets',value, 0))  "Consis Gets",
          sum(decode(name, 'db block gets',value, 0))  "DB Blk Gets",
          sum(decode(name, 'physical reads',value, 0))  "Phys Reads",
         (sum(decode(name, 'consistent gets',value, 0))  +
          sum(decode(name, 'db block gets',value, 0))  -
          sum(decode(name, 'physical reads',value, 0)))
  		       /
         (sum(decode(name, 'consistent gets',value, 0))  +
          sum(decode(name, 'db block gets',value, 0))  )  * 100 "Hit Ratio" 
    from  v\$sesstat ss, v\$statname sn, v\$session se
  where   ss.sid    = se.sid
    and   sn.statistic# = ss.statistic#
    and   value != 0
    and   sn.name in ('db block gets', 'consistent gets', 'physical reads')
  group by se.username, se.sid
/

create index  user_hit_ratio_end_ndx1 on  user_hit_ratio_end   ("User Session");

create table highest_cpu_users_now 
as  select a.user_session, (to_number(b.cpu_usage) - to_number(a.cpu_usage)) / 100 "CPU Usage", 
                  a.sql_address, a.sql_hash_value 
      from highest_cpu_usage a, highest_end_cpu_usage b
     where a.user_session = b.user_session
       and  to_number(b.cpu_usage) - to_number(a.cpu_usage) != 0
       and  to_number(b.cpu_usage) - to_number(a.cpu_usage) > 200
       and  a.sql_address = b.sql_address
       and  a.sql_hash_value  = b.sql_hash_value
/

set linesize 80 

spool $HOME/sesscpu.lis

ttitle 'User Hit Ratios Recorded over the Time Period'

select  substr(a."User Session", 1, 12),
          (a."Consis Gets" - b."Consis Gets" 
                                  +
           a."DB Blk Gets" - b."DB Blk Gets" ) "Interval Logical Reads",
          a."Phys Reads" - b."Phys Reads" "Interval Disk Reads",
          ( ( a."Consis Gets" - b."Consis Gets")  +
             (a."DB Blk Gets" - b."DB Blk Gets") -
             (a."Phys Reads" - b."Phys Reads")) 
  		       /
          ( ( a."Consis Gets" - b."Consis Gets")  +
            ( a."DB Blk Gets" - b."DB Blk Gets" )) * 100 "Interval Hit Ratio"
    from  user_hit_ratio_end a , user_hit_ratio_start b
  where   a."User Session" = b."User Session"
    and    ( a."Consis Gets" - b."Consis Gets")  + (a."DB Blk Gets" - b."DB Blk Gets") > 100
    and    ( a."Phys Reads" - b."Phys Reads") > 100
/

ttitle 'SQL Code that has been Running for at least 30 Seconds'

select sql_text nl,
       'Average Daily Expected Response Time in Seconds = '|| disk_reads / decode(executions,0,1,executions) / 50 nl , 
       'Daily Executions = '||executions nl,
       'Username Currently Accessing Statement  = '|| user_session
  from v\$sqlarea a,   highest_cpu_users_now c
 where  a.address = c.sql_address
  and   a.hash_value = c.sql_hash_value
/


select sql_text nl,
       'Average Daily Expected Response Time in Seconds = '|| buffer_gets / decode(executions,0,1,executions) / 500 nl , 
       'Daily Executions = '||executions nl,
       'Username Currently Accessing Statement  = '|| user_session
  from v\$sqlarea a,   highest_cpu_users_now c
 where  a.address = c.sql_address
  and   a.hash_value = c.sql_hash_value
/


ttitle 'Users Using Most CPU for the Time Period '

select a.user_session, (to_number(b.cpu_usage) - to_number(a.cpu_usage)) / 100 "CPU Usage"
      from highest_cpu_usage a, highest_end_cpu_usage b
     where a.user_session = b.user_session
       and  to_number(b.cpu_usage) - to_number(a.cpu_usage) != 0
       and  to_number(b.cpu_usage) - to_number(a.cpu_usage) > 200
/


spool off
drop table highest_cpu_usage;
drop table user_hit_ratio_start;  
drop table highest_end_cpu_usage;

exit   
END