Re: tkprof question

>> What are the tkprof stats like now.

I didn't run tkprof after tuning. We were pretty happy with the result, and all work ended there.

It would be hard to do better than that with this aplication language. It's just not meant to be quick, but it usually works fine.

I won't try to add up the tkprof times, to match the process time, anymore.

Merci

--

Syltrem
http://pages.infinit.net/syltrem (OpenVMS related web site - en français)
To reply to myself directly, remove zulu from my address

"Andy" <andy.spaven_at_eps-hq.co.uk> a écrit dans le message de news:
H16F9.2671$9R.10235260_at_newsr2.u-net.net...


> You'd be suprised just how much time communications can take once the

> "rudimentary" tuning has got all the individual statements tuned.  The

> problem is determining the amount.  Usually I wouldn't bother looking - I

> tend to examine SQL, tune it and add/remove/modify indexes.  If there is

> still a problem then and only then do I look to rationlise the number of

> calls to Oracle and then only in loops.  You won't have that luxury with a

> third party product.

>

> If you think about the 50000 parse/execute/fetch calls - each is taking on

> average at most about 25ms - (22 mins / 50K) and that ignores the time

taken


> "inside" Oracle to do the fetching from disk etc.  If each call took 5ms

> then you're still talking about 5 minutes of the 22.

>

> Even so there could be more going on.  What are the tkprof stats like now.

>

> Andy

>

> "Syltrem" <syltremzulu_at_videotron.ca> wrote in message

> news:085F9.19330$H67.87766_at_tor-nn1.netcom.ca...

> > Hi

> >

> > Thanks for this info.

> > Let me start by saying that timed_statistics is TRUE as displayed below

> >

> > Yesterday I added an index to speed up the query, and the programs now

> runs

> > in 22 minutes instead of 1.5 hours.

> > You're saying the "missing" time possibly is time spent communicating

> > between the proigram and Oracle. That could well, be, as the application

> > uses a lot of

> > SELECT ROWID FROM...

> > SELECT WHERE ROWID=

> > UPDATE WHERE ROWID=

> > It's using cusrsors for fetching rows.

> >

> > So now I fixed the problem, but is there any way of knowing exactly how

> much

> > time Oracle spent retrieving the data and transmitting the result to the

> > application?

> >

> > Thanks.

> > Syltrem

> >

> > SQL> select value, name from v$parameter where name='timed_statistics';

> >

> > VALUE

>

> --------------------------------------------------------------------------

> --

> > ----

> > NAME

> > ----------------------------------------------------------------

> > TRUE

> > timed_statistics

> >

> >

> > 1 row selected.

> >

> >

> > --

> >

> > Syltrem

> > http://pages.infinit.net/syltrem (OpenVMS related web site - en

français)


> > To reply to myself directly, remove zulu from my address

> >

> > "Andy" <andy.spaven_at_eps-hq.co.uk> a écrit dans le message de news:

> > qW%E9.2641$9R.10226819_at_newsr2.u-net.net...

> > > To get CPU timings you need the initialization parameter

> timed_statistics

> > > set to TRUE for either the whole db or just your session.  This can be

> > done

> > > by

> > >

> > > 1) permanently in the init.ora file (see your dba) or

> > > 2) by issuing alter system set timed_statistics=true in a sql session

> (see

> > > your dba or risk his wrath altering system parameters).

> > > 3) by issuing alter session set timed_statistics=true in a sql session

> > (not

> > > that you're likely to be able to do this for a third party product

> unless

> > > you have the source and can rebuild it - yuk).

> > >

> > > 1 & 2 will have an impact on performance as Oracle gathers statistics

> for

> > > all sessions but only a small one (2-3% tops).

> > >

> > > The "missing" time might be comms time - messages to and from the

server


> > > from the client application typically dominated by fetching lots of

> > > individual rows one at a time rather than using bulk fetch.  Also lots

> of

> > > individual update or delete or insert statements incur "round trip"

> time.

> > > The big clue here is the large number of executes and fetches you're

> > seeing.

> > >

> > > It might also be worth looking at the large numnber of query blocks

per


> > > fetch.  At 120 on average this looks a little high.  This could be a

> > missing

> > > index or poor sql.  Fixing the former is much easier.  Look through

the


> > > tkprof output for the individual statements causing the most problem

and


> > > examine the explain plan for that statement.  If you're unsure you'll

> need

> > > to come back to the group with the SQL and the plan.

> > >

> > > Hope this helps.

> > >

> > > Andy

> > >

> > > "Syltrem" <syltremzulu_at_videotron.ca> wrote in message

> > > news:xMPE9.19275$H67.87189_at_tor-nn1.netcom.ca...

> > > > OpenVMS 7.2-1

> > > > Oracle 8.1.6.0.0

> > > >

> > > > First of, I know my Oracle version is no longer supported. Was

waiting


> > for

> > > > my application vendor to certify 902.

> > > >

> > > > I am trying to see where an application program (language Gembase)

> > should

> > > be

> > > > optimized.

> > > > Program runs for 3.5 hours elapsed, uses 7 minutes of CPU.

> > > > The Oracle server that serves it uses 1.5 hours of CPU  <-- so my

> > problem

> > > > really is here.

> > > >

> > > > When I look at the TKPROF output, it says:

> > > > OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

> > > >

> > > > call     count       cpu    elapsed       disk      query    current

> > > > rows

> > >

>

  ------- ------  -------- ---------- ---------- ---------- ----------  ---


> -

> > > --

> > > > ----

> > > > Parse      283      0.00      10.19          0          0          0

> > > > 0

> > > > Execute  28575      0.00      16.82       3466       9089      41525

> > > > 7061

> > > > Fetch    22940      0.00     721.99     661649    2765453        530

> > > > 21085

> > >

>

  ------- ------  -------- ---------- ---------- ---------- ----------  ---


> -

> > > --

> > > > ----

> > > > total    51798      0.00     749.00     665115    2774542      42055

> > > > 28146

> > > >

> > > > OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

> > > >

> > > > call     count       cpu    elapsed       disk      query    current

> > > > rows

> > >

>

  ------- ------  -------- ---------- ---------- ---------- ----------  ---


> -

> > > --

> > > > ----

> > > > Parse      640      0.00       1.53          0          0          0

> > > > 0

> > > > Execute   6706      0.00       0.38          0          0          0

> > > > 0

> > > > Fetch    12337      0.00       3.09        619      31156       6724

> > > > 12468

> > >

>

  ------- ------  -------- ---------- ---------- ---------- ----------  ---


> -

> > > --

> > > > ----

> > > > total    19683      0.00       5.00        619      31156       6724

> > > > 12468

> > > >

> > > >

> > > > Now I don`t see the figures for CPU time (if someone know how, pls

let


> > me

> > > > know!), but it has to be less than elapsed time.

> > > > Total Elapsed is 5 seconds + 749 seconds = about 12.5 minutes.

> > > >

> > > > Let`s assume Oracle CPU time = Oracle Elapsed time. It can`t be any

> > > greater

> > > > than that.

> > > > What happened in the Oracle server process, during the 1.25 CPU

hours


> > not

> > > > accounted in the trace file?

> > > >

> > > > Am I missing something?

> > > >

> > > > Thanks!

> > > >

> > > > --

> > > >

> > > > Syltrem

> > > > http://pages.infinit.net/syltrem (OpenVMS related web site - en

> > français)

> > > > To reply to myself directly, remove zulu from my address

> > > >

> > > >

> > > >

> > >

> > >

> >

> >

>

>