| tkprof [message #9131] |
Tue, 21 October 2003 02:15  |
Anu
Messages: 82
Registered: May 2000
|
Member |
|
|
Hi,
I wanted to know how to use tkprof ie., I want to check the performance of these two queries using this utility.
Select count(*) from emp;
Select count(1) from emp;
Thanks.
Anu
|
|
|
|
|
|
| Re: tkprof [message #9133 is a reply to message #9132] |
Tue, 21 October 2003 06:25  |
sancha
Messages: 31
Registered: October 2003
|
Member |
|
|
hi
sorry if i m interrupting the thread.
1. but thiru, i think wht anu asked for is "how to use pkprof". and ur reply didnt answer that question.
pls tell how to work with pkprof?
2. and thiru, do i need to be a dba or sysadmin to see the output or to work in "sqltrace" ? since i am not provided with that rights i am not able to see the execution plan :-(
hoping for ur reply.
bfn
|
|
|
|
| Re: tkprof [message #9134 is a reply to message #9133] |
Tue, 21 October 2003 07:33 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Using Tkprof:
1. i) Enable timed_Statistics ( system or session)
ii) Enable tracing ( session)
iii) Run the queries
iv) Disable tracing
v) close your session ,preferably
vi) Identify the generated trace file
vii) Format the tracefile using Tkprof
viii) Interpret the output
eg)
thiru@9.2.0:SQL>alter session set timed_statistics=true;
Session altered.
-- There are many ways to enable tracing..Lets choose the simplest one
thiru@9.2.0:SQL>alter session set sql_trace=true;
Session altered.
thiru@9.2.0:SQL>select count(1) from t;
COUNT(1)
----------
15
thiru@9.2.0:SQL>select count(*) from t;
COUNT(*)
----------
15
thiru@9.2.0:SQL>alter session set sql_trace=false;
Session altered.
-- Identify your process id inorder to locate the associated trace file.Again, this is easier and makes sense when using DEDICATED connections. When using MTS(Shared servers) , the same session could be served by multiple Server processes and the same Server process could serve multiple sessions (ie there is a MANY TO MANY relation) and hence becomes almost impossible..
thiru@9.2.0:SQL>select p.spid from V$process p,v$session s where p.addr=s.paddr and
2 s.audsid=sys_context('userenv','sessionid');
SPID
------------
2912
-- Now just look at your USER_DUMP_DEST directory and scan for this process id(ie 2912). I got the file
-- Now format the trace file
tkprof thiru_ora_2912.trc count.dat
-- Interpret the o/p
more count.dat
TKPROF: Release 9.2.0.1.0 - Production on Tue Oct 21 08:32:28 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: thiru_ora_2912.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
select count(1)
from
t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.05 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.07 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 73
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
15 TABLE ACCESS FULL T
********************************************************************************
select count(*)
from
t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.09 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.09 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 73
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
15 TABLE ACCESS FULL T
********************************************************************************
alter session set sql_trace=false
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.03 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 73
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.18 0 0 0 0
Execute 3 0.00 0.32 0 0 0 0
Fetch 3 0.00 0.00 0 6 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.00 0.51 0 6 0 2
Misses in library cache during parse: 2
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
3 user SQL statements in session.
0 internal SQL statements in session.
3 SQL statements in session.
********************************************************************************
Trace file: thiru_ora_2912.trc
Trace file compatibility: 9.00.01
Sort options: default
1 session in tracefile.
3 user SQL statements in trace file.
0 internal SQL statements in trace file.
3 SQL statements in trace file.
3 unique SQL statements in trace file.
------------------------------------------------------
2. Its easier to work with trace files/tkprof if you are a DBA,but its not mandatory. And EVERYONE can see the query's execution plan/statistics just by using Explain Plan or sqlplus Autotrace ( I believe I have explained how to set this up in one of my previous postings). You dont need Tkprof if you just want to see your execution plan or say logical/physical reads,sorts etc.
Say,you have access(login) to your development/test Server where you are generating the trace file,then you can access it , if your DBA has set
_TRACE_FILES_PUBLIC = true
which makes the trace files publicly accessible.
or you can ask for the trace file from your DBA !
Hope this helps
Thiru
|
|
|
|
| Re: tkprof [message #9135 is a reply to message #9134] |
Tue, 21 October 2003 12:40 |
sancha
Messages: 31
Registered: October 2003
|
Member |
|
|
hi thiru,
thnx for the reply.
sorry for intruding into this thread.
thnx again for ur patience in answering my question.
bfn
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
