Re: Trying to locate a cursor with very little information
- From: Nigel Thomas <nigel.cl.thomas@xxxxxxxxxxxxxx>
- To: cary.millsap@xxxxxxxxxxxx, cshapi@xxxxxxxxx
- Date: Wed, 8 Jul 2009 09:03:30 +0100
Cary
Surely PARSING IN CURSOR #2 would not be emitted if tracing were enabled
AFTER the parse but BEFORE the FETCH. That;s pretty easy to arrange, isn't
it?
Chen
One approach t is to find the known SQLs that get executed/fetched close to
your FETCH #2, and then identify a short list of likely candidates for the
missing #2. Can you find the SQL from just before, just after your fetch?
Can you identify procedural loops? Can you see where the transaction
completion comes, relative to your SQL? All these are clues you can follow
when tracking down #2. Of course, to do this you need to have read access to
the source code.... an a basic understanding of how the code hangs together.
Regards Nigel
2009/7/8 Cary Millsap <cary.millsap@xxxxxxxxxxxx>
> 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>
> 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:
