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: Dennis B. <den...@mc...> - 2005-11-04 18:16:32
|
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 -- Dennis Bolton Manager, Web Engineering McGraw-Hill Learning Group Technology 425-806-3495 |
