Re: Trying to locate a cursor with very little information

  • From: Cary Millsap <cary.millsap@xxxxxxxxxxxx>
  • To: cshapi@xxxxxxxxx
  • Date: Wed, 8 Jul 2009 00:17:23 -0500

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
>
>
>

Other related posts: