| 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_0004_01BFBA1F.E73313A0 Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
You're SQL statements are significantly different.
comparing ename like 'SMITH%' is not the same as empid = :1
You will need to compare two similar statements such as empid = 1234 and
empid = :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
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 ,
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
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
Misses in library cache during parse: 0
When we use bind variables in the Java program i see the following :
SELECT emp_data
from
emp where emp_id=:1
call count cpu elapsed disk query current rows
Thanks a lot in advance !!
------=_NextPart_000_0004_01BFBA1F.E73313A0 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.2919.6307" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =class=3D290122006-10052000>You're=20
<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 a=20
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 each=20
execution when we're not using bind variables , </FONT></DIV> <DIV><FONT face=3DArial size=3D2>and each statement is parsed 2 = times =20
when we're using bind variables .</FONT></DIV> <DIV><FONT face=3DArial size=3D2>There is also an execution for = every fetch of a=20
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 =
0.00 =20 0.00 =20
0 =20
0 =20
0 =20
0<BR>Fetch =20
5 =
0.00 =20
0 =20
5 =20
0 =
10<BR>-------=20
------ --------------------------------- -------- ---------- =
----------=20
 |
 |