I am having 11gr2 database on a 2 node RAC cluster . OS is RHEL version 5.8When i change the SGA_MAX_SIZE to 5GB, after i bounce it shows as 15GB.
I am trying to set my SGA_TARGET and SGA_MAX_SIZE to 5G. But after bouncing SGA_MAX_SIZE is showing as 15GB and not as 5GB
I also tried edit in pfile and then create spfile and started up with spfile. But still same i am observing.
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?
I've done lots of reading about these two parameters, SGA_MAX_SIZE and DB_CACHE_SIZE.
What I understand now is, that setting db_cache_size to a target value and setting all other buffer cache setting to 0 will enable Oracle 9i to automatically manage buffer cache values, right? This is, per meta link document 223299.1 the most important parameter in Oracle 9i regarding memory management.
Now, SGA_MAX_SIZE set the maximum value at wich I can increase the db_cache_size to?
The question is ; WHY? Why should I allocate 1G of RAM to my oracle instance and not set the db_cache_size to the exact same value? What happens with the difference in memory between the SGA_MAX_SIZE and the DB_CACHE_SIZE, ex :
SGA_MAX_SIZE = 1000M
DB_CACHE_SIZE = 500M
What happens with the 500M left over, is it used for anything ?
If sga Buffer Cache Size consume full of SGA_target size, if possible that it will cause performance any issue. I am facing CPU 100% consuming while single query execute, Which query generate monthly report data.
I have two question
1)How to fix the CPU 100% consuming
2)How to find total number user hit oracle specific schema.
Oracle 10.2.0.5 Standard
Sga_target : 14G
Sga_max :20G
Pga :3G
Below SGA details
NAME BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size 2.01795197
Redo Buffers 13.9804688
Buffer Cache Size 13632
Shared Pool Size 640
Large Pool Size 16
Java Pool Size 16
Streams Pool Size 16
Granule Size 16
Maximum SGA Size 20480
Startup overhead in Shared Pool 208
Free SGA Memory Available 6144
When I was going through the Enterprise Manager Grid Control, I found an error due to which I looked at the trace file and it said:
ORA-07445: exception encountered: core dump ACCESS_VIOLATION unable_to_trans_pc PC:0x7C81BD02 ADDR:0x49444E49 UNABLE_TO_READ]
I searched and found that it has something to do with the SGA parameters. I saw that the shared_pool_size and the sga_target paramters are set to 0...Also there are certain SQLs hanging at some point. I thought I should change the above mentioned parameters.
My question now is, can I use the Alter System statements from the SQL Plus to change these parameters, and do they change immediately or do I need to reboot the Oracle instance for those changes to take effect? I would like to do:
alter system set sga_target=400m;
alter system set shared_pool_size=200m;
would these work and take effect immediately?
I was just wondering how is the relationship between MEMORY_TARGET and SGA_MAX_SIZE parameters, if exists any at all.I set my memory parameters as:
# Enable AMM
MEMORY_MAX_SIZE=10G
MEMORY_TARGET=4G
# Disable ASMM
SGA_TARGET=0
SGA_MAX_SIZE=0
So I bounce my instances and when I checked the parameter SGA_MAX_SIZE, Oracle automatically set a value of 2456M to it:
sga_max_size big integer 2456M
sga_target big integer 0
Reading MOS ID 443746.1, there is no clear mention about this relationship. It seems that Oracle automatically set SGA_MAX_SIZE as 60% of MEMORY_TARGET.
In am running Oracle 11gR2 (11.2.0.2.3) under Red Hat Linux.
The Memory tuning advisor has recommended that I increase the size of the SGA_TARGET to 5G, from my current size of 3G.
SQL> select * from v$sga_target_advice order by sga_size;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
1536 .5 1555141 1.667 7251211
2304 .75 949130 1.0174 4572127
3072 1 932898 1 2258733
3840 1.25 930939 .9979 2168835
4608 1.5 929819 .9967 2005529
5376 1.75 929166 .996 2005529
6144 2 928980 .9958 2005529
7 rows selected.However, the SGA_MAX_SIZE parameter is set to 3G, so when I do:
SQL> ALTER SYSTEM SET sga_target=5376M scope=both;
ALTER SYSTEM SET sga_target=5376M scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size
My database used spfile. I know that the procedure to change this is the following: Create pfile from spfile, shutdown immediate, change value in pfile, startup with pfile..However, the pfile does not have any parameter called max_sga_size. It only has the following:
<dbsid>.__sga_target=3221225472
*.sga_target=3221225472
how to change sga_max_size?
What value should i set for sga_target in my oracle 10g database?
Currently -
sga_max_size = 32GB
pga_aggregate_target = 6GB
RAM on server = 64 GB
I'll need to disable db_block_buffers parameters in order to enable sga_target. right?
