Re: Trying to locate a cursor with very little information

  • From: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
  • To: Mark.Bobak@xxxxxxxxxxxx
  • Date: Tue, 7 Jul 2009 19:50:12 -0500

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


Other related posts: