RE: database parameter change for cache and pool size
- From: Michael Dinh <mdinh@xxxxxxxxx>
- To: "development@xxxxxxxxxxxxxxxxx" <development@xxxxxxxxxxxxxxxxx>, oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 18 Jun 2010 05:34:08 -0700
I am on 10.2.0.4
I did not set sga_target in the pfile, but it looks like it is defaulted to 0
I do have sga_max_size=0 set which then get adjusted accordingly when database
starts up.
I will try adding sga_target=0 to pfile/spfile
show parameter sga
NAME TYPE VALUE
------------------------------------ --------------------------------
------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 8400M
sga_target big integer 0
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf
Of Martin Bach [development@xxxxxxxxxxxxxxxxx]
Sent: Friday, June 18, 2010 12:12 AM
To: Michael Dinh; oracle-l-freelists
Subject: Re: database parameter change for cache and pool size
Hi Michael,
I suspect you set sga_target/memory_target (you didn't say which release you
are on) to a non zero value. If so, please read on, if not the following does
not apply.
Setting a value other than 0 for an auto tuned SGA component sets a minimum
size for this component. Oracle will not shrink the component to a size smaller
than set, but still decide to auto manage it when aopropriate.
To truly turn off automatic SGA management you need to set the %target
parameter (excluding pga_aggregate_target) to 0 and set sizes for the
previously auto tuned SGA components in your initialisation file.
Regards,
Martin
Martin Bach
Oracle Certified Master 10g
http://martincarstenbach.wordpress.com
http://www.linkedin.com/in/martincarstenbach
----- Reply message -----
From: "Michael Dinh" <mdinh@xxxxxxxxx>
Date: Fri, Jun 18, 2010 03:30
Subject: database parameter change for cache and pool size
To: "oracle-l-freelists" <oracle-l@xxxxxxxxxxxxx>
Does anyone have experience as to why database cache and pool size changes
automatically?
I monitor for changes and from time to time I would get something like this.
[streams_pool_size] changed from [0] to [335544320]
[db_cache_size] changed from [1073741824] to [738197504]
From the pfile create from spfile I would get
db07.streams_pool_size=335544320
*.db_cache_size=1G
db07.db_cache_size=738197504
I do not have Automatic Shared Memory Manager enabled.
*.sga_max_size=0
*.shared_pool_reserved_size=512M
*.shared_pool_size=3G
Thanks.
Michael Dinh : XIFIN : 858.436.2929
NOTICE OF CONFIDENTIALITY - This material is intended for the use of the
individual or entity to which it is addressed, and may contain information that
is privileged, confidential and exempt from disclosure under applicable laws.
BE FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN PROTECTED HEALTH INFORMATION
(PHI). BY ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE FOREGOING, AND AGREE AS
FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI CONTAINED HEREIN,
EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT NECESSARY TO PERFORM YOUR
OBLIGATIONS RELATING TO THE RECEIPT OF THIS MESSAGE. If the reader of this
email (and attachments) is not the intended recipient, you are hereby notified
that any dissemination, distribution or copying of this communication is
strictly prohibited. Please notify the sender of the error and delete the
e-mail you received. Thank you.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
