|
Get Complete OSM Toolkit (free) |
|
ashclp.sql
ashclpct.sql
ashp.sql
ashpctcpu.sql
ashpcte.sql
ashpctecl.sql
ashpctes.sql
ashpcts.sql
ashrt.sql
ashrt_old.sql
ashsp.sql
ashspct.sql
ashsqlcl.sql
ashsqle.sql
ashsqlpctcl.sql
ashsqlpctcpu.sql
ashsqlpcte.sql
ashsqlpcts.sql
ashsqlpctt.sql
ashsqls.sql
avgexplore.sql
bc.sql
bc7.sql
bcmap.sql
bcmap7.sql
bcobjfb.sql
chr.sql
clone.sql
cr_rtsp.sql
cu.sql
cycledb6
cycledb9
dboc.sql
delete.me
dev_list.txt
dfio.sql
dfl.sql
diag.sql
ds.sql
ds7.sql
dsn.sql
eval1.sql
event_type.sql
event_type_nc.sql
fgidx.sql
fgtbl.sql
hashchk.sql
idx.sql
iosum.sql
iosum9.sql
iosum9x.sql
iosumx.sql
ip.sql
ipcbc.sql
ipx.sql
irtviews.sql
istat.sql
latch.sql
latch8.sql
latch_old.sql
latchchild.sql
latchclass.sql
latchx.sql
lc.sql
lock.sql
loghist8.sql
lruno.sql
mkodo.sql
mkodo_ouch.sql
mts.sql
mysess.sql
objfb.sql
objloc.sql
ogbigld.sql
ogcrobj.sql
ogdoit.sql
ogobjlst.sql
ogobjset.sql
oracpu.sql
oracputext.sql
oracpux.sql
oracpux1.sql
oracpux2.sql
oscpux.sql
oscpux1.sql
oscpux10.sql
oscpux2.sql
oscpux210.sql
osmclear.sql
osmi.sql
osmprep.sql
osmtitle.sql
osmtitlel.sql
osmtitlell.sql
osmtitlem.sql
osmtitles.sql
rbs.sql
rdohist.sql
readme.txt
rlog.sql
rtc.sql
rtcx.sql
rtio.sql
rtow.sql
rtpctx.sql
rtsess.sql
rtsess9.sql
rtsessx.sql
rtsum.sql
rtsys.sql
rtsysx.sql
rtsysx8.sql
sessinfo.sql
sessinfo9.sql
sesstat.sql
sga.sql
simsql1.sql
simsql2.sql
snap_stats.sql
spspinfo.sql
sqls1.sql
sqls18.sql
sqls19.sql
sqls2.sql
sqls3.sql
sqls4.sql
stu.sql
supers.sql
swenq.sql
swenqc.sql
swenqnew.sql
swenqx.sql
swhist.sql
swhist.sql.sav
swhistx.sql
swhistx.sql.sav
swpct.sql
swpctidle.sql
swpctx.sql
swpctxidle.sql
swsessid.sql
swsid.sql
swsw.sql
swswc.sql
swswp.sql
swsys.sql
sysstat.sql
tbfsum.sql
tcsizing.sql
tcstats.sql
test.sql
timechk.sql
topcpu
topdml.sql
tp.sql
tp9.sql
tsmap.sql
tss.sql
undo.sql
users.sql
-- ********************************************************************
-- * Copyright Notice : (c)1999,2000,2001 OraPub, Inc.
-- * Filename : fgtbl.sql
-- * Author : Craig A. Shallahamer
-- * Original : 10-nov-99
-- * Last Update : 21-mar-01
-- * Description : Get TABLE fragmentation details for a given object.
-- * Usage : start fgtbl.sql ‹owner› ‹table name›
-- ********************************************************************
def owner=&&1
def tnm=&&2
prompt
prompt If this script hangs, it is probably becasue the object has not been analyzed.
prompt
set echo off verify off heading off
set termout off
col val4 new_val hwm_blocks noprint
col val5 new_val above_hwm noprint
col val6 new_val row_chains noprint
col val7 new_val row_size noprint
col val7a new_val pct_used noprint
col val7b new_val pct_free noprint
col val8 new_val num_rows noprint
col val9 new_val row_chains_pct noprint
select num_rows val8,
blocks val4,
empty_blocks val5,
chain_cnt val6,
avg_row_len val7,
pct_used val7a,
pct_free val7b,
100*chain_cnt/num_rows val9
from dba_tables
where table_name = upper('&tnm')
and owner = upper('&owner');
col val9 new_val block_size noprint
select value val9
from v$parameter
where name = 'db_block_size';
col val10a new_val blocks_alloc noprint
col val10b new_val bytes_alloc noprint
col val10e new_val hwm_bytes noprint
col val10f new_val bytes_used noprint
select &hwm_blocks+&above_hwm val10a,
(&hwm_blocks+&above_hwm)*&block_size/1024/1024 val10b,
(&hwm_blocks*&block_size)/1024/1024 val10e,
(&num_rows*&row_size)/1024/1024 val10f
from dual;
col val11a new_val blocks_pct_used noprint
col val11b new_val bytes_pct_used noprint
select 100*&hwm_blocks/&blocks_alloc val11a,
100*&num_rows*&row_size/&hwm_bytes/1024/1024 val11b
from dual;
col val12 new_val sf noprint
select count(*) val12
from dba_extents
where segment_name= upper('&tnm')
and owner = upper('&owner');
set termout on
set echo off feedback off verify off
col bogus format 999,999,999 fold_after
select 'Owner : '||'&owner' bogus,
'Table name : '||'&tnm' bogus,
'pct_free : '||&pct_free bogus,
'pct_used : '||&pct_used bogus,
'Number of extents : '||&sf||' ‹-- Segment Fragmentation' bogus,
'Rows : '||&num_rows bogus,
'Row size : '||&row_size bogus,
'Rows frag:migration: '||&row_chains bogus,
'Row % frag:migr. : '||&row_chains_pct||'% ‹-- Row Fragmentation' bogus,
'DB block size : '||&block_size bogus,
'Blocks alloc : '||&blocks_alloc bogus,
'Block HWM : '||&hwm_blocks bogus,
'% alloc used by HWM: '||&blocks_pct_used||'%' bogus,
'MB alloc : '||&bytes_alloc||'MB' bogus,
'MB HWM : '||&hwm_bytes||'MB' bogus,
'MB used : '||&bytes_used||'MB' bogus,
'% HWM bytes used : '||&bytes_pct_used||'% ‹-- Block Fragmentation' bogus
from dual;
prompt *** The table &owner..&tnm must have been recently analyzed for accuracy
prompt *** You may need to ANALYZE TABLE &owner..&tnm DELETE STATISTICS
set feedback on
Know what's important before it's too late!
OraPub's
Performance Training
is like no other...
More Class Pics...
Get student testimonials!
Craig Shallahamer's Blog