One more note:
If system performance changed recently, you can generate statspack report
from the good period and compare it with the statspack report from the time
of bad performance.
Also, you may try to chart / plot statspack data using Excel or 3rd party
tools. This helps to find what changed in the database behavior.
Regards,
Ron
DBA Infopower
http://www.dbainfopower.com
Standard disclaimer:
http://www.dbainfopower.com/dbaip_advice_disclaimer.html
"Ron" <support_at_dbainfopower.com> wrote in message
news:zMednfyHz-ioUazd4p2dnA_at_comcast.com...
>
> Hello gp,
>
> Based on top wait events ( sequential and scattered reads) - I would
> suggest to concentrate on optimizing SQL - some of the BG/execs and
PR/execs
> are pretty heavy
>
> Also, check if buffer cache can be increased - this would help to
> compensate for data flushed by table scans. Also buffer cache can be
> separated into separate pools.
>
> As I mentioned earlier, hard parses are pretty high, but at this point
> they definitely not the top issue.
>
> To Group: Please feel free to add more.
>
> Regards,
>
> Ron
> DBA Infopower
> http://www.dbainfopower.com
> Standard disclaimer:
> http://www.dbainfopower.com/dbaip_advice_disclaimer.html
>
>
> "gp" <gieppetto_at_tiscali.it> wrote in message
> news:c0qnai$i6k$1_at_fata.cs.interbusiness.it...
> > report from a small period :
> >
> >
> > STATSPACK report for
> >
> > DB Name DB Id Instance Inst Num Release OPS Host
>
- ----------- ---------- -------- ---------- ---- ---------
> -
> > ORACLE 1353180294 orcl 1 8.1.6.0.0 NO
SRVLC2000
> >
> > Snap
> Length
> > Start Id End Id Start Time End Time
(Minutes)
>
- -------- -------------------- -------------------- ----------
> -
> > 77 78 16-Feb-04 15:23:59 16-Feb-04 16:24:09
> 60.17
> >
> >
> > Cache Sizes
> > ~~~~~~~~~~~
> > db_block_buffers: 60000
> > db_block_size: 4096
> > log_buffer: 524288
> > shared_pool_size: 131072000
> >
> >
> > Load Profile
> > ~~~~~~~~~~~~
> > Per Second Per Transaction
> > --------------- ---------------
> > Redo size: 1,649.48 2,625.50
> > Logical reads: 5,123.92 8,155.79
> > Block changes: 10.52 16.75
> > Physical reads: 307.17 488.93
> > Physical writes: 10.90 17.35
> > User calls: 82.59 131.46
> > Parses: 20.92 33.31
> > Hard parses: 3.98 6.34
> > Sorts: 4.23 6.73
> > Transactions: 0.63
> >
> > Rows per Sort: 67.91
> > Pct Blocks changed / Read: 0.21
> > Recursive Call Pct: 63.20
> > Rollback / transaction Pct: 3.44
> >
> >
> > Instance Efficiency Percentages (Target 100%)
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > Buffer Nowait Ratio: 100.00
> > Buffer Hit Ratio: 94.01
> > Library Hit Ratio: 87.59
> > Redo NoWait Ratio: 100.00
> > In-memory Sort Ratio: 99.94
> > Soft Parse Ratio: 80.97
> > Latch Hit Ratio: 100.00
> >
> >
> > Top 5 Wait Events
> > ~~~~~~~~~~~~~~~~~ Wait %
> > Total
> > Event Waits Time (cs)
Wt
> > Time
>
> -------------------------------------------- ------------ ------------ ---
> --
> > --
> > db file sequential read 102,587 19,678
> > 45.66
> > db file scattered read 89,625 16,815
> > 39.02
> > log file parallel write 2,325 1,315
> > 3.05
> > log file sync 2,266 1,288
> > 2.99
> > direct path read 7,342 904
> > 2.10
> > -------------------------------------------------------------
> > Wait Events for DB: ORACLE Instance: orcl Snaps: 77 - 78
> > ->cs - centisecond - 100th of a second
> > ->ms - millisecond - 1000th of a second (unit often used for disk IO
> > timings)
> >
>
>
Received on Thu Feb 19 2004 - 01:54:31 CST
