Re: sql trace - forward attribution

I had the same discussion with Jeff Holt (one of Cary's partners in crime) and he described #0 attributions as actions not associated with a cursor (i.e. statement). For example, I worked on a web server which would maintain a persistent connection. Every 90 minutes, it would execute a series of statements to load up the web cache. At the end of the series, it closed all the cursors and issued a "rollback" <conjecture>. As all cursors were closed, the wait time until the next 'awakening' was attributed to cursor #0.

The non-association of #0 also explains why you should not see #0 parses, executes, fetches or stats.

Daniel Fink

Boris Dali wrote:

> Thanks to Anjo, Cary, Tanel, and everybody who
> provided feedback back channel.
>
> Just to rule out the possibility of a collection error
> (somebody suggested that cursor #0 is simply not
> captured) I bounced the DB today, enabled a DB-wide
> trace ... and as expected
>
> grep -i "cursor #0" *
>
> returned nothing, while "wait #0" gives plenty. So it
> is not a trace activation/termination error.
>
> ---
>
> I think what we deal with here is a "variant" of what
> Anjo described, but not exactly that as I don't see
>
> *** SESSION ID:(sid.serial#) lines in the middle of
> any trace file, only in the header, but I think it
> still might be "session switching" of a kind.
>
> What we use here is an n-tier proxy authentication and
> I suspect these waits is the price we pay for it. Not
> sure, but maybe if proxy attributes are "switched" sql
> trace doesn't capture this properly, "forgeting" to
> emit new session info? I would be interested to know
> how to
>
> 1) confirm or refute this
> 2) since waits #0 appear only before the calls to a
> stored code - I don't know if they deliberatly "switch
> sessions" in the code that runs on the app server and
> run the stored code as the schema owner (similar to
> switching current schema as an alternative to using
> synonyms) or it is a feature of Oracle's proxy
> authentication implementation
> 3) how to check "proxy identity" of the user - i.e.
> how to run something like sys_context('userenv',
> 'proxy_user') for sessions other than my own.
>
> Thanks,
> Boris Dali.
>
> --- Anjo Kolk <anjo_at_oraperf.com> wrote: > They write
> all to the same trace file. So there
> > should be different
> > sid.serial# combinations.
>
> ______________________________________________________________________
> Post your free ad now! http://personals.yahoo.ca
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Boris Dali
> INET: boris_dali_at_yahoo.ca
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel W. Fink
  INET: Daniel.Fink_at_Sun.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).