tkprof [message #37627] |
Mon, 18 February 2002 02:04 |
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 |
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 |
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 |
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'
...
|
|
|