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 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 st
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
..
PARSI
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 V$SESS
> 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 f
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 se
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 not
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 pro
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 wri
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: M
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?
---
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
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 happe
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 that
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
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
> sess
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
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
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 i
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
Upc
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=
>
>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
#31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=200147
Thanks a lot, Cary.
Indeed the indented notation seems rather convenient.
I would be delighted to take your PD101 course, just
not sure if people here dealing with the training
budget would share the delight with me :-(
Doesn't hurt to ask though...
Thanks again,
Boris Dali.
__
Boris, thanks for sending me your data. The following note pertains only
to the excerpt you sent me; I didn't look at the whole trace file.
Here's the excerpt you sent:
=
PARSING IN CURSOR #1 len=94 dep=0 uid=83 oct=47 lid=83 tim=1614119418158
hv=1138148843 ad='605d0998'
BEGI
Thanks, Jared.
Yes, running select vs select+rollback in a loop of
1000 iterations I got similar results (average over 3
runs):
STAT...user rollbacks 0 1,000 1,000
LATCH...enqueues 570 1,574 1,004
LATCH...shared pool7,434 9,063 1,629
STAT...recursive 7,754 10,264 2,5
Thanks, Raj.
So yes, as I said in my other email - the rule stated
in the book seem to apply to EXEC db calls only (in
case of SQL fired from PL/SQL). I guess I
misinterpreted it the way that it applies to ALL db
calls for recursive cursors.
Thanks,
Boris Dali.
--- "Jamadagni, Rajendra"
<[EMAIL
But the previous email was a shining example of brevity in action! ;)
I'll have to wait for Cary, et.al. as well as my understanding is the exact same as
yours.
Daniel
"Jamadagni, Rajendra" wrote:
> Sorry about the last empty email ...
>
> Cary is right, the EXEC at dep=0 is the database call
Sorry about the last empty email ...
Cary is right, the EXEC at dep=0 is the database call you should be looking for, why?
because until #1 is parsed, db has no way of finding what needs to do. And once it
finds that "Oh I must run a SQL", the dep increases. So, I'd look for a subsequent
EXEC
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-Original Message-
Sent: Monday, November 2
Thanks for your reply, Daniel.
Yes, it makes sense for the EXEC calls, but it doesn't
explain the PARSEs, does it?
PARSING IN CURSOR #1 len=94 dep=0...
PARSE #1:c=0,e=141,p=0,cr=0,cu=0,mis=0,r=0,dep=0...
PARSING IN CURSOR #2 len=68 dep=1...
PARSE #2:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=1...
Here
Using a slightly modified version of run_stats to return
timings in 1/1 of a second, the timing of 1000 iterations
of a loop executing noop vs. 1000 doing rollback:
.0005 secs
.0354 secs
Here are the stats that were different between the two,
kind of what you would expect:
LATCH.cache buffer
Thanks, Jared, Tanel.
I was a little supprised to see a combination
rlbk=1,rd_only=1. Why read-only bit is set here if it
rolls back anyway?
So I thought may be they mark their tx explicitly as
read-only (aka "set transaction read-only"). Reveiwing
OCI fine manual there seemed to be an option of
Jared, actually your initial post made sense anyway - since you can't roll
back committed transactions anyway. Also, rollback is done on session end if
you haven't done the rollback manually ;)
Tanel.
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sen
Boris,
Cary is correct. It gets a little confusing, especially with pl/sql
involved. It also helps to remember to differentiate between database
calls (parse/execute/fetch) and other events (wait/stat).
Using your example below, I'll attempt an explanation inline.
Daniel Fink
Boris Dali wrote:
hmmm...
I think I meant OCITransCommit() there, not OCITransRollback().
Nobody caught that? :)
Jared
On Mon, 2003-11-24 at 18:29, Jared Still wrote:
> OCI defaults to rollback on transactions on disconnect if
> OCITransRollback() has not been called.
>
> Don't ask me for too much detail, as I
OCI defaults to rollback on transactions on disconnect if
OCITransRollback() has not been called.
Don't ask me for too much detail, as I'm not an OCI programmer,
I just pulled this straight from The Fine Manual.
Jared
On Mon, 2003-11-24 at 16:54, Boris Dali wrote:
> I've got a third party packag
Boris
It look like the app's is doing rollback :-(
Have a look in Note 39817.1 Interpreting Raw SQL_TRACE ... for more info.
XCTEND rlbk=(0 or 1) rd_only= (0 or 1)
rlbk : 1 = rollback 0 = commit
rd_only : 1 = read only transaction 0 = none read only
/peter
Boris Dali wrote:
I've got a third
To my knowledge , you don't need go to google , try on metalink TOP TECH
DOCS --> DATABASE - SQL TUNING
Madhu Reddy X13944
-Original Message-From: Ryan
[mailto:[EMAIL PROTECTED]Sent: Sunday, September 21, 2003 9:30
PMTo: Multiple recipients of list ORACLE-LSubject: sql
Did you check out www.hotsos.com? Cary Millsap and Jeff Holt have a few (as well
as a great book Optimizing Oracle Performance)
Thanks/Richard
--- Ryan <[EMAIL PROTECTED]> wrote:
> did a google search and couldnt find anything worth reading. other than the
> ones on hotsos any other good one
On Tuesday 19 November 2002 22:03, you wrote:
> Qs What is the Cause in particular (or in General) of Time Difference
> between "cpu" & "elapsed" Columns in the following Query ?
>
e = c + wait time (of anykind) (+ rounding errors)
> Qs Is there Any Scope for improvement in the following Query ?
>
Merhaba,
If aggeragate=false, which is not default, SQL statement length does not make sense
since each SQL are same lenght in raw file and
output.
in addition to Ed, check your output. If there ara a lot of kernel calls such as
parse,fetch, etc. row file will be larger than output.
regards.
Hi Arslan,
it's a common situation. In general, the size of tkprof's output depends
on number of identical sql statements. If my memory services me right
tkprof groups identical sikvels by default.
Regards,
Ed
> -Original Message-
> From: Arslan Bahar [mailto:[EMAIL PROTECTED]]
> Sen
42 matches
Mail list logo
