cancel
Showing results for 
Search instead for 
Did you mean: 

ORA-12853 and ORA-04031 simultaneously

former_member759680
Contributor
0 Kudos

Hello,

I am getting the following errors when I try to start Oracle

ORA-12853: insufficient memory for PX buffers: current 0K, max needed 2640K

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

My init.ora file is as follows -

______________________________________________________________________________

EDA.__db_cache_size=1174405120

EDA.__java_pool_size=33554432

EDA.__large_pool_size=0

EDA.__shared_pool_size=1174405120

EDA.__streams_pool_size=0

*._OPTIM_PEEK_USER_BINDS=FALSE

*.background_dump_dest='/oracle/EDA/saptrace/background'

*.compatible='10.2.0'

*.control_file_record_keep_time=30

*.control_files='/oracle/EDA/origlogA/cntrl/cntlrEDA.dbf','/oracle/EDA/origlogB/cntrl/cntrlEDA.dbf','/oracle/EDA/sapdata1/cntrl/cntrlEDA.dbf'

*.core_dump_dest='/oracle/EDA/saptrace/background'

*.db_block_size=8192

*.db_cache_size=1159641169

*.db_files=254

*.db_name='EDA'

*.dml_locks=4000

*.event='10191 trace name context forever, level 1'

*.FILESYSTEMIO_OPTIONS='setall'

*.job_queue_processes=1

*.log_archive_dest='/oracle/EDA/oraarch/EDAarch'

*.log_buffer=1048576

*.log_checkpoint_interval=0

*.log_checkpoints_to_alert=true

*.open_cursors=800

*.optimizer_features_enable='10.2.0.1'

*.pga_aggregate_target=1546188226

*.processes=80

*.recyclebin='off'

*.remote_login_passwordfile='exclusive'

*.remote_os_authent=true

*.sessions=96

*.sga_max_size=2319282339

*.sga_target=2399141888

*.shared_pool_reserved_size=115964116

*.shared_pool_size=1159641169

*.sort_area_retained_size=0

*.sort_area_size=2097152

*.statistics_level='typical'

*.undo_management='AUTO'

*.undo_retention=43200

*.undo_tablespace='PSAPUNDO'

*.user_dump_dest='/oracle/EDA/saptrace/usertrace'

*.workarea_size_policy='AUTO'

_______________________________________________________________________________

According to me everything looks fine. Kindly help me sort this error.

Thanks in advance.

Accepted Solutions (1)

Accepted Solutions (1)

former_member204746
Active Contributor
0 Kudos

it's about time you review SAP note 830576.

you will see, amongst other things, that shared_pool_size must be set to at least 400MB.

Answers (2)

Answers (2)

0 Kudos

>

> According to me everything looks fine. Kindly help me sort this error.

Buff, clearly you have not taken a look to the SAP recommendation about the parameters, because I think I do not have enough fingers to point all missing parameters.

I'd say that the sga_target is the cause of your issues. This parameter automatically resize the SGA elements (the double underscore parameters you see).

Second, the error indicate that there is no space on the large pool.

What is your size for the large pool?

> EDA.__large_pool_size=0

so, I'd start deleting all parameters related to the ASSM (sga_target and double underscore) or set the large pool to a non 0 value. If I'm not wrong, this would set the lower limit for it (at least it does so for the shared pool and buffer pool)

doing that your DB should start.

then, follow the note mentioned by Eric and set properly ALL the database parameters. You can help yourself by using the script from note 1171650

former_member759680
Contributor
0 Kudos

Thanks you guys, that provides a lot of clarity.

Just one last doubt, large_pool_size, whats the difference between setting it to 1 or say 1000.

How would the value impact my database?

Thanks.

Former Member
0 Kudos

Hi Gautam ..

The database administrator can configure an optional memory area called the large pool to provide large memory allocations for:

Session memory for the shared server and the Oracle XA interface (used where transactions interact with more than one database)

I/O server processes

Oracle backup and restore operations

Parallel execution message buffers, if the initialization parameter PARALLEL_AUTOMATIC_TUNING is set to true (otherwise, these buffers are allocated to the shared pool)

By allocating session memory from the large pool for shared server, Oracle XA, or parallel query buffers, Oracle can use the shared pool primarily for caching shared SQL and avoid the performance overhead caused by shrinking the shared SQL cache.

In addition, the memory for Oracle backup and restore operations, for I/O server processes, and for parallel buffers is allocated in buffers of a few hundred kilobytes.

The large pool is better able to satisfy such large memory requests than the shared pool.

Rishi

former_member759680
Contributor
0 Kudos

Ok, let me rephrase, if I want to set the parameter large_pool_size , what non-zero value is ideal or how do I calculate the value that i should set?

Former Member
0 Kudos

1. Play with Shared Pool....

a. The following query determines the available memory for SHARED_POOL_SIZE in Oracle sga

select sum (bytes)/1024/1024 from v$sgastat where pool=u2019shared poolu2019

b. The following query determines the total used memory by shared_Pool in Oracle SGA.

select sum (bytes)/1024/1024 from v$sgastat where pool=u2019shared poolu2019 and name not in (u2019free memoryu2019)

c. This is the most important query

select

sum(a.bytes)/(1024*1024))shared_pool_used,

max(b.value)/(1024*1024) shared_pool_size,

sum(a.bytes)/(1024*1024))-

(sum(a.bytes)/(1024*1024)) shared_pool_avail,

((sum(a.bytes)/(10241024))/(max(b.value)/(10241024)))*100

pct_shared_pool_avl

from v$sgastat a, v$parameter b

where (a.pool=u2019shared poolu2019

and a.name not in (u2019free memoryu2019))

and

b.name=u2019shared_pool_sizeu2019

You need to continously monitor the shared Pool with the above query at differnet times. During Peak times and Non peak times to have glance of shared pool usage in the Oracle database.

if the available pct_shared_pool_avl crosses 95% then i think you should re-consider the Process of increasing the shared_pool_size.

2. There are many way to improve Shared Pool performance.

a. Ask ABAPers to write more generic and reusablecode.

b. Using of right block size.

c. Proper design of the database.

Comment on ORA-04031 :

This error should not appear in any of the application logs, the alert log or any trace files. Do not depend on ORA-04031 errors being written to the alert log, as 4031 errors only appear in the alert log if they affect background process operations (such as PMON activities). 4031u2019s are not internal errors and so could be trapped and handled by the application (this is not recommended).

From 10gR1 onwards, a 4031 trace file is written to the user_dump_dest (or background_dump_dest) directory; this trace file is useful in diagnosing the nature of problem

Hope this will help you.

Regards,

SK

OCP DBA -9i,10g

0 Kudos

>

> Ok, let me rephrase, if I want to set the parameter large_pool_size , what non-zero value is ideal or how do I calculate the value that i should set?

First, by default SAP does not uses the large_pool_size. the default value is "generally" enough.

This pool is needed if you are using shared servers (not in SAP), by parallel executions (if PARALLEL_AUTOMATIC_TUNING is set (default is not set) and, finally, by RMAN.

If you are not using RMAN, then do not set this parameter.

Regarding the information from Subhadip Kumar.

Very nice, but not very useful. (do not take it personally)

> c. This is the most important query

there is a "little" problem for this query. From Oracle 9i, Oracle has divided the shared pool in subpools (the number of subpools depend on the number of CPUs), each subpool has a specific size.

You can have a lot of "free" memory in the shared pool and get a 4031 because one of the subpools (the one tat oracle wants to use for the statement that caused the error) does not have enough memory.

This situation is not contemplated in your query.

I'm not aware that any query that can give you the information from the subpools. May be any reader can provide more information.

For more information about the subpools you can take a look at the [SAP Note 690241 - The Shared SQL Area in the Shared Pool and large SQL Stmnts|http://service.sap.com/sap/support/notes/ 690241]

Regarding the other points, I do not think there is much that can be done with

> a. Ask ABAPers to write more generic and reusablecode.

> b. Using of right block size.

> c. Proper design of the database.

Code comes mainly from SAP (unless you are using ZAP), if you find "bad" code, a message should be open to inform SAP about it.

block size is 8k in SAP, you should not change it

The database is already designed, again, no much to do

Former Member
0 Kudos

Unless Standard code nothing comes directly from SAP, all Z programs must be written by ABAP-ers and if the code is consuming more memory for Z programs then raising message with SAP will not help you rather to check the code for further improvement.

Proper allocation of memory facilitate good designing of database. You have to allocate memory properly for the shared pool, large pool etc.

Former Member
0 Kudos

Hi,

ORA-12853:Reconfigure sga to include at least (max - current) bytes of additional memory .

ORA-04031:If the shared pool is out of memory, either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the INIT.ORA parameters "shared_pool_reserved_size" and "shared_pool_size". If the large pool is out of memory, increase the INIT.ORA parameter "large_pool_size".

Rgds,

Suman