Difficulty to interpret a TKPROF output

[toto4UK]

Hello,

One of my queries running with Oracle 8.1.7.4 on a SPARC machine with SunOS
5.8 has been
traced and TKPROF gave me the following result for PEF stats :

Need to say that query is a simple SELECT statement using a WHERE clause
parametrized with bind vars
given to Oracle through JDBC Thin Driver

call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ------
----
Parse 590 0.23 0.23 0 0 0
0
Execute 590 0.30 0.29 0 0 0
0
Fetch 590 0.37 0.35 0 1154 4616
544
------- ------ -------- ---------- ---------- ---------- ---------- ------
----
total 1770 0.90 0.87 0 1154 4616
544

Misses in library cache during parse: 1
Optimizer goal: CHOOSE

I wanted to know why that query has so many parses.

Thanx for answers.

Sebastian.

[Frank]

Re: Difficulty to interpret a TKPROF output

toto4UK wrote:

Hello,
>
One of my queries running with Oracle 8.1.7.4 on a SPARC machine with SunOS
5.8 has been
traced and TKPROF gave me the following result for PEF stats :
>
Need to say that query is a simple SELECT statement using a WHERE clause
parametrized with bind vars
given to Oracle through JDBC Thin Driver
>
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ------
----
Parse 590 0.23 0.23 0 0 0
0
Execute 590 0.30 0.29 0 0 0
0
Fetch 590 0.37 0.35 0 1154 4616
544
------- ------ -------- ---------- ---------- ---------- ---------- ------
----
total 1770 0.90 0.87 0 1154 4616
544
>
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
>
I wanted to know why that query has so many parses.
>
Thanx for answers.
>
Sebastian.
>
>

Bind variables - your code doesn't have them, and needs to be parsed
every time again. One exec = one parse...

--
Regards, Frank van Bortel

[Mr. DB]

Re: Difficulty to interpret a TKPROF output

Frank <fvanbortel@net scape.netwrote in message news:<bl413q$89 6$1@news4.tilbu 1.nb.home.nl>.. .

toto4UK wrote:
>

Hello,

One of my queries running with Oracle 8.1.7.4 on a SPARC machine with SunOS
5.8 has been
traced and TKPROF gave me the following result for PEF stats :

Need to say that query is a simple SELECT statement using a WHERE clause
parametrized with bind vars
given to Oracle through JDBC Thin Driver

call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ------
----
Parse 590 0.23 0.23 0 0 0
0
Execute 590 0.30 0.29 0 0 0
0
Fetch 590 0.37 0.35 0 1154 4616
544
------- ------ -------- ---------- ---------- ---------- ---------- ------
----
total 1770 0.90 0.87 0 1154 4616
544

Misses in library cache during parse: 1
Optimizer goal: CHOOSE

I wanted to know why that query has so many parses.

Thanx for answers.

Sebastian.

Bind variables - your code doesn't have them, and needs to be parsed
every time again. One exec = one parse...

The following post is incorrect. Notice that "misses in library cache
during parse" is equal to 1. You are seeing 590 total parses, of
which 1 was a hard parse. The remaining 589 parses are what is known
as soft-parses.

The reason why you have so many parses is because the client is
issuing the OCI oparse() call, or the equivalent in java. If this is
OCI or java code, there are ways to get around execution of the parse
calls. If this is a C/C++ program, there are precompiler options that
will prevent the unnecessary parse calls.

If you didn't have bind variables, you would see "misses in library
cache during parse" equal 590.