OraFAQ Forum: Performance Tuning » Value for sga

Home » RDBMS Server » Performance Tuning » Value for sga_target (Oracle 10.2.0.4.0 on AIX 5.3)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Value for sga_target [message #557762]

Fri, 15 June 2012 10:02

raj9999
Messages: 49
Registered: June 2011

Member

What value should i set for sga_target in my oracle 10g database?

Currently -
sga_max_size = 32GB
pga_aggregate_target = 6GB
RAM on server = 64 GB

I'll need to disable db_block_buffers parameters in order to enable sga_target. right?

Report message to a moderator

Re: Value for sga_target [message #557763 is a reply to message #557762]

Fri, 15 June 2012 10:09

BlackSwan
Messages: 20107
Registered: January 2009

Senior Member

what does SGA advisor report?

Report message to a moderator

Re: Value for sga_target [message #557764 is a reply to message #557763]

Fri, 15 June 2012 10:17

raj9999
Messages: 49
Registered: June 2011

Member

How to check that? please provide a query if possible.

Report message to a moderator

Re: Value for sga_target [message #557766 is a reply to message #557764]

Fri, 15 June 2012 10:38

Michel Cadot
Messages: 54208
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> select * from DICTIONARY where lower(comments) like '%advice%' order by 1;
TABLE_NAME                     COMMENTS
------------------------------ --------------------------------------------------
DBA_HIST_DB_CACHE_ADVICE       DB Cache Advice History Information
DBA_HIST_JAVA_POOL_ADVICE      Java Pool Advice History
DBA_HIST_MTTR_TARGET_ADVICE    Mean-Time-To-Recover Target Advice History
DBA_HIST_PGA_TARGET_ADVICE     PGA Target Advice History
DBA_HIST_SGA_TARGET_ADVICE     SGA Target Advice History
DBA_HIST_SHARED_POOL_ADVICE    Shared Pool Advice History
DBA_HIST_STREAMS_POOL_ADVICE   Streams Pool Advice History
GV$DB_CACHE_ADVICE             Synonym for GV_$DB_CACHE_ADVICE
GV$JAVA_POOL_ADVICE            Synonym for GV_$JAVA_POOL_ADVICE
GV$MTTR_TARGET_ADVICE          Synonym for GV_$MTTR_TARGET_ADVICE
GV$PGA_TARGET_ADVICE           Synonym for GV_$PGA_TARGET_ADVICE
GV$PGA_TARGET_ADVICE_HISTOGRAM Synonym for GV_$PGATARGET_ADVICE_HISTOGRAM
GV$PX_BUFFER_ADVICE            Synonym for GV_$PX_BUFFER_ADVICE
GV$SGA_TARGET_ADVICE           Synonym for GV_$SGA_TARGET_ADVICE
GV$SHARED_POOL_ADVICE          Synonym for GV_$SHARED_POOL_ADVICE
GV$STREAMS_POOL_ADVICE         Synonym for GV_$STREAMS_POOL_ADVICE
V$DB_CACHE_ADVICE              Synonym for V_$DB_CACHE_ADVICE
V$JAVA_POOL_ADVICE             Synonym for V_$JAVA_POOL_ADVICE
V$MTTR_TARGET_ADVICE           Synonym for V_$MTTR_TARGET_ADVICE
V$PGA_TARGET_ADVICE            Synonym for V_$PGA_TARGET_ADVICE
V$PGA_TARGET_ADVICE_HISTOGRAM  Synonym for V_$PGA_TARGET_ADVICE_HISTOGRAM
V$PX_BUFFER_ADVICE             Synonym for V_$PX_BUFFER_ADVICE
V$SGA_TARGET_ADVICE            Synonym for V_$SGA_TARGET_ADVICE
V$SHARED_POOL_ADVICE           Synonym for V_$SHARED_POOL_ADVICE
V$STREAMS_POOL_ADVICE          Synonym for V_$STREAMS_POOL_ADVICE

SQL> desc V$SGA_TARGET_ADVICE            
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 SGA_SIZE                                  NUMBER
 SGA_SIZE_FACTOR                           NUMBER
 ESTD_DB_TIME                              NUMBER
 ESTD_DB_TIME_FACTOR                       NUMBER
 ESTD_PHYSICAL_READS                       NUMBER

Database Reference

Regards
Michel

Report message to a moderator

Re: Value for sga_target [message #557767 is a reply to message #557762]

Fri, 15 June 2012 10:41

BlackSwan
Messages: 20107
Registered: January 2009

Senior Member

raj9999 wrote on Fri, 15 June 2012 08:02

What value should i set for sga_target in my oracle 10g database?

Currently -
sga_max_size = 32GB
pga_aggregate_target = 6GB
RAM on server = 64 GB

I'll need to disable db_block_buffers parameters in order to enable sga_target. right?

how much space does the application code consume if it all were in RAM at the same time?

Report message to a moderator

Re: Value for sga_target [message #558985 is a reply to message #557767]

Wed, 27 June 2012 18:24

alan.kendall@nfl.com
Messages: 160
Registered: June 2012
Location: Culver City, California

Senior Member

I usually set sga_target to be 10g lower than my sga_max_size. This give me the ability to increase the sga_target immediately and allocate the memory to other sga cache areas.
CREATE PFILE = '$ORACLE_HOME/dbs/init_pre_30G_SGA_pfile.ora' FROM SPFILE;
alter system set sga_max_size=30g scope=spfile sid='*';
alter system set sga_target=20g scope=spfile sid='*';
shutdown/startup oracle one instance at a time to get new sga_max_size. Fix any errors from an instance that is still up.

After the startup, if I ever need to increase a cache, I can do so immediately without a shutdown.
Suppose I want to increase db_16k_cache_size from 2 gigabytes to 5 gigabytes, I can do so immediately without shutting down by the following:
CREATE PFILE = '$ORACLE_HOME/dbs/init_pre_30G_SGA_pfile.ora' FROM SPFILE;
alter system set sga_target=23g scope=memory sid='PROD1'; -- increased by 3 gigabytes
alter system set db_16k_cache_size=5g scope=memory sid='PROD1'; -- increased by 3 gigabytes
alter system set sga_target=23g scope=memory sid='PROD2'; -- increased by 3 gigabytes
alter system set db_16k_cache_size=5g scope=memory sid='PROD2'; -- increased by 3 gigabytes
alter system set sga_target=23g scope=spfile sid='*'; -- Make permanent for future startups.
alter system set db_16k_cache_size=5g scope=spfile sid='*'; -- Make permanent for future startups.

Report message to a moderator

Re: Value for sga_target [message #558986 is a reply to message #558985]

Wed, 27 June 2012 18:33

BlackSwan
Messages: 20107
Registered: January 2009

Senior Member

Ready, Fire, AIM!

Do you just pull new values out of a hat or use a UOIJA board to obtain new numbers?

Report message to a moderator

Re: Value for sga_target [message #558987 is a reply to message #558986]

Wed, 27 June 2012 18:57

alan.kendall@nfl.com
Messages: 160
Registered: June 2012
Location: Culver City, California

Senior Member

I take snapshots of gv$latch to determine if I am latching at peak times. I then cache heavy hit objects to reduce physical disk reads and latching on freeing up buffers. I use gv$segment_statistics to detemine objects that are being read physically extensively. I do joins on dba_segments that tells me the buffer_pool with gv$bh to determine exactly how much is cached for each buffer_pool. I have been able to reduce physical I/O by 9 times by caching and almost completely eliminate latching.

Here is my io60.sql (and its output) which shows me what is being read physically at this minute in time:
INSTANCE_NAME IO_PER_MINUTE STATISTIC_NAME OBJECT_NAME
---------------- ------------- ------------------------------ -----------------------
NDOCP4 67 physical writes NFLPROD.DM_SYSOBJECT_S.
NDOCP1 900 physical read requests NFLPROD.NFL_CONTENT_S.
NDOCP1 1186 physical read requests NFLPROD.DM_SYSOBJECT_R.
NDOCP4 1848 physical read requests NFLPROD.NFL_CONTENT_S.
NDOCP4 2141 physical read requests NFLPROD.DM_SYSOBJECT_S.
NDOCP4 4587 physical read requests NFLPROD.DM_SYSOBJECT_R.
NDOCP1 114399 physical reads direct NFLPROD.NFL_CONTENT_S.
NDOCP1 114401 physical reads NFLPROD.NFL_CONTENT_S.
NDOCP1 151024 physical reads direct NFLPROD.DM_SYSOBJECT_R.
NDOCP1 151024 physical reads NFLPROD.DM_SYSOBJECT_R.
NDOCP4 235428 physical reads NFLPROD.NFL_CONTENT_S.
NDOCP4 235428 physical reads direct NFLPROD.NFL_CONTENT_S.
NDOCP4 264604 physical reads direct NFLPROD.DM_SYSOBJECT_S.
NDOCP4 264672 physical reads NFLPROD.DM_SYSOBJECT_S.
NDOCP4 583470 physical reads direct NFLPROD.DM_SYSOBJECT_R.
NDOCP4 583477 physical reads NFLPROD.DM_SYSOBJECT_R.
-------------
2709202

io60.sql:
set termout off
set lines 160
break on report
compute sum of io_per_minute break on report
drop table gv$segment_statistics1 purge;
drop table gv$segment_statistics2 purge;
column statistic_name format a30
create table gv$segment_statistics1 as select * from gv$segment_statistics where upper(statistic_name) like '%PHYSICAL%';
execute dbms_lock.sleep(60);
create table gv$segment_statistics2 as select * from gv$segment_statistics where upper(statistic_name) like '%PHYSICAL%';
set wrap off
set termout on
select b.value last, a.value first,i.instance_name,(b.value-a.value)*1 IO_PER_MINUTE,a.statistic_name,
a.owner||'.'||a.object_name||'.'||a.subobject_name object_name
from gv$segment_statistics2 b,gv$segment_statistics1 a,gv$instance i
where a.statistic_name=b.statistic_name and a.inst_id=i.inst_id and b.inst_id=i.inst_id
and a.inst_id=b.inst_id and a.object_type=b.object_type
and a.owner||'.'||a.object_name||'.'||a.subobject_name=b.owner||'.'||b.object_name||'.'||b.subobject_name
and upper(a.statistic_name) like '%PHYSICAL%'
and a.object_name not like 'GV$SEGMENT_STATISTICS%'
and b.value-a.value > 0
order by b.value-a.value;
set termout off
drop table gv$segment_statistics1 purge;
drop table gv$segment_statistics2 purge;
set termout on

Alan

Report message to a moderator

Re: Value for sga_target [message #559012 is a reply to message #558987]

Wed, 27 June 2012 23:15

Michel Cadot
Messages: 54208
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Previous Topic:	a table deleted or updated frequency issue
Next Topic:	redo size

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 23 10:52:09 CDT 2013

Total time taken to generate the page: 0.16051 seconds