set feedback off;
set pagesize 0;
Select (SELECT NAME FROM V$DATABASE) ||'_Oracle_Database_Health_Report: '||to_char(sysdate,'Mon dd yyyy hh24:mi:ss') "TIMESTAMP" from dual;
prompt**=====================================================================================================**
prompt** **Database Current Status**
prompt**=====================================================================================================**
set pagesize 50;
set line 300;
col HOST_NAME FORMAT a12;
col "HOST_ADDRESS" FORMAT a15;
col RESETLOGS_TIME FORMAT a12;
col "DB RAC?" FORMAT A8;
col days format 9999;
select name INSTANCE,HOST_NAME, UTL_INADDR.GET_HOST_ADDRESS "HOST_ADDRESS",
LOGINS, archiver,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB_UP_TIME", RESETLOGS_TIME "RESET_TIME", FLOOR(sysdate-startup_time) days,
(select DECODE(vp1.value,'TRUE','Yes ('|| decode(vp1.value,'TRUE',' instances: '||vp2.value)||')','No')
from v$instance,
(select value from v$parameter
where name like 'cluster_database'
) vp1,
(select value from v$parameter
where name like 'cluster_database_instances'
) vp2) "DB RAC?"
from v$database,gv$instance;
SELECT BANNER "VERSION" FROM V$VERSION;
col "Database Details" format a60;
SELECT
1 sort1,' DBNAME:'||name ||chr(9)||'DBID:'||dbid ||chr(9)|| 'Created:'||to_char(created, 'dd/mm/yyyy hh24:mi:ss') ||chr(10)||
' Log mode:'||log_mode || decode (log_mode,'ARCHIVELOG','',' !!!') ||chr(9)||'Forcelogging:'||force_logging||chr(9)||'Open mode:'||open_mode||chr(10)||
' Remote archiving:'||remote_archive||chr(9)||'Database role:'||database_role "Database Details"
FROM v$database
UNION
SELECT 2 sort1,'Datafiles: '||trim(TO_CHAR(COUNT(*),'9,990'))||chr(9)||'Datafile size(Gb): '||trim(TO_CHAR(SUM(bytes)/1073741824, '9,990'))
FROM v$datafile
UNION
SELECT 3 sort1,'Tempfiles: '||trim(TO_CHAR(COUNT(*),'9,990'))||chr(9)||'Tempfile size(Gb): '||trim(TO_CHAR(SUM(bytes)/1073741824, '9,990'))
FROM v$tempfile
UNION
SELECT 4 sort1,'Segment size (Gb): '||trim(TO_CHAR(SUM(bytes)/1073741824, '9,990'))
FROM dba_segments
UNION
SELECT 5 sort1,'Tables/Indexes: '|| trim(TO_CHAR(SUM(DECODE(type#, 2, 1, 0)), '999,990'))||'/'|| trim(TO_CHAR(SUM(DECODE(type#, 1, 1, 0)), '999,990'))
FROM sys.obj$
WHERE owner# <> 0
UNION
SELECT 6 sort1,'Total DB Users: '||trim( TO_CHAR(COUNT(*), '9,990'))
FROM sys.user$ WHERE
type# = 1
UNION
SELECT 7 sort1,'Online Sessions: '||trim( TO_CHAR(COUNT(*), '9,990'))
FROM gv$session
WHERE type='USER'
UNION
SELECT 8 sort1,'Active Sessions: '||trim( TO_CHAR(COUNT(*), '9,990'))
FROM gv$session
WHERE type='USER' and status = 'ACTIVE'
UNION
SELECT 9 sort1,'Session highwater: '|| trim(TO_CHAR(sessions_highwater, '9,990'))
FROM v$license
UNION
SELECT 10 sort1,'SGA (Mb): '||trim(TO_CHAR(SUM(value)/1048576, '99,990.99'))
FROM v$sga;
prompt**====================================================================================================**
prompt** **Database Parameters Details**
prompt**====================================================================================================**
set pagesize 600;
set line 200;
column "database parameter" format a40;
column "VALUE" format a40;
select name "Database Parameter",value from v$spparameter
where isspecified='TRUE' and NAME != 'control_files';
SET FEEDBACK OFF;
set pagesize 600;
set line 200;
column "NLS_Parameter" format a40;
column "VALUE" format a40;
Select parameter "NLS_Parameter", value from nls_database_parameters;
prompt
prompt
prompt**===================================================================================================**
prompt** **Tunning Database SGA/PGA**
prompt**===================================================================================================**
prompt
set pagesize 0;
SELECT 'SGA MAX Size in MB: '|| trunc(SUM(VALUE)/1024/1024, 2) "SGA_MAX_MB" FROM V$SGA;
set pagesize 50;
set line 200;
column "SGA Pool"format a33;
col "m_bytes" format 999999.99;
select pool "SGA Pool", m_bytes from ( select pool, to_char( trunc(sum(bytes)/1024/1024,2), '99999.99' ) as M_bytes
from v$sgastat
where pool is not null group by pool
union
select name as pool, to_char( trunc(bytes/1024/1024,3), '99999.99' ) as M_bytes
from v$sgastat
where pool is null order by 2 desc
) UNION ALL
select 'TOTAL' as pool, to_char( trunc(sum(bytes)/1024/1024,3), '99999.99' ) from v$sgastat;
Select round(tot.bytes /1024/1024 ,2) sga_total, round(used.bytes /1024/1024 ,2) used_mb, round(free.bytes /1024/1024 ,2) free_mb
from (select sum(bytes) bytes from v$sgastat where name != 'free memory') used,
(select sum(bytes) bytes from v$sgastat where name = 'free memory') free,
(select sum(bytes) bytes from v$sgastat) tot;
select pool, round(sgasize/1024/1024,2) "SGA_TARGET",
round(bytes/1024/1024,2) "FREE_MB",
round(bytes/sgasize*100, 2) "%FREE"
from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
where f.name = 'free memory';
prompt
prompt
prompt Tunning Shared Pool Size:
prompt*-----------------------------------------------------------------------**
col "Data Dict. Gets" heading Data_Dict.|Gets;
col "Data Dict. Cache Misses" heading Dict._Cache|Misses;
col "Data Dict Cache Hit Ratio" heading Dict._Cache|Hit_Ratio;
col "% Missed" heading Missed|%;
SELECT SUM(gets) "Data Dict. Gets", SUM(getmisses) "Data Dict. Cache Misses"
, TRUNC((1-(sum(getmisses)/SUM(gets)))*100, 2) "Data Dict Cache Hit Ratio"
, TRUNC(SUM(getmisses)*100/SUM(gets), 2) "% Missed"
FROM v$rowcache;
prompt
Prompt* The Dict. Cache Hit% shuold be > 90% and misses% should be < 15%. If not consider increase SHARED_POOL_SIZE.
col "Cache Misses" heading Cache|Misses;
col "Library Cache Hit Ratio" heading Lib._Cache|Hit_Ratio;
col "% Missed" heading Missed|%;
SELECT SUM(pins) "Executions", SUM(reloads) "Cache Misses"
, TRUNC((1-(SUM(reloads)/SUM(pins)))*100, 2) "Library Cache Hit Ratio"
, ROUND(SUM(reloads)*100/SUM(pins)) "% Missed"
FROM v$librarycache;
prompt
Prompt* The Lib. Cache Hit% shuold be > 90% and misses% should be < 1%. If not consider increase SHARED_POOL_SIZE.
set pagesize 25;
col "Tot SQL since startup" format a25;
col "SQL executing now" format a17;
SELECT TO_CHAR(SUM(executions)) "Tot SQL since startup", TO_CHAR(SUM(users_executing)) "SQL executing now"
FROM v$sqlarea;
prompt
set pagesize 0;
select 'Cursor_Space_for_Time: '|| value "Cursor_Space_for_Time"
from v$parameter where name = 'cursor_space_for_time';
set pagesize 25;
col "Namespace" heading name|space;
col "Hit Ratio" heading Hit|Ratio;
col "Pin Hit Ratio" heading Pin_Hit|Ratio;
col "Invalidations" heading invali|dations;
SELECT namespace "Namespace", TRUNC(gethitratio*100) "Hit Ratio",
TRUNC(pinhitratio*100) "Pin Hit Ratio", reloads "Reloads", invalidations "Invalidations"
FROM v$librarycache;
prompt
prompt* GETHITRATIO and PINHITRATIO should be more than 90%.
prompt* If RELOADS > 0 then'cursor_space_for_time' Parameter do not set to 'TRUE'
prompt* More of Invalid object in namespace will cause more reloads.
set line 200;
col "NAME" format a30;
col "VALUE" format a12;
select p.name "NAME", a.free_space, p.value "VALUE", trunc(a.free_space/p.value, 2) "FREE%", requests, request_misses req_misses
from v$parameter p, v$shared_pool_reserved a
where p.name = 'shared_pool_reserved_size';
prompt
Prompt* %FREE should be > 0.5, request_failures,request_misses=0 or near 0. If not consider increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE.
prompt
prompt
prompt Tunning Buffer Cache:
prompt -----------------------------------------------------------------------**
SELECT TRUNC( ( 1 - ( SUM(decode(name,'physical reads',value,0)) / ( SUM(DECODE(name,'db block gets',value,0))
+ (SUM(DECODE(name,'consistent gets',value,0))) )) ) * 100 ) "Buffer Hit Ratio"
FROM v$sysstat;
prompt
prompt* The Buffer Cache Hit% should be >90%. If not and the shared pool hit ratio is good consider increase DB_CACHE_SIZE.
set line 200;
col event format a20;
select event, total_waits, time_waited
from v$system_event
where event in ('buffer busy waits');
select s.segment_name, s.segment_type, s.freelists, w.wait_time, w.seconds_in_wait, w.state
from dba_segments s, v$session_wait w
where w.event = 'buffer busy waits'
AND w.p1 = s.header_file AND w.p2 = s.header_block;
prompt
prompt* Check for waits to find a free buffer in the buffer cache and Check if the I/O system is slow.
prompt* Consider increase the size of the buffer cache if it is too small. Consider increase the number of DBWR process if the buffer cache is properly sized.
prompt
prompt
prompt Tunning Redolog Buffer:
prompt -----------------------------------------------------------------------**
col "redolog space request" heading redolog_space|request;
col "redolog space wait time" heading redolog_space|wait_time;
col "Redolog space ratio" heading redolog_space|ratio;
Select e. value "redolog space request", s.value "redolog space wait time", Round(e.value/s.value,2) "Redolog space ratio"
From v$sysstat s, v$sysstat e
Where s.name = 'redo log space requests'
and e.name = 'redo entries';
prompt
prompt * If the ratio of redolog space is less than 5000 then increase the size of redolog buffer until this ratio stop falling.
prompt * There should be no log buffer space waits. Consider making logfile bigger or move the logfile to faster disk.
col "redo_buff_alloc_retries" heading redo_buffer|alloc_retries;
col "redo_entries" heading redo|entries;
col "pct_buff_alloc_retries" heading pct_buffer|alloc_retries;
select v1.value as "redo_buff_alloc_retries", v2.value as "redo_entries",
trunc(v1.value/v2.value,4) as "pct_buff_alloc_retries"
from v$sysstat v1, v$sysstat v2
where v1.name = 'redo buffer allocation retries'
and v2.name = 'redo entries';
column latch_name format a20
select name latch_name, gets, misses, immediate_gets "Immed Gets", immediate_misses "Immed Misses", trunc((misses/decode(gets,0,1,gets))*100,2) Ratio1,
trunc(immediate_misses/decode(immediate_misses+ immediate_gets,0,1, immediate_misses+immediate_gets)*100,2) Ratio2
from v$latch
where name like 'redo%';
prompt
prompt All ratios should be <= 1% if not then decrease the value of log_small_entry_max_size in init.ora
col event format a30;
select * from v$system_event
where event like 'log%';
prompt
Prompt* If Avg_wait_time is minor ignore it otherwise check the log buffer size w.r.t transaction rate and memory size.
prompt
prompt
prompt Tunning PGA Aggregate Target:
prompt -----------------------------------------------------------------------**
set pagesize 600;
set line 200;
column PGA_Component format a40;
column value format 999999999999;
select name "PGA_Component", value from v$pgastat;
Select count(*) "Total No. of Process" from v$process;
set line 200;
column "PGA Target" format a40;
column VALUE_MB format 9999999999.99
SELECT NAME "PGA Target", VALUE/1024/1024 VALUE_MB
FROM V$PGASTAT
WHERE NAME IN ('aggregate PGA target parameter',
'total PGA allocated',
'total PGA inuse')
union
SELECT NAME, VALUE
FROM V$PGASTAT
WHERE NAME IN ('over allocation count');
set line 200;
column "PGA_Work_Pass" format a40;
column "PER" format 999;
select name "PGA_Work_Pass", cnt, decode(total, 0, 0, round(cnt*100/total)) per
from (select name, value cnt, (sum(value) over()) total
from v$sysstat where name like 'workarea exec%'
);
prompt
Prompt* DBA Must increase PGA_AGG_TARGET when "Multipass" > 0 and Reduce when "Optimal" executions 100%.
prompt
prompt Tunning SORT Area Size:
prompt -----------------------------------------------------------------------**
col name format a20;
select name, value from v$sysstat
where name like 'sorts%';
prompt
prompt
prompt**===================================================================================================**
prompt** **Tablespace/CRD File Information**
prompt**===================================================================================================**
col "Database Size" format a15;
col "Free space" format a15;
select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",
round(free.p / 1024 / 1024/1024) || ' GB' "Free space"
from (select bytes from v$datafile
union all select bytes from v$tempfile
union all select bytes from v$log) used,
(select sum(bytes) as p from dba_free_space) free
group by free.p;
SELECT a.tablespace_name tablespace_name,
ROUND(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
-- ROUND(NVL(b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
ROUND((a.bytes_alloc - NVL(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used,
ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_Free,
(case when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)<=0
then 'Immediate action required!'
when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)<5
then 'Critical (<5% free)'
when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)<15
then 'Warning (<15% free)'
when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)<25
then 'Warning (<25% free)'
when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)>60
then 'Waste of space? (>60% free)'
else 'OK'
end) msg
FROM ( SELECT f.tablespace_name,
SUM(f.bytes) bytes_alloc,
SUM(DECODE(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
FROM DBA_DATA_FILES f
GROUP BY tablespace_name) a,
( SELECT f.tablespace_name,
SUM(f.bytes) bytes_free
FROM DBA_FREE_SPACE f
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name (+)
UNION
SELECT h.tablespace_name,
ROUND(SUM(h.bytes_free + h.bytes_used) / 1048576, 2),
-- ROUND(SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / 1048576, 2),
ROUND(SUM(NVL(p.bytes_used, 0))/ 1048576, 2),
ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2),
(case when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)<=0 then 'Immediate action required!'
when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)<5 then 'Critical (<5% free)'
when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)<15 then 'Warning (<15% free)'
when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)<25 then 'Warning (<25% free)'
when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)>60 then 'Waste of space? (>60% free)'
else 'OK'
end) msg
FROM sys.V_$TEMP_SPACE_HEADER h, sys.V_$TEMP_EXTENT_POOL p
WHERE p.file_id(+) = h.file_id
AND p.tablespace_name(+) = h.tablespace_name
GROUP BY h.tablespace_name
ORDER BY 1;
set linesize 200
col file_name format a50 heading "Datafile Name"
col allocated_mb format 999999.99;
col used_mob format 999999.99;
col free_mb format 999999.99;
col tablespace_name format a20;
SELECT SUBSTR (df.NAME, 1, 40) file_name, dfs. tablespace_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb, NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_mb, c.autoextensible
FROM v$datafile df, dba_free_space dfs, DBA_DATA_FILES c
WHERE df.file# = dfs.file_id(+) AND df.file# = c.FILE_ID
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes, dfs.tablespace_name, c.autoextensible
ORDER BY file_name;
SELECT TO_CHAR(creation_time, 'RRRR Month') "Year/Month",
round(SUM(bytes)/1024/1024/1024) "Datafile Growth Rate in GB"
FROM sys.v_$datafile
WHERE creation_time < sysdate
GROUP BY TO_CHAR(creation_time, 'RRRR Month');
TTI off
prompt
prompt** Report Tablespace < 10% free space**
prompt** -----------------------------------------------------------------------**
set pagesize 300;
set linesize 100;
column tablespace_name format a15 heading Tablespace;
column sumb format 999,999,999;
column extents format 9999;
column bytes format 999,999,999,999;
column largest format 999,999,999,999;
column Tot_Size format 999,999 Heading "Total Size(Mb)";
column Tot_Free format 999,999,999 heading "Total Free(Kb)";
column Pct_Free format 999.99 heading "% Free";
column Max_Free format 999,999,999 heading "Max Free(Kb)";
column Min_Add format 999,999,999 heading "Min space add (MB)";
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from (select tablespace_name,0 tots,sum(bytes) sumb
from sys.dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0 from
sys.dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;
col owner format a15;
SELECT owner, count(*), tablespace_name
FROM dba_segments
WHERE tablespace_name = 'SYSTEM' AND owner NOT IN ('SYS','SYSTEM','CSMIG','OUTLN')
Group by owner, tablespace_name;
set pagesize 0;
prompt
SELECT '+ '||count(*)||' NON-SYSTEM objects detected in SYSTEM tablespace with '||
'total size: '||NVL(round(sum(bytes)/1024/1024,2),0)||'MB' "NON-SYSTEM objects"
FROM dba_segments
WHERE tablespace_name = 'SYSTEM' AND owner not in ('SYS','SYSTEM','CSMIG','OUTLN');
set pagesize 50;
col name format A60 heading "Control Files";
select name from sys.v_$controlfile;
prompt
set pagesize 0;
select (case
when count(1) <2 then '+ At least 2 controlfiles are recommended'
when count(1) >=2 and count(1) <=3 then '+ '||count(1)||' mirrors for controlfile detected. - OK'
else '+ More than 3 controlfiles might have additional overhead. Check the wait events.'
end)
from v$controlfile;
set pagesize 0;
col msg format a79;
select
(case when value <45 then '+ ! "control_file_record_keep_time='||value||'" to low. Set to at least 45'
else '+ "control_file_record_keep_time='||value||'" - OK.'
end) msg
from v$parameter where name = 'control_file_record_keep_time';
set pagesize 50;
select segment_name, owner, tablespace_name, status from dba_rollback_segs;
prompt
set pagesize 0;
select 'The average of rollback segment waits/gets is '||
round((sum(waits) / sum(gets)) * 100,2)||'%'
From v$rollstat;
set pagesize 50;
SELECT TO_CHAR(SUM(value), '999,999,999,999,999') "Total Requests"
FROM v$sysstat
WHERE name IN ('db block gets','consistent gets');
set pagesize 50;
SELECT class "Class", count "Count"
FROM v$waitstat
WHERE class IN ( 'free list', 'system undo header', 'system undo block', 'undo header', 'undo block')
GROUP BY class, count;
prompt
prompt* If these are < 1% of Total Number of request for data then extra rollback segment are needed.
prompt
prompt Check Rollback Contention:
prompt -----------------------------------------------------------------------**
select class, count, time from v$waitstat
where class in ('data block', 'undo header', 'undo block', 'segment header');
prompt
prompt* If the contention is on 'data block' check for SQL statements using unselective indexes.
prompt* if the contention is on 'undo header' consider using automatic segment-space management or add more rollback segments.
prompt* if the contention is on 'undo block' consider using automatic segment-space management or make rollback segment sizes larger.
prompt* If the contention is on 'segment header' look for the segment and consider increase free-lists.
set pagesize 50;
set line 200;
col member format A40 heading "Redolog Files";
col group# format 99;
col archived format a3;
col status format a10;
col first_time format a12;
select a.group#, a.member, b.archived, b.status, b.first_time from v$logfile a, v$log b
where a.group# = b.group# order by a.group#;
prompt
set pagesize 0
select 'Group# '||group#||': '||
(case
when members<2 then '+ Redo log mirroring is recommended'
else ' '||members||' members detected. - OK'
end)
from v$log
where members < 2;
set pagesize 0;
select
(case
when count(*)>3 then '+ '||count(*)||' times detected when log switches occured more than 1 log per 5 minutes.'||chr(10)||
'+ You may consider to increase the redo log size.'
else '+ Redo log size: OK'
end) "Redolog Size Status"
from (
select trunc(FIRST_TIME,'HH') Week, count(*) arch_no, trunc(10*count(*)/60) archpermin
from v$log_history
group by trunc(FIRST_TIME,'HH')
having trunc(5*count(*)/60)>1
);
set pagesize 0;
select (case when sync_waits = 0 then '+ Waits for log file sync not detected: log_buffer is OK'
else '+ Waits for log file sync detected ('||sync_waits||' times): Consider to increase the log_buffer'
end) "Log Buffer Status"
from ( select decode( sum(w.total_waits), 0, 0,
nvl(100 * sum(l.total_waits) / sum(w.total_waits), 0)
) sync_waits
from sys.v_$bgprocess b, sys.v_$session s, sys.v_$session_event l, sys.v_$session_event w
where
b.name like 'DBW_' and s.paddr = b.paddr and
l.sid = s.sid and l.event = 'log file sync' and
w.sid = s.sid and w.event = 'db file parallel write'
);
prompt
prompt Last 24hrs Log switch Report:
prompt -----------------------------------------------------------------------**
set pagesize 0;
select '+ Daily (max) : '||max(no)||' switches. Size: '||round((max(no) * max(logsize )) ,2)||'MB'||chr(10)||
'+ Daily (avg) : '||trunc(avg(no))||' switches. Size: '||round((avg(no) * max(logsize )) ,2)||'MB'||chr(10)||
'+ Daily (min) : '||min(no)||' switches. Size: '||round((min(no) * max(logsize )) ,2)||'MB'
from (select trunc(FIRST_TIME,'DD'), count(*) no
from v$log_history
where FIRST_TIME > sysdate - 31
group by trunc(FIRST_TIME,'DD')),
(select max(bytes/1024/1024) logsize from v$log);
set pagesize 0;
select '+ Weekly (max): '||max(no)||' switches. Size: '||round((max(no) * max(logsize )) ,2)||'MB'||chr(10)||
'+ Weekly (avg): '||trunc(avg(no))||' switches. Size: '||round((avg(no) * max(logsize )) ,2)||'MB'||chr(10)||
'+ Weekly (min): '||min(no)||' switches. Size: '||round((min(no) * max(logsize )) ,2)||'MB'
from (select trunc(FIRST_TIME,'WW'), count(*) no
from v$log_history
where FIRST_TIME > sysdate - 31
group by trunc(FIRST_TIME,'WW')),
(select max(bytes/1024/1024) logsize from v$log);
set pagesize 25;
select trunc(completion_time) log_date, count(*) log_switch, round((sum(blocks*block_size) / 1024 / 1024)) "SIZE_MB"
from v$archived_log
WHERE completion_time > (sysdate-1) - 1/24 AND DEST_ID = 1
group by trunc(completion_time)
order by 1 desc;
set line 200;
col event format a30;
col "time_waited" heading time|waited;
col "average_wait" heading average|wait;
select event, time_waited "time_waited", average_wait "average_wait"
from v$system_event
where event like 'log file switch completion';
prompt
prompt * The pct_buff_retries should be 0 or (< 1%) . If it is greater consider moving the logfile to faster disk.
prompt * If there are log file switch waits, it indicates disk I/O contention. Check that redo log files are stored on separated and fast devices.
prompt
prompt
prompt**====================================================================================================**
prompt** **Database Users Activities**
prompt**====================================================================================================**
set pagesize 100;
set line 200;
col username format a20;
col profile format a10;
col default_ts# format a18;
col temp_ts# format a10;
col created format a12;
Select username, account_status status, TO_CHAR(created, 'dd-MON-yyyy') created, profile,
default_tablespace default_ts#, temporary_tablespace temp_ts# from dba_users
where default_tablespace in ('SDH_HRMS_DBF', 'SDH_TIMS_DBF', 'SDH_SHTR_DBF', 'SDH_EDSS_DBF', 'SDH_FIN_DBF', 'SDH_FIN_DBF', 'USERS');
select obj.owner "USERNAME", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "Size_MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj,
(select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc,2 desc, 1;
col status format a20 heading "Session status";
col TOTAL format 9999999999 heading "# sessions";
select status, count(*) TOTAL from gv$session
where type='USER'
group by inst_id,status
order by 1,2;
set line 200;
col LOGON_TIME format a10;
col sid format 99;
col status format a8;
col process format a12;
col SCHEMANAME format a12;
col OSUSER format a15;
col machine format a25;
col SQL_TEXT format a75;
SELECT S.LOGON_TIME, s.sid, s.process, s.schemaname, s.osuser, s.MACHINE, a.sql_text
FROM v$session s, v$sqlarea a, v$process p
WHERE s.SQL_HASH_VALUE = a.HASH_VALUE
AND s.SQL_ADDRESS = a.ADDRESS
AND s.PADDR = p.ADDR
AND S.STATUS = 'INACTIVE';
prompt
prompt
prompt**==================================================================================================**
prompt** **Database Object Information**
prompt**==================================================================================================**
prompt
prompt List of Largest Object in Database:
prompt -----------------------------------------------------------------------**
set line 200;
col SEGMENT_NAME format a30;
col SEGMENT_TYPE format a10;
col BYTES format a15;
col TABLESPACE_NAME FORMAT A25;
SELECT * FROM (select SEGMENT_NAME, SEGMENT_TYPE TYPE, BYTES/1024/1024 SIZE_MB,
TABLESPACE_NAME from dba_segments order by 3 desc ) WHERE ROWNUM <= 5;
prompt
prompt Object Modified in last 7 days:
prompt -----------------------------------------------------------------------**
set line 200;
col owner format a15;
col object_name format a25;
col object_type format a15;
col last_modified format a20;
col created format a20;
col status format a10;
select owner, object_name, object_type, to_char(LAST_DDL_TIME,'MM/DD/YYYY HH24:MI:SS') last_modified,
to_char(CREATED,'MM/DD/YYYY HH24:MI:SS') created, status
from dba_objects
where (SYSDATE - LAST_DDL_TIME) < 7 and owner IN( 'HRMS', 'ORAFIN', 'HRTRAIN')
order by last_ddl_time DESC;
prompt
set pagesize 0;
SELECT 'Object Created in this Week: '|| count(1) from user_objects
where created >= sysdate -7;
prompt
prompt List of Invalid objects of database:
prompt -----------------------------------------------------------------------**
set pagesize 50;
Select owner "USERNAME", object_type, count(*) INVALID from dba_objects
where status='INVALID' group by owner, object_type;
set pagesize 50;
SELECT dt.owner, dt.table_name "Table Change > 10%",
ROUND ( (DELETES + UPDATES + INSERTS) / num_rows * 100) PERCENTAGE
FROM dba_tables dt, all_tab_modifications atm
WHERE dt.owner = atm.table_owner
AND dt.table_name = atm.table_name
AND num_rows > 0
AND ROUND ( (DELETES + UPDATES + INSERTS) / num_rows * 100) >= 10
ORDER BY 3 desc;
prompt
prompt Database Chained Rows Info:
prompt -----------------------------------------------------------------------**
col table_name format a25;
select owner, table_name, pct_free, pct_used, avg_row_len, num_rows, chain_cnt, trunc(chain_cnt/num_rows*100, 2) as perc
from dba_tables where owner not in ('SYS','SYSTEM')
and table_name not in (select table_name from dba_tab_columns
where data_type in ('RAW','LONG RAW') )
and chain_cnt > 0 order by chain_cnt desc;
prompt
prompt
prompt**==================================================================================================**
prompt** **RMAN Configuration and Backup**
prompt**==================================================================================================**
col "RMAN CONFIGURE PARAMETERS" format a100;
select 'CONFIGURE '||name ||' '|| value "RMAN CONFIGURE PARAMETERS"
from v$rman_configuration
order by conf#;
set line 200;
col "DEVIC" format a6;
col "L" format 9;
col "FIN:SS" format 9999;
SELECT DECODE(backup_type, 'L', 'Archived Logs', 'D', 'Datafile Full', 'I', 'Incremental')
backup_type, bp.tag "RMAN_BACKUP_TAG", device_type "DEVIC", DECODE( bs.controlfile_included, 'NO', null, bs.controlfile_included) controlfile,
(sp.spfile_included) spfile, sum(bs.incremental_level) "L", TO_CHAR(bs.start_time, 'dd/mm/yyyy HH24:MI:SS') start_time
, TO_CHAR(bs.completion_time, 'dd/mm/yyyy HH24:MI:SS') completion_time, sum(bs.elapsed_seconds) "FIN:SS"
FROM v$backup_set bs, (select distinct set_stamp, set_count, tag , device_type
from v$backup_piece
where status in ('A', 'X')) bp,
(select distinct set_stamp , set_count , 'YES' spfile_included
from v$backup_spfile) sp
WHERE bs.start_time > sysdate - 1
AND bs.set_stamp = bp.set_stamp
AND bs.set_count = bp.set_count
AND bs.set_stamp = sp.set_stamp (+)
AND bs.set_count = sp.set_count (+)
group by backup_type, bp.tag, device_type, bs.controlfile_included, pieces, sp.spfile_included,start_time, bs.completion_time
ORDER BY bs.start_time desc;
set line 200;
col "DBF_BACKUP_MB" format 999999.99;
col "ARC_BACKUP_MB" format 9999.99;
select trunc(completion_time) "BAK_DATE", sum(blocks*block_size)/1024/1024 "DBF_BACKUP_MB", (SELECT sum(blocks*block_size)/1024/1024 from v$backup_redolog
WHERE first_time > sysdate-1) "ARC_BACKUP_MB"
from v$backup_datafile
WHERE completion_time > sysdate - 1
group by trunc(completion_time)
order by 1 DESC;
col "Datafiles backed up within 24h" format a40;
col "Control backed up" format 999;
col "SPFiles backed up" format 999;
SELECT dbfiles||' out of '||numfiles||' datafiles backed up' "Datafiles backed up within 24h", cfiles "CFiles", spfiles "SPFiles"
FROM (select count(*) numfiles from v$datafile), (select count(*) dbfiles from v$backup_datafile a, v$datafile b
where a.file# = b.file# and a.completion_time > sysdate - 1), (select count(*) cfiles from v$backup_datafile
where file# = 0 and completion_time > sysdate - 1), (select count(*) spfiles from v$backup_spfile where completion_time > sysdate - 1);
prompt
prompt
prompt**===================================================================================================**
prompt** "Workload and I/O Statistics**
prompt**===================================================================================================**
prompt
prompt *** TOP SYSTEM Timed Events (Waits):
prompt*-----------------------------------------------------------------------**
COLUMN event FORMAT A40 HEADING "Wait Event" TRUNC
COLUMN time_waited FORMAT 9999999999999 HEADING "Time|Waited"
COLUMN wait_pct FORMAT 99.90 HEADING "Wait|(%)"
SELECT w.event, w.time_waited, round(w.time_waited/tw.twt*100,2) wait_pct
FROM gv$system_event w, (select inst_id, sum(time_waited) twt from gv$system_event
where time_waited>0
AND event NOT IN ('Null event', 'client message', 'rdbms ipc reply', 'smon timer', 'rdbms ipc message', 'PX Idle Wait', 'PL/SQL lock timer', 'file open', 'pmon timer', 'WMON goes to sleep',
'virtual circuit status', 'dispatcher timer', 'SQL*Net message from client', 'parallel query dequeue wait', 'pipe get')
group by inst_id
) tw
WHERE w.inst_id = tw.inst_id and w.time_waited>0
and round(w.time_waited/tw.twt*100,2) > 1
and w.event NOT IN ('Null event', 'client message', 'rdbms ipc reply', 'smon timer', 'rdbms ipc message', 'PX Idle Wait', 'PL/SQL lock timer', 'file open', 'pmon timer', 'WMON goes to sleep',
'virtual circuit status', 'dispatcher timer', 'SQL*Net message from client', 'parallel query dequeue wait', 'pipe get')
ORDER by 1;
prompt
prompt *** Most buffer gets (TOP 5):
prompt*-----------------------------------------------------------------------**
col object_type format a10;
col object_name format a30;
col statistic_name format a15;
col value format 99999999999;
SELECT * from (
SELECT object_type, object_name, statistic_name,VALUE
FROM v$segment_statistics
WHERE statistic_name LIKE '%logi%' AND VALUE > 50
ORDER BY 4 DESC
) where rownum < 6;
prompt
prompt *** Most I/O operation (TOP 5):
prompt*-----------------------------------------------------------------------**
col object_type format a15 heading "Object Type"
col object_name format a27 heading "Object Name"
col statistic_name format a22 heading "Statistic Name"
col value format 99999999999 heading "Value"
SELECT * from (
SELECT object_type, object_name, statistic_name, VALUE
FROM v$segment_statistics
WHERE statistic_name LIKE '%phys%' AND VALUE > 50
ORDER BY 4 DESC
) where rownum < 6;
prompt
prompt *** Most I/O operation for particualr Query:
prompt*-----------------------------------------------------------------------**
col sql_text format a60;
col reads_per_exe format 99999999 heading 'reads|per_exe';
col "exe" format 99999;
col "sorts" format 99999;
col buffer_gets heading 'buffer|gets';
col disk_reads heading 'disk|reads';
SELECT * FROM (SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_exe,
a.buffer_gets, a.disk_reads, a.executions "exe", a.sorts "sorts", a.address "address"
FROM v$sqlarea a
ORDER BY 2 DESC)
WHERE rownum <= 5;
prompt
prompt Monitoring Full Table Scan of Database:
prompt*-----------------------------------------------------------------------**
set line 200;
col "Full Table Scan" format a30;
SELECT name "Full Table Scan", value FROM v$sysstat
WHERE name LIKE '%table scans %'
ORDER BY name;
prompt
Prompt* Review the query causing high amount of buffer_gets and create additional index to avoid full table scan.
prompt
prompt Monitor TOP CPU Usage and Logical I/O Process:
prompt*-----------------------------------------------------------------------**
col resource_name heading "Resource|Name";
col current_utilization heading "current|utiliz";
col max_utilization heading "Max|utiliz";
col initial_allocation heading "Initial|Alloc";
col limit_value heading "Limit|Value";
select resource_name, current_utilization, max_utilization, initial_allocation, limit_value
from v$resource_limit where resource_name in ('processes','sessions', 'transactions', 'max_rollback_segments');
col name format a30;
select * from (select a.sid, c.username, c.osuser, c.machine, logon_time, b.name, a.value
from v$sesstat a, v$statname b, v$session c
where a.STATISTIC# = b.STATISTIC#
and a.sid = c.sid
and b.name like '%CPU used by this session%'
order by a.value desc)
where rownum < 5;
select 'top logical i/o process', sid, username, total_user_io amt_used, round(100 * total_user_io/total_io,2) pct_used
from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_io
from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid
and c.name in ('consistent gets', 'db block gets') and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by b.sid, nvl(b.username, p.name) order by 3 desc),
(select sum(value) total_io from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid and c.name in ('consistent gets', 'db block gets'))
where rownum < 2
union all
select 'top memory process', sid, username, total_user_mem, round(100 * total_user_mem/total_mem,2)
from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_mem
from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr
and b.sid = a.sid and c.name in ('session pga memory', 'session uga memory')
and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by b.sid, nvl(b.username, p.name) order by 3 desc),
(select sum(value) total_mem from v$statname c, v$sesstat a
where a.statistic# = c.statistic# and c.name in ('session pga memory', 'session uga memory'))
where rownum < 2
union all
select 'top cpu process', sid, username, total_user_cpu, round(100 * total_user_cpu/greatest(total_cpu,1),2)
from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_cpu
from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid
and c.name = 'CPU used by this session' and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by b.sid, nvl(b.username, p.name) order by 3 desc),
(select sum(value) total_cpu from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid
and c.name = 'CPU used by this session') where rownum < 2;
prompt
prompt Monitoring Current Running Long Job in Database:
prompt*-----------------------------------------------------------------------**
set line 200;
col opname format a30;
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM V$SESSION_LONGOPS
WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK
order by 1;
prompt
prompt Monitoring Object locking:
prompt*-----------------------------------------------------------------------**
set line 200;
col username format a15;
col lock_type format a10;
col osuser format a15;
col owner format a10;
col object_name format a20;
SELECT s.sid, s. serial#, s.username, l.lock_type, s.osuser, s.machine,
o.owner, o.object_name, ROUND(w.seconds_in_wait/60, 2) "Wait"
FROM v$session s, dba_locks l, dba_objects o, v$session_wait w
WHERE s.sid = l.session_id
AND l.lock_type IN ('DML','DDL')AND l.lock_id1 = o.object_id
AND l.session_id = w.sid
ORDER BY s.sid;
prompt
prompt Monitor DB Corruption or Need of Recovery:
prompt*-----------------------------------------------------------------------**
set line 200;
SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name, d.STATUS,
r.ERROR, r.CHANGE#, r.TIME FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
WHERE t.TS# = d.TS# AND d.FILE# = r.FILE#;
set linesize 200
col name format a45 heading "Datafile Name";
col "Read Time(ms)" heading 'Read|Time(ms)';
col "Write Time(ms)" heading 'write|Time(ms)';
col "Avg_Time" heading 'Avg|Time(ms)';
select name,PHYRDS,PHYWRTS,READTIM "Read Time(ms)",WRITETIM "Write Time(ms)",AVGIOTIM "Avg_Time"
from v$filestat, v$datafile where v$filestat.file#=v$datafile.file#;
set feedback on
prompt
rem -----------------------------------------------------------------------
rem Filename: DB_Health_Rep.sql
rem Purpose: Database Statistics and Health Report
rem -----------------------------------------------------------------------
prompt Recommendations:
prompt ====================================================================================================
prompt* SQL Cache Hit rate ratio should be above 90%, if not then increase the Shared Pool Size.
prompt* Dict Cache Hit rate ratio should be above 85%, if not then increase the Shared Pool Size.
prompt* Buffer Cache Hit rate ratio should be above 90%, if not then increase the DB Block Buffer value.
prompt* Redo Log space requests should be less than 0.5% of redo entries, if not then increase log buffer.
prompt* Redo Log space wait time should be near to 0.
prompt
set serveroutput ON
DECLARE
libcac number(10,2);
rowcac number(10,2);
bufcac number(10,2);
redlog number(10,2);
redoent number;
redowaittime number;
BEGIN
select value into redlog from v$sysstat where name = 'redo log space requests';
select value into redoent from v$sysstat where name = 'redo entries';
select value into redowaittime from v$sysstat where name = 'redo log space wait time';
select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;
select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache;
select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac
from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
where cur.statistic# = ncu.statistic#
and ncu.name = 'db block gets'
and con.statistic# = nco.statistic#
and nco.name = 'consistent gets'
and phys.statistic# = nph.statistic#
and nph.name = 'physical reads';
dbms_output.put_line('CACHE HIT RATE');
dbms_output.put_line('********************');
dbms_output.put_line('SQL Cache Hit rate = '||libcac);
dbms_output.put_line('Dict Cache Hit rate = '||rowcac);
dbms_output.put_line('Buffer Cache Hit rate = '||bufcac);
dbms_output.put_line('Redo Log space requests = '||redlog);
dbms_output.put_line('Redo Entries = '||redoent);
dbms_output.put_line('Redo log space wait time = '||redowaittime);
if
libcac < 90 then dbms_output.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
END IF;
if
rowcac < 85 then dbms_output.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
END IF;
if
bufcac < 90 then dbms_output.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');
END IF;
if
redlog > 1000000 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!');
END IF;
END;
/
spool off
exit
Output like:
set feedback off;
set pagesize 0;
Select (SELECT NAME FROM V$DATABASE) ||'_Oracle_Database_Health_Report: '||to_char(sysdate,'Mon dd yyyy hh24:mi:ss') "TIMESTAMP" from dual;
prompt**=====================================================================================================**
prompt** **Database Current Status**
prompt**=====================================================================================================**
set pagesize 50;
set line 300;
col HOST_NAME FORMAT a12;
col "HOST_ADDRESS" FORMAT a15;
col RESETLOGS_TIME FORMAT a12;
col "DB RAC?" FORMAT A8;
col days format 9999;
select name INSTANCE,HOST_NAME, UTL_INADDR.GET_HOST_ADDRESS "HOST_ADDRESS",
LOGINS, archiver,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB_UP_TIME", RESETLOGS_TIME "RESET_TIME", FLOOR(sysdate-startup_time) days,
(select DECODE(vp1.value,'TRUE','Yes ('|| decode(vp1.value,'TRUE',' instances: '||vp2.value)||')','No')
from v$instance,
(select value from v$parameter
where name like 'cluster_database'
) vp1,
(select value from v$parameter
where name like 'cluster_database_instances'
) vp2) "DB RAC?"
from v$database,gv$instance;
INSTANCE HOST_NAME HOST_ADDRESS LOGINS ARCHIVE DB_UP_TIME RESET_TIM DAYS DB RAC?
--------- ------------ --------------- ---------- ------- -------------------- --------- ----- -------------------------------------------------------
PROD DEKHATAI009 fe80::6c9d:91df ALLOWED STARTED 26-FEB-2016 09:53:17 02-FEB-16 0 No
SQL> SELECT BANNER "VERSION" FROM V$VERSION;
VERSION
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
col "Database Details" format a60;
SELECT
1 sort1,' DBNAME:'||name ||chr(9)||'DBID:'||dbid ||chr(9)|| 'Created:'||to_char(created, 'dd/mm/yyyy hh24:mi:ss') ||chr(10)||
' Log mode:'||log_mode || decode (log_mode,'ARCHIVELOG','',' !!!') ||chr(9)||'Forcelogging:'||force_logging||chr(9)||'Open mode:'||open_mode||chr(10)||
' Remote archiving:'||remote_archive||chr(9)||'Database role:'||database_role "Database Details"
FROM v$database
UNION
SELECT 2 sort1,'Datafiles: '||trim(TO_CHAR(COUNT(*),'9,990'))||chr(9)||'Datafile size(Gb): '||trim(TO_CHAR(SUM(bytes)/1073741824, '9,990'))
FROM v$datafile
UNION
SELECT 3 sort1,'Tempfiles: '||trim(TO_CHAR(COUNT(*),'9,990'))||chr(9)||'Tempfile size(Gb): '||trim(TO_CHAR(SUM(bytes)/1073741824, '9,990'))
FROM v$tempfile
UNION
SELECT 4 sort1,'Segment size (Gb): '||trim(TO_CHAR(SUM(bytes)/1073741824, '9,990'))
FROM dba_segments
UNION
SELECT 5 sort1,'Tables/Indexes: '|| trim(TO_CHAR(SUM(DECODE(type#, 2, 1, 0)), '999,990'))||'/'|| trim(TO_CHAR(SUM(DECODE(type#, 1, 1, 0)), '999,990'))
FROM sys.obj$
WHERE owner# <> 0
UNION
SELECT 6 sort1,'Total DB Users: '||trim( TO_CHAR(COUNT(*), '9,990'))
FROM sys.user$ WHERE
type# = 1
UNION
SELECT 7 sort1,'Online Sessions: '||trim( TO_CHAR(COUNT(*), '9,990'))
FROM gv$session
WHERE type='USER'
UNION
SELECT 8 sort1,'Active Sessions: '||trim( TO_CHAR(COUNT(*), '9,990'))
FROM gv$session
WHERE type='USER' and status = 'ACTIVE'
UNION
SELECT 9 sort1,'Session highwater: '|| trim(TO_CHAR(sessions_highwater, '9,990'))
FROM v$license
UNION
SELECT 10 sort1,'SGA (Mb): '||trim(TO_CHAR(SUM(value)/1048576, '99,990.99'))
FROM v$sga;
SORT1 Database Details
---------- ------------------------------------------------------------
1 DBNAME:DEV DBID:170256562 Created:15/08/2013 22:30:32
Log mode:ARCHIVELOG Forcelogging:YES Open mode:READ WRITE
Remote archiving:ENABLED Database role:PRIMARY
2 Datafiles: 33 Datafile size(Gb): 305
3 Tempfiles: 1 Tempfile size(Gb): 20
4 Segment size (Gb): 219
5 Tables/Indexes: 6,683/8,977
6 Total DB Users: 104
7 Online Sessions: 93
8 Active Sessions: 18
9 Session highwater: 124
10 SGA (Mb): 8,155.42
prompt**====================================================================================================**
prompt** **Database Parameters Details**
prompt**====================================================================================================**
set pagesize 600;
set line 200;
column "database parameter" format a40;
column "VALUE" format a40;
select name "Database Parameter",value from v$spparameter
where isspecified='TRUE' and NAME != 'control_files';
Database Parameter VALUE
---------------------------------------- ----------------------------------------
processes 500
sessions 772
timed_statistics TRUE
event
streams_pool_size 167772160
nls_language AMERICAN
nls_territory AMERICA
nls_sort BINARY
nls_date_format DD-MON-RR
nls_numeric_characters .,
nls_calendar GREGORIAN
memory_target 6442450944
memory_max_target 8589934592
db_file_name_convert /u02/oradata/SIT1
db_file_name_convert /u06/oradata/DEV
log_file_name_convert /u02/oradata/SIT1
log_file_name_convert /u06/oradata/DEV
control_file_record_keep_time 37
db_block_size 8192
compatible 11.2.0.0.0
log_archive_dest_1 LOCATION=/u05/archivelogs/DEV/
log_archive_format arch_%t_%s_%r.arc
log_buffer 9347072
log_checkpoint_interval 100000
log_checkpoint_timeout 3600
db_files 500
db_recovery_file_dest /u04/flashback/DEV
db_recovery_file_dest_size 5368709120
log_checkpoints_to_alert TRUE
dml_locks 10000
undo_management AUTO
undo_tablespace UNDO_TS
sec_case_sensitive_logon FALSE
remote_login_passwordfile EXCLUSIVE
audit_sys_operations TRUE
dispatchers (PROTOCOL=TCP) (SERVICE=DEVXDB)
local_listener (ADDRESS=(PROTOCOL=TCP)(HOST=devcrpdb1.t
hesource.ca)(PORT=1524))
session_cached_cursors 900
utl_file_dir /temp
plsql_warnings DISABLE:ALL
job_queue_processes 3
parallel_max_servers 32
result_cache_max_size 16121856
audit_trail DB
db_name DEV
open_cursors 900
os_authent_prefix
optimizer_mode ALL_ROWS
_complex_view_merging FALSE
_unnest_subquery FALSE
query_rewrite_enabled TRUE
workarea_size_policy AUTO
optimizer_dynamic_sampling 2
skip_unusable_indexes TRUE
sec_protocol_error_trace_action LOG
diagnostic_dest /u01/app/oracle
max_dump_file_size 20480
SET FEEDBACK OFF;
set pagesize 600;
set line 200;
column "NLS_Parameter" format a40;
column "VALUE" format a40;
Select parameter "NLS_Parameter", value from nls_database_parameters;
NLS_Parameter VALUE
---------------------------------------- ----------------------------------------
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_RDBMS_VERSION 11.2.0.2.0
prompt
prompt
prompt**===================================================================================================**
prompt** **Tunning Database SGA/PGA**
prompt**===================================================================================================**
prompt
set pagesize 0;
SELECT 'SGA MAX Size in MB: '|| trunc(SUM(VALUE)/1024/1024, 2) "SGA_MAX_MB" FROM V$SGA;
SGA MAX Size in MB: 8155.41
set pagesize 50;
set line 200;
column "SGA Pool"format a33;
col "m_bytes" format 999999.99;
select pool "SGA Pool", m_bytes from ( select pool, to_char( trunc(sum(bytes)/1024/1024,2), '99999.99' ) as M_bytes
from v$sgastat
where pool is not null group by pool
union
select name as pool, to_char( trunc(bytes/1024/1024,3), '99999.99' ) as M_bytes
from v$sgastat
where pool is null order by 2 desc
) UNION ALL
select 'TOTAL' as pool, to_char( trunc(sum(bytes)/1024/1024,3), '99999.99' ) from v$sgastat;
SGA Pool M_BYTES
--------------------------------- ---------------------------
shared pool 2688.00
buffer_cache 1392.00
streams pool 160.00
log_buffer 25.28
java pool 16.00
large pool 16.00
fixed_sga 2.13
TOTAL 4299.41
Select round(tot.bytes /1024/1024 ,2) sga_total, round(used.bytes /1024/1024 ,2) used_mb, round(free.bytes /1024/1024 ,2) free_mb
from (select sum(bytes) bytes from v$sgastat where name != 'free memory') used,
(select sum(bytes) bytes from v$sgastat where name = 'free memory') free,
(select sum(bytes) bytes from v$sgastat) tot;
SGA_TOTAL USED_MB FREE_MB
---------- ---------- ----------
4299.41 3576.71 722.71
select pool, round(sgasize/1024/1024,2) "SGA_TARGET",
round(bytes/1024/1024,2) "FREE_MB",
round(bytes/sgasize*100, 2) "%FREE"
from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
where f.name = 'free memory';
POOL SGA_TARGET FREE_MB %FREE
------------------------------------ ---------- ---------- ----------
shared pool 4299.41 546.48 12.71
large pool 4299.41 8.19 .19
java pool 4299.41 9.5 .22
streams pool 4299.41 158.93 3.7
prompt
prompt
prompt Tunning Shared Pool Size:
prompt*-----------------------------------------------------------------------**
col "Data Dict. Gets" heading Data_Dict.|Gets;
col "Data Dict. Cache Misses" heading Dict._Cache|Misses;
col "Data Dict Cache Hit Ratio" heading Dict._Cache|Hit_Ratio;
col "% Missed" heading Missed|%;
SELECT SUM(gets) "Data Dict. Gets", SUM(getmisses) "Data Dict. Cache Misses"
, TRUNC((1-(sum(getmisses)/SUM(gets)))*100, 2) "Data Dict Cache Hit Ratio"
, TRUNC(SUM(getmisses)*100/SUM(gets), 2) "% Missed"
FROM v$rowcache;
Data_Dict. Dict._Cache Dict._Cache Missed
Gets Misses Hit_Ratio %
---------- ----------- ----------- ----------
4675279774 8410169 99.82 .17
prompt
Prompt* The Dict. Cache Hit% shuold be > 90% and misses% should be < 15%. If not consider increase SHARED_POOL_SIZE.
col "Cache Misses" heading Cache|Misses;
col "Library Cache Hit Ratio" heading Lib._Cache|Hit_Ratio;
col "% Missed" heading Missed|%;
SELECT SUM(pins) "Executions", SUM(reloads) "Cache Misses"
, TRUNC((1-(SUM(reloads)/SUM(pins)))*100, 2) "Library Cache Hit Ratio"
, ROUND(SUM(reloads)*100/SUM(pins)) "% Missed"
FROM v$librarycache;
Cache Lib._Cache Missed
Executions Misses Hit_Ratio %
---------- ---------- ---------- ----------
4354872178 1572780 99.96 0
prompt
Prompt* The Lib. Cache Hit% shuold be > 90% and misses% should be < 1%. If not consider increase SHARED_POOL_SIZE.
set pagesize 25;
col "Tot SQL since startup" format a25;
col "SQL executing now" format a17;
SELECT TO_CHAR(SUM(executions)) "Tot SQL since startup", TO_CHAR(SUM(users_executing)) "SQL executing now"
FROM v$sqlarea;
Tot SQL since startup SQL executing now
------------------------- -----------------
830014311 13
set pagesize 25;
col "Namespace" heading name|space;
col "Hit Ratio" heading Hit|Ratio;
col "Pin Hit Ratio" heading Pin_Hit|Ratio;
col "Invalidations" heading invali|dations;
SELECT namespace "Namespace", TRUNC(gethitratio*100) "Hit Ratio",
TRUNC(pinhitratio*100) "Pin Hit Ratio", reloads "Reloads", invalidations "Invalidations"
FROM v$librarycache;
prompt
prompt* GETHITRATIO and PINHITRATIO should be more than 90%.
prompt* If RELOADS > 0 then'cursor_space_for_time' Parameter do not set to 'TRUE'
prompt* More of Invalid object in namespace will cause more reloads.
set line 200;
col "NAME" format a30;
col "VALUE" format a12;
select p.name "NAME", a.free_space, p.value "VALUE", trunc(a.free_space/p.value, 2) "FREE%", requests, request_misses req_misses
from v$parameter p, v$shared_pool_reserved a
where p.name = 'shared_pool_reserved_size';
NAME FREE_SPACE VALUE FREE% REQUESTS REQ_MISSES
------------------------------ ---------- ------------ ---------- ---------- ----------
shared_pool_reserved_size 118816624 123312537 .96 0 0
prompt
Prompt* %FREE should be > 0.5, request_failures,request_misses=0 or near 0. If not consider increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE.
prompt
prompt
prompt Tunning Buffer Cache:
prompt -----------------------------------------------------------------------**
SELECT TRUNC( ( 1 - ( SUM(decode(name,'physical reads',value,0)) / ( SUM(DECODE(name,'db block gets',value,0))
+ (SUM(DECODE(name,'consistent gets',value,0))) )) ) * 100 ) "Buffer Hit Ratio"
FROM v$sysstat;
Buffer Hit Ratio
----------------
93
prompt* The Buffer Cache Hit% should be >90%. If not and the shared pool hit ratio is good consider increase DB_CACHE_SIZE.
set line 200;
col event format a20;
select event, total_waits, time_waited
from v$system_event
where event in ('buffer busy waits');
EVENT TOTAL_WAITS TIME_WAITED
-------------------- ----------- -----------
buffer busy waits 412337 114297
prompt
prompt* Check for waits to find a free buffer in the buffer cache and Check if the I/O system is slow.
prompt* Consider increase the size of the buffer cache if it is too small. Consider increase the number of DBWR process if the buffer cache is properly sized.
prompt
prompt
prompt Tunning Redolog Buffer:
prompt -----------------------------------------------------------------------**
col "redolog space request" heading redolog_space|request;
col "redolog space wait time" heading redolog_space|wait_time;
col "Redolog space ratio" heading redolog_space|ratio;
Select e. value "redolog space request", s.value "redolog space wait time", Round(e.value/s.value,2) "Redolog space ratio"
From v$sysstat s, v$sysstat e
Where s.name = 'redo log space requests'
and e.name = 'redo entries';
redolog_space redolog_space redolog_space
request wait_time ratio
------------- ------------- -------------
558458807 1348 414286.95
prompt
prompt
prompt Tunning PGA Aggregate Target:
prompt -----------------------------------------------------------------------**
set pagesize 600;
set line 200;
column PGA_Component format a40;
column value format 999999999999;
select name "PGA_Component", value from v$pgastat;
PGA_Component VALUE
---------------------------------------- -------------
aggregate PGA target parameter 2248146944
aggregate PGA auto target 1710084096
global memory bound 224808960
total PGA inuse 348168192
total PGA allocated 485531648
maximum PGA allocated 1574793216
total freeable PGA memory 95682560
process count 120
max processes count 156
PGA memory freed back to OS #############
total PGA used for auto workareas 0
maximum PGA used for auto workareas 905170944
total PGA used for manual workareas 0
maximum PGA used for manual workareas 255836160
over allocation count 0
bytes processed #############
extra bytes read/written 70793925632
cache hit percentage 97
recompute count (total) 8474732
Select count(*) "Total No. of Process" from v$process;
Total No. of Process
--------------------
123
set line 200;
column "PGA Target" format a40;
column VALUE_MB format 9999999999.99
SELECT NAME "PGA Target", VALUE/1024/1024 VALUE_MB
FROM V$PGASTAT
WHERE NAME IN ('aggregate PGA target parameter',
'total PGA allocated',
'total PGA inuse')
union
SELECT NAME, VALUE
FROM V$PGASTAT
WHERE NAME IN ('over allocation count');
PGA Target VALUE_MB
---------------------------------------- --------------
aggregate PGA target parameter 2144.00
over allocation count .00
total PGA allocated 463.94
total PGA inuse 332.86
set line 200;
column "PGA_Work_Pass" format a40;
column "PER" format 999;
select name "PGA_Work_Pass", cnt, decode(total, 0, 0, round(cnt*100/total)) per
from (select name, value cnt, (sum(value) over()) total
from v$sysstat where name like 'workarea exec%'
);
PGA_Work_Pass CNT PER
---------------------------------------- ---------- ----
workarea executions - optimal 227377300 100
workarea executions - onepass 232 0
workarea executions - multipass 0 0
prompt
prompt
prompt**===================================================================================================**
prompt** **Tablespace/CRD File Information**
prompt**===================================================================================================**
col "Database Size" format a15;
col "Free space" format a15;
select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",
round(free.p / 1024 / 1024/1024) || ' GB' "Free space"
from (select bytes from v$datafile
union all select bytes from v$tempfile
union all select bytes from v$log) used,
(select sum(bytes) as p from dba_free_space) free
group by free.p;
Database Size Free space
--------------- ---------------
328 GB 92 GB
SELECT a.tablespace_name tablespace_name,
ROUND(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
-- ROUND(NVL(b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
ROUND((a.bytes_alloc - NVL(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used,
ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_Free,
(case when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)<=0
then 'Immediate action required!'
when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)<5
then 'Critical (<5% free)'
when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)<15
then 'Warning (<15% free)'
when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)<25
then 'Warning (<25% free)'
when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)>60
then 'Waste of space? (>60% free)'
else 'OK'
end) msg
FROM ( SELECT f.tablespace_name,
SUM(f.bytes) bytes_alloc,
SUM(DECODE(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
FROM DBA_DATA_FILES f
GROUP BY tablespace_name) a,
( SELECT f.tablespace_name,
SUM(f.bytes) bytes_free
FROM DBA_FREE_SPACE f
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name (+)
UNION
SELECT h.tablespace_name,
ROUND(SUM(h.bytes_free + h.bytes_used) / 1048576, 2),
-- ROUND(SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / 1048576, 2),
ROUND(SUM(NVL(p.bytes_used, 0))/ 1048576, 2),
ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2),
(case when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)<=0 then 'Immediate action required!'
when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)<5 then 'Critical (<5% free)'
when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)<15 then 'Warning (<15% free)'
when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)<25 then 'Warning (<25% free)'
when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)>60 then 'Waste of space? (>60% free)'
else 'OK'
end) msg
FROM sys.V_$TEMP_SPACE_HEADER h, sys.V_$TEMP_EXTENT_POOL p
WHERE p.file_id(+) = h.file_id
AND p.tablespace_name(+) = h.tablespace_name
GROUP BY h.tablespace_name
ORDER BY 1;
TABLESPACE_NAME MEGS_ALLOC MEGS_USED PCT_FREE
------------------------------------------------------------------------------------------ ---------- ---------- ----------
MSG
---------------------------------------------------------------------------------
AQJMS 263 181.38 31.04
OK
LOB_DATA 108 28.44 73.67
Waste of space? (>60% free)
ODI_MASTER 26 12.06 53.61
OK
ODI_WORK 53 21.63 59.2
OK
ORABPEL 945 641.38 32.13
OK
RETEK_DATA 146463.98 117269.48 19.93
Warning (<25% free)
RETEK_INDEX 69478 56067.19 19.3
Warning (<25% free)
RETEK_PART01_D 8602 7087.94 17.6
Warning (<25% free)
RETEK_PART01_I 3072 1 99.97
Waste of space? (>60% free)
RETEK_PART02_D 5376 3624.38 32.58
OK
RETEK_PART02_I 3072 1 99.97
Waste of space? (>60% free)
RETEK_PART03_D 4838 3259.63 32.62
OK
RETEK_PART03_I 3072 1 99.97
Waste of space? (>60% free)
RETEK_PART04_D 5645 4364.13 22.69
Warning (<25% free)
RETEK_PART04_I 3072 1 99.97
Waste of space? (>60% free)
RETEK_PART05_D 3763 2813.81 25.22
OK
RETEK_PART05_I 3072 1 99.97
Waste of space? (>60% free)
RETEK_PART06_D 6195 4660.06 24.78
Warning (<25% free)
RETEK_PART06_I 3072 1 99.97
Waste of space? (>60% free)
SYSAUX 2688 1966.75 26.83
OK
SYSTEM 2688 1782.88 33.67
OK
TEMP 20480 0 100
Waste of space? (>60% free)
TOOLS 2000 1241.38 37.93
OK
UNDO_TS 21000 8122.5 61.32
Waste of space? (>60% free)
USERS 13978 5207.13 62.75
Waste of space? (>60% free)
set linesize 200
col file_name format a50 heading "Datafile Name"
col allocated_mb format 999999.99;
col used_mob format 999999.99;
col free_mb format 999999.99;
col tablespace_name format a20;
SELECT SUBSTR (df.NAME, 1, 40) file_name, dfs. tablespace_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb, NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_mb, c.autoextensible
FROM v$datafile df, dba_free_space dfs, DBA_DATA_FILES c
WHERE df.file# = dfs.file_id(+) AND df.file# = c.FILE_ID
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes, dfs.tablespace_name, c.autoextensible
ORDER BY file_name;
Datafile Name TABLESPACE_NAME ALLOCATED_MB USED_MB FREE_MB AUTOEXTEN
-------------------------------------------------- -------------------- ------------ ---------- ---------- ---------
/u06/oradata/DEV/aqjms01.dbf AQJMS 263.00 181.375 81.63 NO
/u06/oradata/DEV/lob_data01.dbf LOB_DATA 108.00 28.4375 79.56 NO
/u06/oradata/DEV/odi_master01.dbf ODI_MASTER 26.00 12.0625 13.94 NO
/u06/oradata/DEV/odi_work01.dbf ODI_WORK 53.00 21.625 31.38 NO
/u06/oradata/DEV/orabpel.dbf ORABPEL 945.00 641.375 303.63 NO
/u06/oradata/DEV/retek_data01.dbf RETEK_DATA 30720.00 30674 46.00 NO
/u06/oradata/DEV/retek_data02.dbf RETEK_DATA 32767.98 32723.7969 44.19 NO
/u06/oradata/DEV/retek_data03.dbf RETEK_DATA 32256.00 32180.75 75.25 NO
/u06/oradata/DEV/retek_data04.dbf RETEK_DATA 30720.00 21017.9375 9702.06 NO
/u06/oradata/DEV/retek_data05.dbf RETEK_DATA 20000.00 673 19327.00 NO
/u06/oradata/DEV/retek_index01.dbf RETEK_INDEX 25600.00 23158.6875 2441.31 NO
SELECT TO_CHAR(creation_time, 'RRRR Month') "Year/Month",
round(SUM(bytes)/1024/1024/1024) "Datafile Growth Rate in GB"
FROM sys.v_$datafile
WHERE creation_time < sysdate
GROUP BY TO_CHAR(creation_time, 'RRRR Month');
Year/Month Datafile Growth Rate in GB
--------------------------------------------------------------------------------------------------------------------------- --------------------------
2012 December 18
2011 October 2
2011 June 122
2015 October 20
2014 March 30
2013 May 32
2012 October 3
2013 March 1
2011 December 25
2013 August 21
2013 July 1
2012 February 32
TTI off
prompt
prompt** Report Tablespace < 10% free space**
prompt** -----------------------------------------------------------------------**
set pagesize 300;
set linesize 100;
column tablespace_name format a15 heading Tablespace;
column sumb format 999,999,999;
column extents format 9999;
column bytes format 999,999,999,999;
column largest format 999,999,999,999;
column Tot_Size format 999,999 Heading "Total Size(Mb)";
column Tot_Free format 999,999,999 heading "Total Free(Kb)";
column Pct_Free format 999.99 heading "% Free";
column Max_Free format 999,999,999 heading "Max Free(Kb)";
column Min_Add format 999,999,999 heading "Min space add (MB)";
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from (select tablespace_name,0 tots,sum(bytes) sumb
from sys.dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0 from
sys.dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;
Tablespace Total Size(Mb) Total Free(Kb) % Free Min space add (MB)
--------------- -------------- -------------- ------- ------------------
RETEK_INDEX 197,160 17,360,000 8.60 14,849
RETEK_DATA 369,928 36,797,696 9.71 23,005
set pagesize 50;
col name format A60 heading "Control Files";
select name from sys.v_$controlfile;
or
select name from v$controlfile;
Control Files
------------------------------------------------------------
/u06/oradata/DEV/control01.ctl
/u02/oradata/DEV/control02.ctl
set pagesize 0;
select
(case
when count(*)>3 then '+ '||count(*)||' times detected when log switches occured more than 1 log per 5 minutes.'||chr(10)||
'+ You may consider to increase the redo log size.'
else '+ Redo log size: OK'
end) "Redolog Size Status"
from (
select trunc(FIRST_TIME,'HH') Week, count(*) arch_no, trunc(10*count(*)/60) archpermin
from v$log_history
group by trunc(FIRST_TIME,'HH')
having trunc(5*count(*)/60)>1
);
+ Redo log size: OK
set pagesize 0;
select (case when sync_waits = 0 then '+ Waits for log file sync not detected: log_buffer is OK'
else '+ Waits for log file sync detected ('||sync_waits||' times): Consider to increase the log_buffer'
end) "Log Buffer Status"
from ( select decode( sum(w.total_waits), 0, 0,
nvl(100 * sum(l.total_waits) / sum(w.total_waits), 0)
) sync_waits
from sys.v_$bgprocess b, sys.v_$session s, sys.v_$session_event l, sys.v_$session_event w
where
b.name like 'DBW_' and s.paddr = b.paddr and
l.sid = s.sid and l.event = 'log file sync' and
w.sid = s.sid and w.event = 'db file parallel write'
);
+ Waits for log file sync not detected: log_buffer is OK
prompt
prompt Last 24hrs Log switch Report:
prompt -----------------------------------------------------------------------**
set pagesize 0;
select '+ Daily (max) : '||max(no)||' switches. Size: '||round((max(no) * max(logsize )) ,2)||'MB'||chr(10)||
'+ Daily (avg) : '||trunc(avg(no))||' switches. Size: '||round((avg(no) * max(logsize )) ,2)||'MB'||chr(10)||
'+ Daily (min) : '||min(no)||' switches. Size: '||round((min(no) * max(logsize )) ,2)||'MB'
from (select trunc(FIRST_TIME,'DD'), count(*) no
from v$log_history
where FIRST_TIME > sysdate - 31
group by trunc(FIRST_TIME,'DD')),
(select max(bytes/1024/1024) logsize from v$log);
+ Daily (max) : 228 switches. Size: 233472MB
+ Daily (avg) : 115 switches. Size: 117984MB
+ Daily (min) : 33 switches. Size: 33792MB
set pagesize 0;
select '+ Weekly (max): '||max(no)||' switches. Size: '||round((max(no) * max(logsize )) ,2)||'MB'||chr(10)||
'+ Weekly (avg): '||trunc(avg(no))||' switches. Size: '||round((avg(no) * max(logsize )) ,2)||'MB'||chr(10)||
'+ Weekly (min): '||min(no)||' switches. Size: '||round((min(no) * max(logsize )) ,2)||'MB'
from (select trunc(FIRST_TIME,'WW'), count(*) no
from v$log_history
where FIRST_TIME > sysdate - 31
group by trunc(FIRST_TIME,'WW')),
(select max(bytes/1024/1024) logsize from v$log);
+ Weekly (max): 42 switches. Size: 43008MB
+ Weekly (avg): 35 switches. Size: 36454.4MB
+ Weekly (min): 14 switches. Size: 14336MB
set pagesize 25;
select trunc(completion_time) log_date, count(*) log_switch, round((sum(blocks*block_size) / 1024 / 1024)) "SIZE_MB"
from v$archived_log
WHERE completion_time > (sysdate-1) - 1/24 AND DEST_ID = 1
group by trunc(completion_time)
order by 1 desc;
LOG_DATE LOG_SWITCH SIZE_MB
--------------- ---------- ----------
26-FEB-16 1 13
25-FEB-16 4 203
prompt
prompt
prompt**====================================================================================================**
prompt** **Database Users Activities**
prompt**====================================================================================================**
set pagesize 100;
set line 200;
col username format a20;
col profile format a10;
col default_ts# format a18;
col temp_ts# format a10;
col created format a12;
Select username, account_status status, TO_CHAR(created, 'dd-MON-yyyy') created, profile,
default_tablespace default_ts#, temporary_tablespace temp_ts# from dba_users
where default_tablespace in ('SDH_HRMS_DBF', 'SDH_TIMS_DBF', 'SDH_SHTR_DBF', 'SDH_EDSS_DBF', 'SDH_FIN_DBF', 'SDH_FIN_DBF', 'USERS');
USERNAME STATUS CREATED PROFILE DEFAULT_TS# TEMP_TS#
-------------------- ---------- ------------ ---------- ------------------ ---------
ORAESB OPEN 16-SEP-2013 DEFAULT USERS TEMP
ORAWSM OPEN 16-SEP-2013 DEFAULT USERS TEMP
ORACLE OPEN 20-JUN-2011 DEFAULT USERS TEMP
ECOMREADONLY OPEN 28-JUL-2014 DEFAULT USERS TEMP
BACKUPMGR OPEN 18-MAR-2013 DEFAULT USERS TEMP
UCMDB_ID_IBM OPEN 27-MAR-2014 DEFAULT USERS TEMP
AIA OPEN 16-SEP-2013 DEFAULT USERS TEMP
JMSUSER OPEN 16-SEP-2013 DEFAULT USERS TEMP
APTARECL OPEN 27-MAR-2014 DEFAULT USERS TEMP
No of objects with in schemas--
select obj.owner "USERNAME", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "Size_MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj,
(select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc,2 desc, 1;
USERNAME Objects Size_MB
-------------------- ---------- ----------
RMS13 39176 179107
SYS 31757 11357
TSOURCE 870 11038
TS_AUDIT 183 11028
CONV 245 7687
ORAESB 243 1366
PERFSTAT 148 1096
col status format a20 heading "Session status";
col TOTAL format 9999999999 heading "# sessions";
select status, count(*) TOTAL from gv$session
where type='USER'
group by inst_id,status
order by 1,2;
Session status # sessions
-------------------- -----------
ACTIVE 16
INACTIVE 75
set line 200;
col LOGON_TIME format a10;
col sid format 99;
col status format a8;
col process format a12;
col SCHEMANAME format a12;
col OSUSER format a15;
col machine format a25;
col SQL_TEXT format a75;
SELECT S.LOGON_TIME, s.sid, s.process, s.schemaname, s.osuser, s.MACHINE, a.sql_text
FROM v$session s, v$sqlarea a, v$process p
WHERE s.SQL_HASH_VALUE = a.HASH_VALUE
AND s.SQL_ADDRESS = a.ADDRESS
AND s.PADDR = p.ADDR
AND S.STATUS = 'ACTIVE';
LOGON_TIME SID PROCESS SCHEMANAME OSUSER MACHINE SQL_TEXT
---------- --- ------------ ------------ --------------- ------------------------- ---------------------------------------------------------------------------
15-DEC-15 ### 1234 AIA devcrpapp1.thesource.ca begin dbms_aqin.aq$_dequeue_in( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :1
1, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :2
6, :27, :28, :29); end;
prompt
prompt
prompt**==================================================================================================**
prompt** **Database Object Information**
prompt**==================================================================================================**
prompt
prompt List of Largest Object in Database:
prompt -----------------------------------------------------------------------**
set line 200;
col SEGMENT_NAME format a30;
col SEGMENT_TYPE format a10;
col BYTES format a15;
col TABLESPACE_NAME FORMAT A25;
SELECT * FROM (select SEGMENT_NAME, SEGMENT_TYPE TYPE, BYTES/1024/1024 SIZE_MB,
TABLESPACE_NAME from dba_segments order by 3 desc ) WHERE ROWNUM <= 5;
SEGMENT_NAME TYPE SIZE_MB Tablespace
------------------------------ ------------------------------------------------------ ---------- -------------------------
FUTURE_COST_GTT_AU TABLE 11961 RETEK_DATA
DEAL_ITEMLOC_SK2 TABLE 9395 RETEK_DATA
DEAL_ITEMLOC_SK1 TABLE 9371 RETEK_DATA
PRICE_HIST_I2 INDEX 6994 RETEK_INDEX
PRICE_HIST_I1 INDEX 6854 RETEK_INDEX
set line 200;
col owner format a15;
col object_name format a25;
col object_type format a15;
col last_modified format a20;
col created format a20;
col status format a10;
select owner, object_name, object_type, to_char(LAST_DDL_TIME,'MM/DD/YYYY HH24:MI:SS') last_modified,
to_char(CREATED,'MM/DD/YYYY HH24:MI:SS') created, status
from dba_objects
where (SYSDATE - LAST_DDL_TIME) < 7 and owner IN( 'RMS13', 'TSOURCE')
order by last_ddl_time DESC;
RMS13 PK_3101 INDEX 02/26/2016 02:51:52 02/26/2016 02:51:52 VALID
RMS13 PK__3101 TABLE 02/26/2016 02:51:52 02/26/2016 02:51:52 VALID
RMS13 RPM_ITEMLOC_THREAD TABLE 02/26/2016 02:26:42 06/22/2011 14:02:55 VALID
RMS13 RPM_ITEMLOC_THREAD_I1 INDEX 02/26/2016 02:26:42 06/22/2011 14:02:57 VALID
RMS13 RPM_ITEMLOC_THREAD_I2 INDEX 02/26/2016 02:26:42 06/22/2011 14:02:57 VALID
prompt
set pagesize 0;
SELECT 'Object Created in this Week: '|| count(1) from user_objects
where created >= sysdate -7;
Object Created in this Week: 308
prompt
prompt List of Invalid objects of database:
prompt -----------------------------------------------------------------------**
set pagesize 50;
Select owner "USERNAME", object_type, count(*) INVALID from dba_objects
where status='INVALID' group by owner, object_type;
USERNAME OBJECT_TYPE INVALID
-------------------- --------------- ----------
TSOURCE PROCEDURE 1
CONV PACKAGE BODY 4
RMS13 TRIGGER 2
RMS13 FUNCTION 1
set pagesize 50;
SELECT dt.owner, dt.table_name "Table Change > 10%",
ROUND ( (DELETES + UPDATES + INSERTS) / num_rows * 100) PERCENTAGE
FROM dba_tables dt, all_tab_modifications atm
WHERE dt.owner = atm.table_owner
AND dt.table_name = atm.table_name
AND num_rows > 0
AND ROUND ( (DELETES + UPDATES + INSERTS) / num_rows * 100) >= 10
ORDER BY 3 desc;
OWNER Table Change > 10% PERCENTAGE
--------------- ------------------------------------------------------------------------------------------ ----------
RMS13 SA_EXPORTED 10
RMS13 SA_EXPORTED 10
prompt
prompt Database Chained Rows Info:
prompt -----------------------------------------------------------------------**
col table_name format a25;
select owner, table_name, pct_free, pct_used, avg_row_len, num_rows, chain_cnt, trunc(chain_cnt/num_rows*100, 2) as perc
from dba_tables where owner not in ('SYS','SYSTEM')
and table_name not in (select table_name from dba_tab_columns
where data_type in ('RAW','LONG RAW') )
and chain_cnt > 0 order by chain_cnt desc;
OWNER TABLE_NAME % Free PCT_USED AVG_ROW_LEN NUM_ROWS CHAIN_CNT PERC
--------------- ------------------------- ------- ---------- ----------- ---------- ---------- ----------
RMS13 RPM_BULK_CC_PE_THREAD 10.00 20 822 342 41.6
prompt
prompt
prompt**==================================================================================================**
prompt** **RMAN Configuration and Backup**
prompt**==================================================================================================**
col "RMAN CONFIGURE PARAMETERS" format a100;
select 'CONFIGURE '||name ||' '|| value "RMAN CONFIGURE PARAMETERS"
from v$rman_configuration
order by conf#;
RMAN CONFIGURE PARAMETERS
----------------------------------------------------------------------------------------------------
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS
CONFIGURE BACKUP OPTIMIZATION ON
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE'
CONFIGURE CONTROLFILE AUTOBACKUP ON
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F'
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 1 BACKUP TYPE TO BACKUPSET
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE 'SBT_TAPE' TO 1
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE 'SBT_TAPE' TO 1
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin
64/DEV/tdpo.opt)'
set line 200;
col "DEVIC" format a6;
col "L" format 9;
col "FIN:SS" format 9999;
SELECT DECODE(backup_type, 'L', 'Archived Logs', 'D', 'Datafile Full', 'I', 'Incremental')
backup_type, bp.tag "RMAN_BACKUP_TAG", device_type "DEVIC", DECODE( bs.controlfile_included, 'NO', null, bs.controlfile_included) controlfile,
(sp.spfile_included) spfile, sum(bs.incremental_level) "L", TO_CHAR(bs.start_time, 'dd/mm/yyyy HH24:MI:SS') start_time
, TO_CHAR(bs.completion_time, 'dd/mm/yyyy HH24:MI:SS') completion_time, sum(bs.elapsed_seconds) "FIN:SS"
FROM v$backup_set bs, (select distinct set_stamp, set_count, tag , device_type
from v$backup_piece
where status in ('A', 'X')) bp,
(select distinct set_stamp , set_count , 'YES' spfile_included
from v$backup_spfile) sp
WHERE bs.start_time > sysdate - 1
AND bs.set_stamp = bp.set_stamp
AND bs.set_count = bp.set_count
AND bs.set_stamp = sp.set_stamp (+)
AND bs.set_count = sp.set_count (+)
group by backup_type, bp.tag, device_type, bs.controlfile_included, pieces, sp.spfile_included,start_time, bs.completion_time
ORDER BY bs.start_time desc;
BACKUP_TYPE RMAN_BACKUP_TAG DEVIC CONTROLFI SPFILE L
--------------------------------------- ------------------------------------------------------------------------------------------------ ------ --------- --------- --
START_TIME COMPLETION_TIME FIN:SS
--------------------------------------------------------- --------------------------------------------------------- ------
Datafile Full TAG20160226T051549 SBT_TA YES YES
PE
26/02/2016 05:15:49 26/02/2016 05:15:49 0
Datafile Full KEEP_7 SBT_TA YES
PE
26/02/2016 05:15:46 26/02/2016 05:15:47 1
set line 200;
col "DBF_BACKUP_MB" format 999999.99;
col "ARC_BACKUP_MB" format 9999.99;
select trunc(completion_time) "BAK_DATE", sum(blocks*block_size)/1024/1024 "DBF_BACKUP_MB", (SELECT sum(blocks*block_size)/1024/1024 from v$backup_redolog
WHERE first_time > sysdate-1) "ARC_BACKUP_MB"
from v$backup_datafile
WHERE completion_time > sysdate - 1
group by trunc(completion_time)
order by 1 DESC;
BAK_DATE DBF_BACKUP_MB ARC_BACKUP_MB
--------------- ------------- -------------
26-FEB-16 253725.80 64.68
25-FEB-16 65.00 64.68
col "Datafiles backed up within 24h" format a40;
col "Control backed up" format 999;
col "SPFiles backed up" format 999;
SELECT dbfiles||' out of '||numfiles||' datafiles backed up' "Datafiles backed up within 24h", cfiles "CFiles", spfiles "SPFiles"
FROM (select count(*) numfiles from v$datafile), (select count(*) dbfiles from v$backup_datafile a, v$datafile b
where a.file# = b.file# and a.completion_time > sysdate - 1), (select count(*) cfiles from v$backup_datafile
where file# = 0 and completion_time > sysdate - 1), (select count(*) spfiles from v$backup_spfile where completion_time > sysdate - 1);
Datafiles backed up within 24h CFiles SPFiles
---------------------------------------- ---------- ----------
33 out of 33 datafiles backed up 7 6
prompt
prompt *** Most I/O operation (TOP 5):
prompt*-----------------------------------------------------------------------**
col object_type format a15 heading "Object Type"
col object_name format a27 heading "Object Name"
col statistic_name format a22 heading "Statistic Name"
col value format 99999999999 heading "Value"
SELECT * from (
SELECT object_type, object_name, statistic_name, VALUE
FROM v$segment_statistics
WHERE statistic_name LIKE '%phys%' AND VALUE > 50
ORDER BY 4 DESC
) where rownum < 6;
Object Type Object Name Statistic Name Value
--------------- --------------------------- ---------------------- ------------
TABLE PARTITION ITEM_LOC physical reads direct 77612476
TABLE PARTITION ITEM_LOC physical reads direct 77584158
TABLE PARTITION ITEM_LOC physical reads direct 76197389
TABLE PARTITION ITEM_LOC physical reads direct 76183432
TABLE PARTITION ITEM_LOC physical reads direct 74837565
prompt
prompt *** Most buffer gets (TOP 5):
prompt*-----------------------------------------------------------------------**
col object_type format a10;
col object_name format a30;
col statistic_name format a15;
col value format 99999999999;
SELECT * from (
SELECT object_type, object_name, statistic_name,VALUE
FROM v$segment_statistics
WHERE statistic_name LIKE '%logi%' AND VALUE > 50
ORDER BY 4 DESC
) where rownum < 6;
Object Typ Object Name Statistic Name Value
---------- ------------------------------ --------------- ------------
TABLE TS$ logical reads 1291952336
TABLE USER$ logical reads 920355024
INDEX I_OBJ1 logical reads 765475952
TABLE SEG$ logical reads 468984896
INDEX SYS_C00644 logical reads 406461888
prompt
prompt *** Most I/O operation for particualr Query:
prompt*-----------------------------------------------------------------------**
col sql_text format a60;
col reads_per_exe format 99999999 heading 'reads|per_exe';
col "exe" format 99999;
col "sorts" format 99999;
col buffer_gets heading 'buffer|gets';
col disk_reads heading 'disk|reads';
SELECT * FROM (SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_exe,
a.buffer_gets, a.disk_reads, a.executions "exe", a.sorts "sorts", a.address "address"
FROM v$sqlarea a
ORDER BY 2 DESC)
WHERE rownum <= 5;
reads buffer disk
SQL_TEXT per_exe gets reads exe sorts address
------------------------------------------------------------ --------- ---------- ---------- ------ ------ ----------------
DECLARE job BINARY_INTEGER := :job; next_date TIM 249307 571604890 20941821 84 0 07000001E992AA50
call dbms_space.auto_space_advisor_job_proc ( ) 136879 576457928 51877207 379 0 07000001E98C8E30
call dbms_stats.gather_database_stats_job_proc ( 88829 456716307 33666384 379 0 07000001E9888C38
SELECT COUNT(1) FROM ( select * from itemloc_mfque 73861 295486 295446 4 0 07000001FE0D5150
SELECT COUNT(1) FROM ( select * from itemloc_mfque 73860 147744 147720 2 0 07000001AA75C2A0
select 'top logical i/o process', sid, username, total_user_io amt_used, round(100 * total_user_io/total_io,2) pct_used
from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_io
from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid
and c.name in ('consistent gets', 'db block gets') and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by b.sid, nvl(b.username, p.name) order by 3 desc),
(select sum(value) total_io from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid and c.name in ('consistent gets', 'db block gets'))
where rownum < 2
union all
select 'top memory process', sid, username, total_user_mem, round(100 * total_user_mem/total_mem,2)
from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_mem
from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr
and b.sid = a.sid and c.name in ('session pga memory', 'session uga memory')
and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by b.sid, nvl(b.username, p.name) order by 3 desc),
(select sum(value) total_mem from v$statname c, v$sesstat a
where a.statistic# = c.statistic# and c.name in ('session pga memory', 'session uga memory'))
where rownum < 2
union all
select 'top cpu process', sid, username, total_user_cpu, round(100 * total_user_cpu/greatest(total_cpu,1),2)
from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_cpu
from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid
and c.name = 'CPU used by this session' and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by b.sid, nvl(b.username, p.name) order by 3 desc),
(select sum(value) total_cpu from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid
and c.name = 'CPU used by this session') where rownum < 2;
'TOPLOGICALI/OPROCESS' SID USERNAME AMT_USED PCT_USED
--------------------------------------------------------------------- --- -------------------- ---------- ----------
top logical i/o process ### JMSUSER 134437300 10.75
top memory process ### RMS13 6612680 1.58
top cpu process ### AIA 249844 7.43
prompt
prompt Monitoring Current Running Long Job in Database:
prompt*-----------------------------------------------------------------------**
set line 200;
col opname format a30;
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM V$SESSION_LONGOPS
WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK
order by 1;
prompt
prompt Monitoring Object locking:
prompt*-----------------------------------------------------------------------**
set line 200;
col username format a15;
col lock_type format a10;
col osuser format a15;
col owner format a10;
col object_name format a20;
SELECT s.sid, s. serial#, s.username, l.lock_type, s.osuser, s.machine,
o.owner, o.object_name, ROUND(w.seconds_in_wait/60, 2) "Wait"
FROM v$session s, dba_locks l, dba_objects o, v$session_wait w
WHERE s.sid = l.session_id
AND l.lock_type IN ('DML','DDL')AND l.lock_id1 = o.object_id
AND l.session_id = w.sid
ORDER BY s.sid;
prompt
prompt Monitor DB Corruption or Need of Recovery:
prompt*-----------------------------------------------------------------------**
set line 200;
SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name, d.STATUS,
r.ERROR, r.CHANGE#, r.TIME FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
WHERE t.TS# = d.TS# AND d.FILE# = r.FILE#;
set linesize 200
col name format a45 heading "Datafile Name";
col "Read Time(ms)" heading 'Read|Time(ms)';
col "Write Time(ms)" heading 'write|Time(ms)';
col "Avg_Time" heading 'Avg|Time(ms)';
select name,PHYRDS,PHYWRTS,READTIM "Read Time(ms)",WRITETIM "Write Time(ms)",AVGIOTIM "Avg_Time"
from v$filestat, v$datafile where v$filestat.file#=v$datafile.file#;
Read write Avg
Datafile Name PHYRDS PHYWRTS Time(ms) Time(ms) Time(ms)
--------------------------------------------- ---------- ---------- ---------- ---------- ----------
/u06/oradata/DEV/system01.dbf 5462638 1340208 2104018 8645267 0
/u06/oradata/DEV/sysaux01.dbf 10607588 6842678 2175735 31523516 1
/u06/oradata/DEV/retek_part04_d02.dbf 269772 74206 339349 78357 4
/u06/oradata/DEV/users01.dbf 1198565 2522 8597966 18256 8
set feedback on
prompt
rem -----------------------------------------------------------------------
rem Filename: DB_Health_Rep.sql
rem Purpose: Database Statistics and Health Report
rem -----------------------------------------------------------------------
prompt Recommendations:
prompt ====================================================================================================
prompt* SQL Cache Hit rate ratio should be above 90%, if not then increase the Shared Pool Size.
prompt* Dict Cache Hit rate ratio should be above 85%, if not then increase the Shared Pool Size.
prompt* Buffer Cache Hit rate ratio should be above 90%, if not then increase the DB Block Buffer value.
prompt* Redo Log space requests should be less than 0.5% of redo entries, if not then increase log buffer.
prompt* Redo Log space wait time should be near to 0.
prompt
set serveroutput ON
DECLARE
libcac number(10,2);
rowcac number(10,2);
bufcac number(10,2);
redlog number(10,2);
redoent number;
redowaittime number;
BEGIN
select value into redlog from v$sysstat where name = 'redo log space requests';
select value into redoent from v$sysstat where name = 'redo entries';
select value into redowaittime from v$sysstat where name = 'redo log space wait time';
select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;
select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache;
select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac
from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
where cur.statistic# = ncu.statistic#
and ncu.name = 'db block gets'
and con.statistic# = nco.statistic#
and nco.name = 'consistent gets'
and phys.statistic# = nph.statistic#
and nph.name = 'physical reads';
dbms_output.put_line('CACHE HIT RATE');
dbms_output.put_line('********************');
dbms_output.put_line('SQL Cache Hit rate = '||libcac);
dbms_output.put_line('Dict Cache Hit rate = '||rowcac);
dbms_output.put_line('Buffer Cache Hit rate = '||bufcac);
dbms_output.put_line('Redo Log space requests = '||redlog);
dbms_output.put_line('Redo Entries = '||redoent);
dbms_output.put_line('Redo log space wait time = '||redowaittime);
if
libcac < 90 then dbms_output.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
END IF;
if
rowcac < 85 then dbms_output.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
END IF;
if
bufcac < 90 then dbms_output.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');
END IF;
if
redlog > 1000000 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!');
END IF;
END;
/
CACHE HIT RATE
********************
SQL Cache Hit rate = 99.96
Dict Cache Hit rate = 99.82
Buffer Cache Hit rate = 93.35
Redo Log space requests = 1348
Redo Entries = 558464923
Redo log space wait time = 2547
set pagesize 0;
Select (SELECT NAME FROM V$DATABASE) ||'_Oracle_Database_Health_Report: '||to_char(sysdate,'Mon dd yyyy hh24:mi:ss') "TIMESTAMP" from dual;
prompt**=====================================================================================================**
prompt** **Database Current Status**
prompt**=====================================================================================================**
set pagesize 50;
set line 300;
col HOST_NAME FORMAT a12;
col "HOST_ADDRESS" FORMAT a15;
col RESETLOGS_TIME FORMAT a12;
col "DB RAC?" FORMAT A8;
col days format 9999;
select name INSTANCE,HOST_NAME, UTL_INADDR.GET_HOST_ADDRESS "HOST_ADDRESS",
LOGINS, archiver,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB_UP_TIME", RESETLOGS_TIME "RESET_TIME", FLOOR(sysdate-startup_time) days,
(select DECODE(vp1.value,'TRUE','Yes ('|| decode(vp1.value,'TRUE',' instances: '||vp2.value)||')','No')
from v$instance,
(select value from v$parameter
where name like 'cluster_database'
) vp1,
(select value from v$parameter
where name like 'cluster_database_instances'
) vp2) "DB RAC?"
from v$database,gv$instance;
SELECT BANNER "VERSION" FROM V$VERSION;
col "Database Details" format a60;
SELECT
1 sort1,' DBNAME:'||name ||chr(9)||'DBID:'||dbid ||chr(9)|| 'Created:'||to_char(created, 'dd/mm/yyyy hh24:mi:ss') ||chr(10)||
' Log mode:'||log_mode || decode (log_mode,'ARCHIVELOG','',' !!!') ||chr(9)||'Forcelogging:'||force_logging||chr(9)||'Open mode:'||open_mode||chr(10)||
' Remote archiving:'||remote_archive||chr(9)||'Database role:'||database_role "Database Details"
FROM v$database
UNION
SELECT 2 sort1,'Datafiles: '||trim(TO_CHAR(COUNT(*),'9,990'))||chr(9)||'Datafile size(Gb): '||trim(TO_CHAR(SUM(bytes)/1073741824, '9,990'))
FROM v$datafile
UNION
SELECT 3 sort1,'Tempfiles: '||trim(TO_CHAR(COUNT(*),'9,990'))||chr(9)||'Tempfile size(Gb): '||trim(TO_CHAR(SUM(bytes)/1073741824, '9,990'))
FROM v$tempfile
UNION
SELECT 4 sort1,'Segment size (Gb): '||trim(TO_CHAR(SUM(bytes)/1073741824, '9,990'))
FROM dba_segments
UNION
SELECT 5 sort1,'Tables/Indexes: '|| trim(TO_CHAR(SUM(DECODE(type#, 2, 1, 0)), '999,990'))||'/'|| trim(TO_CHAR(SUM(DECODE(type#, 1, 1, 0)), '999,990'))
FROM sys.obj$
WHERE owner# <> 0
UNION
SELECT 6 sort1,'Total DB Users: '||trim( TO_CHAR(COUNT(*), '9,990'))
FROM sys.user$ WHERE
type# = 1
UNION
SELECT 7 sort1,'Online Sessions: '||trim( TO_CHAR(COUNT(*), '9,990'))
FROM gv$session
WHERE type='USER'
UNION
SELECT 8 sort1,'Active Sessions: '||trim( TO_CHAR(COUNT(*), '9,990'))
FROM gv$session
WHERE type='USER' and status = 'ACTIVE'
UNION
SELECT 9 sort1,'Session highwater: '|| trim(TO_CHAR(sessions_highwater, '9,990'))
FROM v$license
UNION
SELECT 10 sort1,'SGA (Mb): '||trim(TO_CHAR(SUM(value)/1048576, '99,990.99'))
FROM v$sga;
prompt**====================================================================================================**
prompt** **Database Parameters Details**
prompt**====================================================================================================**
set pagesize 600;
set line 200;
column "database parameter" format a40;
column "VALUE" format a40;
select name "Database Parameter",value from v$spparameter
where isspecified='TRUE' and NAME != 'control_files';
SET FEEDBACK OFF;
set pagesize 600;
set line 200;
column "NLS_Parameter" format a40;
column "VALUE" format a40;
Select parameter "NLS_Parameter", value from nls_database_parameters;
prompt
prompt
prompt**===================================================================================================**
prompt** **Tunning Database SGA/PGA**
prompt**===================================================================================================**
prompt
set pagesize 0;
SELECT 'SGA MAX Size in MB: '|| trunc(SUM(VALUE)/1024/1024, 2) "SGA_MAX_MB" FROM V$SGA;
set pagesize 50;
set line 200;
column "SGA Pool"format a33;
col "m_bytes" format 999999.99;
select pool "SGA Pool", m_bytes from ( select pool, to_char( trunc(sum(bytes)/1024/1024,2), '99999.99' ) as M_bytes
from v$sgastat
where pool is not null group by pool
union
select name as pool, to_char( trunc(bytes/1024/1024,3), '99999.99' ) as M_bytes
from v$sgastat
where pool is null order by 2 desc
) UNION ALL
select 'TOTAL' as pool, to_char( trunc(sum(bytes)/1024/1024,3), '99999.99' ) from v$sgastat;
Select round(tot.bytes /1024/1024 ,2) sga_total, round(used.bytes /1024/1024 ,2) used_mb, round(free.bytes /1024/1024 ,2) free_mb
from (select sum(bytes) bytes from v$sgastat where name != 'free memory') used,
(select sum(bytes) bytes from v$sgastat where name = 'free memory') free,
(select sum(bytes) bytes from v$sgastat) tot;
select pool, round(sgasize/1024/1024,2) "SGA_TARGET",
round(bytes/1024/1024,2) "FREE_MB",
round(bytes/sgasize*100, 2) "%FREE"
from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
where f.name = 'free memory';
prompt
prompt
prompt Tunning Shared Pool Size:
prompt*-----------------------------------------------------------------------**
col "Data Dict. Gets" heading Data_Dict.|Gets;
col "Data Dict. Cache Misses" heading Dict._Cache|Misses;
col "Data Dict Cache Hit Ratio" heading Dict._Cache|Hit_Ratio;
col "% Missed" heading Missed|%;
SELECT SUM(gets) "Data Dict. Gets", SUM(getmisses) "Data Dict. Cache Misses"
, TRUNC((1-(sum(getmisses)/SUM(gets)))*100, 2) "Data Dict Cache Hit Ratio"
, TRUNC(SUM(getmisses)*100/SUM(gets), 2) "% Missed"
FROM v$rowcache;
prompt
Prompt* The Dict. Cache Hit% shuold be > 90% and misses% should be < 15%. If not consider increase SHARED_POOL_SIZE.
col "Cache Misses" heading Cache|Misses;
col "Library Cache Hit Ratio" heading Lib._Cache|Hit_Ratio;
col "% Missed" heading Missed|%;
SELECT SUM(pins) "Executions", SUM(reloads) "Cache Misses"
, TRUNC((1-(SUM(reloads)/SUM(pins)))*100, 2) "Library Cache Hit Ratio"
, ROUND(SUM(reloads)*100/SUM(pins)) "% Missed"
FROM v$librarycache;
prompt
Prompt* The Lib. Cache Hit% shuold be > 90% and misses% should be < 1%. If not consider increase SHARED_POOL_SIZE.
set pagesize 25;
col "Tot SQL since startup" format a25;
col "SQL executing now" format a17;
SELECT TO_CHAR(SUM(executions)) "Tot SQL since startup", TO_CHAR(SUM(users_executing)) "SQL executing now"
FROM v$sqlarea;
prompt
set pagesize 0;
select 'Cursor_Space_for_Time: '|| value "Cursor_Space_for_Time"
from v$parameter where name = 'cursor_space_for_time';
set pagesize 25;
col "Namespace" heading name|space;
col "Hit Ratio" heading Hit|Ratio;
col "Pin Hit Ratio" heading Pin_Hit|Ratio;
col "Invalidations" heading invali|dations;
SELECT namespace "Namespace", TRUNC(gethitratio*100) "Hit Ratio",
TRUNC(pinhitratio*100) "Pin Hit Ratio", reloads "Reloads", invalidations "Invalidations"
FROM v$librarycache;
prompt
prompt* GETHITRATIO and PINHITRATIO should be more than 90%.
prompt* If RELOADS > 0 then'cursor_space_for_time' Parameter do not set to 'TRUE'
prompt* More of Invalid object in namespace will cause more reloads.
set line 200;
col "NAME" format a30;
col "VALUE" format a12;
select p.name "NAME", a.free_space, p.value "VALUE", trunc(a.free_space/p.value, 2) "FREE%", requests, request_misses req_misses
from v$parameter p, v$shared_pool_reserved a
where p.name = 'shared_pool_reserved_size';
prompt
Prompt* %FREE should be > 0.5, request_failures,request_misses=0 or near 0. If not consider increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE.
prompt
prompt
prompt Tunning Buffer Cache:
prompt -----------------------------------------------------------------------**
SELECT TRUNC( ( 1 - ( SUM(decode(name,'physical reads',value,0)) / ( SUM(DECODE(name,'db block gets',value,0))
+ (SUM(DECODE(name,'consistent gets',value,0))) )) ) * 100 ) "Buffer Hit Ratio"
FROM v$sysstat;
prompt
prompt* The Buffer Cache Hit% should be >90%. If not and the shared pool hit ratio is good consider increase DB_CACHE_SIZE.
set line 200;
col event format a20;
select event, total_waits, time_waited
from v$system_event
where event in ('buffer busy waits');
select s.segment_name, s.segment_type, s.freelists, w.wait_time, w.seconds_in_wait, w.state
from dba_segments s, v$session_wait w
where w.event = 'buffer busy waits'
AND w.p1 = s.header_file AND w.p2 = s.header_block;
prompt
prompt* Check for waits to find a free buffer in the buffer cache and Check if the I/O system is slow.
prompt* Consider increase the size of the buffer cache if it is too small. Consider increase the number of DBWR process if the buffer cache is properly sized.
prompt
prompt
prompt Tunning Redolog Buffer:
prompt -----------------------------------------------------------------------**
col "redolog space request" heading redolog_space|request;
col "redolog space wait time" heading redolog_space|wait_time;
col "Redolog space ratio" heading redolog_space|ratio;
Select e. value "redolog space request", s.value "redolog space wait time", Round(e.value/s.value,2) "Redolog space ratio"
From v$sysstat s, v$sysstat e
Where s.name = 'redo log space requests'
and e.name = 'redo entries';
prompt
prompt * If the ratio of redolog space is less than 5000 then increase the size of redolog buffer until this ratio stop falling.
prompt * There should be no log buffer space waits. Consider making logfile bigger or move the logfile to faster disk.
col "redo_buff_alloc_retries" heading redo_buffer|alloc_retries;
col "redo_entries" heading redo|entries;
col "pct_buff_alloc_retries" heading pct_buffer|alloc_retries;
select v1.value as "redo_buff_alloc_retries", v2.value as "redo_entries",
trunc(v1.value/v2.value,4) as "pct_buff_alloc_retries"
from v$sysstat v1, v$sysstat v2
where v1.name = 'redo buffer allocation retries'
and v2.name = 'redo entries';
column latch_name format a20
select name latch_name, gets, misses, immediate_gets "Immed Gets", immediate_misses "Immed Misses", trunc((misses/decode(gets,0,1,gets))*100,2) Ratio1,
trunc(immediate_misses/decode(immediate_misses+ immediate_gets,0,1, immediate_misses+immediate_gets)*100,2) Ratio2
from v$latch
where name like 'redo%';
prompt
prompt All ratios should be <= 1% if not then decrease the value of log_small_entry_max_size in init.ora
col event format a30;
select * from v$system_event
where event like 'log%';
prompt
Prompt* If Avg_wait_time is minor ignore it otherwise check the log buffer size w.r.t transaction rate and memory size.
prompt
prompt
prompt Tunning PGA Aggregate Target:
prompt -----------------------------------------------------------------------**
set pagesize 600;
set line 200;
column PGA_Component format a40;
column value format 999999999999;
select name "PGA_Component", value from v$pgastat;
Select count(*) "Total No. of Process" from v$process;
set line 200;
column "PGA Target" format a40;
column VALUE_MB format 9999999999.99
SELECT NAME "PGA Target", VALUE/1024/1024 VALUE_MB
FROM V$PGASTAT
WHERE NAME IN ('aggregate PGA target parameter',
'total PGA allocated',
'total PGA inuse')
union
SELECT NAME, VALUE
FROM V$PGASTAT
WHERE NAME IN ('over allocation count');
set line 200;
column "PGA_Work_Pass" format a40;
column "PER" format 999;
select name "PGA_Work_Pass", cnt, decode(total, 0, 0, round(cnt*100/total)) per
from (select name, value cnt, (sum(value) over()) total
from v$sysstat where name like 'workarea exec%'
);
prompt
Prompt* DBA Must increase PGA_AGG_TARGET when "Multipass" > 0 and Reduce when "Optimal" executions 100%.
prompt
prompt Tunning SORT Area Size:
prompt -----------------------------------------------------------------------**
col name format a20;
select name, value from v$sysstat
where name like 'sorts%';
prompt
prompt
prompt**===================================================================================================**
prompt** **Tablespace/CRD File Information**
prompt**===================================================================================================**
col "Database Size" format a15;
col "Free space" format a15;
select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",
round(free.p / 1024 / 1024/1024) || ' GB' "Free space"
from (select bytes from v$datafile
union all select bytes from v$tempfile
union all select bytes from v$log) used,
(select sum(bytes) as p from dba_free_space) free
group by free.p;
SELECT a.tablespace_name tablespace_name,
ROUND(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
-- ROUND(NVL(b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
ROUND((a.bytes_alloc - NVL(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used,
ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_Free,
(case when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)<=0
then 'Immediate action required!'
when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)<5
then 'Critical (<5% free)'
when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)<15
then 'Warning (<15% free)'
when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)<25
then 'Warning (<25% free)'
when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)>60
then 'Waste of space? (>60% free)'
else 'OK'
end) msg
FROM ( SELECT f.tablespace_name,
SUM(f.bytes) bytes_alloc,
SUM(DECODE(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
FROM DBA_DATA_FILES f
GROUP BY tablespace_name) a,
( SELECT f.tablespace_name,
SUM(f.bytes) bytes_free
FROM DBA_FREE_SPACE f
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name (+)
UNION
SELECT h.tablespace_name,
ROUND(SUM(h.bytes_free + h.bytes_used) / 1048576, 2),
-- ROUND(SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / 1048576, 2),
ROUND(SUM(NVL(p.bytes_used, 0))/ 1048576, 2),
ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2),
(case when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)<=0 then 'Immediate action required!'
when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)<5 then 'Critical (<5% free)'
when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)<15 then 'Warning (<15% free)'
when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)<25 then 'Warning (<25% free)'
when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)>60 then 'Waste of space? (>60% free)'
else 'OK'
end) msg
FROM sys.V_$TEMP_SPACE_HEADER h, sys.V_$TEMP_EXTENT_POOL p
WHERE p.file_id(+) = h.file_id
AND p.tablespace_name(+) = h.tablespace_name
GROUP BY h.tablespace_name
ORDER BY 1;
set linesize 200
col file_name format a50 heading "Datafile Name"
col allocated_mb format 999999.99;
col used_mob format 999999.99;
col free_mb format 999999.99;
col tablespace_name format a20;
SELECT SUBSTR (df.NAME, 1, 40) file_name, dfs. tablespace_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb, NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_mb, c.autoextensible
FROM v$datafile df, dba_free_space dfs, DBA_DATA_FILES c
WHERE df.file# = dfs.file_id(+) AND df.file# = c.FILE_ID
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes, dfs.tablespace_name, c.autoextensible
ORDER BY file_name;
SELECT TO_CHAR(creation_time, 'RRRR Month') "Year/Month",
round(SUM(bytes)/1024/1024/1024) "Datafile Growth Rate in GB"
FROM sys.v_$datafile
WHERE creation_time < sysdate
GROUP BY TO_CHAR(creation_time, 'RRRR Month');
TTI off
prompt
prompt** Report Tablespace < 10% free space**
prompt** -----------------------------------------------------------------------**
set pagesize 300;
set linesize 100;
column tablespace_name format a15 heading Tablespace;
column sumb format 999,999,999;
column extents format 9999;
column bytes format 999,999,999,999;
column largest format 999,999,999,999;
column Tot_Size format 999,999 Heading "Total Size(Mb)";
column Tot_Free format 999,999,999 heading "Total Free(Kb)";
column Pct_Free format 999.99 heading "% Free";
column Max_Free format 999,999,999 heading "Max Free(Kb)";
column Min_Add format 999,999,999 heading "Min space add (MB)";
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from (select tablespace_name,0 tots,sum(bytes) sumb
from sys.dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0 from
sys.dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;
col owner format a15;
SELECT owner, count(*), tablespace_name
FROM dba_segments
WHERE tablespace_name = 'SYSTEM' AND owner NOT IN ('SYS','SYSTEM','CSMIG','OUTLN')
Group by owner, tablespace_name;
set pagesize 0;
prompt
SELECT '+ '||count(*)||' NON-SYSTEM objects detected in SYSTEM tablespace with '||
'total size: '||NVL(round(sum(bytes)/1024/1024,2),0)||'MB' "NON-SYSTEM objects"
FROM dba_segments
WHERE tablespace_name = 'SYSTEM' AND owner not in ('SYS','SYSTEM','CSMIG','OUTLN');
set pagesize 50;
col name format A60 heading "Control Files";
select name from sys.v_$controlfile;
prompt
set pagesize 0;
select (case
when count(1) <2 then '+ At least 2 controlfiles are recommended'
when count(1) >=2 and count(1) <=3 then '+ '||count(1)||' mirrors for controlfile detected. - OK'
else '+ More than 3 controlfiles might have additional overhead. Check the wait events.'
end)
from v$controlfile;
set pagesize 0;
col msg format a79;
select
(case when value <45 then '+ ! "control_file_record_keep_time='||value||'" to low. Set to at least 45'
else '+ "control_file_record_keep_time='||value||'" - OK.'
end) msg
from v$parameter where name = 'control_file_record_keep_time';
set pagesize 50;
select segment_name, owner, tablespace_name, status from dba_rollback_segs;
prompt
set pagesize 0;
select 'The average of rollback segment waits/gets is '||
round((sum(waits) / sum(gets)) * 100,2)||'%'
From v$rollstat;
set pagesize 50;
SELECT TO_CHAR(SUM(value), '999,999,999,999,999') "Total Requests"
FROM v$sysstat
WHERE name IN ('db block gets','consistent gets');
set pagesize 50;
SELECT class "Class", count "Count"
FROM v$waitstat
WHERE class IN ( 'free list', 'system undo header', 'system undo block', 'undo header', 'undo block')
GROUP BY class, count;
prompt
prompt* If these are < 1% of Total Number of request for data then extra rollback segment are needed.
prompt
prompt Check Rollback Contention:
prompt -----------------------------------------------------------------------**
select class, count, time from v$waitstat
where class in ('data block', 'undo header', 'undo block', 'segment header');
prompt
prompt* If the contention is on 'data block' check for SQL statements using unselective indexes.
prompt* if the contention is on 'undo header' consider using automatic segment-space management or add more rollback segments.
prompt* if the contention is on 'undo block' consider using automatic segment-space management or make rollback segment sizes larger.
prompt* If the contention is on 'segment header' look for the segment and consider increase free-lists.
set pagesize 50;
set line 200;
col member format A40 heading "Redolog Files";
col group# format 99;
col archived format a3;
col status format a10;
col first_time format a12;
select a.group#, a.member, b.archived, b.status, b.first_time from v$logfile a, v$log b
where a.group# = b.group# order by a.group#;
prompt
set pagesize 0
select 'Group# '||group#||': '||
(case
when members<2 then '+ Redo log mirroring is recommended'
else ' '||members||' members detected. - OK'
end)
from v$log
where members < 2;
set pagesize 0;
select
(case
when count(*)>3 then '+ '||count(*)||' times detected when log switches occured more than 1 log per 5 minutes.'||chr(10)||
'+ You may consider to increase the redo log size.'
else '+ Redo log size: OK'
end) "Redolog Size Status"
from (
select trunc(FIRST_TIME,'HH') Week, count(*) arch_no, trunc(10*count(*)/60) archpermin
from v$log_history
group by trunc(FIRST_TIME,'HH')
having trunc(5*count(*)/60)>1
);
set pagesize 0;
select (case when sync_waits = 0 then '+ Waits for log file sync not detected: log_buffer is OK'
else '+ Waits for log file sync detected ('||sync_waits||' times): Consider to increase the log_buffer'
end) "Log Buffer Status"
from ( select decode( sum(w.total_waits), 0, 0,
nvl(100 * sum(l.total_waits) / sum(w.total_waits), 0)
) sync_waits
from sys.v_$bgprocess b, sys.v_$session s, sys.v_$session_event l, sys.v_$session_event w
where
b.name like 'DBW_' and s.paddr = b.paddr and
l.sid = s.sid and l.event = 'log file sync' and
w.sid = s.sid and w.event = 'db file parallel write'
);
prompt
prompt Last 24hrs Log switch Report:
prompt -----------------------------------------------------------------------**
set pagesize 0;
select '+ Daily (max) : '||max(no)||' switches. Size: '||round((max(no) * max(logsize )) ,2)||'MB'||chr(10)||
'+ Daily (avg) : '||trunc(avg(no))||' switches. Size: '||round((avg(no) * max(logsize )) ,2)||'MB'||chr(10)||
'+ Daily (min) : '||min(no)||' switches. Size: '||round((min(no) * max(logsize )) ,2)||'MB'
from (select trunc(FIRST_TIME,'DD'), count(*) no
from v$log_history
where FIRST_TIME > sysdate - 31
group by trunc(FIRST_TIME,'DD')),
(select max(bytes/1024/1024) logsize from v$log);
set pagesize 0;
select '+ Weekly (max): '||max(no)||' switches. Size: '||round((max(no) * max(logsize )) ,2)||'MB'||chr(10)||
'+ Weekly (avg): '||trunc(avg(no))||' switches. Size: '||round((avg(no) * max(logsize )) ,2)||'MB'||chr(10)||
'+ Weekly (min): '||min(no)||' switches. Size: '||round((min(no) * max(logsize )) ,2)||'MB'
from (select trunc(FIRST_TIME,'WW'), count(*) no
from v$log_history
where FIRST_TIME > sysdate - 31
group by trunc(FIRST_TIME,'WW')),
(select max(bytes/1024/1024) logsize from v$log);
set pagesize 25;
select trunc(completion_time) log_date, count(*) log_switch, round((sum(blocks*block_size) / 1024 / 1024)) "SIZE_MB"
from v$archived_log
WHERE completion_time > (sysdate-1) - 1/24 AND DEST_ID = 1
group by trunc(completion_time)
order by 1 desc;
set line 200;
col event format a30;
col "time_waited" heading time|waited;
col "average_wait" heading average|wait;
select event, time_waited "time_waited", average_wait "average_wait"
from v$system_event
where event like 'log file switch completion';
prompt
prompt * The pct_buff_retries should be 0 or (< 1%) . If it is greater consider moving the logfile to faster disk.
prompt * If there are log file switch waits, it indicates disk I/O contention. Check that redo log files are stored on separated and fast devices.
prompt
prompt
prompt**====================================================================================================**
prompt** **Database Users Activities**
prompt**====================================================================================================**
set pagesize 100;
set line 200;
col username format a20;
col profile format a10;
col default_ts# format a18;
col temp_ts# format a10;
col created format a12;
Select username, account_status status, TO_CHAR(created, 'dd-MON-yyyy') created, profile,
default_tablespace default_ts#, temporary_tablespace temp_ts# from dba_users
where default_tablespace in ('SDH_HRMS_DBF', 'SDH_TIMS_DBF', 'SDH_SHTR_DBF', 'SDH_EDSS_DBF', 'SDH_FIN_DBF', 'SDH_FIN_DBF', 'USERS');
select obj.owner "USERNAME", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "Size_MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj,
(select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc,2 desc, 1;
col status format a20 heading "Session status";
col TOTAL format 9999999999 heading "# sessions";
select status, count(*) TOTAL from gv$session
where type='USER'
group by inst_id,status
order by 1,2;
set line 200;
col LOGON_TIME format a10;
col sid format 99;
col status format a8;
col process format a12;
col SCHEMANAME format a12;
col OSUSER format a15;
col machine format a25;
col SQL_TEXT format a75;
SELECT S.LOGON_TIME, s.sid, s.process, s.schemaname, s.osuser, s.MACHINE, a.sql_text
FROM v$session s, v$sqlarea a, v$process p
WHERE s.SQL_HASH_VALUE = a.HASH_VALUE
AND s.SQL_ADDRESS = a.ADDRESS
AND s.PADDR = p.ADDR
AND S.STATUS = 'INACTIVE';
prompt
prompt
prompt**==================================================================================================**
prompt** **Database Object Information**
prompt**==================================================================================================**
prompt
prompt List of Largest Object in Database:
prompt -----------------------------------------------------------------------**
set line 200;
col SEGMENT_NAME format a30;
col SEGMENT_TYPE format a10;
col BYTES format a15;
col TABLESPACE_NAME FORMAT A25;
SELECT * FROM (select SEGMENT_NAME, SEGMENT_TYPE TYPE, BYTES/1024/1024 SIZE_MB,
TABLESPACE_NAME from dba_segments order by 3 desc ) WHERE ROWNUM <= 5;
prompt
prompt Object Modified in last 7 days:
prompt -----------------------------------------------------------------------**
set line 200;
col owner format a15;
col object_name format a25;
col object_type format a15;
col last_modified format a20;
col created format a20;
col status format a10;
select owner, object_name, object_type, to_char(LAST_DDL_TIME,'MM/DD/YYYY HH24:MI:SS') last_modified,
to_char(CREATED,'MM/DD/YYYY HH24:MI:SS') created, status
from dba_objects
where (SYSDATE - LAST_DDL_TIME) < 7 and owner IN( 'HRMS', 'ORAFIN', 'HRTRAIN')
order by last_ddl_time DESC;
prompt
set pagesize 0;
SELECT 'Object Created in this Week: '|| count(1) from user_objects
where created >= sysdate -7;
prompt
prompt List of Invalid objects of database:
prompt -----------------------------------------------------------------------**
set pagesize 50;
Select owner "USERNAME", object_type, count(*) INVALID from dba_objects
where status='INVALID' group by owner, object_type;
set pagesize 50;
SELECT dt.owner, dt.table_name "Table Change > 10%",
ROUND ( (DELETES + UPDATES + INSERTS) / num_rows * 100) PERCENTAGE
FROM dba_tables dt, all_tab_modifications atm
WHERE dt.owner = atm.table_owner
AND dt.table_name = atm.table_name
AND num_rows > 0
AND ROUND ( (DELETES + UPDATES + INSERTS) / num_rows * 100) >= 10
ORDER BY 3 desc;
prompt
prompt Database Chained Rows Info:
prompt -----------------------------------------------------------------------**
col table_name format a25;
select owner, table_name, pct_free, pct_used, avg_row_len, num_rows, chain_cnt, trunc(chain_cnt/num_rows*100, 2) as perc
from dba_tables where owner not in ('SYS','SYSTEM')
and table_name not in (select table_name from dba_tab_columns
where data_type in ('RAW','LONG RAW') )
and chain_cnt > 0 order by chain_cnt desc;
prompt
prompt
prompt**==================================================================================================**
prompt** **RMAN Configuration and Backup**
prompt**==================================================================================================**
col "RMAN CONFIGURE PARAMETERS" format a100;
select 'CONFIGURE '||name ||' '|| value "RMAN CONFIGURE PARAMETERS"
from v$rman_configuration
order by conf#;
set line 200;
col "DEVIC" format a6;
col "L" format 9;
col "FIN:SS" format 9999;
SELECT DECODE(backup_type, 'L', 'Archived Logs', 'D', 'Datafile Full', 'I', 'Incremental')
backup_type, bp.tag "RMAN_BACKUP_TAG", device_type "DEVIC", DECODE( bs.controlfile_included, 'NO', null, bs.controlfile_included) controlfile,
(sp.spfile_included) spfile, sum(bs.incremental_level) "L", TO_CHAR(bs.start_time, 'dd/mm/yyyy HH24:MI:SS') start_time
, TO_CHAR(bs.completion_time, 'dd/mm/yyyy HH24:MI:SS') completion_time, sum(bs.elapsed_seconds) "FIN:SS"
FROM v$backup_set bs, (select distinct set_stamp, set_count, tag , device_type
from v$backup_piece
where status in ('A', 'X')) bp,
(select distinct set_stamp , set_count , 'YES' spfile_included
from v$backup_spfile) sp
WHERE bs.start_time > sysdate - 1
AND bs.set_stamp = bp.set_stamp
AND bs.set_count = bp.set_count
AND bs.set_stamp = sp.set_stamp (+)
AND bs.set_count = sp.set_count (+)
group by backup_type, bp.tag, device_type, bs.controlfile_included, pieces, sp.spfile_included,start_time, bs.completion_time
ORDER BY bs.start_time desc;
set line 200;
col "DBF_BACKUP_MB" format 999999.99;
col "ARC_BACKUP_MB" format 9999.99;
select trunc(completion_time) "BAK_DATE", sum(blocks*block_size)/1024/1024 "DBF_BACKUP_MB", (SELECT sum(blocks*block_size)/1024/1024 from v$backup_redolog
WHERE first_time > sysdate-1) "ARC_BACKUP_MB"
from v$backup_datafile
WHERE completion_time > sysdate - 1
group by trunc(completion_time)
order by 1 DESC;
col "Datafiles backed up within 24h" format a40;
col "Control backed up" format 999;
col "SPFiles backed up" format 999;
SELECT dbfiles||' out of '||numfiles||' datafiles backed up' "Datafiles backed up within 24h", cfiles "CFiles", spfiles "SPFiles"
FROM (select count(*) numfiles from v$datafile), (select count(*) dbfiles from v$backup_datafile a, v$datafile b
where a.file# = b.file# and a.completion_time > sysdate - 1), (select count(*) cfiles from v$backup_datafile
where file# = 0 and completion_time > sysdate - 1), (select count(*) spfiles from v$backup_spfile where completion_time > sysdate - 1);
prompt
prompt
prompt**===================================================================================================**
prompt** "Workload and I/O Statistics**
prompt**===================================================================================================**
prompt
prompt *** TOP SYSTEM Timed Events (Waits):
prompt*-----------------------------------------------------------------------**
COLUMN event FORMAT A40 HEADING "Wait Event" TRUNC
COLUMN time_waited FORMAT 9999999999999 HEADING "Time|Waited"
COLUMN wait_pct FORMAT 99.90 HEADING "Wait|(%)"
SELECT w.event, w.time_waited, round(w.time_waited/tw.twt*100,2) wait_pct
FROM gv$system_event w, (select inst_id, sum(time_waited) twt from gv$system_event
where time_waited>0
AND event NOT IN ('Null event', 'client message', 'rdbms ipc reply', 'smon timer', 'rdbms ipc message', 'PX Idle Wait', 'PL/SQL lock timer', 'file open', 'pmon timer', 'WMON goes to sleep',
'virtual circuit status', 'dispatcher timer', 'SQL*Net message from client', 'parallel query dequeue wait', 'pipe get')
group by inst_id
) tw
WHERE w.inst_id = tw.inst_id and w.time_waited>0
and round(w.time_waited/tw.twt*100,2) > 1
and w.event NOT IN ('Null event', 'client message', 'rdbms ipc reply', 'smon timer', 'rdbms ipc message', 'PX Idle Wait', 'PL/SQL lock timer', 'file open', 'pmon timer', 'WMON goes to sleep',
'virtual circuit status', 'dispatcher timer', 'SQL*Net message from client', 'parallel query dequeue wait', 'pipe get')
ORDER by 1;
prompt
prompt *** Most buffer gets (TOP 5):
prompt*-----------------------------------------------------------------------**
col object_type format a10;
col object_name format a30;
col statistic_name format a15;
col value format 99999999999;
SELECT * from (
SELECT object_type, object_name, statistic_name,VALUE
FROM v$segment_statistics
WHERE statistic_name LIKE '%logi%' AND VALUE > 50
ORDER BY 4 DESC
) where rownum < 6;
prompt
prompt *** Most I/O operation (TOP 5):
prompt*-----------------------------------------------------------------------**
col object_type format a15 heading "Object Type"
col object_name format a27 heading "Object Name"
col statistic_name format a22 heading "Statistic Name"
col value format 99999999999 heading "Value"
SELECT * from (
SELECT object_type, object_name, statistic_name, VALUE
FROM v$segment_statistics
WHERE statistic_name LIKE '%phys%' AND VALUE > 50
ORDER BY 4 DESC
) where rownum < 6;
prompt
prompt *** Most I/O operation for particualr Query:
prompt*-----------------------------------------------------------------------**
col sql_text format a60;
col reads_per_exe format 99999999 heading 'reads|per_exe';
col "exe" format 99999;
col "sorts" format 99999;
col buffer_gets heading 'buffer|gets';
col disk_reads heading 'disk|reads';
SELECT * FROM (SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_exe,
a.buffer_gets, a.disk_reads, a.executions "exe", a.sorts "sorts", a.address "address"
FROM v$sqlarea a
ORDER BY 2 DESC)
WHERE rownum <= 5;
prompt
prompt Monitoring Full Table Scan of Database:
prompt*-----------------------------------------------------------------------**
set line 200;
col "Full Table Scan" format a30;
SELECT name "Full Table Scan", value FROM v$sysstat
WHERE name LIKE '%table scans %'
ORDER BY name;
prompt
Prompt* Review the query causing high amount of buffer_gets and create additional index to avoid full table scan.
prompt
prompt Monitor TOP CPU Usage and Logical I/O Process:
prompt*-----------------------------------------------------------------------**
col resource_name heading "Resource|Name";
col current_utilization heading "current|utiliz";
col max_utilization heading "Max|utiliz";
col initial_allocation heading "Initial|Alloc";
col limit_value heading "Limit|Value";
select resource_name, current_utilization, max_utilization, initial_allocation, limit_value
from v$resource_limit where resource_name in ('processes','sessions', 'transactions', 'max_rollback_segments');
col name format a30;
select * from (select a.sid, c.username, c.osuser, c.machine, logon_time, b.name, a.value
from v$sesstat a, v$statname b, v$session c
where a.STATISTIC# = b.STATISTIC#
and a.sid = c.sid
and b.name like '%CPU used by this session%'
order by a.value desc)
where rownum < 5;
select 'top logical i/o process', sid, username, total_user_io amt_used, round(100 * total_user_io/total_io,2) pct_used
from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_io
from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid
and c.name in ('consistent gets', 'db block gets') and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by b.sid, nvl(b.username, p.name) order by 3 desc),
(select sum(value) total_io from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid and c.name in ('consistent gets', 'db block gets'))
where rownum < 2
union all
select 'top memory process', sid, username, total_user_mem, round(100 * total_user_mem/total_mem,2)
from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_mem
from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr
and b.sid = a.sid and c.name in ('session pga memory', 'session uga memory')
and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by b.sid, nvl(b.username, p.name) order by 3 desc),
(select sum(value) total_mem from v$statname c, v$sesstat a
where a.statistic# = c.statistic# and c.name in ('session pga memory', 'session uga memory'))
where rownum < 2
union all
select 'top cpu process', sid, username, total_user_cpu, round(100 * total_user_cpu/greatest(total_cpu,1),2)
from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_cpu
from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid
and c.name = 'CPU used by this session' and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by b.sid, nvl(b.username, p.name) order by 3 desc),
(select sum(value) total_cpu from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid
and c.name = 'CPU used by this session') where rownum < 2;
prompt
prompt Monitoring Current Running Long Job in Database:
prompt*-----------------------------------------------------------------------**
set line 200;
col opname format a30;
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM V$SESSION_LONGOPS
WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK
order by 1;
prompt
prompt Monitoring Object locking:
prompt*-----------------------------------------------------------------------**
set line 200;
col username format a15;
col lock_type format a10;
col osuser format a15;
col owner format a10;
col object_name format a20;
SELECT s.sid, s. serial#, s.username, l.lock_type, s.osuser, s.machine,
o.owner, o.object_name, ROUND(w.seconds_in_wait/60, 2) "Wait"
FROM v$session s, dba_locks l, dba_objects o, v$session_wait w
WHERE s.sid = l.session_id
AND l.lock_type IN ('DML','DDL')AND l.lock_id1 = o.object_id
AND l.session_id = w.sid
ORDER BY s.sid;
prompt
prompt Monitor DB Corruption or Need of Recovery:
prompt*-----------------------------------------------------------------------**
set line 200;
SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name, d.STATUS,
r.ERROR, r.CHANGE#, r.TIME FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
WHERE t.TS# = d.TS# AND d.FILE# = r.FILE#;
set linesize 200
col name format a45 heading "Datafile Name";
col "Read Time(ms)" heading 'Read|Time(ms)';
col "Write Time(ms)" heading 'write|Time(ms)';
col "Avg_Time" heading 'Avg|Time(ms)';
select name,PHYRDS,PHYWRTS,READTIM "Read Time(ms)",WRITETIM "Write Time(ms)",AVGIOTIM "Avg_Time"
from v$filestat, v$datafile where v$filestat.file#=v$datafile.file#;
set feedback on
prompt
rem -----------------------------------------------------------------------
rem Filename: DB_Health_Rep.sql
rem Purpose: Database Statistics and Health Report
rem -----------------------------------------------------------------------
prompt Recommendations:
prompt ====================================================================================================
prompt* SQL Cache Hit rate ratio should be above 90%, if not then increase the Shared Pool Size.
prompt* Dict Cache Hit rate ratio should be above 85%, if not then increase the Shared Pool Size.
prompt* Buffer Cache Hit rate ratio should be above 90%, if not then increase the DB Block Buffer value.
prompt* Redo Log space requests should be less than 0.5% of redo entries, if not then increase log buffer.
prompt* Redo Log space wait time should be near to 0.
prompt
set serveroutput ON
DECLARE
libcac number(10,2);
rowcac number(10,2);
bufcac number(10,2);
redlog number(10,2);
redoent number;
redowaittime number;
BEGIN
select value into redlog from v$sysstat where name = 'redo log space requests';
select value into redoent from v$sysstat where name = 'redo entries';
select value into redowaittime from v$sysstat where name = 'redo log space wait time';
select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;
select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache;
select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac
from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
where cur.statistic# = ncu.statistic#
and ncu.name = 'db block gets'
and con.statistic# = nco.statistic#
and nco.name = 'consistent gets'
and phys.statistic# = nph.statistic#
and nph.name = 'physical reads';
dbms_output.put_line('CACHE HIT RATE');
dbms_output.put_line('********************');
dbms_output.put_line('SQL Cache Hit rate = '||libcac);
dbms_output.put_line('Dict Cache Hit rate = '||rowcac);
dbms_output.put_line('Buffer Cache Hit rate = '||bufcac);
dbms_output.put_line('Redo Log space requests = '||redlog);
dbms_output.put_line('Redo Entries = '||redoent);
dbms_output.put_line('Redo log space wait time = '||redowaittime);
if
libcac < 90 then dbms_output.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
END IF;
if
rowcac < 85 then dbms_output.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
END IF;
if
bufcac < 90 then dbms_output.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');
END IF;
if
redlog > 1000000 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!');
END IF;
END;
/
spool off
exit
Output like:
set feedback off;
set pagesize 0;
Select (SELECT NAME FROM V$DATABASE) ||'_Oracle_Database_Health_Report: '||to_char(sysdate,'Mon dd yyyy hh24:mi:ss') "TIMESTAMP" from dual;
prompt**=====================================================================================================**
prompt** **Database Current Status**
prompt**=====================================================================================================**
set pagesize 50;
set line 300;
col HOST_NAME FORMAT a12;
col "HOST_ADDRESS" FORMAT a15;
col RESETLOGS_TIME FORMAT a12;
col "DB RAC?" FORMAT A8;
col days format 9999;
select name INSTANCE,HOST_NAME, UTL_INADDR.GET_HOST_ADDRESS "HOST_ADDRESS",
LOGINS, archiver,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB_UP_TIME", RESETLOGS_TIME "RESET_TIME", FLOOR(sysdate-startup_time) days,
(select DECODE(vp1.value,'TRUE','Yes ('|| decode(vp1.value,'TRUE',' instances: '||vp2.value)||')','No')
from v$instance,
(select value from v$parameter
where name like 'cluster_database'
) vp1,
(select value from v$parameter
where name like 'cluster_database_instances'
) vp2) "DB RAC?"
from v$database,gv$instance;
INSTANCE HOST_NAME HOST_ADDRESS LOGINS ARCHIVE DB_UP_TIME RESET_TIM DAYS DB RAC?
--------- ------------ --------------- ---------- ------- -------------------- --------- ----- -------------------------------------------------------
PROD DEKHATAI009 fe80::6c9d:91df ALLOWED STARTED 26-FEB-2016 09:53:17 02-FEB-16 0 No
SQL> SELECT BANNER "VERSION" FROM V$VERSION;
VERSION
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
col "Database Details" format a60;
SELECT
1 sort1,' DBNAME:'||name ||chr(9)||'DBID:'||dbid ||chr(9)|| 'Created:'||to_char(created, 'dd/mm/yyyy hh24:mi:ss') ||chr(10)||
' Log mode:'||log_mode || decode (log_mode,'ARCHIVELOG','',' !!!') ||chr(9)||'Forcelogging:'||force_logging||chr(9)||'Open mode:'||open_mode||chr(10)||
' Remote archiving:'||remote_archive||chr(9)||'Database role:'||database_role "Database Details"
FROM v$database
UNION
SELECT 2 sort1,'Datafiles: '||trim(TO_CHAR(COUNT(*),'9,990'))||chr(9)||'Datafile size(Gb): '||trim(TO_CHAR(SUM(bytes)/1073741824, '9,990'))
FROM v$datafile
UNION
SELECT 3 sort1,'Tempfiles: '||trim(TO_CHAR(COUNT(*),'9,990'))||chr(9)||'Tempfile size(Gb): '||trim(TO_CHAR(SUM(bytes)/1073741824, '9,990'))
FROM v$tempfile
UNION
SELECT 4 sort1,'Segment size (Gb): '||trim(TO_CHAR(SUM(bytes)/1073741824, '9,990'))
FROM dba_segments
UNION
SELECT 5 sort1,'Tables/Indexes: '|| trim(TO_CHAR(SUM(DECODE(type#, 2, 1, 0)), '999,990'))||'/'|| trim(TO_CHAR(SUM(DECODE(type#, 1, 1, 0)), '999,990'))
FROM sys.obj$
WHERE owner# <> 0
UNION
SELECT 6 sort1,'Total DB Users: '||trim( TO_CHAR(COUNT(*), '9,990'))
FROM sys.user$ WHERE
type# = 1
UNION
SELECT 7 sort1,'Online Sessions: '||trim( TO_CHAR(COUNT(*), '9,990'))
FROM gv$session
WHERE type='USER'
UNION
SELECT 8 sort1,'Active Sessions: '||trim( TO_CHAR(COUNT(*), '9,990'))
FROM gv$session
WHERE type='USER' and status = 'ACTIVE'
UNION
SELECT 9 sort1,'Session highwater: '|| trim(TO_CHAR(sessions_highwater, '9,990'))
FROM v$license
UNION
SELECT 10 sort1,'SGA (Mb): '||trim(TO_CHAR(SUM(value)/1048576, '99,990.99'))
FROM v$sga;
SORT1 Database Details
---------- ------------------------------------------------------------
1 DBNAME:DEV DBID:170256562 Created:15/08/2013 22:30:32
Log mode:ARCHIVELOG Forcelogging:YES Open mode:READ WRITE
Remote archiving:ENABLED Database role:PRIMARY
2 Datafiles: 33 Datafile size(Gb): 305
3 Tempfiles: 1 Tempfile size(Gb): 20
4 Segment size (Gb): 219
5 Tables/Indexes: 6,683/8,977
6 Total DB Users: 104
7 Online Sessions: 93
8 Active Sessions: 18
9 Session highwater: 124
10 SGA (Mb): 8,155.42
prompt**====================================================================================================**
prompt** **Database Parameters Details**
prompt**====================================================================================================**
set pagesize 600;
set line 200;
column "database parameter" format a40;
column "VALUE" format a40;
select name "Database Parameter",value from v$spparameter
where isspecified='TRUE' and NAME != 'control_files';
Database Parameter VALUE
---------------------------------------- ----------------------------------------
processes 500
sessions 772
timed_statistics TRUE
event
streams_pool_size 167772160
nls_language AMERICAN
nls_territory AMERICA
nls_sort BINARY
nls_date_format DD-MON-RR
nls_numeric_characters .,
nls_calendar GREGORIAN
memory_target 6442450944
memory_max_target 8589934592
db_file_name_convert /u02/oradata/SIT1
db_file_name_convert /u06/oradata/DEV
log_file_name_convert /u02/oradata/SIT1
log_file_name_convert /u06/oradata/DEV
control_file_record_keep_time 37
db_block_size 8192
compatible 11.2.0.0.0
log_archive_dest_1 LOCATION=/u05/archivelogs/DEV/
log_archive_format arch_%t_%s_%r.arc
log_buffer 9347072
log_checkpoint_interval 100000
log_checkpoint_timeout 3600
db_files 500
db_recovery_file_dest /u04/flashback/DEV
db_recovery_file_dest_size 5368709120
log_checkpoints_to_alert TRUE
dml_locks 10000
undo_management AUTO
undo_tablespace UNDO_TS
sec_case_sensitive_logon FALSE
remote_login_passwordfile EXCLUSIVE
audit_sys_operations TRUE
dispatchers (PROTOCOL=TCP) (SERVICE=DEVXDB)
local_listener (ADDRESS=(PROTOCOL=TCP)(HOST=devcrpdb1.t
hesource.ca)(PORT=1524))
session_cached_cursors 900
utl_file_dir /temp
plsql_warnings DISABLE:ALL
job_queue_processes 3
parallel_max_servers 32
result_cache_max_size 16121856
audit_trail DB
db_name DEV
open_cursors 900
os_authent_prefix
optimizer_mode ALL_ROWS
_complex_view_merging FALSE
_unnest_subquery FALSE
query_rewrite_enabled TRUE
workarea_size_policy AUTO
optimizer_dynamic_sampling 2
skip_unusable_indexes TRUE
sec_protocol_error_trace_action LOG
diagnostic_dest /u01/app/oracle
max_dump_file_size 20480
SET FEEDBACK OFF;
set pagesize 600;
set line 200;
column "NLS_Parameter" format a40;
column "VALUE" format a40;
Select parameter "NLS_Parameter", value from nls_database_parameters;
NLS_Parameter VALUE
---------------------------------------- ----------------------------------------
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_RDBMS_VERSION 11.2.0.2.0
prompt
prompt
prompt**===================================================================================================**
prompt** **Tunning Database SGA/PGA**
prompt**===================================================================================================**
prompt
set pagesize 0;
SELECT 'SGA MAX Size in MB: '|| trunc(SUM(VALUE)/1024/1024, 2) "SGA_MAX_MB" FROM V$SGA;
SGA MAX Size in MB: 8155.41
set pagesize 50;
set line 200;
column "SGA Pool"format a33;
col "m_bytes" format 999999.99;
select pool "SGA Pool", m_bytes from ( select pool, to_char( trunc(sum(bytes)/1024/1024,2), '99999.99' ) as M_bytes
from v$sgastat
where pool is not null group by pool
union
select name as pool, to_char( trunc(bytes/1024/1024,3), '99999.99' ) as M_bytes
from v$sgastat
where pool is null order by 2 desc
) UNION ALL
select 'TOTAL' as pool, to_char( trunc(sum(bytes)/1024/1024,3), '99999.99' ) from v$sgastat;
SGA Pool M_BYTES
--------------------------------- ---------------------------
shared pool 2688.00
buffer_cache 1392.00
streams pool 160.00
log_buffer 25.28
java pool 16.00
large pool 16.00
fixed_sga 2.13
TOTAL 4299.41
Select round(tot.bytes /1024/1024 ,2) sga_total, round(used.bytes /1024/1024 ,2) used_mb, round(free.bytes /1024/1024 ,2) free_mb
from (select sum(bytes) bytes from v$sgastat where name != 'free memory') used,
(select sum(bytes) bytes from v$sgastat where name = 'free memory') free,
(select sum(bytes) bytes from v$sgastat) tot;
SGA_TOTAL USED_MB FREE_MB
---------- ---------- ----------
4299.41 3576.71 722.71
select pool, round(sgasize/1024/1024,2) "SGA_TARGET",
round(bytes/1024/1024,2) "FREE_MB",
round(bytes/sgasize*100, 2) "%FREE"
from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
where f.name = 'free memory';
POOL SGA_TARGET FREE_MB %FREE
------------------------------------ ---------- ---------- ----------
shared pool 4299.41 546.48 12.71
large pool 4299.41 8.19 .19
java pool 4299.41 9.5 .22
streams pool 4299.41 158.93 3.7
prompt
prompt
prompt Tunning Shared Pool Size:
prompt*-----------------------------------------------------------------------**
col "Data Dict. Gets" heading Data_Dict.|Gets;
col "Data Dict. Cache Misses" heading Dict._Cache|Misses;
col "Data Dict Cache Hit Ratio" heading Dict._Cache|Hit_Ratio;
col "% Missed" heading Missed|%;
SELECT SUM(gets) "Data Dict. Gets", SUM(getmisses) "Data Dict. Cache Misses"
, TRUNC((1-(sum(getmisses)/SUM(gets)))*100, 2) "Data Dict Cache Hit Ratio"
, TRUNC(SUM(getmisses)*100/SUM(gets), 2) "% Missed"
FROM v$rowcache;
Data_Dict. Dict._Cache Dict._Cache Missed
Gets Misses Hit_Ratio %
---------- ----------- ----------- ----------
4675279774 8410169 99.82 .17
prompt
Prompt* The Dict. Cache Hit% shuold be > 90% and misses% should be < 15%. If not consider increase SHARED_POOL_SIZE.
col "Cache Misses" heading Cache|Misses;
col "Library Cache Hit Ratio" heading Lib._Cache|Hit_Ratio;
col "% Missed" heading Missed|%;
SELECT SUM(pins) "Executions", SUM(reloads) "Cache Misses"
, TRUNC((1-(SUM(reloads)/SUM(pins)))*100, 2) "Library Cache Hit Ratio"
, ROUND(SUM(reloads)*100/SUM(pins)) "% Missed"
FROM v$librarycache;
Cache Lib._Cache Missed
Executions Misses Hit_Ratio %
---------- ---------- ---------- ----------
4354872178 1572780 99.96 0
prompt
Prompt* The Lib. Cache Hit% shuold be > 90% and misses% should be < 1%. If not consider increase SHARED_POOL_SIZE.
set pagesize 25;
col "Tot SQL since startup" format a25;
col "SQL executing now" format a17;
SELECT TO_CHAR(SUM(executions)) "Tot SQL since startup", TO_CHAR(SUM(users_executing)) "SQL executing now"
FROM v$sqlarea;
Tot SQL since startup SQL executing now
------------------------- -----------------
830014311 13
set pagesize 25;
col "Namespace" heading name|space;
col "Hit Ratio" heading Hit|Ratio;
col "Pin Hit Ratio" heading Pin_Hit|Ratio;
col "Invalidations" heading invali|dations;
SELECT namespace "Namespace", TRUNC(gethitratio*100) "Hit Ratio",
TRUNC(pinhitratio*100) "Pin Hit Ratio", reloads "Reloads", invalidations "Invalidations"
FROM v$librarycache;
prompt
prompt* GETHITRATIO and PINHITRATIO should be more than 90%.
prompt* If RELOADS > 0 then'cursor_space_for_time' Parameter do not set to 'TRUE'
prompt* More of Invalid object in namespace will cause more reloads.
set line 200;
col "NAME" format a30;
col "VALUE" format a12;
select p.name "NAME", a.free_space, p.value "VALUE", trunc(a.free_space/p.value, 2) "FREE%", requests, request_misses req_misses
from v$parameter p, v$shared_pool_reserved a
where p.name = 'shared_pool_reserved_size';
NAME FREE_SPACE VALUE FREE% REQUESTS REQ_MISSES
------------------------------ ---------- ------------ ---------- ---------- ----------
shared_pool_reserved_size 118816624 123312537 .96 0 0
prompt
Prompt* %FREE should be > 0.5, request_failures,request_misses=0 or near 0. If not consider increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE.
prompt
prompt
prompt Tunning Buffer Cache:
prompt -----------------------------------------------------------------------**
SELECT TRUNC( ( 1 - ( SUM(decode(name,'physical reads',value,0)) / ( SUM(DECODE(name,'db block gets',value,0))
+ (SUM(DECODE(name,'consistent gets',value,0))) )) ) * 100 ) "Buffer Hit Ratio"
FROM v$sysstat;
Buffer Hit Ratio
----------------
93
prompt* The Buffer Cache Hit% should be >90%. If not and the shared pool hit ratio is good consider increase DB_CACHE_SIZE.
set line 200;
col event format a20;
select event, total_waits, time_waited
from v$system_event
where event in ('buffer busy waits');
EVENT TOTAL_WAITS TIME_WAITED
-------------------- ----------- -----------
buffer busy waits 412337 114297
prompt
prompt* Check for waits to find a free buffer in the buffer cache and Check if the I/O system is slow.
prompt* Consider increase the size of the buffer cache if it is too small. Consider increase the number of DBWR process if the buffer cache is properly sized.
prompt
prompt
prompt Tunning Redolog Buffer:
prompt -----------------------------------------------------------------------**
col "redolog space request" heading redolog_space|request;
col "redolog space wait time" heading redolog_space|wait_time;
col "Redolog space ratio" heading redolog_space|ratio;
Select e. value "redolog space request", s.value "redolog space wait time", Round(e.value/s.value,2) "Redolog space ratio"
From v$sysstat s, v$sysstat e
Where s.name = 'redo log space requests'
and e.name = 'redo entries';
redolog_space redolog_space redolog_space
request wait_time ratio
------------- ------------- -------------
558458807 1348 414286.95
prompt
prompt
prompt Tunning PGA Aggregate Target:
prompt -----------------------------------------------------------------------**
set pagesize 600;
set line 200;
column PGA_Component format a40;
column value format 999999999999;
select name "PGA_Component", value from v$pgastat;
PGA_Component VALUE
---------------------------------------- -------------
aggregate PGA target parameter 2248146944
aggregate PGA auto target 1710084096
global memory bound 224808960
total PGA inuse 348168192
total PGA allocated 485531648
maximum PGA allocated 1574793216
total freeable PGA memory 95682560
process count 120
max processes count 156
PGA memory freed back to OS #############
total PGA used for auto workareas 0
maximum PGA used for auto workareas 905170944
total PGA used for manual workareas 0
maximum PGA used for manual workareas 255836160
over allocation count 0
bytes processed #############
extra bytes read/written 70793925632
cache hit percentage 97
recompute count (total) 8474732
Select count(*) "Total No. of Process" from v$process;
Total No. of Process
--------------------
123
set line 200;
column "PGA Target" format a40;
column VALUE_MB format 9999999999.99
SELECT NAME "PGA Target", VALUE/1024/1024 VALUE_MB
FROM V$PGASTAT
WHERE NAME IN ('aggregate PGA target parameter',
'total PGA allocated',
'total PGA inuse')
union
SELECT NAME, VALUE
FROM V$PGASTAT
WHERE NAME IN ('over allocation count');
PGA Target VALUE_MB
---------------------------------------- --------------
aggregate PGA target parameter 2144.00
over allocation count .00
total PGA allocated 463.94
total PGA inuse 332.86
set line 200;
column "PGA_Work_Pass" format a40;
column "PER" format 999;
select name "PGA_Work_Pass", cnt, decode(total, 0, 0, round(cnt*100/total)) per
from (select name, value cnt, (sum(value) over()) total
from v$sysstat where name like 'workarea exec%'
);
PGA_Work_Pass CNT PER
---------------------------------------- ---------- ----
workarea executions - optimal 227377300 100
workarea executions - onepass 232 0
workarea executions - multipass 0 0
prompt
prompt
prompt**===================================================================================================**
prompt** **Tablespace/CRD File Information**
prompt**===================================================================================================**
col "Database Size" format a15;
col "Free space" format a15;
select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",
round(free.p / 1024 / 1024/1024) || ' GB' "Free space"
from (select bytes from v$datafile
union all select bytes from v$tempfile
union all select bytes from v$log) used,
(select sum(bytes) as p from dba_free_space) free
group by free.p;
Database Size Free space
--------------- ---------------
328 GB 92 GB
SELECT a.tablespace_name tablespace_name,
ROUND(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
-- ROUND(NVL(b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
ROUND((a.bytes_alloc - NVL(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used,
ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_Free,
(case when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)<=0
then 'Immediate action required!'
when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)<5
then 'Critical (<5% free)'
when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)<15
then 'Warning (<15% free)'
when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)<25
then 'Warning (<25% free)'
when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)>60
then 'Waste of space? (>60% free)'
else 'OK'
end) msg
FROM ( SELECT f.tablespace_name,
SUM(f.bytes) bytes_alloc,
SUM(DECODE(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
FROM DBA_DATA_FILES f
GROUP BY tablespace_name) a,
( SELECT f.tablespace_name,
SUM(f.bytes) bytes_free
FROM DBA_FREE_SPACE f
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name (+)
UNION
SELECT h.tablespace_name,
ROUND(SUM(h.bytes_free + h.bytes_used) / 1048576, 2),
-- ROUND(SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / 1048576, 2),
ROUND(SUM(NVL(p.bytes_used, 0))/ 1048576, 2),
ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2),
(case when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)<=0 then 'Immediate action required!'
when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)<5 then 'Critical (<5% free)'
when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)<15 then 'Warning (<15% free)'
when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)<25 then 'Warning (<25% free)'
when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)>60 then 'Waste of space? (>60% free)'
else 'OK'
end) msg
FROM sys.V_$TEMP_SPACE_HEADER h, sys.V_$TEMP_EXTENT_POOL p
WHERE p.file_id(+) = h.file_id
AND p.tablespace_name(+) = h.tablespace_name
GROUP BY h.tablespace_name
ORDER BY 1;
TABLESPACE_NAME MEGS_ALLOC MEGS_USED PCT_FREE
------------------------------------------------------------------------------------------ ---------- ---------- ----------
MSG
---------------------------------------------------------------------------------
AQJMS 263 181.38 31.04
OK
LOB_DATA 108 28.44 73.67
Waste of space? (>60% free)
ODI_MASTER 26 12.06 53.61
OK
ODI_WORK 53 21.63 59.2
OK
ORABPEL 945 641.38 32.13
OK
RETEK_DATA 146463.98 117269.48 19.93
Warning (<25% free)
RETEK_INDEX 69478 56067.19 19.3
Warning (<25% free)
RETEK_PART01_D 8602 7087.94 17.6
Warning (<25% free)
RETEK_PART01_I 3072 1 99.97
Waste of space? (>60% free)
RETEK_PART02_D 5376 3624.38 32.58
OK
RETEK_PART02_I 3072 1 99.97
Waste of space? (>60% free)
RETEK_PART03_D 4838 3259.63 32.62
OK
RETEK_PART03_I 3072 1 99.97
Waste of space? (>60% free)
RETEK_PART04_D 5645 4364.13 22.69
Warning (<25% free)
RETEK_PART04_I 3072 1 99.97
Waste of space? (>60% free)
RETEK_PART05_D 3763 2813.81 25.22
OK
RETEK_PART05_I 3072 1 99.97
Waste of space? (>60% free)
RETEK_PART06_D 6195 4660.06 24.78
Warning (<25% free)
RETEK_PART06_I 3072 1 99.97
Waste of space? (>60% free)
SYSAUX 2688 1966.75 26.83
OK
SYSTEM 2688 1782.88 33.67
OK
TEMP 20480 0 100
Waste of space? (>60% free)
TOOLS 2000 1241.38 37.93
OK
UNDO_TS 21000 8122.5 61.32
Waste of space? (>60% free)
USERS 13978 5207.13 62.75
Waste of space? (>60% free)
set linesize 200
col file_name format a50 heading "Datafile Name"
col allocated_mb format 999999.99;
col used_mob format 999999.99;
col free_mb format 999999.99;
col tablespace_name format a20;
SELECT SUBSTR (df.NAME, 1, 40) file_name, dfs. tablespace_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb, NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_mb, c.autoextensible
FROM v$datafile df, dba_free_space dfs, DBA_DATA_FILES c
WHERE df.file# = dfs.file_id(+) AND df.file# = c.FILE_ID
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes, dfs.tablespace_name, c.autoextensible
ORDER BY file_name;
Datafile Name TABLESPACE_NAME ALLOCATED_MB USED_MB FREE_MB AUTOEXTEN
-------------------------------------------------- -------------------- ------------ ---------- ---------- ---------
/u06/oradata/DEV/aqjms01.dbf AQJMS 263.00 181.375 81.63 NO
/u06/oradata/DEV/lob_data01.dbf LOB_DATA 108.00 28.4375 79.56 NO
/u06/oradata/DEV/odi_master01.dbf ODI_MASTER 26.00 12.0625 13.94 NO
/u06/oradata/DEV/odi_work01.dbf ODI_WORK 53.00 21.625 31.38 NO
/u06/oradata/DEV/orabpel.dbf ORABPEL 945.00 641.375 303.63 NO
/u06/oradata/DEV/retek_data01.dbf RETEK_DATA 30720.00 30674 46.00 NO
/u06/oradata/DEV/retek_data02.dbf RETEK_DATA 32767.98 32723.7969 44.19 NO
/u06/oradata/DEV/retek_data03.dbf RETEK_DATA 32256.00 32180.75 75.25 NO
/u06/oradata/DEV/retek_data04.dbf RETEK_DATA 30720.00 21017.9375 9702.06 NO
/u06/oradata/DEV/retek_data05.dbf RETEK_DATA 20000.00 673 19327.00 NO
/u06/oradata/DEV/retek_index01.dbf RETEK_INDEX 25600.00 23158.6875 2441.31 NO
SELECT TO_CHAR(creation_time, 'RRRR Month') "Year/Month",
round(SUM(bytes)/1024/1024/1024) "Datafile Growth Rate in GB"
FROM sys.v_$datafile
WHERE creation_time < sysdate
GROUP BY TO_CHAR(creation_time, 'RRRR Month');
Year/Month Datafile Growth Rate in GB
--------------------------------------------------------------------------------------------------------------------------- --------------------------
2012 December 18
2011 October 2
2011 June 122
2015 October 20
2014 March 30
2013 May 32
2012 October 3
2013 March 1
2011 December 25
2013 August 21
2013 July 1
2012 February 32
TTI off
prompt
prompt** Report Tablespace < 10% free space**
prompt** -----------------------------------------------------------------------**
set pagesize 300;
set linesize 100;
column tablespace_name format a15 heading Tablespace;
column sumb format 999,999,999;
column extents format 9999;
column bytes format 999,999,999,999;
column largest format 999,999,999,999;
column Tot_Size format 999,999 Heading "Total Size(Mb)";
column Tot_Free format 999,999,999 heading "Total Free(Kb)";
column Pct_Free format 999.99 heading "% Free";
column Max_Free format 999,999,999 heading "Max Free(Kb)";
column Min_Add format 999,999,999 heading "Min space add (MB)";
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from (select tablespace_name,0 tots,sum(bytes) sumb
from sys.dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0 from
sys.dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;
Tablespace Total Size(Mb) Total Free(Kb) % Free Min space add (MB)
--------------- -------------- -------------- ------- ------------------
RETEK_INDEX 197,160 17,360,000 8.60 14,849
RETEK_DATA 369,928 36,797,696 9.71 23,005
set pagesize 50;
col name format A60 heading "Control Files";
select name from sys.v_$controlfile;
or
select name from v$controlfile;
Control Files
------------------------------------------------------------
/u06/oradata/DEV/control01.ctl
/u02/oradata/DEV/control02.ctl
set pagesize 0;
select
(case
when count(*)>3 then '+ '||count(*)||' times detected when log switches occured more than 1 log per 5 minutes.'||chr(10)||
'+ You may consider to increase the redo log size.'
else '+ Redo log size: OK'
end) "Redolog Size Status"
from (
select trunc(FIRST_TIME,'HH') Week, count(*) arch_no, trunc(10*count(*)/60) archpermin
from v$log_history
group by trunc(FIRST_TIME,'HH')
having trunc(5*count(*)/60)>1
);
+ Redo log size: OK
set pagesize 0;
select (case when sync_waits = 0 then '+ Waits for log file sync not detected: log_buffer is OK'
else '+ Waits for log file sync detected ('||sync_waits||' times): Consider to increase the log_buffer'
end) "Log Buffer Status"
from ( select decode( sum(w.total_waits), 0, 0,
nvl(100 * sum(l.total_waits) / sum(w.total_waits), 0)
) sync_waits
from sys.v_$bgprocess b, sys.v_$session s, sys.v_$session_event l, sys.v_$session_event w
where
b.name like 'DBW_' and s.paddr = b.paddr and
l.sid = s.sid and l.event = 'log file sync' and
w.sid = s.sid and w.event = 'db file parallel write'
);
+ Waits for log file sync not detected: log_buffer is OK
prompt
prompt Last 24hrs Log switch Report:
prompt -----------------------------------------------------------------------**
set pagesize 0;
select '+ Daily (max) : '||max(no)||' switches. Size: '||round((max(no) * max(logsize )) ,2)||'MB'||chr(10)||
'+ Daily (avg) : '||trunc(avg(no))||' switches. Size: '||round((avg(no) * max(logsize )) ,2)||'MB'||chr(10)||
'+ Daily (min) : '||min(no)||' switches. Size: '||round((min(no) * max(logsize )) ,2)||'MB'
from (select trunc(FIRST_TIME,'DD'), count(*) no
from v$log_history
where FIRST_TIME > sysdate - 31
group by trunc(FIRST_TIME,'DD')),
(select max(bytes/1024/1024) logsize from v$log);
+ Daily (max) : 228 switches. Size: 233472MB
+ Daily (avg) : 115 switches. Size: 117984MB
+ Daily (min) : 33 switches. Size: 33792MB
set pagesize 0;
select '+ Weekly (max): '||max(no)||' switches. Size: '||round((max(no) * max(logsize )) ,2)||'MB'||chr(10)||
'+ Weekly (avg): '||trunc(avg(no))||' switches. Size: '||round((avg(no) * max(logsize )) ,2)||'MB'||chr(10)||
'+ Weekly (min): '||min(no)||' switches. Size: '||round((min(no) * max(logsize )) ,2)||'MB'
from (select trunc(FIRST_TIME,'WW'), count(*) no
from v$log_history
where FIRST_TIME > sysdate - 31
group by trunc(FIRST_TIME,'WW')),
(select max(bytes/1024/1024) logsize from v$log);
+ Weekly (max): 42 switches. Size: 43008MB
+ Weekly (avg): 35 switches. Size: 36454.4MB
+ Weekly (min): 14 switches. Size: 14336MB
set pagesize 25;
select trunc(completion_time) log_date, count(*) log_switch, round((sum(blocks*block_size) / 1024 / 1024)) "SIZE_MB"
from v$archived_log
WHERE completion_time > (sysdate-1) - 1/24 AND DEST_ID = 1
group by trunc(completion_time)
order by 1 desc;
LOG_DATE LOG_SWITCH SIZE_MB
--------------- ---------- ----------
26-FEB-16 1 13
25-FEB-16 4 203
prompt
prompt
prompt**====================================================================================================**
prompt** **Database Users Activities**
prompt**====================================================================================================**
set pagesize 100;
set line 200;
col username format a20;
col profile format a10;
col default_ts# format a18;
col temp_ts# format a10;
col created format a12;
Select username, account_status status, TO_CHAR(created, 'dd-MON-yyyy') created, profile,
default_tablespace default_ts#, temporary_tablespace temp_ts# from dba_users
where default_tablespace in ('SDH_HRMS_DBF', 'SDH_TIMS_DBF', 'SDH_SHTR_DBF', 'SDH_EDSS_DBF', 'SDH_FIN_DBF', 'SDH_FIN_DBF', 'USERS');
USERNAME STATUS CREATED PROFILE DEFAULT_TS# TEMP_TS#
-------------------- ---------- ------------ ---------- ------------------ ---------
ORAESB OPEN 16-SEP-2013 DEFAULT USERS TEMP
ORAWSM OPEN 16-SEP-2013 DEFAULT USERS TEMP
ORACLE OPEN 20-JUN-2011 DEFAULT USERS TEMP
ECOMREADONLY OPEN 28-JUL-2014 DEFAULT USERS TEMP
BACKUPMGR OPEN 18-MAR-2013 DEFAULT USERS TEMP
UCMDB_ID_IBM OPEN 27-MAR-2014 DEFAULT USERS TEMP
AIA OPEN 16-SEP-2013 DEFAULT USERS TEMP
JMSUSER OPEN 16-SEP-2013 DEFAULT USERS TEMP
APTARECL OPEN 27-MAR-2014 DEFAULT USERS TEMP
No of objects with in schemas--
select obj.owner "USERNAME", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "Size_MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj,
(select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc,2 desc, 1;
USERNAME Objects Size_MB
-------------------- ---------- ----------
RMS13 39176 179107
SYS 31757 11357
TSOURCE 870 11038
TS_AUDIT 183 11028
CONV 245 7687
ORAESB 243 1366
PERFSTAT 148 1096
col status format a20 heading "Session status";
col TOTAL format 9999999999 heading "# sessions";
select status, count(*) TOTAL from gv$session
where type='USER'
group by inst_id,status
order by 1,2;
Session status # sessions
-------------------- -----------
ACTIVE 16
INACTIVE 75
set line 200;
col LOGON_TIME format a10;
col sid format 99;
col status format a8;
col process format a12;
col SCHEMANAME format a12;
col OSUSER format a15;
col machine format a25;
col SQL_TEXT format a75;
SELECT S.LOGON_TIME, s.sid, s.process, s.schemaname, s.osuser, s.MACHINE, a.sql_text
FROM v$session s, v$sqlarea a, v$process p
WHERE s.SQL_HASH_VALUE = a.HASH_VALUE
AND s.SQL_ADDRESS = a.ADDRESS
AND s.PADDR = p.ADDR
AND S.STATUS = 'ACTIVE';
LOGON_TIME SID PROCESS SCHEMANAME OSUSER MACHINE SQL_TEXT
---------- --- ------------ ------------ --------------- ------------------------- ---------------------------------------------------------------------------
15-DEC-15 ### 1234 AIA devcrpapp1.thesource.ca begin dbms_aqin.aq$_dequeue_in( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :1
1, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :2
6, :27, :28, :29); end;
prompt
prompt
prompt**==================================================================================================**
prompt** **Database Object Information**
prompt**==================================================================================================**
prompt
prompt List of Largest Object in Database:
prompt -----------------------------------------------------------------------**
set line 200;
col SEGMENT_NAME format a30;
col SEGMENT_TYPE format a10;
col BYTES format a15;
col TABLESPACE_NAME FORMAT A25;
SELECT * FROM (select SEGMENT_NAME, SEGMENT_TYPE TYPE, BYTES/1024/1024 SIZE_MB,
TABLESPACE_NAME from dba_segments order by 3 desc ) WHERE ROWNUM <= 5;
SEGMENT_NAME TYPE SIZE_MB Tablespace
------------------------------ ------------------------------------------------------ ---------- -------------------------
FUTURE_COST_GTT_AU TABLE 11961 RETEK_DATA
DEAL_ITEMLOC_SK2 TABLE 9395 RETEK_DATA
DEAL_ITEMLOC_SK1 TABLE 9371 RETEK_DATA
PRICE_HIST_I2 INDEX 6994 RETEK_INDEX
PRICE_HIST_I1 INDEX 6854 RETEK_INDEX
set line 200;
col owner format a15;
col object_name format a25;
col object_type format a15;
col last_modified format a20;
col created format a20;
col status format a10;
select owner, object_name, object_type, to_char(LAST_DDL_TIME,'MM/DD/YYYY HH24:MI:SS') last_modified,
to_char(CREATED,'MM/DD/YYYY HH24:MI:SS') created, status
from dba_objects
where (SYSDATE - LAST_DDL_TIME) < 7 and owner IN( 'RMS13', 'TSOURCE')
order by last_ddl_time DESC;
RMS13 PK_3101 INDEX 02/26/2016 02:51:52 02/26/2016 02:51:52 VALID
RMS13 PK__3101 TABLE 02/26/2016 02:51:52 02/26/2016 02:51:52 VALID
RMS13 RPM_ITEMLOC_THREAD TABLE 02/26/2016 02:26:42 06/22/2011 14:02:55 VALID
RMS13 RPM_ITEMLOC_THREAD_I1 INDEX 02/26/2016 02:26:42 06/22/2011 14:02:57 VALID
RMS13 RPM_ITEMLOC_THREAD_I2 INDEX 02/26/2016 02:26:42 06/22/2011 14:02:57 VALID
prompt
set pagesize 0;
SELECT 'Object Created in this Week: '|| count(1) from user_objects
where created >= sysdate -7;
Object Created in this Week: 308
prompt
prompt List of Invalid objects of database:
prompt -----------------------------------------------------------------------**
set pagesize 50;
Select owner "USERNAME", object_type, count(*) INVALID from dba_objects
where status='INVALID' group by owner, object_type;
USERNAME OBJECT_TYPE INVALID
-------------------- --------------- ----------
TSOURCE PROCEDURE 1
CONV PACKAGE BODY 4
RMS13 TRIGGER 2
RMS13 FUNCTION 1
set pagesize 50;
SELECT dt.owner, dt.table_name "Table Change > 10%",
ROUND ( (DELETES + UPDATES + INSERTS) / num_rows * 100) PERCENTAGE
FROM dba_tables dt, all_tab_modifications atm
WHERE dt.owner = atm.table_owner
AND dt.table_name = atm.table_name
AND num_rows > 0
AND ROUND ( (DELETES + UPDATES + INSERTS) / num_rows * 100) >= 10
ORDER BY 3 desc;
OWNER Table Change > 10% PERCENTAGE
--------------- ------------------------------------------------------------------------------------------ ----------
RMS13 SA_EXPORTED 10
RMS13 SA_EXPORTED 10
prompt
prompt Database Chained Rows Info:
prompt -----------------------------------------------------------------------**
col table_name format a25;
select owner, table_name, pct_free, pct_used, avg_row_len, num_rows, chain_cnt, trunc(chain_cnt/num_rows*100, 2) as perc
from dba_tables where owner not in ('SYS','SYSTEM')
and table_name not in (select table_name from dba_tab_columns
where data_type in ('RAW','LONG RAW') )
and chain_cnt > 0 order by chain_cnt desc;
OWNER TABLE_NAME % Free PCT_USED AVG_ROW_LEN NUM_ROWS CHAIN_CNT PERC
--------------- ------------------------- ------- ---------- ----------- ---------- ---------- ----------
RMS13 RPM_BULK_CC_PE_THREAD 10.00 20 822 342 41.6
prompt
prompt
prompt**==================================================================================================**
prompt** **RMAN Configuration and Backup**
prompt**==================================================================================================**
col "RMAN CONFIGURE PARAMETERS" format a100;
select 'CONFIGURE '||name ||' '|| value "RMAN CONFIGURE PARAMETERS"
from v$rman_configuration
order by conf#;
RMAN CONFIGURE PARAMETERS
----------------------------------------------------------------------------------------------------
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS
CONFIGURE BACKUP OPTIMIZATION ON
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE'
CONFIGURE CONTROLFILE AUTOBACKUP ON
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F'
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 1 BACKUP TYPE TO BACKUPSET
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE 'SBT_TAPE' TO 1
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE 'SBT_TAPE' TO 1
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin
64/DEV/tdpo.opt)'
set line 200;
col "DEVIC" format a6;
col "L" format 9;
col "FIN:SS" format 9999;
SELECT DECODE(backup_type, 'L', 'Archived Logs', 'D', 'Datafile Full', 'I', 'Incremental')
backup_type, bp.tag "RMAN_BACKUP_TAG", device_type "DEVIC", DECODE( bs.controlfile_included, 'NO', null, bs.controlfile_included) controlfile,
(sp.spfile_included) spfile, sum(bs.incremental_level) "L", TO_CHAR(bs.start_time, 'dd/mm/yyyy HH24:MI:SS') start_time
, TO_CHAR(bs.completion_time, 'dd/mm/yyyy HH24:MI:SS') completion_time, sum(bs.elapsed_seconds) "FIN:SS"
FROM v$backup_set bs, (select distinct set_stamp, set_count, tag , device_type
from v$backup_piece
where status in ('A', 'X')) bp,
(select distinct set_stamp , set_count , 'YES' spfile_included
from v$backup_spfile) sp
WHERE bs.start_time > sysdate - 1
AND bs.set_stamp = bp.set_stamp
AND bs.set_count = bp.set_count
AND bs.set_stamp = sp.set_stamp (+)
AND bs.set_count = sp.set_count (+)
group by backup_type, bp.tag, device_type, bs.controlfile_included, pieces, sp.spfile_included,start_time, bs.completion_time
ORDER BY bs.start_time desc;
BACKUP_TYPE RMAN_BACKUP_TAG DEVIC CONTROLFI SPFILE L
--------------------------------------- ------------------------------------------------------------------------------------------------ ------ --------- --------- --
START_TIME COMPLETION_TIME FIN:SS
--------------------------------------------------------- --------------------------------------------------------- ------
Datafile Full TAG20160226T051549 SBT_TA YES YES
PE
26/02/2016 05:15:49 26/02/2016 05:15:49 0
Datafile Full KEEP_7 SBT_TA YES
PE
26/02/2016 05:15:46 26/02/2016 05:15:47 1
set line 200;
col "DBF_BACKUP_MB" format 999999.99;
col "ARC_BACKUP_MB" format 9999.99;
select trunc(completion_time) "BAK_DATE", sum(blocks*block_size)/1024/1024 "DBF_BACKUP_MB", (SELECT sum(blocks*block_size)/1024/1024 from v$backup_redolog
WHERE first_time > sysdate-1) "ARC_BACKUP_MB"
from v$backup_datafile
WHERE completion_time > sysdate - 1
group by trunc(completion_time)
order by 1 DESC;
BAK_DATE DBF_BACKUP_MB ARC_BACKUP_MB
--------------- ------------- -------------
26-FEB-16 253725.80 64.68
25-FEB-16 65.00 64.68
col "Datafiles backed up within 24h" format a40;
col "Control backed up" format 999;
col "SPFiles backed up" format 999;
SELECT dbfiles||' out of '||numfiles||' datafiles backed up' "Datafiles backed up within 24h", cfiles "CFiles", spfiles "SPFiles"
FROM (select count(*) numfiles from v$datafile), (select count(*) dbfiles from v$backup_datafile a, v$datafile b
where a.file# = b.file# and a.completion_time > sysdate - 1), (select count(*) cfiles from v$backup_datafile
where file# = 0 and completion_time > sysdate - 1), (select count(*) spfiles from v$backup_spfile where completion_time > sysdate - 1);
Datafiles backed up within 24h CFiles SPFiles
---------------------------------------- ---------- ----------
33 out of 33 datafiles backed up 7 6
prompt
prompt *** Most I/O operation (TOP 5):
prompt*-----------------------------------------------------------------------**
col object_type format a15 heading "Object Type"
col object_name format a27 heading "Object Name"
col statistic_name format a22 heading "Statistic Name"
col value format 99999999999 heading "Value"
SELECT * from (
SELECT object_type, object_name, statistic_name, VALUE
FROM v$segment_statistics
WHERE statistic_name LIKE '%phys%' AND VALUE > 50
ORDER BY 4 DESC
) where rownum < 6;
Object Type Object Name Statistic Name Value
--------------- --------------------------- ---------------------- ------------
TABLE PARTITION ITEM_LOC physical reads direct 77612476
TABLE PARTITION ITEM_LOC physical reads direct 77584158
TABLE PARTITION ITEM_LOC physical reads direct 76197389
TABLE PARTITION ITEM_LOC physical reads direct 76183432
TABLE PARTITION ITEM_LOC physical reads direct 74837565
prompt
prompt *** Most buffer gets (TOP 5):
prompt*-----------------------------------------------------------------------**
col object_type format a10;
col object_name format a30;
col statistic_name format a15;
col value format 99999999999;
SELECT * from (
SELECT object_type, object_name, statistic_name,VALUE
FROM v$segment_statistics
WHERE statistic_name LIKE '%logi%' AND VALUE > 50
ORDER BY 4 DESC
) where rownum < 6;
Object Typ Object Name Statistic Name Value
---------- ------------------------------ --------------- ------------
TABLE TS$ logical reads 1291952336
TABLE USER$ logical reads 920355024
INDEX I_OBJ1 logical reads 765475952
TABLE SEG$ logical reads 468984896
INDEX SYS_C00644 logical reads 406461888
prompt
prompt *** Most I/O operation for particualr Query:
prompt*-----------------------------------------------------------------------**
col sql_text format a60;
col reads_per_exe format 99999999 heading 'reads|per_exe';
col "exe" format 99999;
col "sorts" format 99999;
col buffer_gets heading 'buffer|gets';
col disk_reads heading 'disk|reads';
SELECT * FROM (SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_exe,
a.buffer_gets, a.disk_reads, a.executions "exe", a.sorts "sorts", a.address "address"
FROM v$sqlarea a
ORDER BY 2 DESC)
WHERE rownum <= 5;
reads buffer disk
SQL_TEXT per_exe gets reads exe sorts address
------------------------------------------------------------ --------- ---------- ---------- ------ ------ ----------------
DECLARE job BINARY_INTEGER := :job; next_date TIM 249307 571604890 20941821 84 0 07000001E992AA50
call dbms_space.auto_space_advisor_job_proc ( ) 136879 576457928 51877207 379 0 07000001E98C8E30
call dbms_stats.gather_database_stats_job_proc ( 88829 456716307 33666384 379 0 07000001E9888C38
SELECT COUNT(1) FROM ( select * from itemloc_mfque 73861 295486 295446 4 0 07000001FE0D5150
SELECT COUNT(1) FROM ( select * from itemloc_mfque 73860 147744 147720 2 0 07000001AA75C2A0
select 'top logical i/o process', sid, username, total_user_io amt_used, round(100 * total_user_io/total_io,2) pct_used
from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_io
from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid
and c.name in ('consistent gets', 'db block gets') and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by b.sid, nvl(b.username, p.name) order by 3 desc),
(select sum(value) total_io from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid and c.name in ('consistent gets', 'db block gets'))
where rownum < 2
union all
select 'top memory process', sid, username, total_user_mem, round(100 * total_user_mem/total_mem,2)
from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_mem
from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr
and b.sid = a.sid and c.name in ('session pga memory', 'session uga memory')
and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by b.sid, nvl(b.username, p.name) order by 3 desc),
(select sum(value) total_mem from v$statname c, v$sesstat a
where a.statistic# = c.statistic# and c.name in ('session pga memory', 'session uga memory'))
where rownum < 2
union all
select 'top cpu process', sid, username, total_user_cpu, round(100 * total_user_cpu/greatest(total_cpu,1),2)
from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_cpu
from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid
and c.name = 'CPU used by this session' and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by b.sid, nvl(b.username, p.name) order by 3 desc),
(select sum(value) total_cpu from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid
and c.name = 'CPU used by this session') where rownum < 2;
'TOPLOGICALI/OPROCESS' SID USERNAME AMT_USED PCT_USED
--------------------------------------------------------------------- --- -------------------- ---------- ----------
top logical i/o process ### JMSUSER 134437300 10.75
top memory process ### RMS13 6612680 1.58
top cpu process ### AIA 249844 7.43
prompt
prompt Monitoring Current Running Long Job in Database:
prompt*-----------------------------------------------------------------------**
set line 200;
col opname format a30;
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM V$SESSION_LONGOPS
WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK
order by 1;
prompt
prompt Monitoring Object locking:
prompt*-----------------------------------------------------------------------**
set line 200;
col username format a15;
col lock_type format a10;
col osuser format a15;
col owner format a10;
col object_name format a20;
SELECT s.sid, s. serial#, s.username, l.lock_type, s.osuser, s.machine,
o.owner, o.object_name, ROUND(w.seconds_in_wait/60, 2) "Wait"
FROM v$session s, dba_locks l, dba_objects o, v$session_wait w
WHERE s.sid = l.session_id
AND l.lock_type IN ('DML','DDL')AND l.lock_id1 = o.object_id
AND l.session_id = w.sid
ORDER BY s.sid;
prompt
prompt Monitor DB Corruption or Need of Recovery:
prompt*-----------------------------------------------------------------------**
set line 200;
SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name, d.STATUS,
r.ERROR, r.CHANGE#, r.TIME FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
WHERE t.TS# = d.TS# AND d.FILE# = r.FILE#;
set linesize 200
col name format a45 heading "Datafile Name";
col "Read Time(ms)" heading 'Read|Time(ms)';
col "Write Time(ms)" heading 'write|Time(ms)';
col "Avg_Time" heading 'Avg|Time(ms)';
select name,PHYRDS,PHYWRTS,READTIM "Read Time(ms)",WRITETIM "Write Time(ms)",AVGIOTIM "Avg_Time"
from v$filestat, v$datafile where v$filestat.file#=v$datafile.file#;
Read write Avg
Datafile Name PHYRDS PHYWRTS Time(ms) Time(ms) Time(ms)
--------------------------------------------- ---------- ---------- ---------- ---------- ----------
/u06/oradata/DEV/system01.dbf 5462638 1340208 2104018 8645267 0
/u06/oradata/DEV/sysaux01.dbf 10607588 6842678 2175735 31523516 1
/u06/oradata/DEV/retek_part04_d02.dbf 269772 74206 339349 78357 4
/u06/oradata/DEV/users01.dbf 1198565 2522 8597966 18256 8
set feedback on
prompt
rem -----------------------------------------------------------------------
rem Filename: DB_Health_Rep.sql
rem Purpose: Database Statistics and Health Report
rem -----------------------------------------------------------------------
prompt Recommendations:
prompt ====================================================================================================
prompt* SQL Cache Hit rate ratio should be above 90%, if not then increase the Shared Pool Size.
prompt* Dict Cache Hit rate ratio should be above 85%, if not then increase the Shared Pool Size.
prompt* Buffer Cache Hit rate ratio should be above 90%, if not then increase the DB Block Buffer value.
prompt* Redo Log space requests should be less than 0.5% of redo entries, if not then increase log buffer.
prompt* Redo Log space wait time should be near to 0.
prompt
set serveroutput ON
DECLARE
libcac number(10,2);
rowcac number(10,2);
bufcac number(10,2);
redlog number(10,2);
redoent number;
redowaittime number;
BEGIN
select value into redlog from v$sysstat where name = 'redo log space requests';
select value into redoent from v$sysstat where name = 'redo entries';
select value into redowaittime from v$sysstat where name = 'redo log space wait time';
select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;
select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache;
select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac
from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
where cur.statistic# = ncu.statistic#
and ncu.name = 'db block gets'
and con.statistic# = nco.statistic#
and nco.name = 'consistent gets'
and phys.statistic# = nph.statistic#
and nph.name = 'physical reads';
dbms_output.put_line('CACHE HIT RATE');
dbms_output.put_line('********************');
dbms_output.put_line('SQL Cache Hit rate = '||libcac);
dbms_output.put_line('Dict Cache Hit rate = '||rowcac);
dbms_output.put_line('Buffer Cache Hit rate = '||bufcac);
dbms_output.put_line('Redo Log space requests = '||redlog);
dbms_output.put_line('Redo Entries = '||redoent);
dbms_output.put_line('Redo log space wait time = '||redowaittime);
if
libcac < 90 then dbms_output.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
END IF;
if
rowcac < 85 then dbms_output.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
END IF;
if
bufcac < 90 then dbms_output.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');
END IF;
if
redlog > 1000000 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!');
END IF;
END;
/
CACHE HIT RATE
********************
SQL Cache Hit rate = 99.96
Dict Cache Hit rate = 99.82
Buffer Cache Hit rate = 93.35
Redo Log space requests = 1348
Redo Entries = 558464923
Redo log space wait time = 2547
No comments:
Post a Comment