query perf

 

set serveroutput on
set line 200
col DBStatus for a10
col hostname for a15
col os for a30
col instance_name for a15

select INSTANCE_NAME,DBNAME,DBSTATUS,LOAD,CPU,"cpu%",PHY_MEM,SGA_INMB,PGA_INMB,"pgaused%",HOSTNAME,sum(wait) wait,OS
from(select
   instance_name||'-'||INSTANCE_NUMBER instance_name,
   db.name DBNAME,
   decode(db.open_mode,'READ WRITE','GREEN','READ ONLY','STANDBY','RED') DBStatus,
   round(sub.load) load,
   sub.NUM_CPUS CPU,
   round(sub.busy_time/(sub.busy_time +sub.idle_time),2) as "cpu%",
   (round(sub.PHYSICAL_MEMORY_BYTES/1024/1024/1024)) PHY_MeM,
   (select round(sum(value)/1024/1024)  from v$sga) sga_InMB,
   round((sub1.pga_allocated/1024/1024)) pga_InMB,
   round(((sub1.PGA_inuse/1024/1024)/(sub1.pga_allocated/1024/1024))*100) "pgaused%",
   HOST_NAME Hostname,
   sub2.wait_cnt wait,
   (select dbms_utility.port_string from dual) OS
   from
(SELECT
       SUM(DECODE(stat_name, 'NUM_CPUS', value, 0)) AS NUM_CPUS,
       SUM(DECODE(stat_name, 'IDLE_TIME', value, 0)) AS IDLE_TIME,
       SUM(DECODE(stat_name, 'BUSY_TIME', value, 0)) AS BUSY_TIME,
       SUM(DECODE(stat_name, 'USER_TIME', value, 0)) AS USER_TIME,
       SUM(DECODE(stat_name, 'SYS_TIME', value, 0)) AS SYS_TIME,
       SUM(DECODE(stat_name, 'IOWAIT_TIME', value, 0)) AS IOWAIT_TIME,
       SUM(DECODE(stat_name, 'NICE_TIME', value, 0)) AS NICE_TIME,
       SUM(DECODE(stat_name, 'LOAD', value, 0)) AS LOAD,
       SUM(DECODE(stat_name, 'PHYSICAL_MEMORY_BYTES', value, 0)) AS PHYSICAL_MEMORY_BYTES
FROM   v$osstat
ORDER BY stat_name) sub,
v$database db,
v$instance,
(select
      SUM(DECODE(name, 'total PGA allocated', value, 0)) pga_allocated,
      SUM(DECODE(name, 'total PGA inuse', value, 0)) AS  PGA_inuse,
      SUM(DECODE(name, 'maximum PGA allocated', value, 0)) AS max_pga_allocated,
      SUM(DECODE(name, 'total freeable PGA memory', value, 0)) AS PGA_free
FROM v$pgastat
order by NAME) sub1,
(select inst_id, rpad( substr(max(tevent),6,100), 25,' ')  || to_char(substr(max(tevent),1,5),'999') topevt,
                        substr(max(twaitclass),6,100)  wait_class , sum(cnt)  wait_cnt
        from
        (
                        select inst_id,  count(*)  cnt  ,event,wait_class ,
                                lpad(count(*),5,'0') ||event  tevent ,
                                lpad(count(*),5,'0') ||wait_class  twaitclass
                        from gv$session
                        where wait_class !='Idle'
                        group by inst_id,wait_class,event
        )
        group by inst_id
        order by inst_id) sub2
        )
    group by INSTANCE_NAME,DBNAME,DBSTATUS,LOAD,CPU,"cpu%",PHY_MEM,SGA_INMB,PGA_INMB,"pgaused%",HOSTNAME,OS;


******
Output:
******

    INSTANCE_NAME   DBNAME    DBSTATUS         LOAD        CPU       cpu%    PHY_MEM   SGA_INMB   PGA_INMB   pgaused% HOSTNAME              WAIT OS
    --------------- --------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- ---------- ------------------------------
    p5enb2d52-2     P5ENB2D5  GREEN               6        160        .06       2020     130487       1625         85 blph340                  3 x86_64/Linux 2.4.xx