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

Alert logs

Errors in file /u01/rac/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_2866.trc  (incident=206281):
ORA-04031: unable to allocate 2048024 bytes of shared memory (“large pool”,”unknown object”,”large pool”,”PX msg pool”)

Starting SQL statement dump
SQL Information
user_id=0 user_name=SYS module=DBMS_SCHEDULER action=ORA$AT_OS_OPT_SY_5283
sql_id=f6dmkk7mj5s3x plan_hash_value=1001864177 problem_type=4
—– Current SQL Statement for this session (sql_id=f6dmkk7mj5s3x) —–
select min(minbkt),maxbkt,substrb(dump(min(val),16,0,32),1,120) minval,substrb(dump(max(val),16,0,32),1,120) maxval,sum(rep) sumrep, sum(repsq) sumrepsq, max(rep) maxrep, count(*) bktndv, sum(case when rep=1 then 1 else 0 end) unqrep from (select val,min(bkt) minbkt, max(bkt) maxbkt, count(val) rep, count(val)*count(val) repsq from (select /*+ no_expand_table(t) index_rs(t)   parallel(t,64) parallel_index(t,64) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad  */substrb(“SUBSCRIBERID”,1,32) val, ntile(254) over (order by nlssort(substrb(“SUBSCRIBERID”,1,32),’NLS_SORT = binary’)) bkt  from “SCOTT”.”PROVHISTORY” sample (   .4044129542)  t  where substrb(“SUBSCRIBERID”,1,32) is not null) group by val) group by maxbkt order by maxbkt
sql_text_length=801
sql=select min(minbkt),maxbkt,substrb(dump(min(val),16,0,32),1,120) minval,substrb(dump(max(val),16,0,32),1,120) maxval,sum(rep) sumrep, sum(repsq) sumrepsq, max(rep) maxrep, count(*) bktndv, sum(case when rep=1 then 1 else 0 end) unqrep from (select val,min(b
sql=kt) minbkt, max(bkt) maxbkt, count(val) rep, count(val)*count(val) repsq from (select /*+ no_expand_table(t) index_rs(t)   parallel(t,64) parallel_index(t,64) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_p
sql=ad  */substrb(“SUBSCRIBERID”,1,32) val, ntile(254) over (order by nlssort(substrb(“SUBSCRIBERID”,1,32),’NLS_SORT = binary’)) bkt  from “SCOTT”.”PROVHISTORY” sample (   .4044129542)  t  where substrb(“SUBSCRIBERID”,1,32) is not null) group by val)
sql= group by maxbkt order by maxbkt
Compilation Environment Dump

After analysis production scenario

  • Table contain around 200000 Lake records
  • Table had hashing 8 partitions
  • Table had local index stored in separate single tablespace
  • Table had enabled PARALLEL configuration

Work around-1

Truncate table PROVHISTORY reuse storage;

ALTER TABLE PROVHISTORY NOPARALLEL;

since last 5 days there is no any memory error ORA-04031 so problem got resolved.

Work around-2-permanent

I come to know that memory resize operation getting failed or cancelled sometimes

set pagesize 10000
set linesize 1000
SELECT to_char(start_time,'DD-MON-RRRR HH24:MI:SS'),
to_char(end_time,'DD-MON-RRRR HH24:MI:SS'),
component,
oper_type,
oper_mode,
parameter,
ROUND(initial_size/1024/1204) AS initial_size_mb,
ROUND(target_size/1024/1204) AS target_size_mb,
ROUND(final_size/1024/1204) AS final_size_mb,
status
FROM   v$memory_resize_ops
WHERE status != 'COMPLETE'
ORDER BY start_time;

http://divakarmehta.wordpress.com/2013/01/19/sga-memory-pool-space-usage-queries/

http://dbakevin.blogspot.in/2012/06/04031-and-large-pool-parallel-and-px.html

Multiple ORA-4031 Errors Of Reducing Sizes For “PX msg pool” In The Large Pool (Doc ID 1515877.1)
http://oradbastuff.blogspot.in/2014/02/4031-px-msg-pool.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s