Re: [Sqlrelay-discussion] Oracle Ref Cursors created in Stored Procedures

[David M. <dav...@fi...>]

It took a while, but I believe I found the bug.

You can fix it by editing src/connection/sqlrconnection/getbinds.C
and adding this line after line 110:

		curs->busy=true;

That should take care of it.

David Muse
dav...@fi...

On Tue, 2005-10-11 at 15:58 -0400, Matt Flynn wrote:
> I am experiencing similar behavior.
> 
> I am also using sqlrcur_getOutputBindCursor but I have not yet 
> determined if this is the (only) cause of open cursors on the oracle 
> server.  Using PHP OCI I typically see around 8000 open cursors on one 
> database for a particular db user.  Using sqlr I watched the open 
> cursors climb to 20000+ before shutting down sqlrelay.  Oracle version 
> is 9.2.0.5.
> 
> Here is my instance configuration in sqlrelay.conf:
> <instance id="dbinstance" port="9000" socket="/tmp/dbinstance.socket" 
> dbase="oracle8" connections="50" maxconnections="200" maxqueuelength="0" 
> growby="5" ttl="60" endofsession="commit" sessiontimeout="600" 
> runasuser="sqlrelay" runasgroup="sqlrelay" cursors="3" 
> authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none">
> 
> If cursors are left open on the Oracle server, does this mean a cursor 
> is left hanging on the sqlrelay server?  Meaning that my php app can no 
> longer use that sqlrelay cursor?  The behavior I am experiencing is that 
> my sqlrelay connections are eventually all used up and my php app can no 
> longer connect to the sqlrelay servers.  With my current configuration 
> the increase of sqlr-connection processes running on the server is 
> progressive even though the web server traffic is relatively stable.  My 
> 50 initial connections last maybe 15 minutes before they scale up and 
> after about an hour to an hour and a half, all 200 sqlr-connection 
> processes are running and the site can no longer connect to sqlr.
> 
> I have taken extreme care, per 
> http://sqlrelay.sourceforge.net/sqlrelay/faq.html#php, to make sure ALL 
> open sqlr connections and cursors are explicitly closed through calls to 
> sqlrcur_free() and sqlrcon_free() before the php scripts end and I am 
> positive this is not the issue.
> 
> Any suggestions or further troubleshooting steps are greatly appreciated.
> 
> Thanks,
> 
> Matt
> 
> 
> 
> 
> Firstworks/4access wrote:
> 
> >Looks like the server-side isn't cleaning up those cursors properly.
> >I'll take a look and see if I can figure out what's going on.
> >
> >Dave
> >
> >On Thu, 2005-09-22 at 11:01 -0700, Dennis Bolton wrote:
> >  
> >
> >>Hi,
> >>
> >>When running the code below repeatedly I find that the
> >>cursor created in 
> >>
> >> 	$returncur = sqlrcur_getOutputBindCursor($cur,'returncur');
> >>
> >>is never closed on the oracle server.  After enough iterations the
> >>server will error with to many open cursors.
> >>
> >>I am running sqlrelay 3.6.4, but the same results occur on 3.6.2.
> >>PHP version is 4.4 . Oracle is 9.2g
> >>
> >>Here is a couple of consecutive sample results from this code:
> >>
> >>1.
> >>there are 711 open cursors
> >>Array
> >>(
> >>    [StudentSortOrder] => 0
> >>    [ClassID] => 818
> >>    [Class] => Best Wisshes
> >>    [ClassDesc] => 
> >>    [GradeLevel] => 4
> >>    [ActiveStudents] => 1
> >>)
> >>there are 712 open cursors
> >>
> >>2.
> >>there are 712 open cursors
> >>Array
> >>(
> >>    [StudentSortOrder] => 0
> >>    [ClassID] => 818
> >>    [Class] => Best Wisshes
> >>    [ClassDesc] => 
> >>    [GradeLevel] => 4
> >>    [ActiveStudents] => 1
> >>)
> >>there are 713 open cursors
> >>
> >>
> >>
> >>What might I be doing wrong?
> >>
> >>
> >>*************************************************************************************
> >><pre>
> >><?php
> >>
> >>$conn =
> >>sqlrcon_alloc('localhost','','/tmp/emgames.socket','webuser','thisisit',0,1);
> >>//print_r($conn);
> >>
> >>//print_r($relay);
> >>
> >>$countquery = "select count(*) from sys.gv_\$open_cursor";
> >>
> >>$qcur = sqlrcur_alloc($conn);
> >>sqlrcur_sendquery($qcur,$countquery);
> >>sqlrcon_endSession($conn);
> >>
> >>$rowarray=sqlrcur_getRow($qcur,0);
> >>
> >>echo "there are {$rowarray[0]} open cursors\n";
> >>
> >>$procedure = "BEGIN sa.s_classes_teacher(2559,:returncur); END;";
> >>
> >>if($cur = sqlrcur_alloc($conn)){
> >>
> >>	sqlrcur_prepareQuery($cur,$procedure);
> >>
> >>	sqlrcur_defineOutputBindCursor($cur,'returncur');
> >>
> >>	sqlrcur_executeQuery($cur);
> >>  
> >>	$returncur = sqlrcur_getOutputBindCursor($cur,'returncur');
> >>  
> >>	sqlrcur_fetchFromBindCursor($returncur);
> >>
> >>
> >>	$numrows = sqlrcur_rowCount($returncur);
> >>	$numcolumns = sqlrcur_colCount($returncur);
> >>      
> >>      
> >>	// Get the column names and types
> >>	for($i = 0; $i < $numcolumns; $i++){
> >>		$columnnames[$i] = sqlrcur_getColumnName($returncur,$i);
> >>		$columntypes[$i] = sqlrcur_getColumnType($returncur,$i);
> >>	}
> >>
> >>	// Get each row column by column and store each of them in the Results
> >>array
> >>	for($i = 0 ; $i < $numrows  ; $i++) {
> >>		for($j = 0 ; $j < $numcolumns; $j++){
> >>			$results[$i][$columnnames[$j]] = sqlrcur_getField($returncur,$i,$j);
> >>		}
> >>	}
> >>  print_r($results[0]);    
> >>	sqlrcon_endsession($conn);
> >>	sqlrcur_clearbinds($returncur);
> >>	sqlrcur_free($returncur);
> >>	sqlrcur_clearbinds($cur);
> >>	sqlrcur_free($cur);
> >>
> >> }
> >> else {
> >>	 echo "couldn't open cursor";
> >> }
> >>
> >>sqlrcur_sendquery($qcur,$countquery);
> >>sqlrcon_endSession($conn);
> >>
> >>
> >>$rowarray=sqlrcur_getRow($qcur,0);
> >> 
> >>echo "there are {$rowarray[0]} open cursors";
> >>
> >>
> >>sqlrcur_free($qcur);
> >>sqlrcon_free($conn);
> >>?>
> >></pre>
> >>*********************************************************************
> >>    
> >>
> >
> >
> >
> >-------------------------------------------------------
> >SF.Net email is sponsored by:
> >Tame your development challenges with Apache's Geronimo App Server. Download
> >it for free - -and be entered to win a 42" plasma tv or your very own
> >Sony(tm)PSP.  Click here to play: http://sourceforge.net/geronimo.php
> >_______________________________________________
> >Sqlrelay-discussion mailing list
> >Sql...@li...
> >https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> >
> >  
> >
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by:
> Power Architecture Resource Center: Free content, downloads, discussions,
> and more. http://solutions.newsforge.com/ibmarch.tmpl
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>