Date: Thu, 16 Mar 2000 10:57:18 +0200 Subject: Analyze the tkprof output This is a multi-part message in MIME format. ------=_NextPart_000_0044_01BF8F36.659F7C20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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_0044_01BF8F36.659F7C20 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

HI , ALL !

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

There is a Java program that runs SQL = queries =20 , and it looks like each statement is parsed 3 times for each execution = when=20 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 = .

Both the Oracle8i and Windows are new = for me . I=20 previously used Oracle8 on HP-UX and i never seen this kind of tkprof = results=20 .

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       = disk     =20 query    = current       =20 rows
------- ------ -------- ---------- ---------- ----------=20 ---------- =20 ----------------------------------------
Parse    =   =20 15      = 0.03      =20 0.03         =20 0         =20 0         =20 0          =20 0
Execute     5     =20 0.00      =20 0.00         =20 0         =20 0         =20 0          =20 0
Fetch        =20 5      0.00       = 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 program=20 i see the following :

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

call    =20 count       cpu   =20 elapsed       = disk     =20 query    = current       =20 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       = 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           = 4