Topic: Database Administration >> SGA Sizing
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| avnish |
Posted: Jan 28, 2008 03:46:23 AM |
Total Post: 53
Joined: Dec, 2007
|
Hi,
What is the standard sizing paramter for setting an SGA. I have 4GB RAM
how much each of segments of SGA will be set?
i.e.
how much buffer cache,java pool,large pool,redo log buffer cache
shared pool will be set in 4GB RAM
please give answers as early as possible.....
Its urgent need....
thanks in advance |
|
|
 Murtuja |
| Posted: Jan 28, 2008 05:21:13 AM |  |
|
Total Post: 814
Joined: Jan, 2006
|
If you are running Oracle 10g then you have to opt for Automatic Memory Management.
Oracle Database 10g automates the management of shared memory used by an instance and liberates administrators from having to manually configure the sizes of shared memory components.
http://www.oracle.com/technology/products/manageability/database/sga.html
|
|
|
|
|
| Mohammed Taj |
| Posted: Jan 28, 2008 08:07:13 AM | |
|
Total Post: 694
Joined: Jul, 2007
|
When you create database through DBCA that time sga size is 40% of PHYSICAL RAM is calculated.
you can also start with same good % with SGA size and enable automatic sga mgmt, and running workload and db performance you will adjust your memory size.
|
|
|
|
|
| Vinod |
| Posted: Jan 28, 2008 09:59:30 AM | |
|
Total Post: 54
Joined: Oct, 2007
|
I would like to emphasize few brief points over here , the X$KSMSP view shows the breakdown of memory in the SGA. You can query this view to build trend
information on memory usage in the SGA and to see more detail on free memory chunks available\required and v$sga_dynamic_components would give you the sga
components at that point of time. Moreover it would be a best practice to first run the application on a test system .
select component, current_size, min_size, max_size, granule_size
from v$sga_dynamic_components
/
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE
---------------------------------------------------------------- -------------------- -------------------- -------------------- ---
shared pool 352321536 352321536 0
large pool 4194304 4194304 0
java pool 4194304 4194304 0
streams pool 0 0 0
DEFAULT buffer cache 79691776 62914560 0
KEEP buffer cache 0 0 0
RECYCLE buffer cache 0 0 0
DEFAULT 2K buffer cache 0 0 0
DEFAULT 4K buffer cache 0 0 0
DEFAULT 8K buffer cache 0 0 0
DEFAULT 16K buffer cache 0 0 0
DEFAULT 32K buffer cache 0 0 0
ASM Buffer Cache 0 0 0
13 rows selected.
in 10g ASM will manage the best size for these components in the SGA in 10g by sga_target and sga_max_size
Shared Pool
Large Pool
Java Pool
Buffer Cache (the default one managed by db_cache_size)
Streams Pool (new to 10g Release 2)
The other buffer caches (managed through parameters DB_nK_CACHE_SIZE, DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE), Log Buffer, and Fixed SGA areas are not
automatically tuned by MMAN .
However By querying x$ksppi,x$ksppcv,x$ksppsv and v$sqlarea an estimate size of shared pool can be calculated
select cr_shared_pool_size,
sum_obj_size, sum_sql_size,
sum_user_size,
(sum_obj_size + sum_sql_size+sum_user_size)* 1.3 min_shared_pool
from (select sum(sharable_mem) sum_obj_size
from v$db_object_cache where type<> 'CURSOR'),
(select sum(sharable_mem) sum_sql_size from v$sqlarea),
(select sum(250*users_opening) sum_user_size from v$sqlarea),
(select to_Number(b.ksppstvl) cr_shared_pool_size
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.ksppinm ='__shared_pool_size' );
Oracle9i provides an option to completely automate the management of PGA memory.
DBA merely need to specify the maximum amount of PGA memory available
to an instance PGA_AGGREGATE_TARGET. The database automatically distributes this memory
among various active queries in an intelligent manner and PGA_AGGREGATE_TARGET defaults to 20% of the size of the SGA
|
|
|
|
|
| avnish |
| Posted: Jan 31, 2008 01:28:36 AM | |
|
Total Post: 53
Joined: Dec, 2007
|
Thanks Vinod for this detailed information.
Thanks everybody..
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 814 |
| Mohammed Taj | 694 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| positive fanatic | 361 |
| Gitesh Trivedi | 322 |
| Gopu Gopi | 239 |
| neeraj sharma | 228 |
| Ramesh Jois | 226 |
| snehalatha p | 169 |
|
|
