Oracle-L: RE: max open cursors case

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Tue, 08 Apr 2003 09:39:13 -0800
Message-ID: <F001.0057CF74.20030408093913@fatcity.com>

Raj,

Then you set the parameter to whatever their little heart desires, times 2.

Dick Goulet

Some days you get the bear, and then some days he gets you!!

-----Original Message-----
Sent: Tuesday, April 08, 2003 1:19 PM
To: Multiple recipients of list ORACLE-L

Dick,

My fear is they might approve the budget ... what gives then?

Raj

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

-----Original Message-----
Sent: Tuesday, April 08, 2003 11:54 AM
To: Multiple recipients of list ORACLE-L

Raj,

Here was the pile of fun that I had. Our Duhvelopers had this tendency to create cursors for unique SQL statements because they would embed the variables inside the statement, things like 'username = 'MIKE' instead of 'where username = :1'. They would also never call the 'close cursor' command or issue a rollback or commit unless absolutely necessary. Were doing all of this in C mind you. Consequently 1500 cursors later they run into the max open cursors exceeded error message. The SYS.V_$OPEN_CURSOR view also has the SQL their using to open the cursor with, try spooling some of the worse offenders out and send that along across the street, especially is you can highlight places where they have the exact same statement open with different bind variables. Then maybe you can talk them into parameterize their SQL and minimizing the number of open cursors they have. Also try, I know it's hard, to get them to close the cursors when their done with them. You might also have a word or two with your SA. Since they want so many open cursors you need more shared pool to cache them in and someone has to pay for that memory don't they? It certainly puts the issue in a way that a manager can understand. (IE, We need an additional 4G of memory and 2 carrier cards that will cost $10K from your budget and cause 1 day of down time. BTW, If you'd sit on your duhvelopers a little, we would not need this.)

Dick Goulet

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Goulet, Dick
INET: DGoulet_at_vicr.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Apr 08 2003 - 12:39:13 CDT