 Posted 6/15/2010 9:31:10 AM
|
|
|
|
ora error code ORA-12853: | insufficient memory for PX buffers: current stringK, max needed stringK | | Cause: | Insufficient SGA memory for PX buffers
| | Action: | Reconfigure sga to include at least (max - current) bytes of additional memory
|
|
|
|
Posted 6/15/2010 9:31:44 AM
|
|
|
|
I am getting the following error during oracle 11g loading. One of the consultant adviced us to set the large_pool_size or increase the SGA size. I am not sure that either of them will work. kindly help because oracle 11g is using automatic memory manager so we dont need to set large pool making it manual. our dba is of this opinion while the consultant is of the opinion that its better to set the large_pool_size since parallel execution have to allocate and deallocate memory from the share pool which under huge loads can fragment the sga or sga have to shrink the other pools to make room for big memory allocation. While our DBA is more of the opinion that since asmm have allocated memory from the share pool we dont need to worry about it.
who is right?
ORA-12801: error signaled in parallel query server P304, instance dwhrac2:ZAINNGDW2 (2)
ORA-12853: insufficient memory for PX buffers: current 183524K, max needed 2167200K
ORA-04031: unable to allocate 16072 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")
|
|
|
Posted 6/15/2010 9:32:45 AM
|
|
|
|
ORA-12853: insufficient memory for PX buffers: current stringK, max needed string
Cause: Insufficient SGA memory for PX buffers
Action: Reconfigure sga to include at least (max - current) bytes of additional memory
As you may know, when using parallel query, the RAM caching is done from the PGA.
Oracle fires off parallel factotum processes, and each slave simultaneously reads a piece of the large table using PGA RAM.
But in your case, it looks like you want to increase your large_pool_size.
******************************************************
You may have exceed the default limits imposed by Oracle. For example, PC-based Oracle servers (1 or 2 CPU’s with 8 gigabytes of RAM) will often have unused RAM available. For example, a fully-cached 5 gigabyte database on an 8 gigabyte dedicated Oracle server will have approximately 1 gigabyte available for the PGA (allowing 20% for the OS and other SGA regions).
By re-setting pga_aggregate_target = 1000m, _pga_max_size = 1000m and _smm_px_max_size = 333m, parallel queries may now have up to 330 megabytes of RAM (30% of pga_aggegate_target), such that a DEGREE=4 parallel query would have 83 megabytes (333 meg/4).
Hope this helps. . .
|
|
|
Posted 6/15/2010 9:33:09 AM
|
|
|
|
Setting LARGE_POOL_SIZE to a non-zero value will ensure that the large pool always gets at least this amount of memory. To quote from the 11g docs
If these automatically tuned memory pools had been set to nonzero values, those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.
That said your query needs to allocate 2167200K of memory for parallel execution to work, that's a little over 2gb. Whilst that may be appropriate - say for a multiGB or TB table , it may well also be a sign that this particular query is accessing data very inefficiently (and then of course doing it in a parallel manner just to add to the fun). It would be interesting to see the statement and corresponding explain plan for the query giving this error.
|
|
|
Posted 6/15/2010 9:34:10 AM
|
|
|
|
ORA-12853: insufficient memory for PX buffers: current stringK, max needed stringK
Cause: Insufficient SGA memory for PX buffers
Action: Reconfigure sga to include at least (max - current) bytes of additional memory
-----------------------------------------------------------------
If parallel_automatic_tuning = TRUE than we get RAM from the large pool, else we use the shared pool.
"Is there a way to calculate how much shared pool should be alloctaed?"
It would be a function of your maximum degree of parallelism.
If you hit the error with 1 gig with say 15 parallel processes, you could guesstimate that 2 gig would handle 30 PX servers.
What is the degree of parallelism?
There are many good reasons to only use parallelism at the hint level only. It influences the CBO to favour full scans.
|
|
|
Posted 6/15/2010 9:34:43 AM
|
|
|
|
After some troubleshooting, I discovered that my SGA_TARGET parameter is causing the issue. Initially I set SGA as:SGA_TARGET=12288M
However, Oracle did not take the value and threw ORA-12853 After some troubleshooting, I changed the value to 12000M and Oracle starts up without issues.
Therefore, if you encounter ORA-12853 error, try to adjust your SGA_TARGET parameter.
|
|
|
Posted 6/15/2010 9:36:38 AM
|
|
|
|
Sorry if this is repeat qustion, but I wanted to clear some doubt about shared pool.
ORA-12012: error on auto execute of job 636
ORA-20667: ORA-20667: ORA-20667: ORA-20667: ORA-20667: ORA-20667: ORA-12801: error signaled in parallel query server P008
ORA-12853: insufficient memory for PX buffers: current 29056K, max needed 208896K
ORA-04031: unable to allocate 131096 bytes of shared memory ("shared pool","unknown object","sga heap(2,0)","PX msg pool")
So many times I have seen this kind of error and first thing I did is to flush the shared pool, so many times I have to bounce the DB also,
4031 coming for parallel query or some times for other sql queries.
I wanted to know about this error and how to set Shared Pool size.
ORA-04031: unable to allocate 131096 bytes of shared memory ("shared pool","unknown object","sga heap(2,0)","PX msg pool")
1. for eg take this phrase "unable to allocate 131096 bytes of shared memory" this bytes always change .. why ?
2.("shared pool","unknown object","sga heap(2,0)","PX msg pool") this mesg also change. how to find out porper guideline to understand the issue?one more thing when I got this mesg. shared pool free memory was good enough
POOL NAME BYTES
------------ ------------------------------ --------------
shared pool sql area 15,193,092
shared pool free memory 112,852,804
shared pool library cache 23,860,180
large pool free memory 270,651,600
large pool session heap 266,219,312
|
|
|
Posted 6/15/2010 9:37:15 AM
|
|
|
|
I was able to solve this issue, I changed query from parallel 16 to parallel 4,
|
|
|
|