RE: tkprof insert=

From: Larry Elkins <elkinsl_at_flash.net>
Date: Sun, 12 Jun 2005 20:33:07 -0500
Message-ID: <LCEKKJMEKOFIGHAMKJJJOEGICKAA.elkinsl@flash.net>

"Most output table columns correspond directly to the statistics that appear in the
formatted output file. For example, the PARSE_CNT column value corresponds to
the count statistic for the parse step in the output file."

With that in mind, and sample tkprof output below:

call count cpu elapsed disk query current rows ---- ------- ------- --------- -------- -------- ------- ------ Parse 1 0.16 0.29 3 13 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.03 0.26 2 2 4 14

So FETCH_COUNT,FETCH_ELAP,FETCH_DISK,FETCH_QUERY,FETCH_CURRENT FETCH_ROWS, those columns in the tkprof_table correspond to each of the columns on the Fetch row in the example above.

Regarding the INSERT= problem, that occurs on pl/sql blocks. For example, I ran a quick little trace with a pl/sql block and here is what was in the file for inserting:

INSERT INTO tkprof_table VALUES
(
  SYSDATE, 1, 0, 59, 1, 390563, 433787, 0, 6, 0, 1

, 1, 0, 14027, 0, 0, 0, 0, 1
, 0, 0, 0, 0, 0, 0, 0, 18478
, 'declare

i number;
begin
select 1 into i from dual;
end;
');

And here is what happened in SQL*Plus:

SQL> INSERT INTO tkprof_table VALUES
  2 (
  3 SYSDATE, 1, 0, 59, 1, 390563, 433787, 0, 6, 0, 1

  4  , 1, 0, 14027, 0, 0, 0, 0, 1
  5  , 0, 0, 0, 0, 0, 0, 0, 18478
  6  , 'declare

  7 i number;
ERROR:
ORA-01756: quoted string not properly terminated

SQL> begin
  2 select 1 into i from dual;
  3 end;
  4 ');

The ";" after the declaration of the variable confused SQL*Plus. I don't *think* this problem existed if you used server manager. What I do is delete the inserts for procedural code and then run the script.

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781
> -----Original Message-----

> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Green Red
> Sent: Sunday, June 12, 2005 4:20 AM
> To: oracle-l_at_freelists.org
> Subject: tkprof insert=
>
>
> I have seached on google for days, did find anything relevant for
> descriptions of columns of the table tkprof_table (created when
> you run the
> script created by tkprof with insert= )
>
> when I try to run the script generated by tkprof it always fail with
> SQL*Plus which lead me to copy and paste the insert statements one-by-one
> and fix any error when it appear, which is very time consuming
> and lead to
> duplicated rows created, please kindly point out is there any thing I did
> was wrong?
>
> I used,
> oracle server is 9.2.0.2 on HPUX 11
> tkprof of oracle 9.2.0.4 on HPUX 11
> sqlplus 9.2.0.4 on HPUX 11
> and sqlplus 9.2.0.6 on windows 2000
>
> when I create records for one of my trace file on the instance (HPUX 11
> 9.2.0.2) I can see a very big number (like 9.2234E+18) for the column
> fetch_rows for a select statement ran once and fetched 1 row only (the
> fetch_count column is 336) I did open the row trace file and look
> for that
> entry and did not see any string look like that, just wonder if
> there is bug
> in tkprof that produce this? and what is fetch_rows of
> tkprof_table anyway?
> I have tried tested out with a (windows 2000 9.2.0.6) instance
> and the same
> column is normal (showed same value as the fetch_count or exe_count) !

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Jun 12 2005 - 22:18:54 CDT