Perf_status

set lin 450
set pages 100
col perf_status for a100
select PerfMetric " Ins:1 - Performance" , perf_status
FROM (
select PerfMetric , decode(substr(perf_status,1,1), '@',substr(perf_status,2,length(perf_status)), 'X',substr(perf_status,2,length(perf_status)), perf_status) perf_status , decode(substr(ltrim(rtrim(perf_status)),1,1),'*',999,'0',-1,'X',-10,'@',-10,length(ltrim(rtrim(perf_status)))) perf_order
from (
SELECT 'Buffer Cache hit Ratio : ' PerfMetric, (
select CASE WHEN buf_hit_ratio < 90 THEN '*** Warning *** Hit Ratio is LOW '|| buf_hit_ratio ELSE buf_hit_ratio ||' ' END IF
FROM (
select round( ( 1 - ( preads/lreads )) * 100,0) buf_hit_ratio
from (
select (
select sum(value)
from v$sysstat
where name in ('db block gets', 'consistent gets')) lreads, (
select sum(value) lreads
from v$sysstat
where name in ('physical reads') ) preads
from dual ) ) ) perf_status
FROM DUAL UNION
SELECT 'Library Cache Hit Ratio : ' , (
SELECT round((SUM(RELOADS)/sum(pins) ) * 100 ,2) || ' '
FROM V$LIBRARYCACHE )
From dual UNION
SELECT 'Long Running Sessions : ', (
select case when longrunning > 0 THEN '*** Warning *** Long running SQLS :'|| longrunning || ' - MaxTime '|| maxtime ||' hrs' ELSE longrunning ||' ' END IF
FROM (
select count(*) longrunning, round (max(elapsed_time/1000000/3600),2) maxtime
from gv$sqL_monitor a
where status in ( 'QUEUED','EXECUTING' ) and elapsed_time/(1000000) > 300
--select count(*) longrunning   from
--gv$session  vs
--where vs.status ='ACTIVE'
--and username is not null
--and inst_id = userenv('INSTANCE')
--and program not like 'oracle@%'
--and last_call_et > 600
) )
from dual UNION
--SELECT 'Session Details                : ',
--case when totalsess/allowedsess * 100 > 75
--then  '*** Warning *** Close to reaching MaxSession - ' || TotalSess ||'/'|| AllowedSess
--else    TotalSess ||'/'|| AllowedSess
--end
--from  (
--SELECT a.inst_id, ActiveSess, TotalSess,   allowedSess, (totalsess/allowedsess) * 100  connper
--FROM ( select /*+ rule */  inst_id,count(*) ActiveSess from gv$session where status ='ACTIVE' group by inst_id) a,
------(select /*+ rule */  inst_id,count(*) TotalSess from gv$session  group by inst_id) b,
--( select inst_id, value allowedSess from gv$parameter where name ='processes') c
--WHERE   a.inst_id =b.inst_id
--AND     a.inst_id = c.inst_id
--)
--UNION
SELECT 'Active SQLs > 5Mins : ', case when SerialSqlids > 5 or prllsqlidis > 5 then '*** Warning *** Active Sqids '||SerialSqlids ||', Parallel SQLids - '|| prllsqlidis else nvl(SerialSqlids,0) ||decode(prllsqlidis,0,' ',null,'',', Parallel Sqls '|| prllsqlidis ) end ActiveSqls
FROM (
select sum(decode(sql_type,'S',1,0)) SerialSqlids, sum(decode(sql_type,'P',1,0)) prllsqlidis
from (
select SQL_ID,decode( count(distinct px_qcsid),1,'P',0,'S') sql_type
from gv$sqL_monitor
where status in ( 'QUEUED','EXECUTING' ) group by sql_id) ) UNION
SELECT 'Avg Log Switches per Hour : ', case when avg_switch > 10 then '*** Warning *** Avg per hr '||round(Avg_switch)||' - Max Switches ('|| max_switch ||') at '||timeobserved else round(avg_switch) ||' ' end if
from (
select avg(switch) avg_switch, max( switch) max_switch
from (
SELECT inst_id, to_char(first_time,'HH24') TimeObserved ,count(*) switch
FROM gV$LOG_HISTORY a
where a.inst_id = a.thread# and a.thread#=userenv('INSTANCE') and first_time > SYSDATE - (480 / (24 * 60)) group by inst_id, to_char(first_time,'HH24') )) a, (
select Timeobserved
from (
SELECT inst_id, to_char(first_time,'DD-MON-YY HH24') TimeObserved ,count(*) switch
FROM gV$LOG_HISTORY a
where a.inst_id = a.thread# and a.thread#=userenv('INSTANCE') and first_time > SYSDATE - (480 / (24 * 60)) group by inst_id, to_char(first_time,'DD-MON-YY HH24') order by 3 desc )
where rownum < 2 ) UNION
SELECT 'Blocking Sessions : ', (
SELECT CASE WHEN blkedSess > 0 THEN '*** Warning *** Blocked Sessions found - '||blkedSess ELSE blkedsess ||' ' END IF
FROM (
select count(*) blkedSess
from v$session
where blocking_session_status ='VALID' ) )
FROM DUAL UNION
SELECT 'Undo Segments Status : ', (
SELECT CASE WHEN undogb > 5 then '*** Warning *** High UNEXPIRED UNDO '|| undogb ||' GB , ' || (
select value
from v$parameter
where name='undo_retention') ||'s retn' else undogb ||' ' end undousage
from (
select round( sum(bytes)/(1048576*1024)) undogb
from DBA_UNDO_EXTENTS
where tablespace_name = (
select value
from v$parameter
where name ='undo_tablespace' ) and status !='EXPIRED' ) )
FROM DUAL UNION
SELECT 'Invalid Password Attempts : ', (
SELECT count(*)||' ' invalid_passwd_cnt
from sys.user$
where lcount> 10 )
FROM DUAL UNION
SELECT 'LogOn Triggers : ', (
select count(*) ||' '
from dba_triggers
where TRIGGERING_EVENT like 'LOGON%' )
FROM DUAL UNION
--SELECT   'AWR  Last Generated           : ',
--(
--select round((sysdate - TO_DATE (TO_CHAR (max_awr_date, 'YYYY-MON-DD HH24:MI:SS'), 'YYYY-MON-DD HH24:MI:SS') ) * 1440  ,0) || '  Minutes back'
--from ( select max(end_interval_time) max_awr_date from dba_hist_snapshot where dbid = ( select dbid from v$database)  )
--)
--FROM DUAL
select 'AWR Last Generated : ', case when days > 1 then '*** AWR not generated for '|| days ||' Days ***' when minute1 > snap_interval then '*** AWR not generated for '|| minute ||' Minutes'
--ELSE minute || ' minutes  back - ' || last_time
ELSE minute ||' ' end Comments
from (
SELECT round ( sysdate - max(trunc(end_interval_time))) days , round ( (cast(sysdate as date) - cast(max(end_INTERVAL_TIME) as date)) *1400 ) minute, sysdate - max(end_INTERVAL_TIME) minute1 , max(end_interval_time ) last_time
FROM dba_hist_snapshot
WHERE dbid = (
select dbid
from v$database)) , dba_hist_wr_control UNION
select 'Max Open Cursors by Session : ', case when ( max_open_cursors/allowed * 100 ) > 50 then '*** Warning *** Too many Open Cursors - ' || max_open_cursors ||' out of '|| Allowed || ' ***' else max_open_cursors || ' / '|| Allowed end
from (
select (
select max(count(*))
from v$open_cursor a group by sid) max_open_cursors , Value Allowed
from v$parameter b
where name ='open_cursors' )
--SELECT  'Max Open Cursors by Session   : ', ( select max(count(*)) from  v$open_cursor group by sid ) || ' of  '|| ( select value from v$parameter where name='open_cursors')
--FROM DUAL
UNION
SELECT 'Host Status : ', CASE WHEN cpu_usage > 70 or load/cpus > 3 THEN '*** Warning *** HOST has very Busy ' || 'LOAD : '||Load ||' CPU Usage : '||cpu_usage ELSE 'LOAD : '||Load ||' CPU Usage : '||cpu_usage END
from (
select round(max(decode(stat_name,'LOAD',value))) load, max(decode(stat_name,'NUM_CPUS',value)) cpus, max(decode(stat_name,'CPU_USAGE',value)) cpu_usage
from (
select stat_name, value
from v$osstat
where stat_name in ( 'LOAD','NUM_CPUS') union
select 'CPU_USAGE' stat_name, round(value,2) value
from gv$sysmetric
where metric_name ='Host CPU Utilization (%)' and group_id = 3 ) ) UNION
SELECT 'DB IO Performance : ', (
select round(totwaittim/totwait,2) || ' ms '
from
(select event,sum(wait_count) as totwait,sum (wait_time_milli*wait_count) as totwaittim
from v$event_histogram
where event ='db file sequential read' group by event) )
FROM DUAL UNION
SELECT 'PGA Usage : ', (
SELECT CASE when round(pgausage/paramvalue * 100,0) > 100 THEN '*** Warning *** ' || round(pgausage/paramvalue * 100,0) || ' % of ' || paramvalue ||' MB Used' ELSE pgausage||' MB' END IF
FROM (
select round(SUM(pga_used_mem)/(1048576),0) pgausage
from v$process ) , (
select round(decode(value,0 , (
select value
from v$parameter
where name in ('memory_target') ),value)/ 1048576,0) paramvalue
from v$parameter
where name ='pga_aggregate_target' ) )
FROM DUAL UNION
SELECT 'Sort Usage - TEMP TBS : ' , (
SELECT CASE when Sortusage > 0.5 THEN '*** Warning *** Sort Usage is High - ' || sortusage ELSE sortusage||' ' END IF
FROM (
select round(sum(u.blocks)*8192/1073741824,2) sortusage
from gv$session s, gv$sort_usage u
where s.saddr = u.session_addr and s.inst_id= u.inst_id and u.contents = 'TEMPORARY' and s.inst_id = userenv('instance') group by s.inst_id ) )
FROM DUAL UNION
SELECT 'Total Memory Usage : ' , (
select round(
((select SUM(pga_alloc_mem)/(1073741824)
from v$process ) + (
select sum(value )/1073741824
from v$sga )) ,0) || ' GB of ' || (
select round((value/1073741824),0)
from v$osstat
where stat_name = 'PHYSICAL_MEMORY_BYTES' ) || 'GB (Host PhyMemory)'
from dual )
FROM DUAL UNION
SELECT 'SYSAUX Space Usage : ' , (
SELECT CASE when sysauxsize > 15 THEN '*** Warning *** SYSAUX HIGH USAGE - '||sysauxsize ||' GB' ELSE sysauxsize ||' GB' END IF
FROM (
select round(sum(bytes) / (1073741824),0) sysauxsize
from dba_segments
where tablespace_name ='SYSAUX' ) )
FROM DUAL UNION
SELECT 'Recyclebin Space Usage : ' , (
SELECT CASE when recyclesize > 1 THEN '*** Warning *** Purge Recyclebin '||recyclesize ||' GB' WHEN recyclesize > 0 THEN recyclesize||' GB' ELSE '0' END IF
FROM (
select decode(sum(space),0,'0', round(( sum(space)*8198) / 1073741824,2)) recyclesize
from dba_recyclebin ) )
FROM DUAL UNION
SELECT 'UNDO Space shortage : ' , (
SELECT decode( undospace,null,'0',to_char(undospace,'999,999'))
FROM (
SELECT round( avg(UNXPSTEALCNT)), round( avg(EXPBLKREUCNT)) , round ( avg(NOSPACEERRCNT)) undospace
FROM v$UNDOSTAT
where ( UNXPSTEALCNT !=0 or EXPBLKREUCNT !=0 or NOSPACEERRCNT !=0 ) ) )
FROM DUAL UNION
SELECT 'Current Rollback Usage : ' , (
SELECT CASE when Rollbk > 200 THEN '*** Warning *** Rollback is High '||round(Rollbk) ||' MB' ELSE Rollbk || ' ' END IF
FROM (
select nvl( Round ( sum(used_ublk)* 8196 / (1048576),0),0) rollbk
from v$transaction ) )
FROM DUAL UNION
SELECT 'Transaction Recovery : ', (
SELECT CASE when cnt > 0 THEN '*** Warning *** - Transaction Recovery occuring'|| cnt ||'Trans - ' ||BlksPending ELSE '0' END IF
FROM (
select count(*) cnt, sum( undoblockstotal-undoblocksdone) BlksPending
from gv$fast_start_transactions
where undoblockstotal-undoblocksdone > 0 ) )
FROM DUAL UNION
SELECT 'Aud$ Size - Should be small : ' , (
SELECT CASE when AUdSize > 1000 THEN '*** Warning *** AUD$ size is High '||round(Audsize) ||' MB' ELSE decode(AudSize,0,audSIze||' ',AudSize||'MB') END IF
FROM (
select round(sum(bytes)/(1048576)) audsize
from dba_segments
where segment_name in ( 'AUD$','SYS_AUD$_COPY')) )
FROM DUAL UNION
SELECT 'Integrated Capture : ' , decode(IC_status, null, '0', ic_status )
from (
select listagg(Decode(status,'ABORTED','*** Warning *** ABORTED-',status) ||' -'||count(*)||' ') within group ( order by count(*) ) as IC_Status
from dba_capture group by status ) UNION
SELECT 'Redolog Status : ' , (
SELECT case when activelog > 2 or NotArchived > 2 THEN '*** Warning *** Active Logs - '||activelog ||', Not Archived ' ||NotArchived ELSE 'Active -' || Activelog ||' Total - '||totalLog END RedologStatus
FROM (
SELECT sum(decode(status,'ACTIVE',1,0)) ActiveLog,sum(decode(Archived,'NO',1,0)) NOtArchived, count(*) TotalLog
FROM gV$LOG a
where a.inst_id = a.thread# and a.thread#=userenv('INSTANCE') ) )
FROM DUAL UNION
SELECT 'Result Cache Invalid : ' , (
SELECT CASE WHEN resultCache > 1 THEN '*** Warning *** Result Caches are Invalid - '||Resultcache ELSE ResultCache ||' ' END IF
FROM (
select count(*) resultcache
from V$RESULT_CACHE_OBJECTS
where status in ( 'Invalid', 'Expired') ) )
FROM DUAL UNION
SELECT 'FULL SCAN OPERATIONS : ' , (
SELECT CASE WHEN fullscan > 1 THEN '*** Warning *** FULLSCAN Operations Occuring '||fullscan ELSE fullscan ||' ' END IF
FROM (
select count(*) fullscan
from v$session_longops
where sofar <> totalwork and SQL_PLAN_OPTIONS in ( 'ALL', 'FULL','FAST FULL SCAN') and opname not like '%RMAN%' ) )
FROM DUAL UNION
select 'Shared Pool Fragmentation : ', case when free /total * 100 < 20 THEN '*** Warning *** Fragmented - Free Memory is LOW ***' || round ( (free/total) *100,2) ||'%' ELSE round(Used)||' out of ' || total END
FROM (
select SUm( decode(name ,'free memory',bytes,0))/(1048576) free, SUm( decode(name ,'free memory',0,bytes))/(1048576) used, round(sum(bytes)/(1048576)) total
from gv$sgastat
where pool='shared pool' ) UNION
SELECT 'BACKUP in Progress : ', case when backupcnt > 0 THEN '*** Warning *** '|| backupcnt || ' Sessions are running Backup ' ELSE backupcnt ||' ' END IF
FROM (
SELECT count(*) backupcnt
from gv$session_longops a, gv$session b
where sofar <> totalwork and a.inst_id = b.inst_id and a.sid = b.sid and b.status ='ACTIVE' and opname like '%RMAN%' and totalwork > 0 ) UNION
SELECT 'EXP/IMPDB in Progress : ', case when expimp > 0 or schedjobs > 0 THEN '*** Warning *** JOBS/EXPDP/IMPDP Running '|| ( expimp +schedjobs+gather) ELSE expimp ||'0' END IF
FROM (
select sum(decode( substr(module,1,9),'Data Pump',1,0) ) expimp, sum(decode( substr(module,1,9),'DBMS_SCHE',1,0) ) schedjobs, sum(decode( substr(opname,1,9),'Gather Sc',1,0) ) gather
from v$session_longops a , v$session b
where a.sid = b.sid and sofar <> totalwork and ( module like 'Data Pump%' or module like 'DBMS_SCHEDULER%' or opname like 'Gather Schema%' ) and b.status ='ACTIVE' ) UNION
SELECT 'Fixed/Dictionary STATS Age : ', case when ( sysdate - min(last_analyzed)) > 180 THEN '*** Warning *** Fixed,Dictionary Statistics are ' || round( sysdate - min(last_analyzed))||' Old' ELSE round( sysdate - min (last_analyzed) ) ||' ' END
from dba_tab_statistics
where table_name in ( 'FET$','X$VERSION') UNION
select 'SGA Mode- Size : ', case when ( phymem < 32 and sga < 8 ) or ( phymem > 32 and sga < (phymem * 0.20 ) ) then '*** Warning *** SGA Small - '||sga_mode_size ||' - '|| SGA ||'GB of ' || 'PhyMem: '||phymem when SGA > ( phymem * 0.60) then '*** Warning *** SGA allocated is very high ' ELSE sga_mode_size || '- '||SGA|| '- PhyMem: ' || phymem end Sgasetup
from (
select (
select round(sum(value )/1073741824)
from v$sga ) SGA, (
select round((value/1073741824))
from v$osstat
where stat_name = 'PHYSICAL_MEMORY_BYTES') Phymem, (
select decode( avg(value),0,'SGA_TARGET','MEMORY_TARGET - '||round( avg(value)/(1048576*1000))||'GB' ) SGA_MGMT
from (
select inst_id, name, value
from gv$parameter
where name in ('memory_target','memory_max_target') ) ) sga_mode_size
from dual ) union
SELECT 'Restore GRP or UNDO Gurantee : ', CASE when restore_point > 0 then '*** Restore Point Set ' || restore_point ELSE '0' END Restore_point
FROM (
SELECT (
SELECT count(*)
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES' ) + (
SELECT count(*)
FROM dba_tablespaces a
where retention ='GURANTEEE' and tablespace_name = (
select value
from v$parameter
where name ='undo_tablespace') ) restore_point
FROM DUAL ) UNION
Select 'Parameter Setup : ', CASE when param_count < 5 THEN '*** Warning - Best Practice Setup missing *** - Version: ' || Ver ELSE 'Y'||' - ' || Ver end param_setup
FROM (
SELECT substr( regexp_replace(version,'\.*'),1,4) ver, CASE WHEN substr( regexp_replace(version,'\.*'),1,4) =1120 then (
select count(*) param_count_1201
from v$parameter
where name in ( '_smu_debug_mode','_highthreshold_undoretention', '_memory_broker_stat_interval','_optimizer_use_feedback', '_datafile_write_errors_crash_instance', '_use_adaptive_log_file_sync','_gc_policy_time','_gc_policy_minimum') ) WHEN substr( regexp_replace(version,'\.*'),1,4) =1210 then (
select count(*) param_count_1201
from v$parameter
where name in ( '_optimizer_adaptive_plans','optimizer_adaptive_features', '_sql_plan_directive_mgmt_control','_optimizer_use_feedback', '_optimizer_dsdir_usage_control','_optimizer_ads_use_result_cache', '_drop_stat_segment') ) WHEN substr( regexp_replace(version,'\.*'),1,4) =1220 then (
select count(*) param_count_1220
from v$parameter
where name in ( '_optimizer_adaptive_plans','optimizer_adaptive_features', '_sql_plan_directive_mgmt_control','_optimizer_use_feedback', '_optimizer_dsdir_usage_control','_optimizer_ads_use_result_cache', '_drop_stat_segment') ) WHEN substr( regexp_replace(version,'\.*'),1,4) = 1900 then (
select count(*) param_count_1901
from v$parameter
where name in ( '_optimizer_adaptive_plans','optimizer_adaptive_features', '_sql_plan_directive_mgmt_control','_optimizer_use_feedback', '_optimizer_dsdir_usage_control','_optimizer_ads_use_result_cache', '_drop_stat_segment') ) else 0 end param_count
from v$instance ) UNION
select 'Standby Availability : ' , case when stdby_cnt > 0 THEN 'YES : ' ||stdby_cnt || ' ' || (
select listagg(process ||'-'||count(process) ,' ') within group ( order by process)
from v$archive_dest
where status ='VALID' and target='STANDBY' group by process ) || ' ' || (
select listagg(transmit_mode ||'-'||count(transmit_mode)||' ',' ') within group ( order by transmit_mode)
from v$archive_dest
where status ='VALID' and target='STANDBY' group by transmit_mode ) || ' ' || (
select listagg(decode(affirm,'NO','NOAFFRIM','YES','AFFIRM' ) ||'-'||count(affirm) ,' ') within group ( order by affirm)
from v$archive_dest
where status ='VALID' and target='STANDBY' group by affirm ) ELSE 'NO' END standby_status
from (
select count(*) stdby_cnt
from v$archive_dest
where status ='VALID' and target ='STANDBY' ) UNION
SELECT 'Resource Manager : ', case when length(nvl(value,0)) > 1 and not exists
(select 1
from DBA_FEATURE_USAGE_STATISTICS
where name ='Exadata' and currently_used='TRUE') then '*** Warning '||substr(value,1,20) ||' is set - DONOT SET' else nvl(value,'NONE') end
from V$PARAMETER
WHERE name ='resource_manager_plan' UNION
SELECT 'Invalid Parameters : ', case when dbwr_io_slaves > 0 or backup_tape_io_slaves ='true' THEN '*** dbwr_io_slaves and backup_tape_io_slaves should be disabled ***' ELSE 'NONE' END IF
FROM (
select max(decode(name,'dbwr_io_slaves',value)) dbwr_io_slaves, max(decode(name,'backup_tape_io_slaves',value)) backup_tape_io_slaves
from V$PARAMETER
WHERE name in ( 'dbwr_io_slaves','backup_tape_io_slaves') ) UNION
select 'DB Features : ', 'X'||listagg(feature,',') within group (order by feature )
from (
select decode(name,'Real Application Clusters (RAC)','RAC','Oracle Java Virtual Machine (user)','JVM','In-Memory Column Store','In-Memory', name) Feature
from DBA_FEATURE_USAGE_STATISTICS
where NAME in ( 'GoldenGate', 'In-Memory Column Store', 'JSON', 'Data Guard', 'Real Application Clusters (RAC)','Exadata','Oracle Java Virtual Machine (user)') and CURRENTLY_USED ='TRUE' and version = (
select version
from v$instance) and dbid = (
select dbid
from v$database) ) UNION
select 'DB Information : ' , '@'||sys_context('USERENV','DB_NAME') || '-'||substr(version,1,4) ||'-'|| substr(host_name,1,16) ||'-'|| (
select substr(platform_name,1,5)
from v$database) || ' CPUs:' || (
select value
from v$osstat
where stat_name ='NUM_CPUS') ||
',Mem:'||(select round(value/1048576/1024) ||'GB'
from v$osstat
where stat_name='PHYSICAL_MEMORY_BYTES')||',' ||
(select decode(open_mode,'READ WRITE','OPEN RW','READ ONLY WITH APPLY','READ ONLY-ADG',open_mode) ||'-'|| to_char(sysdate,'HH24:MI:SS')
from v$database )
from v$instance UNION
select 'Parallel Setup : ', 'Max Servers: '||max_server ||', Degree Limit: '|| degree_limit ||', Message Size: '|| mesg_size
from (
select max(decode(name,'parallel_max_servers',value)) max_server , max(decode(name,'parallel_degree_limit',value)) degree_limit , max(decode(name,'parallel_execution_message_size',value)) Mesg_size
from v$parameter
where name in ( 'parallel_max_servers','parallel_degree_limit','parallel_execution_message_size') ) )
---Add New here
) order by perf_order desc