Re: [Sqlrelay-discussion] Oracle Ref Cursors created in Stored Procedures
Brought to you by:
mused
Menu
▾
▴
Re: [Sqlrelay-discussion] Oracle Ref Cursors created in Stored Procedures
|
From: Matt F. <ma...@eq...> - 2005-11-04 18:54:51
|
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 >> >> |
