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

[Matt F. <ma...@eq...>]

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
>
>  
>