ASM INSTANCE & Shared Pool (ORA-4031)

原创 作者:xzh2000 时间:2008-03-26 14:17:56 0 删除 编辑

The setting for the SHARED_POOL_SIZE parameter determines the amount of memory required to manage the instance. The setting for this parameter is also used to determine the amount of space that is allocated for extent storage. The default value for this parameter is suitable for most ASM environments.

[@more@]1) For 32-bit environments 32 MB is the default and minimum requirement for an ASM instance, but 48 MB is recommended.

2) On 64-bit platforms 88 MB are required for an ASM instance, recommended values is 150 MB.

3) Guidelines to set Shared Pool for database Instances using ASM as storage option (When the Automatic Memory Management feature is not used in a database instance, the SGA parameter settings for a database instance may require minor modifications to support ASM):

· SHARED_POOL_SIZE initialization parameter—Aggregate the values from the following queries to obtain the current database storage size that is either already on ASM or will be stored in ASM. Next, determine the redundancy type that is used or will be used, and calculate the SHARED_POOL_SIZE, using the aggregated value as input.

SQL> SELECT SUM(bytes)/(1024*1024*1024) FROM V$DATAFILE;
SQL> SELECT SUM(bytes)/(1024*1024*1024) FROM V$LOGFILE a, V$LOG b
SQL> SELECT SUM(bytes)/(1024*1024*1024) FROM V$TEMPFILE WHERE

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

4) When Migrating from single instance to RAC add an additional 15% more shared pool to the Database & ASM instances, since RAC-specific memory is mostly allocated in the shared pool at SGA creation time (that value is heuristic, based on RAC sizing experience).

请登录后发表评论 登录


  • 博文量
  • 访问量