I've been trying to emulate this on 9.2.0.2, but I can't. The oddity (I think) is that your >WAIT #0: nam='SQL*Net break/reset to client' ela= 57 p1=675562835 p2=1 p3=0
is recorded against cursor #0, not cursor #54, so it is cursor zero (whatever that is) that has failed to return any data, not cursor 54 (which, as you can see, has fetched a row). Is this cursor inside a pl/sql function, perhaps, and could the function have some code that manages to discard the value and therefore manage to return without a return value ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) ____England______January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -----Original Message----- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 31 December 2002 13:31 >Tim, > >I don't see that ... I know this sql caused the problem ... >===================================================================== ======= >=============================== >PARSING IN CURSOR #54 len=238 dep=1 uid=44 oct=3 lid=44 tim=1016624815165467 >hv=2089161539 ad='24ba3ad8' >SELECT b.pr_mobility_code > FROM prp_requests b, > prp_sr_cal_pps a > WHERE b.pr_req_unit_id = :b3 > AND b.pr_gr_id = a.pscp_id > AND a.pscp_prp_id = :b2 > AND a.pscp_prp_version = :b1 >END OF STMT >PARSE #54:c=0,e=1192,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1016624815165463 >BINDS #54: > bind 0: dty=2 mxl=22(21) mal=00 scl=00 pre=00 oacflg=03 oacfl2=1 size=72 >offset=0 > bfp=11054edb0 bln=22 avl=05 flg=05 > value=9044628 > bind 1: dty=2 mxl=22(21) mal=00 scl=00 pre=00 oacflg=03 oacfl2=1 size=0 >offset=24 > bfp=11054edc8 bln=22 avl=04 flg=01 > value=586823 > bind 2: dty=2 mxl=22(21) mal=00 scl=00 pre=00 oacflg=03 oacfl2=1 size=0 >offset=48 > bfp=11054ede0 bln=22 avl=02 flg=01 > value=2 >EXEC #54:c=0,e=1681,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1016624815167288 >WAIT #54: nam='global cache cr request' ela= 417 p1=32 p2=500383 >p3=504403158449678016 >WAIT #54: nam='db file sequential read' ela= 2816 p1=32 p2=500383 p3=1 >WAIT #54: nam='global cache cr request' ela= 445 p1=33 p2=517103 >p3=504403158399168096 >WAIT #54: nam='db file sequential read' ela= 1858 p1=33 p2=517103 p3=1 >WAIT #54: nam='db file sequential read' ela= 781221 p1=31 p2=1251231 p3=1 >FETCH >#54:c=0,e=787456,p=3,cr=17,cu=0,mis=0,r=1,dep=1,og=4,tim=101662481595 4763 >WAIT #0: nam='SQL*Net break/reset to client' ela= 57 p1=675562835 p2=1 p3=0 >WAIT #0: nam='SQL*Net break/reset to client' ela= 630 p1=675562835 p2=0 p3=0 >WAIT #0: nam='SQL*Net message to client' ela= 1 p1=675562835 p2=1 p3=0 >*** 2002-12-27 16:17:21.846 >WAIT #0: nam='SQL*Net message from client' ela= 29596938 p1=675562835 p2=1 >p3=0 >===================== >PARSING IN CURSOR #45 len=8 dep=0 uid=3318 oct=45 lid=3318 >tim=1016624845553757 hv=1226881397 ad='24f0b6b8' >ROLLBACK >END OF STMT >PARSE #45:c=0,e=412,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1016624845553752 >XCTEND rlbk=1, rd_only=1 >EXEC #45:c=0,e=61,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1016624845553883 >WAIT #45: nam='SQL*Net message to client' ela= 3 p1=675562835 p2=1 p3=0 >WAIT #45: nam='SQL*Net message from client' ela= 13212 p1=675562835 p2=1 >p3=0 >===================== >PARSING IN CURSOR #45 len=8 dep=0 uid=3318 oct=45 lid=3318 >tim=1016624845567343 hv=1226881397 ad='24f0b6b8' >ROLLBACK >END OF STMT >PARSE #45:c=0,e=47,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1016624845567339 >XCTEND rlbk=1, rd_only=1 >EXEC #45:c=0,e=52,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1016624845567450 >WAIT #45: nam='SQL*Net message to client' ela= 1 p1=675562835 p2=1 p3=0 >WAIT #45: nam='SQL*Net message from client' ela= 6733 p1=675562835 p2=1 p3=0 >STAT #43 id=1 cnt=1 pid=0 pos=1 obj=29433 op='TABLE ACCESS BY INDEX ROWID >SELLING_ROTATIONS (cr=3 r=1 w=0 time=14786 us)' >STAT #43 id=2 cnt=1 pid=1 pos=1 obj=422698 op='INDEX UNIQUE SCAN SR_PK_PRIM >(cr=2 r=0 w=0 time=27 us)' > >Thanks for the explanation ... >Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).