comparing tkprof results between cursor_sharing = exact or similar

10.2.0.2 on Sun Solaris
All base tables and indexes have been analyzed.

I'm trying to tune some SQL that comes from the application. Unfortunately it is a really complex view, which joins 7 tables, and doesn't use bind variables.   The following is from tkprof

SELECT * from complex_vw where col = 27 ;

call count cpu elapsed disk query current rows ------- ------ ----- ------- ---- ----- ------- ----

Parse        1  13.86   13.64    0     0       0    0
Execute      1   0.00    0.00    0     0       0    0
Fetch        1   0.31    0.31    0  6817       0    0
------- ------  ----- ------- ---- ----- ------- ----
total        3  14.17   13.95    0  6817       0    0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS

The above results appear 9 times through the tkprof file.

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ----- ---- ------- ---- ----- ------- ----

Parse      90  0.01    0.00    0     0       0    0
Execute    90  0.02    0.02    0     0       0    0
Fetch      90  0.00    0.00    0   180       0   90


------- -----  ---- ------- ---- ----- ------- ----

total     270  0.03    0.03    0   180       0   90

Misses in library cache during parse: 0

        1  session in tracefile.
       14  user  SQL statements in trace file.
       90  internal SQL statements in trace file.
      104  SQL statements in trace file.
       15  unique SQL statements in trace file.
     2003  lines in trace file.
      127  elapsed seconds in trace file.

Every time the where clause changes it has to parse it again and thus another 13.86 seconds is consumed.

So, I set cursor_sharing = similar and I get the following:

SELECT * from complex_vw where col = :"SYS_B_0" ;

call count cpu elapsed disk query current rows ------- ------ ------ ------- ---- ------ ------- ----

Parse        9   0.03    0.01    0      0       0    0
Execute      9 127.74  126.10    0      0       0    0
Fetch        9  14.26   14.85  338 905595       0    0
------- ------ ------ ------- ---- ------ ------- ----
total       27 142.03  140.97  338 905595       0    0

Misses in library cache during parse: 9
Misses in library cache during execute: 9 Optimizer mode: ALL_ROWS

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ ---- ------- ---- ----- ------- ----

Parse      168 0.04    0.04    0     0       0    0
Execute    545 0.28    0.24    0     0       0    0
Fetch     1232 0.10    0.09    0  2261       0 2085
------- ------ ---- ------- ---- ----- ------- ----
total     1945 0.42    0.39    0  2261       0 2085

Misses in library cache during parse: 26 Misses in library cache during execute: 26

        1  session in tracefile.
       14  user  SQL statements in trace file.
      545  internal SQL statements in trace file.
      559  SQL statements in trace file.
       32  unique SQL statements in trace file.
    11231  lines in trace file.
      141  elapsed seconds in trace file.

My question is why does Execute cpu value go up to 127.74 seconds when it is using bind variables (with changing values). If I execute the same query over and over (with the same bind variable value) then it executes sub second.

The explain plans between

cursor_sharing = exact or similar

is the same.

I realise that the query is returning 0 rows, but even when it returns 4 rows it takes just as long to run. Received on Sun Mar 04 2007 - 21:01:34 CST