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
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
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
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
..
PARSING IN CURSOR #1
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
if session gets switched, wouldn't this terminate
sql trace for the session (in my case it doesn't)?
Thanks,
Boris Dali.
--- Anjo Kolk [EMAIL PROTECTED] wrote:
Cursor 0 also happens in oracle due to session
switching (multiple
sessions in the same process), oracle apps uses
that you perform. SQL trace is inherited by the process it you
set in a session, so other sessions that run in the same process will
produce also trace output.
Anjo.
-Original Message-
Boris Dali
Sent: Monday, January 05, 2004 7:34 PM
To: Multiple recipients of list ORACLE-L
Thanks, Anjo
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
: Monday, January 05, 2004 10:49 PM
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 t
process, the contents
will be written into one single file.
Tanel.
- Original Message -
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Monday, January 05, 2004 10:49 PM
Right, but the new session (that inherits the sql
trace attribute) - wouldn't it produce
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
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
I don't have the book with me right now, but I am
obviously missing something in the forward
attribution concept as it doesn't seem to help me in
explanation of the following lines:
WAIT #31: nam='SQL*Net message to client' ela= 1
p1=1413697536 p2=1 p3=0
WAIT #31: nam='SQL*Net message from
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:
excerpt
=
PARSING IN CURSOR #1 len=94 dep=0 uid=83 oct=47 lid=83 tim=1614119418158
hv=1138148843
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.
24, 2003 8:10 PM
To: Multiple recipients of list ORACLE-L
Reading Cary's Optimizing Oracle Performance, page
91 it says:
A database call with dep=n+1 is the recursive child
of the first SUBSEQUENT (empasis mine) dep=n database
call listed in the SQL trace data stream
Does this apply to the SQL
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
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 you
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
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
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 package that connects to Oracle
via OCI and works in HTTP-like (stateless) fashion.
Reviewing raw SQL trace output I don't see a single
:
Reading Cary's Optimizing Oracle Performance, page
91 it says:
A database call with dep=n+1 is the recursive child
of the first SUBSEQUENT (empasis mine) dep=n database
call listed in the SQL trace data stream
Does this apply to the SQL issued from PL/SQL?
I am looking at the simple
.
Reviewing raw SQL trace output I don't see a single
commit or rollback there, but there are plenty of
XCTEND tx markers with rlbk=1 (after about every
SELECT statement). Is this normal? Does this mean that
this app rollbacks (implicitly?) after each of those
selects?
Thanks
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
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
attempt an
explanation inline.
Daniel Fink
Boris Dali wrote:
Reading Cary's Optimizing Oracle Performance,
page
91 it says:
A database call with dep=n+1 is the recursive
child
of the first SUBSEQUENT (empasis mine) dep=n
database
call listed in the SQL trace data stream
Does
I've got a third party package that connects to Oracle
via OCI and works in HTTP-like (stateless) fashion.
Reviewing raw SQL trace output I don't see a single
commit or rollback there, but there are plenty of
XCTEND tx markers with rlbk=1 (after about every
SELECT statement). Is this normal? Does
Reading Cary's Optimizing Oracle Performance, page
91 it says:
A database call with dep=n+1 is the recursive child
of the first SUBSEQUENT (empasis mine) dep=n database
call listed in the SQL trace data stream
Does this apply to the SQL issued from PL/SQL?
I am looking at the simple packaged
party package that connects to Oracle
via OCI and works in HTTP-like (stateless) fashion.
Reviewing raw SQL trace output I don't see a single
commit or rollback there, but there are plenty of
XCTEND tx markers with rlbk=1 (after about every
SELECT statement). Is this normal? Does this mean
package that connects to Oracle
via OCI and works in HTTP-like (stateless) fashion.
Reviewing raw SQL trace output I don't see a single
commit or rollback there, but there are plenty of
XCTEND tx markers with rlbk=1 (after about every
SELECT statement). Is this normal? Does this mean that
this app
did a google search and couldnt find anything worth
reading. other than the ones on hotsos any other good ones? namely ones on
traces other than 10053 and 10046?
Ive seen a few others mentioned but no details.
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 ones?
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 ?
There
Title: Message
Qs What is the Cause in particular (or in General) of
Time Difference between "cpu" "elapsed" Columns in the following Query
?
Qs Isthere Any Scope for improvement in the
following Query ?
Qs Is there any Best practise of working with Such
Tables ?
NOTE -
1) (tran_date
My guess will be that PHYRDS is the count of start i/o's. Each start i/o
read mutilblock_read_count blocks from the disk.
The data buffer that you read with each start i/o is the same: 8 blocks of
8k or 16 blocks of 4k. So you get the same number of start i/o's.
Yechiel Adar
Mehish
- Original
list,
i'm doing benchmarking using two DB's with different block size
i run a count(*) on a 17 million row table, and compare the sql_trace file
and the v$filestat stats..
the db was bounced before each test, the init.ora params were identical,
EXCEPT
in DB1 (4k block size) the muldiblock read
List:
We have a crystal report performing badly. (No! ,you say. You're shocked!)
The report has a visual basic front end.
Our developer wants to set sql trace in the VB code. It's not working.
When I tkprof her trace file, all that's in there is the ALTER SESSION SET
SQL_TRACE TRUE command
wants to set sql trace in the VB code. It's not working.
When I tkprof her trace file, all that's in there is the ALTER SESSION SET
SQL_TRACE TRUE command.
Is there some trick here? I don't know VB at all, so I don't know how to
advise her. She looked on the Microsoft site, but it was not helpful
Title: RE: set sql*trace VB/Crystal
I don't know vb either. However the trace file is likely generated on the server-side. She would not see the output. She might want to try alter session set autotrace on; instead. That way she should see the results. That is how it works in SQL*PLUS
Title: RE: set sql*trace VB/Crystal
If you want to see the trace do the following connected to the appropriate database:
SQL show parameters dump;
NAME TYPE VALUE
--- --
background_core_dump string partial
say. You're shocked!)
The report has a visual basic front end.
Our developer wants to set sql trace in the VB code.
It's not working.
When I tkprof her trace file, all that's in there is
the ALTER SESSION SET
SQL_TRACE TRUE command.
Is there some trick here? I don't know VB at all,
so
What connection are they using?
If they are using Oracle Object Of OLE I think there is a parameter that can
be set.
From: [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: set sql*trace VB/Crystal
Date: Mon, 12 Aug 2002 13:08
]
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
List:
We have a crystal report performing badly. (No! ,you say. You're shocked!)
The report has a visual basic front end.
Our developer wants to set sql trace in the VB code. It's not working.
When I tkprof her trace file, all that's
a crystal report performing badly. (No! ,you say. You're
shocked!)
The report has a visual basic front end.
Our developer wants to set sql trace in the VB code. It's not working.
When I tkprof her trace file, all that's in there is the ALTER SESSION
SET
SQL_TRACE TRUE command.
Is there some trick
hello Siva,
You can concatenate raw SQL_TRACE files as a single file, then upload this
file to itrprof.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, June 19, 2001 2:30 AM
Hello Gurus,
I was trying to pull distinct sql's and their
Hello Gurus,
I was trying to pull distinct sql's and their total execute count from a
bunch of 250 trace output files(output from tkprof trace files). How can I
achive this. What i need the cumulative sum of execute count from different
files for each sql's.
As u all know sql can extend more
i have take trace file with
SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial,TRUE).
and trace file size is the 5MB but outpu of tkproff 31KB . is it
normal.?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Arslan Bahar
INET: [EMAIL PROTECTED]
Fat
PROTECTED]]
Sent: 31 ìàÿ 2001 ã. 13:06
To: Multiple recipients of list ORACLE-L
Subject: SQL TRACE
i have take trace file with
SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial,TRUE).
and trace file size is the 5MB but outpu of tkproff
31KB
While working on application performance issues, I noticed significant
discrepancy in time reported by SQL Trace and actual time taken by the
application.
Total Elapsed time reported by SQL Trace was 180 seconds, but it took 500
seconds to run it. (It was a PL/SQL procedure. I just measured
59 matches
Mail list logo
