Oracle DB Health Scripts 2 - I/O,cpu_time,user_time,wait_time,PGA_memory

SELECT   mymodule "Module", SUM (cpu_time) "CPU Time", SUM (wait_time) "Wait
Time",
         SUM (cpu_time) + SUM (wait_time) "Total Time"
    FROM (SELECT a.module mymodule,
                 (CASE (session_state)
                     WHEN 'ON CPU'
                        THEN wait_time / 100
                  END
                 ) cpu_time,
                 (CASE (session_state)
                     WHEN 'WAITING'
                        THEN time_waited / 100
                  END
                 ) wait_time
            FROM dba_hist_active_sess_history a, dba_hist_snapshot b
           WHERE b.end_interval_time > sysdate-10
             AND a.snap_id = b.snap_id
             AND a.user_id NOT IN (0, 5)
             AND a.instance_number = b.instance_number)
GROUP BY mymodule
  HAVING SUM (cpu_time) + SUM (wait_time) > 0
ORDER BY 2 DESC;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select
                 decode(n.wait_class,'User I/O','User I/O',
                                     'Commit','Commit',
                                     'Wait')                               CLASS,
                 sum(round(m.time_waited/m.INTSIZE_CSEC,3))                AAS
           from  v$waitclassmetric  m,
                 v$system_wait_class n
           where m.wait_class_id=n.wait_class_id
             and n.wait_class != 'Idle'
           group by  decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait')
          union
             select 'CPU_ORA_CONSUMED'                                     CLASS,
                    round(value/100,3)                                     AAS
             from v$sysmetric
             where metric_name='CPU Usage Per Sec'
               and group_id=2
          union
            select 'CPU_OS'                                                CLASS ,
                    round((prcnt.busy*parameter.cpu_count)/100,3)          AAS
            from
              ( select value busy from v$sysmetric where metric_name='Host CPU Utilization (%)' and group_id=2 ) prcnt,
              ( select value cpu_count from v$parameter where name='cpu_count' )  parameter
          union
             select
               'CPU_ORA_DEMAND'                                            CLASS,
               nvl(round( sum(decode(session_state,'ON CPU',1,0))/60,2),0) AAS
             from v$active_session_history ash
             where SAMPLE_TIME > sysdate - (12/24);

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select name,round(value/1024/1024) MB from v$pgastat where name in ('total PGA allocated','maximum 

PGA allocated')

union all

select 'alloc v$process',round(sum(pga_alloc_mem)/1024/1024) MB from v$process

union all

select 'max PGA v$process',round(sum(pga_max_mem)/1024/1024) MB from v$process

union all

select name,round(value/1024/1024) MB from v$sysstat where name like '%pga%';

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

session and Process wise PGA memory usage :-

SELECT   to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||

                  nvl(lower(ssn.machine), ins.host_name) "SESSION",

             to_char(prc.spid, '999999999') "PID/THREAD",

             to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' "      CURRENT SIZE",

             to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' "      MAXIMUM SIZE"

    FROM     v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,

          v$instance ins,  v$statname stat1, v$statname stat2

 WHERE    se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'

 AND      se2.statistic#  = stat2.statistic# and stat2.name = 'session pga memory max'

 AND      se1.sid        = ssn.sid

 AND      se2.sid        = ssn.sid

 AND      ssn.paddr      = bgp.paddr (+)

 AND      ssn.paddr      = prc.addr  (+)

ORDER BY &sort_order DESC;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

session and Process wise UGA memory usage :-

SELECT   to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||

                  nvl(lower(ssn.machine), ins.host_name) "SESSION",

             to_char(prc.spid, '999999999') "PID/THREAD",

             to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' "      CURRENT SIZE",

             to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' "      MAXIMUM SIZE"

    FROM     v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,

          v$instance ins,  v$statname stat1, v$statname stat2

 WHERE    se1.statistic# = stat1.statistic# and stat1.name = 'session uga memory'

 AND      se2.statistic#  = stat2.statistic# and stat2.name = 'session uga memory max'

 AND      se1.sid        = ssn.sid

 AND      se2.sid        = ssn.sid

 AND      ssn.paddr      = bgp.paddr (+)

 AND      ssn.paddr      = prc.addr  (+)

ORDER BY &sort_order DESC;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++