SEO

I'm in the process of speccing out the hardware for an Oracle 10.2.0.3
server. We're expecting a database in the 200G size. Working set is
hard to estimate, but in analagous 100G systems I see a buffer hit
ratio or around 70% with a 2.3 G SGA (about as big as I can get it on
a 32 bit OS). Based on this and a fair amount of spitball analysis I'd
guess the working set on the new box to be around 8-10G.

For the new hardware, we're going to 64bit os, and 64 bit Oracle.
We're expecting 50 or so concurrent connections to the database (all
coming from an application pool). Given the nature of the application
though, its very unusual for the acutal concurrency to get much over
15 or so active sessions.

The question I have is, is there any downside to me buying, say, a 32G
box and setting the SGA size at 20G? Will I actually end up harming my
performance with an over-large SGA (assuming I have enough physical
memory to keep the box out of swap)?

Pat <pat.casey@service-now.com> wrote in news:c3b1b601-abd9-4d2c-acaa-
e4d947b44bc5@d77g2000hsb.googlegroups.com:

> Working set is
> hard to estimate, but in analagous 100G systems I see a buffer hit
> ratio or around 70% with a 2.3 G SGA (about as big as I can get it on
> a 32 bit OS). Based on this and a fair amount of spitball analysis I'd
> guess the working set on the new box to be around 8-10G.

Choose any hit ratio

http://www.oracledba.co.uk/tips/choose.htm


Using BCHR for anything other than pure wishful dreaming,
is like using Tarot cards to select your spouse.

There is NO, NO, NO relationship between raw database size (200GB)
and database performance as measured by ANY metric!

Good Luck on your search for the Holy Grail!

On May 8, 8:31 pm, "Ana C. Dent" <anaced...@hotmail.com> wrote:
> Pat <pat.ca...@service-now.com> wrote in news:c3b1b601-abd9-4d2c-acaa-
> e4d947b44...@d77g2000hsb.googlegroups.com:
>
> > Working set is
> > hard to estimate, but in analagous 100G systems I see a buffer hit
> > ratio or around 70% with a 2.3 G SGA (about as big as I can get it on
> > a 32 bit OS). Based on this and a fair amount of spitball analysis I'd
> > guess the working set on the new box to be around 8-10G.
>
> Choose any hit ratio
>
> http://www.oracledba.co.uk/tips/choose.htm
>
> Using BCHR for anything other than pure wishful dreaming,
> is like using Tarot cards to select your spouse.
>
> There is NO, NO, NO relationship between raw database size (200GB)
> and database performance as measured by ANY metric!
>
> Good Luck on your search for the Holy Grail!

What I want to do is, as much as possible, serve data out of cache. On
analogous 32 bit boxes with a 2.3G SGA, I'm seeing an awful lot of
physical IOs, enough that many of my queries are spending > 50% of
their time in IO wait.

The classic solution to this is:

add more memory

What i want to know is if there's a potential downside to throwing
memory at the problem. I have the hardware budget to buy an aweful lot
of memory, but I don't want to spend it there if there if it'll be
counterproductive.

Pat <pat.casey@service-now.com> wrote in news:12a7f1d9-6dce-4ba5-9d41-
73c18ab0dd7e@y21g2000hsf.googlegroups.com:


When your only tool is a hammer, all problems are viewed as nails.

>
> The classic solution to this is:
> add more memory

What you are attempting to do is covert Physical I/O to Logical I/O.

A smarter solution is to add an index to reduce I/O by orders of magnitude.

On May 8, 9:00 pm, "Ana C. Dent" <anaced...@hotmail.com> wrote:
> Pat <pat.ca...@service-now.com> wrote in news:12a7f1d9-6dce-4ba5-9d41-
> 73c18ab0d...@y21g2000hsf.googlegroups.com:
>
> When your only tool is a hammer, all problems are viewed as nails.
>
>
>
> > The classic solution to this is:
> > add more memory
>
> What you are attempting to do is covert Physical I/O to Logical I/O.
>
> A smarter solution is to add an index to reduce I/O by orders of magnitude.

The problem here isn't excessive table scans or an absence of indexes.
The working set of indexes simply don't fit in cache all that well.
I've got mutltiple indexes > 1 G in size and a half dozen or so >
500M.

So, while I appreciate the tutorial on the importance of indexes as a
component to an efficient data retreival strategy, I find it a bit odd
that you're acting as though cache memory isn't an analagous
component.

This is the database back end for an enterprise application, it's not
a data warehouse application. It tends to aggressively chew over the
same working set (the aforementioned 10-12G of memory) querying it in
all sorts of unpredictable, end-user defined, ways. If I knew a set of
additional indexes I could add that would reduce my working set, I'd
have already added them. At this point, the only solution I can see
here is to bump up the SGA so that my (existing) index and data blocks
fit in memory.

Pat <pat.casey@service-now.com> wrote in
news:e71181dd-9753-4709-a063-ef2fc5254d26@a70g2000hsh.googlegroups.com:

> On May 8, 9:00 pm, "Ana C. Dent" <anaced...@hotmail.com> wrote:
>> Pat <pat.ca...@service-now.com> wrote in
>> news:12a7f1d9-6dce-4ba5-9d41-
>> 73c18ab0d...@y21g2000hsf.googlegroups.com:
>>
>> When your only tool is a hammer, all problems are viewed as nails.
>>
>>
>>
>> > The classic solution to this is:
>> > add more memory
>>
>> What you are attempting to do is covert Physical I/O to Logical I/O.
>>
>> A smarter solution is to add an index to reduce I/O by orders of
>> magnitude.
>
> The problem here isn't excessive table scans or an absence of indexes.
> The working set of indexes simply don't fit in cache all that well.
> I've got mutltiple indexes > 1 G in size and a half dozen or so >
> 500M.
>
> So, while I appreciate the tutorial on the importance of indexes as a
> component to an efficient data retreival strategy, I find it a bit odd
> that you're acting as though cache memory isn't an analagous
> component.
>
> This is the database back end for an enterprise application, it's not
> a data warehouse application. It tends to aggressively chew over the
> same working set (the aforementioned 10-12G of memory) querying it in
> all sorts of unpredictable, end-user defined, ways. If I knew a set of
> additional indexes I could add that would reduce my working set, I'd
> have already added them. At this point, the only solution I can see
> here is to bump up the SGA so that my (existing) index and data blocks
> fit in memory.

You have all the answers.
BCHR is a measure of performance.
DB size relates to perfromance.
More memory results in better response time.

Have A Nice Day!

On Thu, 08 May 2008 20:46:14 -0700, Pat wrote:

> physical IOs, enough that many of my queries are spending > 50% of their
> time in IO wait.
>
> The classic solution to this is:
>
> add more memory

The proper solution would be to tune the #$%&! queries.

--
Mladen Gogala
http://mgogala.freehostia.com

Advice: buy, 16G RAM
If you later find that more would be usefull, you can add it when needed.

But if you have too much $ , buy more. I would be clad to show how utilize
it!
(By order)

"Pat" <pat.casey@service-now.com> wrote in message
news:c3b1b601-abd9-4d2c-acaa-e4d947b44bc5@d77g2000hsb.googlegroups.com...
>
> I'm in the process of speccing out the hardware for an Oracle 10.2.0.3
> server. We're expecting a database in the 200G size. Working set is
> hard to estimate, but in analagous 100G systems I see a buffer hit
> ratio or around 70% with a 2.3 G SGA (about as big as I can get it on
> a 32 bit OS). Based on this and a fair amount of spitball analysis I'd
> guess the working set on the new box to be around 8-10G.
>
> For the new hardware, we're going to 64bit os, and 64 bit Oracle.
> We're expecting 50 or so concurrent connections to the database (all
> coming from an application pool). Given the nature of the application
> though, its very unusual for the acutal concurrency to get much over
> 15 or so active sessions.
>
> The question I have is, is there any downside to me buying, say, a 32G
> box and setting the SGA size at 20G? Will I actually end up harming my
> performance with an over-large SGA (assuming I have enough physical
> memory to keep the box out of swap)?
>

Comments embedded.
On May 8, 11:35*pm, Pat <pat.ca...@service-now.com> wrote:
> On May 8, 9:00 pm, "Ana C. Dent" <anaced...@hotmail.com> wrote:
>
> > Pat <pat.ca...@service-now.com> wrote in news:12a7f1d9-6dce-4ba5-9d41-
> > 73c18ab0d...@y21g2000hsf.googlegroups.com:
>
> > When your only tool is a hammer, all problems are viewed as nails.
>
> > > The classic solution to this is:
> > > add more memory
>
> > What you are attempting to do is covert Physical I/O to Logical I/O.
>
> > A smarter solution is to add an index to reduce I/O by orders of magnitude.
>

Possibly. It's also possible that this is a highly transactional
configuration, effecting large volumes of data changes. An index
won't help there.

> The problem here isn't excessive table scans or an absence of indexes.
> The working set of indexes simply don't fit in cache all that well.
> I've got mutltiple indexes > 1 G in size and a half dozen or so >
> 500M.
>
> So, while I appreciate the tutorial on the importance of indexes as a
> component to an efficient data retreival strategy, I find it a bit odd
> that you're acting as though cache memory isn't an analagous
> component.
>

It isn't, really, since if you're running a system which modifies
large volumes of data the cached data blocks may be invalidated by the
insert/update/delete activity, requiring them to be refreshed to
ensure reliable and accurate result sets are returned. Increasing the
cache size won't help when blocks are marked as modified and thus
refreshed due to transactional activity.

> This is the database back end for an enterprise application, it's not
> a data warehouse application. It tends to aggressively chew over the
> same working set (the aforementioned 10-12G of memory) querying it in
> all sorts of unpredictable, end-user defined, ways.

No, it 'chews over' the application data which, in turn, 'churns' the
existing cache because the data blocks have been modified since the
last query used them. No amount of memory will stop that behaviour.

> If I knew a set of
> additional indexes I could add that would reduce my working set, I'd
> have already added them.

I don't believe it's the size of your 'working set' that is the
'problem'; the issue is querying constantly changing data which is
brought into the cache because the blocks, essentially, undergo
continuous change during the business day.

> At this point, the only solution I can see
> here is to bump up the SGA so that my (existing) index and data blocks
> fit in memory.

I can't believe that will do much good except to give the vendor of
your memory a better bottom line. You should install the PLUSTRACE
role then use autotrace on some of these queries during the day to
report some useful query statistics, such as redo generated. Yes, a
query can generate redo, and undo, due to delayed block cleanout, and
that phenomenon will increase your physical reads, and, yes,
increasing the SGA, and the resulting buffer cache, can help some but
Oracle restricts block cleanout for a transaction of any size to 10%
of the total buffer cache blocks; any transaction modifying a block
count in excess of the 10% threshold will relegate the cleanout of the
remaining blocks affected to the next operation which touches those
blocks, even if that operation is a (relatively) simple select
statement. To eliminate this entirely you would need to allocate a
number of blocks in the buffer cache equal to 10 times the size of the
largest transaction you could possibly execute in your database, and I
doubt you have the budget, or a machine, that can provide that much
memory. Throwing memory at this 'problem' is, in my opinion, not the
solution.

You need to find the source of this buffer cache block churning, and I
expect it's due to high transactional activity. Yes, you can install
the maximum amount of RAM your machine can support, and you can
allocate 80% of that to your database, that will only do so much to
keep data in memory. Once that data changes the cached values are no
longer valid and require a refresh, which involves physical I/O.
Unless you stop all transactional activity you can't guarantee that
the data you loaded into cache at 9:15 this morning will still be
there at 9:37 that same morning.

In this case bigger isn't always better.


David Fitzjarrell

We have a multi purpuse database of about 500 GB on a SAN storing system
with about 100 concurrent users. Although we are using many indexes it
helped very much to have 20GB of RAM for the database. The users directly
can see by the response time of the standard input windows if data is loaded
from the disks or if it is already in the main memory: if it is in memory
the problematic queries take 0.5 to 3 seconds, if it must be loaded it can
take also more than 60 seconds if there are other users requiring data from
disk. Unfortunately the Windows 64bit Operating System does seem not to make
always the best usage of the additional memory: We see many page faults in
the processes. But nevertheless I would say: after having a multiprocessor
CPU the most important part is the quantity of main memory.

Arne Ortlinghaus
ACS Data Systems

"Pat" <pat.casey@service-now.com> schrieb im Newsbeitrag
news:e71181dd-9753-4709-a063-ef2fc5254d26@a70g2000hsh.googlegroups.com...
> On May 8, 9:00 pm, "Ana C. Dent" <anaced...@hotmail.com> wrote:
>> Pat <pat.ca...@service-now.com> wrote in news:12a7f1d9-6dce-4ba5-9d41-
>> 73c18ab0d...@y21g2000hsf.googlegroups.com:
>>
>> When your only tool is a hammer, all problems are viewed as nails.
>>
>>
>>
>> > The classic solution to this is:
>> > add more memory
>>
>> What you are attempting to do is covert Physical I/O to Logical I/O.
>>
>> A smarter solution is to add an index to reduce I/O by orders of
>> magnitude.
>
> The problem here isn't excessive table scans or an absence of indexes.
> The working set of indexes simply don't fit in cache all that well.
> I've got mutltiple indexes > 1 G in size and a half dozen or so >
> 500M.
>
> So, while I appreciate the tutorial on the importance of indexes as a
> component to an efficient data retreival strategy, I find it a bit odd
> that you're acting as though cache memory isn't an analagous
> component.
>
> This is the database back end for an enterprise application, it's not
> a data warehouse application. It tends to aggressively chew over the
> same working set (the aforementioned 10-12G of memory) querying it in
> all sorts of unpredictable, end-user defined, ways. If I knew a set of
> additional indexes I could add that would reduce my working set, I'd
> have already added them. At this point, the only solution I can see
> here is to bump up the SGA so that my (existing) index and data blocks
> fit in memory.