Database Health Check Scripts
The script is used to check the Health of database every daya morning.The CBSPROD batch file is used to set the target of database to be check the health report. The PROD_SCRIPT is having actual command to be performed for this operation. MOVE_DAILY_REPORT batch file is responsible to generate the folder for today's data and move this CSPROD.TXT report file to the particular generated date folder.
HOW TO USE:
Do not try to use this script with SYS/SYSTEM users. Create s separate user with DBA privilege and connect it as below:
SQLPLUSW performace/performace@orcl3 @D:\DB_Performace\Prod_Script\Prod_Script.bat
You must provide the proper path for PROD_SCRIPT.BAT either just by double click you can find the result on the output folder. Do not forget to provide the path for output folder in PROD_SCRIPT.BAT
spool D:\DB_Monitoring_Performace\CBSPROD.txt
..
..--Paste the script in middle
..
spool off
exit
RECOMMENDATION:
This IS only one of the concept of performance Monitoring. You have to check some other aspects like AWR/Statspack Report, Network Performance for I/O Report, Alert.Log/Trace file etc.
AUTOMATE THIS SCRIPT:
You can automated this script in every day morning by using Windows task scheduler or any alternative method. Following is the Steps:
1. Create separate CSPROD and PROD_SCRIPT for all the database.
2. Create different folder with the database name inside the report folder to store seperate output for all DB
3. Schedule these CSPROD script by using windows task scheduler.
EXECUTION TIME:
The maximum time taken by script depend on your database datafiles or over all database structure. On 120 GB single instance database the script taking 5 seconds to complete this operation.
FLEXIBILITY:
You can modify this script at any time just by modifying the PROD_SCRIPT.BAT file with your desired query.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CBSPROD.bat Contents:
SQLPLUSW performace/performace@orcl3 @D:\DB_Performace\Prod_Script\Prod_Script.bat
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Move_Daily_report_Prod.bat Contents:
FOR /F "tokens=1-4 delims=/ " %%I IN ('DATE /t') DO SET mydate=%%K-%%J-%%L
mydate=%%K-1-%%J-%%L
D:
cd DB_Performace
cd Prod_Script
cd Report
md %mydate%
cd..
move CBSPROD.txt D:\DB_Performace\Prod_Script\Report\%mydate%
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Prod_Script.bat Contents:
spool D:\DB_Monitoring_Performace\CBSPROD.txt
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;
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**---------------Database SGA Component Size------------------------------**
set line 200;
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**---------------DB Characterset Information-------------------------------**
Select * from nls_database_parameters;
col name format A60 heading "Control Files";
select name from sys.v_$controlfile;
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.first_time from v$logfile a, v$log b
where a.group# = b.group# order by a.group#;
prompt**---------------DB Profile and Default Information--------------------------**
set line 200;
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**---------------PGA_AGGREGATE_TARGET------------------------------------**
set line 200;
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 from v$pgastat;
prompt**---------------Users Log on Information------------------------------------**
set line 200;
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 from v$session where type !='BACKGROUND';
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----------------------------------**
SET LINE 200;
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 usnig SQL statements-------------------**
set line 200;
SELECT * FROM (SELECT 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 2 DESC)
WHERE rownum <= 5;
prompt**--------------Monitoring Objects Created within 7 days---------------------**
select count(1) from user_objects where CREATED >= sysdate - 7;
prompt**--------------Counting Invalid object in Database---------------------------**
Select owner, object_type, count(*) from dba_objects where status='INVALID' group by owner, object_type;
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**----------------Monitoring DML Lock------------------------------------------**
set line 200;
col username format a30;
col lock_type format a20;
col osuser format a30;
col owner format a25;
col object_name format a50;
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_Time"
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**-----------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**---------------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--------------------**
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#;
prompt**---------------Tablespace Information--------------------------------------**
col tablespace_name format a15 heading "Tablespace Name"
SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) Free_space,
nvl(total_space-Free_space, 0) Used_space,
total_space
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---------------------------**
set linesize 200
col file_name format a50 heading "Datafile Name"
SELECT SUBSTR (df.NAME, 1, 40) 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
prompt**---------------Report Tablespace < 10% free space-----------------------------**
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;
prompt**-------------------File I/O statistics--------------------------------------**
prompt
set linesize 150
col name format a50 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
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
No comments:
Post a Comment