04031 and large pool, parallel and PX msg pool

Parallelism require "PX msg pool". In past, "PX msg pool" are allocate from shared_pool:

SYS @ test > select * from v$sgastat where  upper(name) like '%PX%';
POOL         NAME                                                    BYTES
------------ -------------------------------------------------- ----------
shared pool  PX subheap desc                                           256
shared pool  PX msg pool struct                                         56
shared pool  PX QC deq stats                                          1696
shared pool  ksu: ksusg_emptypxsta                                    5200
shared pool  PX server deq stats                                      1696
shared pool  PX list of chunk lists                                   1088
shared pool  PX subheap                                              66792
shared pool  PX QC msg stats                                          2904
shared pool  PX server msg stats                                      2904
shared pool  PX msg pool                                           8192000

Since 10g, if Automatic Shared Memory Management(ASMM) is enabled, the "PX msg pool" will be allocated from large_pool:

SYS @ SYS @ test > select * from v$sgastat where name like '%PX%';
POOL         NAME                                                    BYTES
------------ -------------------------------------------------- ----------
shared pool  PX subheap desc                                           256
shared pool  PX msg pool struct                                         56
shared pool  PX QC deq stats                                          1696
shared pool  PX server deq stats                                      1696
shared pool  PX list of chunk lists                                   1088
shared pool  PX subheap                                             757656
shared pool  PX QC msg stats                                          2904
shared pool  PX server msg stats                                      2904
large pool   PX msg pool                                           8088608

Ignore those names starting with PX in shared_pool, most of they are only some kind of descriptions for "PX msg pool". For example, "PX msg pool struct" describle the structure of "PX msg pool". "PX server msg stats" records statistics afor "PX msg pool".

Recently We hit the error 04031 in large_pool:

ORA-04031: unable to allocate 2048024 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")

Obviously it is because insufficient space in large pool.
Current size for large pool is 16m.This size fit most cases, for example, parallel 10.
But in our case the issue SQL's parallel is 20:

*** 2012-06-20 22:11:37.731
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=5a10rbj2tx6j4) -----
select substrb(dump(val,16,0,32),1,120) ep, cnt from (select /*+ no_expand_table(t) index_rs(t) parallel(t,20) parallel_index(t,20) dbms_stats cursor_shari
ng_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */max(substrb("BUSINXXXMENT",1,32)) val,count(*) cnt from "XXXAVETH"."XXX_EXT" t where substrb("BUSINXXXMENT",1,32) is not null group by nlssort(substrb("BUSINXXXMENT",1,32), 'NLS_SORT = binary')) order by nlssor
t(val,'NLS_SORT = binary')

If the SGA have enough free memory, this is not a issue, because large_pool=16m only define an lower bound of large_pool. When large_pool is lack of space, ASMM will try to allocate some space for it:

SYS @ test > select * from v$sgastat where POOL ='large pool';
POOL         NAME                                               BYTES
------------ -------------------------------------------------- ----------
large pool   PX msg pool                                        8088608
large pool   free memory                                        300000
<---- here from another session i run one SQL with parallel 20：--->

SYS @ test > /
POOL         NAME                                               BYTES
------------ -------------------------------------------------- ----------
large pool   PX msg pool                                        26287976
large pool   free memory                                        2883640
large pool   KGH: NO ACCESS                                     188512

We can see large pool extends automaticlly. Also notice: "KGH: NO ACCESS", it means ASMM retrive some space from large_pool and offer to db_cache.But in our case, the SGA already reached its sga_max_size, so there is no extra space to offer to large_pool.

The issue SQL is coming from a daily gather stats job:

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'XXXVETH',estimate_percent=>20,block_sample=>false,method_opt=>'For all columns size 225',GRANULARITY=>'ALL',degree=>20,cascade=>true);

To fix the issue, we can either:
1. increase large pool size.
2. reduce degree in gather stats job.