configuration guidelines for SGA sizing on the database instance.ASM

Aggregate the values from the following queries to obtain the current database storage size that is either on Oracle ASM or stored in Oracle ASM. Next, determine the redundancy type and calculate the SHARED_POOL_SIZE using the aggregated value as input.



SELECT SUM(bytes)/(1024*1024*1024) FROM V$DATAFILE;


SELECT SUM(bytes)/(1024*1024*1024) FROM V$LOGFILE a, V$LOG b
WHERE a.group#=b.group#;


SELECT SUM(bytes)/(1024*1024*1024) FROM V$TEMPFILE
WHERE status='ONLINE';


◦For disk groups using external redundancy, every 100 GB of space needs 1 MB of extra shared pool plus 2 MB
◦For disk groups using normal redundancy, every 50 GB of space needs 1 MB of extra shared pool plus 4 MB
◦For disk groups using high redundancy, every 33 GB of space needs 1 MB of extra shared pool plus 6 MB