Monitoring Script for DBA Oracle

Monitoring Script for DBA Oracle

Script give you all the details of the database which dba need to identify and check one by one. It will give you spool file which helps to prevent and identify the issue fast in one go. It cover almost all part of the database for check issue or performance.


spool depthanaylse.log
set pagesize 0
set ver off
set lines 132
set pause off
set heading off
set serveroutput on
set feedback off

DECLARE
v_db_version      NUMBER;

BEGIN
-- Produces a 2-digit numeric version i.e. 80, 81, 92, etc.
SELECT TO_NUMBER(TRANSLATE(SUBSTR(banner,(INSTR(banner,'Release ')+8),4),
'0123456789.',
'0123456789')
) version
INTO v_db_version
FROM v$version
WHERE banner LIKE 'Oracle%Release%';
dbms_output.put_line (TO_CHAR (v_db_version));
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
RAISE;
END;
/

set pages 50000
set head off
set lines 180
prompt
prompt Tablespace Size Information
prompt ============================
prompt NAME         0-10 GB  11-100 GB 101-500 GB 501-1000 GB     > 1 TB
prompt ========= ========== ========== ========== =========== ==========
select f.name,
a.num "0-10 GB",
b.num "11-100 GB",
c.num "101-500 GB",
d.num "501-1000 GB",
e.num "> 1 TB"
from
(select count( sum(bytes)/(1024*1024*1024)) NUM
from dba_data_files f
group by tablespace_name
having sum(bytes)/(1024*1024*1024) between 0 and 10
) a
,
(select count(sum(bytes)/(1024*1024*1024)) NUM
from dba_data_files f
group by tablespace_name
having sum(bytes)/(1024*1024*1024) between 11 and 100
) b
,
(select count(sum(bytes)/(1024*1024*1024)) NUM
from dba_data_files f
group by tablespace_name
having sum(bytes)/(1024*1024*1024) between 101 and 500
) c
,
(select count(sum(bytes)/(1024*1024*1024)) NUM
from dba_data_files f
group by tablespace_name
having sum(bytes)/(1024*1024*1024) between 501 and 1000
) d
,
(select count(sum(bytes)/(1024*1024*1024)) NUM
from dba_data_files f
group by tablespace_name
having sum(bytes)/(1024*1024*1024) > 1001
) e,
(select name from V$database) f
;

prompt
prompt Tablespace Capacity Forecast
prompt ============================
prompt
prompt Note: This is a theoretical estimate which can be used in the absence of any other formal
prompt capacity forecasting methods as a basis of forecasting space requirements
prompt
set feedback off
set lines 180
set head on
col "Create Time" format a11
col name format a30
col curr_size_mb format 99999999
col growth_per_day format 99999999.9
col growth_120_days format 99999999.9
col growth_240_days format 99999999.9
col projection_365_days_mb format 99999999
select min(creation_time) "Create Time", ts.name, round(sum(df.bytes)/1024/1024) curr_size_mb,
round( (sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)),1) growth_per_day,
round( (sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)) * 120,1) growth_120_days,
round( (sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)) * 240,1) growth_240_days,
round( (sum(bytes)/1024/1024)+((sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time))*365) ) projection_365_days_mb
from V$datafile df ,V$tablespace ts where df.ts#=ts.ts# group by df.ts#,ts.name order by df.ts#;
set head off
prompt

prompt
prompt Block Sizes
prompt ===========
prompt
set serveroutput on
set feedback off
begin
for x in (select block_size, count(*) num from dba_tablespaces group by block_size) loop
dbms_output.put_line(rpad('Block size',30,'.')||': '||x.block_size);
dbms_output.put_line(rpad('#TS using this BLK Size',30,'.')||': '||x.num);
dbms_output.put_line('');
dbms_output.put_line('');
end loop;
--
end;
/
prompt
prompt Invalid Object Details
Prompt ======================
select owner,object_type,count(*) from dba_objects where status='INVALID'
group by owner,object_type;

prompt
prompt Extent Management
prompt =================
prompt
set serveroutput on
begin
for x in (select EXTENT_MANAGEMENT, count(*) num from dba_tablespaces group by EXTENT_MANAGEMENT) loop
dbms_output.put_line(rpad('Extent Management',30,'.')||': '||x.EXTENT_MANAGEMENT);
dbms_output.put_line(rpad('#TS using this Ext Mgmt',30,'.')||': '||x.num);
dbms_output.put_line('');
dbms_output.put_line('');
end loop;
--
end;
/

prompt
prompt Segment Space Management
prompt ========================
prompt
set serveroutput on
begin
for x in (select SEGMENT_SPACE_MANAGEMENT, count(*) num from dba_tablespaces group by SEGMENT_SPACE_MANAGEMENT) loop
dbms_output.put_line(rpad('Segment Space Management',30,'.')||': '||x.SEGMENT_SPACE_MANAGEMENT);
dbms_output.put_line(rpad('#TS using this Space Mgmt',30,'.')||': '||x.num);
dbms_output.put_line('');
dbms_output.put_line('');
end loop;
--
end;
/

prompt
prompt Top 5 Indexes bigger than their corresponding Tables
prompt ====================================================
prompt Note - Possible candidates for REBUILDING
prompt
set head on
set lines 200
col "Table" format a30
col "Index" format a30
col "Owner" format a20
col tab_gb format 999999.99
col ind_gb format 999999.99
select * from (
select a.owner, a.segment_name "Table", a.tab_gb, b.index_name "Index", b.ind_gb
from
(select owner,segment_name, round(sum(bytes/(1024*1024*1024)),2) TAB_GB
from dba_segments where segment_type='TABLE'
and owner not in ('SYS','SYSTEM')
group by owner,segment_name) a,
(select s.owner,i.table_name, i.index_name, round(sum(bytes/(1024*1024*1024)),2) IND_GB
from dba_indexes i, dba_segments s
where s.segment_type='INDEX'
and s.segment_name=i.index_name
and s.owner not in ('SYS','SYSTEM')
group by s.owner,i.table_name, i.index_name) b
where a.owner=b.owner
and a.segment_name=b.table_name
and a.tab_gb < b.ind_gb
order by b.ind_gb desc
) where rownum<6;
set head off

prompt
prompt Archive Log Information
prompt =======================
prompt
archive log list;
prompt
set head off
set feedback off
select 'Archive Log destination is pointing to Flash Recovery Area'
from V$parameter
where lower(name) like 'log_archive_dest%' and lower(value) like '%use_db_recovery_file_dest%';

set feedback off
prompt
prompt Undo Management Info
prompt =======================
prompt
set serveroutput on
declare
w_aum varchar2(20);
begin
for x in (select name, value from V$parameter where name in ('undo_management','undo_retention')) loop
if lower(x.name) = 'undo_management' then
w_aum := nvl(lower(x.value),'manual');
end if;
dbms_output.put_line(rpad(x.name,30,'.')||': '||x.value);
end loop;
--
if w_aum = 'auto' then
for y in (select sum(SSOLDERRCNT) ssold, sum(NOSPACEERRCNT) nospa, trunc(sysdate-min(BEGIN_TIME)) n from V$undostat) loop
dbms_output.put_line(rpad('#Snapshot too old errors',30,'.')||': '||y.ssold||' in the last '||y.n|| ' days');
dbms_output.put_line(rpad('#No undo space errors',30,'.')||': '||y.nospa||' in the last '||y.n|| ' days');
end loop;
end if;
--
end;
/

set feedback off
prompt
prompt Statspack/AWR Info
prompt =======================
prompt
set serveroutput on
declare
w_snp varchar2(50);
w_ret varchar2(50);
w_min date;
w_num number;
w_type varchar2(1);
begin
for x in (select owner from dba_tables where table_name ='STATS$SNAPSHOT') loop
execute immediate 'select min(SNAP_TIME) min, count(1) num from stats$snapshot'
into w_min, w_num;
dbms_output.put_line(rpad('STATSPACK earliest snap',30,'.')||': '|| to_char(w_min,'dd-MON-yyyy hh24:mi') );
dbms_output.put_line(rpad('STATSPACK number of snaps',30,'.')||': '||w_num);
w_type := 'S';
dbms_output.put_line('STATSPACK is configured');
end loop;
--
begin
execute immediate 'select retention, snap_interval from DBA_HIST_WR_CONTROL where rownum<2'
into w_ret, w_snp;
w_type := 'A';
dbms_output.put_line('AWR is configured');
dbms_output.put_line(rpad('AWR Snap Interval',30,'.')||': '||w_snp);
dbms_output.put_line(rpad('AWR Retention',30,'.')||': '||w_ret);
exception when others then null;
end;
--
if w_type is null then
dbms_output.put_line('Neither AWR nor STATSPACK is configured');
end if;
end;
/

set feedback off
prompt
prompt Partitioning Information
prompt =======================
prompt
set serveroutput on
begin
for x in (select count(1) num from dba_tab_partitions where table_owner not in ('SYS','SYSTEM')
and tablespace_name not in ('SYSTEM','SYSAUX') )loop
dbms_output.put_line(rpad('Tab Partition count',30,'.')||': '||x.num);
end loop;
--
for x in (select count(1) num from dba_tab_subpartitions where table_owner not in ('SYS','SYSTEM')
and tablespace_name not in ('SYSTEM','SYSAUX') )loop
dbms_output.put_line(rpad('Tab Sub-partition count',30,'.')||': '||x.num);
end loop;
--
for x in (select count(1) num from dba_ind_partitions where index_owner not in ('SYS','SYSTEM')
and tablespace_name not in ('SYSTEM','SYSAUX') )loop
dbms_output.put_line(rpad('Ind Partition count',30,'.')||': '||x.num);
end loop;
--
for x in (select count(1) num from dba_ind_subpartitions where index_owner not in ('SYS','SYSTEM')
and tablespace_name not in ('SYSTEM','SYSAUX') )loop
dbms_output.put_line(rpad('Ind Sub-partition count',30,'.')||': '||x.num);
end loop;
--
for x in (select PARTITIONING_TYPE, SUBPARTITIONING_TYPE, count(*) num from dba_part_tables where owner not in ('SYS','SYSTEM')
and def_tablespace_name not in ('SYSTEM','SYSAUX')
group by PARTITIONING_TYPE, SUBPARTITIONING_TYPE)loop
dbms_output.put_line(rpad('PartType',30,'.')||': '||x.PARTITIONING_TYPE);
dbms_output.put_line(rpad('SubPartType',30,'.')||': '||x.SUBPARTITIONING_TYPE);
dbms_output.put_line(rpad('NumParts',30,'.')||': '||x.NUM);
dbms_output.put_line('');
end loop;
end;
/

set feedback off
prompt
prompt Data Guard Information
prompt =======================
prompt
set serveroutput on
set lines 180
declare
w1 pls_integer;
w2 pls_integer;
w_service varchar2(50);
w_var_1 varchar2(10);
begin
for y in (select count(*) val from V$parameter where name like 'log_archive_dest%' and upper(value) like '%SERVICE=%') loop
if y.val=0 then
dbms_output.put_line(rpad('Dataguard configuration',30,'.')||': '|| 'NONE');
else
for x in (select value from V$parameter where name like 'log_archive_dest%' and upper(value) like '%SERVICE=%') loop
/*w1 := instr(UPPER(value),'SERVICE=');
w2 := instr(UPPER(value),' ',w1);
w_service := substr(value,w1+9,w2-w1+10)*/
select upper(SUPPLEMENTAL_LOG_DATA_PK) into w_var_1 from V$database;
dbms_output.put_line(rpad('Standby Logs shipped to',30,'.')||': '|| x.value);
if w_var_1 = 'YES' then
dbms_output.put_line(rpad('DataGuard config',30,'.')||': '|| 'LOGICAL');
else
dbms_output.put_line(rpad('DataGuard config',30,'.')||': '|| 'PHYSICAL');
end if;
for z in (select round(avg(bytes/(1024*1024*1024)),2) redo_val from
( select sum(blocks*block_size) bytes
from V$archived_log
where applied='YES' group by trunc(FIRST_TIME)
)
) loop
dbms_output.put_line(rpad('Avg redo applied per day (GB)',30,'.')||': '|| z.redo_val);
end loop;
dbms_output.put_line('');
end loop;
end if;
end loop;
--
end;
/

prompt
prompt Audit Trail Information
prompt =======================
prompt
set serveroutput on size 1000000
set feedback off verify off
set lines 200 pages 999
declare
audsize number;
audcount number;
v_db_version number;
w_min_date date;
w_count number;
begin
for x in (select value from V$parameter where name='audit_trail') loop
dbms_output.put_line( rpad('audit_trail parameter',40,'.')||': '|| x.value );
end loop;
--
select sum(bytes)/(1024*1024) into audsize from dba_segments where segment_name='AUD$' and owner='SYS';
dbms_output.put_line( rpad('Size of AUD$ (M)',40,'.')||': '|| round(audsize,2) );
for x in ( select segment_name, sum(bytes)/(1024*1024) isize from dba_segments
where segment_name in (select index_name from dba_indexes where table_name='AUD$')
and owner='SYS'
group by segment_name ) loop
dbms_output.put_line( rpad('Size of '||x.segment_name||'(M)',40,'.')||': '|| round(x.isize,2) );
end loop;
--
SELECT TO_NUMBER(TRANSLATE(SUBSTR(banner,(INSTR(banner,'Release ')+8),4),'0123456789.','0123456789') )
INTO v_db_version
FROM V$version
WHERE banner LIKE 'Oracle%Release%';
--
if v_db_version <=93 then
select /*+ full(a) parallel(a 16) */ count(1), min(timestamp#) into audcount, w_min_date from sys.aud$ a;
--
else
execute immediate 'select /*+ full(a) parallel(a 16) */ count(1), min(ntimestamp#) from sys.aud$ a' into audcount, w_min_date;
end if;
dbms_output.put_line( rpad('Count of audit records',40,'.')||': '|| audcount);
dbms_output.put_line( rpad('Oldest Record dated',40,'.')||': '|| w_min_date);
--
for x in (
select t.tablespace_name, round(t.bytes/(1024*1024),2) AUTOEXTEND_GB, round(currbytes/(1024*1024),2) TOTAL_GB, round(e.bytes/(1024*1024),2) USED_GB, round(e.bytes*100/currbytes,2) Usage
from (select tablespace_name, sum(greatest(bytes,MAXBYTES)) bytes, sum(bytes) currbytes
from dba_data_files group by tablespace_name) t,(select tablespace_name,sum(bytes) bytes from sm$ts_used  group by tablespace_name) e
where t.tablespace_name=e.tablespace_name and e.tablespace_name = (select tablespace_name from dba_tables where table_name='AUD$' and owner='SYS')
) loop
dbms_output.put_line( rpad('Audit TS ',40,'.')||': '|| x.tablespace_name);
dbms_output.put_line( rpad('Audit TS size(M)',40,'.')||': '|| x.TOTAL_GB);
dbms_output.put_line( rpad('Audit TS Usage%',40,'.')||': '|| x.usage);
end loop;
--
for y in (select name,value from V$parameter where name like 'audit%') loop
dbms_output.put_line( rpad(y.name,40,'.')||': '|| y.value);
end loop;
--
select count(*) into w_count from dba_stmt_audit_opts;
dbms_output.put_line( rpad('STMT Audit Options',40,'.')||': '|| w_count);
--
select count(*) into w_count  from dba_priv_audit_opts;
dbms_output.put_line( rpad('PRIV Audit Options',40,'.')||': '|| w_count);
--
select count(*) into w_count  from dba_obj_audit_opts;
dbms_output.put_line( rpad('OBJECT Audit Options',40,'.')||': '|| w_count);
--
--dbms_output.put_line('+++++++++++++++++++End of Audit details+++++++++++++++++++++++');
end;
/

set feedback off
prompt
prompt Scheduler Jobs Information
prompt ==========================
prompt
set serveroutput on
declare
wj1 pls_integer;
begin
for x in (select count(1) value from dba_jobs) loop
dbms_output.put_line(rpad('DBMS Jobs',30,'.')||': '||x.value);
end loop;
--
begin
execute immediate 'select count(1) from dba_scheduler_jobs'
into wj1;
dbms_output.put_line(rpad('Ora Scheduler Jobs',30,'.')||': '||wj1);
exception when others then null;
end;
end;
/

prompt
prompt Optimizer Statistics Info
prompt =========================
prompt
set serveroutput on
set feedback off verify off
declare
perc varchar2(20);
tot number;
begin
for x in ( select count(*) num
from dba_tables
where owner not in ('SYS','SYSTEM') and nvl(last_analyzed,sysdate-100)<sysdate-7 ) loop
select count(1) into tot from dba_tables where owner not in ('SYS','SYSTEM');
perc := round(x.num*100/tot,1)||'%';
dbms_output.put_line(rpad('#Tables stale since 1 week',30,'.')||': '||x.num||' ('||perc||')');
end loop;
--
exception when others then null;
end;
/

set serveroutput on
declare
perc varchar2(20);
tot number;
begin
for x in ( select count(*) num
from dba_indexes
where owner not in ('SYS','SYSTEM') and nvl(last_analyzed,sysdate-100)<sysdate-7 ) loop
select count(1) into tot from dba_indexes where owner not in ('SYS','SYSTEM');
perc := round(x.num*100/tot,1)||'%';
dbms_output.put_line(rpad('#Indexes stale since 1 week',30,'.')||': '||x.num||' ('||perc||')');
end loop;
--
exception when others then null;
end;
/

set serveroutput on
declare
perc varchar2(20);
tot number;
begin
for x in ( select count(*) num
from (select table_owner,nvl(last_analyzed,sysdate-100) last_analyzed from dba_tab_partitions union select table_owner,nvl(last_analyzed,sysdate-100) last_analyzed from dba_tab_subpartitions)
where table_owner not in ('SYS','SYSTEM') and last_analyzed7 days',30,'.')||': '||x.num||' ('||perc||')');
end loop;
--
exception when others then null;
end;
/

set serveroutput on
declare
perc varchar2(20);
tot number;
begin
for x in ( select count(*) num
from (select index_owner,nvl(last_analyzed,sysdate-100) last_analyzed from dba_ind_partitions union select index_owner,nvl(last_analyzed,sysdate-100) last_analyzed from dba_ind_subpartitions)
where index_owner not in ('SYS','SYSTEM') and last_analyzed7 days',30,'.')||': '||x.num||' ('||perc||')');
end loop;
--
exception when others then null;
end;
/

prompt
prompt Queries with unstable execution plans - Top 10 on elapsed time( only for 10g and above)
prompt ========================================================================================
prompt
set lines 180 pages 999
set serveroutput on feedback off verify off
declare
x_sql varchar2(2000);
TYPE cur_typ IS REF CURSOR;
c cur_typ;
w_sql_id varchar2(40);
w_plan_hash_value number;
w_et number;
w_ela number;
w_avg_ela number;
w_hdr boolean := false;
w_version number;
begin
--
SELECT TO_NUMBER(TRANSLATE(SUBSTR(banner,(INSTR(banner,'Release ')+8),4),
'0123456789.',
'0123456789')
)
INTO w_version
FROM V$version
WHERE banner LIKE 'Oracle%Release%';
--
if w_version >= 100 then
x_sql:= 'select * from (
select * from (select /*+ push_subq(@q1) push_subq(@q2) */ a.sql_id, a.plan_hash_value, count(*) "ExecTimes",
round(sum(ELAPSED_TIME_DELTA)/10e6,1) "ElapsedTime", round(sum(ELAPSED_TIME_DELTA)/10e6/count(*),1) "AvgElaTime"
from dba_hist_sqlstat a, ( select /*+ no_unnest qb_name(q2) */ sql_id
from (select /*+ no_unnest qb_name(q1) */ sql_id, plan_hash_value
from dba_hist_sqlstat
where plan_hash_value>0 and elapsed_time_delta>0
group by  sql_id, plan_hash_value)
group by sql_id having count(*) >1
) b
where a.sql_id = b.sql_id
group by a.sql_id, a.plan_hash_value
having round(sum(ELAPSED_TIME_DELTA)/10e6/count(*),1) >0
order by 3 desc)
) where rownum 1 then
wm := '  -  Multiplexed';
else
wm := '  -  Not Multiplexed';
end if;
dbms_output.put_line(rpad('Members in group '||x.group#,30,'.')||': '||x.members||wm);
sz := round(x.bytes/1024,1);
end loop;
dbms_output.put_line(rpad('Log File size (K)',30,'.')||': '||sz);
dbms_output.put_line('');
--
end;
/

set head off
prompt
prompt Controlfile Information
prompt =======================
select case when count(*) > 1 then rpad('Controlfiles',30,'.')||': Multiplexed'
else rpad('Controlfiles',30,'.')||': Not Multiplexed'
end
from V$controlfile;

prompt
prompt Standby Log File Information
prompt =============================
set serveroutput on
declare
sz number;
gp number;
begin
select count(distinct group#) into gp from V$standby_log;
select round(max(bytes)/1024,1) into sz from V$standby_log;
--
dbms_output.put_line(rpad('Number of groups',30,'.')||': '||gp);
dbms_output.put_line(rpad('Log File size (K)',30,'.')||': '||sz);
--
end;
/
prompt
Prompt  Report on  Number of Log Switches per Hour
Prompt  ==========================================
set head on
col "Day" for a10
col   "00" for a4
col   "01" for a4
col   "02" for a4
col   "03" for a4
col   "04" for a4
col   "05" for a4
col   "06" for a4
col   "07" for a4
col   "08" for a4
col   "09" for a4
col   "10" for a4
col   "11" for a4
col   "12" for a4
col   "13" for a4
col   "14" for a4
col   "15" for a4
col   "16" for a4
col   "17" for a4
col   "18" for a4
col   "19" for a4
col   "20" for a4
col   "21" for a4
col   "22" for a4
col   "23" for a4
set lines 200 pages 999
set colsep ""
SELECT trunc(first_time) "Day",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
TO_CHAR(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
FROM V$LOG_HISTORY
where trunc(first_time)>=trunc(sysdate -30 )
GROUP BY trunc(first_time)
order by  trunc(first_time);

set head on
Prompt
Prompt  Number and Size of Archive Log Generated Per Day
Prompt  ===========================================================
select trunc(first_time)" Date" ,round((sum(blocks)*block_size)/1024/1024)"Total Size(Mb)",
count(*) "# of Archive Logs Generated"
from V$archived_log
where trunc(first_time) >=trunc(sysdate -30)
group by trunc(first_time),block_size
order by trunc(first_time);

Prompt
Prompt Database I/O Footprint
Prompt =======================

set serveroutput on
declare
w_sql varchar2(4000);
begin
w_sql := '
begin
for x in (select sum(value) tot_time from V$osstat where stat_name like ''%TIME'') loop
for y in (select value io_time from V$osstat where STAT_NAME=''IOWAIT_TIME'') loop
dbms_output.put_line(rpad(''Approx I/O Wait'',30,''.'')||'': ''||round((y.io_time*100/x.tot_time),1)||''%'');
exit;
end loop;
exit;
end loop;
end;
';
execute immediate w_sql;
exception when others then null;
end;
/

Prompt
Prompt Miscellaneous Info
Prompt ===================
set head off;
SELECT rpad('CPU Count',30,'.')||': '
||value from V$parameter where name='cpu_count';
SELECT rpad('DB Size (GB)',30,'.')||': '
||round(sum(bytes)/(1024*1024*1024),2) from dba_data_files;
SELECT rpad('# Tablespaces',30,'.')||': '
||count(1) from dba_tablespaces;
SELECT rpad('# Datafiles',30,'.')||': '
||count(1) from dba_data_files;
SELECT rpad('# Schemas with objects',30,'.')||': '
||count(1) from ( select count(1) from dba_objects where OWNER not in ('SYS','SYSTEM','DBSNMP') group by owner);
SELECT rpad('Flash Recovery Area',30,'.')||': '
||decode( nvl(value,0),0,'Not configured','Configured') from V$parameter where NAME='db_recovery_file_dest_size';
SELECT rpad('Longest query execution time',30,'.')||': '
||max(MAXQUERYLEN)||' seconds'
from V$undostat where exists (select 1 from V$parameter where lower(name)='undo_management' and lower(value)='auto');
SELECT rpad('Max concurrent sessions',30,'.')||': '
||MAX_UTILIZATION from V$resource_limit where RESOURCE_NAME='sessions';
SELECT rpad('Max concurrent processes',30,'.')||': '
||MAX_UTILIZATION from V$resource_limit where RESOURCE_NAME='processes';
SELECT rpad('Max concurrent Txns',30,'.')||': '
||MAX_UTILIZATION from V$resource_limit where RESOURCE_NAME='transactions';
SELECT rpad('Max Parallelism',30,'.')||': '
||MAX_UTILIZATION from V$resource_limit where RESOURCE_NAME='parallel_max_servers';
select rpad('ASM in use?',30,'.')||': '||decode( count(*),0,'No','Yes')
from dba_data_files where substr(file_name,1,1)='+';
select rpad('Chained rows fetched',30,'.')||': '||round((tfcr*100/(tfbr+tfcr)),2) ||'%'
from
(select value tfcr from V$sysstat where name='table fetch continued row') a,
(select value tfbr from V$sysstat where name='table fetch by rowid') b;
select rpad('FILESYSTEMIO_OPTIONS',30,'.')||': '||nvl(value,'NONE')
from V$parameter where lower(name)='filesystemio_options';
select rpad('DISK_ASYNCH_IO',30,'.')||': '||nvl(value,'NONE')
from V$parameter where lower(name)='disk_asynch_io';

Prompt
Prompt RAC Statistics
Prompt ===============
set serveroutput on
BEGIN
IF dbms_utility.is_cluster_database THEN
dbms_output.put_line('Running in SHARED / RAC mode.');
dbms_output.put_line('  ');
dbms_output.put_line(chr(9)||'=====List of Instances=====');
for x in (select instance_number,host_name, instance_name from gV$instance) loop
dbms_output.put_line(chr(9)||x.instance_number||':'||x.host_name||':'||x.instance_name);
end loop;
ELSE
dbms_output.put_line('Running in SINGLE INSTANCE / EXCLUSIVE mode.');
END IF;
END;
/

set head on
set feedback off
set verify off
col inst_id format 999
col service_name format a30
col DBCPU format 999999999
col RedoSize format 999999999999999
col GC_CR_BlksRecd format 9999999999999999
col GC_CUR_Blks_Recd format 9999999999999999
col GC_BlksLost format 9999999999999999
col SesLogReads format 999999999999999999
col UserCalls format 999999999999999999
col Logons format 99999999
set lines 200 pages 999
select a.inst_id,
max(decode(a.name,'CPU used by this session',a.value,null)/(sysdate-startup_time)) DBCPU,
max(decode(a.name,'redo size',a.value,null)/(sysdate-startup_time)) RedoSize,
max(decode(a.name,'global cache cr blocks received',a.value,'gc cr blocks received',a.value,null)/(sysdate-startup_time)) GC_CR_BlksRecd,
max(decode(a.name,'global cache current blocks received',a.value,'gc current blocks received',a.value,null)/(sysdate-startup_time)) GC_CUR_BLKS_RECD,
max(decode(a.name,'global cache blocks lost',a.value,'gc blocks lost',a.value,null)/(sysdate-startup_time)) GC_BlksLost,
max(decode(a.name,'session logical reads',a.value,null)/(sysdate-startup_time)) SesLogReads,
max(decode(a.name,'user calls',a.value,null)/(sysdate-startup_time)) UserCalls,
max(decode(a.name,'logons cumulative',a.value,null)/(sysdate-startup_time)) Logons
from gV$sysstat a, gV$instance b, V$parameter c
where a.inst_id=b.inst_id
and c.name='cluster_database' and c.value='TRUE'
group by a.inst_id
order by inst_id;

prompt
Prompt Database Registry Details
Prompt ==========================

set pages 9999
set lines 180
col comp_name for a50
col status for a20
col comp_id for a20
select comp_id,comp_name,version,status,modified from dba_registry;

Prompt
Prompt Database PSU/CPU Details
Prompt ==========================
column action format a15
column action_time format a30
column comments format a35
column action format a20
set linesize 300
select comments,action_time,action
from
(select action,action_time,comments
from sys.registry$history
where action in ('CPU','APPLY')
order by action_time desc)
where comments 'view recompilation';

Prompt Top 20 wait events
Prompt =======================
col event format a40
col wait_class format a40
set feedback off verify off
set lines 200 pages 99
select * from
(
select substr(event,1,40) event, round(TIME_WAITED/100,1) time_secs, TOTAL_WAITS
from V$system_event
where event not in (select name from V$event_name where WAIT_CLASS='Idle')
order by TIME_WAITED desc
)
where rownum 0
GROUP BY wait_class
UNION
SELECT stat_name wait_class,
ROUND ((SUM(VALUE) / 1000000), 1) time_secs
FROM gV$sys_time_model
WHERE stat_name IN ('background cpu time', 'DB CPU')
group by stat_name
)
ORDER BY time_secs DESC;

prompt
Prompt  Buffer Cache / Throughput figures
Prompt  ==================================
col metric_name format a40
col minval format 9999999.99
col avgval format 9999999.99
col maxval format 9999999.99
SELECT METRIC_NAME,
ROUND(MIN(MINVAL),2) minval,
ROUND(AVG(AVERAGE),2) avgval,
ROUND(MAX(MAXVAL),2) maxval
FROM DBA_HIST_SYSMETRIC_SUMMARY
WHERE BEGIN_TIME > SYSDATE-7
AND METRIC_NAME in ('User Commits Per Sec','Buffer Cache Hit Ratio','User Transaction Per Sec')
GROUP BY METRIC_NAME;

select round( (gets-(phyr-phyrd))*100/gets,1) "Buffer Cache Hit%"
from
(select value phyr from V$sysstat where name='physical reads') a,
(select sum(value) phyrd from V$sysstat where name in ('physical reads direct','physical reads direct (lob)')) b,
(select value gets from V$sysstat where name='session logical reads') c;

set lines 200
set serveroutput on size 1000000
set long 1000000
prompt
prompt Peak SQL Activity -- 7 Days
prompt ===========================
prompt Peak SQL Activity Periods for the last 7 days. 10g and above only.
prompt The top 3 periods for each day are displayed with the SQL Execute CPU time spent in mins
prompt Note: SQL Execute CPU time is a factor of Number of sessions and wall clock time.
prompt
declare
w_b_t date;
w_e_t date;
w_str varchar2(500);
BEGIN
for x in (select distinct a.instance_number, trunc(begin_interval_time) bt --, trunc(end_interval_time) et
from dba_hist_snapshot a, V$instance b
where begin_interval_time>=sysdate-7 and a.instance_number=b.instance_number
order by trunc(begin_interval_time) ) loop
--
w_str := rpad(x.bt,20,'.')||': ';
--
for y in ( select to_char(begin_interval_time,'hh24:mi') bt, to_char(end_interval_time,'hh24:mi') et, cpu
from (
select begin_interval_time, end_interval_time, sum(round(CPU_TIME_DELTA/10e6)) cpu
from dba_hist_sqlstat a, dba_hist_snapshot b
where trunc(b.begin_interval_time) = x.bt
and a.snap_id=b.snap_id
and b.instance_number=x.instance_number
and a.instance_number=b.instance_number
group by begin_interval_time, end_interval_time
order by sum(round(CPU_TIME_DELTA/10e6)) desc)
where rownum sysdate-7
and job_name=''GATHER_STATS_JOB''
order by ACTUAL_START_DATE desc';
elsif substr(x.banner,17,2) >= 11 then
w_str := 'SELECT CLIENT_NAME,STATUS  from DBA_AUTOTASK_CLIENT where client_name=''auto optimizer stats collection''';
w_str2 := 'select CLIENT_NAME,JOB_START_TIME,JOB_DURATION
from DBA_AUTOTASK_JOB_HISTORY
where CLIENT_NAME=''auto optimizer stats collection''
and trunc(JOB_START_TIME) > sysdate - 7';
end if;
--
execute immediate w_str into w_job, w_status;
dbms_output.put_line( rpad('Statistics collection method',30,'.')||': '||w_job||' - '||w_status);
--
open c for w_str2;
loop
fetch c into w_jname, w_st_date, w_runtime;
if not w_hdr then
dbms_output.put_line(rpad('Name',20)||rpad('Start Date',20)||rpad('Ela.Time',20) );
dbms_output.put_line( rpad('=',75,'=') );
w_hdr := true;
end if;
--
dbms_output.put_line(rpad(w_jname,20)||rpad(w_st_date,20)||rpad(w_runtime,20) );
exit when c%notfound;
end loop;
--
end loop;
--

end;
/

prompt
prompt Cluster Interconnect Details
Prompt ============================
SET SERVEROUTPUT ON
declare
w_name varchar2(15);
w_ip_address varchar2(16);
w_is_public varchar2(13);
w_source varchar2(31);
BEGIN
IF DBMS_UTILITY.is_cluster_database THEN
select  name, ip_address, is_public, source into
w_name,w_ip_address,w_is_public,w_source from V$cluster_interconnects;
dbms_output.put_line(rpad('Name',10)||rpad('IP Address',15)|| rpad('Public',20)||rpad('Source',25));
dbms_output.put_line(rpad(' ',70,'-'));
dbms_output.put_line(rpad(w_name,10)||rpad(w_ip_address,15)||rpad(w_is_public,15)||rpad(w_source,25));
ELSE
DBMS_OUTPUT.put_line('Not a RAC Database');
END IF;
END;
/

PROMPT
PROMPT Top 20 wait events
PROMPT =======================
col event format a40
set feedback off verify off
set lines 200 pages 99
select * from
(
select substr(event,1,40) event, TIME_WAITED, TOTAL_WAITS
from V$system_event
where event not in (
'dispatcher timer',
'lock element cleanup',
'Null event',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'pipe get',
'PL/SQL lock timer',
'pmon timer',
'rdbms ipc message',
'slave wait',
'smon timer',
'SQL*Net break/reset to client',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net more data to client',
'virtual circuit status',
'WMON goes to sleep'
)
order by TIME_WAITED desc
)
where rownum sysdate-7 and a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and a.instance_number=c.instance_number
order by CPU_TIME_DELTA desc) where rownum < 2;
--
select nvl(max(snap_id),0)
into w1
from dba_hist_snapshot a, V$instance b
where snap_idtname,type=>'IMPLEMENTATION');
--dbms_output.put_line(rec);
--rec := DBMS_ADVISOR.GET_TASK_REPORT (task_name=>tname,type=>'TEXT',level=>'BASIC',section=>'ALL');
--dbms_output.put_line(rec);
select sum(impact) into w_tot from dba_advisor_findings where TASK_ID=w_task_id;
for x in ( select type,message,impact,IMPACT_TYPE from dba_advisor_findings
where TASK_ID=w_task_id and impact>0 and type='PROBLEM'
order by impact desc
) loop
dbms_output.put_line( rpad('Impact',20,'.')||': '||round((x.impact*100/w_tot),1)||'% DB Time');
for i in 1..(ceil(length(x.message)/100)) loop
dbms_output.put_line( rpad('Remark',20,'.')||': '|| substr(x.message,((i-1)*100) + 1,100) );
end loop;
dbms_output.put_line('======================================');
end loop;
--

null;
END;
/

Advertisements