error in UpdateStats job

[Former Member]

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

[former_member227600 · Accepted solution]

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]

[Former Member · Accepted solution]

Thanks Karan,

It is Ok in my system.

I have Oracle 9.2.0.4.0 & SHARED_POOL_SIZE= 771751936.

plz suggest, what should i do?

Ankit

[Former Member · Accepted solution]

Hai,

You have to follow the steps as mentioned in the SAP Note 690241.

1. Reduce the memory consumption or

2. Increase the size of the shared pool.

Regards,

Yoganand.V

[Former Member · Accepted solution]

Dear Yoganand,

How can i check memory consumption ?

How can i reduce memory consumption ?

Ankit

[Former Member]

thanks to all of u

i have not done anything.

[Former Member]

Hi Ankit,

Have a look at SAP Note 706132.

Regards,

Sachin Rane.

[Former Member]

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.

[Former Member]

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

[Former Member]

Dear all,

After that it is completed successfully daily. Till now i didnt make any changes in parameters or anywheer. Should i make changes now ?

Ankit

[Former Member]

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

[fidel_vales]

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