Oracle-L: RE: max open cursors case

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Tue, 08 Apr 2003 07:53:49 -0800
Message-ID: <F001.0057CD3C.20030408075349@fatcity.com>

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

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

Jeremiah,

I agree wholeheartedly, but my (battle) cry falls on deaf ears ...

The only rule of engagement is "... it is that stupid database that can't figure out my code ..." oh and they recently hired some super developers who have experience on (SQL Server) .. really top notch developers ... does a select * on a 35M rows table using TOAD and complains " the database is too slow " ...

Question is what kind of proof I can send across the street ...? 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 10:14 AM
To: Multiple recipients of list ORACLE-L

The developers have introduced a cursor leak somewhere. That number is PER USER. They need to fix their code.

--

Jeremiah Wilton
http://www.speakeasy.net/~jwilton

--

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 - 10:53:49 CDT