Echo ON SVRMGR> connect internal; Connected. SVRMGR> alter system set timed_statistics=true; Statement processed. SVRMGR> alter user sys default tablespace USERS; Statement processed. SVRMGR> SVRMGR> Rem ******************************************************************** SVRMGR> Rem Gather Ending Statistics SVRMGR> Rem ******************************************************************** SVRMGR> SVRMGR> SVRMGR> insert into stats$end_latch select * from v$latch; 53 rows processed. SVRMGR> insert into stats$end_stats select * from v$sysstat; 161 rows processed. SVRMGR> insert into stats$end_lib select * from v$librarycache; 8 rows processed. SVRMGR> update stats$dates set end_time = sysdate; 1 row processed. SVRMGR> insert into stats$end_event select * from v$system_event; 30 rows processed. SVRMGR> insert into stats$end_bck_event 2> select event, sum(total_waits), sum(time_waited) 3> from v$session s, v$session_event e 4> where type = 'BACKGROUND' and s.sid = e.sid 5> group by event; 16 rows processed. SVRMGR> insert into stats$end_waitstat select * from v$waitstat; 10 rows processed. SVRMGR> insert into stats$end_roll select * from v$rollstat; 5 rows processed. SVRMGR> insert into stats$end_file select * from stats$file_view; 79 rows processed. SVRMGR> insert into stats$end_dc select * from v$rowcache; 30 rows processed. SVRMGR> SVRMGR> Rem ******************************************************************** SVRMGR> Rem Create Summary Tables SVRMGR> Rem ******************************************************************** SVRMGR> SVRMGR> drop table stats$stats; drop table stats$stats * ORA-00942: table or view does not exist SVRMGR> drop table stats$latches; drop table stats$latches * ORA-00942: table or view does not exist SVRMGR> drop table stats$roll; drop table stats$roll * ORA-00942: table or view does not exist SVRMGR> drop table stats$files; drop table stats$files * ORA-00942: table or view does not exist SVRMGR> drop table stats$dc; drop table stats$dc * ORA-00942: table or view does not exist SVRMGR> drop table stats$lib; drop table stats$lib * ORA-00942: table or view does not exist SVRMGR> drop table stats$event; drop table stats$event * ORA-00942: table or view does not exist SVRMGR> drop table stats$bck_event; drop table stats$bck_event * ORA-00942: table or view does not exist SVRMGR> drop table stats$waitstat; drop table stats$waitstat * ORA-00942: table or view does not exist SVRMGR> SVRMGR> create table stats$stats as 2> select e.value-b.value change , n.name 3> from v$statname n , stats$begin_stats b , stats$end_stats e 4> where n.statistic# = b.statistic# and n.statistic# = e.statistic#; Statement processed. SVRMGR> SVRMGR> create table stats$latches as 2> select e.gets-b.gets gets, 3> e.misses-b.misses misses, 4> e.sleeps-b.sleeps sleeps, 5> e.immediate_gets-b.immediate_gets immed_gets, 6> e.immediate_misses-b.immediate_misses immed_miss, 7> n.name 8> from v$latchname n , stats$begin_latch b , stats$end_latch e 9> where n.latch# = b.latch# and n.latch# = e.latch#; Statement processed. SVRMGR> SVRMGR> create table stats$event as 2> select e.total_waits-b.total_waits event_count, 3> e.time_waited-b.time_waited time_waited, 4> e.event 5> from stats$begin_event b , stats$end_event e 6> where b.event = e.event 7> union all 8> select e.total_waits event_count, 9> e.time_waited time_waited, 10> e.event 11> from stats$end_event e 12> where e.event not in (select b.event from stats$begin_event b); Statement processed. SVRMGR> SVRMGR> Rem background waits SVRMGR> create table stats$bck_event as 2> select e.total_waits-b.total_waits event_count, 3> e.time_waited-b.time_waited time_waited, 4> e.event 5> from stats$begin_bck_event b , stats$end_bck_event e 6> where b.event = e.event 7> union all 8> select e.total_waits event_count, 9> e.time_waited time_waited, 10> e.event 11> from stats$end_bck_event e 12> where e.event not in (select b.event from stats$begin_bck_event b); Statement processed. SVRMGR> SVRMGR> Rem subtrace background events out of regular events SVRMGR> update stats$event e 2> set (event_count, time_waited) = 3> (select e.event_count - b.event_count, 4> e.time_waited - b.time_waited 5> from stats$bck_event b 6> where e.event = b.event) 7> where e.event in (select b.event from stats$bck_event b); 16 rows processed. SVRMGR> SVRMGR> create table stats$waitstat as 2> select e.class, 3> e.count - b.count count, 4> e.time - b.time time 5> from stats$begin_waitstat b, stats$end_waitstat e 6> where e.class = b.class; Statement processed. SVRMGR> SVRMGR> create table stats$roll as 2> select e.usn undo_segment, 3> e.gets-b.gets trans_tbl_gets, 4> e.waits-b.waits trans_tbl_waits, 5> e.writes-b.writes undo_bytes_written, 6> e.rssize segment_size_bytes, 7> e.xacts-b.xacts xacts, 8> e.shrinks-b.shrinks shrinks, 9> e.wraps-b.wraps wraps 10> from stats$begin_roll b, stats$end_roll e 11> where e.usn = b.usn; Statement processed. SVRMGR> SVRMGR> create table stats$files as 2> select b.ts table_space, 3> b.name file_name, 4> e.pyr-b.pyr phys_reads, 5> e.pbr-b.pbr phys_blks_rd, 6> e.prt-b.prt phys_rd_time, 7> e.pyw-b.pyw phys_writes, 8> e.pbw-b.pbw phys_blks_wr, 9> e.pwt-b.pwt phys_wrt_tim, 10> e.megabytes_size 11> from stats$begin_file b, stats$end_file e 12> where b.name=e.name; Statement processed. SVRMGR> SVRMGR> create table stats$dc as 2> select b.parameter name, 3> e.gets-b.gets get_reqs, 4> e.getmisses-b.getmisses get_miss, 5> e.scans-b.scans scan_reqs, 6> e.scanmisses-b.scanmisses scan_miss, 7> e.modifications-b.modifications mod_reqs, 8> e.count count, 9> e.usage cur_usage 10> from stats$begin_dc b, stats$end_dc e 11> where b.cache#=e.cache# 12> and nvl(b.subordinate#,-1) = nvl(e.subordinate#,-1); Statement processed. SVRMGR> SVRMGR> create table stats$lib as 2> select e.namespace, 3> e.gets-b.gets gets, 4> e.gethits-b.gethits gethits, 5> e.pins-b.pins pins, 6> e.pinhits-b.pinhits pinhits, 7> e.reloads - b.reloads reloads, 8> e.invalidations - b.invalidations invalidations 9> from stats$begin_lib b, stats$end_lib e 10> where b.namespace = e.namespace; Statement processed. SVRMGR> SVRMGR> SVRMGR> Rem ******************************************************************* SVRMGR> Rem Output statistics SVRMGR> Rem ******************************************************************* SVRMGR> SVRMGR> spool ${ScriptDir}/report.txt; MGR-03512: spool file "alpesh.txt" is already open SVRMGR> SVRMGR> set charwidth 12 Charwidth 12 SVRMGR> set numwidth 10 Numwidth 10 SVRMGR> Rem Select Library cache statistics. The pin hit rate shoule be high. SVRMGR> select namespace library, 2> gets, 3> round(decode(gethits,0,1,gethits)/decode(gets,0,1,gets),3) 4> gethitratio, 5> pins, 6> round(decode(pinhits,0,1,pinhits)/decode(pins,0,1,pins),3) 7> pinhitratio, 8> reloads, invalidations 9> from stats$lib; LIBRARY GETS GETHITRATI PINS PINHITRATI RELOADS INVALIDATI ------------ ---------- ---------- ---------- ---------- ---------- ---------- BODY -376 .973 -376 .973 0 0 CLUSTER 0 1 0 1 0 0 INDEX 0 1 0 1 0 0 OBJECT 0 1 0 1 0 0 PIPE 0 1 0 1 0 0 SQL AREA -588307 .997 -2474993 .999 -60 -7 TABLE/PROCED -19242 .979 -145314 .997 -16 0 TRIGGER -160 .994 -160 .988 0 0 8 rows selected. SVRMGR> SVRMGR> set charwidth 27; Charwidth 27 SVRMGR> set numwidth 12; Numwidth 12 SVRMGR> Rem The total is the total value of the statistic between the time SVRMGR> Rem bstat was run and the time estat was run. Note that the estat SVRMGR> Rem script logs on as "internal" so the per_logon statistics will SVRMGR> Rem always be based on at least one logon. SVRMGR> select n1.name "Statistic", 2> n1.change "Total", 3> round(n1.change/trans.change,2) "Per Transaction", 4> round(n1.change/logs.change,2) "Per Logon", 5> round(n1.change/(to_number(to_char(end_time, 'J'))*60*60*24 - 6> to_number(to_char(start_time, 'J'))*60*60*24 + 7> to_number(to_char(end_time, 'SSSSS')) - 8> to_number(to_char(start_time, 'SSSSS'))) 9> , 2) "Per Second" 10> from stats$stats n1, stats$stats trans, stats$stats logs, stats$dates 11> where trans.name='user commits' 12> and logs.name='logons cumulative' 13> and n1.change != 0 14> order by n1.name; Statistic Total Per Transact Per Logon Per Second --------------------------- ------------ ------------ ------------ ------------ CPU used by this session -509064 12.93 85.1 -8.88 CPU used when call started -509062 12.93 85.1 -8.88 CR blocks created -82073 2.08 13.72 -1.43 Current blocks converted fo -83 0 .01 0 DBWR buffers scanned -8677898 220.34 1450.67 -151.4 DBWR checkpoints -508 .01 .08 -.01 DBWR free buffers found -1928352 48.96 322.36 -33.64 DBWR lru scans -16812 .43 2.81 -.29 DBWR make free requests -3616 .09 .6 -.06 DBWR summed scan depth -8677906 220.34 1450.67 -151.4 DBWR timeouts -7697 .2 1.29 -.13 OS Integral shared text siz -13321552971 338247.84 2226939.65 -232414.83 OS Integral unshared data s -2981459084 75702.29 498405.06 -52016.1 OS Involuntary context swit -2049162 52.03 342.55 -35.75 OS Maximum resident set siz -41131868 1044.38 6875.94 -717.61 OS Page faults -667406 16.95 111.57 -11.64 OS Page reclaims -320234 8.13 53.53 -5.59 OS Signals received -24 0 0 0 OS System time used -562095 14.27 93.96 -9.81 OS User time used -2388428 60.64 399.27 -41.67 OS Voluntary context switch -2234194 56.73 373.49 -38.98 SQL*Net roundtrips to/from -3344794 84.93 559.14 -58.36 background checkpoints comp -39 0 .01 0 background checkpoints star -41 0 .01 0 background timeouts -28063 .71 4.69 -.49 bytes received via SQL*Net -358474921 9102.04 59925.6 -6254.14 bytes sent via SQL*Net to c -373665410 9487.75 62464.96 -6519.16 calls to get snapshot scn: -1181777 30.01 197.56 -20.62 calls to kcmgas -45091 1.14 7.54 -.79 calls to kcmgcs -15980 .41 2.67 -.28 calls to kcmgrs -1874087 47.58 313.29 -32.7 change write time -11290 .29 1.89 -.2 cleanouts and rollbacks - c -17091 .43 2.86 -.3 cleanouts only - consistent -4297 .11 .72 -.07 cluster key scan block gets -2091355 53.1 349.61 -36.49 cluster key scans -297719 7.56 49.77 -5.19 commit cleanout failures: b -33 0 .01 0 commit cleanout failures: c -9998 .25 1.67 -.17 commit cleanout number succ -250697 6.37 41.91 -4.37 consistent changes -147946 3.76 24.73 -2.58 consistent gets -686270915 17425.12 114722.65 -11973.04 cursor authentications -113520 2.88 18.98 -1.98 data blocks consistent read -144317 3.66 24.13 -2.52 db block changes -2747324 69.76 459.27 -47.93 db block gets -3681160 93.47 615.37 -64.22 deferred (CURRENT) block cl -67672 1.72 11.31 -1.18 dirty buffers inspected -5 0 0 0 enqueue conversions -7130 .18 1.19 -.12 enqueue releases -152831 3.88 25.55 -2.67 enqueue requests -152894 3.88 25.56 -2.67 enqueue timeouts -8 0 0 0 enqueue waits -40 0 .01 0 exchange deadlocks 2 0 0 0 execute count -1299042 32.98 217.16 -22.66 free buffer inspected -8098 .21 1.35 -.14 free buffer requested -117157672 2974.75 19585.03 -2043.99 immediate (CR) block cleano -21388 .54 3.58 -.37 immediate (CURRENT) block c -82844 2.1 13.85 -1.45 logons cumulative -5982 .15 1 -.1 logons current -26 0 0 0 messages received -47355 1.2 7.92 -.83 messages sent -47354 1.2 7.92 -.83 no work - consistent read g -589258097 14961.87 98505.2 -10280.51 opened cursors cumulative -294557 7.48 49.24 -5.14 opened cursors current -156 0 .03 0 parse count -589467 14.97 98.54 -10.28 parse time cpu -1993 .05 .33 -.03 parse time elapsed -33387 .85 5.58 -.58 physical reads -117045857 2971.91 19566.34 -2042.04 physical writes -142110 3.61 23.76 -2.48 process last non-idle time -1.74934E+11 4441751.69 29243388.25 -3051989.75 recursive calls -366237 9.3 61.22 -6.39 recursive cpu usage -80211 2.04 13.41 -1.4 redo blocks written -725596 18.42 121.3 -12.66 redo buffer allocation retr -104 0 .02 0 redo entries -1407612 35.74 235.31 -24.56 redo log space requests -20 0 0 0 redo size -350896366 8909.62 58658.7 -6121.92 redo small copies -173018 4.39 28.92 -3.02 redo synch time -9313 .24 1.56 -.16 redo synch writes -35393 .9 5.92 -.62 redo wastage -8516302 216.24 1423.65 -148.58 redo write time -9493 .24 1.59 -.17 redo writer latching time -443 .01 .07 -.01 redo writes -37254 .95 6.23 -.65 rollback changes - undo rec -4039 .1 .68 -.07 rollbacks only - consistent -67119 1.7 11.22 -1.17 session connect time -1.74934E+11 4441751.69 29243388.25 -3051989.75 session logical reads -689752216 17513.51 115304.62 -12033.78 session pga memory -524352344 13313.84 87655.02 -9148.13 session pga memory max -554466300 14078.47 92689.12 -9673.51 session uga memory -20289336 515.17 3391.73 -353.98 session uga memory max -273352520 6940.7 45695.84 -4769.05 sorts (disk) -13 0 0 0 sorts (memory) -66842 1.7 11.17 -1.17 sorts (rows) -4930899 125.2 824.29 -86.03 table fetch by rowid -296455935 7527.32 49558 -5172.13 table fetch continued row -3566 .09 .6 -.06 table scan blocks gotten -246435912 6257.26 41196.24 -4299.45 table scan rows gotten -1978448548 50234.83 330733.63 -34517.05 table scans (long tables) -4815 .12 .8 -.08 table scans (short tables) -458715 11.65 76.68 -8 total number commit cleanou -265977 6.75 44.46 -4.64 transaction rollbacks -24 0 0 0 transaction tables consiste -11 0 0 0 transaction tables consiste -2703 .07 .45 -.05 user calls -3344715 84.93 559.13 -58.35 user commits -39384 1 6.58 -.69 user rollbacks -1960 .05 .33 -.03 write requests -17582 .45 2.94 -.31 110 rows selected. SVRMGR> SVRMGR> SVRMGR> set numwidth 27 Numwidth 27 SVRMGR> Rem Average length of the dirty buffer write queue. If this is larger SVRMGR> Rem than the value of: SVRMGR> Rem 1. (db_files * db_file_simultaneous_writes)/2 SVRMGR> Rem or SVRMGR> Rem 2. 1/4 of db_block_buffers SVRMGR> Rem which ever is smaller and also there is a platform specific limit SVRMGR> Rem on the write batch size (normally 1024 or 2048 buffers). If the average SVRMGR> Rem length of the dirty buffer write queue is larger than the value SVRMGR> Rem calculated before, increase db_file_simultaneous_writes or db_files. SVRMGR> Rem Also check for disks that are doing many more IOs than other disks. SVRMGR> select queue.change/writes.change "Average Write Queue Length" 2> from stats$stats queue, stats$stats writes 3> where queue.name = 'summed dirty queue length' 4> and writes.name = 'write requests'; Average Write Queue Length --------------------------- 0 1 row selected. SVRMGR> SVRMGR> SVRMGR> set charwidth 32; Charwidth 32 SVRMGR> set numwidth 13; Numwidth 13 SVRMGR> Rem System wide wait events for non-background processes (PMON, SVRMGR> Rem SMON, etc). Times are in hundreths of seconds. Each one of SVRMGR> Rem these is a context switch which costs CPU time. By looking at SVRMGR> Rem the Total Time you can often determine what is the bottleneck SVRMGR> Rem that processes are waiting for. This shows the total time spent SVRMGR> Rem waiting for a specific event and the average time per wait on SVRMGR> Rem that event. SVRMGR> select n1.event "Event Name", 2> n1.event_count "Count", 3> n1.time_waited "Total Time", 4> round(n1.time_waited/n1.event_count, 2) "Avg Time" 5> from stats$event n1 6> where n1.event_count > 0 7> order by n1.time_waited desc; Event Name Count Total Time Avg Time -------------------------------- ------------- ------------- ------------- buffer deadlock 2 0 0 library cache pin 101 0 0 2 rows selected. SVRMGR> SVRMGR> SVRMGR> Rem System wide wait events for background processes (PMON, SMON, etc) SVRMGR> select n1.event "Event Name", 2> n1.event_count "Count", 3> n1.time_waited "Total Time", 4> round(n1.time_waited/n1.event_count, 2) "Avg Time" 5> from stats$bck_event n1 6> where n1.event_count > 0 7> order by n1.time_waited desc; Event Name Count Total Time Avg Time -------------------------------- ------------- ------------- ------------- 0 rows selected. SVRMGR> SVRMGR> SVRMGR> set charwidth 18; Charwidth 18 SVRMGR> set numwidth 11; Numwidth 11 SVRMGR> Rem Latch statistics. Latch contention will show up as a large value for SVRMGR> Rem the 'latch free' event in the wait events above. SVRMGR> Rem Sleeps should be low. The hit_ratio should be high. SVRMGR> select name latch_name, gets, misses, 2> round((gets-misses)/decode(gets,0,1,gets),3) 3> hit_ratio, 4> sleeps, 5> round(sleeps/decode(misses,0,1,misses),3) "SLEEPS/MISS" 6> from stats$latches 7> where gets != 0 8> order by name; LATCH_NAME GETS MISSES HIT_RATIO SLEEPS SLEEPS/MISS ------------------ ----------- ----------- ----------- ----------- ----------- NLS data objects -3 0 1 0 0 archive control -6 0 1 0 0 cache buffer handl -390 0 1 0 0 cache buffers chai -1475752834 -31933 1 -7589 .238 cache buffers lru -440682 -5690 .987 -11168 1.963 dml lock allocatio -197310 -66 1 -33 .5 enqueue hash chain -313652 -149 1 -80 .537 enqueues -375568 -101 1 -49 .485 global transaction 867 0 1 0 0 global tx free lis 10 0 1 0 0 global tx hash map 55 0 1 0 0 ktm global data -135 0 1 0 0 latch wait list -21713 -33 .998 0 0 library cache -9653935 -46441 .995 -14853 .32 library cache load -870 0 1 0 0 list of block allo -100627 -6 1 -2 .333 loader state objec -81 0 1 0 0 messages -475240 -127 1 -22 .173 modify parameter v -5984 -1 1 -2 2 multiblock read ob -31141967 -21460 .999 -3328 .155 process allocation -5934 -1 1 -1 1 redo allocation -1583865 -1665 .999 -775 .465 redo copy -120 -118 .017 -240 2.034 row cache objects -1067383 -1188 .999 -217 .183 sequence cache -19432 0 1 0 0 session allocation -191777 -121 .999 -50 .413 session idle bit -6762398 -1488 1 -305 .205 session switching -908 0 1 0 0 shared pool -569763 -624 .999 -180 .288 sort extent pool -104 0 1 0 0 system commit numb -3085779 -4646 .998 -943 .203 transaction alloca -302174 -73 1 -23 .315 undo global data -220633 -80 1 -86 1.075 user lock -23536 -2 1 -1 .5 34 rows selected. SVRMGR> SVRMGR> set numwidth 16 Numwidth 16 SVRMGR> Rem Statistics on no_wait gets of latches. A no_wait get does not SVRMGR> Rem wait for the latch to become free, it immediately times out. SVRMGR> select name latch_name, 2> immed_gets nowait_gets, 3> immed_miss nowait_misses, 4> round((immed_gets/immed_gets+immed_miss), 3) 5> nowait_hit_ratio 6> from stats$latches 7> where immed_gets + immed_miss != 0 8> order by name; LATCH_NAME NOWAIT_GETS NOWAIT_MISSES NOWAIT_HIT_RATIO ------------------ ---------------- ---------------- ---------------- cache buffers chai -221707205 -8017 -8016 cache buffers lru -244201375 -2170579 -2170578 process allocation -5884 -1 0 redo copy -1388756 -1127 -1126 4 rows selected. SVRMGR> SVRMGR> Rem Buffer busy wait statistics. If the value for 'buffer busy wait' in SVRMGR> Rem the wait event statistics is high, then this table will identify SVRMGR> Rem which class of blocks is having high contention. If there are high SVRMGR> Rem 'undo header' waits then add more rollback segments. If there are SVRMGR> Rem high 'segment header' waits then adding freelists might help. Check SVRMGR> Rem v$session_wait to get the addresses of the actual blocks having SVRMGR> Rem contention. SVRMGR> select * from stats$waitstat 2> where count != 0 3> order by count desc; CLASS COUNT TIME ------------------ ---------------- ---------------- segment header -4 -23 undo block -151 -220 undo header -483 -1342 data block -1957 -552 4 rows selected. SVRMGR> SVRMGR> SVRMGR> set numwidth 19; Numwidth 19 SVRMGR> Rem Waits_for_trans_tbl high implies you should add rollback segments. SVRMGR> select * from stats$roll; UNDO_SEGMENT TRANS_TBL_GETS TRANS_TBL_WAITS UNDO_BYTES_WRITTEN SEGMENT_SIZE_BYTES XACTS SHRINKS WRAPS ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- 0 -109 0 0 610304 0 0 0 1 -42688 -28 -49341959 62910464 -3 0 -5 2 -34525 -7 -26918152 62910464 -1 0 -3 3 -30991 -31 -30251721 62910464 -1 0 -4 4 -41236 -36 -51101143 62910464 -1 0 -6 5 rows selected. SVRMGR> SVRMGR> set charwidth 39 Charwidth 39 SVRMGR> Rem The init.ora parameters currently in effect: SVRMGR> select name, value from v$parameter where isdefault = 'FALSE' 2> order by name; NAME VALUE --------------------------------------- --------------------------------------- always_anti_join NESTED_LOOPS audit_trail NONE background_dump_dest /u01/app/oracle/admin/c21p2/bdump cache_size_threshold 32000 compatible 7.3.4.4 control_files /u02dt5/oradata/c21p2/control02.ctl, /u core_dump_dest /u01/app/oracle/admin/c21p2/cdump cpu_count 4 db_block_buffers 320000 db_block_lru_latches 2 db_block_size 4096 db_files 100 db_name c21p2 distributed_transactions 31 dml_locks 500 enqueue_resources 520 gc_freelist_groups 50 gc_releasable_locks 320000 ifile /u01/app/oracle/admin/c21p2/pfile/confi job_queue_interval 600 job_queue_processes 2 log_archive_dest /tctorahot/archivelog/c21p2 log_archive_format c21p2_%s.arc log_archive_start TRUE log_buffer 1048576 log_simultaneous_copies 4 max_dump_file_size 204800 mts_max_dispatchers 0 mts_max_servers 0 mts_servers 0 mts_service c21p2 open_cursors 250 optimizer_mode CHOOSE processes 100 remote_login_passwordfile NONE rollback_segments r01, r02, r03, r04 sequence_cache_hash_buckets 10 sessions 115 shared_pool_size 300000000 sort_area_retained_size 2048000 sort_area_size 2048000 sort_direct_writes AUTO temporary_table_locks 115 timed_statistics TRUE transactions 126 transactions_per_rollback_segment 34 unlimited_rollback_segments TRUE user_dump_dest /u01/app/oracle/admin/c21p2/udump utl_file_dir * 49 rows selected. SVRMGR> SVRMGR> set charwidth 15; Charwidth 15 SVRMGR> set numwidth 8; Numwidth 8 SVRMGR> Rem get_miss and scan_miss should be very low compared to the requests. SVRMGR> Rem cur_usage is the number of entries in the cache that are being used. SVRMGR> select * from stats$dc 2> where get_reqs != 0 or scan_reqs != 0 or mod_reqs != 0; NAME GET_REQS GET_MISS SCAN_REQ SCAN_MIS MOD_REQS COUNT CUR_USAG --------------- -------- -------- -------- -------- -------- -------- -------- dc_tablespaces -4792 0 0 0 0 51 37 dc_free_extents -11306 -1013 -537 0 -2341 200 183 dc_segments -14719 -138 0 0 -606 526 513 dc_rollback_seg -2314 0 0 0 0 18 12 dc_used_extents -986 -536 0 0 -978 34 19 dc_tablespace_q -110 -39 0 0 -22 12 10 dc_files -13 -5 0 0 -4 21 1 dc_users -72592 -4 0 0 -5 21 17 dc_user_grants -54458 -5 0 0 0 29 12 dc_objects -15059 -314 0 0 -82 422 417 dc_tables -141225 -121 0 0 -69 277 274 dc_columns -236994 -857 -8714 -89 -380 5141 5124 dc_table_grants -4783 -316 0 0 0 162 161 dc_indexes -11697 -59 -3580 -38 0 394 381 dc_constraint_d -103678 -356 -3217 -41 -3 3388 3384 dc_constraint_d -9 -9 -168 -61 0 1 0 dc_synonyms -2316 -38 0 0 0 46 42 dc_usernames -14559 -7 0 0 -2 20 7 dc_object_ids -40313 -81 0 0 0 360 359 dc_constraints -6 -4 0 0 -6 1 0 dc_sequences -424 -1 0 0 -421 14 4 dc_tablespaces -546 -7 0 0 -546 17 6 dc_histogram_de -9740 -283 0 0 0 837 820 23 rows selected. SVRMGR> SVRMGR> SVRMGR> set charwidth 80; Charwidth 80 SVRMGR> set numwidth 10; Numwidth 10 SVRMGR> Rem Sum IO operations over tablespaces. SVRMGR> select 2> table_space||' ' 3> table_space, 4> sum(phys_reads) reads, sum(phys_blks_rd) blks_read, 5> sum(phys_rd_time) read_time, sum(phys_writes) writes, 6> sum(phys_blks_wr) blks_wrt, sum(phys_wrt_tim) write_time, 7> sum(megabytes_size) megabytes 8> from stats$files 9> group by table_space 10> order by table_space; TABLE_SPACE READS BLKS_READ READ_TIME WRITES BLKS_WRT WRITE_TIME MEGABYTES ------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- AR_DATA 0 0 0 0 0 0 10 AR_INDEX 0 0 0 0 0 0 4 CU_DATA -10556 -35312 -1261 -452 -452 -111 655 CU_INDEX -8933 -8933 -610 -569 -569 -327 429 ED_DATA -4849 -25264 -1305 -592 -592 -667 1020 ED_DATA_REORG 0 0 0 0 0 0 25 ED_INDEX -1752 -1752 -126 -2200 -2200 -2951 571 IN_DATA -723687 -934291 -9587 -3924 -3924 -1671 2009 IN_DATA_REORG 0 0 0 0 0 0 224 IN_INDEX -86744 -86744 -4081 -13200 -13200 -2652 3222 OH_DATA -266379 -266379 -6937 -2207 -2207 -592 5671 OH_DATA_REORG 0 0 0 0 0 0 537 OH_INDEX -76339 -76339 -13350 -12230 -12230 -4129 7792 OMSSNAP 0 0 0 0 0 0 0 OMS_DATA -11804100 -87160982 -74291 -5578 -5578 -1744 2690 OMS_INDEX -1839 -1839 -151 -2756 -2756 -2488 1048 OR_DATA -48739 -91711 -3022 -6781 -6781 -2530 244 OR_INDEX -22325 -22325 -4558 -14119 -14119 -9573 539 PU_DATA -36560 -36806 -104 -2180 -2180 -1317 806 PU_INDEX -4934 -4934 -355 -3872 -3872 -3210 509 RBS -255 -255 -28 -39812 -39812 -14318 1775 SYSTEM -3383 -16479 -121 -1164 -12982 -977 105 SY_DATA -38797 -44634 -1552 -2668 -2668 -575 1604 SY_INDEX -21479 -21479 -3176 -5748 -5748 -1854 3762 TEMP -434 -5573 0 -476 -7326 0 1947 TOOLS -24 -24 0 2 2 0 105 USERS -3714415 -28201489 -115056 -2916 -2916 -674 537 27 rows selected. SVRMGR> SVRMGR> SVRMGR> set charwidth 48; Charwidth 48 SVRMGR> set numwidth 10; Numwidth 10 SVRMGR> Rem I/O should be spread evenly accross drives. A big difference between SVRMGR> Rem phys_reads and phys_blks_rd implies table scans are going on. SVRMGR> select table_space, file_name, 2> phys_reads reads, phys_blks_rd blks_read, phys_rd_time read_time, 3> phys_writes writes, phys_blks_wr blks_wrt, phys_wrt_tim write_time, 4> megabytes_size megabytes 5> from stats$files order by table_space, file_name; TABLE_SPACE FILE_NAME READS BLKS_READ READ_TIME WRITES BLKS_WRT WRITE_TIME MEGABYTES ------------------------------ ------------------------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- AR_DATA /u01dt5/oradata/c21p2/ar_data03.dbf 0 0 0 0 0 0 3 AR_DATA /u01dt5/oradata/c21p2/ar_data04.dbf 0 0 0 0 0 0 2 AR_DATA /u04dt5/oradata/c21p2/ar_data01.dbf 0 0 0 0 0 0 2 AR_DATA /u04dt5/oradata/c21p2/ar_data02.dbf 0 0 0 0 0 0 3 AR_INDEX /u05dt5/oradata/c21p2/ar_index01.dbf 0 0 0 0 0 0 4 CU_DATA /u03dt5/oradata/c21p2/cu_data02.dbf -1476 -5283 -124 -28 -28 0 116 CU_DATA /u04dt5/oradata/c21p2/cu_data01.dbf -9080 -30029 -1137 -424 -424 -111 539 CU_INDEX /u03dt5/oradata/c21p2/cu_index02.dbf -864 -864 -65 -52 -52 -63 77 CU_INDEX /u05dt5/oradata/c21p2/cu_index01.dbf -8069 -8069 -545 -517 -517 -264 352 ED_DATA /u04dt5/oradata/c21p2/ed_data01.dbf -4849 -25264 -1305 -592 -592 -667 516 ED_DATA /u04dt5/oradata/c21p2/ed_data02.dbf 0 0 0 0 0 0 504 ED_DATA_REORG /reorg/oraclereorg/ed_data_reorg01.dbf 0 0 0 0 0 0 25 ED_INDEX /u03dt5/oradata/c21p2/ed_index01.dbf -1752 -1752 -126 -2200 -2200 -2951 571 IN_DATA /u01dt5/oradata/c21p2/in_data01.dbf -266996 -349489 -206 -67 -67 -10 671 IN_DATA /u02dt5/oradata/c21p2/in_data02.dbf -224905 -284930 -8553 -2944 -2944 -1532 667 IN_DATA /u06dt5/oradata/c21p2/in_data03.dbf -231786 -299872 -828 -913 -913 -129 671 IN_DATA_REORG /reorg/oraclereorg/in_data_reorg01.dbf 0 0 0 0 0 0 224 IN_INDEX /u03dt5/oradata/c21p2/in_index06.dbf -16326 -16326 -476 -1063 -1063 -451 537 IN_INDEX /u04dt5/oradata/c21p2/in_index01.dbf -8593 -8593 -311 -1173 -1173 -156 537 IN_INDEX /u05dt5/oradata/c21p2/in_index03.dbf -17218 -17218 -776 -1617 -1617 -495 537 IN_INDEX /u06dt5/oradata/c21p2/in_index02.dbf -14590 -14590 -806 -2309 -2309 -399 537 IN_INDEX /u06dt5/oradata/c21p2/in_index04.dbf -13761 -13761 -1014 -6176 -6176 -1004 537 IN_INDEX /u06dt5/oradata/c21p2/in_index05.dbf -16256 -16256 -698 -862 -862 -147 537 OH_DATA /reorg/oraclereorg/reorg02.dbf 0 0 0 0 0 0 1 OH_DATA /reorg/oraclereorg/reorg03.dbf 0 0 0 0 0 0 1 OH_DATA /reorg/oraclereorg/reorg04.dbf -2995 -2995 0 0 0 0 84 OH_DATA /u02dt5/oradata/c21p2/oh_data11.dbf 0 0 0 0 0 0 4 OH_DATA /u03dt5/oradata/c21p2/oh_data03.dbf -23107 -23107 -1455 -162 -162 -38 638 OH_DATA /u03dt5/oradata/c21p2/oh_data06.dbf -93092 -93092 -153 -469 -469 -177 639 OH_DATA /u03dt5/oradata/c21p2/oh_data10.dbf -838 -838 -14 0 0 0 131 OH_DATA /u04dt5/oradata/c21p2/oh_data02.dbf -26877 -26877 -2681 -697 -697 -140 642 OH_DATA /u04dt5/oradata/c21p2/oh_data05.dbf -6242 -6242 -118 -537 -537 -183 636 OH_DATA /u05dt5/oradata/c21p2/oh_data01.dbf -13851 -13851 -551 -16 -16 0 645 OH_DATA /u05dt5/oradata/c21p2/oh_data04.dbf -11129 -11129 -411 -303 -303 -54 640 OH_DATA /u05dt5/oradata/c21p2/oh_data07.dbf -28987 -28987 -1114 -23 -23 0 640 OH_DATA /u05dt5/oradata/c21p2/oh_data08.dbf -51993 -51993 -351 0 0 0 478 OH_DATA /u05dt5/oradata/c21p2/oh_data09.dbf -7268 -7268 -89 0 0 0 492 OH_DATA_REORG /reorg/oraclereorg/oh_data_reorg01.dbf 0 0 0 0 0 0 537 OH_INDEX /u01dt5/oradata/c21p2/oh_index05.dbf -15031 -15031 -5581 -550 -550 -212 1075 OH_INDEX /u01dt5/oradata/c21p2/oh_index08.dbf -16342 -16342 -2237 -6963 -6963 -2313 537 OH_INDEX /u02dt5/oradata/c21p2/oh_index02.dbf -7051 -7051 -513 -352 -352 -209 1075 OH_INDEX /u02dt5/oradata/c21p2/oh_index04.dbf -3655 -3655 -743 -448 -448 -174 1075 OH_INDEX /u02dt5/oradata/c21p2/oh_index06.dbf -4568 -4568 -405 -448 -448 -202 1075 OH_INDEX /u03dt5/oradata/c21p2/oh_index03.dbf -6688 -6688 -892 -421 -421 -68 1075 OH_INDEX /u03dt5/oradata/c21p2/oh_index07.dbf -4325 -4325 -781 -895 -895 -178 537 OH_INDEX /u04dt5/oradata/c21p2/oh_index10.dbf -2461 -2461 -283 -393 -393 -163 268 OH_INDEX /u06dt5/oradata/c21p2/oh_index01.dbf -12354 -12354 -1016 -1078 -1078 -466 538 OH_INDEX /u06dt5/oradata/c21p2/oh_index09.dbf -3864 -3864 -899 -682 -682 -144 537 OMSSNAP /u02dt5/oradata/c21p2/omssnap01.dbf 0 0 0 0 0 0 0 OMS_DATA /u04dt5/oradata/c21p2/oms_data01.dbf -1859751 -13928274 -12314 -408 -408 -225 538 OMS_DATA /u05dt5/oradata/c21p2/oms_data02.dbf -3092270 -22798830 -19568 -1176 -1176 -30 538 OMS_DATA /u05dt5/oradata/c21p2/oms_data03.dbf -208 -208 -14 -833 -833 -551 538 OMS_DATA /u06dt5/oradata/c21p2/oms_data04.dbf -4823228 -35628007 -30135 -2001 -2001 -58 538 OMS_DATA /u06dt5/oradata/c21p2/oms_data05.dbf -2028643 -14805663 -12260 -1160 -1160 -880 538 OMS_INDEX /u01dt5/oradata/c21p2/oms_index01.dbf -1482 -1482 -112 -2281 -2281 -2282 514 OMS_INDEX /u01dt5/oradata/c21p2/oms_index03.dbf -36 -36 0 0 0 0 1 OMS_INDEX /u02dt5/oradata/c21p2/oms_index02.dbf -321 -321 -39 -475 -475 -206 533 OR_DATA /u01dt5/oradata/c21p2/or_data01.dbf -48739 -91711 -3022 -6781 -6781 -2530 244 OR_INDEX /u04dt5/oradata/c21p2/or_index01.dbf -22325 -22325 -4558 -14119 -14119 -9573 539 PU_DATA /u06dt5/oradata/c21p2/pu_data01.dbf -29214 -29460 -61 -769 -769 -457 538 PU_DATA /u06dt5/oradata/c21p2/pu_data02.dbf -4529 -4529 -43 -1409 -1409 -860 134 PU_DATA /u06dt5/oradata/c21p2/pu_data03.dbf -2817 -2817 0 -2 -2 0 134 PU_INDEX /u01dt5/oradata/c21p2/pu_index01.dbf -3582 -3582 -239 -2835 -2835 -2180 268 PU_INDEX /u01dt5/oradata/c21p2/pu_index02.dbf -1352 -1352 -116 -1037 -1037 -1030 241 RBS /u01dt5/oradata/c21p2/rbs01.dbf 0 0 0 0 0 0 682 RBS /u02dt5/oradata/c21p2/rbs02.dbf -233 -233 -20 -25173 -25173 -9888 420 RBS /u03dt5/oradata/c21p2/rbs03.dbf -22 -22 -8 -14639 -14639 -4430 673 SYSTEM /u01dt5/oradata/c21p2/system01.dbf -3383 -16479 -121 -1164 -12982 -977 105 SY_DATA /u01dt5/oradata/c21p2/sy_data03.dbf -822 -1125 -84 -502 -502 -304 525 SY_DATA /u06dt5/oradata/c21p2/sy_data01.dbf -34772 -39829 -1292 -2057 -2057 -256 542 SY_DATA /u06dt5/oradata/c21p2/sy_data02.dbf -3203 -3680 -176 -109 -109 -15 537 SY_INDEX /u01dt5/oradata/c21p2/sy_index01.dbf -6741 -6741 -929 -1663 -1663 -43 1075 SY_INDEX /u01dt5/oradata/c21p2/sy_index04.dbf -870 -870 -190 -937 -937 -194 537 SY_INDEX /u02dt5/oradata/c21p2/sy_index02.dbf -7751 -7751 -610 -781 -781 -619 1075 SY_INDEX /u04dt5/oradata/c21p2/sy_index03.dbf -6117 -6117 -1447 -2367 -2367 -998 1075 TEMP /u02dt5/oradata/c21p2/temp_1.dbf -434 -5573 0 -476 -7326 0 984 TEMP /u03dt5/oradata/c21p2/temp_2.dbf 0 0 0 0 0 0 963 TOOLS /u03dt5/oradata/c21p2/tools01.dbf -24 -24 0 2 2 0 105 USERS /u03dt5/oradata/c21p2/users01.dbf -3714415 -28201489 -115056 -2916 -2916 -674 537 79 rows selected. SVRMGR> SVRMGR> SVRMGR> set charwidth 25 Charwidth 25 SVRMGR> Rem The times that bstat and estat were run. SVRMGR> select to_char(start_time, 'dd-mon-yy hh24:mi:ss') start_time, 2> to_char(end_time, 'dd-mon-yy hh24:mi:ss') end_time 3> from stats$dates; START_TIME END_TIME ------------------------- ------------------------- 18-dec-06 16:56:30 19-dec-06 08:51:48 1 row selected. SVRMGR> SVRMGR> set charwidth 75 Charwidth 75 SVRMGR> Rem Versions SVRMGR> select * from v$version; BANNER ---------------------------------------------------------------- Oracle7 Server Release 7.3.4.4.0 - Production PL/SQL Release 2.3.4.4.0 - Production CORE Version 3.5.4.0.0 - Production TNS for IBM/AIX RISC System/6000: Version 2.3.4.0.0 - Production NLSRTL Version 3.2.4.0.0 - Production 5 rows selected. SVRMGR> SVRMGR> SVRMGR> spool off;