Re: Trying to locate a cursor with very little information
- From: Cary Millsap <cary.millsap@xxxxxxxxxxxx>
- To: nigel.cl.thomas@xxxxxxxxxxxxxx
- Date: Wed, 8 Jul 2009 15:40:24 -0500
Nigel,
I do agree with your suggestion beginning with "One approach...."
The answer to your question in your first paragraph, however, is no. I would
expect the PARSING IN CURSOR to be emitted before the first dbcall printed
into the trace, even if that first dbcall printed were an EXEC or a FETCH.
The PARSING IN CURSOR doesn't really have anything to do with parsing. The
Oracle kernel emits it before the first dbcall rendered for a cursor slot,
no matter what that dbcall is. Here's a simple Perl script that will
demonstrate what I mean:
use DBI;
my $dbh = DBI->connect("dbi:Oracle:", $ARGV[0]);
$dbh->{RowCacheSize} = 1;
my $sth = $dbh->prepare("select rownum from dba_objects");
$sth->execute;
$sth->fetchrow_array;
$dbh->do("alter session set sql_trace=true");
$sth->fetchrow_array;
And here's the trace data it produces. It also produces similar data for
9.2.0.8 over SPARC Solaris. I can't test it on 8i because we don't have an
instance running right now.
=====================
PARSING IN CURSOR #2 len=32 dep=0 uid=81 oct=42 lid=81 tim=437474772399 hv=0
ad='cd3fc04' sqlid='0000000000000'
alter session set sql_trace=true
END OF STMT
EXEC #2:c=0,e=138,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=437474772397
XCTEND rlbk=0, rd_only=1, tim=437474774467
*** 2009-07-08 11:39:11.687
CLOSE #2:c=0,e=28,dep=0,type=0,tim=437475197453
=====================
PARSING IN CURSOR #3 len=30 dep=0 uid=81 oct=3 lid=81 tim=437475197825
hv=2126240741 ad='3013836c' sqlid='4yukjc1zbrqz5'
select rownum from dba_objects
END OF STMT
FETCH
#3:c=0,e=204,p=0,cr=4,cu=0,mis=0,r=2,dep=0,og=1,plh=895809668,tim=437475197823
Cary
On Wed, Jul 8, 2009 at 3:03 AM, Nigel Thomas <nigel.cl.thomas@xxxxxxxxxxxxxx
> wrote:
> 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:
