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;
(
geocities.com/md_seraphin/toolbox) (
geocities.com/md_seraphin)