Re: ASMM

Hi,

Just to throw in what I like to do, and in general it works very well.

Hugespages and hence ASMM
For standard dev databases:

2GB SGA  
512MB shared pool
1GB Buffer cache

Then for a prod database something like (or course exact numbers depend on the database in question and the memory on the server etc.

8GB SGA
1GB shared pool
5GB buffer cache

The general idea is to use ASMM to avoid having to think to hard about specific settings for all the "pools". However having seen Oracle be really stupid in certain cases and force the buffer cache to < 100MB and hence stuff performance. I like to set minimums for the shared pool and buffer cache so as to avoid anything extreme happening but leave Oracle with some memory to manage itself.

Cheers,

Chris


On Monday, 12 May 2014, 17:19, Chris Taylor <christopherdtaylor1994_at_gmail.com> wrote:
 
Well, as an aside, I would recommend bumping up the available memory to the database and take advantage of that extra RAM - keeping in mind anything else that might be running on that server such as webservers and the like.  It might not help your ratios but it should help performance and might help your ratios *if* stuff doesn't get flushed out of the cache is used regularly.  

Chris



On Mon, May 12, 2014 at 11:09 AM, Zelli, Brian <Brian.Zelli_at_roswellpark.org> wrote:

It has 32g ram and this is currently the only database.
> 
> 
>Brian
> 
> 
>From:Chris Taylor [mailto:christopherdtaylor1994_at_gmail.com] 
>Sent: Monday, May 12, 2014 11:23 AM
>To: Zelli, Brian
>Cc: oracle-l_at_freelists.org
>Subject: Re: ASMM
> 
>Ok, those are relatively small numbers in today's world but I assume the server this database is on either has a.) not much RAM, and/or b.) several database instances running?
> 
>One thing you'll notice is this line:
>"Startup overhead in Shared Pool" - this is space used in your shared pool that is required at startup - as far as I know there is nothing we can do about this number, but this number subtracts from your available shared pool for "other things".
> 
>So, for example:  Your shared pool size is 464MB and your startup overhead is 192MB, that leaves you with a "useable" shared pool of 272MB.  That seems quite small to me.  How much RAM does your Server have installed, and how many database instances are running on it?
> 
>Chris
> 
> 
>On Mon, May 12, 2014 at 10:09 AM, Zelli, Brian <Brian.Zelli_at_roswellpark.org> wrote:
>Fixed SGA Size                   2.07003021
>Redo Buffers                     6.41796875
>Buffer Cache Size                       592
>Shared Pool Size                        464
>Large Pool Size                          16
>Java Pool Size                           16
>Streams Pool Size                         0
>Shared IO Pool Size                       0
>Granule Size                             16
>Maximum SGA Size                 1672.48828
>Startup overhead in Shared Pool         192
> 
>NAME                                SIZE_MB
>-------------------------------- ----------
>Free SGA Memory Available               576
> 
>Is the results of the query.  It is a mix and there are a lot of SQL with literals.  
> 
>Brian
> 
> 
>This email message may contain legally privileged and/or confidential information.  If you are not the intended recipient(s), or the employee or agent responsible for the delivery of this message to the intended recipient(s), you are hereby notified that any disclosure, copying, distribution, or use of this email message is prohibited.  If you have received this message in error, please notify the sender immediately by e-mail and delete this email message from your computer. Thank you. 

--
http://www.freelists.org/webpage/oracle-l