on 08-07-2010 4:25 PM
Hi,
We are getting some oracle error in SM21, this arrise at the time of some background job execution.
Background job finish succesfully, but while during the period of execution we are getting this error continues.
Please find the below error.
Database error 12801at EXE
ORA-12801: error signalled in parallel query server.
P000#ORA-04031: Unable to allocate 1518024 bytes of shared Memory
("shared pool","Create bitmap index)
We have total 5 GB memory on server and when i check in ST06 it shows me as 7 MB is only free.
SGA & PGA value is as below
sga_max_size=695897948
shared_pool_reserved_size=34794897
shared_pool_size=347948974
pga_aggregate_target=463931965
We are using oracle 9.2.0.6.
Pls suggest.
Thanks
Anil
Read SAP note 830576 and adapt shared_pool_size as recommended.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks problem resolved.
i have changed my parameter SHARED_POOL_SIZE
Thanks
Anil
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Anil,
Have a look at below SAPnote specifically for your issue on a BI system
Note 1472386 - ORA-04031 out of memory error in BW systems
Also, for the recommended size of the shared pool for a BW system, see Notes 830576 and 690241. The values specified there are lower guide values, and you may have to adjust the size of the shared pool to the actual requirement of the database.
All the best !
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I see errors like Yours if the database have too little space for SGA.
Consider use of Automatic Shared Memory Management (ASMM) using sga_target and sga_max_size instead. In this way the database would size pools to the size it need. Most of our databases ends up with a shared_pool_size around 650M.
set sga_target
keep sga_max_size >= sga_target.
unset shared_pool_size
unset large_pool_Size
unset java_pool_size
unset db_cache_size
Regards Audun
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Inserted dots
>
sga_max_size=695.897.948
> shared_pool_reserved_size=34.794.897
> shared_pool_size=347.948.974
> pga_aggregate_target=463.931.965
Hi,
this values look way too low. Check the parameter Notes in SAPNET.
shared_pool should be 450M at minimum.
As this looks to be a BW system (nobody else creates Bitmap-Indexes),
I'd rather go for 700 MB shared_pool (all those nice ODS joins come without
bindvariables to support histograms, so each execution -> new parsing),
and at least 800M-1G of PGA. (The notes suggestes 20% of RAM for PGA,
but I rarely saw that utilized, normally 15% will do it. Monitor and increase if not enough)
To have SGA_MAX limited to < 700MB on a 5GB machine does not make much sense.
This suggests your db_cache_size can barely be more the 350M as well.
Should be at 1G up to 1,5G minimum.
May be you need to downsize your SAP-Instance to get back some phys RAM?
Volker
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.