Tuning SGA


-- monitoring SGA system parameters
--
-- max size of the sga
select name, value, description from v$parameter where name='sga_max_size';
-- sum of all values of the following query cannot exceed sga_max_size

select sum(value) from
(
select name, value, description from v$parameter
where name in ('java_pool_size',
'shared_pool_size',
'large_pool_size')
UNION
select name, value, description from v$parameter where name like
'db_%cache_size'
UNION
select name, value, description from v$parameter where name='log_buffer'
);

--
-- lets have a look at something
-- the value of sga_max_size may not be equal to the sum of its components
-- the difference between these tow values will give you the approximate
-- free space
--
select trunc(((t2.sga_max_size - t1.total_sga_params)/1024/1024)) FREE_MB
from
(
select sum(value) total_sga_params from
(
select name, value, description from v$parameter
where name in ('java_pool_size',
'shared_pool_size',
'large_pool_size')
UNION
select name, value, description from v$parameter where name like
'db_%cache_size'
UNION
select name, value, description from v$parameter where
name='log_buffer'
)
) t1
,(
select sum(value) sga_max_size from v$parameter where
name='sga_max_size'
) t2;

--
-- other ways of obtaining free sga size
--
select * from v$sgastat where name='free memory';
select current_size/1024/1024 MB from v$sga_dynamic_free_memory;
--
-- changing sga and sga componenets values
--
-- recommended: sga_max_size should be phsical_memory/2
alter system set sga_max_size=500M scope=spfile;
-- changes of this parameter will take affect after instance restart
alter system set java_pool_size=150M scope=spfile;
-- changes of this parameter will take affect after instance restart
alter system set shared_pool_size=150M;
alter system set large_pool_size=50M;
alter system set db_cache_size=150M;

-- which system parameter can be changed without an instance restart
--
select * from v$parameter where issys_modifiable='IMMEDIATE'
--
-- following parameter needs an instance restart
--
select * from v$parameter where issys_modifiable!='FALSE'
-- to change these kind of system parameters following steps can be
followed
alter system set <name>=<value> scope=spfile;
-- from now on you should login from sqlplus "/as sysdba"
alter system checkpoint;
shutdown immediate;
startup;

Advices for SGA
--
-- advices for sga
--
select * from V$SHARED_POOL_ADVICE;
select * from V$DB_CACHE_ADVICE;
select * from V$PGA_TARGET_ADVICE;
NOTES:
1- db_cache_advice system parameter should be ON for v$shared_pool_advice
system view can be up-to-date.
2- for all advice views statistics_level system parameter should be at
least in BASIC mode.
-- setting db_cache_advice system parameter
--
select
name,
value,
issys_modifiable,
description
from
v$parameter
where
name='db_cache_advice';
alter system set db_cache_advice='ON';
select
name,
value,
issys_modifiable,
description
from
v$parameter
where
name='db_cache_advice';
--
-- modifying statistics_level
--
select value from v$parameter where name='statistics_level';
alter system set statistics_level='TYPICAL';
-- allowed values are [ALL|TYPICAL|BASIC]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: