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
you may not be seeing parse etc entries for cursor #0 merely because maybe by design,
cursor#0 gets invoked before trace gets activated. This way, you will never get cursor
#0 info.
You can tell, I am guessing but to get similar experience, start trace in an already
active session and you'll
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
Thanks, Tanel.
I did checked v$session_connect info, but it doesn't
tell me much, except authentication_type='PROXY' which
is less than useful as it doesn't tell to whom.
v$session.client_identifier is empty.
Thanks,
Boris Dali.
Check V$SESSION_CONNECT_INFO view.
CLIENT_IDENTIFIER in
Thanks, Raj, but I don't think so. The whole reason
behind enabling a db-wide trace was to capture
everything sql trace is instrumented to capture. As
you know sql_trace is a static parameter, so I got
everything from the begining, including:
PARSING IN CURSOR #1
ALTER DATABASE MOUNT
..
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
Btw, reading through a 10046/12 trace from instance startup database
opening can reveal really lots of interesting information :)
Tanel.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, January 06, 2004 8:24 PM
Thanks, Raj, but I
Thanks, Cary.
Could you elaborate what do you mean by wait events
associated with COMMIT processing? Why does Oracle
need this exchange of messages with the client
(well, with the app server really in my case of a
3-tier deployment) to perform a commit?
In any event, as I described earlier in
In-line...
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 1/27 Atlanta
- SQL Optimization 101: 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
Boris Dali
Cursor 0 also happens in oracle due to session switching (multiple
sessions in the same process), oracle apps uses that but it also could
happen with certain other application servers (haven't investigated it).
Anjo.
-Original Message-
Boris Dali
Sent: Monday, January 05, 2004 3:59 PM
Oracle Portal uses session switching as well (and Apps 11i uses Portal...)
Tanel.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, January 05, 2004 7:49 PM
Cursor 0 also happens in oracle due to session switching (multiple
sessions in
Thanks, Anjo.
When session switching occurs does the new session get
the same sid and serial#? And what happens with the
session being switched/replaced - does the
transaction it was performing get commited/rollbacked?
I don't see XCTEND markers before those pesky WAIT #0
in the trace file.
Also
No,
Each session will have its own sid and serail#, but they all run in the
same process. Basically the client side tells oracle, that it wants to
switch from session to session and oracle will keep the state of the
switched out session. So you don't have to commit or rollback on every
switch
I actually build a testcase for this and it still failed on 9.2 without
any patches. It is supposed to be fixed in some later patch. I don't
have the patches
-Original Message-
Anjo Kolk
Sent: Monday, January 05, 2004 6:49 PM
To: Multiple recipients of list ORACLE-L
Cursor 0 also
Anjo,
I suppose your test-case involved more than just use
of sqlplus. Probably some middle tier with
connection/session pooling of some sort?
--- Anjo Kolk [EMAIL PROTECTED] wrote: I actually
build a testcase for this and it still
failed on 9.2 without
any patches. It is supposed to be
Right, but the new session (that inherits the sql
trace attribute) - wouldn't it produce a **separate**
trace file? In my case there's only one trace file
with sid.serial# clearly stated at the begining of the
trace file and WAIT #0 scattered all over the trace.
..Or am I missing something?
---
Trace file has server process number in it's name, not session number, thus
as long as the sessions are served by the same server process, the contents
will be written into one single file.
Tanel.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent:
Tanel,
What I see in the trace file header is something like
the following:
...
*** SESSION ID:(22.9304) 2003-12-29 15:04:45.743
...
Which is sid.serial# isn't it?
If session switching occurs, handled by the same
shadow process and the new session with a different
sid.serial# continues to
They write all to the same trace file. So there should be different
sid.serial# combinations.
-Original Message-
Boris Dali
Sent: Monday, January 05, 2004 9:49 PM
To: Multiple recipients of list ORACLE-L
Right, but the new session (that inherits the sql
trace attribute) - wouldn't it
Thanks a lot for your reply, Cary.
One follow-up question. What would motivate a chat
of sometimes 5, sometimes 10-20 'SQL*Net message
to/from client' consecutive wait lines emitted to the
trace file in the following manner:
WAIT #0: nam='SQL*Net message to client' ela= 2
p1=1413697536 p2=1 p3=0
Boris,
Cursor #0 seems reserved for two special uses: (1) wait events
associated with COMMIT processing (also, of course, ROLLBACK and
SAVEPOINT), and (2) wait events associated with dbcalls not instrumented
because of bug 2425312.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
WAIT #31: nam='SQL*Net message to client' ela= 1
p1=1413697536 p2=1 p3=0
WAIT #31: nam='SQL*Net message from client' ela= 692 p1=1413697536 p2=1
p3=0
WAIT #31: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1
p3=0 FETCH
22 matches
Mail list logo
