Chen, I agree with Kerry. I'm surprised, though, that there's no PARSING IN CURSOR #2 anywhere in the trace stream preceding the FETCH #2 line. Whether there's a PARSE #2 or not, I'd expect a PARSING IN CURSOR #2 to precede either the FETCH #2 or an EXEC #2 in there somewhere. What circumstance do you think caused Oracle not to emit the PARSING IN CURSOR section? Cary Millsap Method R Corporation http://method-r.com http://carymillsap.blogspot.com http://twitter.com/cary_millsap On Tue, Jul 7, 2009 at 7:50 PM, Kerry Osborne <kerry.osborne@xxxxxxxxxxx>wrote: > Maybe the other Cary will pitch in here, but I think Mark is right. Cursor > numbers can be reused in trace files so even if there was a place where they > were stored along with the sql (which I don't think there is), you wouldn't > know which number 2 to look at. > > Kerry Osborne > Enkitec > blog: kerryosborne.oracle-guy.com > > > > > > > > On Jul 7, 2009, at 7:37 PM, Bobak, Mark wrote: > > Hi Chen, >> >> I don't think it's in the SGA. You and I could reference the same SQL, >> and have different cursor #'s in our respective trace files. I suspect that >> information is private to the PGA, and I can't think where it would be >> mapped to an X$/V$.... >> >> Honestly, I think you're out of luck..... >> >> If anyone has any better ideas, I'd be happy to be wrong.... >> >> -Mark >> >> -----Original Message----- >> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] >> On Behalf Of Chen Shapira >> Sent: Tuesday, July 07, 2009 7:04 PM >> To: Daniel Fink; oracle-l >> Subject: Re: Trying to locate a cursor with very little information >> >> I don't have the sql text in the trace file, but Oracle still have the >> cursor in the shared pool. >> And Oracle knows that cursor #2 from a specific process is related to >> a specific open cursor in the pool (otherwise exec and fetch calls >> would fail). So there must be a way to find the sql text from Oracle's >> SGA. >> >> I was hoping someone already figured it out... >> >> Chen >> >> On Tue, Jul 7, 2009 at 3:51 PM, Daniel Fink<daniel.fink@xxxxxxxxxxxxxx> >> wrote: >> >>> If you don't have the PARSE or PARSING lines, you won't have the sql >>> text. >>> However, if the STAT lines were written, you might be able to reverse >>> engineer the statement from the plan. Use the STAT lines to see what >>> plans >>> use those operations and what statements use those plans. >>> >> -- >> //www.freelists.org/webpage/oracle-l >> >> >> >> >> -- >> //www.freelists.org/webpage/oracle-l >> >> >> > -- > //www.freelists.org/webpage/oracle-l > > >