Re: Oracle cursors and purge

[prev] [thread] [next] [Date index for 2004/11/09]

Hi all,

Thanks for the quick responses.

So far I have had time to try the suggestion to clear out the cached 
handles. That didn't solve the problem, but I learned more about CDBI! 
Actually, that part of the code appears to be working perfect since 
after many runs the cache shows only 4 cached statements.

Yes, I'm using set_sql to create a search method and it takes one 
parameter. I believe that is getting bound by CDBI (I'm using the '?' 
syntax).

I'm also using the CDBI $dbh to execute some queries using straight DBI 
code since I have some libraries that were written pre-CDBI. All of 
those are using bound variables as well. I'll go through again, though, 
and make sure I don't have anything unbound.

I'm moving on to try to narrow down where the problem is coming from. 
It's a very complex system--that's why it's taking me some time to 
factor out where the problem is. For the short term, I re-wrote the 
functionality around the problem.

The other thing I realized I didn't mention is we are running very old 
versions of several modules:

DBD::Oracle -- 1.12
DBI -- 1.21
Class::DBI -- 0.95
Ima::DBI -- 0.31

We're planning on some upgrades soon, so that might help. (Maybe 
someone knows for sure this is my problem.)

Perrin and Tim are correct that using the purge_object_index_every 
method had nothing to do with fixing the problem. The reason the 
problem "went away" was that I was running CDBI 0.96 from local install 
and I didn't have Ima::DBI installed there.

So another piece to the puzzle is that when running CDBI 0.96 without 
Ima::DBI version 0.31, the problem goes away.

To give some sample code, I'm pulling in Ima::DBI in my base CDBI 
module this way:

   sub db_Main {
     require DBI;

     # Keep track of current handle to avoid opening a new
     # one for each connect.
     if ( defined $pid ){
       return $dbh if $pid == $$;
     }
     $pid = $$;

     $dbh = DBI->connect(connect_string,
			username,
			password,
			{PrintError => 1,
            RaiseError => 1,
            AutoCommit => 1,
            ShowErrorStatement  => 1,
			 RootClass => 'Ima::DBI',
			})
...

I don't have the "problem" SQL narrowed down yet because once the max 
cursors is reached, every query the app tries to run throws the cursors 
error.

I'm leaning toward 'finish' not being called somewhere or some sort of 
scoping issue where CDBI objects are staying in scope too long. Is this 
possible, or just crazy talk?

Thanks again for the help so far.
Jim


On Nov 8, 2004, at 8:55 AM, Adam Przygienda wrote:

> Hi Jim,
>
> do you use retrieve_from_sql or other functions which include SQL 
> statements
> or parts of SQL statements?
>
> Can you post here a piece of code incl. your sql statement which raised
> this exception?
>
> If you don't use bind variables in the WHERE part of your sql 
> statements
> and query a large number of objects, Oracle will create an open cursor
> for every single call to the db.
>
>
> Geetings
> Adam
>
>
> On Wed, 3 Nov 2004 09:45:49 -0500, Jim Brandt <cbrandt@xxxxxxx.xxx> 
> wrote:
>
>> I've got a web app using Class::DBI and I recently had an "edge case" 
>> for a particular script that caused Oracle to start spewing:
>>
>> ORA-01000 maximum open cursors exceeded
>>
>> I investigated and found a few loops creating quite a few CDBI 
>> objects because of the large number of records.
>>
>> I experimented with the purge_object_index_every method, and setting 
>> that to a lower value appeared to solve the problem. I think our 
>> Oracle cursor setting is at 1000, so the script was going over.
>>
>> My question is, would folks expect setting the purge this way would 
>> solve the problem? I just want to make sure it's actually doing what 
>> I think it's doing.
>>
>> If this is the case, I can add some info to the Kwiki regarding the 
>> open cursors error.
>>
>> Thanks,
>> Jim
>>
>> ==========================================
>> Jim Brandt
>> Administrative Computing Services
>> University at Buffalo
>>
> -- 
> -----------------------------------------------------------------------
> Adam Przygienda
> Computer Science Engineer SEC
> SWISSCOM Enterprise Solutions AG
> IP-Plus Internet services  http://www.ip-plus.net
> -----------------------------------------------------------------------
>
==========================================
Jim Brandt
Administrative Computing Services
University at Buffalo