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 5,008 .1 619 2.0 58,806,702 158,278,272
221.9
D 10,016 .2 1,239 1.7 49,510,240 120,698,597
169.2
D 15,024 .3 1,858 1.5 44,595,719 100,832,333
141.3
D 20,032 .4 2,478 1.4 41,273,666 87,403,398
122.5
D 25,040 .5 3,097 1.3 38,542,248 76,362,022
107.0
D 30,048 .6 3,717 1.2 35,967,561 65,954,188
92.5
D 35,056 .7 4,336 1.1 33,628,296 56,498,044
79.2
D 40,064 .8 4,955 1.1 31,907,259 49,540,991
69.4
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
D 55,088 1.1 6,814 1.0 29,095,614 38,175,312
53.5
D 60,096 1.2 7,433 1.0 28,598,287 36,164,941
50.7
D 65,104 1.3 8,053 0.9 28,183,870 34,489,710
48.3
D 70,112 1.4 8,672 0.9 27,821,913 33,026,557
46.3
D 75,120 1.5 9,291 0.9 27,496,055 31,709,318
44.4
D 80,128 1.6 9,911 0.9 27,204,516 30,530,810
42.8
D 85,136 1.7 10,530 0.9 26,927,887 29,412,574
41.2
D 90,144 1.8 11,150 0.9 26,651,485 28,295,264
39.7
D 95,152 1.9 11,769 0.9 26,413,195 27,332,006
38.3
D 100,160 2.0 12,389 0.8 24,202,729 18,396,504
25.8

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%?

"vsevolod afanassiev" <vsevolod.afanassiev (AT) gmail (DOT) com> wrote

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

On Jun 15, 6:38*pm, vsevolod afanassiev
<vsevolod.afanass... (AT) gmail (DOT) com> wrote:
An inherent assumption of the advisory is that your system is doing
about the same work all the time. You, however, know that the batch
job doesn't make the computer do the same things as normal
operations. All the cache advisory is going to do is tell you you
need more and more cache. (Yes, that is unfairly negative about the
advisory, it does have some usefulness - according to the docs, if you
alter system db_cache_advice=on when it is already on, it will reset
the stats, and that may make it match the top 5. Haven't tried it
myself.).

What you need to do is two-fold: Be sure the batch sql is doing what
it needs to do most efficiently, then see what resources it is using
for the batch.

What the advisory cannot tell you is how your sql deals with data
distribution. You have presumably (from the db file sequential read)
a lot of indexed access - perhaps it is more likely the blocks that
have the data you need are already in memory, if the data is not
scattered about blocks. It is possible that giving more memory will
allow more blocks to be in memory over the life of the batch, letting
indexed access go faster. It is also possible that this will totally
take over your cpu, where before the time taken to grab blocks off
disk allowed the cpu to get some actual work done (like those freeway
onramp meters, if you've seen them). It is also possible that
changing to multiblock reads can load up the cache faster than
whatever data and excess data is gotten through probes. Then there's
the small table issue Jonathan alluded to, and Oracle auto-tuning with
various appropriateness. Too many unknown variables, really, which is
why you can't just throw memory at a problem or even predict with
queuing theory without doing some empirical testing. Even if you know
the current bottleneck, you can't know what other bottleneck will
appear if you remove it.

What percent improvement if you double memory size? Somewhere between
-1000 and +200%, says this rabbit in my hat.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...lf-expression/

On Wed, 15 Jun 2011 18:38:43 -0700, vsevolod afanassiev wrote:


It depends on the tables from which the two values are computed. Most of
the modern tools use V$EVENTMETRIC table, which has the events for the
interval. It doesn't have to be the same as V$SYSTEM_EVENT, it usually
isn't.


--
http://mgogala.byethost5.com

va:

# This database is running batch processing. We are trying to justify
purchasing more memory.

In general improving batch processing times by throwing memory at the
database instance is not a very effective approach. Might get 20 or
30 percent improvement for some parts ( shot in the dark ).

Much more effective in general is looking at the SQL being run in the
batch and attacking the ( top ) bad performing parts of the batch
runs. Reducing logical IOs will lead you to a reduction in physical
IOs.


# The question is: how much improvement in batch run time we'll get if
we double memory size? 25%? 50%?

Well the cache advisory does give you a "reasonably accurate" guess at
how many physical IOs you may save by increasing memory. That is an
aggregate though ... and 10046 tracing and resource profiling of the
relevant batch runs would eventually get you closer to a better
guesstimate.

On Jun 16, 11:38*am, vsevolod afanassiev
<vsevolod.afanass... (AT) gmail (DOT) com> wrote:


I won't repeat the good advice already given.
Just one addition: note that the total batch run time is the sum of
*all* I/O wait time *plus* all CPU used to process the data that has
been brought into memory.
I cannot see an indication of how much CPU versus how much I/O time is
being spent in the batch run.

But I can guarantee you that if CPU is, say, 70% and I/O wait 30%,
then the most you can gain by only adding memory is a portion of the I/
O wait, ie, a portion of 30%. It will never exceed that.

However, if you look at the SQL with the intention of reducing how
much I/O you have to do to process all data - say, by reducing use of
intermediate tables - then you will have an immediate gain that
reflects across the board: CPU + I/O.

Worth a shot? In my book: yes.

On Jun 16, 5:18*pm, Noons <wizofo... (AT) gmail (DOT) com> wrote:
I don't see how you can guarantee that. What if the 70% is due to cpu
run queues from i/o asking for cpu to perform i/o, and you wipe out
the need for most of the i/o? It could happen your cpu usage could go
down and it could all run twice as fast. (I suspect with no evidence
certain OS patches have done something like that). As with every
complex system, it depends, even a batch process is complex.
Sometimes the answer is as simple as turning on async i/o 8-)

I think we're all agreed that one needs to at least look at whether
the work is optimal before throwing memory at it. In olden days,
memory could help just about any system, but now even low end
commodity machines may have enough.

jg
--
@home.com is bogus.
Happy 00th birthday, IBM! http://www.pcmag.com/article2/0,2817,2387107,00.asp

joel garry wrote,on my timestamp of 18/06/2011 2:08 AM:

that would not show as user mode CPU,that would be wait I/O or kernel CPU. I am
assuming of course an OS that measures the correct quantities. Some,
unfortunately, don't... I should have said the 70%CPU meant the user mode kind.

Exactly. But that complexity does not subvert CPU modes.

Very true. The numbers provided in the OP really don't give us much info in
what regards the OS: they are mostly to do with the wait events and the cache
advisory. That does not necessarily translate into "add more memory" at the OS
level, a world of other things might be involved. More than likely, an
inefficient process is at the core.


Bingo. In general throwing more hardware only works well if all else is
reasonably well sorted out. Throw more memory at an inefficient process and
more than likely the result will be more memory inefficiently used!

I'm reminded of a recent series of events at work. After years of me pushing
for partitioning to be extensively used in our DW, they finally started to do
it. Performance improvement has been so large the folks in the DW team now are,
if anything, over-partitioning!

Before, all I ever heard from them was: "we need a bigger box, this is too
slow". Now, it's: "we're getting more than an order of magnitude performance
improvement with LESS indexing! Why didn't you tell us indexes are slow?".

Love it!

On Sat, 18 Jun 2011 10:45:31 +1000, Noons wrote:

Love that stuff. Indexes are slow? Where are they moving to?



--
http://mgogala.byethost5.com

Mladen Gogala wrote,on my timestamp of 18/06/2011 10:58 AM:
LOL! Amazing how these things start, isn't it?
A bunch of partitioned tables make a few batch jobs run faster when using only
the partition key or a local prefixed index and all of a sudden, "all" indexes
are slow!