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

[Dennis B. <den...@mc...>]

Nope, it did nothing at all.

I still get one extra cursor left open on the database for each bind
cursor returned.  I just rebuilt it again this morning with the fix
applied to 0.36.4 (Had been on 0.36.2), with the same results.
Just in case, I did a manual uninstall and rebuilt everything.
No luck.

Dennis

On Fri, 2005-11-04 at 13:50 -0500, Matt Flynn wrote:
> So did this fix do nothing at all for you?  The reason I say the fix 
> helped is because it took 3x as long (6 hrs  vs 2 hrs prior to applying 
> the fix) before the oracle cursors climbed to an unacceptable level.  If 
> the fix is not working, the extra time it took sqlrelay to fail may be 
> due to less traffic on the site at the time of testing.  Perhaps this 
> fix did not solve the issue.
> 
> -Matt
> 
> 
> Dennis Bolton wrote:
> 
> >Good luck Matt,
> >
> >I still get the same results as below with ref cursors left open.
> >
> >Dennis
> >
> >On Fri, 2005-11-04 at 12:48 -0500, Matt Flynn wrote:
> >  
> >
> >>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
> >>>
> >>> 
> >>>
> >>>      
> >>>
> >>
> >>-------------------------------------------------------
> >>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
> >>    
> >>
> 
> 
> 
> -------------------------------------------------------
> 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
-- 
Dennis Bolton
Manager, Web Engineering        
McGraw-Hill Learning Group Technology
425-806-3495