subject:"RE\: TKPROF"

Re: TKProf Analyzer

2004-01-23 Thread Pete Finnigan

Hi,

check out Mogens site, he has a PL/SQL tool for storing trace files
in a repository. Its called TraceFile Repository and was written by
Torben Holm and its free - have a look http://www.miracleas.dk/tools/Mir
TFR104.zip -t might be an alternative for you? - it has a web based
front end using htp and htf so is quite handy to view your trace files.

hope this helps

Kind regards

Pete
-- 
Pete Finnigan
email:[EMAIL PROTECTED]
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Pete Finnigan
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

RE: tkprof issues - was Performance Problem

2003-08-27 Thread Mladen Gogala

Title: Message

Raj,
can you do truss on that tkprof? It would be nice to see where exactly does
tkprof fail.
HP-UX
11 has truss.
--Mladen GogalaOracle DBA

-Original Message-From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
Jamadagni, RajendraSent: Wednesday, August 27, 2003 9:05
AMTo: Multiple recipients of list ORACLE-LSubject: RE:
tkprof issues - was Performance Problem
Swap is 16G, 1.2% used
RAM is 16G,
16 processors.
Raj

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-From: Mladen Gogala
[mailto:[EMAIL PROTECTED]Sent: Tuesday, August 26, 2003
6:25 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: tkprof issues - was Performance
Problem
Are you sure that your swap space is sufficient?
--Mladen GogalaOracle DBA

-Original Message-From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
Jamadagni, RajendraSent: Tuesday, August 26, 2003 5:59
PMTo: Multiple recipients of list ORACLE-LSubject:
tkprof issues - was Performance Problem
unable to allocate space of size 48 (couple of time
50).
run as root too so no ulimits ...
Raj

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-From: Mladen Gogala
[mailto:[EMAIL PROTECTED]Sent: Tuesday, August 26, 2003
5:50 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: Performance Problem
Nope, you're the first. What happened? Segmentation violation? If
that is so, I'd like to know, because
not all of my trace files are small.
--Mladen GogalaOracle DBA

Any application program on your system can tell you
where it is spending its time. Let it tell
you. Take a 10046 level-12 trace of *any* important, slow application program. Read http://www.hotsos.com/dnloads/1/timing-data/Oracle%20Operational%20Timin
g%20Data.pdf, or ask the list for details if you need
some help.
Cary Millsap Hotsos
Enterprises, Ltd. http://www.hotsos.com
Upcoming events: - Hotsos
Clinic 101 in Sydney - Hotsos Symposium 2004,
March 7-10 Dallas - Visit www.hotsos.com for
schedule details...
-Original Message- Burton, Laura Sent: Tuesday, August 26,
2003 2:30 PM To: Multiple recipients of list
ORACLE-L
No, I had read not to analyze the sys tables in the
'TIP' section of the book I am using as a
reference (Oracle Performance Tuning/Tips & Techniques). As I stated earlier, I also made sure that I
analyzed all the tables and indexes that were
involved, because I had read that leaving a
table 'un'analyzed would cause a performance hit.
Someone earlier had suggested doing the analyze during
an 'off' time. This I did not do. It was
done while everything was going on, so maybe that is why everything came to a standstill. Anyway I
want to try it again after I upgrade and do so

RE: tkprof issues - was Performance Problem

2003-08-27 Thread Jamadagni, Rajendra

Title: Message

Swap is 16G, 1.2% used
RAM is 16G,
16 processors.
Raj

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-From: Mladen Gogala
[mailto:[EMAIL PROTECTED]Sent: Tuesday, August 26, 2003 6:25
PMTo: Multiple recipients of list ORACLE-LSubject: RE:
tkprof issues - was Performance Problem
Are
you sure that your swap space is sufficient?
--Mladen GogalaOracle DBA

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 !

Any application program on your system can tell you
where it is spending its time. Let it tell you.
Take a 10046 level-12 trace of *any* important,
slow application program. Read http://www.hotsos.com/dnloads/1/timing-data/Oracle%20Operational%20Timin
g%20Data.pdf, or ask the list for details if you need
some help.
Cary Millsap Hotsos Enterprises,
Ltd. http://www.hotsos.com
Upcoming events: - Hotsos Clinic
101 in Sydney - Hotsos Symposium 2004, March
7-10 Dallas - Visit www.hotsos.com for schedule
details...
-Original Message- Burton, Laura Sent: Tuesday, August 26,
2003 2:30 PM To: Multiple recipients of list
ORACLE-L
No, I had read not to analyze the sys tables in the
'TIP' section of the book I am using as a
reference (Oracle Performance Tuning/Tips & Techniques). As I stated earlier, I also made sure that I
analyzed all the tables and indexes that were
involved, because I had read that leaving a
table 'un'analyzed would cause a performance hit.
Someone earlier had suggested doing the analyze during
an 'off' time. This I did not do. It was
done while everything was going on, so maybe that is why everything came to a standstill. Anyway I want
to try it again after I upgrade and do so when
others are not on.
If you know of any other gotcha's, please let me
know. I may not have picked up on it in my
research.
Someone else had responded about looking at systemic
things before attacking the code. I had
already done this and found that I needed to enlarge my sort area because the disk read ratio was a little
high. I also enlarged my shared pool
size. The stats I have been running since then to keep track of this are staying between 98 and 99% so I do
not think this is my problem now. Tho

RE: tkprof issues - was Performance Problem

2003-08-26 Thread Mladen Gogala

Title: Message

Are
you sure that your swap space is sufficient?
--Mladen GogalaOracle DBA

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-From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
Jamadagni, RajendraSent: Tuesday, August 26, 2003 4:54
PMTo: Multiple recipients of list ORACLE-LSubject:
RE: Performance Problem
Funny ...
I have tkprof give up analyzing a 4.2G tracefile on a
64bit platform. anyone else experienced this??
Raj
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- From:
Cary Millsap [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 26, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Performance Problem
Laura,
You might find the problem by checking the things you plan
to check, and by following the advice of the book
you're using. But the odds are very good that you
will not. At least not for a long time...
Any application program on your system can tell you where
it is spending its time. Let it tell you. Take a
10046 level-12 trace of *any* important, slow
application program. Read http://www.hotsos.com/dnloads/1/timing-data/Oracle%20Operational%20Timin
g%20Data.pdf, or ask the list for details if you need
some help.
Cary Millsap Hotsos Enterprises,
Ltd. http://www.hotsos.com
Upcoming events: - Hotsos Clinic
101 in Sydney - Hotsos Symposium 2004, March 7-10
Dallas - Visit www.hotsos.com for schedule
details...
-Original Message- Burton,
Laura Sent: Tuesday, August 26, 2003 2:30
PM To: Multiple recipients of list ORACLE-L

No, I had read not to analyze the sys tables in the 'TIP'
section of the book I am using as a reference
(Oracle Performance Tuning/Tips & Techniques). As I stated earlier, I also made sure that I
analyzed all the tables and indexes that were
involved, because I had read that leaving a table
'un'analyzed would cause a performance hit.
Someone earlier had suggested doing the analyze during an
'off' time. This I did not do. It was done
while everything was going on, so maybe that is
why everything came to a standstill. Anyway I want to try it
again after I upgrade and do so when others are not
on.
If you know of any other gotcha's, please let me
know. I may not have picked up on it in my
research.
Someone else had responded about looking at systemic
things before attacking the code. I had
already done this and found that I needed to enlarge my sort area because the disk read ratio was a little
high. I also enlarged my shared pool
size. The stats I have been running since then to keep track of this are staying between 98 and 99% so I do
not think this is my problem now. Those
changes did not make any difference to the
users. Even though the disk/memory read was not above 95%, it
was at 92% so that is probably why no performance gain
was noticed. We are using PL/SQL procedures
heavily. The stats on the Library Cache looked good though.
I read something this weekend about how using 'logical'
drives to separate the different files can cause a
performance hit. I am using logical
disks, and I plan to change when I can, but I'm not sure yet
how much that will help. I have redistributed some
of the rollback segments so that they are not all
located on the same disk. However since some
of the drives are logical, that may not ha

Re: tkprof output

2003-03-13 Thread Jonathan Lewis


Repeat but changing the 12 to a zero should work.
You will find, however, that any cursor that has
not closed when you stop tracing will not dump
its 'STAT' lines (including execution plan) to the
trace file.


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now available One-day tutorials:
  Cost Based Optimisation
  Trouble-shooting and Tuning
  Indexing Strategies

(see http://www.jlcomp.demon.co.uk/tutorial.html )

UK___April 8th
UK___April 22nd

Denmark May 21-23rd

USA_(FL)_May 2nd


Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

UK_(Manchester)_May
USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: 13 March 2003 15:49


> I use sys.dbms_system.set_ev( v_seid ,v_sernum ,10046,12 ,'') to
start
> tracing on 8.1.6 db . What should I do to stop tracing without
exiting out
> of session .
>
> Thanks,
> -ak
>
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

RE: tkprof output

2003-03-13 Thread Cary Millsap

sys.dbms_system.set_ev( v_seid, v_sernum, 10046, 0, '' )
 ^


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic 101, Mar 25-27 Oxford
- Hotsos Clinic 101, Apr  8-10 Chicago


-Original Message-
Sent: Thursday, March 13, 2003 9:49 AM
To: Multiple recipients of list ORACLE-L

I use sys.dbms_system.set_ev( v_seid ,v_sernum ,10046,12 ,'') to start
tracing on 8.1.6 db . What should I do to stop tracing without exiting out
of session .

Thanks,
-ak


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, March 12, 2003 9:07 AM


> An action with dep=n+1 (n>=0) for cursor #k is the recursive child of the
> next dep=n action for cursor #k that immediately follows in the trace
data.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Hotsos Clinic 101, Mar 25-27 Oxford
> - Hotsos Clinic 101, Apr  8-10 Chicago
>
>
> -Original Message-
> Sent: Wednesday, March 12, 2003 10:16 AM
> To: Multiple recipients of list ORACLE-L
>
> Thanks Jonathan,
> what is meaning of recursive depth ?  I see calls to cdef$, seq$
> tables/views  does it hint something .  I though procedure is using some
> sequence and these are internal calls to generate seq numbers . Is that
rite
> ?
>
> -ak
>
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Tuesday, March 11, 2003 2:54 PM
>
>
> >
> > Any SQL within a pl/sql block is recursive SQL
> > (user recursive, rather than SYS recursive) so
> > this time could simply be the cost of running
> > your application code.
> >
> > Unfortunately the tkprof output doesn't quote
> > the recursive depth of the SQL - however if
> > you identify possible suspects, you can check
> > back in the raw trace file for lines like:
> > PARSING IN CURSOR #N
> > there will be a bit in the line like
> > dep=n
> > If n is not zero, then this is a 'recursive'
> > cursor.
> >
> > Regards
> >
> > Jonathan Lewis
> > http://www.jlcomp.demon.co.uk
> >
> > Now available One-day tutorials:
> >   Cost Based Optimisation
> >   Trouble-shooting and Tuning
> >   Indexing Strategies
> >
> > (see http://www.jlcomp.demon.co.uk/tutorial.html )
> >
> > UK___March 19th
> > UK___April 8th
> > UK___April 22nd
> >
> > USA_(FL)_May 2nd
> >
> >
> > Next dates for the 3-day seminar:
> > (see http://www.jlcomp.demon.co.uk/seminar.html )
> >
> > UK_(Manchester)_May
> > USA_(CA, TX)_August
> >
> >
> > The Co-operative Oracle Users' FAQ
> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> >
> >
> > - Original Message -
> > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> > Sent: 11 March 2003 19:29
> >
> >
> > > I am running tkprof on event 10046 output and I see at the end .
> > elapsed
> > > time of 18 secs in recursive calls thats what surprises me . Do you
> > know
> > > what I should look at next and what can be done to reduce these
> > timings
> > >
> > > Thanks,
> > > -ak
> > >
> > >
> > >
> > >
> > > OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
> > >
> > > call count   cpuelapsed   disk  querycurrent
> > > rows
> > > --- --   -- -- -- --
> >   --
> > > 
> > > Parse   26  0.08   0.10  3 26  0
> > > 0
> > > Execute 37  0.04   0.08  0  4  6
> > > 2
> > > Fetch   32  1.13   2.14   5079   5189 70
> > > 30
> > > --- --   -- -- -- --
> >   --
> > > 
> > > total   95  1.25   2.32   5082   5219 76
> > > 32
> > >
> > > Misses in library cache during parse: 23
> > >
> > >
> > > OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
> > >
> > > call count   cpuelapsed   disk  querycurrent
> > > rows
> > > --- --   -- -- -- --
> >   --
> > > 
> > > Parse   41  0.03   0.05  0  0  0
> > > 0
> > > Execute541  4.40  12.30   1051   6442  63782
> > > 38712
> > > Fetch  550  3.23   6.31   3977  24298340
> > > 419
> > > --- --   -- -- -- --
> >   --
> > > 
> > > total 1132  7.66  18.66   5028  30740  64122
> > > 39131
> > >
> > > Misses in library cache during parse: 9
> > >
> > >   164  user  SQL statements in session.
> > >41  internal SQL statements in session.
> > >   205  SQL statements in session.
> > > 0  statements EXPLAINed in this session.
> > >
> > **
> > **
> > > 
> > > Trace file: ora_28633_ak.trc
> > > Trace file compatibility: 8.00.04
> > > Sort opt

Re: tkprof output

2003-03-13 Thread Igor Neyman

sys.dbms_system.set_ev( v_seid ,v_sernum ,10046, 0,'')

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, March 13, 2003 10:49 AM


> I use sys.dbms_system.set_ev( v_seid ,v_sernum ,10046,12 ,'') to start
> tracing on 8.1.6 db . What should I do to stop tracing without exiting out
> of session .
>
> Thanks,
> -ak
>
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Wednesday, March 12, 2003 9:07 AM
>
>
> > An action with dep=n+1 (n>=0) for cursor #k is the recursive child of
the
> > next dep=n action for cursor #k that immediately follows in the trace
> data.
> >
> >
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> >
> > Upcoming events:
> > - Hotsos Clinic 101, Mar 25-27 Oxford
> > - Hotsos Clinic 101, Apr  8-10 Chicago
> >
> >
> > -Original Message-
> > Sent: Wednesday, March 12, 2003 10:16 AM
> > To: Multiple recipients of list ORACLE-L
> >
> > Thanks Jonathan,
> > what is meaning of recursive depth ?  I see calls to cdef$, seq$
> > tables/views  does it hint something .  I though procedure is using some
> > sequence and these are internal calls to generate seq numbers . Is that
> rite
> > ?
> >
> > -ak
> >
> >
> > - Original Message -
> > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> > Sent: Tuesday, March 11, 2003 2:54 PM
> >
> >
> > >
> > > Any SQL within a pl/sql block is recursive SQL
> > > (user recursive, rather than SYS recursive) so
> > > this time could simply be the cost of running
> > > your application code.
> > >
> > > Unfortunately the tkprof output doesn't quote
> > > the recursive depth of the SQL - however if
> > > you identify possible suspects, you can check
> > > back in the raw trace file for lines like:
> > > PARSING IN CURSOR #N
> > > there will be a bit in the line like
> > > dep=n
> > > If n is not zero, then this is a 'recursive'
> > > cursor.
> > >
> > > Regards
> > >
> > > Jonathan Lewis
> > > http://www.jlcomp.demon.co.uk
> > >
> > > Now available One-day tutorials:
> > >   Cost Based Optimisation
> > >   Trouble-shooting and Tuning
> > >   Indexing Strategies
> > >
> > > (see http://www.jlcomp.demon.co.uk/tutorial.html )
> > >
> > > UK___March 19th
> > > UK___April 8th
> > > UK___April 22nd
> > >
> > > USA_(FL)_May 2nd
> > >
> > >
> > > Next dates for the 3-day seminar:
> > > (see http://www.jlcomp.demon.co.uk/seminar.html )
> > >
> > > UK_(Manchester)_May
> > > USA_(CA, TX)_August
> > >
> > >
> > > The Co-operative Oracle Users' FAQ
> > > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> > >
> > >
> > > - Original Message -
> > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> > > Sent: 11 March 2003 19:29
> > >
> > >
> > > > I am running tkprof on event 10046 output and I see at the end .
> > > elapsed
> > > > time of 18 secs in recursive calls thats what surprises me . Do you
> > > know
> > > > what I should look at next and what can be done to reduce these
> > > timings
> > > >
> > > > Thanks,
> > > > -ak
> > > >
> > > >
> > > >
> > > >
> > > > OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
> > > >
> > > > call count   cpuelapsed   disk  querycurrent
> > > > rows
> > > > --- --   -- -- -- --
> > >   --
> > > > 
> > > > Parse   26  0.08   0.10  3 26  0
> > > > 0
> > > > Execute 37  0.04   0.08  0  4  6
> > > > 2
> > > > Fetch   32  1.13   2.14   5079   5189 70
> > > > 30
> > > > --- --   -- -- -- --
> > >   --
> > > > 
> > > > total   95  1.25   2.32   5082   5219 76
> > > > 32
> > > >
> > > > Misses in library cache during parse: 23
> > > >
> > > >
> > > > OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
> > > >
> > > > call count   cpuelapsed   disk  querycurrent
> > > > rows
> > > > --- --   -- -- -- --
> > >   --
> > > > 
> > > > Parse   41  0.03   0.05  0  0  0
> > > > 0
> > > > Execute541  4.40  12.30   1051   6442  63782
> > > > 38712
> > > > Fetch  550  3.23   6.31   3977  24298340
> > > > 419
> > > > --- --   -- -- -- --
> > >   --
> > > > 
> > > > total 1132  7.66  18.66   5028  30740  64122
> > > > 39131
> > > >
> > > > Misses in library cache during parse: 9
> > > >
> > > >   164  user  SQL statements in session.
> > > >41  internal SQL statements in session.
> > > >   205  SQL statements in session.
> > > > 0  statements EXPLAINed in this session.
> > > >
> > >

Re: tkprof output

2003-03-13 Thread AK

also how can we check if  one particular session is being traced (10046 ) or
not  .  Basically I want to be sure that tracing is stopped for the session
and its not fillling up disk space .

-ak



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, March 12, 2003 9:07 AM


> An action with dep=n+1 (n>=0) for cursor #k is the recursive child of the
> next dep=n action for cursor #k that immediately follows in the trace
data.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Hotsos Clinic 101, Mar 25-27 Oxford
> - Hotsos Clinic 101, Apr  8-10 Chicago
>
>
> -Original Message-
> Sent: Wednesday, March 12, 2003 10:16 AM
> To: Multiple recipients of list ORACLE-L
>
> Thanks Jonathan,
> what is meaning of recursive depth ?  I see calls to cdef$, seq$
> tables/views  does it hint something .  I though procedure is using some
> sequence and these are internal calls to generate seq numbers . Is that
rite
> ?
>
> -ak
>
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Tuesday, March 11, 2003 2:54 PM
>
>
> >
> > Any SQL within a pl/sql block is recursive SQL
> > (user recursive, rather than SYS recursive) so
> > this time could simply be the cost of running
> > your application code.
> >
> > Unfortunately the tkprof output doesn't quote
> > the recursive depth of the SQL - however if
> > you identify possible suspects, you can check
> > back in the raw trace file for lines like:
> > PARSING IN CURSOR #N
> > there will be a bit in the line like
> > dep=n
> > If n is not zero, then this is a 'recursive'
> > cursor.
> >
> > Regards
> >
> > Jonathan Lewis
> > http://www.jlcomp.demon.co.uk
> >
> > Now available One-day tutorials:
> >   Cost Based Optimisation
> >   Trouble-shooting and Tuning
> >   Indexing Strategies
> >
> > (see http://www.jlcomp.demon.co.uk/tutorial.html )
> >
> > UK___March 19th
> > UK___April 8th
> > UK___April 22nd
> >
> > USA_(FL)_May 2nd
> >
> >
> > Next dates for the 3-day seminar:
> > (see http://www.jlcomp.demon.co.uk/seminar.html )
> >
> > UK_(Manchester)_May
> > USA_(CA, TX)_August
> >
> >
> > The Co-operative Oracle Users' FAQ
> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> >
> >
> > - Original Message -
> > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> > Sent: 11 March 2003 19:29
> >
> >
> > > I am running tkprof on event 10046 output and I see at the end .
> > elapsed
> > > time of 18 secs in recursive calls thats what surprises me . Do you
> > know
> > > what I should look at next and what can be done to reduce these
> > timings
> > >
> > > Thanks,
> > > -ak
> > >
> > >
> > >
> > >
> > > OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
> > >
> > > call count   cpuelapsed   disk  querycurrent
> > > rows
> > > --- --   -- -- -- --
> >   --
> > > 
> > > Parse   26  0.08   0.10  3 26  0
> > > 0
> > > Execute 37  0.04   0.08  0  4  6
> > > 2
> > > Fetch   32  1.13   2.14   5079   5189 70
> > > 30
> > > --- --   -- -- -- --
> >   --
> > > 
> > > total   95  1.25   2.32   5082   5219 76
> > > 32
> > >
> > > Misses in library cache during parse: 23
> > >
> > >
> > > OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
> > >
> > > call count   cpuelapsed   disk  querycurrent
> > > rows
> > > --- --   -- -- -- --
> >   --
> > > 
> > > Parse   41  0.03   0.05  0  0  0
> > > 0
> > > Execute541  4.40  12.30   1051   6442  63782
> > > 38712
> > > Fetch  550  3.23   6.31   3977  24298340
> > > 419
> > > --- --   -- -- -- --
> >   --
> > > 
> > > total 1132  7.66  18.66   5028  30740  64122
> > > 39131
> > >
> > > Misses in library cache during parse: 9
> > >
> > >   164  user  SQL statements in session.
> > >41  internal SQL statements in session.
> > >   205  SQL statements in session.
> > > 0  statements EXPLAINed in this session.
> > >
> > **
> > **
> > > 
> > > Trace file: ora_28633_ak.trc
> > > Trace file compatibility: 8.00.04
> > > Sort options: default
> > >
> > >1  session in tracefile.
> > >  164  user  SQL statements in trace file.
> > >   41  internal SQL statements in trace file.
> > >  205  SQL statements in trace file.
> > >  167  unique SQL statements in trace file.
> > > 5369  lines in trace file.
> > >
> > >
> > >
> > > --
> > > Please see the of

Re: tkprof output

2003-03-13 Thread AK

I use sys.dbms_system.set_ev( v_seid ,v_sernum ,10046,12 ,'') to start
tracing on 8.1.6 db . What should I do to stop tracing without exiting out
of session .

Thanks,
-ak


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, March 12, 2003 9:07 AM


> An action with dep=n+1 (n>=0) for cursor #k is the recursive child of the
> next dep=n action for cursor #k that immediately follows in the trace
data.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Hotsos Clinic 101, Mar 25-27 Oxford
> - Hotsos Clinic 101, Apr  8-10 Chicago
>
>
> -Original Message-
> Sent: Wednesday, March 12, 2003 10:16 AM
> To: Multiple recipients of list ORACLE-L
>
> Thanks Jonathan,
> what is meaning of recursive depth ?  I see calls to cdef$, seq$
> tables/views  does it hint something .  I though procedure is using some
> sequence and these are internal calls to generate seq numbers . Is that
rite
> ?
>
> -ak
>
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Tuesday, March 11, 2003 2:54 PM
>
>
> >
> > Any SQL within a pl/sql block is recursive SQL
> > (user recursive, rather than SYS recursive) so
> > this time could simply be the cost of running
> > your application code.
> >
> > Unfortunately the tkprof output doesn't quote
> > the recursive depth of the SQL - however if
> > you identify possible suspects, you can check
> > back in the raw trace file for lines like:
> > PARSING IN CURSOR #N
> > there will be a bit in the line like
> > dep=n
> > If n is not zero, then this is a 'recursive'
> > cursor.
> >
> > Regards
> >
> > Jonathan Lewis
> > http://www.jlcomp.demon.co.uk
> >
> > Now available One-day tutorials:
> >   Cost Based Optimisation
> >   Trouble-shooting and Tuning
> >   Indexing Strategies
> >
> > (see http://www.jlcomp.demon.co.uk/tutorial.html )
> >
> > UK___March 19th
> > UK___April 8th
> > UK___April 22nd
> >
> > USA_(FL)_May 2nd
> >
> >
> > Next dates for the 3-day seminar:
> > (see http://www.jlcomp.demon.co.uk/seminar.html )
> >
> > UK_(Manchester)_May
> > USA_(CA, TX)_August
> >
> >
> > The Co-operative Oracle Users' FAQ
> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> >
> >
> > - Original Message -
> > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> > Sent: 11 March 2003 19:29
> >
> >
> > > I am running tkprof on event 10046 output and I see at the end .
> > elapsed
> > > time of 18 secs in recursive calls thats what surprises me . Do you
> > know
> > > what I should look at next and what can be done to reduce these
> > timings
> > >
> > > Thanks,
> > > -ak
> > >
> > >
> > >
> > >
> > > OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
> > >
> > > call count   cpuelapsed   disk  querycurrent
> > > rows
> > > --- --   -- -- -- --
> >   --
> > > 
> > > Parse   26  0.08   0.10  3 26  0
> > > 0
> > > Execute 37  0.04   0.08  0  4  6
> > > 2
> > > Fetch   32  1.13   2.14   5079   5189 70
> > > 30
> > > --- --   -- -- -- --
> >   --
> > > 
> > > total   95  1.25   2.32   5082   5219 76
> > > 32
> > >
> > > Misses in library cache during parse: 23
> > >
> > >
> > > OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
> > >
> > > call count   cpuelapsed   disk  querycurrent
> > > rows
> > > --- --   -- -- -- --
> >   --
> > > 
> > > Parse   41  0.03   0.05  0  0  0
> > > 0
> > > Execute541  4.40  12.30   1051   6442  63782
> > > 38712
> > > Fetch  550  3.23   6.31   3977  24298340
> > > 419
> > > --- --   -- -- -- --
> >   --
> > > 
> > > total 1132  7.66  18.66   5028  30740  64122
> > > 39131
> > >
> > > Misses in library cache during parse: 9
> > >
> > >   164  user  SQL statements in session.
> > >41  internal SQL statements in session.
> > >   205  SQL statements in session.
> > > 0  statements EXPLAINed in this session.
> > >
> > **
> > **
> > > 
> > > Trace file: ora_28633_ak.trc
> > > Trace file compatibility: 8.00.04
> > > Sort options: default
> > >
> > >1  session in tracefile.
> > >  164  user  SQL statements in trace file.
> > >   41  internal SQL statements in trace file.
> > >  205  SQL statements in trace file.
> > >  167  unique SQL statements in trace file.
> > > 5369  lines in trace file.
> > >
> > >
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: htt

Re: tkprof output

2003-03-12 Thread AK

Thanks Jonathan for the information . Let me go through raw -trace file once
more to get info . Everytime I go through it I find something interesting .

-ak

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, March 12, 2003 8:54 AM


>
> The examples you have given are SYS-recursive,
>
> The call to cdef$ is Oracle looking for some information
> about constraints (one possibility is that you keep breaking
> a PK or UK constraint and Oracle has to keep looking up
> the name of the constraint because it doesn't cache constraint
> names).
>
> The call to seq$ usually appears because you are using
> a sequence with a very small, or no, CACHE set - so
> as you get the next value from the sequence, oracle has
> to bump the sequence high-water value and write it to
> disc.
>
> In general, the recursive depth simply tells you how
> far down the stack of calls your cursor is. For example
> (which may be wrong in detail, but right in gist) if you
> execute an anonymous pl/sql which runs an SQL statement
> that uses a sequence.nextval that happens to bump
> the sequence high-water, you would (probably see):
>
>
> anonymous pl/sqldep = 0
> SQL statementdep = 1
> update seq$ statementdep = 3
>
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Now available One-day tutorials:
>   Cost Based Optimisation
>   Trouble-shooting and Tuning
>   Indexing Strategies
>
> (see http://www.jlcomp.demon.co.uk/tutorial.html )
>
> UK___March 19th
> UK___April 8th
> UK___April 22nd
>
> USA_(FL)_May 2nd
>
>
> Next dates for the 3-day seminar:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> UK_(Manchester)_May
> USA_(CA, TX)_August
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: 12 March 2003 16:15
>
>
> > Thanks Jonathan,
> > what is meaning of recursive depth ?  I see calls to cdef$, seq$
> > tables/views  does it hint something .  I though procedure is using
> some
> > sequence and these are internal calls to generate seq numbers . Is
> that rite
> > ?
> >
> > -ak
> >
> >
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jonathan Lewis
>   INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: AK
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

RE: tkprof output

2003-03-12 Thread Cary Millsap

An action with dep=n+1 (n>=0) for cursor #k is the recursive child of the
next dep=n action for cursor #k that immediately follows in the trace data.


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic 101, Mar 25-27 Oxford
- Hotsos Clinic 101, Apr  8-10 Chicago


-Original Message-
Sent: Wednesday, March 12, 2003 10:16 AM
To: Multiple recipients of list ORACLE-L

Thanks Jonathan,
what is meaning of recursive depth ?  I see calls to cdef$, seq$
tables/views  does it hint something .  I though procedure is using some
sequence and these are internal calls to generate seq numbers . Is that rite
?

-ak


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, March 11, 2003 2:54 PM


>
> Any SQL within a pl/sql block is recursive SQL
> (user recursive, rather than SYS recursive) so
> this time could simply be the cost of running
> your application code.
>
> Unfortunately the tkprof output doesn't quote
> the recursive depth of the SQL - however if
> you identify possible suspects, you can check
> back in the raw trace file for lines like:
> PARSING IN CURSOR #N
> there will be a bit in the line like
> dep=n
> If n is not zero, then this is a 'recursive'
> cursor.
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Now available One-day tutorials:
>   Cost Based Optimisation
>   Trouble-shooting and Tuning
>   Indexing Strategies
>
> (see http://www.jlcomp.demon.co.uk/tutorial.html )
>
> UK___March 19th
> UK___April 8th
> UK___April 22nd
>
> USA_(FL)_May 2nd
>
>
> Next dates for the 3-day seminar:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> UK_(Manchester)_May
> USA_(CA, TX)_August
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: 11 March 2003 19:29
>
>
> > I am running tkprof on event 10046 output and I see at the end .
> elapsed
> > time of 18 secs in recursive calls thats what surprises me . Do you
> know
> > what I should look at next and what can be done to reduce these
> timings
> >
> > Thanks,
> > -ak
> >
> >
> >
> >
> > OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
> >
> > call count   cpuelapsed   disk  querycurrent
> > rows
> > --- --   -- -- -- --
>   --
> > 
> > Parse   26  0.08   0.10  3 26  0
> > 0
> > Execute 37  0.04   0.08  0  4  6
> > 2
> > Fetch   32  1.13   2.14   5079   5189 70
> > 30
> > --- --   -- -- -- --
>   --
> > 
> > total   95  1.25   2.32   5082   5219 76
> > 32
> >
> > Misses in library cache during parse: 23
> >
> >
> > OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
> >
> > call count   cpuelapsed   disk  querycurrent
> > rows
> > --- --   -- -- -- --
>   --
> > 
> > Parse   41  0.03   0.05  0  0  0
> > 0
> > Execute541  4.40  12.30   1051   6442  63782
> > 38712
> > Fetch  550  3.23   6.31   3977  24298340
> > 419
> > --- --   -- -- -- --
>   --
> > 
> > total 1132  7.66  18.66   5028  30740  64122
> > 39131
> >
> > Misses in library cache during parse: 9
> >
> >   164  user  SQL statements in session.
> >41  internal SQL statements in session.
> >   205  SQL statements in session.
> > 0  statements EXPLAINed in this session.
> >
> **
> **
> > 
> > Trace file: ora_28633_ak.trc
> > Trace file compatibility: 8.00.04
> > Sort options: default
> >
> >1  session in tracefile.
> >  164  user  SQL statements in trace file.
> >   41  internal SQL statements in trace file.
> >  205  SQL statements in trace file.
> >  167  unique SQL statements in trace file.
> > 5369  lines in trace file.
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: AK
> >   INET: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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 inf

Re: tkprof output

2003-03-12 Thread Jonathan Lewis


The examples you have given are SYS-recursive,

The call to cdef$ is Oracle looking for some information
about constraints (one possibility is that you keep breaking
a PK or UK constraint and Oracle has to keep looking up
the name of the constraint because it doesn't cache constraint
names).

The call to seq$ usually appears because you are using
a sequence with a very small, or no, CACHE set - so
as you get the next value from the sequence, oracle has
to bump the sequence high-water value and write it to
disc.

In general, the recursive depth simply tells you how
far down the stack of calls your cursor is. For example
(which may be wrong in detail, but right in gist) if you
execute an anonymous pl/sql which runs an SQL statement
that uses a sequence.nextval that happens to bump
the sequence high-water, you would (probably see):


anonymous pl/sqldep = 0
SQL statementdep = 1
update seq$ statementdep = 3


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now available One-day tutorials:
  Cost Based Optimisation
  Trouble-shooting and Tuning
  Indexing Strategies

(see http://www.jlcomp.demon.co.uk/tutorial.html )

UK___March 19th
UK___April 8th
UK___April 22nd

USA_(FL)_May 2nd


Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

UK_(Manchester)_May
USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: 12 March 2003 16:15


> Thanks Jonathan,
> what is meaning of recursive depth ?  I see calls to cdef$, seq$
> tables/views  does it hint something .  I though procedure is using
some
> sequence and these are internal calls to generate seq numbers . Is
that rite
> ?
>
> -ak
>
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Re: tkprof output

2003-03-12 Thread AK

Thanks Jonathan,
what is meaning of recursive depth ?  I see calls to cdef$, seq$
tables/views  does it hint something .  I though procedure is using some
sequence and these are internal calls to generate seq numbers . Is that rite
?

-ak


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, March 11, 2003 2:54 PM


>
> Any SQL within a pl/sql block is recursive SQL
> (user recursive, rather than SYS recursive) so
> this time could simply be the cost of running
> your application code.
>
> Unfortunately the tkprof output doesn't quote
> the recursive depth of the SQL - however if
> you identify possible suspects, you can check
> back in the raw trace file for lines like:
> PARSING IN CURSOR #N
> there will be a bit in the line like
> dep=n
> If n is not zero, then this is a 'recursive'
> cursor.
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Now available One-day tutorials:
>   Cost Based Optimisation
>   Trouble-shooting and Tuning
>   Indexing Strategies
>
> (see http://www.jlcomp.demon.co.uk/tutorial.html )
>
> UK___March 19th
> UK___April 8th
> UK___April 22nd
>
> USA_(FL)_May 2nd
>
>
> Next dates for the 3-day seminar:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> UK_(Manchester)_May
> USA_(CA, TX)_August
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: 11 March 2003 19:29
>
>
> > I am running tkprof on event 10046 output and I see at the end .
> elapsed
> > time of 18 secs in recursive calls thats what surprises me . Do you
> know
> > what I should look at next and what can be done to reduce these
> timings
> >
> > Thanks,
> > -ak
> >
> >
> >
> >
> > OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
> >
> > call count   cpuelapsed   disk  querycurrent
> > rows
> > --- --   -- -- -- --
>   --
> > 
> > Parse   26  0.08   0.10  3 26  0
> > 0
> > Execute 37  0.04   0.08  0  4  6
> > 2
> > Fetch   32  1.13   2.14   5079   5189 70
> > 30
> > --- --   -- -- -- --
>   --
> > 
> > total   95  1.25   2.32   5082   5219 76
> > 32
> >
> > Misses in library cache during parse: 23
> >
> >
> > OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
> >
> > call count   cpuelapsed   disk  querycurrent
> > rows
> > --- --   -- -- -- --
>   --
> > 
> > Parse   41  0.03   0.05  0  0  0
> > 0
> > Execute541  4.40  12.30   1051   6442  63782
> > 38712
> > Fetch  550  3.23   6.31   3977  24298340
> > 419
> > --- --   -- -- -- --
>   --
> > 
> > total 1132  7.66  18.66   5028  30740  64122
> > 39131
> >
> > Misses in library cache during parse: 9
> >
> >   164  user  SQL statements in session.
> >41  internal SQL statements in session.
> >   205  SQL statements in session.
> > 0  statements EXPLAINed in this session.
> >
> **
> **
> > 
> > Trace file: ora_28633_ak.trc
> > Trace file compatibility: 8.00.04
> > Sort options: default
> >
> >1  session in tracefile.
> >  164  user  SQL statements in trace file.
> >   41  internal SQL statements in trace file.
> >  205  SQL statements in trace file.
> >  167  unique SQL statements in trace file.
> > 5369  lines in trace file.
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: AK
> >   INET: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: Jonathan Lewis
>   INET: [EMAIL PROTECTED]
>
> 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

Re: tkprof output

2003-03-11 Thread Jonathan Lewis


Any SQL within a pl/sql block is recursive SQL
(user recursive, rather than SYS recursive) so
this time could simply be the cost of running
your application code.

Unfortunately the tkprof output doesn't quote
the recursive depth of the SQL - however if
you identify possible suspects, you can check
back in the raw trace file for lines like:
PARSING IN CURSOR #N
there will be a bit in the line like
dep=n
If n is not zero, then this is a 'recursive'
cursor.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now available One-day tutorials:
  Cost Based Optimisation
  Trouble-shooting and Tuning
  Indexing Strategies

(see http://www.jlcomp.demon.co.uk/tutorial.html )

UK___March 19th
UK___April 8th
UK___April 22nd

USA_(FL)_May 2nd


Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

UK_(Manchester)_May
USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: 11 March 2003 19:29


> I am running tkprof on event 10046 output and I see at the end .
elapsed
> time of 18 secs in recursive calls thats what surprises me . Do you
know
> what I should look at next and what can be done to reduce these
timings
>
> Thanks,
> -ak
>
>
>
>
> OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
>
> call count   cpuelapsed   disk  querycurrent
> rows
> --- --   -- -- -- --
  --
> 
> Parse   26  0.08   0.10  3 26  0
> 0
> Execute 37  0.04   0.08  0  4  6
> 2
> Fetch   32  1.13   2.14   5079   5189 70
> 30
> --- --   -- -- -- --
  --
> 
> total   95  1.25   2.32   5082   5219 76
> 32
>
> Misses in library cache during parse: 23
>
>
> OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
>
> call count   cpuelapsed   disk  querycurrent
> rows
> --- --   -- -- -- --
  --
> 
> Parse   41  0.03   0.05  0  0  0
> 0
> Execute541  4.40  12.30   1051   6442  63782
> 38712
> Fetch  550  3.23   6.31   3977  24298340
> 419
> --- --   -- -- -- --
  --
> 
> total 1132  7.66  18.66   5028  30740  64122
> 39131
>
> Misses in library cache during parse: 9
>
>   164  user  SQL statements in session.
>41  internal SQL statements in session.
>   205  SQL statements in session.
> 0  statements EXPLAINed in this session.
>
**
**
> 
> Trace file: ora_28633_ak.trc
> Trace file compatibility: 8.00.04
> Sort options: default
>
>1  session in tracefile.
>  164  user  SQL statements in trace file.
>   41  internal SQL statements in trace file.
>  205  SQL statements in trace file.
>  167  unique SQL statements in trace file.
> 5369  lines in trace file.
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: AK
>   INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: Jonathan Lewis
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Re: tkprof output

2003-03-11 Thread Jared . Still

Using sys=yes on the tkprof command line would be a good start.

That way you will see which statements are generating all the recursive 
SQL.

Jared






"AK" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 03/11/2003 11:29 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:tkprof output


I am running tkprof on event 10046 output and I see at the end . elapsed
time of 18 secs in recursive calls thats what surprises me . Do you know
what I should look at next and what can be done to reduce these timings

Thanks,
-ak




OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- -- 
--

Parse   26  0.08   0.10  3 26  0
0
Execute 37  0.04   0.08  0  4  6
2
Fetch   32  1.13   2.14   5079   5189 70
30
--- --   -- -- -- -- 
--

total   95  1.25   2.32   5082   5219 76
32

Misses in library cache during parse: 23


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- -- 
--

Parse   41  0.03   0.05  0  0  0
0
Execute541  4.40  12.30   1051   6442  63782
38712
Fetch  550  3.23   6.31   3977  24298340
419
--- --   -- -- -- -- 
--

total 1132  7.66  18.66   5028  30740  64122
39131

Misses in library cache during parse: 9

  164  user  SQL statements in session.
   41  internal SQL statements in session.
  205  SQL statements in session.
0  statements EXPLAINed in this session.


Trace file: ora_28633_ak.trc
Trace file compatibility: 8.00.04
Sort options: default

   1  session in tracefile.
 164  user  SQL statements in trace file.
  41  internal SQL statements in trace file.
 205  SQL statements in trace file.
 167  unique SQL statements in trace file.
5369  lines in trace file.



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: AK
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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: 
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Re: tkprof output

2003-03-11 Thread Igor Neyman

Run tkprof with "SYS = YES" option and find recursive calls that contribute
to this time.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, March 11, 2003 2:29 PM


> I am running tkprof on event 10046 output and I see at the end . elapsed
> time of 18 secs in recursive calls thats what surprises me . Do you know
> what I should look at next and what can be done to reduce these timings
>
> Thanks,
> -ak
>
>
>
>
> OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
>
> call count   cpuelapsed   disk  querycurrent
> rows
> --- --   -- -- -- --  
--
> 
> Parse   26  0.08   0.10  3 26  0
> 0
> Execute 37  0.04   0.08  0  4  6
> 2
> Fetch   32  1.13   2.14   5079   5189 70
> 30
> --- --   -- -- -- --  
--
> 
> total   95  1.25   2.32   5082   5219 76
> 32
>
> Misses in library cache during parse: 23
>
>
> OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
>
> call count   cpuelapsed   disk  querycurrent
> rows
> --- --   -- -- -- --  
--
> 
> Parse   41  0.03   0.05  0  0  0
> 0
> Execute541  4.40  12.30   1051   6442  63782
> 38712
> Fetch  550  3.23   6.31   3977  24298340
> 419
> --- --   -- -- -- --  
--
> 
> total 1132  7.66  18.66   5028  30740  64122
> 39131
>
> Misses in library cache during parse: 9
>
>   164  user  SQL statements in session.
>41  internal SQL statements in session.
>   205  SQL statements in session.
> 0  statements EXPLAINed in this session.
>

> 
> Trace file: ora_28633_ak.trc
> Trace file compatibility: 8.00.04
> Sort options: default
>
>1  session in tracefile.
>  164  user  SQL statements in trace file.
>   41  internal SQL statements in trace file.
>  205  SQL statements in trace file.
>  167  unique SQL statements in trace file.
> 5369  lines in trace file.
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: AK
>   INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: Igor Neyman
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Re: tkprof on 901 database

2003-01-31 Thread Connor McDonald

nope - its an oversight of oracle's.  You can upgrade
to 9.2 (which I'd recommend because its so much
better) or it might even be in one of the 9.0 patches.
 Using the 817 or 92 tkprof binary also seems to work
effectively.

hth
connor

 --- Randy Pace <[EMAIL PROTECTED]> wrote: > I have a
9.01 database installed on my laptop and
> cannot find the
> tkprof.exe. Has it been renamed or is it part of an
> installation that I did
> not do?
> 
> Thanks for any and all replys.
> 
> Randy R. Pace
> Development Team Leader
> 
> Accela, Inc. 
> 9662 South 700 East
> Sandy
> UT, 84070
> 
> Tel: 801 495 9300 
> Fax: 801 495 9301
> 
> www.accela.com  
> 
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: Randy Pace
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).
>  

=
Connor McDonald
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: [EMAIL PROTECTED]

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will 
sit in a boat and drink beer all day"

__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Re: tkprof analysis

2002-05-30 Thread Tim Gorman


Oracle8i Server Tuning guide (part #A76992, available for free download from
http://docs.oracle.com), pages 6-22 through 6-30 for TKPROF output example

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, May 30, 2002 7:38 AM


> Hi,
>
> Can any one point me or kindly send me the analysis of
> TKProf outout please.
>
> Thanks you so much for your help.
>
> Ravi.
>
> __
> Do You Yahoo!?
> Everything you'll ever need on one web page
> from News and Sport to Email and Music Charts
> http://uk.my.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: =?iso-8859-1?q?Nalla=20Ravi?=
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (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.com
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

RE: TKPROF?

2002-04-11 Thread Paul Baumgartel


I thought it was Trace Kernel Profiler.

PB

--- "Deshpande, Kirti" <[EMAIL PROTECTED]> wrote:
> Transient Kernel Profile.
> 
> - Kirti 
> 
> 
> -Original Message-
> Sent: Wednesday, April 10, 2002 8:38 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> What does it stand for? Tool Kit Profiler???
> 
> tia,
> 
> David Nemeth
> 
> __
> Do You Yahoo!?
> Yahoo! Tax Center - online filing with TurboTax
> http://taxes.yahoo.com/
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: David Nemeth
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (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.com
> -- 
> Author: Deshpande, Kirti
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (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).


__
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Paul Baumgartel
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

Re: TKPROF?

2002-04-11 Thread Ora NT DBA

I have heard two different ones, no idea if either is correct :-)

trace kernel profiler
transient kernel profiler

John

[EMAIL PROTECTED] wrote:

>What does it stand for? Tool Kit Profiler???
>
>tia,
>
>David Nemeth
>
>__
>Do You Yahoo!?
>Yahoo! Tax Center - online filing with TurboTax
>http://taxes.yahoo.com/
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ora NT DBA
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

RE: TKPROF?

2002-04-10 Thread K Gopalakrishnan


Anjo:

I always thought it Trace Kernel PROFile?


Best Regards,
K Gopalakrishnan
Bangalore, INDIA



-Original Message-
Sent: Wednesday, April 10, 2002 8:23 PM
To: Multiple recipients of list ORACLE-L


Tool Kernel PROFiler

Anjo.

David Nemeth wrote:

> What does it stand for? Tool Kit Profiler???
>
> tia,
>
> David Nemeth
>
> __
> Do You Yahoo!?
> Yahoo! Tax Center - online filing with TurboTax
> http://taxes.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: David Nemeth
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (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.com
-- 
Author: Anjo Kolk
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.com
-- 
Author: K Gopalakrishnan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

Re: TKPROF?

2002-04-10 Thread Anjo Kolk


Tool Kernel PROFiler

Anjo.

David Nemeth wrote:

> What does it stand for? Tool Kit Profiler???
>
> tia,
>
> David Nemeth
>
> __
> Do You Yahoo!?
> Yahoo! Tax Center - online filing with TurboTax
> http://taxes.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: David Nemeth
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (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.com
-- 
Author: Anjo Kolk
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

RE: TKPROF?

2002-04-10 Thread Deshpande, Kirti


Transient Kernel Profile.

- Kirti 


-Original Message-
Sent: Wednesday, April 10, 2002 8:38 PM
To: Multiple recipients of list ORACLE-L


What does it stand for? Tool Kit Profiler???

tia,

David Nemeth

__
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: David Nemeth
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

Re: tkprof plan missing rows

2002-03-13 Thread Mohammed Shakir


Do you have data in the tables that you are running the query againt?
You do have row count zero, so tkprof seems to count the rows but the
rows do not seem to be there or are not selected.

--- "Baker, Barbara" <[EMAIL PROTECTED]> wrote:
> 
> Oracle 7.3.4, OpenVMS 7.1
> 
> My tkprof report is missing the row count in the execution plan. 
> Does
> anyone know why what I might be missing?
> If I autotrace the same query in the same database, I do get
> cardinality.  
> The tables have been analyzed.
> 
> If I tkprof another database using the same version and OS, that
> tkprof DOES
> have row counts.
> I believe I'm just missing a parameter somewhere, but I don't know
> where.
> 
> Thanks for any ideas.
> Barb
> 
> $ tkprof DRAX02_AMPROD_FG_SRV_041.TRC;1 sel.tkp  explain=user/pwd
> sys=no
> 
> *
> ***
> 
> 
> Rows Execution Plan
> ---  ---
>   0  SELECT STATEMENT   GOAL: CHOOSE
>   0   TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'WO'
>   0INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'PK_WO' (UNIQUE)
> 
>

> 
> 
> 
> here's a tkprof from a different database:
> 
> Rows Execution Plan
> ---  ---
>   0  SELECT STATEMENT   GOAL: CHOOSE
> 1440212   SORT (GROUP BY)
> 2785044HASH JOIN
> 3109095 TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF
> 'WORK_ORDER_DETAILS'
> 3762491  INDEX   GOAL: ANALYZED (RANGE SCAN) OF
>  'WORK_ORDER_DETAILS_IDX3' (NON-UNIQUE)
> 5727880 TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'SLS_OF_REC'
> 
>

> 
>   
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Baker, Barbara
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (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).
> 

> ATTACHMENT part 2 application/ms-tnef name=winmail.dat



=
Mohammed Shakir
CompuSoft, Inc.
11 Heather Way
East Brunswick, NJ 08816-2825
(732) 672-0464 (Cell)
(732) 257-6001 (Home)

__
Do You Yahoo!?
Yahoo! Sports - live college hoops coverage
http://sports.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mohammed Shakir
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

Re: tkprof plan missing rows

2002-03-08 Thread Jonathan Lewis



It usually means the cursor for that query
was not closed before the end of file (e.g.
SQL in pl/sql and you didn't do an exit
to get out of sql*plus) so Oracle never
got around to dumping the STAT lines.


Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 08 March 2002 18:15


|
|Oracle 7.3.4, OpenVMS 7.1
|
|My tkprof report is missing the row count in the execution plan.
Does
|anyone know why what I might be missing?
|If I autotrace the same query in the same database, I do get
cardinality.
|The tables have been analyzed.
|
|If I tkprof another database using the same version and OS, that
tkprof DOES
|have row counts.
|I believe I'm just missing a parameter somewhere, but I don't know
where.
|
|Thanks for any ideas.
|Barb
|
|$ tkprof DRAX02_AMPROD_FG_SRV_041.TRC;1 sel.tkp  explain=user/pwd
|sys=no
|
|*
|***
|
|
|Rows Execution Plan
|---  ---
|  0  SELECT STATEMENT   GOAL: CHOOSE
|  0   TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'WO'
|  0INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'PK_WO' (UNIQUE)
|
|*
***
|
|

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

RE: Tkprof output

2002-02-22 Thread Baker, Barbara


Dennis:
In the session:
alter session set events '10046 trace name context forever, level
12'


Barb

> --
> From: DENNIS WILLIAMS[SMTP:[EMAIL PROTECTED]]
> Reply To: [EMAIL PROTECTED]
> Sent: Friday, February 22, 2002 3:08 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:  RE: Tkprof output
> 
> Paul (or anyone) - 
>   Is there a way to set the level within the session as:
>alter session set sql_trace = true
>   Or do I have to start the session, start a SQLPLUS session, find the
> session_id and run the procedure to set it on another session? 
> Dennis Williams
> DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]
> 
> 
> -Original Message-
> Sent: Friday, February 22, 2002 3:23 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Set you 10046 trace at level 4 or 12, and then check the trace file.
> 
> Paul
> 
> 
> -Original Message-
> Sent: Friday, February 22, 2002 3:53 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hi Listers,
> 
> I got the following statement in the tkprof output file :
> 
> update EMP set "ENAME"=:V001,"EMPNO"=:V002
> where rowid = :V003
> 
> I'm just wondering how to get the value of that variables (instead of
> :V001,
> 
> :V002 and :V003).
> Is there a way to do that ?
> Thanks.
> 
> Aldi
> 
> _
> Send and receive Hotmail on your mobile device: http://mobile.msn.com
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Aldi Barco
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (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.com
> -- 
> Author: 
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (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.com
> -- 
> Author: DENNIS WILLIAMS
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (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.com
-- 
Author: Baker, Barbara
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

RE: Tkprof output

2002-02-22 Thread DENNIS WILLIAMS


Paul (or anyone) - 
Is there a way to set the level within the session as:
 alter session set sql_trace = true
Or do I have to start the session, start a SQLPLUS session, find the
session_id and run the procedure to set it on another session? 
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Friday, February 22, 2002 3:23 PM
To: Multiple recipients of list ORACLE-L


Set you 10046 trace at level 4 or 12, and then check the trace file.

Paul


-Original Message-
Sent: Friday, February 22, 2002 3:53 PM
To: Multiple recipients of list ORACLE-L


Hi Listers,

I got the following statement in the tkprof output file :

update EMP set "ENAME"=:V001,"EMPNO"=:V002
where rowid = :V003

I'm just wondering how to get the value of that variables (instead of :V001,

:V002 and :V003).
Is there a way to do that ?
Thanks.

Aldi

_
Send and receive Hotmail on your mobile device: http://mobile.msn.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Aldi Barco
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.com
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

RE: Tkprof output

2002-02-22 Thread Paul . Parker


Set you 10046 trace at level 4 or 12, and then check the trace file.

Paul


-Original Message-
Sent: Friday, February 22, 2002 3:53 PM
To: Multiple recipients of list ORACLE-L


Hi Listers,

I got the following statement in the tkprof output file :

update EMP set "ENAME"=:V001,"EMPNO"=:V002
where rowid = :V003

I'm just wondering how to get the value of that variables (instead of :V001,

:V002 and :V003).
Is there a way to do that ?
Thanks.

Aldi

_
Send and receive Hotmail on your mobile device: http://mobile.msn.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Aldi Barco
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

RE: Tkprof.

2002-02-05 Thread C.S.Venkata Subramanian


Ayyappan -->RTFM
--

On Mon, 04 Feb 2002 22:15:19  
 Rajesh Dayal wrote:
>Just type tkprof on command line (DOS) or on shell prompt (UNIX),
>
>You would find the help and all your doubts would be cleared. 
>
>HTH,
>Rajesh
>-Original Message-
>Sent: Tuesday, February 05, 2002 9:15 AM
>To: Multiple recipients of list ORACLE-L
>
>Hi all
>
>I am having a doubt in how to use Tkprof.
>
>
>This communication contains information, which is confidential and may also
>be privileged. It is for the exclusive use of the intended recipient(s). If
>you are not the intended recipient(s), please note that any distribution,
>printing, copying or use of this communication or the information in it is
>strictly prohibited. If you have received this communication in error,
>please notify the sender immediately and then destroy any copies of it.
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Ayyappan S
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (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.com
>-- 
>Author: Rajesh Dayal
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (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.com
-- 
Author: C.S.Venkata Subramanian
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

Re: Tkprof.

2002-02-05 Thread Bjørn Engsig


If you are in to doing just moderately interesting performance 
investigation and tuning, I would highly recommend learning how to use 
the raw trace file.  The aggregation that tkprof does has a tendency to 
hide information rather than providing much more than the raw file.

Thanks, Bjørn.

[EMAIL PROTECTED] wrote:

>Check OTN.  Signup is free, , they've never hassled or
>spammed me, and the documentation is invaluable.
>
>
>David A. Barbour
>Oracle DBA, OCP
>AISD
>512-414-1002
>
>
>  
> 
>Rajesh Dayal  
> 
><[EMAIL PROTECTED]>   
>ecom.com>cc:  
>     
>Sent by: Subject: RE: Tkprof. 
> 
>[EMAIL PROTECTED]
> 
>om
> 
>  
> 
>  
> 
>02/05/2002
> 
>12:15 AM  
> 
>Please respond
> 
>to ORACLE-L   
> 
>  
> 
>  
> 
>
>
>
>
>Just type tkprof on command line (DOS) or on shell prompt (UNIX),
>
>You would find the help and all your doubts would be cleared.
>
>HTH,
>Rajesh
>-Original Message-
>Sent: Tuesday, February 05, 2002 9:15 AM
>To: Multiple recipients of list ORACLE-L
>
>Hi all
>
>I am having a doubt in how to use Tkprof.
>
>
>
>This communication contains information, which is confidential and may also
>be privileged. It is for the exclusive use of the intended recipient(s). If
>you are not the intended recipient(s), please note that any distribution,
>printing, copying or use of this communication or the information in it is
>strictly prohibited. If you have received this communication in error,
>please notify the sender immediately and then destroy any copies of it.
>
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Ayyappan S
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (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.com
>--
>Author: Rajesh Dayal
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (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.com
-- 
Author: =?ISO-8859-1?Q?Bj=F8rn?= Engsig
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051

RE: Tkprof.

2002-02-05 Thread DBarbour



Check OTN.  Signup is free, , they've never hassled or
spammed me, and the documentation is invaluable.


David A. Barbour
Oracle DBA, OCP
AISD
512-414-1002


   

Rajesh Dayal   

   
ecom.com>cc:   

Sent by: Subject: RE: Tkprof.  

[EMAIL PROTECTED] 

om 

   

   

02/05/2002 

12:15 AM   

Please respond 

to ORACLE-L

   

   





Just type tkprof on command line (DOS) or on shell prompt (UNIX),

You would find the help and all your doubts would be cleared.

HTH,
Rajesh
-Original Message-
Sent: Tuesday, February 05, 2002 9:15 AM
To: Multiple recipients of list ORACLE-L

Hi all

I am having a doubt in how to use Tkprof.



This communication contains information, which is confidential and may also
be privileged. It is for the exclusive use of the intended recipient(s). If
you are not the intended recipient(s), please note that any distribution,
printing, copying or use of this communication or the information in it is
strictly prohibited. If you have received this communication in error,
please notify the sender immediately and then destroy any copies of it.



--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ayyappan S
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.com
--
Author: Rajesh Dayal
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

RE: Tkprof.

2002-02-04 Thread Rajesh Dayal


Just type tkprof on command line (DOS) or on shell prompt (UNIX),

You would find the help and all your doubts would be cleared. 

HTH,
Rajesh
-Original Message-
Sent: Tuesday, February 05, 2002 9:15 AM
To: Multiple recipients of list ORACLE-L

Hi all

I am having a doubt in how to use Tkprof.


This communication contains information, which is confidential and may also
be privileged. It is for the exclusive use of the intended recipient(s). If
you are not the intended recipient(s), please note that any distribution,
printing, copying or use of this communication or the information in it is
strictly prohibited. If you have received this communication in error,
please notify the sender immediately and then destroy any copies of it.


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ayyappan S
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.com
-- 
Author: Rajesh Dayal
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

RE: tkprof Trace Output Evaluation

2001-10-01 Thread VIVEK_SHARMA



A Small Addition :-
SST Table only has 50 Records with index on (set_id,sol_id)

Qs. Under the Column "count" , Is a SMALL Value of "Execute" ( = 2)
Versus a
Large Value of "Fetch" ( = 2525 ) considered Good or Expensive for
performance ?
Qs. What does the above mean to indicate ? 
I am Quite basic in tkprof evaluation .
I have Generally Seen "count-Execute" EQUAL to "count-Fetch" , for Other
Traces

Thanks so much


> -Original Message-
> From: Bill Zakrzewski [SMTP:[EMAIL PROTECTED]]
> Sent: Tuesday, October 02, 2001 1:59 AM
> To:   LazyDBA.com Discussion; VIVEK_SHARMA
> Subject:  Re: tkprof Trace Output Evaluation 
> 
> I quickly looked at your query and one improvement you can make is to
> remove
> the
> 
>  "WHERE  B.sol_id
> in (
>   SELECT sol_id from SST WHERE set_id =  :1  ) "
> 
> and replace it with
> 
> WHERE exists (select 'x' from SST where sol_id=B.sol_id
>  and set_id  = :1)
> 
> 
> - Original Message -
> From: "VIVEK_SHARMA" <[EMAIL PROTECTED]>
> To: "LazyDBA.com Discussion" <[EMAIL PROTECTED]>
> Sent: Monday, October 01, 2001 4:18 PM
> Subject: tkprof Trace Output Evaluation
> 
> 
> 
> 
> Under the Column "count" , Is a SMALL Value of "Execute" ( = 2) Versus
> a
> Large Value of "Fetch" ( = 2525 ) considered Good or Expensive for
> performance ?
> 
> NOTE -
> TBA_FLEXI_FIXED_PARM_TBL Contains 600,000 records only
> 2 indexes exist on TBA_FLEXI_FIXED_PARM_TBL
> Index 1 = UNIQUE index on acid
> Index 2 = Index on sol_id
> 
> TBA_GENERAL_ACCT_MAST_TBL Contains about 11,000,000 records
> 
> Following is the Detailed Trace :-
> 
> **
> **
> 
> 
> SELECT A.ACID
> FROM
>  TBA_FLEXI_FIXED_PARM_TBL A, TBA_GENERAL_ACCT_MAST_TBL B WHERE
> B.sol_id
> in (
>   SELECT sol_id from SST WHERE set_id =  :1  ) AND B.ACID = A.ACID AND
>   ( A.NEXT_AUTO_CR_DATE IS NULL OR A.NEXT_AUTO_CR_DATE <= TO_DATE(
> :2 ,
>   'DD-MM- HH24:MI:SS') OR (   A.AUTO_CR_FREQ_HLDY_STAT = 'P' AND
>   A.NEXT_AUTO_CR_DATE >TO_DATE( :3 , 'DD-MM- HH24:MI:SS') AND
>   A.NEXT_AUTO_CR_DATE  AND
>   A.DEL_FLG != 'Y' AND A.ENTITY_CRE_FLG = 'Y' AND
>   A.AUTO_CR_FROM_OPER_ACCT_FLG = 'Y'   ORDER BY B.CRNCY_CODE,
>   B.ACCT_CRNCY_CODE, B.SCHM_CODE, B.FORACID
> 
> 
> call count   cpuelapsed   disk  querycurrent
> rows
> --- --   -- -- -- --
> --
> Parse1  0.01   0.01  0  0  0
> 0
> Execute  2  0.01   0.01  0  0  0
> 0
> Fetch 2525 78.62 111.15  774412538463 11
> 25248
> --- --   -- -- -- --
> --
> total 2528 78.64 111.17  774412538463 11
> 25248
> 
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 20  (TBAGEN)
> 
> Rows Execution Plan
> ---  ---
>   0  SELECT STATEMENT   GOAL: CHOOSE
>   0   SORT (ORDER BY)
>   0NESTED LOOPS
>   0 NESTED LOOPS
>   0  PARTITION RANGE (ALL) PARTITION: START=1 STOP=2
>   0   TABLE ACCESS   GOAL: ANALYZED (FULL) OF
>   'FLEXI_FIXED_PARM_TABLE' PARTITION: START=1 STOP=2
>   0  TABLE ACCESS   GOAL: ANALYZED (BY GLOBAL INDEX ROWID) OF
>  'GENERAL_ACCT_MAST_TABLE' PARTITION:ROW LOCATION
>   0   INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF
>   'IDX_GENERAL_ACCT_MAST_TABLE' (UNIQUE)
>   0 VIEW OF 'VW_NSO_1'
>   0  SORT (UNIQUE)
>   0   INDEX   GOAL: ANALYZED (RANGE SCAN) OF
>   'IDX_SOL_ID_SET_TABLE' (UNIQUE)
> 
> **
> **
> 
> 
> 
> 
> 
> Oracle documentation is here:
> http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
> To unsubscribe: send a blank email to
> [EMAIL PROTECTED]
> To subscribe:   send a blank email to [EMAIL PROTECTED]
> Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
> Tell yer mates about http://www.farAwayJobs.com
> By using this list you agree to these
> terms:http://www.lazydba.com/legal.html
> 
> 
> 
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: VIVEK_SHARMA
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

RE: Tkprof output

2001-08-31 Thread Robertson Lee - lerobe

Title: RE: Tkprof output



A huge 
thanks for all those who took the time out to respond to my problem. The query 
ran to completion yesterday and (as I said to Lisa) I was later told that I was 
not to worry as it was a one off migration (well that only wasted about 1- 2 
hours of my time investigating and whatever it took you guys to help me 
out).
 
Gr
 
Lee
 


  -Original Message-From: Paul Baumgartel 
  [mailto:[EMAIL PROTECTED]]Sent: 30 August 2001 
  19:31To: Multiple recipients of list ORACLE-LSubject: 
  RE: Tkprof output
  Also note the 
  very high "query" number (i.e. buffers gotten for consistent read).  
  That could account for a lot of the i/o, which is the proximate cause of the 
  lng elapsed time.  From the (to use one of Lisa's favorite terms) 
  doco (for 9i):
  Read 
  Consistency 
  Your system might spend excessive time rolling 
  back changes to blocks in order to maintain a consistent view. Consider the 
  following scenarios: 
  
If there are 
many small transactions and an active long-running query is running in the 
background on the same table where the changes are happening, then the query 
might need to roll back those changes often, in order to obtain a 
read-consistent image of the table. Compare the following 
V$SYSSTAT statistics to determine whether this is happening: 



  consistent 
  changes statistic indicates the number of times a database block has 
  rollback entries applied to perform a consistent read on the block. 
  Workloads that produce a great deal of consistent changes can consume a 
  great deal of resources. 
  
  consistent 
  gets statistic counts the number of logical reads in consistent mode. 
  
  
If there are 
few very, large rollback segments, then your system could be spending a lot 
of time rolling back the transaction table during delayed block cleanout in 
order to find out exactly which SCN a transaction was committed. The ratio 
of the following V$SYSSTAT statistics should be close to 1: 

ratio = transaction tables consistent reads undo records applied /
transaction tables consistent read rollbacks


A solution is to create more, smaller 
rollback segments, or to use automatic undo management. 
  
   
  Paul Baumgartel MortgageSight 
  Holdings, LLC [EMAIL PROTECTED] 

  
-Original Message-From: Koivu, Lisa 
[mailto:[EMAIL PROTECTED]]Sent: Thursday, August 30, 2001 
12:47 PMTo: Multiple recipients of list 
ORACLE-LSubject: RE: Tkprof output
Lee, 
This query seems suspect 

UPDATE VM_LIVE.SINGLE_CUSTOMER_HISTORY SCH SET 
VISIBLE=1WHERE ACXIOM_CUSTOMER_KEY = :b1  AND VERSION_NO = 
:b2 
because of this 

  call 
  count   cpu    
  elapsed   
  disk  query    
  current    rows--- 
  --   -- -- -- --  
  --Parse    
  0  0.00   
  0.00  
  0  
  0  
  0   
  0Execute  39562  
  4.55   
  7.22  10897 
  118687  
  1   
  1Fetch    
  0  0.00   
  0.00  
  0  
  0  
  0   0--- 
  --   -- -- -- --  
  --total    39562  
  4.55   
  7.22  10897 
  118687  
  1   1 
  
It's reading a ton of blocks to operate on ONE 
record.  What's the table structure here?  What's the index 
structure?  Cardinality?  
The buffer gets in the other queries are 
suspect too.  What's your blocksize?  It's reading a ton of blocks 
to arrive at the result. 

  -Original Message- From:   Robertson Lee - lerobe [SMTP:[EMAIL PROTECTED]] 
  Sent:   Thursday, August 30, 2001 11:56 AM To: Multiple recipients of list ORACLE-L Subject:    Tkprof output 
  Apologies for the length of the mail. 
    This query 
  is running for a mad amount of time, anyone any ideas.   Code and tkprof out 
  put shown below.   Huge TIA   
  Lee (who must learn more about such things 
  !!!)   DECLARE CURSOR TEMP_CDS ISSELECT 
  ACXIOM_CUSTOMER_KEY,   
  VERSION_NO,   
  ADDRESS_OCCUPANCY_KEYFROM   
  CUSTOMER_DETAIL_SOURCEWHERE  VISIBLE=1;   COUNTER 
  NUMBER(8);   BEGIN   COUNTER:=0;   FOR 
  I IN TEMP_CDS   LOOP    UPDATE &SCHEMA..SINGLE_CUSTOMER 
  SC    
  SET VISIBLE = 
  1    
  WHERE 
  ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY    
  AND   VERSION_NO 
  =I.VERSION_NO;    UPDATE 
  &SCHEMA..SINGLE_CUSTOMER_HISTORY 
  SCH    
  SET VISIBLE = 
  1

RE: Tkprof output

2001-08-30 Thread Christopher Spence

Title: Message



Take a 
look at using array fetches to retrieve rows more efficeintly.  It appears 
you are fetching 1 row at a time.  Which can be grossly 
inefficient.
Another helpful thing would to generate the TKPROF with Explan plans so 
you can see the rows per step and the paths chosen.
 
Also, 
consider of bulk transaction use for the updates, which seem to be touching alot 
of blocks.
 
 
"Do not criticize someone until you walked a 
mile in their shoes, that way when you criticize them, you are a mile a way and 
have their shoes."
Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:    (707) 885-2275 
Fuelspot 73 Princeton Street North, Chelmsford 01863   

  
  -Original Message-From: Robertson Lee - 
  lerobe [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 30, 2001 
  11:56 AMTo: Multiple recipients of list ORACLE-LSubject: 
  Tkprof output
  Apologies for the 
  length of the mail.
   
  This query is 
  running for a mad amount of time, anyone any ideas. 
   
  Code and tkprof 
  out put shown below.
   
  Huge 
  TIA
   
  Lee (who must 
  learn more about such things !!!)
   
  DECLARE CURSOR 
  TEMP_CDS ISSELECT 
  ACXIOM_CUSTOMER_KEY,   
  VERSION_NO,   
  ADDRESS_OCCUPANCY_KEYFROM   
  CUSTOMER_DETAIL_SOURCEWHERE  VISIBLE=1;
   
  COUNTER 
  NUMBER(8);
   
  BEGIN   COUNTER:=0;   FOR 
  I IN TEMP_CDS   LOOP
   
   UPDATE 
  &SCHEMA..SINGLE_CUSTOMER 
  SC    SET 
  VISIBLE = 
  1    WHERE 
  ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY    
  AND   VERSION_NO 
  =I.VERSION_NO;
   
   UPDATE 
  &SCHEMA..SINGLE_CUSTOMER_HISTORY 
  SCH    SET 
  VISIBLE = 
  1    WHERE 
  ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY    
  AND   VERSION_NO 
  =I.VERSION_NO;
   
   UPDATE 
  &SCHEMA..ADDRESS_OCCUPANCY 
  AO    SET 
  VISIBLE = 
  1    WHERE 
  ADDRESS_OCCUPANCY_KEY = I.ADDRESS_OCCUPANCY_KEY;
   
    COUNTER := COUNTER + 
  1;  IF (COUNTER = 
  5)  
  THEN  
  COUNTER:=0;  
  COMMIT;  END IF;   END 
  LOOP;   COMMIT;
   
  Sort options: 
  prsela  exeela  fchela  
  count    
  = number of times OCI procedure was 
  executedcpu  = cpu time in seconds executing 
  elapsed  = elapsed time in seconds 
  executingdisk = number of physical reads of 
  buffers from diskquery    = number of buffers gotten for 
  consistent readcurrent  = number of buffers gotten in current mode 
  (usually for update)rows = number of rows 
  processed by the fetch or execute 
  call
   
  UPDATE 
  VM_LIVE.SINGLE_CUSTOMER SC SET VISIBLE=1 
  WHERE ACXIOM_CUSTOMER_KEY = :b1  AND VERSION_NO = 
  :b2
   
  call 
  count   cpu    
  elapsed   disk  
  query    current    
  rows--- --   -- -- -- 
  --  --Parse    
  0  0.00   
  0.00  
  0  
  0  
  0   
  0Execute  39562 15.51 
  398.98  56555 
  181085  40672   
  39562Fetch    
  0  0.00   
  0.00  
  0  
  0  
  0   0--- 
  --   -- -- -- --  
  --total    39562 
  15.51 398.98  
  56555 181085  
  40672   39562
   
  Misses in 
  library cache during parse: 0Misses in library cache during execute: 
  1Optimizer goal: CHOOSEParsing user id: 39  (VM_LIVE)   
  (recursive depth: 1)
   
  Rows Execution 
  Plan---  
  ---  
  0  UPDATE STATEMENT   GOAL: 
  CHOOSE  0   UPDATE OF 
  'SINGLE_CUSTOMER'  0    TABLE 
  ACCESS (BY INDEX ROWID) OF 'SINGLE_CUSTOMER'  
  0 INDEX (UNIQUE SCAN) OF 'SINGLE_CUSTOMER_PK' 
  (UNIQUE)
   
  
   
  UPDATE 
  VM_LIVE.ADDRESS_OCCUPANCY AO SET VISIBLE=1 
  WHERE ADDRESS_OCCUPANCY_KEY = :b1
   
  call 
  count   cpu    
  elapsed   disk  
  query    current    
  rows--- --   -- -- -- 
  --  --Parse    
  0  0.00   
  0.00  
  0  
  0  
  0   
  0Execute  39562 12.57 
  186.88  57285 
  124038  40726   
  39562Fetch    
  0  0.00   
  0.00  
  0  
  0  
  0   0--- 
  --   -- -- -- --  
  --total    39562 
  12.57 186.88  
  57285 124038  
  40726   39562
   
  Misses in 
  library cache during parse: 0Optimizer goal: CHOOSEParsing user id: 
  39  (VM_LIVE)   (recursive depth: 1)
   
  Rows Execution 
  Plan---  
  -

RE: Tkprof output

2001-08-30 Thread Paul Baumgartel

Title: RE: Tkprof output



Also note the 
very high "query" number (i.e. buffers gotten for consistent read).  
That could account for a lot of the i/o, which is the proximate cause of the 
lng elapsed time.  From the (to use one of Lisa's favorite terms) doco 
(for 9i):
Read 
Consistency 
Your system might spend excessive time rolling 
back changes to blocks in order to maintain a consistent view. Consider the 
following scenarios: 

  If there are 
  many small transactions and an active long-running query is running in the 
  background on the same table where the changes are happening, then the query 
  might need to roll back those changes often, in order to obtain a 
  read-consistent image of the table. Compare the following 
  V$SYSSTAT statistics to determine whether this is happening: 
  
  
  
consistent 
changes statistic indicates the number of times a database block has 
rollback entries applied to perform a consistent read on the block. 
Workloads that produce a great deal of consistent changes can consume a 
great deal of resources. 

consistent 
gets statistic counts the number of logical reads in consistent mode. 


  If there are few 
  very, large rollback segments, then your system could be spending a lot of 
  time rolling back the transaction table during delayed block cleanout in order 
  to find out exactly which SCN a transaction was committed. The ratio of the 
  following V$SYSSTAT statistics should be close to 1: 
  ratio = transaction tables consistent reads undo records applied /
transaction tables consistent read rollbacks


  A solution is to create more, smaller 
  rollback segments, or to use automatic undo management. 

 
Paul Baumgartel MortgageSight Holdings, 
LLC [EMAIL PROTECTED] 

  -Original Message-From: Koivu, Lisa 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, August 30, 2001 
  12:47 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: Tkprof output
  Lee, 
  This query seems suspect 
  UPDATE VM_LIVE.SINGLE_CUSTOMER_HISTORY SCH SET 
  VISIBLE=1WHERE ACXIOM_CUSTOMER_KEY = :b1  AND VERSION_NO = 
  :b2 
  because of this 
  
call 
count   cpu    
elapsed   
disk  query    
current    rows--- 
--   -- -- -- --  
--Parse    
0  0.00   
0.00  
0  
0  
0   
0Execute  39562  
4.55   7.22  
10897 
118687  
1   
1Fetch    
0  0.00   
0.00  
0  
0  
0   0--- 
--   -- -- -- --  
--total    39562  
4.55   7.22  
10897 
118687  
1   1 

  It's reading a ton of blocks to operate on ONE 
  record.  What's the table structure here?  What's the index 
  structure?  Cardinality?  
  The buffer gets in the other queries are suspect 
  too.  What's your blocksize?  It's reading a ton of blocks to arrive 
  at the result. 
  
-Original Message- From:   Robertson Lee - lerobe [SMTP:[EMAIL PROTECTED]] Sent:   Thursday, August 30, 2001 11:56 AM To: Multiple recipients of list ORACLE-L Subject:    Tkprof output 
Apologies for the length of the mail. 
  This query is 
running for a mad amount of time, anyone any ideas.   Code and tkprof out put 
shown below.   Huge TIA   Lee (who must learn more about such things !!!) 
  DECLARE 
CURSOR TEMP_CDS ISSELECT 
ACXIOM_CUSTOMER_KEY,   
VERSION_NO,   
ADDRESS_OCCUPANCY_KEYFROM   
CUSTOMER_DETAIL_SOURCEWHERE  VISIBLE=1;   COUNTER 
NUMBER(8);   BEGIN   COUNTER:=0;   FOR I IN 
TEMP_CDS   LOOP   
 UPDATE 
&SCHEMA..SINGLE_CUSTOMER 
SC    SET 
VISIBLE = 
1    
WHERE 
ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY    
AND   VERSION_NO 
=I.VERSION_NO;    UPDATE 
&SCHEMA..SINGLE_CUSTOMER_HISTORY 
SCH    
SET VISIBLE = 
1    
WHERE 
ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY    
AND   VERSION_NO 
=I.VERSION_NO;    UPDATE 
&SCHEMA..ADDRESS_OCCUPANCY 
AO    SET 
VISIBLE = 
1    
WHERE ADDRESS_OCCUPANCY_KEY = I.ADDRESS_OCCUPANCY_KEY;     COUNTER := COUNTER + 
1;  IF (COUNTER = 
5)  
THEN  
COUNTER:=0;  
COMMIT;  END IF;   END 
LOOP;   COMMIT;  
Sort options: prsela  exeela  
fchela count    
= number of times OCI procedure was 
executedcpu  = cpu time in seconds 
executingelapsed  = elapsed time in seconds 
executingdisk = num

RE: Tkprof output

2001-08-30 Thread Jamadagni, Rajendra


Are you on 8i? Then you can use BULK binding which will definitely give you
some speed. IF you need example ... let me know.
 
Raj
__ 
Rajendra Jamadagni  MIS, ESPN Inc. 
Rajendra dot Jamadagni at ESPN dot com 
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art ! 


*1

This e-mail message is confidential, intended only for the named recipient(s) above 
and may contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank you.

*1




Are 
you on 8i? Then you can use BULK binding which will definitely give 
you some speed. IF you need example ... let me know.
 
Raj
__ 
Rajendra 
Jamadagni  
    MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com 
Any opinion expressed here is 
personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an 
art !

RE: Tkprof output

2001-08-30 Thread Koivu, Lisa

Title: RE: Tkprof output





Lee, 


This query seems suspect


UPDATE VM_LIVE.SINGLE_CUSTOMER_HISTORY SCH SET VISIBLE=1
WHERE
 ACXIOM_CUSTOMER_KEY = :b1  AND VERSION_NO = :b2


because of this


call count   cpu    elapsed   disk  query    current    rows
--- --   -- -- -- --  --
Parse    0  0.00   0.00  0  0  0   0
Execute  39562  4.55   7.22  10897 118687  1   1
Fetch    0  0.00   0.00  0  0  0   0
--- --   -- -- -- --  --
total    39562  4.55   7.22  10897 118687  1   1



It's reading a ton of blocks to operate on ONE record.  What's the table structure here?  What's the index structure?  Cardinality?  

The buffer gets in the other queries are suspect too.  What's your blocksize?  It's reading a ton of blocks to arrive at the result. 



-Original Message-
From:   Robertson Lee - lerobe [SMTP:[EMAIL PROTECTED]]
Sent:   Thursday, August 30, 2001 11:56 AM
To: Multiple recipients of list ORACLE-L
Subject:    Tkprof output


Apologies for the length of the mail.
 
This query is running for a mad amount of time, anyone any ideas. 
 
Code and tkprof out put shown below.
 
Huge TIA
 
Lee (who must learn more about such things !!!)
 
DECLARE CURSOR TEMP_CDS IS
SELECT ACXIOM_CUSTOMER_KEY,
   VERSION_NO,
   ADDRESS_OCCUPANCY_KEY
FROM   CUSTOMER_DETAIL_SOURCE
WHERE  VISIBLE=1;
 
COUNTER NUMBER(8);
 
BEGIN
   COUNTER:=0;
   FOR I IN TEMP_CDS
   LOOP
 
 UPDATE &SCHEMA..SINGLE_CUSTOMER SC
    SET VISIBLE = 1
    WHERE ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY
    AND   VERSION_NO =I.VERSION_NO;
 
 UPDATE &SCHEMA..SINGLE_CUSTOMER_HISTORY SCH
    SET VISIBLE = 1
    WHERE ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY
    AND   VERSION_NO =I.VERSION_NO;
 
 UPDATE &SCHEMA..ADDRESS_OCCUPANCY AO
    SET VISIBLE = 1
    WHERE ADDRESS_OCCUPANCY_KEY = I.ADDRESS_OCCUPANCY_KEY;
 
  COUNTER := COUNTER + 1;
  IF (COUNTER = 5)
  THEN
  COUNTER:=0;
  COMMIT;
  END IF;
   END LOOP;
   COMMIT;

 
Sort options: prsela  exeela  fchela 

count    = number of times OCI procedure was executed
cpu  = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call

 
UPDATE VM_LIVE.SINGLE_CUSTOMER SC SET VISIBLE=1
WHERE
 ACXIOM_CUSTOMER_KEY = :b1  AND VERSION_NO = :b2
 

call count   cpu    elapsed   disk  query    current    rows
--- --   -- -- -- --  --
Parse    0  0.00   0.00  0  0  0   0
Execute  39562 15.51 398.98  56555 181085  40672   39562
Fetch    0  0.00   0.00  0  0  0   0
--- --   -- -- -- --  --
total    39562 15.51 398.98  56555 181085  40672   39562
 
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 39  (VM_LIVE)   (recursive depth: 1)
 
Rows Execution Plan
---  ---
  0  UPDATE STATEMENT   GOAL: CHOOSE
  0   UPDATE OF 'SINGLE_CUSTOMER'
  0    TABLE ACCESS (BY INDEX ROWID) OF 'SINGLE_CUSTOMER'
  0 INDEX (UNIQUE SCAN) OF 'SINGLE_CUSTOMER_PK' (UNIQUE)
 

 
UPDATE VM_LIVE.ADDRESS_OCCUPANCY AO SET VISIBLE=1
WHERE
 ADDRESS_OCCUPANCY_KEY = :b1
 

call count   cpu    elapsed   disk  query    current    rows
--- --   -- -- -- --  --
Parse    0  0.00   0.00  0  0  0   0
Execute  39562 12.57 186.88  57285 124038  40726   39562
Fetch    0  0.00   0.00  0  0  0   0
--- --   -- -- -- --  --
total    39562 12.57 186.88  57285 124038  40726   39562
 
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user

RE: TKPROF QUERY

2001-06-25 Thread Scott . Shafer


Is your unix user id a member of the dba group?  If not, that is your
problem.  Have your dba grant "read" to "world" for that trace file, i.e., 

chmod 664 rapid_ora_14247.trc

Scott Shafer
San Antonio, TX
210-581-6217

"Common sense will not accomplish great things. Simply become insane and
desperate."

> -Original Message-
> From: Seema Singh [SMTP:[EMAIL PROTECTED]]
> Sent: Monday, June 25, 2001 4:28 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:  TKPROF QUERY
> 
> Hi
> I am getting following eror when I am executing following command.Let me 
> know it Is teh permission problem or any thing else?
> $ls -la
> rw-rw   1 oracle   dba12502 Jun 22 17:09 rapid_ora_14247.trc
> $ tkprof rapid_ora_14247.trc rapid_ora_14247.trc.out
> 
> TKPROF: Release 8.1.6.2.0 - Production on Fri Jun 22 17:09:43 2001
> 
> (c) Copyright 1999 Oracle Corporation.  All rights reserved.
> 
> 
> could not open trace file rapid_ora_14247.trc
> Thanks
> -Seema
> 
> 
> _
> Get your FREE download of MSN Explorer at http://explorer.msn.com
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Seema Singh
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (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.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

Re: Tkprof/explain plan errors

2001-06-02 Thread Danisment Gazi Unal (Unal Bilisim)

Note that tkprof's execution plan shows execution plan against current
database connection to which tkprof is connected. So, execution plan in
trace file and execution plan in tkprof output may be different. And, I
guess, tkprof shows first execution plan. If your statement parsed more
than once, Execution plans may be different.

have you run itrprof at
http://www.unal-bilisim.com/products/itrprof/itrprof.html .
It reports execution plans in trace files(so, correct execution plans) and
you can see all execution plan(s).

regards...

[EMAIL PROTECTED] wrote:

> I am trying to do some performance tuning on a users query by
> using tkprof.  After running tkprof on the trace file (which
> was generated after using alter sessions set sql_trace true),
> I look at the results and they say
>
> error connecting to database using: /
> ORA-01017: invalid username/password; logon denied
>
> EXPLAIN PLAN option disabled.
> 
>
> However, the correct username/password is being used.  Not sure
> what is going on with Explain plan.  Any suggestions
>
> -
> Sent using MailStart.com ( http://MailStart.Com/welcome.html )
> The FREE way to access your mailbox via any web browser, anywhere!
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (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).

--

Web: http://www.unal-bilisim.com
*
Questions and Answers: http://www.unal-bilisim.com/qa/discus/
*
itrprof SQL Analyzer:
http://www.unal-bilisim.com/products/itrprof/itrprof.html

itrprof SQL Analyzer is a web based tool which analyzes
SQL_TRACE/Event10046 trace files of Oracle,
finds bottlenecks and offersinformation on how to tune them.
*
iOraBugFinder:
http://www.unal-bilisim.com/products/iorabugfinder/iorabugfinder.html

iOraBugFinder is a web based tool which scans Oracle's alert log files,
ORA-600 and ORA-7445 trace files; extracts relevant information from them
and generates URL links to relevant bugs, notes, forums at Metalink.
*

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Danisment Gazi Unal (Unal Bilisim)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

Re: tkprof and security?

2001-05-09 Thread Rachel Carmichael


Paul -- both :)

I sometimes see answers to posts before I see the original post...
considering that I'm usually in dizzy mode to begin with, that does NOT help
my equilibrium (someone stop the world I want to get ON)

Rachel


>From: Paul Drake <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: Re: tkprof and security?
>Date: Wed, 09 May 2001 06:30:53 -0800
>
>Rachel Carmichael wrote:
> >
> > That's what I love about this list before I can answer, someone has
> > provided scripts to do things :)
> >
> > Thanks Tim, I'm adding these to my scripts library
> >
> > Rachel
> >
>
>Rachel,
>
>Is that due to the speed of the list members, or the lag of the mail
>server?
>
>Paul
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Paul Drake
>   INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (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).

_
Get your FREE download of MSN Explorer at http://explorer.msn.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

Re: tkprof and security?

2001-05-09 Thread Thater, William


On Wed, 9 May 2001,Paul Drake scribbled on the wall in glitter crayon:

->Rachel Carmichael wrote:
->>
->> That's what I love about this list before I can answer, someone has
->> provided scripts to do things :)
->>
->> Thanks Tim, I'm adding these to my scripts library
->>
->> Rachel
->>
->
->Rachel,
->
->Is that due to the speed of the list members, or the lag of the mail
->server?
->
->Paul
->
Picky, picky, picky.;-)

--
Bill Thater Certifieable ORACLE DBA
Telergy, Inc.[EMAIL PROTECTED]
~~
You gotta program like you don't need the money,
You gotta compile like you'll never get hurt,
You gotta run like there's nobody watching,
It's gotta come from the heart if you want it to work.
~~
Spellchecker not found.  Press -- to continue ...

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Thater, William
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

Re: tkprof and security?

2001-05-09 Thread Paul Drake

Rachel Carmichael wrote:
> 
> That's what I love about this list before I can answer, someone has
> provided scripts to do things :)
> 
> Thanks Tim, I'm adding these to my scripts library
> 
> Rachel
> 

Rachel,

Is that due to the speed of the list members, or the lag of the mail
server?

Paul
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Paul Drake
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

Re: tkprof and security?

2001-05-09 Thread Rachel Carmichael


That's what I love about this list before I can answer, someone has 
provided scripts to do things :)

Thanks Tim, I'm adding these to my scripts library

Rachel


>From: "Tim Sawmiller" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: Re: tkprof and security?
>Date: Wed, 09 May 2001 04:40:22 -0800
>
>trace_on.sql
>
>REM Script to turn TRACE MODE on for a selected SID.
>set serveroutput ON size 2000;
>set verify OFF;
>DECLARE
>   r_sid   NUMBER;
>   v_sid   NUMBER;
>   v_serialNUMBER;
>   v_username  VARCHAR(30);
>   v_statusVARCHAR2(8);
>   v_serverVARCHAR2(9);
>   p_server_pidVARCHAR2(9);
>
>BEGIN
>--  Accept r_sid Number 'What SID do you wish to trace? - '
>   r_sid := &SID;
>   SELECT s.sid, s.serial#, s.status, s.server, p.spid
> INTO v_sid, v_serial, v_status, v_server, p_server_pid
> FROM v$session s, v$process p
>WHERE s.sid = r_sid
>  AND s.paddr = p.addr;
>   DBMS_OUTPUT.PUT_LINE ('Sid ' || TO_CHAR(v_sid) ||
>'  Serial# ' || TO_CHAR(v_serial) ||
>'  Username ' || v_username ||
>'  Status ' || v_status ||
>'  Server ' || v_server );
>   IF v_server != 'DEDICATED' THEN
> DBMS_OUTPUT.PUT_LINE
>  ('Tracing only produces valid results for a Dedicated 
>Connection');
> DBMS_OUTPUT.PUT_LINE
>  ('Tracing *NOT* turned on.');
> RETURN;
>   END IF;
>-- Time to Turn Trace ON
>SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(v_sid, v_serial, TRUE);
>
>   DBMS_OUTPUT.PUT_LINE ('Trace Mode is ON for '||v_sid);
>   DBMS_OUTPUT.PUT_LINE ('Output sent to 
>udump/ora_'||p_server_pid||'.trc');
>
>EXCEPTION
>   WHEN NO_DATA_FOUND THEN
> DBMS_OUTPUT.PUT_LINE ('Cannot find a session for SID 
>'||TO_CHAR(r_sid));
>   WHEN OTHERS THEN
> DBMS_OUTPUT.PUT_LINE ('Error: '||SQLERRM);
>END;
>/
>
>traceoff.sql
>
>REM Script to turn TRACE MODE off for a selected SID.
>set serveroutput ON size 2000;
>set verify OFF;
>DECLARE
>   r_sid   NUMBER;
>   v_sid   NUMBER;
>   v_serialNUMBER;
>   v_username  VARCHAR(30);
>   v_statusVARCHAR2(8);
>   v_serverVARCHAR2(9);
>   p_server_pidVARCHAR2(9);
>
>BEGIN
>--  Accept r_sid Number 'What SID do you wish to trace? - '
>   r_sid := &SID;
>   SELECT s.sid, s.serial#, s.status, s.server, p.spid
> INTO v_sid, v_serial, v_status, v_server, p_server_pid
> FROM v$session s, v$process p
>WHERE s.sid = r_sid
>  AND s.paddr = p.addr;
>   DBMS_OUTPUT.PUT_LINE ('Sid ' || TO_CHAR(v_sid) ||
>'  Serial# ' || TO_CHAR(v_serial) ||
>'  Username ' || v_username ||
>'  Status ' || v_status ||
>'  Server ' || v_server );
>-- Time to Turn Trace OFF
>SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(v_sid, v_serial, FALSE);
>
>   DBMS_OUTPUT.PUT_LINE ('Trace Mode is OFF for '||v_sid);
>   DBMS_OUTPUT.PUT_LINE ('Output is in udump/ora_'||p_server_pid||'.ora');
>
>EXCEPTION
>   WHEN NO_DATA_FOUND THEN
> DBMS_OUTPUT.PUT_LINE ('Cannot find a session for SID 
>'||TO_CHAR(r_sid));
>   WHEN OTHERS THEN
> DBMS_OUTPUT.PUT_LINE ('Error: '||SQLERRM);
>END;
>/
>
>
>
> >>> [EMAIL PROTECTED] 05/09/01 04:30AM >>>
>On a development box, 'alter session' should not cause
>too much harm.  If you're concerned, write a package
>as SYS which turns on tracing for the developers
>session and just grant access on that...
>
>You may want to look at _trace_files_public parameter
>as well so they can see their trace files.
>
>hth
>connor
>
>--- Chris Rezek <[EMAIL PROTECTED]> wrote: > I want
>to enable our developers to use tkprof for
> > their own tuning work
> > without giving them general ALTER SESSION
> > privileges.  What's the
> > minimal set of permissions do they need to use
> > explain plan and tkprof?
> >
> > Chris Rezek
> > Oracle DBA
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Chris Rezek
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- (858) 538-5051  FAX:
> > (858) 53

Re: tkprof and security?

2001-05-09 Thread Tim Sawmiller


trace_on.sql

REM Script to turn TRACE MODE on for a selected SID.
set serveroutput ON size 2000;
set verify OFF;
DECLARE
  r_sid NUMBER;
  v_sid NUMBER;
  v_serial  NUMBER;
  v_usernameVARCHAR(30);
  v_status  VARCHAR2(8);
  v_server  VARCHAR2(9);
  p_server_pid  VARCHAR2(9);

BEGIN
--  Accept r_sid Number 'What SID do you wish to trace? - '
  r_sid := &SID;
  SELECT s.sid, s.serial#, s.status, s.server, p.spid
INTO v_sid, v_serial, v_status, v_server, p_server_pid
FROM v$session s, v$process p
   WHERE s.sid = r_sid
 AND s.paddr = p.addr;
  DBMS_OUTPUT.PUT_LINE ('Sid ' || TO_CHAR(v_sid) ||
   '  Serial# ' || TO_CHAR(v_serial) ||
   '  Username ' || v_username ||
   '  Status ' || v_status ||
   '  Server ' || v_server );
  IF v_server != 'DEDICATED' THEN
DBMS_OUTPUT.PUT_LINE 
 ('Tracing only produces valid results for a Dedicated Connection');
DBMS_OUTPUT.PUT_LINE 
 ('Tracing *NOT* turned on.');
RETURN;
  END IF;
-- Time to Turn Trace ON
   SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(v_sid, v_serial, TRUE);

  DBMS_OUTPUT.PUT_LINE ('Trace Mode is ON for '||v_sid);
  DBMS_OUTPUT.PUT_LINE ('Output sent to udump/ora_'||p_server_pid||'.trc');
 
EXCEPTION
  WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Cannot find a session for SID '||TO_CHAR(r_sid));
  WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error: '||SQLERRM);
END;
/

traceoff.sql

REM Script to turn TRACE MODE off for a selected SID.
set serveroutput ON size 2000;
set verify OFF;
DECLARE
  r_sid NUMBER;
  v_sid NUMBER;
  v_serial  NUMBER;
  v_usernameVARCHAR(30);
  v_status  VARCHAR2(8);
  v_server  VARCHAR2(9);
  p_server_pid  VARCHAR2(9);

BEGIN
--  Accept r_sid Number 'What SID do you wish to trace? - '
  r_sid := &SID;
  SELECT s.sid, s.serial#, s.status, s.server, p.spid
INTO v_sid, v_serial, v_status, v_server, p_server_pid
FROM v$session s, v$process p
   WHERE s.sid = r_sid
 AND s.paddr = p.addr;
  DBMS_OUTPUT.PUT_LINE ('Sid ' || TO_CHAR(v_sid) ||
   '  Serial# ' || TO_CHAR(v_serial) ||
   '  Username ' || v_username ||
   '  Status ' || v_status ||
   '  Server ' || v_server );
-- Time to Turn Trace OFF
   SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(v_sid, v_serial, FALSE);

  DBMS_OUTPUT.PUT_LINE ('Trace Mode is OFF for '||v_sid);
  DBMS_OUTPUT.PUT_LINE ('Output is in udump/ora_'||p_server_pid||'.ora');
 
EXCEPTION
  WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Cannot find a session for SID '||TO_CHAR(r_sid));
  WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error: '||SQLERRM);
END;
/



>>> [EMAIL PROTECTED] 05/09/01 04:30AM >>>
On a development box, 'alter session' should not cause
too much harm.  If you're concerned, write a package
as SYS which turns on tracing for the developers
session and just grant access on that...

You may want to look at _trace_files_public parameter
as well so they can see their trace files.

hth
connor

--- Chris Rezek <[EMAIL PROTECTED]> wrote: > I want
to enable our developers to use tkprof for
> their own tuning work
> without giving them general ALTER SESSION
> privileges.  What's the
> minimal set of permissions do they need to use
> explain plan and tkprof?
> 
> Chris Rezek
> Oracle DBA
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com 
> -- 
> Author: Chris Rezek
>   INET: [EMAIL PROTECTED] 
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (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).


=
Connor McDonald
http://www.oracledba.co.uk (mirrored at 
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"


Do You Yahoo!?
Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk 
or your free @yahoo.ie address at http://mail.yahoo.ie 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing:

Re: tkprof and security?

2001-05-09 Thread Connor McDonald


On a development box, 'alter session' should not cause
too much harm.  If you're concerned, write a package
as SYS which turns on tracing for the developers
session and just grant access on that...

You may want to look at _trace_files_public parameter
as well so they can see their trace files.

hth
connor

--- Chris Rezek <[EMAIL PROTECTED]> wrote: > I want
to enable our developers to use tkprof for
> their own tuning work
> without giving them general ALTER SESSION
> privileges.  What's the
> minimal set of permissions do they need to use
> explain plan and tkprof?
> 
> Chris Rezek
> Oracle DBA
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Chris Rezek
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (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).


=
Connor McDonald
http://www.oracledba.co.uk (mirrored at 
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"


Do You Yahoo!?
Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk
or your free @yahoo.ie address at http://mail.yahoo.ie
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

Re: tkprof for 10046 event

2001-04-06 Thread Danisment Gazi Unal


Hi,

I developed a web based tool which analyzing event
10046 traces. I'm currently looking for a java servlet
service provider.

this tool does:

- what tkprof does
Plus
- reports all execution plans for SQL statment(I
guess, tkprof reports first, but there is no guarantee
first and other execution plans are some). you can
also specify execution plan count. exection plan is
reported according to trace file. tkprof connects to
db and generate new execution plans. so, current
execution plan and real execution plan in trace file
may be different.
- reports relative times of statement. so, you can see
when your stament started and time intervals between
statements.
- report binds variables for each parse,loop. You can
also specify number of loop and bind variables.
- reports non-idle waits for each statement
- reports CPU usage and non-waits total for each
statement.  reports CPU/wait ratio againt current
statement and overall total. you can compare them then
breakdown which one is higher.
- gives tuninig advise ordered by costs. this is one
of the very important part of tool. there are two
costs unit. Time Cost and Amount Cost. You can compare
your costs with other statements to measure
improvement.
- reports overall. it's not like in tkprof. tkprof
reports according to recursive/non-recursive usage.
But, a user pl/sql statement runs in recursive mode.
so you can not separate SYS and NON-SYS statements in
overall section of tkprof. becasue of that, I used
user account instead of recursion. so, SYS and NON-SYS
is seperated in overall section. because of SYS's
recursive transaction is done by SYS, they are
automaticly separeted.

regards...

--- K Gopalakrishnan <[EMAIL PROTECTED]> wrote:
> Hi !
> 
> I have not seen any tool for formatting the wait
> event
> stats. Steve Adams has some script in his book
> (Oracle8i Internal Sevices page -16).
> 
> You can write a similar script and explore the trace
> files. 
> 
> 
> =
> Have a nice day !!
>

> Best Regards,
> K Gopalakrishnan,
> Bangalore, INDIA.
> 
> __
> Do You Yahoo!?
> Get email at your own domain with Yahoo! Mail. 
> http://personal.mail.yahoo.com/
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: K Gopalakrishnan
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (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).


=
---
Danisment Gazi Unal
Web: http://www.geocities.com/danisment
---

__
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail. 
http://personal.mail.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Danisment Gazi Unal
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

Re: tkprof for 10046 event

2001-04-05 Thread K Gopalakrishnan


Hi !

I have not seen any tool for formatting the wait event
stats. Steve Adams has some script in his book
(Oracle8i Internal Sevices page -16).

You can write a similar script and explore the trace
files. 


=
Have a nice day !!

Best Regards,
K Gopalakrishnan,
Bangalore, INDIA.

__
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail. 
http://personal.mail.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: K Gopalakrishnan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

RE: tkprof for 10046 event

2001-04-05 Thread Steve Adams


Hi Steve,

I've not checked recently, but Guy Harrison used to have one on his web site.

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/
@   http://www.christianity.net.au/


-Original Message-
Sent: Friday, 6 April 2001 1:52
To: Multiple recipients of list ORACLE-L


Hi,

When setting event 10046 at level 8 it produces a trace file with wait
events. Does anyone have a utility like tkprof which summarises the trace
file to show a summary of wait events and timings per sql statement?

Thanks,

Steve Wilkes
___
Oracle DBA
npower
email:[EMAIL PROTECTED] 

=
This email and any files transmitted with it are confidential and 
intended solely for the use of the individual or entity to whom they   
are addressed. If you have received this email in error please notify 
gpupower.co.uk or [EMAIL PROTECTED]

This outgoing e-mail (and any attachments) has been checked
(using Sophos Sweep 3.44 + patches) before leaving us (UK 08457 353637),
and has been found to be clean from any virus infection.

=
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Wilkes, Steve
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.com
-- 
Author: Steve Adams
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

RE: TKPROF

2001-02-09 Thread Sam P. Roberts (ZADCO ITIS)


YES
THIS IS AN ORACLE FINANCIALS DATABASE - RULE BASED OPTIMIZER

-IM CURIOUS ABOUT THIS, BECAUSE I DOUBT THE EXPLAIN (Please NOO!!) - Has
anyone had rubbish back from EXPLAIN ?

SAM 
 
-Original Message-
Sent: Thursday, February 08, 2001 12:17 PM
To: Multiple recipients of list ORACLE-L


Hello,

Do you have timed_statistics = true in your init file ?

Regards
Henrik

Sam P. Roberts (ZADCO ITIS) wrote:

> This is the output from tkprof
> 
> call count   cpuelapsed   disk  querycurrent
> rows
> --- --   -- -- -- --
> --
> Parse1  0.02   0.02  0  0  0
> 0
> Execute  1  0.00   0.00  0  0  0
> 0
> Fetch2  0.34   1.73 83385  0
> 7
> --- --   -- -- -- --
> --
> total4  0.36   1.75 83385  0
> 7
> 
> Misses in library cache during parse: 1
> Optimizer goal: RULE
> 
> Misses in library cache during parse: 1
> Optimizer goal: RULE
> Parsing user id: 36  (GL)
> 
> Rows Execution Plan
> ---  ---
>   0  SELECT STATEMENT   GOAL: RULE
>   0   NESTED LOOPS
>   0INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'SAM1' (NON-UNIQUE)
>   0TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
>'GL_BALANCES'
>   0 INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'GL_BALANCES_N1'
> 
> My question - Why is the rows returned 7 in stats, yet the explain shows
now
> Rows:
> 
> Thanks
> 
> Sam


-- 
---
Henrik Ekenberg Anoto AB

E-mail :[EMAIL PROTECTED]  


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Henrik Ekenberg
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.com
-- 
Author: Sam P. Roberts (ZADCO ITIS)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

Re: TKPROF

2001-02-08 Thread Danisment UNAL


hello,

if EXPLAIN option is used, tkprof generates output
against current database statistics not against
statistics in raw file. this makes followings wrong:

- explain plan may be different than explain plan in
raw file since statistics may have changed after raw
file is generated.
- because of hard parse, execution plans of same SQLs
may be different. if you use aggregate=yes(which is
default), tkprof will show one execution plan.

I did not test for returned row. but, if explain plan
changes, returned row should be changed.

If there is "Row Source Operation" in your output, use
this one instead of explain plan section. "Row Source
Operation" shows exact execution plan in raw file.

regards...

--- "Sam P. Roberts (ZADCO ITIS)"
<[EMAIL PROTECTED]> wrote:
> This is the output from tkprof
> 
> call count   cpuelapsed   disk 
> querycurrent
> rows
> --- --   -- --
> -- --
> --
> Parse1  0.02   0.02  0  
>0  0
> 0
> Execute  1  0.00   0.00  0  
>0  0
> 0
> Fetch2  0.34   1.73 83  
>  385  0
> 7
> --- --   -- --
> -- --
> --
> total4  0.36   1.75 83  
>  385  0
> 7
> 
> Misses in library cache during parse: 1
> Optimizer goal: RULE
> 
> Misses in library cache during parse: 1
> Optimizer goal: RULE
> Parsing user id: 36  (GL)
> 
> Rows Execution Plan
> --- 
> ---
>   0  SELECT STATEMENT   GOAL: RULE
>   0   NESTED LOOPS
>   0INDEX   GOAL: ANALYZED (RANGE SCAN) OF
> 'SAM1' (NON-UNIQUE)
>   0TABLE ACCESS   GOAL: ANALYZED (BY INDEX
> ROWID) OF
>'GL_BALANCES'
>   0 INDEX   GOAL: ANALYZED (RANGE SCAN) OF
> 'GL_BALANCES_N1'
> 
> My question - Why is the rows returned 7 in stats,
> yet the explain shows now
> Rows:
> 
> Thanks
> 
> Sam
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Sam P. Roberts (ZADCO ITIS)
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (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).


=
---
Danisment Gazi Unal
Web: http://www.geocities.com/danisment
---

__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35 
a year!  http://personal.mail.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Danisment UNAL
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

Re: TKPROF

2001-02-08 Thread Henrik Ekenberg


Hello,

Do you have timed_statistics = true in your init file ?

Regards
Henrik

Sam P. Roberts (ZADCO ITIS) wrote:

> This is the output from tkprof
> 
> call count   cpuelapsed   disk  querycurrent
> rows
> --- --   -- -- -- --
> --
> Parse1  0.02   0.02  0  0  0
> 0
> Execute  1  0.00   0.00  0  0  0
> 0
> Fetch2  0.34   1.73 83385  0
> 7
> --- --   -- -- -- --
> --
> total4  0.36   1.75 83385  0
> 7
> 
> Misses in library cache during parse: 1
> Optimizer goal: RULE
> 
> Misses in library cache during parse: 1
> Optimizer goal: RULE
> Parsing user id: 36  (GL)
> 
> Rows Execution Plan
> ---  ---
>   0  SELECT STATEMENT   GOAL: RULE
>   0   NESTED LOOPS
>   0INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'SAM1' (NON-UNIQUE)
>   0TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
>'GL_BALANCES'
>   0 INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'GL_BALANCES_N1'
> 
> My question - Why is the rows returned 7 in stats, yet the explain shows now
> Rows:
> 
> Thanks
> 
> Sam


-- 
---
Henrik Ekenberg Anoto AB

E-mail :[EMAIL PROTECTED]  


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Henrik Ekenberg
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).

52 matches

Mail list logo