About Setting Database Initialization Parameters for Use with Oracle ASM

When you do not use automatic memory management in a database instance, the SGA parameter settings for a database instance may require minor modifications to support Oracle ASM. When you use automatic memory management, the sizing data discussed in this section can be treated as informational only or as supplemental information to help determine the appropriate values that you should use for the SGA. Oracle highly recommends using automatic memory management.

The following are configuration guidelines for SGA sizing on the database instance:

• PROCESSES initialization parameter—Add 16 to the current value

• LARGE_POOL_SIZE initialization parameter—Add an additional 600K to the current value

• SHARED_POOL_SIZE initialization parameter—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