Re: Trying to locate a cursor with very little information

From: Kerry Osborne <kerry.osborne_at_enkitec.com>
Date: Tue, 7 Jul 2009 19:50:12 -0500
Message-Id: <109C4204-9E6F-4F00-93D3-D39DCBE055F8_at_enkitec.com>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org
> ] 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_at_optimaldba.com> 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.
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jul 07 2009 - 19:50:12 CDT