-- Script for Health Check of Database
--run as DBA user or with SELECT_CATALOG_ROLE
set lines 300
col spoolname new_value spoolname
select 'health_check.log.'||to_char(sysdate, 'yymmdd-HHMI') spoolname from dual ;
spool '&spoolname'
prompt**---------------Database General Information-----------------------------**
SELECT DBID "DATABASE_ID", NAME "DB_NAME", LOG_MODE, OPEN_MODE, RESETLOGS_TIME FROM V$DATABASE;
SELECT instance_name, status, to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM sys.v_$instance;
column "Host Name" format a15;
column "Host Address" format a15;
SELECT UTL_INADDR.GET_HOST_ADDRESS "Host Address", UTL_INADDR.GET_HOST_NAME "Host Name" FROM DUAL;
SELECT BANNER "VERSION" FROM V$VERSION;
prompt**---------------Location of alert logs----------------------------**
select * from V$DIAG_INFO where name ='Diag Trace';
prompt**---------------information about Database usage-----------------------------**
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;
prompt**---------------Parameter related information ----------------------------**
col value for a80
select name, value from v$parameter where name like '%pfile';
prompt**---------------information about temporary tablespace usage-----------------------------**
select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024, ALLOCATED_SPACE/1024/1024, FREE_SPACE/1024/1024 from dba_temp_free_space ;
col FILE_NAME for a70
SELECT tablespace_name, file_name, bytes/1024/1024 "Used in MB", MAXBYTES/1024/1024 "Max in MB", MAXBLOCKS,STATUS, AUTOEXTENSIBLE
FROM dba_temp_files ;
prompt**---------------information about resource usage-----------------------------**
select * from v$resource_limit;
prompt**---------------Database Memory Component Size Dynamics------------------------------**
SELECT component,
ROUND(current_size/1024/1204) AS current_size_mb,
ROUND(min_size/1024/1204) AS min_size_mb,
ROUND(max_size/1024/1204) AS max_size_mb
FROM v$memory_dynamic_components
WHERE current_size != 0
ORDER BY component;
prompt**---------------Database SGA Component Size------------------------------**
select
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;
prompt**---------------PGA_AGGREGATE_TARGET------------------------------------**
column name clear;
select name, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
from (select name, value cnt, (sum(value) over()) total
from v$sysstat where name like 'workarea exec%'
);
Prompt--DBA increase this Parameter when "multipass" value are greater than ZERO and Reduce whenever the optimal executions are 100 percent.
select name, value/1024/1204 "in MB" from v$pgastat where name not like '%count%'
and name not like '%percentage%';
select name, value from v$pgastat where name like '%count%'
or name like '%percentage%';
prompt**---------------DB Characterset Information-------------------------------**
Select * from nls_database_parameters;
prompt**---------------DB controlfile Information-------------------------------**
col name format A60 heading "Control Files";
select name from sys.v_$controlfile;
prompt**---------------DB Redolog Information-------------------------------**
col member format A40 heading "Redolog Files";
set line 200;
col archived format a15;
col status format a10;
col first_time format a20;
select a.group#, a.member, b.archived, b.status, b.bytes/1024/1024 as mb , b.first_time from v$logfile a, v$log b
where a.group# = b.group# order by a.group#;
prompt**---------------DB Profile and Default Information--------------------------**
col username format a25;
col profile format a20;
col default_tablespace format a25;
col temporary_tablespace format a25;
Select username, profile, default_tablespace, temporary_tablespace from dba_users;
prompt**--------------Monitoring Current Running Long Job in DB--------------------**
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**---------------Users Logon Information------------------------------------**
col OSUSER format a40;
col STATUS format a15
col MACHINE format a35;
Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss') "Logon_Time",osuser,status,machine,sql_id from v$session where type !='BACKGROUND';
prompt**---------------Session Status Summary ------------------------------------**
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
break on inst_id skip page
compute sum label 'sum of status' of USERNAME on status
compute sum of total on status
compute sum of total on report
select inst_id,status,USERNAME,count(status) total from gv$session
group by rollup(inst_id,USERNAME,status)
order by inst_id,USERNAME,status;
SELECT STATUS , COUNT(STATUS) FROM V$SESSION
GROUP BY STATUS ;
prompt**---------------Monitoring statistics of tables ---------------------------**
select OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED from dba_tables
where OWNER not in ('SYS','SYSTEM','DBSNMP','PERFSTAT','OUTLN')
order by LAST_ANALYZED desc;
prompt**---------------Monitoring Schema Growth Rate---------------------------**
select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "Size in 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;
prompt**---------------Largest object in Database----------------------------------**
col SEGMENT_NAME format a30;
col SEGMENT_TYPE format a30;
col BYTES format a30;
col TABLESPACE_NAME FORMAT A30;
SELECT * FROM (select SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024/1024 GB, TABLESPACE_NAME from dba_segments order by 3 desc ) WHERE ROWNUM <= 5;
prompt**--------------Monitoring Most resource using SQL statements-------------------**
SELECT * FROM (SELECT sql_id, Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets, a.disk_reads, a.executions, a.sorts, a.address
FROM v$sqlarea a
ORDER BY 3 DESC)
WHERE rownum <= 5;
prompt**--------------Monitoring Unusable indexes --------------------**
select index_name, table_name, partitioned from dba_indexes where status ='UNUSABLE';
prompt**--------------Monitoring Unusable index partitions --------------------**
select index_name, PARTITION_NAME, status from dba_ind_partitions where status ='UNUSABLE';
prompt**--------------Monitoring Non-Sys Objects Created within 15 days---------------------**
col object_name for a40
select owner,
object_name,
to_char(created,'MM-DD-YYYY HH24:MI:SS') created
from dba_objects
where owner != 'SYS'
and (
trunc(created) >= trunc(sysdate - 15)
)
order by created desc;
prompt **Please note that addition of partition also appears under this. **
prompt**--------------Counting Invalid object in Database---------------------------**
Select owner, object_type, count(*) from dba_objects where status='INVALID' group by owner, object_type;
prompt**-----------Monitor Non-Sys owned tables in SYSTEM Tablespace-----------------**
SELECT owner, table_name, tablespace_name FROM dba_tables WHERE tablespace_name = 'SYSTEM' AND owner NOT IN ('SYSTEM', 'SYS', 'OUTLN');
prompt**--------------Details of sequences---------------------------**
select * from dba_sequences where SEQUENCE_OWNER not in ('SYS','SYSTEM','DBSNMP','PERFSTAT');
prompt**---------------Track Redolog Generation-------------------------------------**
select trunc(completion_time) logdate, count(*) logswitch, round((sum(blocks*block_size) / 1024 / 1024)) "REDO PER DAY(MB)"
from v$archived_log
group by trunc(completion_time)
order by 1;
prompt**--------------Monitor DB Corruption or Need of Recovery--------------------**
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#;
prompt**---------------Database Datafile Information-----------------------------**
set lines 300
col FILE_NAME for a70
set pages 50
select FILE_ID,FILE_NAME, status, TABLESPACE_NAME,ONLINE_STATUS, AUTOEXTENSIBLE from DBA_DATA_FILES order by FILE_ID;
prompt**---------------Tablespace Information--------------------------------------**
col tablespace_name format a35 heading "Tablespace Name"
SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) "Free_space in MB",
nvl(total_space-Free_space, 0) "Used_space in MB",
total_space "TOTAL_space in MB"
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name
/
prompt**---------------Shows Used/Free Space Per Datafile---------------------------**
col file_name format a70 heading "Datafile Name"
SELECT df.NAME file_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_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;
TTI off
col file_name clear
prompt**---------------Report Tablespace < 10% free space-----------------------------**
set pagesize 300;
column tablespace_name format a35 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(Mb)";
column Pct_Free format 999.99 heading "% Free";
column Max_Free format 999,999,999 heading "Max Free(Mb)";
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_MB
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;
prompt **-------------------File Max Size statistics--------------------------------------**
col file_name format a70 heading "Datafile Name"
select file_id, tablespace_name, bytes/1024/1024 "bytes_MB", maxbytes/1024/1024 "maxbytes_MB", maxblocks, increment_by, file_name,autoextensible
from dba_data_files where autoextensible = 'YES';
prompt**-------------------File I/O statistics--------------------------------------**
prompt
col name format a70 heading "Datafile Name"
select name,PHYRDS "Physical Reads",PHYWRTS "Physical Writes",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
col name clear
prompt
rem -----------------------------------------------------------------------
rem Filename: sga_stat.sql
rem Purpose: Display database SGA statistics
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('SGA CACHE STATISTICS');
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
Thanks to
-- http://osamamustafa.blogspot.sg/2013/01/database-healthcheck.html
-- http://shahiddba.blogspot.sg/2012/03/database-health-check-scripts.html
--run as DBA user or with SELECT_CATALOG_ROLE
set lines 300
col spoolname new_value spoolname
select 'health_check.log.'||to_char(sysdate, 'yymmdd-HHMI') spoolname from dual ;
spool '&spoolname'
prompt**---------------Database General Information-----------------------------**
SELECT DBID "DATABASE_ID", NAME "DB_NAME", LOG_MODE, OPEN_MODE, RESETLOGS_TIME FROM V$DATABASE;
SELECT instance_name, status, to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM sys.v_$instance;
column "Host Name" format a15;
column "Host Address" format a15;
SELECT UTL_INADDR.GET_HOST_ADDRESS "Host Address", UTL_INADDR.GET_HOST_NAME "Host Name" FROM DUAL;
SELECT BANNER "VERSION" FROM V$VERSION;
prompt**---------------Location of alert logs----------------------------**
select * from V$DIAG_INFO where name ='Diag Trace';
prompt**---------------information about Database usage-----------------------------**
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;
prompt**---------------Parameter related information ----------------------------**
col value for a80
select name, value from v$parameter where name like '%pfile';
prompt**---------------information about temporary tablespace usage-----------------------------**
select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024, ALLOCATED_SPACE/1024/1024, FREE_SPACE/1024/1024 from dba_temp_free_space ;
col FILE_NAME for a70
SELECT tablespace_name, file_name, bytes/1024/1024 "Used in MB", MAXBYTES/1024/1024 "Max in MB", MAXBLOCKS,STATUS, AUTOEXTENSIBLE
FROM dba_temp_files ;
prompt**---------------information about resource usage-----------------------------**
select * from v$resource_limit;
prompt**---------------Database Memory Component Size Dynamics------------------------------**
SELECT component,
ROUND(current_size/1024/1204) AS current_size_mb,
ROUND(min_size/1024/1204) AS min_size_mb,
ROUND(max_size/1024/1204) AS max_size_mb
FROM v$memory_dynamic_components
WHERE current_size != 0
ORDER BY component;
prompt**---------------Database SGA Component Size------------------------------**
select
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;
prompt**---------------PGA_AGGREGATE_TARGET------------------------------------**
column name clear;
select name, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
from (select name, value cnt, (sum(value) over()) total
from v$sysstat where name like 'workarea exec%'
);
Prompt--DBA increase this Parameter when "multipass" value are greater than ZERO and Reduce whenever the optimal executions are 100 percent.
select name, value/1024/1204 "in MB" from v$pgastat where name not like '%count%'
and name not like '%percentage%';
select name, value from v$pgastat where name like '%count%'
or name like '%percentage%';
prompt**---------------DB Characterset Information-------------------------------**
Select * from nls_database_parameters;
prompt**---------------DB controlfile Information-------------------------------**
col name format A60 heading "Control Files";
select name from sys.v_$controlfile;
prompt**---------------DB Redolog Information-------------------------------**
col member format A40 heading "Redolog Files";
set line 200;
col archived format a15;
col status format a10;
col first_time format a20;
select a.group#, a.member, b.archived, b.status, b.bytes/1024/1024 as mb , b.first_time from v$logfile a, v$log b
where a.group# = b.group# order by a.group#;
prompt**---------------DB Profile and Default Information--------------------------**
col username format a25;
col profile format a20;
col default_tablespace format a25;
col temporary_tablespace format a25;
Select username, profile, default_tablespace, temporary_tablespace from dba_users;
prompt**--------------Monitoring Current Running Long Job in DB--------------------**
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**---------------Users Logon Information------------------------------------**
col OSUSER format a40;
col STATUS format a15
col MACHINE format a35;
Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss') "Logon_Time",osuser,status,machine,sql_id from v$session where type !='BACKGROUND';
prompt**---------------Session Status Summary ------------------------------------**
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
break on inst_id skip page
compute sum label 'sum of status' of USERNAME on status
compute sum of total on status
compute sum of total on report
select inst_id,status,USERNAME,count(status) total from gv$session
group by rollup(inst_id,USERNAME,status)
order by inst_id,USERNAME,status;
SELECT STATUS , COUNT(STATUS) FROM V$SESSION
GROUP BY STATUS ;
prompt**---------------Monitoring statistics of tables ---------------------------**
select OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED from dba_tables
where OWNER not in ('SYS','SYSTEM','DBSNMP','PERFSTAT','OUTLN')
order by LAST_ANALYZED desc;
prompt**---------------Monitoring Schema Growth Rate---------------------------**
select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "Size in 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;
prompt**---------------Largest object in Database----------------------------------**
col SEGMENT_NAME format a30;
col SEGMENT_TYPE format a30;
col BYTES format a30;
col TABLESPACE_NAME FORMAT A30;
SELECT * FROM (select SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024/1024 GB, TABLESPACE_NAME from dba_segments order by 3 desc ) WHERE ROWNUM <= 5;
prompt**--------------Monitoring Most resource using SQL statements-------------------**
SELECT * FROM (SELECT sql_id, Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets, a.disk_reads, a.executions, a.sorts, a.address
FROM v$sqlarea a
ORDER BY 3 DESC)
WHERE rownum <= 5;
prompt**--------------Monitoring Unusable indexes --------------------**
select index_name, table_name, partitioned from dba_indexes where status ='UNUSABLE';
prompt**--------------Monitoring Unusable index partitions --------------------**
select index_name, PARTITION_NAME, status from dba_ind_partitions where status ='UNUSABLE';
prompt**--------------Monitoring Non-Sys Objects Created within 15 days---------------------**
col object_name for a40
select owner,
object_name,
to_char(created,'MM-DD-YYYY HH24:MI:SS') created
from dba_objects
where owner != 'SYS'
and (
trunc(created) >= trunc(sysdate - 15)
)
order by created desc;
prompt **Please note that addition of partition also appears under this. **
prompt**--------------Counting Invalid object in Database---------------------------**
Select owner, object_type, count(*) from dba_objects where status='INVALID' group by owner, object_type;
prompt**-----------Monitor Non-Sys owned tables in SYSTEM Tablespace-----------------**
SELECT owner, table_name, tablespace_name FROM dba_tables WHERE tablespace_name = 'SYSTEM' AND owner NOT IN ('SYSTEM', 'SYS', 'OUTLN');
prompt**--------------Details of sequences---------------------------**
select * from dba_sequences where SEQUENCE_OWNER not in ('SYS','SYSTEM','DBSNMP','PERFSTAT');
prompt**---------------Track Redolog Generation-------------------------------------**
select trunc(completion_time) logdate, count(*) logswitch, round((sum(blocks*block_size) / 1024 / 1024)) "REDO PER DAY(MB)"
from v$archived_log
group by trunc(completion_time)
order by 1;
prompt**--------------Monitor DB Corruption or Need of Recovery--------------------**
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#;
prompt**---------------Database Datafile Information-----------------------------**
set lines 300
col FILE_NAME for a70
set pages 50
select FILE_ID,FILE_NAME, status, TABLESPACE_NAME,ONLINE_STATUS, AUTOEXTENSIBLE from DBA_DATA_FILES order by FILE_ID;
prompt**---------------Tablespace Information--------------------------------------**
col tablespace_name format a35 heading "Tablespace Name"
SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) "Free_space in MB",
nvl(total_space-Free_space, 0) "Used_space in MB",
total_space "TOTAL_space in MB"
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name
/
prompt**---------------Shows Used/Free Space Per Datafile---------------------------**
col file_name format a70 heading "Datafile Name"
SELECT df.NAME file_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_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;
TTI off
col file_name clear
prompt**---------------Report Tablespace < 10% free space-----------------------------**
set pagesize 300;
column tablespace_name format a35 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(Mb)";
column Pct_Free format 999.99 heading "% Free";
column Max_Free format 999,999,999 heading "Max Free(Mb)";
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_MB
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;
prompt **-------------------File Max Size statistics--------------------------------------**
col file_name format a70 heading "Datafile Name"
select file_id, tablespace_name, bytes/1024/1024 "bytes_MB", maxbytes/1024/1024 "maxbytes_MB", maxblocks, increment_by, file_name,autoextensible
from dba_data_files where autoextensible = 'YES';
prompt**-------------------File I/O statistics--------------------------------------**
prompt
col name format a70 heading "Datafile Name"
select name,PHYRDS "Physical Reads",PHYWRTS "Physical Writes",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
col name clear
prompt
rem -----------------------------------------------------------------------
rem Filename: sga_stat.sql
rem Purpose: Display database SGA statistics
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('SGA CACHE STATISTICS');
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
Thanks to
-- http://osamamustafa.blogspot.sg/2013/01/database-healthcheck.html
-- http://shahiddba.blogspot.sg/2012/03/database-health-check-scripts.html
No comments:
Post a Comment