on 2009 Feb 04 6:30 AM
Dear All,
I have got this error in UpdateStats job.
BR0883I Table selected to collect statistics after check: SAPPRD.VBAP (102478/153942)
BR0280I BRCONNECT time stamp: 2009-02-04 01.21.11
BR0881I Collecting statistics for table SAPPRD.VBAP with method/sample E/P30 ...
BR0280I BRCONNECT time stamp: 2009-02-04 01.21.26
BR0301E SQL error -4031 at location stats_tab_collect-16
ORA-04031: unable to allocate 4160 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","state objects")
BR0886E Checking/collecting statistics failed for table SAPPRD.VBAP
BR0280I BRCONNECT time stamp: 2009-02-04 01.21.28
Plz suggest what i have to do. Is it a serious problem.
Ankit
Hi,
ORA-04031: unable to allocate 4160 bytes of shared memory
These problem seems related to shared memory. Please refer to Snote 690241. Click on below link ,you will get the solution of your problem.
[https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/sapnotes/index2.htm?numm=690241]
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
thanks to all of u
i have not done anything.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Ankit,
Have a look at SAP Note 706132.
Regards,
Sachin Rane.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Fidel,
Don't know what you are trying to mean by "copy/paste" reply. Both the thread have the same error ORA-04031 that is why the answer is same. Anyway I've answered all your query in the previous thread.
SK
OCP DBA.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Dear all,
Thanks for your suggestions. This update stats job is scheduled daily in my system daily in night but the error occured only once on 4th day of this month. After that it is compleetd successfully daily. Till now i didnt make any changes in parameters or anywheer. Should i make changes now ?
Ankit
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Subhadip Kumar,
Please, take a look at [this thread|; for comments about that "copy/paste" reply.
@Ankit Gupta
1) 9.2.0.4 is very old, very buggy and oracle ended its customer care support. If you need to be in 9i, install the latest patch set and all related patches in SAP Service Marketplace.
2) it may be that your shared pol is too small
3) take a look at SAP Note 869006 - Composite SAP note: ORA-04031 point 5
I will not enter in:
> How can i check memory consumption ?
> How can i reduce memory consumption ?
If you do not know this, then you should not be administering oracle.
If you are not administering oracle, then talk to the DBA
User | Count |
---|---|
83 | |
12 | |
10 | |
10 | |
10 | |
9 | |
8 | |
7 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.