Re: Execute to Parse %:

Brian wrote:
> I did some more research. A cursor is not parsed when the application
> keeps executing the same cursor over and over aging with the exact same
> values in the bind variables. So the Execute to Parse Ratio is really
> measuring how often the application keeps executing the same SQL
> statement (and getting the exact same results).
>
> Not sure why the target is 100%. A low ratio in my case really means
> users are passing different values to bind variables and querying
> different rows the DB.
>

How did you research this? Because that is demonstratably and utterly wrong. Oracle only has to parse if the statement itself changes. It doesn't care what values the bind variables are. That's why you don't blindly cry 'Bind variables in all cases' because if due to data skew different values produce different execution plans, you want the best possible plan for your query and not the one that was optimized for a different question.

Look at the following tkprof snippet:

begin

   for i in 1..1000 loop
     insert into demo values (i);
   end loop;
end;

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

Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.08       0.10          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.09       0.11          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 62

Elapsed times include waiting on following events:

   Event waited on                             Times   Max. Wait  Total Waited
   ----------------------------------------   Waited  ----------  ------------
   SQL*Net message to client                       1        0.00          0.00
   SQL*Net message from client                     1        2.62          2.62
********************************************************************************

INSERT INTO DEMO
VALUES
  (:B1 )

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

Parse        1      0.01       0.00          0          0          0           0
Execute   1000      0.29       0.25          0          4       1075        1000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1001      0.30       0.26          0          4       1075        1000

Misses in library cache during parse: 1
Misses in library cache during execute: 1 Optimizer mode: CHOOSE
Parsing user id: 62 (recursive depth: 1)

Wow, one parse but 1000 executions with different values, too!

Regards,
Holger Received on Fri Jul 01 2005 - 08:57:05 CDT