Re: reduce wait times

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)

>