Re: Comparing Top 5 Timed Events and Cache Advisory

"vsevolod afanassiev" <vsevolod.afanassiev_at_gmail.com> wrote in message news:1da9e9ea-1934-401a-8a3c-a45a7ab8788a_at_18g2000prd.googlegroups.com...
> There seems to be inconsistency between data in Top 5 Timed Events and
> Cache Advisory
>
> Top 5 Events shows that 90% of all time is spend doing reads:
>
> Top 5 Timed Events
> Avg %Total
> ~~~~~~~~~~~~~~~~~~
> wait Call
> Event Waits Time (s)
> (ms) Time
> ----------------------------------------- ------------ -----------
> ------ ------
> db file sequential read 18,010,465
> 103,171 6 87.3
> CPU time
> 8,135 6.9
> db file scattered read 2,772,236
> 4,252 2 3.6
> read by other session 535,946
> 949 2 .8
> db file parallel read 65,775 856
> 13 .7
>
> However cache advisory shows that "Est % db time for Rds" is only
> 57.3% for current cache size:
>
> Est
> Phys
> Estimated Est
> Size for Size Buffers Read Phys Reads Est Phys %
> dbtime
> P Est (M) Factr (thousands) Factr (thousands) Read Time
> for Rds
> --- -------- ----- ------------ ------ -------------- ------------
> --------
> D 45,072 .9 5,575 1.0 30,671,422 44,545,292
> 62.4
> D 50,080 1.0 6,194 1.0 29,759,277 40,858,074
> 57.3 <<<<<<<--------
> D 50,176 1.0 6,206 1.0 29,744,366 40,797,800
> 57.2
>
> Shouldn't these two values to be the same?
>
> This database is running batch processing. We are trying to justify
> purchasing more memory.
> The question is: how much improvement in batch run time we'll get if
> we double memory size? 25%? 50%?
>

The Top 5 is for the snapshot interval.
The figures from the advisories are since database startup. Notice that your Top 5 reads are in the order of 10's of millions, the advisory at the line you indicate shows blocks read in the order of 29 Billion.

You can't really tell from these figures what might happen if you increase memory.
Gut reaction is that it shouldn't get worse - but it could because you happen to hit some odd boundary condition with the "smal table" threshold

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com