There is bug 1210242 on 8.1.5 about reparsing. I do
not know if it is generic or unix only.
With timed_statistics or sql_trace equal to true,
Oracle doesn't share the cursors, so it is doing way
too much parsing.
After putting timed_statistics to false, performance
was much better in our case.
- Andrey Bronfin <bronfin_at_VisualTop.com> a écrit:
> 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 !!
>
Stephane Paquette
DBA Oracle
stephane_paquette_at_yahoo.com
spaquette_at_houra.fr
(33) 01 53 93 06 50
Do You Yahoo!?
Received on Tue May 09 2000 - 03:54:32 CDT
