Oracle High Performance Tuning for 9i and 10g - page 140

< Day Day Up >

Indexes

set wrap off linesize 132 pages 80; column pos format 990; column col format a10; column ind format a25; column tab format a25; column typ format a20; column tbs format a25; select t.table_name "Tab" ,decode(t.index_type,'NORMAL','BTree','BITMAP','Bitmap' ,'FUNCTION-BASED NORMAL','Function-Based BTree' ,t.index_type) "Typ" ,t.index_name "Ind", c.column_name "Col", c.column_position "Pos" ,t.tablespace_name "Tbs" from user_indexes t, user_ind_columns c where t.table_name = c.table_name and t.index_name = c.index_name and t.index_type not in ('IOT – TOP','LOB') order by t.table_name, t.index_name, c.column_position;

< Day Day Up >

Space in the Database

–database size SELECT 'Database Size' "*****" ,round(sum(round(sum(nvl(fs.bytes/1024/1024,0)))) / sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round (df.bytes/1024/1024 – sum(nvl(fs.bytes/1024/1024,0)))) * 100, 0) "%Free" ,round(sum(round(df.bytes/1024/1024 – sum(nvl(fs.bytes/1024/1024,0)))) / sum(round(sum (nvl(fs.bytes/1024/1024,0))) + round(df.bytes/1024/ 1024 – sum(nvl(fs.bytes/1024/1024,0)))) * 100, 0) "%Used" ,sum(round(sum(nvl(fs.bytes/1024/1024,0)))) "Mb Free" ,sum(round(df.bytes/1024/1024 – sum(nvl(fs.bytes/1024/1024,0)))) "Mb Used" ,sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round (df.bytes/1024/1024 – sum(nvl(fs.bytes/1024/1024,0)))) "Size" FROM dba_free_space fs, dba_data_files df WHERE fs.file_id(+) = df.file_id GROUP BY df.tablespace_name, df.file_id, df.bytes, df.autoextensible ORDER BY df.file_id; –tablespace size COL Tablespace FORMAT a16; SELECT df.tablespace_name "Tablespace" ,round((sum(nvl(fs.bytes,0))/ (df.bytes)) * 100) "%Free" ,round(((df.bytes – sum(nvl(fs.bytes,0))) / (df.bytes) ) * 100) "%Used" ,round(sum(nvl(fs.bytes/1024/1024,0))) "Mb Free" ,round(df.bytes/1024/1024 – sum(nvl(fs.bytes/1024/1024,0))) "Mb Used" FROM dba_free_space fs, dba_data_files df WHERE fs.file_id(+) = df.file_id GROUP BY df.tablespace_name, df.file_id, df.bytes, df.autoextensible ORDER BY df.file_id; –extent size COL Object FORMAT a24; COL Type FORMAT a5; SELECT segment_name "Object", segment_type "Type" ,ROUND(SUM(bytes)/1024/1024) "Mb", ROUND (SUM(bytes)/1024) "Kb" ,SUM(bytes) "Bytes", SUM(blocks) "Blocks" FROM dba_extents WHERE owner = 'ACCOUNTS' AND segment_type IN ('TABLE','INDEX') GROUP BY segment_name, segment_type ORDER BY segment_name, segment_type DESC; –segment

size

COL Object FORMAT a24; COL Type FORMAT a5; SELECT segment_name "Object", segment_type "Type" ,ROUND(bytes/1024/1024) "Mb", ROUND(bytes/1024) "Kb" ,bytes "Bytes", blocks "Blocks" FROM dba_segments WHERE owner = 'ACCOUNTS' AND segment_type IN ('TABLE','INDEX') ORDER BY segment_name, segment_type DESC;

< Day Day Up >

TKPROF Trace Files Interpretation Scripts

A Perl Wrapper Script

Tip

Please do not use this script without testing.

$source = shift; #a trace

path

and file

name

open(trace,$source)  die "Cannot

open

file $source\n"; @lines =

<

trace

;

close (trace); $len = @lines; @p = split("\\.",$source); $output = $p[0].".out"; open(output,"

>

$output"); $x = 0; $i = 0; while ($i

<

$len) { if (($lines[$i] =~ /^.*SELECT.*$/i)  ($lines[$i] =~ /^.*INSERT.*$/i)  ($lines[$i] =~ /^.*UPDATE.*$/i)  ($lines[$i] =~ /^.*DELETE.*$/i)) { $x = $i; } if ($lines[$i] =~ /^.*TABLE ACCESS FULL DUAL.*$/i) { $i++;

next

; } if (($lines[$i] =~ /^.*SORT MERGE.*$/i)  ($lines[$i] =~ /^.*TABLE ACCESS FULL.*$/i)) { $y = $i; while ($lines[$i] ne "\n") { $y = $i; $i++; } print output "-------------------------------- --------------------------------\n"; for ($j = $x; $j

<

= $y; $j++) { if ($lines[$j] ne "\n") { if (($lines[$j] =~ /^call.*count.*$/)  ($lines[$j] =~ /^Parse.*$/)  ($lines[$j] =~ /^Execute .*$/)  ($lines[$j] =~ /^Fetch.*$/)  ($lines[$j] =~ /^total.*$/)  ($lines[$j] =~ /^-.*$/)  ($lines[$j] =~ /^Misses.*$/)  ($lines[$j] =~ /^Optimizer.*$/)  ($lines[$j] =~ /^Parsing.*$/)){ next; } print output "$lines[$j]"; } } } $i++; } close(output);