Re: tkprof question

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

>

>

>