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-10-26 23:39:46
|
Thanks David! I'll give it a try. Dennis On Wed, 2005-10-26 at 19:24 -0400, 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 -- Dennis Bolton Manager, Web Engineering McGraw-Hill Learning Group Technology 425-806-3495 |
