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

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.

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.

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?

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

 

Hi

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

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,

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



 

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
 

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


 

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,

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,

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,

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,

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

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

please give a sql or link to page.

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
/

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

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

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

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 :-)

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

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

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.

Yes. valid point. thanks

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.

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

Thanks.

Tom,

Thanks for the response

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

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

Thanks..

Thanks.

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 ?

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.

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.

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?

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

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

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

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

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.

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.

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?

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.

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

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.

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?

"...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.

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

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,

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

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?

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

"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.

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

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.

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.

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

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

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?

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?

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.

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.

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.

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!

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,

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.

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

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

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.

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

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

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.

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

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

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

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

Just a thanks Tom

I will take a look see

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

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?

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

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

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).

"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."

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. 
 

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.

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.

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?

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

"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

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.

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 ;)

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.

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

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.

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

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

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

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

Thanks for your time Tom

Really appreciate your assistance

Jim

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>

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> 

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?....

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:

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:

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.

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

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:

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.

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.

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!

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!

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,

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.

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


Thanks in advance.

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).

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