Skip to Main Content
  • Questions
  • How can execute/parse ratio be small or negative, and soft parse ratio be so high?

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Doug.

Asked: May 26, 2002 - 7:23 pm UTC

Last updated: August 17, 2012 - 1:14 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Tom, I have statistics during a certain period at night that always indicate a soft parse ratio of 99.99% and an execute to parse ratio that is something like -.01 or .01 or .02 or .1 i.e., something very low. How is this possible? I understand from a different post that a negative execute to parse ratio can be caused by parsing statments and never executing them. But, I thought the soft parse ratio was indicitive of finding a statement in the shared pool already that we need to soft parse and execute. How can the two ratios be so different? Cursor_sharing=FORCED in this example. Is this an idiosyncrasy of cursor_sharing=forced?

and Tom said...

That means the PARSES are greater than or equal to the EXECUTES. The formula used it:

,' Execute to Parse %:' dscr
, round(100*(1-:prse/:exe),2) pctval

As prse = exe, the execute/parse % goes to ZERO. As prse starts to exceed executes, the % goes NEGATIVE.

This happens when an application parses a query but never executes it. Some applications (generic apps typically) parse a "select * from T" to describe a table, never execute the cursor -- their parses exceed their executes.

The soft parse ratio is good cause they parse "select * from t" hundreds of times -- and each parse is in fact a soft parse. They just parse way too much.

A soft parse happens during the PARSE. You never need execute the query to increase the soft parse count. So the two are not really related in any way. You can have a low execute to parse with a high soft parse ratio, you can have a high execute to parse with ah low soft parse ratio as well. They are two indicators that you look at independent of each other.



Rating

  (28 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Nothing to do with Cursor_sharing=forced?

Doug, May 28, 2002 - 12:25 am UTC

Tom - if I turn cursor_sharing back to exact during the same period of activity, the execute/parse goes up to around 20-30% and the soft parse goes down. Does that make any sense? Can the over-binding you discuss in your book cause excess parsing?

Tom Kyte
May 28, 2002 - 7:21 am UTC

I ran this:


ops$tkyte@ORA817DEV.US.ORACLE.COM> alter system flush shared_pool;

System altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter system set cursor_sharing=exact;

System altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec perfstat.statspack.snap

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2          type rc is ref cursor;
  3  
  4          l_cursor rc;
  5  begin
  6          for i in 1 .. 1000
  7          loop
  8                  open l_cursor for 'select * from dual where dummy = ''' || i || '''';
  9                  close l_cursor;
 10          end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec perfstat.statspack.snap

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter system flush shared_pool;

System altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter system set cursor_sharing=force;

System altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec perfstat.statspack.snap

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2          type rc is ref cursor;
  3  
  4          l_cursor rc;
  5  begin
  6          for i in 1 .. 1000
  7          loop
  8                  open l_cursor for 'select * from dual where dummy = ''' || i || '''';
  9                  close l_cursor;
 10          end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec perfstat.statspack.snap

PL/SQL procedure successfully completed.


and the stats pack shows 

a) no measurable difference in the execute to parse
b) a huge difference in the soft parse %


The only thing I can hypothesize in your case is that the execute/parse would have gone upto 20-30% <b>all by itself anyway as it is 100% controlled by the client code and cannot be affected by the server settings at all.  It is 100% a function of the number of times the client parses a sql statement and how many times they execute it</b>.  There must have been something different about the execution path of your code during that time.


<b>stats pack with cursor_sharing=exact and hard parse of 1,000 statements:</b>

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            159,598.00            319,196.00
              Logical reads:              1,281.00              2,562.00
              Block changes:                183.00                366.00
             Physical reads:                  6.50                 13.00
            Physical writes:                  0.00                  0.00
                 User calls:                  3.00                  6.00
                     Parses:                570.00              1,140.00<b>
                Hard parses:                505.50              1,011.00</b>
                      Sorts:                 42.50                 85.00
                     Logons:                  0.00                  0.00
                   Executes:                594.50              1,189.00
               Transactions:                  0.50

  % Blocks changed per Read:   14.29    Recursive Call %:               99.89
 Rollback per transaction %:    0.00       Rows per Sort:               59.49

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:              100.00
            Buffer  Hit   %:   99.49    In-memory Sort %:              100.00
            Library Hit   %:   73.12     <b>   Soft Parse %:               11.32
         Execute to Parse %:    4.12       </b>  Latch Hit %:              100.00
Parse CPU to Parse Elapsd %:   97.98     % Non-Parse CPU:               99.61




-------------------------------------------------------------------------
<b>stats pack with cursor_sharing=force</b>

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            178,840.00            357,680.00
              Logical reads:              1,310.00              2,620.00
              Block changes:                226.00                452.00
             Physical reads:                  0.00                  0.00
            Physical writes:                 62.00                124.00
                 User calls:                  3.00                  6.00
                     Parses:                568.00              1,136.00<b>
                Hard parses:                  6.00                 12.00</b>
                      Sorts:                 42.50                 85.00
                     Logons:                  0.00                  0.00
                   Executes:                592.50              1,185.00
               Transactions:                  0.50

  % Blocks changed per Read:   17.25    Recursive Call %:               99.88
 Rollback per transaction %:    0.00       Rows per Sort:               59.45

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:              100.00
            Buffer  Hit   %:  100.00    In-memory Sort %:              100.00
            Library Hit   %:   93.33    <b>    Soft Parse %:               98.94
         Execute to Parse %:    4.14      </b>   Latch Hit %:               99.99
Parse CPU to Parse Elapsd %:   76.92     % Non-Parse CPU:               99.94
 

Very Puzzling

anil, May 28, 2002 - 8:32 am UTC


Sir

How a Statement given for parsing cannot Execute
Can u give me an Example of this

Regards



Tom Kyte
May 28, 2002 - 9:16 am UTC

dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );


that just parsed the query, described it (to find the number of columns, data types, names, sizes, etc).

It never executed it -- If you closed the cursor, you would have a PARSE without an execute.

clarification on execute to parse ratio

amit poddar, September 04, 2005 - 9:38 pm UTC

Hi,

I was going through this thread on c.d.o.s

</code> http://tinyurl.com/bwmpx <code>

the question is why is execute to parse ratio so less.

The responses to the question has been that the bind variables
are not being used here.

My confusion is that, without knowing the soft parse ratio how can we make this conclusion?

Since when parse to execute ratio is very small, we can conclude only one thing that the application is not reusing the cursor (which can be unavoidable in a stateless connection) i.e. the application is parsing too much, now those parse can be all soft parses that would mean that bind variables are being used correctly, or there could be lot of hard parses that would mean bind variables not being used.

Could you please tell me if I am correct in my analysis



Tom Kyte
September 05, 2005 - 10:03 am UTC

(which can be unavoidable in a
stateless connection)


Not really, it just took work on the part of the programmer OR the use today of JDBC 3.0 statement caching! It is totally avoidable using java.


</code> http://www.oracle.com/technology/products/oracle9i/daily/jun24.html <code>


but, if just given:

Execute to Parse % : 35.84
Parse CPU to Parse Elapsed % : 1.48


it would be valid to say "One of the very possible causes is lack of bind variables, since if you do not use binds we KNOW for a fact you are parsing as often as possible and the more you parse, then lower that execute to parse ratio is."

However, it is only one possible cause (just over soft parsing can do that)

Given that we are given that information AND:

These 2 are way below the 100% target. All other readings are between
97 to 100%.


it is less likely that hard parsing is the culprit (less likely), since we would expect a rather low soft parse % as well, but apparently it is at 97% or above.


In short, insufficient data to say which it it.


Not that the ratio of 35% is even telling us "there is a problem", we need to see the numbers behind the numbers here.

more clarification

amit poddar, September 05, 2005 - 11:22 am UTC

Hi,
you said it is completely avoidable using Java where we can cache prepared statement objects or use the inbuilt caching of jdbc 3.0.

my question is:

Is this avoidable in a mod_plsql environment (stateless)

after serving each request mod_plsql resets the session state, does that destroy the session_cached_cursors also ?



Tom Kyte
September 05, 2005 - 11:42 am UTC

not in mod_plsql, it does a "reset" that unfortunately closes all cursors. all of them.

clarification

amit poddar, September 06, 2005 - 9:58 am UTC

"These 2 are way below the 100% target. All other readings are between
97 to 100%.


it is less likely that hard parsing is the culprit (less likely), since we would
expect a rather low soft parse % as well, but apparently it is at 97% or above.


In short, insufficient data to say which it it."


Parse CPU to Parse Elapsed = 1.48%

Doesn't this tell us that while parsing
for every 68 seconds (over 1 minute) only 1 second was used for parsing ?

If thats true then rest of the time must have been spent while waiting on latches

library cache(in case of soft parses)
shared_pool latch (in case of hard parses)

true even then it is not sufficient to say whether it is hard parsing a lot or over soft parsing .

Could you please tell me whether my thought process here is correct ?

Tom Kyte
September 06, 2005 - 10:01 am UTC

the problem with ratios is they tell us not too much about the numbers behind the numbers. We just don't know.


The time might have been spent waiting on latches.
The time might have been spent waiting for the CPU to get us running.

But the latches for a soft parse are still really high (soft parse does MUCH of the work of a hard parse! it latches like crazy too, the only good parse -- no parse :)

So, even if it was waiting for latches, it does not imply hard parsing at all.

more clarification

amit poddar, September 06, 2005 - 10:12 am UTC

"The time might have been spent waiting on latches.
The time might have been spent waiting for the CPU to get us running."

Statspack can easily tell us whether it was waiting for latches or not.

But how would we know "if The time was spent waiting for the
CPU" from statspack. or we cannot ?

May be I am over analyzing this but ....


Tom Kyte
September 06, 2005 - 10:44 am UTC

right, but you see, all we have are those little ratios, I have a presentation where I've defined ratios as:

a mathematical device used to obscure and hide real information from view.

Given the information provided, we cannot make any claims.


In the absence of wait events, a long elapsed versus small cpu indicates a possible wait for cpu.

Parse Ratio of Package Cursor

Steven Ensslen, September 06, 2005 - 2:42 pm UTC

I'm working on a similar symptom in a PL/SQL package called by DBMS_JOB on 10.1.0.3. This database is always busy running ETL, so we're always looking for improvements (I know that this isn't a good methodology).

I have a package with function that executes a cursor (the code is poorly written, I'd love to fix it but the poltics in my workspace don't let me):

CURSOR lc_r(cv_1 VARCHAR2)
IS SELECT 'x' c
FROM dc_tab_school
WHERE dc_tab_school.datamsgcnt_datamsg_id = stag_control.get_traxFullSystemDatamsgId()
AND cv_1 = dc_tab_school.mincode;

So, there should be one parse of this cursor for every session that calls it, right? Well I an analyst came to me with the following stats that I later verified from v$sql:

SUM(EXECUTIONS) SUM(PARSE_CALLS) SUM(INVALIDATIONS)
519019 505589 0

Frankly, I'm lost. PROGRAM_ID and PROGRAM_LINE# confirm that this is the cursor from the package. I can prove that there haven't been close to 1/2 million sessions. Moreover, I can't replicate this behaviour in our test environment. If I call this function 1000 times I get 1 parse call.

I'm not sure if this matters, but CURSOR_SHARING is set to EXACT.

How can a statically declared cursor in a pl/sql package be parsed more than once per session?


Tom Kyte
September 06, 2005 - 8:56 pm UTC

what is session_cached_cursors set to?

and how many cursors do you believe this program might be using all together - answer with "dozens, hundreds, many hundreds, thousands" is sufficient

Isn't Oracle suppose to cache SQL? then why this?

Sejas Patel, December 20, 2005 - 9:50 am UTC

hi,

how do you explain the below problem from a SQLPLUS session as well as a J2EE application?

a session was traced which was used by hibernate (J2EE) to run some SQL and i received parse = 6888 and execute = 6888.

i have been able to get the same effect from sqlplus session as below.

SQL>conn vehicles/vvv
connected

--from other SYS session
SQL> select sid, username from v$session;

       SID USERNAME
---------- ------------------------------
       143
       146 VEHICLES
       148 SYS
       151
       154
       158
       160
       161
       162
       163
       164
       165
       166
       167
       168
       169
       170

SQL> exec dbms_monitor.session_trace_enable(146);

PL/SQL procedure successfully completed.

--Back to "vehicles" session
SQL> select * from model_ref where rownum < 10;
.....700 results
SQL> select * from model_ref where rownum < 10;
.....700 results
SQL> select * from model_ref where rownum < 10;
.....700 results

--Back to SYS
SQL> exec dbms_monitor.session_trace_disable(146);

PL/SQL procedure successfully completed.

C:\oracle\product\admin\orcl\udump>tkprof orcl_ora_3784.trc 146.out

Now lot of internal SQL was recorded and!!!!!!!

Can you please tell me why it parsed all 3 executions of "select * from model_ref where rownum < 10;"???????

Isn't it suppose to cache the SQL at first parse and execute it 3 times?

I would expect the ratio of 100*(1-(1/3)) = 66% but instead what i acheived is 100*(1-(3/3)) = 0% according to the ratio formula round(100*(1-(:par/:exec)),2)

Please enlighten..

********************************************************************************

select * 
from
 model_ref where rownum < 10


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.19       0.19          0         32          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0         15          0          27
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.19       0.19          0         47          0          27

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 58  

Rows     Row Source Operation
-------  ---------------------------------------------------
      9  COUNT STOPKEY (cr=5 pr=0 pw=0 time=161 us)
      9   TABLE ACCESS FULL MODEL_REF (cr=5 pr=0 pw=0 time=136 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       6        0.00          0.00
  SQL*Net message from client                     6        0.41          1.09



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.19       0.19          0         32          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0         15          0          27
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.19       0.19          0         47          0          27

Misses in library cache during parse: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       9        0.00          0.00
  SQL*Net message from client                     8        8.54         10.26


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        9      0.00       0.00          0          0          0           0
Execute      9      0.00       0.01          0          0          0           0
Fetch       27      0.02       0.00          0         32          0          19
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       45      0.02       0.02          0         32          0          19

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

    3  user  SQL statements in session.
    9  internal SQL statements in session.
   12  SQL statements in session.
********************************************************************************
Trace file: orcl_ora_3784.trc
Trace file compatibility: 10.01.00
Sort options: default

       1  session in tracefile.
       3  user  SQL statements in trace file.
       9  internal SQL statements in trace file.
      12  SQL statements in trace file.
      10  unique SQL statements in trace file.
     186  lines in trace file.
      10  elapsed seconds in trace file.
 

Tom Kyte
December 20, 2005 - 10:01 am UTC

sqlplus is just a "dumb" command line processor, it's logic is:


loop
get input from user;
parse input <<<===
execute it
if input was query, show it
close cursor
end loop


Oracle parses in response to something saying "parse this", Your 'hibernate' app is parsing SQL over and over and over - just like the dumb little command line program that is called SQLPlus.


Sqlplus is not expecting you to type in the same text over and over - it therefore doesn't have that "optimization"

However, stuff your query into a stored procedure and you'd see and entirely different behaviour (maybe move ALL sql from your java code into stored procedures!)

stuffed the query in stored procedure,,,ERR!

Sejas Patel, December 20, 2005 - 6:56 pm UTC

Hi Tom.

You must have noticed i also posted the same kind of question on other thread </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2654838638693 <code>but it got too confusing (thats what you get for asking questions at 3am). Nevermind.

Yes i stuffed the SQL in stored procedure and executed the procedure from SQLPLUS again (may be this is a mistake), but still no change.

So when you say stuff it in a stored procedure, do you mean in my web application?

If yes then i already have a proof that tracing session, which is running on mod_plsql, is resulting in 0.09:1 parse:execute. compared to 1:1 on JDBC platform.

Dont know if this is the right time/place to ask but;

Can you please give me suggestions on how can I integrate J2EE with Oracle database without using some dumbf*&k software that generates SQL for me.

One way of that is creating an API in java which is a very very, still very time consuming.

Another way is to write some PL/SQL API (which i have but non-interective with java), that will pass the collection of data to java through i dont know what.

Bottom line is, how can i pass "bulk collects", "oracle object type collections", "plsql type table collection", etc. that we normally use in stored procedures TO JAVA.

Don't know enough java (always been a plsql fan) but people who do, dont know how to optimize SQL, so its a bit catch 22.

So it'll be a big help if you can draw your magic explaination card here.

thanks much.

regards,

Tom Kyte
December 20, 2005 - 7:03 pm UTC

... Can you please give me suggestions on how can I integrate J2EE with Oracle
database without using some dumbf*&k software that generates SQL for me. ...


well, many people "code it". I create transactional API's in stored procedures (encapsulation! maximal - I mean MAXIMAL - reuse of software - to an extent you have *never* seen before).

Then other things that are good at doing user interfaces call these.

You would be very interested in jpublisher - creates the classes that map to SQL object types:
</code> http://docs.oracle.com/docs/cd/B10501_01/java.920/a96658/toc.htm <code>

Comparing apples to apples

Sejas Patel, December 20, 2005 - 8:09 pm UTC

Hi Tom,

Thanks for the link, will be reading right after i give you an update.

Had a discussion with few people in J2EE team and the point they seem to make is, a SQL is completely bound from JDBC and only do a soft parse.

Yes! this is true when sql is bound.

But while tracing a JDBC session, 6888 statements were executed and 6888 was soft parsed. Is this normal/ok???

I mean sure native-static sql will be a much better option in pl/sql but now we have JDBC and we can't throw it away.

So 1:1 soft-parse:execute : shouldn't bother or big no no??

thanks much for prompt replies.

loving it.

Tom Kyte
December 21, 2005 - 7:14 am UTC

there are three kinds of parsing:

a hard parse, this is very very very bad.
a soft parse, this is only very very bad.
NO PARSE. this is of course the only good parse.

NO, 6,888 soft parses is horrible, bad - for 6,888 executes. Ask the java dudes if they would compile a subroutine before every execution of it (ok, ok, you can call it a "method"). That is what a parse is - a compilation.



hard parse/execute %

Sejas Patel, January 05, 2006 - 12:34 am UTC

Hi Tom,

Continuing from above: my Oracle Performance Manager shows me a char for HARD PARSE/EXECUTE % of about 2.5% on average.

Does that mean the instance is only parsing 2.5% of SQL its executing?

This % figure doesn't quite fit in with the fomula you earlier explained but this might be calculated differently as its percentage and not ratio.

If so, i would say we are doing good in killing unbound SQL everyday.

Phrase: A SQL a day, keeps the doctor away (especially who are hired on $500/hour rate).

Is that 2.5% good or still unclear as you would prefer numbers behind number?

Thanks.

Tom Kyte
January 05, 2006 - 10:26 am UTC

what is a "hard parse/execute %"?


Is this from a GUI in Enterprise manager or a report of some sort?

Execute to Parse %: -0.77

Tariq Zia, June 06, 2006 - 2:44 am UTC

STATSPACK report for

DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
DSMLHO 2678070668 dsmlho 1 8.1.7.0.0 NO DSMLHO

Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 11 06-Jun-06 10:53:46 38
End Snap: 12 06-Jun-06 11:05:01 38
Elapsed: 11.25 (mins)

Cache Sizes
~~~~~~~~~~~
db_block_buffers: 41600 log_buffer: 524288
db_block_size: 8192 shared_pool_size: 125829120

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 3,479.48 3,469.20
Logical reads: 155.14 154.68
Block changes: 11.75 11.72
Physical reads: 1.06 1.06
Physical writes: 1.07 1.06
User calls: 3.12 3.11
Parses: 5.06 5.04
Hard parses: 0.01 0.01
Sorts: 0.44 0.44
Logons: 0.37 0.37
Executes: 5.02 5.00
Transactions: 1.00

% Blocks changed per Read: 7.58 Recursive Call %: 90.74
Rollback per transaction %: 0.59 Rows per Sort: 418.06

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.32 In-memory Sort %: 98.99
Library Hit %: 98.70 Soft Parse %: 99.79
Execute to Parse %: -0.77 Latch Hit %: 99.97
Parse CPU to Parse Elapsd %: 68.18 % Non-Parse CPU: 100.00

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 68.68 68.94
% SQL with executions>1: 79.49 79.79
% Memory for SQL w/exec>1: 87.10 87.86

Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
PX Deq: Execution Msg 1,329 269,775 99.63
log file sync 152 351 .13
control file parallel write 218 260 .10
log file parallel write 291 249 .09
direct path read 322 50 .02
-------------------------------------------------------------
Wait Events for DB: DSMLHO Instance: dsmlho Snaps: 11 -12
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
PX Deq: Execution Msg 1,329 1,308 269,775 2030 2.0
log file sync 152 0 351 23 0.2
control file parallel write 218 0 260 12 0.3
log file parallel write 291 0 249 9 0.4
direct path read 322 0 50 2 0.5
direct path write 40 0 32 8 0.1
db file parallel write 18 0 25 14 0.0
db file sequential read 32 0 11 3 0.0
latch free 37 22 10 3 0.1
control file sequential read 37 0 3 1 0.1
enqueue 23 0 3 1 0.0
refresh controlfile command 6 0 2 3 0.0
file open 3 0 2 7 0.0
SQL*Net more data to client 181 0 1 0 0.3
PX Deq: Join ACK 22 0 0 0 0.0
LGWR wait for redo copy 1 0 0 0 0.0
buffer busy waits 1 0 0 0 0.0
SQL*Net message from client 1,405 0 319,506 2274 2.1
PX Idle Wait 173 152 31,749 1835 0.3
SQL*Net message to client 1,405 0 0 0 2.1
SQL*Net more data from clien 2 0 0 0 0.0
-------------------------------------------------------------
Background Wait Events for DB: DSMLHO Instance: dsmlho Snaps: 11 -12
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
control file parallel write 218 0 260 12 0.3
log file parallel write 291 0 249 9 0.4
db file parallel write 18 0 25 14 0.0
control file sequential read 18 0 2 1 0.0
LGWR wait for redo copy 1 0 0 0 0.0
rdbms ipc message 1,337 654 386,270 2889 2.0
pmon timer 219 219 67,411 3078 0.3
smon timer 2 2 61,440 ###### 0.0
-------------------------------------------------------------

Problem: Sometime user take more time to execute any form (application) or sometimes its work smooth.
Can you please tell me the area where the exect problem.
Execute to Parse %: -0.77 (why it is in -ve)


Tom Kyte
June 06, 2006 - 8:30 am UTC

Parses: 5.06 5.04
Executes: 5.02 5.00


you are PARSING more often than you EXECUTE!! That is why it is negative.

Ask the developers "so, this utility API you have developed to hide the overwhelming complexity of SQL API's - who wrote it, we need to have a talk"


I'm guessing - the developers have written an API to "make things easier", and are parsing SQL statements many times (more than once) before executing it.

confused

ARU, August 09, 2006 - 9:09 pm UTC

Hi Tom,

We have a oltp 10g database which has lots of hits per second and being a Television website it's got lots of selects happening all the time. We are using cursor_sharing=similar. The following query is being executed 5600 times and parsed also 5600 times. The query from v$sqlarea shows no literals but bind variables. Please can you help me understand that even when lots of people are logging in and may be executing the query how and why the query is being executed after being parsed each time.
How can I get the soft parse ratio for the query down.
I thouht using cursor_share = similar will help in it being parsed once and executed many times like you always suggest but guess I'm wrong. Please help!!

WITH ITEMS AS ( SELECT SUBJECT_CONTENT_ID,
OBJECT_CONTENT_ID,
WEIGHT,
ATTRIBUTE,
ROWNUM AS ITEM_ORDER,
LEVEL AS ITEM_LEVEL,
CONNECT_BY_ISLEAF AS ITEM_IS_LEAF,
CONNECT_BY_ISCYCLE AS ITEM_IS_CYCLE,
R.DATE_PUBLISHED,
R.DATE_EXPIRED

FROM

WITH ITEMS AS ( SELECT SUBJECT_CONTENT_ID,
OBJECT_CONTENT_ID,
WEIGHT,
ATTRIBUTE,
ROWNUM AS ITEM_ORDER,
LEVEL AS ITEM_LEVEL,
CONNECT_BY_ISLEAF AS ITEM_IS_LEAF,
CONNECT_BY_ISCYCLE AS ITEM_IS_CYCLE,
R.DATE_PUBLISHED,
R.DATE_EXPIRED FROM RELATIONSHIP R

CONNECT BY NOCYCLE PRIOR
R.OBJECT_CONTENT_ID = R.SUBJECT_CONTENT_ID
AND LEVEL <= :B2 AND
( PRIOR R.DATE_EXPIRED > SYSDATE OR PRIOR R.DATE_EXPIRED IS NULL )
AND EXISTS ( SELECT 1 FROM CONTENT C INNER JOIN HISTORY H ON (H.REVISION= C.CURR ENT_REVISION AND H.CONTENT_ID = C.CONTENT_ID)
WHERE C.CONTENT_ID = R.SUBJECT_CONTENT_ID AND
NOT EXISTS ( SELECT /*+ INDEX (content_type content_type_description)*/ 0

FROM

CONTENT_TYPE T
WHERE DESCRIPTION = 'KEYWORD'
AND T.CONTENT_TYPE_ID = C.CONTENT_TYPE_ID )
AND H.DATE_PUBLISHED IS NOT NULL AND ( C.DATE_EXPIRED > SYSDATE OR C.DATE_EXPIRED IS NULL ) )
START WITH SUBJECT_
CONTENT_ID = :B1
ORDER SIBLINGS BY R.WEIGHT, R.INSERT_DATE_TIME )
SELECT C.CONTENT_ID,
C.TITLE,
TO_CHAR( C.DATE_CREATED, :B7 ) AS CREATED_DATE,
TO_CHAR( C.DATE_CREATED, :B6 ) AS CREATED_TIME,
TO_CHAR( C.DATE_EXPIRED, :B7 ) AS EXPIRES_DATE,
TO_CHAR( C.DATE_EXPIRED, :B6 ) AS EXPIRES_TIME,
T.DESCRIPTION AS CONTENT_TYPE_DESCRIPTION,
C.WEIGHT,
C.SOURCE,
C.AUTHOR,
H.EDITOR AS EDITOR,
TO_CHAR( H.DATE_PUBLISHED, :B7 ) AS PUBLISHED_DATE,
TO_CHAR( H.DATE_PUBLISHED, :B6 ) AS PUBLISHED_TIME,
I.ATTRIBUTE.EXTRACT( :B5 , :B3 ).GETCLOBVAL() AS RELATIONSHIP_ATTRIBUTES,
H.BODY.EXTRACT( :B4 , :B3 ).GETCLOBVAL() AS BODY,
I.ITEM_LEVEL,
ITEM_ORDER,
ROUND( (C.DATE_EXPIRED - SYSDATE) * 24 * 60 * 60 ) AS CTTE,
ROUND( (H.DATE_PUBLISHED - SYSDATE)* 24 * 60* 60 ) AS CTTP,
ROUND( (I.DATE_EXPIRED - SYSDATE) * 24 * 60 * 60 ) AS RTTE,
ROUND( (I.DATE_PUBLISHED - SYSDATE) * 24 * 60 * 60 ) AS RTTP FROM CONTENT C INNER JOIN ITEMS I
ON (C.CONTENT_ID = I.OBJECT_CONTENT_ID) INNER JOIN CONTENT_TYPE T ON (C.CONTENT_TYPE_ID = T.CONTENT_TYPE_ID) INNER JOIN HISTORY H ON (H.REVISION = C.CURRENT_REVISION AND H.CONTENT_ID = C.CONTENT_ID)
UNION ALL SELECT C.CONTENT_ID,
C.TITLE,
TO_CHAR( C.DATE_CREATED, :B7 ) AS CREATED_DATE,
TO_CHAR( C.DATE_CREATED, :B6 ) AS CREATED_TIME,
TO_CHAR( C.DATE_EXPIRED, :B7 ) AS EXPIRES_DATE,
TO_CHAR( C.DATE_EXPIRED, :B6 ) AS EXPIRES_TIME,
T.DESCRIPTION AS CONTENT_TYPE_DESCRIPTION,
C.WEIGHT,
C.SOURCE,
C.AUTHOR,
H.EDITOR AS EDITOR,
TO_CHAR( H.DATE_PUBLISHED, :B7 ) AS PUBLISHED_DATE,
TO_CHAR( H.DATE_PUBLISHED, :B6 ) AS PUBLISHED_TIME,
NULL AS RELATIONSHIP_ATTRIBUTES,
H. BODY.EXTRACT( :B4 , :B3 ).GETCLOBVAL() AS BODY,
0 AS ITEM_LEVEL,
0 AS ITEM_ORDER,
ROUND( (C.DATE_EXPIRED - SYSDATE)* 24* 60 * 60 ) AS CTTE, ROUND( (H.DATE_PUBLISHED - SYSDATE) * 24* 60 *60 )AS CTTP, NULL AS RTTE,
NULL AS RTTP
FROM CONTENT C INNER JOIN CONTENT_TYPE T
ON (C.CONTENT_TYPE_ID = T.CONTENT_TYPE_ID) INNER JOIN HISTORY H
ON (H.REVISION = C.CURRENT_REVISION
AND H.CONTENT_ID = C.CONTENT_ID) WHERE C.CONTENT_ID = :B1
ORDER BY ITEM_ORDER ;


REGARDS,
Aru.

Tom Kyte
August 10, 2006 - 9:04 am UTC

Let me finish your sentence:

you wrote:
We are using cursor_sharing=similar.

it should have been:

We are using cursor_sharing=similar to work around a bug in the developed code whilst the developers do nothing other then fix this P1 bug in their code.



cursor sharing cannot reduce the NUMBER of parse calls (only your coders can do that! really - that is the ONLY way to reduce parse calls, they developers must call PARSE less often!!!)

cursor sharing can reduce the NUMBER OF HARD PARSES, that is all.

There are hard parses (they are very very very bad)
There are soft parses (they are only very very bad)

then there is "the absence of a parse" - or "no parse" - that is the only good parse.

Question regarding your response to Aru

Steven C. Buttgereit, August 11, 2006 - 1:00 pm UTC

In your response to Aru, you talk about the CURSOR_SHARING = SIMILAR being bad as it doesn't avoid soft parsing. That part I get, but in the context of the code fragments Aru provided, there seemed to be bind variable usage (looked like from PL/SQL, in part).

If this was the case, should I take it from your response that even code where the proper use of bind variables was employed, the CURSOR_SHARING=SIMILAR causes otherwise good code to still be soft parsed when it might not otherwise be? (Presuming 9iR2 or 10g, if it matters)

I'm pretty sure there will be soft parsing if the query has any intentional constants mixed with binds as CURSOR_SHARING force/similar will try to make any literals it finds into binds. However, the case where there are only bindable values is now ambiguous in my reading of your response.


Thanks,
Steve

Tom Kyte
August 11, 2006 - 1:13 pm UTC

cursor_sharing similar affects well written code


drop table t;

create table t ( x int, y int );

variable a number
alter session set cursor_sharing = similar;
alter session set sql_trace=true;
select * from t where x = 1;
select * from t where x = 1 and y = :a;

tkprof shows:

select * from t where x = :"SYS_B_0"

select * from t where x = :"SYS_B_0" and y = :a

cursor sharing similar cannot, will not reduce PARSE CALLS.

cursor sharing similar/force can simply sometimes turn a hard parse into a soft parse.

It cannot reduce the over all number of parse calls


RE: Question regarding your response to Aru

Steven C. Buttgereit, August 11, 2006 - 4:59 pm UTC

Understood on the parse calls and CURSOR_SHARING=SIMILAR/FORCE.

What I was unclear on what the exact nature of CURSOR_SHARING=SIMILAR on a query like:

select * from t where x = :a;

So, following your lead, I ran a couple of tests (below) and from the looks of it, there is no direct negative impact on query above; leaving aside, of course, the potential for impact on any recursive calls spawned by the query (or might be for a real world query).

The reason this distinction is important to me is I have a third party app that is also managed (DBA services) by the same third party. With 9.2.0.6, there is a bug with CURSOR_SHARING=SIMILAR/FORCE on our platform, so we're at exact, but the ISV is claiming that the similar setting is an important performance configuration. I'm about to argue that we need to leave it at exact and take a look at the code. As I make that argument I want to be able to answer the 'it doesn't hurt' response as accurrately as I can.

Thanks for the clarification on the prior note,

Steve

(clear shared pool)

Test one (similar)...
variable a number
alter session set cursor_sharing = similar;
alter session set sql_trace = true;
select * from t where x = :a;
select * from t where x = :a and y = 2;
select * from t where x = :a;
select * from t where x = :a and y = 2;
select * from t where x = :a;
select * from t where x = :a and y = 2;
select * from t where x = :a;
select * from t where x = :a and y = 2;
select * from t where x = :a;
select * from t where x = :a and y = 2;
alter session set sql_trace = false;

(clear shared pool)

Test two (exact)...
variable a number
alter session set cursor_sharing = exact;
alter session set sql_trace = true;
select * from t where x = :a;
select * from t where x = :a and y = 2;
select * from t where x = :a;
select * from t where x = :a and y = 2;
select * from t where x = :a;
select * from t where x = :a and y = 2;
select * from t where x = :a;
select * from t where x = :a and y = 2;
select * from t where x = :a;
select * from t where x = :a and y = 2;
alter session set sql_trace = false;

TKPROF Results Test One (similar)...

********************************************************************************

select *
from
t where x = :a


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.01 0.03 1 1 0 0
Execute 5 0.00 0.00 1 1 0 0
Fetch 5 0.00 0.00 0 10 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.01 0.03 2 12 0 0

Misses in library cache during parse: 1
********************************************************************************

select *
from
t where x = :a and y = :"SYS_B_0"


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 1 0 0
Fetch 5 0.00 0.00 0 10 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.00 0.00 0 11 0 0

Misses in library cache during parse: 1
********************************************************************************

TKPROF Results Test One (exact)...

********************************************************************************

select *
from
t where x = :a


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.02 0.03 1 1 0 0
Execute 5 0.00 0.00 1 1 0 0
Fetch 5 0.00 0.00 0 10 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.02 0.03 2 12 0 0

Misses in library cache during parse: 1
********************************************************************************

select *
from
t where x = :a and y = 2


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 1 0 0
Fetch 5 0.00 0.00 0 10 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.00 0.00 0 11 0 0

Misses in library cache during parse: 1
********************************************************************************



Tom Kyte
August 11, 2006 - 6:12 pm UTC

I'm not sure what you showed here - cursor sharing similatr/force on a statement without literals will have no effect, this is true (but it is the rare set of statements that have no literals)

and if the developers are saying similar is important for performance, they have a bug in their code - they are SQL INJECTION culpable (almost certainly). Ask your vendor how they protect against sql injection.

RE: RE: Question regarding your response to Aru

Steven C. Buttgereit, August 18, 2006 - 3:27 pm UTC

I just got confused in the earlier response because of my reading of it. I wasn't sure if you were contradicting the no literal case behavior or not. In the end it was just validating that; I was mistaken in my reading of your initial response to Aru. However, you weren't doing that in fact and I can still believe the world is indeed round :-).

Thanks for the tip regarding the vendor. Between your book (Oracle 9i and 10g Programming Techniques and Solutions), Ask Tom website, and just plain reminding me of the best way to figure this stuff out (testing for myself) has help put this pointy-haired-manager type back on the right path. I can now show, and prove with some of my own test cases, the vendor the error of their ways when they say things like: "...the big return comes in when the similar setting is in place and anonymous pl/sql blocks are used, oracle will recognize such anonymous blocks and assign it's own bind variables to avoid reparsing the statement, for a big savings. [app name] uses quit a bit of anonymous pl/sql blocks, so the similar setting is our standard when it is available."

Thanks again,
Steve

A reader, October 14, 2006 - 11:21 pm UTC

I'm using the code to test on the cursor_sharing on my 9i database. However the result is opposite from what I read:
The hard parses was significantly lower when I set 'Exact' compare to 'similar'/'force'(in statspack load profile)
EXACT given
Parses: 640.00
Hard parses: 8.50
FORCE given
Parses: 326.50
Hard parses: 254.00
SIMILAR given
Parses: 326.50
Hard parses: 254.00


Tom Kyte
October 15, 2006 - 4:03 am UTC

Not possible. Share test case with us.

cursor sharing exact will have as many OR MORE hard parses as similar/force would - given the same set of input SQL under the same conditions.




A reader, October 14, 2006 - 11:24 pm UTC

Sorry, I used this testing code:
exec perfstat.statspack.snap
alter system flush shared_pool;
alter system set cursor_sharing=force; /* ecetera */
exec perfstat.statspack.snap
declare
type rc is ref cursor;
l_cursor rc;
begin
for i in 1 .. 1000
loop
open l_cursor for 'select * from dual where dummy = ''' || i || '''';
close l_cursor;
end loop;
end;
/


Tom Kyte
October 15, 2006 - 4:09 am UTC

well, that doesn't quite make sense here, you did 1000 parses all together, why do the numbers above not reflect that.

I don't know what you did wrong, but you did "something" not correct.  If you ran the above code as it is laid out, you sort of took a statspack snapshot of a flush of the shared pool and a setting of cursor sharing to force - NOT of "relevant" code.

run this test:


ops$tkyte%ORA10GR2> create table t (x int);

Table created.

ops$tkyte%ORA10GR2> create table parse_stats
  2  as
  3  select a.name, b.value, cast( null as number ) run1, cast(null as number) run2
  4    from v$statname a, v$mystat b
  5   where a.statistic# = b.statistic#
  6     and a.name like 'parse%';

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session set cursor_sharing=exact;

Session altered.

ops$tkyte%ORA10GR2> declare
  2      l_cursor sys_refcursor;
  3  begin
  4      for i in 1 .. 1000
  5      loop
  6          open l_cursor for 'select /* nobind exact */ * from t where x = ' || i;
  7          close l_cursor;
  8      end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> merge into parse_stats using ( select a.name, b.value new_val
  2                                   from v$statname a, v$mystat b
  3                                  where a.statistic# = b.statistic#
  4                                    and a.name like 'parse%' ) s
  5  on (parse_stats.name = s.name) when matched then update set run1 = new_val;

5 rows merged.

ops$tkyte%ORA10GR2> alter session set cursor_sharing=force;

Session altered.

ops$tkyte%ORA10GR2> declare
  2      l_cursor sys_refcursor;
  3  begin
  4      for i in 1 .. 1000
  5      loop
  6          open l_cursor for 'select /* nobind force */ * from t where x = ' || i;
  7          close l_cursor;
  8      end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> merge into parse_stats using ( select a.name, b.value new_val
  2                                   from v$statname a, v$mystat b
  3                                  where a.statistic# = b.statistic#
  4                                    and a.name like 'parse%' ) s
  5  on (parse_stats.name = s.name) when matched then update set run2 = new_val;

5 rows merged.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select name, run1, run2,
  2         decode( run1, 0, null,
  3                 to_char(run2/run1*100,'9,999.00') || ' %' ) run2_pct_of_run1
  4    from (
  5  select name, run1-value run1, run2-run1 run2
  6    from parse_stats
  7         )
  8  /

NAME                                 RUN1       RUN2 RUN2_PCT_OF_RU
------------------------------ ---------- ---------- --------------
parse time cpu                         88          5      5.68 %
parse time elapsed                    100          4      4.00 %
parse count (total)                  1022       1012     99.02 %
parse count (hard)                   1003          2       .20 %
parse count (failures)                  0          0

 

A reader, October 15, 2006 - 11:15 am UTC

I just tested on a 10g database using your recent test case. I just edit your code slightly to add on the stats for value SIMILAR and here is the result from the table merge_stats:
10:54:46 SQL> select * from parse_stats;

NAME                                VALUE       RUN1       RUN2       RUN3
------------------------------ ---------- ---------- ---------- ----------
parse time cpu                        835       1458       1481       1496
parse time elapsed                    880       1508       1522       1539
parse count (total)                  3627       4647       5656       6665
parse count (hard)                   2137       3141       3145       3147
parse count (failures)                  0          0          0          0
Note: run1 (EXACT) run2 (FORCE) run3 (SIMILAR) 

Tom Kyte
October 15, 2006 - 1:06 pm UTC

ummm come on - please expose the full kimono here.

you modified my script
you show us output
without showing the inputs

SHOW US THE MONEY, the entire thing - so we can point out maybe where you went wrong!!!


do you see that none of your numbers - NONE of them - make any sense. Not with the test script as supplied!!! no sense whatsoever.




A reader, October 15, 2006 - 12:47 pm UTC

Thank you for your reply. The test case I did before is on a simple 9.2.0.7 database. I just wanted to verify different settings for the cursor_sharing. The resulting numbers are per second from load profile. For example, here is the load profile for EXACT:
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 248,240.00 496,480.00
Logical reads: 1,098.00 2,196.00
Block changes: 383.50 767.00
Physical reads: 9.50 19.00
Physical writes: 32.00 64.00
User calls: 2.00 4.00
Parses: 640.00 1,280.00
Hard parses: 8.50 17.00
Sorts: 127.50 255.00
Logons: 0.00 0.00
Executes: 721.00 1,442.00
Transactions: 0.50


Tom Kyte
October 15, 2006 - 1:13 pm UTC

that is meaningless information - nothing useful can be gotten from that in this particular example.

(other than it appears you might be on a "real" machine? doing some work? I'd be really mad at you playing with this stuff on a real machine with real work..)

A reader, October 15, 2006 - 3:51 pm UTC

the (9.2.0.7) database is a test one. No login except my connection. Here is my running:
alter system flush shared_pool;
alter system set cursor_sharing=exact;
exec perfstat.statspack.snap
declare
type rc is ref cursor;
l_cursor rc;
begin
for i in 1 .. 1000
loop
open l_cursor for 'select * from dual where dummy = ''' || i || '''';
close l_cursor;
end loop;
end;
/
exec perfstat.statspack.snap
alter system flush shared_pool;
alter system set cursor_sharing=force;
exec perfstat.statspack.snap
declare
type rc is ref cursor;
l_cursor rc;
begin
for i in 1 .. 1000
loop
open l_cursor for 'select * from dual where dummy = ''' || i || '''';
close l_cursor;
end loop;
end;
/
exec perfstat.statspack.snap
alter system flush shared_pool;
alter system set cursor_sharing=similar;
exec perfstat.statspack.snap
declare
type rc is ref cursor;
l_cursor rc;
begin
for i in 1 .. 1000
loop
open l_cursor for 'select * from dual where dummy = ''' || i || '''';
close l_cursor;
end loop;
end;
/
exec perfstat.statspack.snap
====================================
the 10g database is actually in Production mode (please do not be mad). Here is my modified code:
create table t (x int);
create table parse_stats
as
select a.name, b.value, cast( null as number ) run1, cast(null as number) run2, cast(null as number) run3
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name like 'parse%';

alter session set cursor_sharing=exact;
declare
l_cursor sys_refcursor;
begin
for i in 1 .. 1000
loop
open l_cursor for 'select /* nobind exact */ * from t where x = ' || i;
close l_cursor;
end loop;
end;
/
merge into parse_stats using ( select a.name, b.value new_val
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name like 'parse%' ) s
on (parse_stats.name = s.name) when matched then update set run1 = new_val;

alter session set cursor_sharing=force;
declare
l_cursor sys_refcursor;
begin
for i in 1 .. 1000
loop
open l_cursor for 'select /* nobind force */ * from t where x = '|| i;
close l_cursor;
end loop;
end;
/
merge into parse_stats using ( select a.name, b.value new_val
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name like 'parse%' ) s
on (parse_stats.name = s.name) when matched then update set run2 = new_val;

alter session set cursor_sharing=similar;
declare
l_cursor sys_refcursor;
begin
for i in 1 .. 1000
loop
open l_cursor for 'select /* nobind similar */ * from t where x = '|| i;
close l_cursor;
end loop;
end;
/
merge into parse_stats using ( select a.name, b.value new_val
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name like 'parse%' ) s
on (parse_stats.name = s.name) when matched then update set run3 = new_val;

/*
select name, run1, run2, run3, decode( run1, 0, null, to_char(run2/run1*100,'9,999.00') || ' %' ) run2_pct_of_run1
from (select name, run1-value run1, run2-run1 run2 from parse_stats);
*/
-- Printout the result.....
select * from parse_stats;


Tom Kyte
October 16, 2006 - 7:42 am UTC

I would be mad if I were your DBA!!!!!! You are totally trashing my shared pool, stop right now, don't do that - you are killing the system you are looking at.


when you find yourself a test system (goto otn.oracle.com/xe and download the free version of oracle, NOW)

and you just use my script "as is", with the merge (I don't know what you are hoping to ACCOMPLISH WITH STATSPACK HERE), and you cut and paste straight out of sqlplus like I do...

then I'll gladly take a look.

I will not participate in your killing of a production machine - this is what really sets up the barriers between DBAs and Developers.  If I caught you doing this on a live system - as a developer - I'd be really @%#%#'d at you. 


Unless and until you provide THIS OUTPUT, I'm just not looking anymore

ops$tkyte%ORA10GR2> create table t (x int);

Table created.

ops$tkyte%ORA10GR2> create table parse_stats
  2  as
  3  select a.name, b.value, cast( null as number ) run1, cast(null as number) run2,
  4  cast(null as number) run3
  5    from v$statname a, v$mystat b
  6   where a.statistic# = b.statistic#
  7     and a.name like 'parse%';

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session set cursor_sharing=exact;

Session altered.

ops$tkyte%ORA10GR2> declare
  2      l_cursor sys_refcursor;
  3  begin
  4      for i in 1 .. 1000
  5      loop
  6          open l_cursor for 'select /* nobind exact */ * from t where x = ' || i;
  7          close l_cursor;
  8      end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> merge into parse_stats using ( select a.name, b.value new_val
  2                                   from v$statname a, v$mystat b
  3                                  where a.statistic# = b.statistic#
  4                                    and a.name like 'parse%' ) s
  5  on (parse_stats.name = s.name) when matched then update set run1 = new_val;

5 rows merged.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session set cursor_sharing=force;

Session altered.

ops$tkyte%ORA10GR2> declare
  2      l_cursor sys_refcursor;
  3  begin
  4      for i in 1 .. 1000
  5      loop
  6          open l_cursor for 'select /* nobind force */ * from t where x = '|| i;
  7          close l_cursor;
  8      end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> merge into parse_stats using ( select a.name, b.value new_val
  2                                   from v$statname a, v$mystat b
  3                                  where a.statistic# = b.statistic#
  4                                    and a.name like 'parse%' ) s
  5  on (parse_stats.name = s.name) when matched then update set run2 = new_val;

5 rows merged.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session set cursor_sharing=similar;

Session altered.

ops$tkyte%ORA10GR2> declare
  2      l_cursor sys_refcursor;
  3  begin
  4      for i in 1 .. 1000
  5      loop
  6          open l_cursor for 'select /* nobind similar */ * from t where x = '|| i;
  7          close l_cursor;
  8      end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> merge into parse_stats using ( select a.name, b.value new_val
  2                                   from v$statname a, v$mystat b
  3                                  where a.statistic# = b.statistic#
  4                                    and a.name like 'parse%' ) s
  5  on (parse_stats.name = s.name) when matched then update set run3 = new_val;

5 rows merged.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select name, run1, run2, run3
  2    from (select name, run1-value run1, run2-run1 run2, run3-run2 run3  from parse_stats);

NAME                                 RUN1       RUN2       RUN3
------------------------------ ---------- ---------- ----------
parse time cpu                         84          2         51
parse time elapsed                     87          3         61
parse count (total)                  1022       1012       1012
parse count (hard)                   1004          2       1001
parse count (failures)                  0          0          0
 

(and if you gather stats on T before doing this, you would discover:


ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );

.....

ops$tkyte%ORA10GR2> select name, run1, run2, run3
  2    from (select name, run1-value run1, run2-run1 run2, run3-run2 run3  from parse_stats);

NAME                                 RUN1       RUN2       RUN3
------------------------------ ---------- ---------- ----------
parse time cpu                         77          4          2
parse time elapsed                     81          4          8
parse count (total)                  1021       1012       1012
parse count (hard)                   1003          2          2
parse count (failures)                  0          0          0
 

verification of test scenarios

A reader, November 05, 2006 - 11:37 am UTC

Hi Tom,
I had to wait for the creation of a (10g) database.
Now I applied your test scenarios on a 9i and a 10g database and followings are the results
* for 10g database:
-> if I log in as testdba (a dba account) then I will have exactly the results as yours:
----> without dbms_stats.gather_table_stats (user, 'T')
Name base Force Exact Similar
------------------------- ---------- ---------- ---------- ----------
parse time cpu 209 26 579 614
parse time elapsed 222 29 589 620
parse count (total) 795 1025 1031 1030
parse count (hard) 158 7 1010 1010
parse count (failures) 0 1 1 1
----> with dbms_stats.gather_table_stats (user, 'T')
Name base Force Exact Similar
------------------------- ---------- ---------- ---------- ----------
parse time cpu 306 30 547 23
parse time elapsed 308 28 556 23
parse count (total) 1070 1024 1031 1011
parse count (hard) 219 7 1010 5
parse count (failures) 0 1 1 1
-> if I log in as sys then I will have the quite different results. With or Without dbms_stats.gather_table_stats, I always end up with:
Name base Force Exact Similar
-------------------- ---------- ---------- ---------- ----------
parse time cpu 280 569 599 616
parse time elapsed 276 567 616 605
parse count (total) 856 1034 1018 1017
parse count (hard) 212 1010 1010 1010
parse count (failures) 0 1 1 1
* for 9i database:
-> I logged in as testdba and it does not matter if i ran dbms_stats.gather_table_stats, I always end up with the 'good' result:
Name base Force Exact Similar
------------------------- ---------- ---------- ---------- ----------
parse time cpu 148 19 150 29
parse time elapsed 153 22 144 25
parse count (total) 705 1023 1008 1007
parse count (hard) 139 4 1003 4
parse count (failures) 0 0 0 0

Can you please give me some explanation on the above results:
1. in 10g, why the difference between a dba account and the sys account?
2. in 9i, why dbms_stats does not take effect?
Thank you for your time
PS. I must change the merge statements to cope with the syntax of 9i (...when matched then update set run1 = new_val when not matched then insert (run1) values (DEFAULT);). The same statements were used at both databases (9i and 10g)





Tom Kyte
November 05, 2006 - 5:19 pm UTC

DO NOT USE SYS FOR ANYTHING, sys is magic, sys is special. stop it.

don't even go there, not even relevant since IN REAL LIFE, you won't be doing things as SYS.

Had I know you were testing with sys, we could have ended all of this discussion a long time ago.

Thank you for your time.

A reader, November 21, 2006 - 11:22 am UTC

Thank you for your time. I'm very appreciated your replies.

SESSION_CACHE_CURSOR

Tony, June 09, 2007 - 10:36 am UTC

Hi, Tom

Our applicatoin is having the same problems.

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 100.02 Soft Parse %: 100.00
Execute to Parse %: 0.02 Latch Hit %: 88.49
Parse CPU to Parse Elapsd %: 92.63 % Non-Parse CPU: 82.28

We believe that the low ratof of Execute to Parse is due to the ineffecient sharing and compiling of SQL statement of the application. However, it's a bit difficult for us to change the application since it is not owned by us.

After searching a bit, we think the oracle (Oracle 9.0)initial parameter SESSION_CACHE_CURSOR & OPEN_CURSOR seems to be an out-of-box solution to this problem.

What's your opinion?
Thanks
Tom Kyte
June 09, 2007 - 10:51 am UTC

... We believe that the low ratof of Execute to Parse is due to the ineffecient sharing and compiling of SQL ....


the sharing is very efficient - soft parse % shows they are doing that right. However, they are definitely parsing/execute/close - over and over.

session cached cursors will help alleviate some of the latching (some of) incurred by the soft parse. It will NOT affect the execute to parse % number at all, you would be looking for decreased parse cpu and decreased latching.

execute to parse %

David, July 15, 2007 - 2:01 am UTC

you say, the sharing is very efficient - soft parse % shows they are doing that right. However, they are definitely parsing/execute/close - over and over.

How do we increase execute to parse ratio? does low value (5% as our database statspack shows) mean it is very bad and needs to be tuned? soft parse% is around 95%...

Thanks
Tom Kyte
July 17, 2007 - 10:49 am UTC

How do we increase execute to parse ratio?


by

a) parsing less
b) executing more


period.

Eval a variable

Guillermo, November 14, 2008 - 1:23 pm UTC

Tom, how can i to evaluate a variable with execute immediate or another statement without using for example:
I want to write in the dbs_output 100
declare
lnu_num number;
begin
lnu_number := 100;
execute immediate 'begin dbms_output.put_line(''Number is->''||:lnu_number) end;' using lnu_number
end;
My question is I don't want to use USING statement, how can i evaluate automatically my var lnu_number.
Tom Kyte
November 18, 2008 - 6:09 pm UTC

why don't you "want to"

because, your choices would be

a) put it into a global package variable and reference that (bad idea)
b) use dbms_sql and bind it (not a bad idea, if you do not know the number of binds at compile time and that is "why you don't want to"

parse call> execution

ashish, August 08, 2012 - 5:15 am UTC

Hello Tom,

I have set cursor_sharing=similar, but one of my query which runs after every 5 mins using perl script shows parse call > execution;

how to decrease parse call of particular statement?

Its always choosing different plan.

Below is the query and plan which differ within two hours when i used awrsqrpt.sql report

SELECT DISTINCT B.PRODUCT_OBJ_ID0 AS PROD_ID,B.DEAL_OBJ_ID0 AS DEAL_ID,D.ACCOUNT_OBJ_ID0 AS ACCT_ID,
CASE WHEN A.ADJUSTMENT_TYPE IN (1) THEN PIN.S2DIQ(D.START_T) || ':' || PIN.S2DIQ(D.END_T) || '|' || D.REASON || '|' || D.APPROVED_BY || '|' || A.CAMS_DESCR || '|' || D.COMMENTS || ' : REF:-' || D.POID_ID0
WHEN A.ADJUSTMENT_TYPE IN (3) THEN PIN.S2DIQ(D.START_T) || ':' || PIN.S2DIQ(D.END_T) || '|' || D.REASON || '|' || D.APPROVED_BY || '|' || A.CAMS_DESCR || '|' || D.COMMENTS || ' : REF:-' || D.POID_ID0
when a.ADJUSTMENT_TYPE in (8) then pin.s2diq(d.start_t) || ':' || pin.s2diq(d.end_t) || '|' || a.CAMS_DESCR || '|' || d.COMMENTS || ' : REF:-' || d.POID_ID0
when a.ADJUSTMENT_TYPE in (2,5) then d.REASON || '|' || d.approved_by || '|' || a.CAMS_DESCR || '|' || d.COMMENTS || ' : REF:-' || d.POID_ID0
else d.COMMENTS || ' : REF:-' || d.POID_ID0 end as comments,
b.ELEMENT_ID as element_id ,a.BILLNOW_FLAG as bill_flag,
case when a.SCALABLE_FLAG=0 then a.FIXED_QUANTITY
when a.SCALABLE_FLAG=1 then d.SCALABLE_QUANTITY end as quantity,
d.poid_id0 as trans_id,d.program_name as progname,d.BILL_NOW,
NVL(d.UNIT_CHARGE,0) AS unit_charge,e.BILL_TYPE,e.STATUS,f.current_bal,d.CREATED_T,g.current_bal
FROM
pin.YOU_CAMS_T a,
PIN.YOU_CAMS_DEAL_T B,
PIN.YOU_CAMS_RIGHTS_T C,
PIN.YOU_CAMS_TRANS_T D,
PIN.ACCOUNT_T E,
(SELECT * FROM PIN.ACCOUNT_BALANCES_T WHERE REC_ID = 1000101) F,
(select * from pin.ACCOUNT_BALANCES_T where rec_id = 1000050) g
WHERE d.YOU_CAMS_OBJ_ID0=a.POID_ID0
AND a.POID_ID0=b.OBJ_ID0
AND d.YOU_CAMS_RIGHTS_OBJ_ID0=c.poid_id0
AND d.you_cams_rec_id=b.REC_ID
AND d.ACCOUNT_OBJ_ID0=e.POID_ID0
AND e.poid_id0 = f.obj_id0(+)
AND e.poid_id0 = g.obj_id0(+)
AND d.STATUS=0
AND D.PURCHASE_START_T<pin.D2SIQ(SYSDATE)
ORDER BY d.CREATED_T;





Tom Kyte
August 17, 2012 - 1:14 pm UTC

how to decrease parse call of particular statement?


you have to CHANGE THE CODE to do that. you have to change the code.

the code prepares a statement and every single time your code prepares a statement - we do at least a soft parse.

so, why is your code preparing a statement over and over and over again? would you compile your source code each time you run a subroutine? you would compile it once and execute it over and over again.

do the same thing in your program. if you can (I know nothing about sql in perl)

PArse count is high

Ashit, November 20, 2014 - 1:13 pm UTC

Although we find that parse call is high compare to execute but how can we control that..means how can we force the oracle engine to not parse the statement again and again... and can u give any scenario where java code prepare the stmt(soft parse) only without executing them..
Recently i was looking into 1 application where dual statement parse call and execution count was same what could be the reason for this?