Home » RDBMS Server » Performance Tuning » ora - 04031 (Oracle, 10.2.0.4)
ora - 04031 [message #545152] Mon, 27 February 2012 10:17 Go to next message
savugar
Messages: 33
Registered: February 2012
Member
I am executing the attached procedure to drop the empty subpartitions from 10 partitioned tables.each partitioned table has more than 15000 subpartitions

While dropping the sub partitions, I got the below error.

ORA-04031: unable to allocate 4128 bytes of shared memory
 (shared pool","NK_ERPRFX_CHARGETYPEKEY_XOLAP","mvobj part des","Allocate kctph[]/ckyph[]/ckyprt[] array") 
EDX_RPT_PYMNT_REFUND_FACT_X"


I think I am getting this error because of high number of parsing because it needs to fetch the count of each partitions.Since each subpartition name differs, every time the select count(1) will be getting parsed. I think this is the reason for the error. Pls correct me if I am wrong.

pls tell me the way to drop the subpartitions without getting this error except increasing the shared pool size.

 -- Loop For all subpartitions for each part_table
               FOR j IN lc_subpart_arr.FIRST .. lc_subpart_arr.LAST
               LOOP
                  v_count := 999;

                  v_sql1 :=
                        'SELECT COUNT(1) FROM '
                     || v_part_tab
                     || ' SUBPARTITION ('
                     || lc_subpart_arr (j)
                     || ')';

                  EXECUTE IMMEDIATE (v_sql1)
                               INTO v_count;
                  --  DBMS_OUTPUT.put_line ('Vcount- ' || v_count);

                  IF v_count = 0
                  THEN
                     v_sql :=
                           'ALTER TABLE '
                        || v_part_tab
                        || ' DROP SUBPARTITION '
                        || lc_subpart_arr (j);

                     BEGIN
                        EXECUTE IMMEDIATE (v_sql);
                        --DBMS_OUTPUT.put_line (v_sql);
                        v_subpart_cnt := v_subpart_cnt +1;



SQL> select * from GV$SGAINFO order by inst_id;

   INST_ID NAME                                  BYTES RES
---------- -------------------------------- ---------- ---
         1 Free SGA Memory Available                 0
         1 Startup overhead in Shared Pool   721420288 No
         1 Maximum SGA Size                 2.2263E+10 No
         1 Granule Size                       16777216 No
         1 Streams Pool Size                  16777216 Yes
         1 Fixed SGA Size                      2123872 No
         1 Large Pool Size                   268435456 Yes
         1 Shared Pool Size                 7868514304 Yes
         1 Buffer Cache Size                1.3959E+10 Yes
         1 Redo Buffers                       14651392 No
         1 Java Pool Size                    134217728 Yes
         2 Startup overhead in Shared Pool   721420288 No
         2 Free SGA Memory Available                 0
         2 Maximum SGA Size                 2.2263E+10 No
         2 Granule Size                       16777216 No
         2 Streams Pool Size                  16777216 Yes
         2 Java Pool Size                    134217728 Yes
         2 Large Pool Size                   268435456 Yes
         2 Shared Pool Size                 7868514304 Yes
         2 Buffer Cache Size                1.3959E+10 Yes
         2 Redo Buffers                       14651392 No
         2 Fixed SGA Size                      2123872 No
         3 Fixed SGA Size                      2123872 No
         3 Redo Buffers                       14651392 No
         3 Buffer Cache Size                1.3959E+10 Yes
         3 Shared Pool Size                 7868514304 Yes
         3 Large Pool Size                   268435456 Yes
         3 Free SGA Memory Available                 0
         3 Streams Pool Size                  16777216 Yes
         3 Granule Size                       16777216 No
         3 Maximum SGA Size                 2.2263E+10 No
         3 Startup overhead in Shared Pool   721420288 No
         3 Java Pool Size                    134217728 Yes


SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 21232M
sga_target                           big integer 0

[Updated on: Mon, 27 February 2012 11:53] by Moderator

Report message to a moderator

Re: ora - 04031 [message #545156 is a reply to message #545152] Mon, 27 February 2012 10:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
08:57:30 SQL> select * from V$SGA_TARGET_ADVICE;

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
       488		.5	    231 		  1		  22121
       732	       .75	    231 		  1		  22121
       976		 1	    231 		  1		  22121
      1220	      1.25	    231 		  1		  22121
      1464	       1.5	    231 		  1		  22121
      1708	      1.75	    231 		  1		  22121
      1952		 2	    231 		  1		  22121

7 rows selected.
Re: ora - 04031 [message #545334 is a reply to message #545156] Tue, 28 February 2012 08:17 Go to previous messageGo to next message
savugar
Messages: 33
Registered: February 2012
Member
I am getting "no rows selected" from V$SGA_TARGET_ADVICE.
Re: ora - 04031 [message #545335 is a reply to message #545334] Tue, 28 February 2012 08:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
which edition of V10 is running?

SELECT * FROM V$VERSION;
Re: ora - 04031 [message #545357 is a reply to message #545335] Tue, 28 February 2012 14:44 Go to previous messageGo to next message
savugar
Messages: 33
Registered: February 2012
Member
10.2.0.4.

SGA_TARGET = 0 in this database. for some reason, they disabled the automatic memory management feature.
Re: ora - 04031 [message #545358 is a reply to message #545357] Tue, 28 February 2012 15:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>10.2.0.4.

Thank You for not doing as requested.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: ora - 04031 [message #550027 is a reply to message #545152] Thu, 05 April 2012 05:59 Go to previous message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
It looks like the bug 9578670. Try the workaround "_kghdsidx_count"=1 (i.e. with only one subpool of shared pool).
Previous Topic: session doing FTS not appearing in longops
Next Topic: Oracle 11g alternates between two execution plans
Goto Forum:
  


Current Time: Thu Mar 28 10:14:59 CDT 2024