| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Analyze the tkprof output
This is a multi-part message in MIME format.
------=_NextPart_000_009A_01BF932A.5AEB41E0 Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi , Thomas & all !
U're right , but i'm talking about extensive parsing for exactly the =
same statement .=20
For example this :
SELECT emp_data=20
from
emp where emp_id=3D:1
call count cpu elapsed disk query current =
rows
------- ------ -------- ---------- ---------- ---------- ---------- =
Parse 8 0.01 0.01 0 0 0 =
0
Execute 4 0.00 0.00 0 0 0 =
0
Fetch 4 0.00 0.00 0 4 16 =
4
total 16 0.01 0.01 0 4 16 =
4
U see , there are 2 parses for each fetch and one execute for each fetch =
.
As far as i understand there should be 1 parse and one execute for all =
the 4 fetches . Isn't it correct ?=20
TIA=20
You're SQL statements are significantly different. comparing ename like 'SMITH%' is not the same as empid =3D :1 You will need to compare two similar statements such as empid =3D 1234 = and empid =3D :1
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Andrey =
Bronfin
Sent: Tuesday, May 09, 2000 4:18 AM
To: Multiple recipients of list ORACLE-L
Subject: Analyze the tkprof output=20
HI , ALL !
I'm having a pretty strange tkprof output of a SQL trace .
There is a Java program that runs SQL queries , and it looks like =
each statement is parsed 3 times for each execution when we're not using =
bind variables ,=20
and each statement is parsed 2 times when we're using bind = variables .
There is also an execution for every fetch of a query .
We use Oracle 8.1.5 on Windows2000 .
Both the Oracle8i and Windows are new for me . I previously used =
Oracle8 on HP-UX and i never seen this kind of tkprof results .
Please help !
TIA=20
Here is what i have in my *.prf file :
Select ename,emp_id
from
emp where ename like 'SMITH%' order by ename
call count cpu elapsed disk query =
current rows
------- ------ -------- ---------- ---------- ---------- =
---------- ----------------------------------------
Parse 15 0.03 0.03 0 0 =
0 0
Execute 5 0.00 0.00 0 0 =
0 0
Fetch 5 0.00 0.00 0 5 =
0 10
------- ------ --------------------------------- -------- =
---------- ---------- ---------- ---------- ----------
total 25 0.03 0.03 0 5 =
0 10
Misses in library cache during parse: 0
When we use bind variables in the Java program i see the following =
:
SELECT emp_data=20
from
emp where emp_id=3D:1
call count cpu elapsed disk query =
current rows
------- ------ -------- ---------- ---------- ---------- =
---------- -----------------------------------------
Parse 8 0.01 0.01 0 0 =
0 0
Execute 4 0.00 0.00 0 0 =
0 0
Fetch 4 0.00 0.00 0 4 =
16 4
------- ------ -------- ---------- ---------- ---------- =
---------- -----------------------------------------------
total 16 0.01 0.01 0 4 =
16 4
Thanks a lot in advance !!
------=_NextPart_000_009A_01BF932A.5AEB41E0 Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META content=3D"text/html; charset=3Diso-8859-1" =http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2920.0" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT face=3DArial size=3D2>Hi , Thomas & all !</FONT></DIV> <DIV><FONT face=3DArial size=3D2>U're right , but i'm talking about =extensive=20
<DIV><FONT face=3DArial size=3D2>For example this :</FONT></DIV> <DIV><FONT face=3DArial size=3D2> <DIV><FONT face=3DArial size=3D2>SELECT emp_data = <BR>from<BR> emp where=20
rows<BR>------- ------ -------- ---------- ---------- ----------=20 ---------- =20 -----------------------------------------<BR>Parse  =; =20
0.01 =20
0 =20 0 =20 0 =200<BR>Execute = 4 =20
0 =20 0 =20 0 =200<BR>Fetch =20 4 0.00 =
0.00 =20 0 =20 4 =20 16 =
4<BR>-------=20 ------ -------- ---------- ---------- ---------- ---------- =20 -----------------------------------------------<BR>total  =; =20 16 =
<DIV> </DIV> <DIV>TIA </DIV></FONT></DIV> <DIV> </DIV> <BLOCKQUOTE=20
</DIV> <DIV><BR></DIV> <DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20class=3D290122006-10052000>You're SQL statements are significantly=20 different.</SPAN></FONT></DIV>
size=3D2>-----Original Message-----<BR><B>From:</B> <A=20
href=3D"mailto:root_at_fatcity.com">root_at_fatcity.com</A> [<A=20
href=3D"mailto:root_at_fatcity.com">mailto:root_at_fatcity.com</A>]<B>On =
Behalf Of=20
</B>Andrey Bronfin<BR><B>Sent:</B> Tuesday, May 09, 2000 4:18=20 AM<BR><B>To:</B> Multiple recipients of list = ORACLE-L<BR><B>Subject:</B>=20
Analyze the tkprof output <BR><BR></DIV></FONT>
<BLOCKQUOTE=20
style=3D"BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; =
MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">
<DIV><BR></DIV>
<DIV><FONT face=3DArial size=3D2>HI , ALL !</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>I'm having a pretty strange =
tkprof output of=20
a SQL trace .</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>There is a Java program that runs =
SQL=20
queries , and it looks like each statement is parsed 3 times =
for=20
each execution when we're not using bind variables , </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>and each statement is =
parsed 2=20
times when we're using bind variables =
.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>There is also an execution for =
every fetch of=20
a query .</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>We use Oracle 8.1.5 on =
Windows2000=20
.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Both the Oracle8i and Windows are =
new for me=20
. I previously used Oracle8 on HP-UX and i never seen this kind of =
tkprof=20
results .</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Please help !</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>TIA </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Here is what i have in my *.prf =
file=20
:</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Select =
ename,emp_id<BR>from<BR> emp=20
where ename like 'SMITH%' order by ename</FONT></DIV>
<DIV><FONT face=3DArial size=3D2><BR>call =20
count cpu =20
elapsed =20
disk query =20
current rows<BR>-------=20
------ -------- ---------- ---------- ---------- =
---------- =20
=
----------------------------------------<BR>Parse =
=20
15 =
0.03 =20
0.03 =20
0 =20
0 =20
0 =20
0<BR>Execute =
5 =20
0.00 =20
0.00 =20
0 =20
0 =20
0 =20
0<BR>Fetch =20
5 =
0.00 =20
0.00 =20
0 =20
5 =20
0 =
10<BR>-------=20
------ --------------------------------- -------- ----------=20
---------- ---------- ---------- =20
----------<BR>total =20
25 =
0.03 =20
0.03 =20
0 =20
5 =20
0 =
10</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Misses in library cache during =
parse:=20
0</FONT></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>When we use bind variables =
in the Java=20
program i see the following :</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>SELECT emp_data =
<BR>from<BR> emp=20
where emp_id=3D:1</FONT></DIV>
<DIV><FONT face=3DArial size=3D2><BR>call =20
count cpu =20
elapsed =20
disk query =20
current rows<BR>-------=20
------ -------- ---------- ---------- ---------- =
---------- =20
=
-----------------------------------------<BR>Parse  =
; =20
8 =
0.01 =20
0.01 =20
0 =20
0 =20
0 =20
0<BR>Execute =
4 =20
0.00 =20
0.00 =20
0 =20
0 =20
0 =20
0<BR>Fetch =20
4 =
0.00 =20
0.00 =20
0 =20
4 =20
16 =20
4<BR>------- ------ -------- ---------- ---------- =
----------=20
---------- =20
=
-----------------------------------------------<BR>total  =
; =20
16 =
0.01 =20
0.01 =20
0 =20
4 =20
Received on Tue Mar 21 2000 - 03:41:11 CST
 |
 |