Home » SQL & PL/SQL » SQL & PL/SQL » tkprof
tkprof [message #37627] Mon, 18 February 2002 02:04 Go to next message
krishna
Messages: 141
Registered: October 1998
Senior Member
I issued the statement
tkprof c:oracletkprofinput.trc c:oracletkprofoutput.trc sys=no explain=plsql/plsql sort=prsela,exeela,fchela print=10
where plsql is the username. Now how do i find out the time elapsed in servicing a query?
Re: tkprof [message #37637 is a reply to message #37627] Mon, 18 February 2002 08:26 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
before you generate the trace file, you need timed_statistics set on.
alter session set timed_statistics=ture;
alter session set sql_trace=true;
-- do some tracnsactions...
alter session set sql_trace=fasle;
-- now run tkprof
Re: tkprof [message #37647 is a reply to message #37637] Mon, 18 February 2002 23:54 Go to previous messageGo to next message
krishna
Messages: 141
Registered: October 1998
Senior Member
For finding out the time elapsed in servicing a query, i followed these steps:

alter session set timed_statistics=true;
alter session set sql_trace=true;
Now i did
select * from emp;
Then,
alter session set sql_trace=false;
Finally,
tkprof c:oracletkprofinput.trc c:oracletkprofoutput.prf explain=plsql/plsql table=plsql.temp_plan_table_a insert =storea.sql sys=no sort=(execpu,fchcpu)
But when i open c:oracletkprofoutput.prf, the file does not contain any statistics. Why is this? What am i doing wrong?
Re: tkprof [message #37660 is a reply to message #37637] Tue, 19 February 2002 07:51 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
In the example below, the time was very small, so figures aren't accurate. Make sure that timed_statistics is modifiable on your system. In 7.3.x you still needed to change it in the init.ora ans restart the db. Not sure when it changed.
select name, value, isses_modifiable
from v$parameter
where name = 'timed_statistics'

NAME VALUE ISSES_MODIFIABLE
---------------------------------------- ---------------------------------------- ----------------
timed_statistics FALSE TRUE
1 row selected

SQL> alter session set timed_statistics=true;
Session altered.
SQL> alter session set sql_trace=true;
Session altered.

SQL> select * from xyz where rownum <= 1;
ID COMMENTS CREATED
---------- -------------------- ---------
1 hello world1 19-NOV-01

SQL> alter session set sql_trace=false;
Session altered.
SQL> select value from v$parameter where name = 'user_dump_dest';

VALUE
--------------------------------------------------------------------------------
/apps/oracle/admin/dev_db/udump

Find your file in the directory above (ls -ltr).

tkprof tcdev1_ora_12204.trc tcdev1_ora_12204.log explain=maceya/pass@dev_db sys=no

cat tcdev1_ora_12204.log

...
select *
from
xyz where rownum <= 1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 24 1 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 1 1 4 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.02 0.01 1 25 5 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44 (MACEYA)

Rows Row Source Operation
------- ---------------------------------------------------
1 COUNT STOPKEY
1 TABLE ACCESS FULL XYZ

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 COUNT (STOPKEY)
1 TABLE ACCESS (FULL) OF 'XYZ'

...
Previous Topic: Dynamic SQLs
Next Topic: COMMIT inside a PL/SQL script
Goto Forum:
  


Current Time: Fri Mar 29 05:16:08 CDT 2024