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

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

This seems to have helped the situation but not resolved it.  I'm 
looking into other areas where cursors could be left open.

David Muse wrote:

>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
>>
>>    
>>
>
>
>
>-------------------------------------------------------
>This SF.Net email is sponsored by the JBoss Inc.
>Get Certified Today * Register for a JBoss Training Course
>Free Certification Exam for All Training Attendees Through End of 2005
>Visit http://www.jboss.com/services/certification for more information
>_______________________________________________
>Sqlrelay-discussion mailing list
>Sql...@li...
>https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>  
>