On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.
Course Of The Month
8 days, 9 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Impact of resetting sga_target
Posted on 2014-12-06
We've just made changes to increase memory and implement hugepages. One of the objectives was to also increase db_cache_size to 26G, but got an error saying there was not enough memory. We have 64G memory with 42G in sga and 7G in pga. in our parameter file i have both sga_max and sga_target. I see someone that got the same error resolved the problem by setting sga_target =0 then alter system set db_cache_size to desired size.
My question is since the application is up, what's the impact of setting sga_target to 0 while there are users on the system?
My question is since the application is up, what's the impact of setting sga_target to 0 while there are users on the system?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
3 Comments
Active today
From your last few previous questions I know you are working with a Vendor to troubleshoot what I believe to be performance issues.
Any reason you are deviating from Automatic Memory Management?
http://docs.oracle.com/cd/E11882_01/server.112/e25494/memory.htm#ADMIN11011
As for setting sga_target to 0:
http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams052.htm#REFRN10033
Any reason you are deviating from Automatic Memory Management?
http://docs.oracle.com/cd/E11882_01/server.112/e25494/memory.htm#ADMIN11011
As for setting sga_target to 0:
http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams052.htm#REFRN10033
If SGA_TARGET is set: If the parameter is not specified, then the default is 0 (internally determined by the Oracle Database). If the parameter is specified, then the user-specified value indicates a minimum value for the memory pool.
If SGA_TARGET is not set, then the default is either 48 MB or 4 MB * number of CPUs, whichever is greater
When we startup each week after cold backup we get this advisory in the alert log telling us before we start up again to implement hugepages. Looking at directions on implementing huge pages i see:
"... Automatic Memory Management (AMM) feature which is incompatible with HugePages. Disable AMM before proceeding. To disable, set the initialization parameters MEMORY_TARGET and MEMORY_MAX_TARGET to 0 (zero)."
I think I phrased the question poorly again:-) Our current situation is we just implemented hugepages early this morning and increased other parameters. We intended to increase db_cache_size to 26G, but got an error saying there was not enough memory. Total is 64G, up from 16. SGA 24G up from 8G and PGA 7G up from 1 1/2G so there is enough memory.
I saw one of those dba-oracle guys had a similar problem that he resolved by setting sga_target to 0 then setting db_cache_size.
SQL> alter system set db_cache_size = 1024M;
alter system set db_cache_size = 1024M
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cache
Even then, it would not let me grow the buffer cache. I had to disable sga_target for 11g to finally allow it:
Solution
SQL> alter system set sga_target = 0;
System altered.
SQL> alter system set db_cache_size = 1024M;
System altered.
SQL> alter system set memory_target = 1648M;
System altered.
Right now students are banging away taking finals.
What i am really asking is if i go in and try setting sga_target to 0 on the fly, is there ANY chance it would even for a second interfere with our long suffering students?
I'm going to post another question because despite the fact that I had to settle for 16G for db_cache_size in my initfile, when i query v$sgainfo, it's saying db_cache_size IS 25,728 (~26G) , the value that i got the error on when i tried to startup with that in the initfile.
"... Automatic Memory Management (AMM) feature which is incompatible with HugePages. Disable AMM before proceeding. To disable, set the initialization parameters MEMORY_TARGET and MEMORY_MAX_TARGET to 0 (zero)."
I think I phrased the question poorly again:-) Our current situation is we just implemented hugepages early this morning and increased other parameters. We intended to increase db_cache_size to 26G, but got an error saying there was not enough memory. Total is 64G, up from 16. SGA 24G up from 8G and PGA 7G up from 1 1/2G so there is enough memory.
I saw one of those dba-oracle guys had a similar problem that he resolved by setting sga_target to 0 then setting db_cache_size.
SQL> alter system set db_cache_size = 1024M;
alter system set db_cache_size = 1024M
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cache
Even then, it would not let me grow the buffer cache. I had to disable sga_target for 11g to finally allow it:
Solution
SQL> alter system set sga_target = 0;
System altered.
SQL> alter system set db_cache_size = 1024M;
System altered.
SQL> alter system set memory_target = 1648M;
System altered.
Right now students are banging away taking finals.
What i am really asking is if i go in and try setting sga_target to 0 on the fly, is there ANY chance it would even for a second interfere with our long suffering students?
I'm going to post another question because despite the fact that I had to settle for 16G for db_cache_size in my initfile, when i query v$sgainfo, it's saying db_cache_size IS 25,728 (~26G) , the value that i got the error on when i tried to startup with that in the initfile.
Active today
The only possible issue setting sga_target to 0 is that performance can suffer if Oracle cannot get enough memory to run efficiently.
I've seen 'not enough memory' memory errors on startup when I knew there was because Oracle needs contiguous memory when it goes to get it. There was an issue when I would shutd down Oracle and immediately try to start back up memory would become fragmented.
Only a serer reboot would allow me to start up the database.
I've seen 'not enough memory' memory errors on startup when I knew there was because Oracle needs contiguous memory when it goes to get it. There was an issue when I would shutd down Oracle and immediately try to start back up memory would become fragmented.
Only a serer reboot would allow me to start up the database.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Truncate is a DDL Command where as Delete is a DML Command.
Both will delete data from table, but what is the difference between these below statements
truncate table <table_name> ??
delete from <table_name> ??
The first command cannot be …
Introduction
A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows syntax for various backup options while discussing how the different basic backup types work. It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month8 days, 9 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
734 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.