Re: tkprof question

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

> > >

> > >

> > >

> >

> >

>

>