ORA-4031错误，请大家帮忙看看是否可以定位原因

bluemoon0083

I added up all the subpools, 2082 MB, still less than 2560M......

BTxigua

原帖由 Yong Huang 于 2011-4-2 03:25 发表
> 确实，shared pool原来的大小是2560M。

Something is not right. I added up all the bytes in v$sgastat for each instance as shown in the attachment ngact1_j000_4311.rar. All the sums are about 450 MB. Maybe the shared pool size was 450 MB when the error occurred? Or I didn't do the addition correctly?

Yong Huang

Memory Utilization of Subpool 1 :455890432
Memory Utilization of Subpool 2 :436233560
Memory Utilization of Subpool 3 :453006256
Memory Utilization of Subpool 4 :419451416
Memory Utilization of Subpool 5 :419451680
                                          Total :2184033344

Yong Huang

原帖由 bluemoon0083 于 2011-4-1 19:19 发表
they have 5 subpools, you mean 450 MB here is the size of one subpool ?

I'm sorry. Somehow I was thinking of 5 instances of a RAC database. A big mistake! Indeed you have 5 subpools and the total size is about what you said.

Since memory is evenly distributed and used by your subpools, reducing subpools is not the best solution, even though it should be able to merge some small chunks into bigger ones. Your biggest shared pool consumers are gcs resources and gcs shadows. According to Bug 6614853, you can

--- begin quote ---
execute following query
    select * from v$resource_limit
     where resource_name like 'gcs%' or resource_name like 'ges%'

  And compare max_utilization and initial_allocations.
  If max_utilization is very large compared to initial_allocations
  for ges_ress/ges_locks, they can consider to set _lm_ress/_lm_locks
  parameter to allocate spaces at database startup time.
--- end quote ---

Other settings that can save shared pool usage will turn off various database advisories. I always set db_cache_advice to off on all databases I manage (I never check v$db_cache_advice). Several others are controlled by underscore parameters, such as _object_statistics and _library_cache_advice (Ref: Bug 8561410). Implications of setting them can be found in
http://yong321.freeshell.org/com ... cyAndStatistics.doc

Anyway, I would check v$resource_limit for 'gcs%' resources and possibly lower _lm_ress/_lm_locks, best done with Oracle support's approval because they're underscore parameters. In addition, I would further raise shared_pool_size (and squeeze down buffer cache). Users will be more unhappy to get ORA-4031 than get slightly lower response because the data is no longer in buffer cache.

Yong Huang

mdkii

原帖由 Yong Huang 于 2011-4-3 05:28 发表


I'm sorry. Somehow I was thinking of 5 instances of a RAC database. A big mistake! Indeed you have 5 subpools and the total size is about what you said.

Since memory is evenly distributed and used by your subpools, reducing subpools is not the best solution, even though it should be able to merge some small chunks into bigger ones. Your biggest shared pool consumers are gcs resources and gcs shadows. According to Bug 6614853, you can

--- begin quote ---
execute following query
    select * from v$resource_limit
     where resource_name like 'gcs%' or resource_name like 'ges%'

  And compare max_utilization and initial_allocations.
  If max_utilization is very large compared to initial_allocations
  for ges_ress/ges_locks, they can consider to set _lm_ress/_lm_locks
  parameter to allocate spaces at database startup time.
--- end quote ---

Other settings that can save shared pool usage will turn off various database advisories. I always set db_cache_advice to off on all databases I manage (I never check v$db_cache_advice). Several others are controlled by underscore parameters, such as _object_statistics and _library_cache_advice (Ref: Bug 8561410). Implications of setting them can be found in
http://yong321.freeshell.org/com ... cyAndStatistics.doc

Anyway, I would check v$resource_limit for 'gcs%' resources and possibly lower _lm_ress/_lm_locks, best done with Oracle support's approval because they're underscore parameters. In addition, I would further raise shared_pool_size (and squeeze down buffer cache). Users will be more unhappy to get ORA-4031 than get slightly lower response because the data is no longer in buffer cache.

Yong Huang

多么专业的回答：）