To tune the OID11g the DB parameter recommendation is :
sga_target Minimum 60% of RAM
sga_max_size Minimum 60% of RAM
db_cache_size Minimum 60% of RAM
So what is RAM size..is that the RAM size on the linux BOX which is 8 GB in my case
or RAM size assigned to Oracle DB?
If I take RAM size 8 GB I am not able to modify the parameter. I am getting below error:
SQL> alter system set sga_max_size=4916M scope=both;
alter system set sga_max_size=4916M scope=both
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
how to check that total memory assigned to Oracle DB?
Please let me know the correct way to set these param, any help apprecited.
Edited by: JohnWatson on May 24, 2013 1:29 AM
Sorry, SB - I didn't mean to interrupt. But anyway, I think we both know that the problems here are only just beginning.
Edited by: JohnWatson on May 24, 2013 1:32 AM
Thanks that worked but now I am not able to restart DB
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 3221225472 cannot be set to more than MEMORY_TARGET 1275068416.
before setting these parameter my DB startup has these info:
Total System Global Area 1269366784 bytes
Fixed Size 2212976 bytes
Variable Size 956304272 bytes
Database Buffers 301989888 bytes
Redo Buffers 8859648 bytes
SGA_MAX_SIZE parameter should be less than memory_target. memory_target will auto allocate for( SGA+PGA).if we specify memory_target , AMM will be enabled and memory will be allocated upon requirement by oracle . sga_size=x sga_max_size=2x (x,2x i'm specifying x,2x for example)
SGA_MAX_SIZE < MEMORY_TARGET.
I have created a PFILE from the SPFILE exist and then manually rollback the changes of parameters and then startup the DB with PFILE option it got started again.
But still my original question How do I set 60% of 5GB RAM to the parameters like sga_target, sga_target_max etc in DB? do I need to first change the MEMORY_TARGET value = 5GB?
To tune the OID11g the DB parameter recommendation is
Sorry, but that's not the recommendation.
The recommendations are here: http://docs.oracle.com/cd/E23943_01/core.1111/e10108/oid.htm
And that document even includes a link to the database performance tuning guide (right at the bottom of the table that tells you the recommendations) which would tell you how to set them
The link is correct and in that under notes it is mentioned that:
"A higher value may be required if the directory size exceeds 1 million entries or a high rate of I/O is observed. In case of 64-bit systems, one can go up to 60-70% of the RAM available for the Oracle Database on the box."
I am using 64 bit system.
It doesn't say that 60% of your RAM is the recommended minimum. It says 1.7G is the recommended minimum, and you can go higher if needed, up to 60%. That is, if you observe a lot of I/O or you have > 1 million directory entries.
At the bottom of the table is a link that tells you "read this to learn how to set the values"
But, unless you have a reason to set it higher (other than, "oh lookie at all my memory, let me do this"), don't
Make sure you are reading the document properly, please. Just because it says you can use up to 60% for this and up to 60% for that, doesn't mean you should (do you see a problem if you did? you'd be using 120% of your memory)