ORA-1000 suddenly cropping up

[uparna]

Hi !

Here i am again.

From the past few weeks , the DBA team who monitor our production database have been on my back regarding the ORA-1000 error. They say that there are a few sessions that have more than 999 cursors open. While i didnt take much notice of it , i was alarmed to see that 90-100% of Shared Pool is used nowadays full time , because of these open cursors! This is causing the database performance to go down. We have done no code change in the recent past and i would like your help in assisting me with this problem.

The DBA team mentions that the procedures do not have "close" cursor statements , hence are leading to this predicament. My answer is this : every single place that a cursor is used in the procedures , it is used in a FOR loop , and hence there is no requirement for me to OPEN ,FETCH or CLOSE. I even provided them with Oracle documentation on the same and they refuse to believe it citing 'perhaps Oracle never tested these(cursor FOR loops) through Java'. What do i say?
Is it true that even if i use the Cursors in FOR loops i have to go ahead and close them explicitly? i have more than 3-400 procedures and all of them use cursors left-right and center.... so i am consulting you all before going gung-ho on the changes.

Here is our system architecture: we have a Java front end on SunOne , calling procedures from an Oracle 9.2.0.4 db. All the business logic is there in the procedures and the front end just displays the data. Are there any known problems in Oracle 9i that result in ORA-1000s ? In fact , the DBA team mentioned that they have put a security patch on the prod db, on a date which suspiciously coincides with the date from which we are experiancing performance problems (they are refusing to see the light and are calling it a coincidence). Is there hope?


Regards,
S. Jayaram Uparna .

 

[SantaMufasa]

Jayaram,

Do you have an Oracle-Support agreement? If so, this is the time to use it. There may be one or more bugs associated with your environment (including the coincidental security patch). Oracle has the definite list of known bugs. They can search the bug database for your situation and even if they do not identify any bugs, they can/should help you resolve your "open cursors" issue.

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 05:56 (01Dec04) UTC (aka "GMT" and "Zulu"),
@ 22:56 (30Nov04) Mountain Time

 

[uparna]

Hi Dave,
i agree with you , but i cant make the buffalloes drink water..they stick to their opinion that this is a code problem , even though no code change was done. Wish i could convince them. They are the mothership anyway, and so they have the last word. Thanks anyway !

Regards,
S. Jayaram Uparna .

 

[SantaMufasa]

Jayarm, the "Buffalloes" remind me of the farmer that was plowing his field in the blazing heat of the noon-day sun. When asked why he was plowing in the hottest part of the day instead of in the cool of the early morning or evening, he defiantely answered, "I'll be damned if I humour the sun." His defiance doesn't hurt the sun, it only hurts himself.

Their not going to Oracle Tech Support doesn't hurt Oracle, it only hurts you and the Buffalloes. Going to OTS doesn't cost you or the Buffalloes anything, and it should definitely solve your problem. Are the Buffalloes afraid that OTS will prove them wrong? What's more important: Solving the problem and moving on, or theorising possible causes and throwing rocks at the Sun?

Do you need permission from the Buffalloes to contact OTS? If not, call for help and get it resolved. You are paying for help, why not use it?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:05 (01Dec04) UTC (aka "GMT" and "Zulu"),
@ 10:05 (01Dec04) Mountain Time

 

[uparna]

agree. Will ask them to raise a TAR for it again.Only they can do it , not me....so will ask for it.

Hell, i will make them do it.

. Thanks for your insightful reply.

Regards,
S. Jayaram Uparna .

 

[SantaMufasa]

Acha, Jayaram !!! Wayta be assertive !

Fir Milengue,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 18:33 (01Dec04) UTC (aka "GMT" and "Zulu"),
@ 11:33 (01Dec04) Mountain Time

 

[uparna]

thanks dave...really impressed with the "fir milengue" . .

One more question. I know there are many refined methods that have to be used to estimate the shared pool , but here are the raw details : i have a 50Gb Database (dump is that big ) that has 3-400 SPs , 30% of which are really complex and the rest are complex/medium. All the business logic is in the procedures ; we have around 40 active users at any given point in time , connecting through a connection pool (application maintained) which maintains a minimum of 8 connections to satisfy them.

With this information , can you tell me if a 117 MB shared pool is enough? Thanks !

Regards,
S. Jayaram Uparna .

 

[SantaMufasa]

Jayaram,

I apologise for not knowing the answer to this via some didactic method, but strictly empirically, I have chosen to configure most of our production instances with 350MB-500MB of shared pool, with at least 50MB of java pool.

The demands you describe for your environment are even "beefier" (pardon the slang) than most of ours. I would highly recommend increasing your shared pool to the neighborhood of my numbers.

I'm thinking that Oracle may have improved recommendations from their newer automated tuning-assistance tools.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 21:31 (01Dec04) UTC (aka "GMT" and "Zulu"),
@ 14:31 (01Dec04) Mountain Time