Intermittant ORA-4031 errors

[Chucks_k]

Hi all,

We get intermittant ORA-4031 errors when running some Java code with sql via the Oracle's thin JDBC client:

java.sql.SQLException: ORA-04031: unable to allocate 52480 bytes of shared memory ("shared pool","unknown object","library cache","kslcr - unsafe positions")

I am pretty sure its due to undersized structure. But i am not sure whether to increase the shared pool or the java pool. The shared pool is sized to 33554432 (33M). Or is it a bug?. We have 9.2.0.1.0 installed.


Any suggestions?

Thanks in advance,
Chucks_k

[davey23uk]

are you using bind variables - number 1 cause for shared pool errors

[Chucks_k]

Hi there,

No bind vars are being used (not applicable for these queries). These are the 3 sql statements:

> SELECT min_page_req_time,avg_page_req_time,max_page_req_time
> FROM page_requests_summary
> WHERE timestamp>=TO_DATE('00-00-0000 00:00','dd-mm-yyyy hh24:mi:ss')
> AND timestamp<=TO_DATE('00-00-0000 23:59','dd-mm-yyyy hh24:mi:ss')
> AND page_req_hour=24
> AND store_id IS NULL
>
> SELECT session_hour,session_count
> FROM sessions_summary
> WHERE TO_CHAR(session_date,'dd-mm-yyyy hh24:mi:ss')='01-01-1001 01:01:01'
> ORDER BY session_hour
>
> SELECT to_char(orderdate,'HH24'), count(*)
> FROM STORE_ORDER
> WHERE orderdate >= to_date('09-11-2004 00:00:00','DD-MM-YYYY HH24:MI:SS')
> AND orderdate <= to_date('10-11-2004 00:00:00','DD-MM-YYYY HH24:MI:SS')
> GROUP BY to_char(orderdate,'HH24')

I imagine these are parsed/executed from the shared pool?

Thanks,
Chucks

[Chucks_k]

* Error on the last note..Last line should read 'executed/parsed afresh from the shared pool'

Thanks,
Chucks

[marist89]

No bind vars are being used (not applicable for these queries). These are the 3 sql statements:

You only run three queries a day? My guess you think only these three queries are being run, but there are hundreds of copies in the shared pool.

[Chucks_k]

Hi Jeff,

No there are plenty of queries being run, but these 3 queries are part of a job to extract data from the db every morning. The job fails with the error i gave earlier, so dont know exactly which out of the 3 throws the error. Our shared pool is sized to 30m and the shared pool reserved size is set to a 1m. I am considering changing these to 50M and 5M respectively.