Re: the wonders of automatic sga management

hpuxrac wrote:
> Here's an interesting blog topic from pythian:
>
> http://www.pythian.com/blogs/291/pitfalls-of-oracle-auto-sga-management
>
> Nothing like growing the shared pool to 5 gig to hold lots of SQL not
> using bind variables eh?
>
> Maybe we will see something in 11g so that we can set limits ... let
> shared pool vary between a min and a max?

I wonder if the author of this blog page has not read the Oracle documentation, or well written Oracle 10g books?

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm "In addition to setting SGA_TARGET to a non-zero value, you must set the value of all automatically sized SGA components to zero to enable full automatic tuning of these components. Alternatively, you can set one or more of the automatically sized SGA components to a non-zero value, which is then used as the minimum setting for that component during SGA tuning."

"You can exercise some control over the size of the automatically sized SGA components by specifying minimum values for the parameters corresponding to these components. Doing so can be useful if you know that an application cannot function properly without a minimum amount of memory in specific components. You specify the minimum amount of SGA space for a component by setting a value for its corresponding initialization parameter. Here is an example configuration: SGA_TARGET = 256M
SHARED_POOL_SIZE = 32M
DB_CACHE_SIZE = 100M" Even with automatic SGA tuning enabled, it is possible to specify minimum values for various SGA components. For instance, if one were to specify a large value for db_cache_size, that would automatically reduce the amount of memory available for the other components of the SGA, thus setting an upper limit of the shared pool size.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Dec 15 2006 - 08:29:33 CST