Re: TKPROF questions

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 14 Nov 1999 20:48:32 -0500
Message-ID: <+GQvOC1jz575WEbAHlijHG19FzxH_at_4ax.com>


A copy of this was sent to alan_psb_at_yahoo.com (if that email address didn't require changing) On Sun, 14 Nov 1999 16:39:37 GMT, you wrote:

>Hi Sybrand,
>
>Is the time given in SQL*Plus (set timing on) is equal to the elapsed
>time in TKPROF output?

It should be fairly close but the sqlplus timing will generally be larger then the tkprof timing as it includes the time sqlplus spends doing things other then your query -- tkprof only times what it took to do your query. if you print to a really slow device -- and have lots of rows -- sqlplus's timing will be larger then tkprofs.

For example, I just did a select * from all_users where rownum < 500 over a dialup line. SQLPlus says:

...
499 rows selected.

Elapsed: 00:00:06.98
tkyte_at_8.0>

It took 6.98 seconds for sqlplus to fetch and display the data. tkprof on the other hand says


select *
from
 all_users where rownum < 500

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       35      0.16       0.16          0      18595          3         499
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       37      0.17       0.17          0      18595          3         499

which is the total time the database spent processing my query ( 17/100's of a second).

> What is the time unit in SQL*Plus when timing is
>setting on?
>

It depends on platform. In unix it is hours:minutes:seconds:hsecs.

On NT it is like this:

tkyte_at_ORACLE> set timing on
tkyte_at_ORACLE> exec dbms_lock.sleep(1);

PL/SQL procedure successfully completed.

 real: 1272
tkyte_at_ORACLE>

you divide by 1000 to get seconds -- the above snippet took 1.272 seconds.

>Thanks,
>Alan
>
>In article <382d4ead.5594147_at_news.demon.nl>,
> postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote:
>> On Sat, 13 Nov 1999 06:23:09 GMT, alan_psb_at_yahoo.com wrote:
>>
>> >I have few questions about using TKPROF. (on Oracle8)
>> >
>> >1) Is it possible to specify the resulting file in using TKPROF? This
>is
>> >because I found that all the output information is forwarded to one
>> >particular file. However, I want each SQL statmenet trace file
>forwarded
>> >to different files.
>> >
>> >2) I found that the resulting file has the following error:
>> >
>> >Misses in library cache during parse: 0
>> >Optimizer goal: CHOOSE
>> >Parsing user id: 37 (TESTING)
>> >error during parse of EXPLAIN PLAN statement
>> >ORA-00942: table or view does not exist
>> >
>> >However, I have already run the utlxplan.sql (user TESTING). Why such
>an
>> >error message
>> >shown and how to solve this problem?
>> >
>> >3) Besides the SQL statmenet that I executed, I find that two more
>> >statmenets:
>> >
>> >OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
>> >...
>> >OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
>> >...
>> >
>> >Is the above information generated by Oracle other sessions when
>using
>> >TKPROF? What is the use of the above information? SHould I ignore
>this?
>> >
>>
>> 1) No, the trace file name always includes the hex of the process id
>> on the server. You could easily calculate the projected trace file. If
>> you want statements to go to different files you need to reconnect and
>> you still have the chance of getting the same file.
>>
>> 2) TKPROF creates it's own plan table in the user specified by
>> explain= . You should either specify the user who originally ran the
>> statements or the owner of the tables.
>>
>> 3) The overall totals are overall totals, they are calculated by
>> tkprof from the trace file,as you could suspect
>> >Thanks,
>> >Alan
>> >
>> >
>> >Sent via Deja.com http://www.deja.com/
>> >Before you buy.
>>
>> Hth,
>>
>> Sybrand Bakker, Oracle DBA
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Mon Nov 15 1999 - 02:48:32 CET

Original text of this message