# db_stats_before_and_after.ksh password database 

sqlplus -s sys/$1@$2 << EOF

set lines 300 pages 10000 trimout on trimspool on numwidth 15 echo off feedback 2 
prompt ==========================================================
prompt ==========================================================
prompt Database Performance Report
set heading off
select 'Run time: '||to_char(sysdate,'dd-Mon-yy hh24:mi:ss') run_time from dual;
set heading on
prompt ==========================================================
prompt ==========================================================
prompt
prompt
prompt

--
-- PHYSICAL DBF IO
--
prompt ==========================================================
prompt Database I/O
set heading off
select 'Run time: '||to_char(sysdate,'dd-Mon-yy hh24:mi:ss') run_time from dual;
set heading on

column name format a40 trunc
column file# format 999         heading fl#
column phyrds format 99,999,999 heading 'Phy Rds'
column phywrts format 999,999,999       heading 'Phy Wrts'
column phyblkrd format 99,999,999       heading 'Blk Rds'
column phyblkwrt format 99,999,999      heading 'Blk Wr'
column total_io format 99,999,999       heading 'Total IO'

break on report
compute sum of phyrds on report
compute sum of phywrts on report
compute sum of phyblkrd on report
compute sum of phyblkwrt on report
compute sum of total_io on report

select d.file#, substr(name,1,10)||'..'||substr(name,length(name)-28) name,
       phyrds, phywrts, phyblkrd, phyblkwrt,
       phyrds+phywrts total_io
from v\$dbfile d, v\$filestat f
where d.file# = f.file#
order by d.file#
/

clear bre

--
-- CACHED BLOCKS
--

prompt ==========================================================
prompt Cached Blocks
set heading off
select 'Run time: '||to_char(sysdate,'dd-Mon-yy hh24:mi:ss') run_time from dual;
set heading on

column owner format a20 trunc
column segment_name format a30 trunc
column segment_type format a10 trunc

break on report
compute sum LABEL 'Blocks Cached' of number_cached on report

select e.owner, e.segment_name, e.segment_type, count(*) number_cached
from x\$bh x, dba_extents e
where x.dbafil = e.file_id
and e.block_id <= x.dbablk
and e.block_id+e.blocks >= x.dbablk
group by e.owner, e.segment_name, e.segment_type
order by number_cached desc
/
clear bre

-- Summary of Cached Blockes

break on report
compute sum of num on report
column dirty format a5

select   decode(state, 0, 'FREE', 1, 'XCUR', 2, 'SCUR', 3, 'CR', 4, 'READ', 5, 'MREC', 6, 'IREC') status,
         decode(bitand(flag,1), 0, 'N', 'Y') dirty, count(*) num
from x\$bh x
group by to_char(sysdate,'dd-mm-yy hh24:mi:ss'), decode(state, 0, 'FREE', 1, 'XCUR', 2, 'SCUR', 3, 'CR', 4, 'READ', 5, 'MREC', 6, 'IREC'),
         decode(bitand(flag,1), 0, 'N', 'Y')
/
clear break

--
-- SYSTEM WAITS
--

prompt ==========================================================
prompt System Waits
set heading off
select 'Run time: '||to_char(sysdate,'dd-Mon-yy hh24:mi:ss') run_time from dual;
set heading on

select * from v\$waitstat
/

--
-- SYSTEM EVENTS
--

prompt ==========================================================
prompt System Events
set heading off
select 'Run time: '||to_char(sysdate,'dd-Mon-yy hh24:mi:ss') run_time from dual;
set heading on
column average_wait format 9999999999.999

select * from v\$system_event
/

--
-- DATAFILE WAITS
--

prompt ==========================================================
prompt Datafile Waits
set heading off
select 'Run time: '||to_char(sysdate,'dd-Mon-yy hh24:mi:ss') run_time from dual;
set heading on

column name format a50 trunc
column count format 999,999,999
column time format 999,999
column file# format 999

break on report
compute sum of count on report
compute sum of time on report

select file#, name, count, time
from x\$kcbfwait, v\$dbfile
where indx+1 = file#
order by count
/

--
-- ROLLBACK SEGMENT STATS
--

prompt ==========================================================
prompt Rollback Segment Statistics
set heading off
select 'Run time: '||to_char(sysdate,'dd-Mon-yy hh24:mi:ss') run_time from dual;
set heading on

select * from v\$rollstat
/

--
-- SYSTEM STATISTICS
--

prompt ==========================================================
prompt System Statistics
set heading off
select 'Run time: '||to_char(sysdate,'dd-Mon-yy hh24:mi:ss') run_time from dual;
set heading on

select * from v\$sysstat
/

--
-- REDO LOGS
--

prompt ==========================================================
prompt Redo Logs
set heading off
select 'Run time: '||to_char(sysdate,'dd-Mon-yy hh24:mi:ss') run_time from dual;
set heading on
column member format a80

select * from v\$log
/
select * from v\$logfile
/

--
-- REDO LOG STATS
--

prompt ==========================================================
prompt Redo Log Stats
set heading off
select 'Run time: '||to_char(sysdate,'dd-Mon-yy hh24:mi:ss') run_time from dual;
set heading on

column name format a20
column immediate_gets heading 'Immed|Gets'
column immediate_misses heading 'Immed|Miss'
column ratio format 999.9

set head off

select 'Small Entry Max Size --> '||s.value,
       'Log Simultaneous Copies --> '||l.value,
      'CPU_COUNT --> '||c.value
from v\$parameter s, v\$parameter l, v\$parameter c
where s.name = 'log_small_entry_max_size'
 and l.name = 'log_simultaneous_copies'
 and c.name = 'cpu_count'
/
set head on

select ln.name, gets, misses, misses/decode(gets,0,1,gets)*100 ratio,
   immediate_gets, immediate_misses,
   immediate_misses/decode(immediate_gets,0,1,immediate_gets)*100 ratio
from v\$latch l, v\$latchname ln
where ln.name IN ('redo allocation','redo copy')
and ln.latch# = l.latch#
/

--
-- INITIALISATION SETTINGS
--

prompt ==========================================================
prompt Initialisation Settings

column KSPPINM format a35
column KSPPDESC format a45 word_wrap
column KSPPSTVL format a30
column def format a2

select a.KSPPINM, a.KSPPDESC, b.KSPPSTVL, decode(b.KSPPSTDF,'TRUE','Y','FALSE','N',b.KSPPSTDF) def
from x\$ksppi a, x\$ksppcv b
where a.INDX = b.INDX
and b.KSPPSTDF='FALSE'
order by 1
/


prompt ==========================================================
prompt ==========================================================
prompt End of Database Performance Report
set heading off
select 'Run time: '||to_char(sysdate,'dd-Mon-yy hh24:mi:ss') run_time from dual;
set heading on
prompt ==========================================================
prompt ==========================================================

exit

EOF