Intermittant ORA-4031 errors

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

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

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

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

Thanks,
Chucks

Quote:

---

Originally Posted by Chucks_k

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.

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.