| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: tkprof question
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
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...Received on Wed Nov 27 2002 - 09:03:39 CST
> 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
> >
> >
> >
>
>
 |
 |