REM
REM Author: Mohit Dubey 
REM Visit http://www.geocities.com/md_seraphin for more goodies!
REM
REM This program is distributed under the GNU Public License Version 2
REM with the additional privisio that the original author's name and
REM contact details must be retained as-is in any modified or copied
REM versions of this program.
REM


set lines 132 termout off echo off feedback off sqlnumber off sqlprompt "" trimspool on escape \

column owner format a10 heading 'Owner'
column segment_name format a30 heading 'Segment Name'
column tablespace_name format a18 heading 'Tablespace Name'
column segment_type format a10 heading 'Type'
column bytes format 999,999,999,990 heading 'Size'
column extents format 990 heading 'Exts'
column next_extent format 999,999,990 heading 'Next'
column pct_increase format 990 heading 'PCT'
column ratio format 90.999 heading 'Ratio'
column count format 9,990 heading 'Count'
column addr format 9,990 heading 'Addr'
column indx format 9,990 heading 'Indx'
column gets format 999,999,990 heading 'Gets'
column waits format 999,999,990 heading 'Waits'
column bytes format 9,999,999,990 heading 'Bytes'
column value format 9,999,999,990 heading 'Value'
column total_gets format 99,999,990 heading 'Total Gets'
column misses format 99,999,990 heading 'Misses'
column immediate_gets format 999,999,990 heading 'Immediate|Gets'
column immediate_misses format 999,999,990 heading 'Immediate|Misses'
column class format A24 heading 'Class'
column name format A24 heading 'Name'
column phyrds format 999,999,990 heading 'Physical|Reads'
column phywrts format 999,999,990 heading 'Physical|Writes'
column CACHE# format 99,999,990 heading 'Cache No'
column TYPE format A11 heading 'Type'
column SUBORDINATE# format 99,999,990 heading 'Subordinate No'
column PARAMETER format A20 heading 'Parameter'
column COUNT format 99,999,990 heading 'Count'
column USAGE format 99,999,990 heading 'Usage'
column FIXED format 99,999,990 heading 'Fixed'
column GETMISSES format 9,999,990 heading 'Get|Misses'
column SCANS format 9,999,990 heading 'Scans'
column SCANMISSES format 9,999,990 heading 'Scan|Misses'
column SCANCOMPLETES format 9,999,990 heading 'Scan|Completes'
column MODIFICATIONS format 9,999,990 heading 'Modifications'
column FLUSHES format 99,999,990 heading 'Flushes'
column sum(pins) format 999,999,990 heading 'Executions'
column sum(value) format 999,999,999,990 heading 'Total Value'
column sum(reloads) format 999,999,990 heading 'Cache misses|while executing'

break on today
column today new_value _date
column dbnam new_value _dbname
select to_char(sysdate,'dd Mon yyyy hh24:mi:ss') today,
name dbnam from v$database;


set echo off feedback off termout off header on ttitle off pages 0

spool dbstat.log

PROMPT
PROMPT *****************************************************************
PROMPT * Tuning: SGA Statistics
select name,
       sgasize/1024/1024 "Allocated (M)",
       bytes/1024 "Free (K)",
       round(bytes/sgasize*100, 2) "% Free"
from   (select sum(bytes) sgasize from v$sgastat) s, v$sgastat f
where  f.name = 'free memory';

PROMPT 
PROMPT *****************************************************************
PROMPT * Tuning: buffer cache
select 'Buffer Hit Ratio' "Statistic",
(1-(p.value/(d.value+c.value)))*100 ratio
from v$sysstat d,v$sysstat c,v$sysstat p
where d.name ='db block gets' 
and c.name ='consistent gets' 
and p.name ='physical reads';

PROMPT 
PROMPT *****************************************************************
PROMPT * Tuning: library cache
select 'Library Cache ' "Statistic",sum(pins) "Pins",
sum(reloads) "Reloads" , (sum(reloads)*100) / sum(pins) "Ratio"
from v$librarycache group by 'Library Cache ';


PROMPT 
PROMPT *****************************************************************
PROMPT * Tuning: library cache hash table size
select
  least(8, ceil(log(2, ceil(count(*) / 509))))  "INDEX"
from
  -- sys.x$kglob  o
  sys.p_x$kglob  o
where
  o.inst_id = userenv('Instance') and
  o.kglhdadr = o.kglhdpar;


PROMPT
PROMPT *****************************************************************
PROMPT * INFORMATIONAL: "HOT" latches
column object_name format a60
select /*+ ordered */
  l.child#  latch#,
  o.kglnaobj  object_name
from
  ( select
      count(*)  latches,
      avg(sleeps)  sleeps
    from
      v$latch_children
    where
      name = 'library cache'
  )  a,
  v$latch_children  l,
  ( select
      s.buckets *
      power(
        2,
        least(
          8,
          ceil(log(2, ceil(count(*) / s.buckets)))
        )
      )  buckets
    from
      ( select
	  decode(y.ksppstvl,
	    0, 509,
	    1, 1021,
	    2, 2039,
	    3, 4093,
	    4, 8191,
	    5, 16381,
	    6, 32749,
	    7, 65521,
	    8, 131071,
            509
	  )  buckets
	from
	  -- sys.x$ksppi  x,
	  -- sys.x$ksppcv  y
	  sys.p_x$ksppi  x,
	  sys.p_x$ksppcv  y
	where
	  x.inst_id = userenv('Instance') and
	  y.inst_id = userenv('Instance') and
	  x.ksppinm = '_kgl_bucket_count' and
	  y.indx = x.indx
      )  s,
      -- sys.x$kglob  c
      sys.p_x$kglob  c
    where
      c.inst_id = userenv('Instance') and
      c.kglhdadr = c.kglhdpar
    group by
      s.buckets
  )  b,
  -- sys.x$kglob  o
  sys.p_x$kglob  o
where
  l.name = 'library cache' and
  l.sleeps > 2 * a.sleeps and
  mod(mod(o.kglnahsh, b.buckets), a.latches) + 1 = l.child# and
  o.inst_id = userenv('Instance') and
  o.kglhdadr = o.kglhdpar;


PROMPT 
PROMPT *****************************************************************
PROMPT * Tuning: data dictionary cache
break on report
set pages 40
compute sum of gets on report
compute sum of getmisses on report
select parameter , gets , getmisses , scans , scanmisses
from v$rowcache
where getmisses !=0 or scanmisses !=0 ;

clear break
break on report
compute sum of phyrds on report
compute sum of phywrts on report
column name format A38 heading 'Name'
PROMPT 
PROMPT *****************************************************************
PROMPT * INFORMATIONAL: Disk I/O 
select name , phyrds , phywrts
from v$datafile df , v$filestat fs 
where df.file# = fs.file#
order by name;

PROMPT *
PROMPT * Waits by file...
SELECT name, count 
  -- FROM x$kcbfwait, v$datafile
  FROM sys.p_x$kcbfwait, v$datafile
 WHERE indx + 1 = file#;

PROMPT 
PROMPT *****************************************************************
PROMPT * Tuning: Rollback Segment Contention
select class , count 
from v$waitstat
where upper(class) like '%UNDO%';

PROMPT 
PROMPT *****************************************************************
PROMPT * INFORMATIONAL: Total requests for Data
select sum(value) from v$sysstat
where name in ('db block gets','consistent gets');

PROMPT
PROMPT *****************************************************************
PROMPT * INFORMATIONAL: Dynamic extension
SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME='RECURSIVE CALLS' ;


column name format A24 heading 'Name'
PROMPT 
PROMPT *****************************************************************
PROMPT * Tuning: Free List Contention 
select class , count waits 
from v$waitstat
where class = 'free list';

PROMPT 
PROMPT *****************************************************************
PROMPT * Tuning: Redo Log Contention 
column name format A24 heading 'Name'
select name , value 
from v$sysstat 
where name ='redo log space requests';

PROMPT 
PROMPT *****************************************************************
PROMPT * Redo Log Buffer Latches
select name latch_name, gets, misses,
round(decode(gets-misses,0,1,gets-misses)/
decode(gets,0,1,gets),3) hit_ratio
from v$latch where name = 'redo allocation';
column latch_name format a20
select name latch_name, immediate_gets, immediate_misses,
round(decode(immediate_gets-immediate_misses,0,1,
immediate_gets-immediate_misses)/
decode(immediate_gets,0,1,immediate_gets),3) hit_ratio
from v$latch where name = 'redo copy';
PROMPT
PROMPT * Current values for associated parameters are:
column name format a30
column value format a10
select name,value from v$parameter where name in
('log_small_entry_max_size','log_simultaneous_copies',
'cpu_count');
PROMPT
PROMPT * INFORMATIONAL: Redo log buffer latches
PROMPT * Two types of latches control the access of the log buffer,
PROMPT * 'redo allocation latch' and 'redo copy latch'.
select ln.name , gets ,misses , immediate_gets , immediate_misses 
from v$latch l , v$latchname ln
where ln.name in ( 'redo allocation' , 'redo copy' )
and ln.latch# = l.latch#;

col value format 999,999,999,999
PROMPT 
PROMPT *****************************************************************
PROMPT * Tuning: Sort Areas 
select name , value 
from v$sysstat 
where name in ('sorts (memory)' , 'sorts (disk)');

PROMPT
PROMPT * The number of disk sorts that have been performed, their average size, 
PROMPT * and the peak number of concurrent disk sorts are as below...
column average_size format a12
select /*+ ordered */
  s.disk_sorts,
  decode(s.disk_sorts, 0, 'n/a',
    lpad(
      ceil((nvl(w1.kwrites, 0) + nvl(w2.kwrites, 0)) / s.disk_sorts) || 'K',
      12
    )
  )  average_size,
  least(s.disk_sorts, p.peak)  peak_concurrent
from
  (
    select
      value  disk_sorts
    from
      v$sysstat
    where
      name = 'sorts (disk)'
  )  s,
  (
    select /*+ ordered */
      sum(i.kcfiopbw * e.febsz) / 1024  kwrites
    from
      (
	select distinct
	  tempts#
	from
	  -- sys.user$
	  sys.p_user$
	where
	  type# = 1
      )  u,
      -- sys.file$  f,
      -- sys.x$kcfio  i,
      -- sys.x$kccfe  e
      sys.p_file$  f,
      sys.p_x$kcfio  i,
      sys.p_x$kccfe  e
    where
      i.inst_id = userenv('Instance') and
      e.inst_id = userenv('Instance') and
      f.ts# = u.tempts# and
      i.kcfiofno = f.file# and
      e.fenum = i.kcfiofno
  )  w1,
  (
    select /*+ ordered use_nl(h) */
      sum(i.kcftiopbw * e.tfbsz) / 1024  kwrites
    from
      (
	select distinct
	  tempts#
	from
	  -- sys.user$
	  sys.p_user$
	where
	  type# = 1
      )  u,
      -- sys.x$ktfthc  h,
      -- sys.x$kcftio  i,
      -- sys.x$kcctf  e
      sys.p_x$ktfthc  h,
      sys.p_x$kcftio  i,
      sys.p_x$kcctf  e
    where
      h.inst_id = userenv('Instance') and
      i.inst_id = userenv('Instance') and
      e.inst_id = userenv('Instance') and
      h.ktfthctsn = u.tempts# and
      i.kcftiofno = h.ktfthctfno and
      e.tfnum = i.kcftiofno
  )  w2,
  (
    select /*+ ordered */
      sum(l.max_utilization)  peak
    from
      (
	select /*+ ordered */ distinct
	  t.contents$
	from
	  (
	    select distinct
	      tempts#
	    from
	      -- sys.user$
	      sys.p_user$
	    where
	      type# = 1
	  )  u,
	  -- sys.ts$  t
	  sys.p_ts$  t
	where
	  t.ts# = u.tempts#
      )  y,
      v$resource_limit  l
    where
      (y.contents$ = 0 and l.resource_name = 'temporary_table_locks') or
      (y.contents$ = 1 and l.resource_name = 'sort_segment_locks')
  )  p;



select
	username,
	sid,
	ktssoses sess_addr,
	ktssosno sess_num,
	prev_sql_addr sqladdr,
	prev_hash_value sqlhash,
	ktssotsn tablespace,
	decode(ktssocnt,
		0, 'PERMANENT',
		1, 'TEMPORARY'
	) type,
	decode(ktssosegt,
		1, 'SORT',
		2, 'HASH',
		3, 'DATA',
		4, 'INDEX',
		5, 'LOB_DATA',
		6, 'LOB_INDEX' ,
		   'UNDEFINED'
	) segtype,
	ktssofno segfile#,
	ktssobno segblock#,
	ktssoexts extents,
	ktssoblks blocks,
	ktssorfno segrelf#
from 
	-- sys.x$ktsso,
	sys.p_x$ktsso,
	v$session 
where
	ktssoses = v$session.saddr 
and	ktssosno = v$session.serial#    
-- and	sys.x$ktsso.inst_id = sys_context('userenv','instance')
and	sys.p_x$ktsso.inst_id = sys_context('userenv','instance');

PROMPT


break on owner skip 1 on segment_type skip 1
set autocommit on pages 1000
create table calc_space 
(tablespace_name,space,type) as
select tablespace_name,sum(bytes) ,'Allocated'
from dba_data_files 
group by tablespace_name;

insert into calc_space
select tablespace_name,sum(bytes) , 'Free'
from dba_free_space 
group by tablespace_name;


ttitle '**>> Fragmentation report for '_dbname' on '_date 
set lines 75 pages 66 head on feed off
select owner,segment_type,segment_name,extents,bytes
from dba_segments
where extents > 2 
and owner not like 'SYS%' order by 1,2,3;

ttitle '**>> Report on Data File Sizes for '_dbname' on '_date 
break on tablespace_name skip 1
column type format a10 heading 'Type'
column space format 9,999,999,990 heading 'Space'
column allocated format 9,999,999,990 heading 'Space'
column free format 9,999,999,990 heading 'Space'
select tablespace_name , type,space
from calc_space 
order by 1,2;

clear break
ttitle '**>> Report on Actual Space Used for '_dbname' on '_date
break on owner skip 1 
column segment_type format a15
select owner,tablespace_name,segment_type,sum(bytes) "Bytes" 
from dba_segments
group by owner,tablespace_name,segment_type;

drop table calc_space;

ttitle '**>> Report on tables with Increase > 0% for '_dbname' on '_date
select owner,segment_name,segment_type,pct_increase,extents,next_extent
from dba_segments
where pct_increase > 0 and owner not like 'SYS%'
order by 1,2;

column owner format a10 heading 'Owner'
column object_type format a21 heading 'Object Type'
column status format a10 heading 'Status'
column total format 99990 heading 'No|Obj'
break on owner skip 1 
ttitle '**>> Reconciliation report for '_dbname' on '_date 
select owner,object_type,status,count(*) "total" 
from dba_objects
group by owner,object_type,status;

ttitle '**>> Constraint Status Report for '_dbname' on '_date 
select owner,CONSTRAINT_TYPE,status,count(*)
from dba_constraints 
group by owner,CONSTRAINT_TYPE,status;

spool off 

set sqlnumber on sqlprompt "SQL>"
set pages 40 head on feed on echo on lines 120 termout on 
exit;