Re: Did I Misunderstand Sizing Rule Of Thumb?

A copy of this was sent to "Bill Buchan" <bill.buchan_at_ossian.net> (if that email address didn't require changing) On Sun, 12 Sep 1999 09:54:36 +0100, you wrote:

>A while back I was given a sizing rule of thumb which I've been using to
>date since I've not had time to do any proper performance analysis (blame
>the guy that sets my schedule!).
>

I hate rules of thumb... but anyway

>The rule was:
>
>1. SGA should be about a third of physical memory

If other things are happening on the box, 1/3 might be too much (other instances, other applications).

if you are using MTS -- this is way wrong -- you might need 2/3's or more of the memory dedicated to the SGA.

If you are using dedicated server, a max of 1/3 is probably a good place to start.

>2. The Shared Pool should be about 10 times the size of the Buffer Cache
>

the size of the shared pool is in no way correlated to the size of the buffer cache -- I find no correlation between the 2.

The shared pool should be sized based on what you are doing. somethings, like replication (especially before they internalized lots of the code) would demand larger shared pools to cache all of the plsql code. Other apps -- OLTP apps with a small number of total queries -- need a very small shared pool. MTS would demand a larger one, unless your are using 8.0 and up and have a Large Pool configured.

10 times the size of the buffer cache isn't a rule I'd follow. I start it smallish (10-15 meg) and go up from there.

>I'm increasing suspicious that I misheard this as I seem to be wasting a lot
>of resources. In particular, with Rule 2 surely the buffer cache should be
>bigger - isn't the overhead of fetching data to memory more than doing some
>extra parsing?
>

Actually, i've seen many systems were over 80% of their runtime was spent parsing queries (this was due to the lack of bind variables so every query was a new query and had to be parsed). You could have made the shared pool infinitely large and it wouldn't help here. by introducing bind variables -- runtimes would go way way down (and the cpu had a chance to cool off).

Hard parses should be avoided at all costs -- you will find you spend more time doing this then ANYTHING else.

>In my particular case the main application that runs on the database is a
>generic application using ODBC without any bind variables - what impact
>should that make on a sizing plan for the shared pool?
>

if it doesn't use bind variables:

• plan on very poor shared pool usage, size isn't going to matter very much at all
• it won't matter what size you make the shared pool, we'll be flushing it lots anyway
• buy lots of extra cpu's. parsing and managing the shared pool (which is a misnomer in a system that doesn't use bind variables) is cpu intensive.

>Any advice would be much appreciated.
>

try to make it use stored procedures (the sql in stored procedures will use bind variables naturally).

try to use bind variables in the application itself.

use tkprof and sql_trace and time_statistics to discover that 75% or more of your execution time in the database is spent parsing queries and not executing/fetching data...

>Thanks,
>
>- Bill.
>
>
>
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Sep 12 1999 - 08:14:14 CDT