Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Bal.

Asked: December 12, 2004 - 10:29 am UTC

Last updated: July 01, 2013 - 9:34 pm UTC

Version: 9i

Viewed 100K+ times! This question is

You Asked

Hi Tom,
Can you give us an idea as to how to estimate size of SGA? An experienced dba told me that the best way to estimate size for SGA is to set aside 40% of memory for sga i.e for 2gb memory 800mb is sufficient. Is this correct?
If yes, then if i add another 1gb ram how can i justify this to customer saying ONLY 40% will be used by oracle.

Thanks a lot for all your help.

and we said...

so, that is the way to estimate the SGA size. Interesting.


I always thought it is a function of what you need to do, not how much ram just happened to be installed.


But basically you need to understand how Oracle uses memory (my book Expert one on one Oracle goes into that). There is the SGA and PGA memory.

Your DBA's rule of thumb (ROT) here is "you want to use 40/50% of RAM for the SGA leaving the other 50% for the dedicated servers (processes -- they allocate PGA) and 10% or so for the OS and related processes"

That is, their ROT is how to maximize the use of memory for a typical dedicated server configuration.

That ROT is totally wrong if you are using shared server (there the SGA might be 80-90% of RAM)

That ROT is totally wrong if you are not the only game in town (not the only thing on the server).


That ROT does not mean you are using only 40% of the additional gig of ram you are putting in, it means "40% of it will go towards the SGA, the remaining will be available for dedicated servers to do sorts and hashes and stuff"


but the major considerations here are:

a) how much do you want to assign to your buffer cache for maximum performance
b) how big is your shared/java pool (a function of how much sql/plsql/java you run in your database, no magical number for all to use)
c) do you run in shared server (than the large pool is used and will be large -- that is part of the sga) or in dedicated server -- then you need to leave OS memory for dynamic allocations
d) what else is going on in the machine.


Rating

  (109 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

what would be your suggestion.

Pravesh Karthik from India, December 13, 2004 - 12:44 am UTC

Tom,

I understand what you mean to say as above, when you are going to start a new application, you will not have an idea of how big your sga components need to be, or what should be the size of buffer cache. In that case, what would be your suggestion.

I typically give the minimal values or considering the application db usuage, from the application architectural team ...set the sga_max_size. Then check for advisories through statspack or directly from the advisory tables. Check the application for another few days .. set the values ..the check for weeks. and set the optimal values as derived from the above process ...

Is this the right way?. or what would be your suggestion on this.


Thanks for your consideration
Pravesh Karthik from India

Tom Kyte
December 13, 2004 - 10:00 am UTC

i start small and go up from there. your approach is sound.

Bal Krishan, December 13, 2004 - 1:00 am UTC

Exceelent Tom. This is what i was looking for. Just one followup.
Assuming nothing else is going on the machine, can you give us a ROUGH estimate of percent divison of SGA in a typical server configuration among its components in both shared and dedicated mode for maximum performance.
a) DB buffer
b) Redo log buffer
c) Shared pool
d) Large pool
e) Java pool

Thanks in advance. You are doing a great job.

Tom Kyte
December 13, 2004 - 10:08 am UTC

nope.

if you really don't know, use 10g and just set sga_target and let it figure it out empirically over time.

You are missing the point...

Scot, December 13, 2004 - 10:46 am UTC

I think you are missing the point. Your followup is the same as your original question, and Tom has already answered both. There is no rough estimate that applies to all situations. Are you running a data warehouse with lots of huge queries? Are you running a heavy oltp system? You can't just list all of the components of Oracle memory that exist and expect someone to be able to tell you how to set them. Read the concepts guide and administrators guide chapters on memory allocation and use, then read the performance tuning guide, and then follow what Pravesh suggested and test it out.

sga_target & sga_max_size

Enrique, December 20, 2004 - 7:16 am UTC

Hi

I am configuraring a new Oracle 10g instance for production purposes. I read from Oracle 10g new features for administrators exam guide (from Oracle Press) that when we use sga_target we should not set any parameters for the 4 dynamic tunable components, buffer cache, shared pool, java pool and large pool to reduce database's ability to adapt to database wrkload changes. How true is this? If I set sga_target to 1000M how much memory will these 4 components consume to start?

I have another question about sga_target & sga_max_size. If we set sga_max_size bigger than sga_target will sga_target always be lower than sga_max_size? For example

sga_target = 1200M
sga_max_size = 1600M

Does this make sense? Will Oracle allocate memory up to 1600M sometime or maximum it will reach 1200M. What's the point combining these two parameters, shouldnt we just use sga_target and forget about sga_max_size?

Tom Kyte
December 20, 2004 - 8:54 am UTC

how true is it?

100% true, if you set the sga_target, the other 4 components are set for you.


if you set the SGA_TARGET to 1000m, the 4 components will be sized to consume 1000m.  consider:

SQL> show parameter sga_target
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------<b>
sga_target                           big integer 1000M</b>


SQL> show sga
 
Total System Global Area <b>1048576000 bytes</b>
Fixed Size                   782424 bytes
Variable Size             259002280 bytes
Database Buffers          788529152 bytes
Redo Buffers                 262144 bytes


SQL> show parameter pool
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
buffer_pool_recycle                  string
global_context_pool_size             string
java_pool_size                       big integer 0
large_pool_size                      big integer 0
olap_page_pool_size                  big integer 0
shared_pool_reserved_size            big integer 12373196
shared_pool_size                     big integer 0
streams_pool_size                    big integer 0
SQL>


<b>basically, Oracle will setup reasonable initial sized pools (if you know how to peek at _ parameters, you'll see them:

__java_pool_size               4194304
__large_pool_size              4194304
__shared_pool_size             247463936

) and will put the rest in the buffer cache.  Over time, if the pools need more, it'll steal from the buffer cache and increase them.</b>


sga_target has to be less than or equal to sga_max_size.  It depends on the OS how the memory is reserved, but basically your 1200/1600 would have you start with an SGA of 1,200 meg that could be grown by you to 1600m (using alter system)


SQL> show parameter sga
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 1200M
sga_target                           big integer 1008M

SQL> alter system set sga_target = 1100m;
 
System altered.
 
SQL> alter system set sga_target = 1300m;
alter system set sga_target = 1300m
*
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

 

sga_target

A reader, December 20, 2004 - 10:05 am UTC

Hi

So if we set sga_target we should forget about setting parameters for shared pool, large pool, java pool and buffer cache correct?

Tom Kyte
December 20, 2004 - 10:24 am UTC

that would be the prefered way, you can set the others -- then they are used as "minimums" -- but in for a penny -- in for I pound on this one I say

you would either

o use auto SGA sizing
o or not



what does Oracle do with those extra memory when sga_max_size is set

A reader, December 21, 2004 - 6:20 am UTC

Hi

From your demo

sga_target = 1200M
sga_max_size = 1600M

when you do show SGA it shows that 1600M is used (this is because of platform) but if you check v$sga_dynamic_components you can see that not 1600M is used, even we add all SGA components from v$sgastat we dont see 1600M is used but at OS level 1600M is used. What does Oracle do with the extra 400M?




Tom Kyte
December 21, 2004 - 9:02 am UTC

Oracle just has it allocated -- the real question is "what does the OS do with it"

Typically the OS will not have it in RAM, it'll be virtually there -- meaning that while it may be allocated -- it is not being used, it is on disk.

ORA-00821: Specified value of sga_target is too small, needs to be at least 296M

Dawar, February 28, 2005 - 3:57 pm UTC

Tom,

I have installed 10g Release 1 (10.1.0.3) Patch Set on 10.1.0.2.0. (for Lunix x86)

I followed the steps from the README for 3761843.
After complete succsufull message.
I am trying to do some post installation steps as mentioned in the document above.

Here are the steps I done after patch installation.
With error message.


2) For single-instance installations, start the listener as follows:
$ lsnrctl start
#
4) Use SQL*Plus to login to the database as the SYS user with SYSDBA 
privileges:
$ sqlplus "/ AS SYSDBA"
#
5) Start the database:
SQL> STARTUP
#
6) Set the value of the SHARED_POOL_SIZE initialization parameter to at least 
150 MB:
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='150M' SCOPE=spfile;
#
7) Set the value of the JAVA_POOL_SIZE initialization parameter to at least 120 
MB:
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='120M' SCOPE=spfile;
#
8) Shut down the database:
SQL> SHUTDOWN
9)
SQL> STARTUP UPGRADE
ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0

*******************************************************

No I wanted to run catpatch.sql so I logged on to database but

oracle@abc:~/product/10.1.0/db_1/rdbms/admin> sqlplus

SQL*Plus: Release 10.1.0.3.0 - Production on Mon Feb 28 11:48:04 2005

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup
ORA-00821: Specified value of sga_target is too small, needs to be at least 296M

What should I do to solve this issue?


Regards,
Dawar



 

Tom Kyte
February 28, 2005 - 4:01 pm UTC

alter the system set the sga_target to 296m or more

You've upped the size of other SGA components so that now your target sga size that you had set before is too small to hold everything.

ORA-01034: ORACLE not available

Dawar, February 28, 2005 - 5:26 pm UTC

oracle@abc:~> sqlplus

SQL*Plus: Release 10.1.0.3.0 - Production on Mon Feb 28 14:25:13 2005

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> alter system set sga_target ='296M';
alter system set sga_target ='296M'
*
ERROR at line 1:
ORA-01034: ORACLE not available


SQL> startup
ORA-00821: Specified value of sga_target is too small, needs to be at least 296M
SQL>

Regards,
Dawar
 

Tom Kyte
February 28, 2005 - 5:32 pm UTC

ok, assuming you want to get well soon -- do this (messy but it'll work)

cd $ORACLE_HOME/dbs
strings -a spfile$ORACLE_SID > init$ORACLE_SID.temp

edit init$ORACLE_SID.temp
fix up the sga_target


SQL> connect / as sysdba
SQL> startup force pfile=init<your_sid>.temp
SQL> create spfile from pfile;
SQL> shutdown


 

DAWAR, February 28, 2005 - 6:21 pm UTC

You said

strings -a spfile$ORACLE_SID > init$ORACLE_SID.temp

I think this is " $ORACLE_SID.ora "

strings -a spfile$ORACLE_SID.ora > init$ORACLE_SID.temp

is it correct?

Without ora its found nothing but with ora its work.
If my understanding is correct you do not have to reply me.

thanks

Dawar


Tom Kyte
February 28, 2005 - 6:29 pm UTC

not if you want to use the startup force command I gave you.

It could be called x.txt, whatever.dat, it is just a file name.

I called it .temp in case you have a .ora file there, did not want to clobber it.

ORA-01078: failure in processing system parameters

Dawar, March 01, 2005 - 11:36 am UTC

Tom,

I have craeted initsid.temp file as below:

Cd $ORACLE_HOME/dbs
strings -a spfile$ORACLE_SID > init$ORACLE_SID.temp

I have edited init$ORACLE_SID.temp and change the size of sga_target to 310378496.


oracle@abc:~/product/10.1.0/db_1/dbs> ls -ltr
total 2855
-rw-r--r--    1 oracle   oinstall    12920 2004-08-31 22:08 initdw.ora

-rw-r--r--    1 oracle   oinstall     8385 2004-08-31 22:08 init.ora

-rwSr-----    1 oracle   oinstall     1536 2005-01-01 11:52 orapwmysid

-rw-r-----    1 oracle   oinstall  2867200 2005-02-21 07:33 snapcf_mysid.f

-rw-rw----    1 oracle   oinstall     1544 2005-02-28 11:06 hc_mysid.dat

-rw-r-----    1 oracle   oinstall       24 2005-02-28 11:06 lkMYSID

-rw-r-----    1 oracle   oinstall     3584 2005-02-28 11:12 spfilemysid.ora

-rw-r--r--    1 oracle   oinstall      621 2005-02-28 14:27 alert_mysid.log

-rw-r--r--    1 oracle   oinstall     1114 2005-02-28 15:17 initmysid.temp

*******************************************************************


oracle@abc:~/product/10.1.0/db_1/dbs> sqlplus /nolog

SQL*Plus: Release 10.1.0.3.0 - Production on Tue Mar 1 08:22:58 2005

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL> connect as sysdba
Enter user-name: sys
Enter password:
Connected to an idle instance.
SQL> startup force pfile=initmysid.temp
ORACLE instance started.

Total System Global Area  310378496 bytes
Fixed Size                   778896 bytes
Variable Size             291773808 bytes
Database Buffers           16777216 bytes
Redo Buffers                1048576 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;
create spfile from pfile
*
ERROR at line 1:
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file
'/opt/oracle/product/10.1.0/db_1/dbs/initmysid.ora'


SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.



Regards,
Dawar

 

Tom Kyte
March 01, 2005 - 11:41 am UTC

sorry, that command was wrong ;) it'll use the default .ora name.

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_616a.htm#2072538 <code>

shows the create spfile command and how to tell it what pfile to use.

Dawar, March 01, 2005 - 11:53 am UTC

Tom,

But now I do not see spfilemysid.ora file.

oracle@abc:$ORACLE_HOME/dbs> ls -ltr
total 2851
-rw-r--r-- 1 oracle oinstall 12920 2004-08-31 22:08 initdw.ora

-rw-r--r-- 1 oracle oinstall 8385 2004-08-31 22:08 init.ora

-rwSr----- 1 oracle oinstall 1536 2005-01-01 11:52 orapwmysid

-rw-r----- 1 oracle oinstall 2867200 2005-02-21 07:33 snapcf_mysid.f

-rw-rw---- 1 oracle oinstall 1544 2005-02-28 11:06 hc_mysid.dat

-rw-r--r-- 1 oracle oinstall 621 2005-02-28 14:27 alert_mysid.log

-rw-r--r-- 1 oracle oinstall 1114 2005-03-01 08:22 initmysid.temp

-rw-r----- 1 oracle oinstall 24 2005-03-01 08:41 lkMYSID

Do I need to create spfile$ORACLE_SID.ora file?

If yes, how?

I think I need to create spfile$ORACLE_SID.ora from init$ORACLE_SID.temp and then create init$ORACLE_SID.ora from spfile$ORACLE_SID.ora.

** strings -a init$ORACLE_SID.temp > spfile$ORACLE_SID.ora

** strings -a spfile$ORACLE_SID.ora > init$ORACLE_SID.ora

Is it correct?


Regards,
Dawar


Tom Kyte
March 01, 2005 - 12:31 pm UTC

you do not show us what commands you used.

where is the ORIGINAL spfile go to?

Ok, here it is step by step:

[ora9ir2@xtkyte-pc dbs]$ strings -a spfileora9ir2.ora > init$ORACLE_SID.temp

[ora9ir2@xtkyte-pc dbs]$ mv spfileora9ir2.ora spfileora9ir2.ora.backup

[ora9ir2@xtkyte-pc dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.5.0 - Production on Tue Mar 1 12:24:49 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.

idle> startup force pfile=initora9ir2.temp
ORACLE instance started.

Total System Global Area 126948840 bytes
Fixed Size 452072 bytes
Variable Size 58720256 bytes
Database Buffers 67108864 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

idle> create spfile from pfile='/home/ora9ir2/dbs/initora9ir2.temp';
File created.

idle> !ls -ltr
total 20
-rw-rw---- 1 ora9ir2 ora9ir2 2560 Mar 1 12:23 spfileora9ir2.ora.backup
-rwSr----- 1 ora9ir2 ora9ir2 1536 Mar 1 12:24 orapwora9ir2
-rw-rw-r-- 1 ora9ir2 ora9ir2 996 Mar 1 12:24 initora9ir2.temp
-rw-rw---- 1 ora9ir2 ora9ir2 24 Mar 1 12:24 lkORA9IR2
-rw-rw---- 1 ora9ir2 ora9ir2 2560 Mar 1 12:25 spfileora9ir2.ora

idle> startup force
ORACLE instance started.

Total System Global Area 126948840 bytes
Fixed Size 452072 bytes
Variable Size 58720256 bytes
Database Buffers 67108864 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
idle>


and back in business.

Dawar, March 01, 2005 - 12:10 pm UTC

Tom,

I followed your link as below but getting this error while trying to create spfile.

Examples
Creating a Server Parameter File: Examples

The following example creates a default server parameter file from a client initialization parameter file named t_init1.ora:

CREATE SPFILE 
   FROM PFILE = '$ORACLE_HOME/work/t_init1.ora';

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_616a.htm#2072591

*************************************************************************************************
SQL*Plus: Release 10.1.0.3.0 - Production on Tue Mar 1 09:04:56 2005

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.
SQL> create spfile from pfile = '/opt/oracle/product/10.1.0/db_1/dbs';
create spfile from pfile = '/opt/oracle/product/10.1.0/db_1/dbs'
*
ERROR at line 1:
ORA-01012: not logged on

Regards,
Dawar
 

Tom Kyte
March 01, 2005 - 12:34 pm UTC

you have to start the instance.

Dawar, March 01, 2005 - 12:44 pm UTC

Tom,

My idea has also work.

create spfile$ORACLE_SID.ora from init$ORACLE_SID.temp and
then create init$ORACLE_SID.ora from spfile$ORACLE_SID.ora.

** strings -a init$ORACLE_SID.temp > spfile$ORACLE_SID.ora

** strings -a spfile$ORACLE_SID.ora > init$ORACLE_SID.ora

Now I can od the above process.
Thanks for telling this great trick.


Anyway Is there any limit to put SGA size?

I wonder its better to put large size for better performance.
Currently I used 296M only.

Regards,
Dawar

how to calculate sga used currently

raaghid, March 22, 2005 - 10:35 am UTC

when the oracle server is running , how to calculate how much sga utilized.

please give a sql or link to page.

Tom Kyte
March 22, 2005 - 11:13 am UTC

v$sgastat


sql is here thanks.

Raaghid, March 23, 2005 - 9:07 am UTC

yes. I got it through this sql.

select round(sum(bytes)/1024/1024,2) total_sga,
round(sum(decode(name,'free memory',bytes,0))/1024/1024,2) free,
round((sum(decode(name,'free memory',bytes,0))/1024/1024)/(sum(bytes)/1024/1024)*100,2) free_per
from
v$sgastat
/



sga is stable

Raaghid, March 23, 2005 - 9:16 am UTC

Tom, I have checked the production server through the above sql.

since we have added memory, we have restarted the server. (normally we wont do)
In the (1st day) morning SGA was used less.. and more free. And it was increasing slowly. And now by evening it is stable ie.. around 25 % is free (sometime 24%+ sometime 25 %) for last 3 hrs or so. (+ or - some 1%)
So it means, SGA which has been set already is ok and 25% is free. Since most of the sqls could have been used by the users today from the morning, new sqls to come for parsing will be very less.

Anyhow, I will monitor the same for few more days. Now please confirm my understanding that 75% of currently alloted SGA is used and around 25 % is free. So no need to increase/decrease SGA. (still I will monitor for some more days before coming to final conculsion)
Note: I have NOT used ROT 1/3rd of RAM should be SGA

Tom Kyte
March 23, 2005 - 9:26 am UTC

the sga is a collect of memory pools. 25% "free" would be wasted memory, you want near 100% used ultimately without lots of reloads in the shared pool and such.


not sure what or how you are measuring this.

sga size

Raaghid, March 23, 2005 - 9:49 am UTC

ok Tom,
If this trend (25%free) continues, then I will reduce to 80%, so it means nearly 4 or 5% will be free, so that i am not wasting any memory. pl reply

Tom Kyte
March 23, 2005 - 6:01 pm UTC

you haven't told us what you mean by "25% free" at all, so -- no comment.

database creation

friend, March 23, 2005 - 2:22 pm UTC

Greetings!
Please provide suggestion on init settings as I have to create one database 20gb to support 100 users.

control_files = (/oracle/Friendprd/oradata01/Friendprd_01.ctl,
/oracle/Friendprd/oralog01/Friendprd_02.ctl,
/oracle/Friendprd/oralog02/Friendprd_03.ctl)


background_dump_dest = /oracle/admin/Friendprd/bdump
user_dump_dest = /oracle/admin/Friendprd/udump
core_dump_dest = /oracle/admin/Friendprd/bdump
audit_file_dest = /oracle/admin/Friendprd/bdump


job_queue_processes=2
AQ_TM_PROCESSES=0

db_files = 400 # MEDIUM

db_file_multiblock_read_count = 16 # LARGE

db_block_size=8192

db_block_buffers = 90000 # LARGE

shared_pool_size = 300000000 # LARGE

log_checkpoint_interval = 10000

processes = 100 # MEDIUM

parallel_max_servers = 5 # SMALL

log_buffer = 10485760
local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=hugo)(PORT=1527))'
timed_statistics = true # if you want timed statistics
max_dump_file_size = 10240 # limit trace file size to 5 Meg each

sort_area_size=5000000

# log_archive_start = true
# log_archive_dest = /oracle/Friendprd/oraarch
# log_archive_format = "Friendprd_%s.log"

optimizer_mode = choose
compatible = 9.2.0
job_queue_processes=2
db_name=Friendprd

undo_tablespace=undotb1
undo_management=auto
undo_retention=300
#dblink_encrypt_login=true
sql92_security=true
Audit_trail=OS



load averages: 5.81, 6.32, 6.71
977 processes: 964 sleeping, 4 zombie, 2 stopped, 7 on cpu
CPU states: 55.4% idle, 33.8% user, 5.7% kernel, 5.1% iowait, 0.0% swap
Memory: 16G real, 488M free, 2364M swap in use, 7878M swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
22727 briosso 11 0 0 35M 10M cpu27 285.6H 6.23% java
4453 briosso 11 0 0 34M 10M cpu28 191.7H 6.23% java
9927 briosso 11 0 0 35M 10M cpu19 190.2H 6.22% java
23624 briosso 11 0 0 36M 13M cpu24 185.8H 6.21% java
27677 oracle 11 60 0 0K 0K cpu25 51:16 1.75% oracle
18822 oracle 1 0 0 0K 0K sleep 89:13 0.92% oracle
19804 oracle 1 59 0 640M 613M sleep 0:02 0.46% oracle
27472 oracle 14 4 0 203M 56M sleep 342:30 0.42% java
3771 root 1 35 0 68M 22M sleep 51.8H 0.34% scopeux
20248 oracle 1 0 0 1245M 1231M sleep 631:54 0.31% oracle
19851 oracle 1 3 0 3392K 2800K cpu30 0:00 0.15% top
21597 oracle 1 1 0 629M 599M sleep 17.4H 0.14% oracle
19367 root 1 3 0 6128K 3264K sleep 0:02 0.12% boks_sshd
19874 oracle 11 4 0 641M 608M sleep 0:00 0.11% oracle
19923 oracle 1 3 0 639M 605M sleep 0:00 0.10% oracle


Tom Kyte
March 23, 2005 - 6:24 pm UTC

size of database is almost not relevant.

what is DONE in the database is.

A reader, March 23, 2005 - 4:06 pm UTC

Friend, If you are on 9i you should'nt be using
db_block_buffers = 90000

db_cache_size is the one, your shared_pool_size is way to much, bring it down.

I'm sure there is more to come, but that will be from Tom :-)

Rob K, March 23, 2005 - 6:11 pm UTC

The first thing I would do is put your database in archive mode and read up on RMAN. Your 100 users will be upset if you lose your database and are unable to recover.

Rob

database

friend, March 23, 2005 - 11:24 pm UTC

Hi Tom,

I am not sure about my settings and this database is normally for select and testing of application tool.
I know SGA setting is not correct so need your help to think in right direction.
hAVE TO REPLACE db_block_buffer with db_cache_size

Tom Kyte
March 24, 2005 - 8:33 am UTC

"for select and testing of application tool" is not "sufficient".

do you have a DBA you can consult with.

what is 25%

Raaghid, March 24, 2005 - 12:04 am UTC

select round(sum(bytes)/1024/1024,2) total_sga,
round(sum(decode(name,'free memory',bytes,0))/1024/1024,2) free,
round((sum(decode(name,'free
memory',bytes,0))/1024/1024)/(sum(bytes)/1024/1024)*100,2) free_per
from
v$sgastat
/

777.79 194.45 25.00

Out of 778, 194.45 is free ie. 25 %.

If this trend continues can I reduct SGA by 20% so that 5% will be free and no wastage of memory.


Tom Kyte
March 24, 2005 - 8:34 am UTC

you had better look at WHAT COMPONENTS have free memory.

you wouldn't want to shrink the large pool if the java pool is the free thing and so on.



yes valid point

Raaghid, March 24, 2005 - 9:52 am UTC

Yes. valid point. thanks

statistics of sga

Raaghid, March 25, 2005 - 5:13 am UTC

v@sgastat
.06 fixed_sga
390.63 db_block_buffers
5 log_buffer
216.81 free memory shared pool
1.05 miscellaneous shared pool
.04 KGFF heap shared pool
.22 State objects shared pool
.02 table columns shared pool
3.3 db_block_hash_buckets shared pool
0 KGK heap shared pool
0 PLS non-lib hp shared pool
0 type object de shared pool
.16 log_buffer shared pool
.1 ktlbk state objects shared pool
0 errors shared pool
11.42 KQLS heap shared pool
.09 long op statistics array shared pool
0 fixed allocation callback shared pool
2.08 PL/SQL MPCODE shared pool
.06 branches shared pool
.21 transactions shared pool
.14 db_handles shared pool
.05 SYSTEM PARAMETERS shared pool
2.15 dictionary cache shared pool
.01 joxlod: init h shared pool
0 trigger inform shared pool
.05 character set object shared pool
1.02 PL/SQL DIANA shared pool
38.99 library cache shared pool
.07 trigger defini shared pool
76.55 sql area shared pool
.19 processes shared pool
.48 sessions shared pool
.03 table definiti shared pool
.08 Checkpoint queue shared pool
.68 event statistics per sess shared pool
25.94 db_block_buffers shared pool
.11 message pool freequeue shared pool

Now free memory is 216mb. But how to know which of the above is how much free. pl revert


Tom Kyte
March 25, 2005 - 7:54 am UTC

pl revert?

but it would appear your shared pool is large and you are not using much of it.

A reader, May 27, 2005 - 10:09 am UTC

Tom.

As now we have sga_target & sga_max_size, when would you prefer manual sizing of sga parameters?

Thanks.



Tom Kyte
May 27, 2005 - 12:15 pm UTC

</code> http://oracle.apress.com/ <code> check out the chapter on memory structures. Basically, it only sizes bits of the SGA, not everything so keep, recycle, nk caches -- log buffers and so on -- you still do manually.


For a system that has a DBA dedicated to the care and feeding thereof, manual will probably be used for the immediate future. For the system that runs on a box in the corner that you don't have time to really look at closely, auto will rule the day. (opinion). I'm finding on "ok systems", the automatic is doing all right. I haven't heard of any horror stories -- but I haven't really seen personally a big system doing it as yet. Some time to get acceptance on it I guess.

A reader, May 27, 2005 - 9:52 pm UTC

Tom,

Thanks for the response

"log buffers and so on -- you still do manually."

could you please elaborate on the "so on"...

Thanks..

Tom Kyte
May 28, 2005 - 9:14 am UTC

I elaborate in great detail at the apress link above, but suffice to say that only

db_cache_size, shared_pool_size, large_pool_size and java_pool_size

are auto-sga managed, the rest (which are all listed and explained in that chapter) are not. These include log_buffer, streams_pool, db_Nk_cache_size, db_keep_cache_size, and db_recycle_cache_size.

A reader, May 28, 2005 - 10:26 am UTC

Thanks.

Hi tom

sikanthar, July 08, 2005 - 6:26 am UTC

Tom,

1.if i fail to set SGA_MAX_SIZE in init.ora what would happen to this size?is this calculated by adding all other memory parameters size!

2.what's the default value for SGA_MAX_SIZE ?


Tom Kyte
July 08, 2005 - 7:54 am UTC

ORA-00821 resolved!

Michael LeVan, September 21, 2005 - 1:18 pm UTC

Thanks Tom! I'm working with Oracle 10g and ran into this error. I found you by querying google and followed your thread in working with Dawar. Thanks again.

hc_<sid>.dat

Deepak, October 19, 2005 - 6:23 am UTC

Hi Tom,

In my 10g DB I found files named hc_<sid>.dat in <ORACLE_HOME>\database folder on my W2K box. When I opened the file through text editor it did seem like a binary file containing a line "DO NOT DELETE OR OVERWRITE THIS FILE!!!" in the begining.

Is it used for locking SGA?

If YES will Oracle truely lock it in memory in Windows environments (as I have read somewhere that on windows you can not guarantee locking of SGA, am I right?)?

If NO the please help me in understanding the real purpose of this file.

Tom Kyte
October 19, 2005 - 7:19 am UTC

it is a 'heartbeat' file. It is not for locking the sga. It is an internal undocumented file that you should not delete or overwrite ;)

Increase SGA_MAX_SIZE greater than 1.7G

Nadir Pervez, November 22, 2005 - 2:18 am UTC

I tried to increase SGA size greater than 1.7G, but i received an error i.e "ORA-27100 shared momory realm already exists."
I then recoverd my database using previous pfile.
Can you tell me how to increase the size of SGA greater than 1.7G?

Tom Kyte
November 22, 2005 - 8:31 am UTC

tell us how you tried to increase your sga. step by step. sounds like the shutdown didn't shutdown and the shared memory segment was still there.

Need a bit of "detail", like - what you did, what os, etc. even a version of the database would be useful..

Sizing SGA

Amitkumar D Mehta, January 28, 2006 - 2:47 am UTC

Nice to see differnet scenario.
But in ideal case, what should be the size of differed parts of SGA , how to calculate?



Tom Kyte
January 28, 2006 - 1:00 pm UTC

If you don't really have a good idea - use the automatic SGA feature of 10g.

Else, as stated - lots of times - you need slightly more information about what, how, whatever you will be doing and how much data you'll be doing it to and how many users will be doing it and so on.

A reader, February 01, 2006 - 9:07 am UTC

Hi Tom, the strings trick works too on windows, there is a utility on </code> http://www.sysinternals.com/Utilities/Strings.html <code>

Is there any Performance Impact by Increasing Buffer Cache ?

Vivek Sharma, February 15, 2006 - 6:38 am UTC

Hi Tom,

In one of a note, I have read that "Increasing the size of SGA might degrade the performance due to buffer cache contention". I know, if I oversize my shared pool, it will degrade the performance due to shared pool latch contention and was of the opinion that though increasing buffer cache might not improve the performance but will atleast not degrade the performance of a database. Want to know your opinion on this.

Thanks and Regards
Vivek

Tom Kyte
February 15, 2006 - 9:35 am UTC

I do not agree with the diagnoses so much (as far as 'increasing the shared pool will increase latch contention', or 'due to buffer cache contention')

it is true that oversizing your SGA can lead to

a) better performance
b) the same performance
c) incredibly bad performance

but, can you point us to something you have read so we can see the point being made in context?

Performance degradation due to oversized Buffer Cache

Vivek Sharma, February 15, 2006 - 1:28 pm UTC

Hi Tom,

Thanks for your reply. In my question (above), I have raised a doubt over an statement passed by one of the DBA that "an oversized SGA might degrade the performance due to buffer cache contention". The DBA has specifically said about the buffer cache contention. Hence, my question was that will an oversized buffer cache degrade the performance ? I feel that as I gradually increase the buffer cache, at one point of time, I will not see any performance improvement but will also not observe any degradation. Am I correct ?

Regards
Vivek

Tom Kyte
February 15, 2006 - 2:07 pm UTC

it will take longer to discover a block isn't present in the cache.

the buffer cache will be able to hold many more dirty blocks than before, shutdown might be affected a lot by that. startup after an instance failure as well (can be tuned by making dbwr be aggressive keeping the cache clean)

offlining things could take much longer, truncates as well. things that need to process the buffer cache will be affected by a huge cache.

it is unlikely to be the cause of buffer cache contention (we create more lists as the cache gets larger) but it is one of the reasons we have the option for 32k blocks (to reduce the number of blocks being managed). The more you got, the more you got to manage.

Bigger is not always better. Not everything is positively impacted by larger buffer caches.

SGA_TARGET in 10g R2

A reader, May 25, 2006 - 4:25 pm UTC

Hi Tom.

In my environment I have set SGA_TARGET to 4 GB and SGA_MAX_SIZE to 6 GB. I have these questions:

1. From a monitoring prospective, how will I be able to tell if SGA_TARGET is no longer sufficient and needs to be increased? Is there a query to determine that? I know that AMM takes from the buffer cache to allocate to the other pools dynamically, but I wan to proactively monitor for a time when SGA_TARGET is no longer sufficient and I am wondering how to do so.

2. What is the recommended gap (or percentage of difference) between SGA_TARGET and SGA_MAX_SIZE? Should both parameters be set to the same value initially and if not, why? I understand that SGA_TARGET can be adjusted dynamically by the DBA and SGA_MAX_SIZE requires a reboot.

Thanks for any advice.

Tom Kyte
May 25, 2006 - 6:01 pm UTC

1) statspack or AWR and the advisors will be useful to tell you if you could benefit from the various pools being larger.

2) none. There is no such thing as a "recommended gap". You are simply telling us

a) how large the sga should be right now
b) how big you would like to have the flexibility to grow it to without shutting down

Can you elaborate more ...

A reader, May 26, 2006 - 5:41 am UTC

Can you elaborate more on my first question?

I am looking for some query I can schedule to run and email me if the SGA_TARGET need to be larger. I am using AWR through Enterprise manager.

Thanks.

Tom Kyte
May 26, 2006 - 8:53 am UTC

Right after you give me the indisputable logic, I'll show you how to email it.

For me, this is a decision YOU make based on YOUR system since YOU know your memory sizes (how much ram you actually have) and YOU would be reviewing the AWR advisories from time to time to evaluate if YOU would benefit from a larger sga_target.

A reader

A reader, May 26, 2006 - 10:05 am UTC

Since AMM takes from the buffer cache to allocate to the other pools, can I say that if my buffer cache falls below a certain amount, the SGA_TARGET needs to be adjusted?

Tom Kyte
May 27, 2006 - 9:20 pm UTC

No, I cannot say that, you cannot say that.

Tell me, what magical number would that be?

And what if you have no more physical memory to give to the SGA ?

Look - there are many variables here, if you want it all automatic, give ALL FREE MEMORY on your machine to the SGA and be done with it. No more monitoring, no more thinking about how it could be, should be, would be used.

Set the PGA aggregate target and the SGA target to be a tad less than physical memory on the machine and you are done.

SGA Memory Settings

Tim, September 20, 2006 - 3:31 pm UTC

We are adding another 4GB of physical memory to our database server and allocating 3GB of this to the recycle pool. Oracle Server 9.2.0.4. OLAP application.
Under consideration is to also lock the SGA into memory.
Could you explain (or direct to appropriate reference) why or why not we would want to lock the SGA into memory? This is a concept I am not familiar with - and your guidance would be appreciated here.
Thanks.

Tom Kyte
September 20, 2006 - 3:46 pm UTC

well, locking the sga into memory prevents portions of it from being paged/swapped out.

which would happen if you exceed real physical memory on the machine

which means you made the sga too large for your machine

which means to me - that you really don't want to lock the sga, you want to size the sga and pga aggregate target for your physical ram.

SGA Memory Settings

Tim, September 20, 2006 - 3:39 pm UTC

BTW - With regard to above post - we are running AIX V 5.2.

unswappable SGA

Mike, September 21, 2006 - 3:48 am UTC

We were getting parts of our SGA swapped, causing all kinds of havoc--10gR1 on Red Hat Enterprise Linux 3/AMD64. There was lots of memory, but it the OS was greedily keeping it in its filesystem cache.

Rather than trying to teach Linux to be less "swappy", we pinned the SGA into memory by enabling hugepages. We now have 0 bytes of swap used--even after a subsequent SGA size increase--and are loving life again.

Tom's your-SGA-is-too-big-if-you-swap should be taken as doctrine, not dogma.

About AMM

CT, October 09, 2006 - 6:21 pm UTC

Tom, I'd like to post this as a new topic, but since you're "Too Busy" for me (story of my life ;) I'll post a comment here.

What are your thoughts about AMM? Since I've changed companies, I now have had a chance to look at it and I'm a little (maybe more than a little suprised) about a couple of things.

First the stats of the box:
Win 2003 Server 64-bit
4 3.6G Dual Core XEON Processors
32G RAM

We have the max sga size set to ~20G and the Total SGA Size set to ~15G. Oracle has sized the Shared Pool to ~5.1G (!) and the buffer cache to ~9G. With Oracle automatically reducing the buffer cache and increasing the Shared Pool, this seems counter intuitive to my tuning (and reading your books) experience. Isn't a 5.1G shared pool incredibly crazy? We have several legacy apps on this db that were recently upgraded and they don't use bind variables which I think may be causing Oracle to keep upsizing the shared pool.

Your thoughts?

Tom Kyte
October 09, 2006 - 9:28 pm UTC

sounds like you haven't a bind variable in sight, am I right - do you have a massively bad hard parse problem here?

Ahh, I see that now, you do. Yes, that is what auto memory management will tend to do over time, your shared pool is TRASHED, it says "instead of 4031, we'll steal from the cache"

You can set minimums for the various caches if you like - things that start with A work for many to most people, but not in every single case. When you have a disaster of a system - as you do (no binds, ugh), you might have to do somethings "yourself"

Tools

A reader, October 10, 2006 - 1:23 pm UTC

"...you might have to do somethings "yourself" "

That made me laugh. As if I'd be happy letting any tool determine how my db runs. I much prefer controlling as many options as I can and using tools to point me in a (sometimes not even the right) direction.


it helps but have one doubt

RK, December 27, 2006 - 5:30 pm UTC

I have one of the database. Initially both sga_target and sga_max_size set to 800m. Then I set sga_target to 712m then check for sizes and they are showing
sga_max_size=800m
sga_target=712m
but when I select sum(value)/1024/1024 from v$sga; showing 800m instead of 712m. Why is this showing like this?


SQL> show parameter sga;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 800M
sga_target big integer 800M
SQL> alter system set sga_target=712m scope=both;

System altered.

SQL> show parameter sga;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 800M
sga_target big integer 712M
SQL> select sum(value)/1024/1024 from v$sga;

SUM(VALUE)/1024/1024
--------------------
800

Please let me know why this is like this?

Thanks
Tom Kyte
December 28, 2006 - 9:41 am UTC

because the max size will always show there - we don't use up to max size, we use up to target - the OS will not have paged in the memory we are not using - it is fine.

Auto SGA

Deepak, September 05, 2007 - 2:10 pm UTC

Hi Tom,

Have few queries on Auto SGA(ASMM). Considering that we have set ASMM for our instance.

> When more memory requered in a particular pool which pool Oracle does choose to get the memory from?
> Is there any priorities associated in pulling out memory from pools?
> Why should we set the minimum values knowing that Oracle can adjust them as and when required?

Please help me in understanding the concepts.
Tom Kyte
September 05, 2007 - 5:43 pm UTC

which pool? whichever one we want, it is automatic, it decides and its decisions will vary from situation to situation, release to release.

you would set minimums because you want to, if you know the best 'starting' point, it is a way for you to tell us.

how much SGA is too much

David, September 06, 2007 - 4:21 pm UTC

Tom,

I have over a dozen production databases (10.2.0.3) on 64-bit RedHat Linux AS 4.0 with 16 GB RAM. With Automatic Memeory Management, I would like to use as much as 14 GB for SGA_TARGET, based on the rule of "the more memory the better." However, I also heard people saying that more SGA could have negative performance impact. I am kind of puzzeled. Could you please share your thought in this regard?

Thanks,
David
Tom Kyte
September 11, 2007 - 8:30 am UTC

sure, you use 14gb for the SGA leaving about 1 to 1.5 gb for the PGA.

if you need lots more PGA memory (process memory for hashing and sorting), you have just done the wrong thing.

It is impossible to give you a single answer - I can tell you 14gb is correct and give you a 'for example', I can tell you 14gb is very wrong (and give you a for example), I can tell you it matters not whether you use 14gb or some other value and .....


Buffer Exterminate

Deepak, September 11, 2007 - 11:16 am UTC

Hi Tom,

In our database (10g/Solaris 9) the major wait event is "Buffer Exterminate". Ours is an OLTP database. We have set Auto SGA. As per metalink this wait event happens when Oracle tries to pull memory out of some pool and allocates it to something else. Even after increasing SGA_TARGET we are unable to get rid of it.

Can you please describe how to reduce this wait event? Should we go for manual SGA?


Tom Kyte
September 15, 2007 - 3:40 pm UTC

one will always have a major wait event. Always. No matter what.


Now, is your major wait event actually causing a performance related problem - have you determined that your applications are suffering from this.

I ask, because I have had more than one time where people say "always we see X and Y as our top two wait events, how can we fix that", only to discover they were waiting for like 20 seconds in a 15 minute window across dozens of users (eg: so what).



Basically, if you are seeing this a lot and it does account for a HUGE amount of time, it means your SGA is resizing frequently. My first thought is "you have a hard parse problem" (it always comes back to binding). So, do you?

possible negative performance impact with big SGA

olivier bernhard, September 11, 2007 - 4:07 pm UTC

Hello David i guess people saying that you could run into performance issues using very big SGA are refering to possible Latch bottleneck that may/could occure in case it takes a long time to scan a chain of buffers in the database buffer cache. Since the number of latches is limited then the bigger SGA you have the longer the chains of buffers you get, the longer it takes to scan it, the longer your process is holding the dedicated latch. but this is just theory. I think what you have to do is dedicating the amount of memory you think you need, and then monitor your system. At the same time you should not forget that your disk probably cost much more than your memory (so why not take advantage of them ...) and that doing everything in memory does not mean your system goes faster. regards, Olivier

re: Buffer Exterminate

jerry ma, September 13, 2007 - 4:37 am UTC

"more memroy more better" is not always true.
to manage so huge memory will add additonal management overhead to db system. so then , more potetial latch event occur.

maybe, as far as huge memory, memeroy database such as TimesTen will be more fit.

v$sga_dynamic_components says 1.2GB?

A reader, October 01, 2007 - 2:38 pm UTC

Tom I remember an old post of yours (which I couldn't find)  where you described a problem with a shared pool that was too big.  Every night, staff was paid to flush the pool at 3am.  You had them drop the pool to 70MB and everything was fine.  I have another situation where the pool seems awfully large to me, 1.2GB.  I questioned it and was told they had gotten this from  v$sga_dynamic_components.   This doesn't "magically" tell you what the pool should be does it? Doesn't it base it on other settings such as sga_target?  
This is considered a 10G recommendation - 
SQL> select component, current_size, min_size from  v$sga_dynamic_components;

COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
  MIN_SIZE
----------
shared pool                                                        1342177280
1342177280

Tom Kyte
October 03, 2007 - 4:30 pm UTC

that is showing what size it is now.

if they just set sga target, oracle decided to make it that size.

if they set the shared_pool_size, it is what they told us to make it.

A reader

Reader, October 05, 2007 - 2:47 pm UTC

Recently I was given the responsibility to manage one 10g database which someone else configured. It is running in manual memory management mode so shared pool, java pool, large pool is set manually. DB_CACHE_SIZE is set to 0.
Shared pool is 12% of sga, large pool is set to .3% and java pool is set to .6%. Rest 86% shows up as buffer cache.
What is this part of sga and what is it used for? What would be oracle using for db_cache_size since it is set to 0?


Thank you for all the wonderful tips that you share on this website.

Sizing of SGA

azzzz, February 14, 2008 - 5:28 pm UTC

Hi Tom,

We are runnning oracle 10.1.0.5 on windows 2000 sp4.
1). When I query sql> sho parameters sga; I got SGA_MAX_SIZE as 1784 M and SGA_TARGET = 0
but when I use
select round(sum(bytes)/1024/1024,2) total_sga,
round(sum(decode(name,'free memory',bytes,0))/1024/1024,2) free,round((sum(decode(name,'free memory',bytes,0))/1024/1024)/(sum(bytes)/1024/1024)*100,2) free_per from v$sgastat

it shows the TOTAL_SGA as 1814.37M

Why is this difference.

2). Is it not required to set SGA_TARGET. Is it enough to set SGA_MAX_SIZE

3). When I use select * from v$sgastat it shows:
SHARED_POOL_SIZE of 111 MB free
LARGE_POOL_SIZE of 117 MB free
JAVA_POOL_SIZE of 124 MB free.
If ORACLE says it will dynamically allot the memory, why is it not allocating the free memory to BUFFER_CACHE which needs some memory.

Can you please tell me how can I change the BUFFER_CACHE size dynamically with out shutting down the database.

Thanks in advance.

Tom Kyte
February 15, 2008 - 7:55 am UTC

1) because you disabled automatic tuning of the SGA (sga_target=0), the other parameters (shared pool size, java pool size, etc) are used to size the sga.

the sga is also allocated in granules and all things are rounded UP to be a multiple of the granule size - which is 4, 8, or 16mb depending on platform and other considerations.


2) read the documentation on these parameters, you have disabled automatic memory tuning by setting the sga_target to 0

Sizing of SGA

azzzz, February 15, 2008 - 10:20 am UTC

Hi Tom,

Do you mean that the parameters were set manually. If so when I do 
SQL> sho sga

Total System Global Area 1870659584 bytes
Fixed Size                   789220 bytes
Variable Size             632681756 bytes
Database Buffers         1228800000 bytes
Redo Buffers                8388608 bytes

and when I do

SQL> sho parameter db_

db_block_buffers     integer     150000
db_cache_size        big integer 0

If it is manually set then why it is showing db_cache_size as 0

If my understanding is right is it allocating the various pool sizes from the parameter sga_max_size which is set to 
1784 MB for the first time when databases is started and since the ASSM is set to 0, it is not dynamically resizing according to the requirements.

Thanks

Tom Kyte
February 17, 2008 - 6:59 am UTC

(did you think about reading the reference guide about parameters? they are documented.... just a thought)

You set db_block_buffers (the old fashioned way to do it)
You did not set db-cache_size (the way to do it in current releases)

therefore, the cache is being set by taking db_block_buffers * block_size and using that as the basis for the buffer cache size.


You are using 100% manual memory management. Your understanding should be "we are not allocating the various pool sizes - YOU ARE"


ASSM - that is automatic segment space management, a replacement for freelist and freelist groups - nothing to do with memory (SGA) allocations.


Goto your init.ora or your spfile. See what you are setting, that is what is being used (after rounding off to granule sizes - 4/8/16mb depending on various factors)

Overview of the parameters in the init.ora file

Manoj thakur, March 24, 2008 - 6:23 am UTC

Suppose my database size is 6.5 GB,Hard Disk size=144 GB,Ram Size=4 GB.I have set some parameters in the init.ora file.Could you please check and tell whether the following parameters are correct in the init.ora
SGA_MAX_SIZE:= 1536 MB
Could you please tell me whether the following configuration is correct
shared_pool_size=400 MB
shared_pool_reserved_size=40 MB
pga_aggregate_target=200 MB
sort_area_size=50 Mb
sort_area_retained_size=5 MB
db_block_size=8192
db_cache_size=500 Mb
SGA_max_size=1536 MB
sessions=1000
open_cursors=500
processess=800
Can you please check and tell whether the following parameters are correct
Tom Kyte
March 24, 2008 - 11:25 am UTC

if the database starts up, they are all correct - as in syntactically correct.

since you have such a tiny database and machine, you probably just want 'auto everything', so just set the sga_size and pga_aggregrate target (I'll presume windows :( - and 32bit at that - make sure they two of those are well under 1.8 gb when summed)

lose the shared pool settings

lose the sort area stuff - unless you are using shared server in 9i - which I doubt

lose the db_cache_size

let everything else default unless you have good reason to set it otherwise (eg: I seriously doubt this machine will get anywhere NEAR 800 concurrent sessions)

Way of setting SGA in 10G

Dhairyasheel Ttawde, March 31, 2008 - 2:49 am UTC

Hi,
Here's what I do to size up the sga.

when the system is first installed with apps loaded et all.
We set the sga to approx 600mb. i.e. setting sga_target & sga_max_size to the same value. We run the apps a few times.
Then we scale up the two parameters sga_max_size and sga_target to say 900M.
When we do this Oracle automatically scales up the buffer cache, shared_pool allotments according to the previous load that the apps generated.
When I say scales up, I mean that it may decrease the value
of shared_pool and increase buffer_cache if it feels so.

I believe here there is a connection between the AWR & the advisors running in DB.

Also as a rule of thumb, re-sizing sga or other pools is the last resort for us. Usually dramatic improvements are seen by tuning the app code.

Am I right Tom?

Tom Kyte
March 31, 2008 - 9:33 am UTC

absolutely wrong.


Except for: "Usually dramatic improvements are seen by tuning the app code."


if you have a linux server with 512mb of ram and you set the sga to 600mb, you have done wrong.


The size of the SGA and PGA has to be a function of the amount of real memory installed - in addition to other things.

Additional Technical info on previous comment.

Dhairyasheel Ttawde, April 01, 2008 - 12:32 am UTC

Hi Tom,
Sorry, forgot to mention that the 600m SGA setting was a default we used to set since our production boxes were minimum 4GB RAM & the 600m value was derived by our previous experiences with tuning our apps.

In One particular instance, We were running the server with 4gb RAM and 600m SGA. As a last resort we increased the SGA size to 1GB by setting the sga_target & sga_max_size to the same values. On restarting the instance we found that Oracle increased the value of buffer_cache (compared to previous value) & decreased the value of shared_pool (compared to previous value).

also the parameters like
db_cache_size, shared_pool_size were set to 0 (before and after the sga scale up)to enable automatic memory tuning.

The decrease of shared_pool_size was astounding as we had never seen this behaviour in 8i or 9i.

So my understanding is that when the SGA size was scaled up, Oracle referred the AWR statistics to resize the pools accordingly.

Is that whats happening here?


Tom Kyte
April 01, 2008 - 9:23 am UTC

If I had 4gb of ram, 600mb would not have been my starting point, probably closer to 2gb at least.

and since sga memory management wasn't available in 8i or 9i, it should not have been "astounding", it could never have happened in the past.


and if the shared pool needed to be made larger, it would have been - over time, that is the "auto" bit....

What forms the basis for Auto Tuning

Dhairyasheel Ttawde, April 01, 2008 - 9:52 am UTC

HI Tom,
Thanks for the answer.
The 600m sga setting for a 4gb RAM server, is something born out of practice of running/tuning the apps to their limit without looking at oracle init parameters.

Also I can understand the auto tuning of the memory pools,
but what I want to know is what data inside oracle forms the basis from which Oracle derives the intelligence of howmuch to scale up or scale down the pools.

From my little understanding of 10G I believe its the workload repository & ASH. is it so?

p.s - Any plans on giving seminar's in India ? from the kind of fan following you have here, you will probably have to book an entire stadium.

Regards.
Tom Kyte
April 01, 2008 - 10:09 am UTC

... but what I want to know is what data inside oracle forms the basis from which ...

it is the information captured into ash and awr and other metrics, yes.


Different SGA_TARGET betweem RAC nodes

A Reader, May 02, 2008 - 12:46 am UTC

Hi Tom,

Is it possible to have different sizes for SGA_TARGET between RAC nodes?

node-1 has 32G as physical memory and I want to set SGA_TARGET=20G
node-2 has 60G as physical memory and I want to set SGA_TARGET=40G
node-3 has 80G of physical memory and I want to set SGA_TARGET=50G

How can I set this? Can I set this by bouncing one instance at a time instead of re-starting entire RAC-database?

This is for 10gRel2 on Solaris10. At this SGA_TARGET & SGA_MAX_SIZE is set to 15G with ASMM enabled.

Thank you.

Tom Kyte
May 02, 2008 - 7:14 am UTC

yes it is

yes you can

the question is "should I" - it is not really a good idea to have imbalances like this - I'd think about spreading the ram out so that each has about 60gb.

"how" depends on "how" you are setting parameters, hopefully with a stored parameter file.

see the alter system command, the "sid=" bit - you can set individual parameters that way.

Different SGA_TARGET betweem RAC nodes

A Reader, May 02, 2008 - 11:10 am UTC

Hi Tom,

Thanks for you reply. Appreciate it!

The reason why we are planning to have imbalance in sga_target it that we have 5 nodes cluster and out of which 2 nodes are powerful compare to other 3.

So we do load balancing via services we created and using resource manager where we do heavy lifting on 2 powerful nodes versus quick queries and transactions on other 3.

ADDM reports are always recommending on increasing sga_target on those 2 nodes where we do heavy lifting (bulk processing).

Now 3 nodes are maxed out with 32G of physical memory so we cannot add more memory, but we would like to make use other 2 nodes where we 60G and 80G of memory resp.

Yes, we use SPFILE and we can use "SID=" to set individual sga_target at instance level. But what about SGA_MAX_SIZE parameter? Can we set that at SID level as well or is it set globally (same for all instances in cluster)?

Last question: After making change to SGA_TARGET parameter (and may be SGA_MAX_SIZE) in SPFILE for each SID can we re-start one instance at a time or we have to re-start Database (all instances at once)?
We would like to do this without downtime if possible.

I'm asking lot of questions here as I couldn't find clear answer on having imbalancing in terms of SGA between RAC nodes in any oracle docs.

Appreciate your help and thanks for taking some time out from your busy schedule.


Tom Kyte
May 02, 2008 - 12:27 pm UTC

the sga's are independent of each other and separately configurable.

to see if a parameter would have to be set the same on all instances, the reference guide comes in handy

compatible:
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams028.htm#i1125496

documented as "all instances must be same"

sga_target:
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams193.htm#I1010256

no such restriction

to change the SGA_TARGET may or may not require a restart of an instance, depends on the current max size.

but if you do have to restart, you can do it instance by instance.

Different SGA_TARGET betweem RAC nodes

A Reader, May 02, 2008 - 2:59 pm UTC

Hi Tom,

Thank you for your quick and prompt reply.

We've set SGA_MAX_SIZE to 15G for all instances.

So, we will change sga_max_size at instance level and bounce one instance at a time.
Then we will be able to change sga_target dynamically.

Thanks again!

Tom Kyte
May 02, 2008 - 3:14 pm UTC

well, you should set the max and the target together first, no reason to dynamically resize if you are going to have an outage anyway.

how to solve ORA:00093

mukesh, August 06, 2008 - 8:44 am UTC

hi tom
i am fetching problem that _shared_pool_reserved_min_alloc must be between 4000 and 0 error can u plz suggest me all parameter size in init.ora
i will give u my database configuration
Database size = 11GB
RAM = 6GB
waiting for u r reply ... reply as early as possible .....
* db_block_buffers,
* db_block_size,
* db_cache_size,
* db_keep_cache_size,
* db_recycle_cache_size,
* java_pool_size.
* large_pool_size,
* log_buffer,
* shared_pool_size,
* streams_pool_size,
Tom Kyte
August 06, 2008 - 9:06 am UTC

"U" is not available.
"PLZ" - German postal codes? I do not understand.
"R" - not sure what R is either.

Would you use words in the future, we are not talking over our mobile phones.

Why are you setting an underscore parameter.

And you have given us no configuration information whatsoever, you've listed some parameters - no values and definitely not the value in question!

oracle memory parameters

Thakur Manoj, February 19, 2009 - 10:05 am UTC

Dear Sir

I have an unpatched oracle version 9.2.0.1.0.I have default memory parameters being set in Oracle because of which I am facing performance issue in the application.I have 8 GB Ram size ,OS is Windows 2003 Server R2 Enterprise Edition.The application is made in VB.net and ASP.net through which we are accessing the application through web.In my application there are lot of tables which are getting inserted,updated and deleted through the procedure.The application is a risk scorer application.Whenever the borrower comes to the bank for the loan every borrower is assigned the rating as per basel-11 awareness.Now My memory is 8GB Ram .It is a dedicated server mechanism and not shared server mechanism.

I am setting Oracle parameters for better performance as
SGA_Max_size=3.5 GB(3670016 Bytes)
db_cache_size=1 GB
shared_pool_size=1 GB
pga_aggregrate_target=1.5 Gb
log_buffer=20 MB(20971520 Bytes)

Do you think the parameters that I have set is correct ?
What could be my Java Pool Parameters and Large pool parameters since there in no Java being used in the application?

Appreciate your urgent reply on this sir.

Tom Kyte
February 19, 2009 - 1:40 pm UTC


if you are 64bit, that looks ok.

Use the advisors that report their output in a statspack report to fine tune.

Take a snapshot during your peak workload
Wait 15 minutes
Take another snapshot
Wait 15 minutes
repeat a couple of times

Generate the reports and see what advice there is for sizing your shared pool and buffer cache.

oracle memory parameters

Thakur Manoj, February 19, 2009 - 10:10 am UTC

Dear sir

Also would like to tell you that there is a huge data in Lakhs coming through the Interface getting inserted in main tabels,interface tables and audit tables when we click on the save button through the procedure being called after EOD.
Well let me also tell you that this is a Bank's data after proposal getting sanctioned for the Loan purpose.
So Request you to please help me in this above issue.Please suggest me the correct parameters for the above issue.Well let me also tell you that this is not the OLTP Environment.

Appreciate your help on the below.

Regards

oracle memory parameters

Thakur Manoj, February 20, 2009 - 12:42 am UTC

Dear Sir

Thanks for the reply
As you said if it is a 64 bit the memory parameters looks ok.but let me tell yo u that this is a 32 bit environment.

Appreciate your urgent reply on this sir

Regards

Tom Kyte
February 21, 2009 - 8:49 pm UTC

...
I am setting Oracle parameters for better performance as
SGA_Max_size=3.5 GB(3670016 Bytes)
db_cache_size=1 GB
shared_pool_size=1 GB
pga_aggregrate_target=1.5 Gb
log_buffer=20 MB(20971520 Bytes)
.......

I disagree with you, you would not have 2gb and a max size of 3.5gb for the sga (plus 1.5 gb of pga which takes away from the max sga size on windows 32bit)

are you sure

a) you have SET these parameters
b) you are 32bit


if you set some 'magic' parameters, you can get a little bit more sga, but it is going to be about 1.8gb out of the box.

32bit is so lame, your memory settings are far too large for 32bit.

Oracle Parameters

Thakur Manoj, February 24, 2009 - 4:20 am UTC

Dear Sir

What if I set

SGA_MAX_SIZE=3.2 GB
db_cache_size=1.2 GB
shared_pool_size=1.2GB
pga_aggregrate_target=800 MB
log_buffer=5 M
optimizer_index_caching = 10
optimizer_index_cost_adj =30
_always_semi_join = off
_always_anti_join = off
PROCESSESS = 800
OPEN_CURSORS = 2000
DB_FILE_MULTIBLOCK_READ_COUNT = 16

I think these would be the parameters that would improve my performance for 32 bit.

Appreciate your urgent reply on the same.

Tom Kyte
February 24, 2009 - 6:05 am UTC

laughing out loud

... I think these would be the parameters that would improve my performance for 32
bit.
....

one question for you: why?


that is urgent, reply soon.


we've already discussed how on 32 bit windows, in most cases you are limited to a process size of 1.7/1.8gb, you have blown that out of the water, that instance will likely not start.

Oracle Parameters

Thakur Manoj, February 25, 2009 - 12:47 am UTC

Dear Sir

Then what could be the possible SGA_MAX_SIZE for 8 GB Ram,db_cache_size,shared_pool_size,and pga_aggregrate_target for better performance of our application on 32 bit windows platform.Since It is been told that 20 % of the Ram goes to Windows 2003 server Enterprise edition.

Can you suggest me suitable parameters for better performances

Appreciate your urgent reply on the same
Tom Kyte
February 25, 2009 - 6:35 am UTC

what is up with the "urgent", urgent means "emergency, life or death". This is not urgent - my reply certainly won't be urgent.

You have 32bits, you are probably limited to about 1.7/1.8gb of ram. A single process in 32bits cannot really address more than that in windows. You could put 100gb of ram in that machine - it would change nothing.

So, unless you have set special parameters in the booting of the machine (the /3gb switch for example), you are limited to 1.8ish GB of memory for the SGA and PGA. If you have the /3gb stuff - maybe 3gb.

So, understand what you are able to address (your job, you learn this - ask your system administrator, your windows expert on site) and then set the memory parameters to be LESS than that maximum imposed by the 32bit-ness you are living with.

http://www.mydatabasesupport.com/forums/oracle-server/174755-2gb-limit-memory-oracle-windows-32-bit.html

Oracle Parameters

Thakur Manoj, February 25, 2009 - 12:50 am UTC

Dear Sir

What could be the possible parameters for 8GB Ram on Windows 2003 Server Enterprise Edition.What could be the SGA_MAX_SIZE,SHARED_POOL_SIZE,JAVA_POOL_SIZE,DB_CACHE_SIZE,PGA_AGGREGRATE_TARGET FOR 32 BIT WINDOWS PLATFORM

THIS IS A DEDICATED SERVER ENVIRONMENT FOR BETTER PERFORMANCE.pLEASE SUGGEST WHAT COULD BE MY SGA_MAX_SIZE,SHARED_POOL_SIZE,DB_CACHE_SIZE,PGA_AGGREGRATE_TARGET FOR BETTER PERFORMANCE

Appreciate your urgent reply on the same?

Regards

SIZE of SHARED POOL memory in SGA.

Rajeshwaran, Jeyabal, July 17, 2009 - 9:36 am UTC

Tom,

I have a doubt regarding SHARED POOL Memory in SGA.
The information stored in ORACLE SGA Memory is
(i) Database Buffer
(ii) Redo Log Buffer
(iii) Shared Pool memory
   
rajesh@IRADSDB> SELECT NVL(NAME,'TOTAL_SIZE') AS NAME,sum(ROUND(((VALUE/1024)/1024),2)) AS SIZE_MB
  2  FROM V$SGA
  3  GROUP BY ROLLUP(NAME)
  4  /

NAME                              SIZE_MB
------------------------------ ----------
Database Buffers                      420
Fixed Size                           1.19
Redo Buffers                          6.8
Variable Size                         156
TOTAL_SIZE                         583.99

From the abouve Query
a) Total SGA size is 583.99 MB
b) Database Buffer Size in SGA is 420 MB
c) Redo Buffer size in SGA is 6.8 MB

Please correct me if i am wrong.

Questions
1) What does the VARIABLE SIZE & FIXED SIZE Values refers?
2) How to find the Size of SHARED POOL memory?

Thanks,
Rajesh.
Tom Kyte
July 24, 2009 - 8:32 am UTC

<quote src=Expert Oracle Database Architecture>

Fixed SGA
The fixed SGA is a component of the SGA that varies in size from platform to platform and release to release. It is "compiled" into the Oracle binary itself at installation time (hence the name "fixed"). The fixed SGA contains a set of variables that point to the other components of the SGA, and variables that contain the values of various parameters. The size of the fixed SGA is something over which we have no control, and it is generally very small. Think of this area as a `bootstrap¿ section of the SGA, something Oracle uses internally to find the other bits and pieces of the SGA.

</quote>



the variable part is everything else that isn't the buffer cache, the fixed size, the redo buffers - it includes things like the shared pool, large pool, java pool, etc.

ops$tkyte%ORA10GR2> select pool, sum(bytes) from v$sgastat where pool is not null group by pool order by pool;

POOL         SUM(BYTES)
------------ ----------
java pool      16777216
large pool     16777216
shared pool   268440148



sga_Target vs sga_max_size

A reader, November 02, 2009 - 1:16 am UTC

hi tom,

i have read the meaning of sga_Target and sga_max_size at oracle definietion.

i also understand that altering sga_Target does not require a db bounce but alter sga_max_size will require db bounce.

but is that the only reason to leave a buffer between sga_target and sga_max_size ? so that there can be growth ?

q1) is any there other reasons why to set
sga_Target < sga_max_size ? why cant we just give all we have in sga_max_size to sga_target ?

if sga_max_size is preallocated memory, then wouldnt we be wasting that amount of memory ?

e.g sga_max_size = 1000mb (pre-allocated ?)
sga_Target = 800mb

hope to hear your advice soon
rgds,
Noob












Tom Kyte
November 09, 2009 - 12:11 pm UTC

... but is that the only reason to leave a buffer between sga_target and
sga_max_size ? so that there can be growth ?
...

yes.


q1) because you might need the memory for something else - like your pga - during the day and at night you want to configure differently (eg: during the day, bigger pga, smaller sga - at night - smaller pga bigger sga)


it is reserved, but not touched memory, the OS will not have it physically in ram if you are not using it.



Shared Pool Percent of SGA Greater then Buffer Cache

Jim Cox, November 12, 2009 - 2:07 pm UTC

Hi Tom

I am using ASSM in 10G and just increaed my SGA from 4GB to 8GB and let Oracle set the other pools and buffer cache. I noticed that the Shared Pool is using 57% of SGA and Buffer Cache is using 40%. I know Oracle will adjust the pools on what it decides is best, so do I need to worry about the Shared Pool getting so big ?

Thanks
Jim
Tom Kyte
November 15, 2009 - 2:18 pm UTC

sounds like you have a bind variable problem there - do you?

Possible Bind Variable Issue

Jim Cox, November 19, 2009 - 5:16 pm UTC

Hi Tom

May be, I have no control over the code as it is supplied by the vendor. Any advice on how to check ? Same code was running in Oracle 9i, but I was setting my own pools. You had written something at the time about shared_pool and that it should not be very high because that would be an indicator that bind variables were probably not being used and that setting the shared_pool higher would be masking the problem. Forgot the exact article.

Thanks
Jim

Thanks For The Info

Jim, December 02, 2009 - 4:19 pm UTC

Just a thanks Tom

I will take a look see

Script Output Generated

Jim, December 03, 2009 - 10:45 am UTC

Hi Tom,

ran the script you pointed me to and it returned 28 sql statements with a range of counts from 102-787. Doesn't seem that good to me. I wanted to ask if there was some particular reason you wrote the script with having > 100 ?
Was that just arbitrary ?

I guess my only alternative is to submit the sql list to the vendor and see waht they say.

Thanks Again
Tom Kyte
December 04, 2009 - 4:05 pm UTC

100 was arbitrary, a good place to start. fix those and then lower 100.

... I guess my only alternative is to submit the sql list to the vendor and see
waht they say. ..

or figure out where in the application this sql is generated and then go to that part of the application and try to sql inject it - to break it. Then you can go to the vendor and say "if you do not use binds, right now, for everything that should - you have a security bug, a big one - and here is my example"


SGA_MAX_SIZE in 9i

Arindam, January 20, 2010 - 3:28 am UTC

Hi Tom,

I am facing a problem while reducing the SGA MAX size. Here are the steps I am using:



1) show sga

Total System Global Area 590317328 bytes ----- i.e. sga_max_size is 562 MB

Fixed Size 730896 bytes

Variable Size 117440512 bytes

Database Buffers 471859200 bytes

Redo Buffers 286720 bytes

2) alter system set sga_max_size=350m scope=spfile;

3) shutdown

4) startup

Then the sga_max_size is only showing the same with fixed size reduced only few bytes. But when I create pfile from spfile, it is showing me sga_max_size= 367001600. i.e. 350m itself. I cannot figure out where the problem is.

Can you please suggest me a way out please?

Tom Kyte
January 20, 2010 - 11:30 am UTC

first, you do realize that 350mb is 350*1024*1024 bytes right?

ops$tkyte%ORA10GR2> select 350 * 1024 * 1024 from dual;

350*1024*1024
-------------
    367001600


Ok, so 367001600 *is* 350mb. They are the same.


So, that is OK.

As for the fixed size, that is not going to change very much as you resize the SGA, the fixed size is just that - the fixed size of the SGA.

Why do you think the fixed size would change much, if at all??? You don't get to pick how big that is, we do.

sga

A reader, January 20, 2010 - 7:57 pm UTC


SGA_MAX_SIZE in ORACLE 9i

Arindam, January 20, 2010 - 11:54 pm UTC

Hi Tom,

This is ok. I am not much bothered about the Fixed size. What I am looking is that, how I can reduce the sga_max_size. It was previously 562 MB. I am trying to set 350 MB. I was following the steps below:

1) alter system set sga_max_size=350m scope=spfile;
2) shutdown
3) startup

But the sga_max_size remains the same as it was earlier (562 MB).
Tom Kyte
January 21, 2010 - 8:30 am UTC

prove it to me,

show parameter sga_max
alter system....
shutdown
startup
show parameter sga_max

and show us all of your memory related parameters.

The docs say...

joel garry, January 21, 2010 - 4:07 pm UTC

"If SGA_MAX_SIZE specified in the initialization parameter file is less than the sum of all components specified or defaulted at initialization time, then the setting in the initialization parameter file is ignored."
Tom Kyte
January 25, 2010 - 1:40 am UTC

correct, if they are specify the other values, which is what we are asking to see :)

SGA_MAX_SIZE in Oracle 9i

Arindam, January 22, 2010 - 1:37 am UTC

Here are the steps again:

SQL> sho sga

Total System Global Area  556762824 bytes
Fixed Size                   730824 bytes
Variable Size              83886080 bytes
Database Buffers          471859200 bytes
Redo Buffers                 286720 bytes
SQL>  sho parameter sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 556762824
SQL> !bc
556762824/1024/1024
530
quit

SQL> alter system set sga_max_size=480m scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  556763088 bytes
Fixed Size                   731088 bytes
Variable Size              83886080 bytes
Database Buffers          471859200 bytes
Redo Buffers                 286720 bytes
Database mounted.
Database opened.
SQL> sho parameter sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 556763088
SQL> !bc
556763088/1024/1024
530


So no reduction at all in sga_max_size. 
But in SPFILE the parameter *.sga_max_size=503316480 i.e. 480MB. Can't understand why it is not showing in the SQL prompt. 
 

Tom Kyte
January 25, 2010 - 1:43 am UTC

you are not giving us what we ASKED for


... and show us all of your memory related parameters. ...

i will suspect you have set other memory related parameters

and their settings make this max size IMPOSSIBLE.

Please look in the docs.

joel garry, January 22, 2010 - 3:24 pm UTC

If you look in your versions concepts guide under "Dynamic SGA" of the Memory Architecture chapter, you will understand what is happening.

In a nutshell, there are several components of the SGA that are added up to get to the SGA size: db_cache_size, log_buffer, shared_pool_size and large_pool_size. Each of these consists of granules of memory, so Oracle takes what they are set at and rounds up to the next granule - 8 or 16MB depending on platform, for SGA over 128MB.

So, figure out what each of those components are set to, round up the granules, add them up and compare to the size of the SGA.

It ought to be obvious by now your buffer cache alone is a big chunk of the SGA, bigger than what you are trying to set the SGA to.

SGA_TARGET

Daniel Vasanthan, March 10, 2010 - 8:34 am UTC

HI Tom,
I have read many of your articles and found useful. Even I have your book at home which is ultimate. But I somewhat feel that this thread is going nowhere. People in the thread are requesting you for guidelines for SGA_TARGET in dedicated environment for a specific RAM size, but somewhat your answers are going out of that. Also how to check the peek in to __shared_pool values ( as mentioned in your answers). I really don't know and wish to know.
Tom Kyte
March 10, 2010 - 9:03 am UTC

.. but somewhat your answers are
going out of that. ...

I don't know what you mean.



create pfile from spfile
edit pfile that you created

probably easiest way to see the __ stuff.

shared memory attached to oracle

A reader, April 14, 2010 - 7:01 am UTC

Hi Tom,
         You said above "it is reserved, but not touched memory, the OS will not have it physically in ram if you are not using it. " 
We have the following system:
OS: HP-UX edcddj0 B.11.31 U ia64 2803668160 unlimited-user license
Database: 11.2.0.1

SQL> show sga

Total System Global Area 2087780352 bytes
Fixed Size                  2171384 bytes
Variable Size             671092232 bytes
Database Buffers         1409286144 bytes
Redo Buffers                5230592 bytes
SQL> show parameter sga

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
lock_sga                             boolean                           FALSE
pre_page_sga                         boolean                           FALSE
sga_max_size                         big integer                       2000M
sga_target                           big integer                       1808M


 ipcs -ma | grep oraeeg
m  348061714 0xcd1733ec --rw-rw----    oraeeg    dbaeeg    oraeeg    dbaeeg     28 2113937408 26961 15551  7:54:44  7:55:10  7:43:01

I see that the shared memory attached is 2113937408. That means that SGA_MAX_SIZE gets attached. Does this still means the only 1808MB will be in RAM and not the whole 2GB?

Tom Kyte
April 14, 2010 - 9:11 am UTC

we only touch that which we need and the OS only pages in that which is touched and needed.

so, 2gb reserved, not all of it need be in physical memory.

shared memory attached to oracle

Avnish, April 15, 2010 - 12:20 am UTC

Hi Tom,
Thanks for your prompt reply as always. Your replies are always enlightening.


Sga -allocation

A reader, April 23, 2010 - 5:58 am UTC


"we only touch that which we need and the OS only pages in that which is touched and needed.

so, 2gb reserved, not all of it need be in physical memory."

Can you please explain in detail about this ? I am getting confused with the words 'reserved' , 'allocated' ,'touched' and ,'needed' - if I set sga_max_size=2gb and sga_target_size=1.5 gb at the instance start-up - how the memory is allocated actually when the instance is started for sga and how the memory is allocated on going in future for sga? Many thanks for your valuable time
Tom Kyte
April 23, 2010 - 9:16 am UTC

we'll allocate 2gb, but only touch up to 1.5 gb of it. The OS will do what it feels like doing as far as giving us memory - the OS of today are typically what you might call 'demand paged' - as you demand the memory - they give you the physical memory, paging in from disk if needed.


Typically, with your settings, you would expect to see 1.5gb of real memory in use and 2gb of memory allocated to the process, with 0.5gb of memory probably sitting in a page file (not really sitting there, waiting to sit there since you haven't touched it yet - it doesn't have a value, they don't have to really write anything to disk yet)

You can allocate 2gb of memory on a system with 1gb of physical ram, it'll work typically just fine - a tad slow when you try to use all of it - fine if you just used a small bit of it. Same concept applies here, until you actually touch the memory, the OS doesn't have to make it available for you.

sga / pga

A reader, April 23, 2010 - 2:15 pm UTC

Thanks for your reply ,Please correct my understaning i it is wrong -

1.SGA _TARGET memory is 'pre-allocated' and SGA_MAX_SZE is 'reserved' at the instance start-up in one (or) multiple SHARED SEGMENTs. We can touch (use) the SGA memory UP TO sga_target and as we go on demanding for sga memory ,the OS will give us.if we dont use some of the sga_target memory , it can use for other application/process running on the box , When we tocuh/need/ask , the OS will get it for us ..

2.But for a server process the PGA memory is not 'pre-allocated' , it is created when server process is created and will grow DYNAMICALLY using malloc()/pmap() calls.
Tom Kyte
April 23, 2010 - 2:42 pm UTC

1) it is all "pre allocated", if you set sga max size to 50gb and ask for a report of allocated memory, it'll show us with 50gb.

We'll only use and hence the operating system will only map to real ram - sga_target bytes of it.

If we shrink the sga_target, we'll stop using some of it and the OS would page that out to disk after a while.


2) correct

SGA Sizing

Zain, April 25, 2010 - 11:35 am UTC

Dear All,

The basic fundamental behind sizing your SGA(System Global Area) is nothing comes out on fly, everyone has to have a practical approach rather then getting things easily and readily available. One cant become a proper DBA just by getting answers on the fly and fix it. So practical approach is very much important.

HTH ;)

understand the *concepts*

bix, April 27, 2010 - 11:18 am UTC

but before that - every one must have 'SOLID' understanding of the ORACLE concepts,

1.Learn and have solid understanding of the basics and the concepts of ORACLE in theory
2.then start working on Oracle products - you will get practical experience
3.use 1 & 2 together to for effective design or to fix the issues.



SGA Max an SGA Target

Jim, December 06, 2010 - 11:11 am UTC

Hi Tom

I am using Oracle 10g 64-bit - Windows 2003 64-bit, and setting both SGA Max and SGA Target to 11GB. I also have open_cursors and session_cached_cursors set to 1000 each. My question is that I am monitoring the database via OEM and I am seeing my shared_pool at times go into the 4GB range. I have no control over the code so I can only tweak the database parameters. Since Oracle is adjusting my buffer cache (6GB) and shared pool (4GB), should I be concerned about this high shared_pool value ? I know I have read some articles in which I believe you have said that having a high shared_pool value is not good as it indicates hard parsing and lack of use of bind variables.

Thanks
Tom Kyte
December 07, 2010 - 9:49 am UTC

... open_cursors and session_cached_cursors set
to 1000 each. ...

your application really needs 1,000 cursor?? I seriously doubt it. Let me guess:

o you are running a connection pool
o you have set the connections to cycle themselves every N uses
o you set open cursors to 1000 to 'fix' an ora-100 the applications would get every now and then

In other words, you are trying to mask a massive bug in the client application - they are leaking cursors like a sieve and rather than fix the code, you've made everyone less efficient by hiding the problem and setting parameters really, almost absurdly, high.


... I know I have read some articles in which I believe
you have said that having a high shared_pool value is not good as it indicates
hard parsing and lack of use of bind variables. ...

it could be a cause of that - or if your application really truly has 1000 open cursor - it could just be that your system has an abnormally large amount of sql.

I rather think the former however :) What is your soft parse percent?

Oracle Advisors

Kev Burgess, January 25, 2011 - 7:25 am UTC

Hi Tom,

How reliable are the Oracle advisors for estimating SGA and BUFFER_CACHE sizes? I have been using SGA_TARGET_ADVICE and DB_CACHE_ADVICE for sizing my 10gR2 databases but there seems to be very little mention of them here.

Many thanks.
Tom Kyte
February 01, 2011 - 12:02 pm UTC

They are very good - and when I do mention them it is simply to say "use them"

They work by using a little bit of extra memory to 'watch' what you have been doing and realistically report drops (or increases) in physical IO's you can expect to see if you were to follow the advice.

Follow-up on Soft Parse

Jim Cox, February 25, 2011 - 2:34 pm UTC

Hi Tom

I took a look at two snaps of 3-hour interval for this month:

Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 17211 02-Feb-11 08:00:39
End Snap: 17214 02-Feb-11 11:00:22


Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 99.98 Redo NoWait %: 100.00
Buffer Hit %: 99.86 In-memory Sort %: 100.00
Library Hit %: 89.43 Soft Parse %: 86.92
Execute to Parse %: 76.39 Latch Hit %: 99.93
Parse CPU to Parse Elapsd %: 21.18 % Non-Parse CPU: 91.11


=====

Begin Snap: 17713 23-Feb-11 08:00:40
End Snap: 17716 23-Feb-11 11:00:27

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 99.80 Redo NoWait %: 100.00
Buffer Hit %: 95.48 In-memory Sort %: 100.00
Library Hit %: 96.24 Soft Parse %: 89.00
Execute to Parse %: 78.44 Latch Hit %: 99.94
Parse CPU to Parse Elapsd %: 62.38 % Non-Parse CPU: 74.51

Jim
Tom Kyte
February 28, 2011 - 7:52 am UTC

that is a horrible soft parse %.

not sure what else to say as you didn't really say anything?

Followup on Soft Parse-Response

Jim Cox, March 02, 2011 - 11:05 am UTC

Hi Tom

I was answering your question regarding the review I submitted titled:
SGA Max an SGA Target

You wanted to know what my soft parse was because you did not like the fact that i had 1000 cursors configured

Jim
Tom Kyte
March 02, 2011 - 11:19 am UTC

Again, my hypothesis is

you are using a connection pool, your connections eventually get "max open cursors exceeded" and someone "fixed" it by bumping up the number of open cursors instead of having the developers track down their cursor leak. Sort of like using a band-aid to fix a leaking pipe, not very pretty and won't last very long.


And given that the hard parse rate is so high, that seems to add to my feelings that my hypothesis is correct - the developers are not really in tune with database development if they are hard parsing at such a rate. They are flooding the shared pool with literal sql and leaving cursors open at a relatively high rate.

Followup on Soft Parse-Response

Jim Cox, March 02, 2011 - 11:28 am UTC

Hi Tom

the software comes from the vendor and out of my control
I have never had max cursors exceeded. I have gradually just increased them starting at 100, incrementing by 100 and now up to 1000 over the course of the year. I thought I could keep more sql in the shared pool so it would not have to be swapped out. There were never any erros of any sort. Guess I was assuming incorrectly what the max cursors actually does as well as session_cursors. How do you determine just what the max and session cursor should be set at ?

Thanks

Tom Kyte
March 02, 2011 - 12:01 pm UTC

open cursors controls the number of open cursors an individual session may request to have open. If that session doesn't request that many cursors, it'll never hit it - all you've done is waste a bit of memory on each connection as we use that parameter to size a data structure (well, we allocate 64 slots at a time).

You probably should have just left it at the default value, it was more than sufficient.

Followup on Soft Parse-Response

Jim Cox, March 08, 2011 - 2:52 pm UTC

Hi Tom

just a followup after changing my cursors from 1000 to 100
Received this in DBConsole for my Database


ADDM Findings:

Soft parsing of SQL statements was consuming significant database time.

Investigate application logic to keep open the frequently used cursors.
Note that cursors are closed by both cursor close calls and session disconnects

(I have no control over the code)



Consider increasing the maximum number of open cursors a session can have by increasing the value of parameter "open_cursors".
Action Consider increasing the session cursor cache size by increasing the value of parameter "session_cached_cursors".

Rationale The value of parameter "open_cursors" was "100" during the analysis period.
Rationale The value of parameter "session_cached_cursors" was "100" during the analysis period

I am running 10.2.0.5 on windows 2003 R2 (both 64-bit)

I have one database with 2 web servers connecting with 100 persistent connections each

how do i actually determine a good number for cursors

Thanks
Jim
Tom Kyte
March 08, 2011 - 3:28 pm UTC

You have already (found the right size). I'm not sure why they suggest upping open_cursors - that would not decrease the number of parse calls.

Having session_cached_cursors at 100 is a pretty good setting, I wouldn't be too worried about upping that value too much - 100 to 150 is "normal".


The only reason I could see for upping open cursors would be to up session cached cursors - but I wouldn't go above 100 or 150 for that setting.

Followup on Soft Parse-Response (Thanks)

Jim Cox, March 15, 2011 - 12:14 pm UTC

Thanks for your time Tom

Really appreciate your assistance

Jim

hassan, September 25, 2011 - 2:56 pm UTC

oracle@abc:~> sqlplus

SQL*Plus: Release 10.1.0.3.0 - Production on Mon Feb 28 14:25:13 2005

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> alter system set sga_target ='500M';
alter system set sga_target ='500M'
*
ERROR at line 1:
ORA-01034: ORACLE not available


SQL> startup
ORA-00821: Specified value of sga_target is too small, needs to be at least 12M
SQL>

Tom Kyte
September 25, 2011 - 3:10 pm UTC

Ok, it is time for my favorite game:

Let's play "guess the question".

All you do to play is post some stuff and you let us try to guess what you wanted...




I'll guess you are using an spfile...

I'll guess you managed to set the sga_target to a really small value using "scope=spfile" and cannot start your database...

I'll guess you want to know what your options are...

Here is perhaps the easiest way:

idle> startup force
ORA-00821: Specified value of sga_target 4M is too small, needs to be at least 36M

<b>I have the same problem you do...</b>


idle> create pfile='/tmp/pfile.ora' from spfile;

File created.

<b>create an OS text file of parameters...</b>

idle> edit /tmp/pfile.ora

<b> edit it and fix it up...</b>

idle> create spfile from pfile='/tmp/pfile.ora';

File created.

<b>make it be my spfile again...</b>

idle> startup force
ORACLE instance started.

Total System Global Area  150667264 bytes
Fixed Size                  1342736 bytes
Variable Size              96469744 bytes
Database Buffers           46137344 bytes
Redo Buffers                6717440 bytes
Database mounted.
Database opened.
idle> 


and we are back in business...

SGA_max_Size and Sga_target

Rohit, October 09, 2011 - 10:27 am UTC

Hi

I am configuraring a new Oracle 10g instance for production purposes. I read from Oracle 10g new 
features for administrators exam guide (from Oracle Press) that when we use sga_target we should 
not set any parameters for the 4 dynamic tunable components, buffer cache, shared pool, java pool 
and large pool to reduce database's ability to adapt to database wrkload changes. How true is this? 
If I set sga_target to 1000M how much memory will these 4 components consume to start?

I have another question about sga_target & sga_max_size. If we set sga_max_size bigger than 
sga_target will sga_target always be lower than sga_max_size? For example

sga_target = 1200M
sga_max_size = 1600M

Does this make sense? Will Oracle allocate memory up to 1600M sometime or maximum it will reach 
1200M. What's the point combining these two parameters, shouldnt we just use sga_target and forget 
about sga_max_size? 



Followup   December 20, 2004 - 8am Central time zone:

how true is it?

100% true, if you set the sga_target, the other 4 components are set for you.


if you set the SGA_TARGET to 1000m, the 4 components will be sized to consume 1000m.  consider:

SQL> show parameter sga_target
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
sga_target                           big integer 1000M


SQL> show sga
 
Total System Global Area 1048576000 bytes
Fixed Size                   782424 bytes
Variable Size             259002280 bytes
Database Buffers          788529152 bytes
Redo Buffers                 262144 bytes


SQL> show parameter pool
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
buffer_pool_recycle                  string
global_context_pool_size             string
java_pool_size                       big integer 0
large_pool_size                      big integer 0
olap_page_pool_size                  big integer 0
shared_pool_reserved_size            big integer 12373196
shared_pool_size                     big integer 0
streams_pool_size                    big integer 0
SQL>


basically, Oracle will setup reasonable initial sized pools (if you know how to peek at _ 
parameters, you'll see them:

__java_pool_size               4194304
__large_pool_size              4194304
__shared_pool_size             247463936

) and will put the rest in the buffer cache.  Over time, if the pools need more, it'll steal from 
the buffer cache and increase them.


sga_target has to be less than or equal to sga_max_size.  It depends on the OS how the memory is 
reserved, but basically your 1200/1600 would have you start with an SGA of 1,200 meg that could be 
grown by you to 1600m (using alter system)


SQL> show parameter sga
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 1200M
sga_target                           big integer 1008M

SQL> alter system set sga_target = 1100m;
 
System altered.
 
SQL> alter system set sga_target = 1300m;
alter system set sga_target = 1300m
*
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



Ok if we manually add shared_pool_size=80m its getting configured we can check it in show parameter pool, if we set set shared_pool_size=1300m manually can it be configured?....

Tom Kyte
October 09, 2011 - 11:17 am UTC

sga_target we should
not set any parameters for the 4 dynamic tunable components, buffer cache,
shared pool, java pool


I would say that should use the words "do not have to" instead of "should not". You can - but do not technically have to. If you do set them, they will be considered "minimun values" - that is, if you set the sga_target to 10gb and each of the components to 1gb - that means each will be at least one gigabyte in size and the remaining six will be used to increase their sizes as the software sees fit.


If I set sga_target to 1000M how much memory will these 4 components consume to
start?


it depends and can vary over time. That is why it is called auto - it is automatic and under the covers.


I have another question about sga_target & sga_max_size. If we set sga_max_size
bigger than
sga_target will sga_target always be lower than sga_max_size?


target is less than or equal to max. So, it might not be lower, it could be equal.


Does this make sense? Will Oracle allocate memory up to 1600M sometime or
maximum it will reach
1200M.


that causes us to allocate and use 1200m, but allows you to dynamically alter the target up to 1600m without having to shutdown and restart.

Huge SGA cause performance degration

goh, December 17, 2011 - 8:11 am UTC

Hi Tom,

Is it any implication having huge SGA size (100GB) ? We have a heavy OLTP environment with about 2 Terabyte in size. Currently, we have more than 120GB physical RAM and we set 100GB SGA_MAX. Buffer hit ratio is 99.9% and we have adequte memory for the MTS and OS process. One of the consultant asks us to reduce the SGA_MAX size as he says to much SGA size will have impact on database performance. Can you share what is the impact if having too much SGA size assuming we have sufficient memory for OS and MTS operation ? Is it normal to have 100GB SGA size for 2 terabyte database size, it is only 5% of the total DB size ? Thanks.

Regards
Goh
Tom Kyte
December 17, 2011 - 9:28 am UTC

A 100gb SGA is becoming fairly "normal" these days.


do you see any indication of any overhead due to a large SGA? High cpu consumption for simple queries? lots of cache buffers chains latching issues?

what does the buffer cache adivisor tell you - if you were to decrease the SGA size - would physical reads increase?

SGA

sam, December 17, 2011 - 10:00 am UTC

Tom:

I thought on 32 bit machines an oracle instance memory max is 2GB and 64 bit machines it is 4 GB.

Based on your comment here you can set the SGA to any size you wish limited by the total physical RAM on machine.

I think you also mentioned that increaing memory/cache infinitely for oracle will not improve overall performance.

can you clarify?
Tom Kyte
December 17, 2011 - 10:06 am UTC

64 bit is usually done using 48 bit of the pointer on intel x86 - allowing for about 256 TB - 262,144gb - of memory to be addressed.


on 32 bit machines it was typically 1.75gb - sometimes extendable by magic on windows with a series of switches that could totally destabilize your system.

I think you also mentioned that increaing memory/cache infinitely for oracle
will not improve overall performance.


correct, there are diminishing returns on using memory at some point for disk based database systems (but not a database like times ten). The larger the cache, the more to manage, the possibly longer the list of blocks to walk.

But 100gb is not considered huge these days, it is becoming a new normal in some respects. 10 years ago, it would have been different.

SGA

sam, December 17, 2011 - 10:12 am UTC

Tom:

so on 32 bit machines SGA max = 1.75 GB

and on 64 bit machines SGA max = 252 TB?

Those 32 bit machine do not scale at all. it will only do small sized databases.
Tom Kyte
December 17, 2011 - 12:22 pm UTC

those 32 bit machines did us well in their time Sam.

When I started working at Oracle - if your server had 256MB of ram and 4 cpus (which could have been 486 class cpus - the sequent machine I used was) - you had a honking big server.

When I started at Oracle - the theoretical 1.75gb limit was something we didn't anticipate hitting any time soon.

And you know what - we got stuff done on those machines, believe it - or not. The world ran on that. Mainframes - they were even smaller (memory wise).


You can do quite a bit with a 1.75gb SGA...

But, today in 2011 - it wouldn't make sense to use 32bit since 64bit is so widely available.



The theoretical maximum amount of memory varies by platform - it could be 2^34gb of memory if they used all of their bits in the pointer. It depends.

And it is theoretical, no ones put such a thing together yet - physical machine limits you to a lot less. You have to have engineered a machine capable of having 256tb of memory.

Huge SGA

goh, December 17, 2011 - 9:56 pm UTC

Thanks Tom.

No swap activity happen in the OS level or high CPU utilization in the server.

cache buffers chains latching does not appear in the top 5 events (1 hour interval).

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time 30,954 66.1
db file sequential read 4,911,498 15,815 3 33.8 User I/O
db file parallel write 36,982 927 25 2.0 System I/O
log file parallel write 80,478 320 4 0.7 System I/O
log file sync 55,473 273 5 0.6 Commit



Apart from top 5 events, where can we check if cache buffers chains latching contention in the awr report and which event we should pay attention. Is there any rule of thumb for the buffers chains latching contention event?

From -> Latch Activity
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Name Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
cache buffer handles 2,273,312 0.6 0.0 0 0 N/A
cache buffers chains 1,424,332,001 0.1 0.0 1 8,422,533 0.1
cache buffers lru chain 7,758,100 3.4 0.0 3 7,706,088 4.2
cache table scan latch 0 N/A N/A 0 35,392 0.2

Latch Sleep Breakdown
Latch Name
----------------------------------------
Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3
-------------- ----------- ----------- ---------- -------- -------- --------
cache buffers lru chain
7,758,100 260,217 5,355 255,117 0 0 0


From SGA Target advisor, set SGA to 134GB should be the ideal setting for our environment. Right.

SGA Target Advisor
SGA Target SGA Size Est DB Est Physical
Size (M) Factor Time (s) Reads
---------- ---------- ------------ ----------------
19,200 0.3 3,847,412 534,361,335
38,400 0.5 2,382,446 324,887,652
57,600 0.8 1,765,080 206,741,288
76,800 1.0 1,429,092 148,499,704
96,000 1.3 1,275,187 114,805,121
115,200 1.5 1,177,730 93,465,714
134,400 1.8 1,099,845 76,388,248
153,600 2.0 1,099,851 76,388,248

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.99 Redo NoWait %: 100.00
Buffer Hit %: 99.32 In-memory Sort %: 100.00


This is based on 1 hour interval of AWR report during the peak hour.

Appreciate your advice.

Regards
Goh
Tom Kyte
December 18, 2011 - 5:29 am UTC

you are not really missing your get requests on the latch and not sleeping on it - so at a cursory glance, it doesn't appear to be an issue. And given that you still do 5million IO's in that IO (reads), it doesn't seem to be advisable to reduce the buffer cache (this is all off the top of the head here, I don't really know anything about your database...)

I'd doubt that shrinking the SGA would be advisable.

134gb would be the ideal setting if and only if you had more memory. It would be a bad idea as it is now.


Given the high number of db file sequential reads - I'm hoping you are a pure OLTP system...

Huge SGA

goh, December 18, 2011 - 5:43 am UTC

Thanks Tom,

It is the OLTP environment and it appears that one of SQL required some tuning 15,000 executions in 1 hour and each execution read contribute about 5000 IO.

Can share from which section and events that tell no contention of buffer cache latch as explain in your previous reply "you are not really missing your get requests on the latch and not sleeping on it ".

Appreciate it.


cache buffers lru chain
7,758,100 260,217 5,355 255,117

From -> Latch Activity
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Name Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
cache buffer handles 2,273,312 0.6 0.0 0 0 N/A
cache buffers chains 1,424,332,001 0.1 0.0 1 8,422,533 0.1
cache buffers lru chain 7,758,100 3.4 0.0 3 7,706,088 4.2
cache table scan latch 0 N/A N/A 0 35,392 0.2

Latch Sleep Breakdown
Latch Name
----------------------------------------
Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3
-------------- ----------- ----------- ---------- -------- -------- --------
cache buffers lru chain
7,758,100 260,217 5,355 255,117 0 0 0


Regards
Goh

Tom Kyte
December 18, 2011 - 8:11 am UTC

your average slps/miss and wait times.

you don't have very many misses, hardly any sleeps.

AMM

A reader, May 08, 2012 - 11:03 am UTC

Tom:

I am migrating a 9i schema to 11g R2 new database on linux.

Do you normally recommend using the automatic memory manager in 11g or just allocation fixed amount of RAM to use?

I heard some DBA saying the memory manager does not work very well and they had to go back to manual mode.
Tom Kyte
May 08, 2012 - 11:27 am UTC

automatic memory management works well for those "small databases, with a few users, that do not have a dedicated dba to watch". I would use auto pga memory management along with either

a) sga target with lower bounds set for the db cache, shared pool and such

b) just setting the db cache, shared pool and such and using the advisors to see if we should schedule a change



memory

A reader, May 08, 2012 - 10:55 pm UTC

it is fairly small database (15 GB) and a few hundred users (not database accounts).

But i select AMM in dbca and I selected 1 GB for total memory. Do i need to do something special in dbca or after db is created to set minimum buffer cache size or anything else.
Tom Kyte
May 10, 2012 - 3:06 am UTC

Sam

have your DBA take care of this please.

or re-read what I already typed just above, I could cut and paste it again, but that isn't really necessary is it.

SDO_NET Cache

Jon T, September 12, 2012 - 10:36 am UTC

When we size the SGA do we have to consider the Load on Demand cache managed by the Network Data Model component of Oracle Spatial? I'm asking since I'm not really sure where this cache is stored (or if it's even in the SGA).

http://docs.oracle.com/cd/E11882_01/appdev.112/e11831/sdo_net_concepts.htm#CIHFHAID

It looks like there's a config file that you can use to force Oracle to keep certain node partitions in memory but I have no idea if other things, like ASMM, play a role.

Thanks for all the help over the years!

A sysadmin speaks

Tim, September 27, 2012 - 7:58 pm UTC

Hi Tom & all contributors - As a Unix sysadmin with little knowledge of Oracle internals, I've found this page very useful in explaining the thought processes needed to plan SGA sizing (and hence physical memory requirements).

I appreciate that the DB is better at managing its data than OS filesystem caching, but in future any DBA who demands more memory on the grounds "more is better" will be asked to show some evidence that the application needs it.

To digress slightly, and give you a view from the "other side" - Many years ago, I went on a Unix performance tuning course. The tutors introduction was basically "I don't know why you're here - 95% of performance problems are due to badly designed Apps". And he was right, most issues I've seen since then could only be slightly helped by hardware or OS tweaking, whereas a review of indexes or SQL gave huge improvements.

Finally, I believe the old issue of disk I/O & multiple spindles has been largely obsoleted by modern Enterprise disk arrays with large caches. But I'll browse the rest of "AskTom" to get your views on these side issues.

Thanks again!

Large SGA Causing paging and CBC

Praveen, May 07, 2013 - 9:43 am UTC

Hi Tom,

I have a OLTP system running on windows 64 bit. We were facing a lot of CBC, lot of paging on the system and high CPU usage as well.

We have a SGA_TARGET=46gb and SGA_MAX=54gb, it is a 10.2.0.3 database. Size of the database is about 450gb.

I am trying to prove to client that the huge SGA is causing the latches and paging to occur.

By reducing the SGA as per advisory I do not see a raise in the estimated physical reads, does that mean I can reduce the SGA?

Let me know your advice, or the points I need to look for to highlight that indeed the large SGA is cauising this.

Thanks,
Praveen
Tom Kyte
May 07, 2013 - 3:33 pm UTC

paging - sure. latching, maybe - maybe not.

latching is a symptom, not a cause. The number one cause of the sympton - artificially high concurrency.

http://tinyurl.com/RWP-OLTP-CONNECTIONS

showing that there is paging going on would be easy, should be anyway. The OS would tell you that. is there?


you don't say how much physical RAM the machine has, if it has 64gb or more, I don't think the SGA is too large as you would have a small number of connections (see video), PGA memory would be small (no big sorts or anything, it is OLTP).

Estimate SGA Size

pranav, June 21, 2013 - 4:19 pm UTC

Tom,

First of all, thank you very much for spending your valuable time in helping people like me. We are so blessed.

I am trying to calculate the estimated size of SGA. I am not sure how to check whether my database benefits from increasing the SGA size(especially shared_pool/buffer_cache). I have access to AWR reports but no OEM. Please provide some guidance. Thanks for your help.
Tom Kyte
July 01, 2013 - 5:07 pm UTC

there is a buffer cache advisory report in there, check it out.

What happen if SGA memory is very low ?

Kumaraswamy, June 29, 2013 - 9:25 am UTC

Hi Tom,
what will happen if SGA's allocated memory is very low apart from it's performance ?


Thanks in advance.
Tom Kyte
July 01, 2013 - 9:22 pm UTC

you could receive ora-4031 errors, unable to allocate.

Estimate SGA Size

pranav, July 01, 2013 - 5:13 pm UTC

Thanks Tom. But we disabled the parameter db_cache_advice parameter as it caused the database outages. Please tell me how else we can make an educated guess for the correct parameters. Wondering if there is a way at all(apart from the advisory).
Tom Kyte
July 01, 2013 - 9:34 pm UTC

do you have a bug# I can look at then? how did you determine it was this?


there are too many variables to make an educated guess short of "more is probably better except when it isn't"

why exist some netcard information in spfile ?

A reader, December 09, 2013 - 8:29 am UTC

strings /oracle/products/11.2.0/dbs/spfileXXXXXXXXX.ora 查看spfile,some information about netcard exists,i donnot know why?
...............................................
*.shared_pool_size=2g
xxx.shared_pool_size=2147483648
*.sql92_security=TRUE
*.standby_file_management='AUTO'
xxx.thread=2
xxx.thread=1
xxx.undo_tablespace='UNDOTBS1'
xxx.undo_tablespace='UNDOTBS2'
0.0 0.7 0 0
11/29/13 00:00:03 lan904 809.2 389.2 0 0
NetIF NetIn NetOut Net Net
Date Time Name bytes/s bytes/s COLLISION ERROR
----------------------------------------------------------------------------------
11/29/13 00:00:47 lan0 67.9 75.8 0 0
11/29/13 00:00:47 lan1 0.2 3.6 0 0
11/29/13 00:00:47 lan9 11.8 8.8 0 0
11/29/13 00:00:47 lan11 2.0 2.2 0 0
11/29/13 00:00:47 lan13 412.7 485.1 0 0
11/29/13 00:00:47 lan3 0.4 0.0 0 0
11/29/13 00:00:47 lan14 0.7 0.0 0 0
11/29/13 00:00:47 lan4 0.4 0.0 0 0
11/29/13 00:00:47 lan21 0.7 0.0 0 0
11/29/13 00:00:47 lan7 0.4 0.0 0 0
11/29/13 00:00:47 lan26 0.7 0.0 0 0
11/29/13 00:00:47 lan900 69.2 75.8 0 0
11/29/13 00:00:47 lan901 0.2 3.6 0 0
11/29/13 00:00:47 lan902 14.1 8.8 0 0
11/29/13 00:00:47 lan903 2.1 2.2 0 0
11/29/13 00:00:47 lan904 402.9 479.4 0 0
NetIF NetIn NetOut Net Net
Date Time Name bytes/s bytes/s COLLISION ERROR
----------------------------------------------------------------------------------
11/29/13 00:01:30 lan0 41.7 41.2 0 0