Message-Id: <10493.105309@fatcity.com> From: "Thomas L. Harleman" Date: Wed, 10 May 2000 01:34:37 -0500 Subject: 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@fatcity.com [mailto:root@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 ------- ------ -------- ---------- ---------- ---------- ---------- -- -------------------------------------- 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 from emp where emp_id=: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_0004_01BFBA1F.E73313A0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

You're=20 SQL statements are significantly different.

comparing ename like 'SMITH%' is not the same = as empid=20 =3D :1

You=20 will need to compare two similar statements such as empid =3D 1234 and = empid =3D=20 :1

-----Original Message-----
From: root@fatcity.com=20 [mailto:root@fatcity.com]On Behalf Of Andrey = Bronfin
Sent:=20 Tuesday, May 09, 2000 4:18 AM
To: Multiple recipients of = list=20 ORACLE-L
Subject: Analyze the tkprof output =

HI , ALL !

I'm having a pretty strange tkprof = output of a=20 SQL trace .

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 ,

and each statement is parsed 2 = times =20 when we're using bind variables .

There is also an execution for = every fetch of a=20 query .

We use Oracle 8.1.5 on Windows2000=20 .

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 .

Please help !

TIA

Here is what i have in my *.prf = file=20 :

Select = ename,emp_id
from
emp=20 where ename like 'SMITH%' order by ename

call    =20 count       cpu   =20 elapsed      =20 disk      query   =20 current        rows
-------=20 ------ -------- ---------- ---------- ---------- = ---------- =20 = ----------------------------------------
Parse    =   =20 15      = 0.03      =20 0.03         =20 0         =20 0         =20 0          =20 0
Execute     5      = 0.00      =20 0.00         =20 0         =20 0         =20 0          =20 0
Fetch       =20 5      = 0.00      =20 0.00         =20 0         =20 5         =20 0          = 10
-------=20 ------ --------------------------------- -------- ---------- = ----------=20 ---------- ---------- =20 ----------
total      =20 25      = 0.03      =20 0.03         =20 0         =20 5         =20 0          = 10

Misses in library cache during = parse:=20 0

When we use bind variables in = the Java=20 program i see the following :

SELECT emp_data =
from
emp=20 where emp_id=3D:1

call    =20 count       cpu   =20 elapsed      =20 disk      query   =20 current        rows
-------=20 ------ -------- ---------- ---------- ---------- = ---------- =20 = -----------------------------------------
Parse    = ;    =20 8      = 0.01      =20 0.01         =20 0         =20 0         =20 0          =20 0
Execute      = 4     =20 0.00      =20 0.00         =20 0         =20 0         =20 0          =20 0
Fetch        =20 4      = 0.00      =20 0.00         =20 0         =20 4        =20 16           = 4
-------=20 ------ -------- ---------- ---------- ---------- = ---------- =20 = -----------------------------------------------
total   = ;      =20 16      = 0.01      =20 0.01         =20 0         =20 4        =20 16          =20