Skip to Main Content
  • Questions

  • difference between parse count (total) and parse count (hard)

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: May 03, 2001 - 9:13 am UTC

Last updated: December 02, 2002 - 7:20 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,

I appreciate if you help in understanding differences between parse count (total) and parse count (hard). Does it have any impact on parse to execute ratio. What is the best way of calculating Parse to Execute ratio, and what is the optimum value for Parse to Execute Ratio.

Thanks.

Sudhakar.




and Tom said...

A hard parse is the parse of a query for the very first time -- it is not in the shared pool. This is very very expensive. In most systems, over time as the database runs- the hard parse count should go down to ZERO. If not, it indicates that you are not useing bind variables and that is the NUMBER 1 performance killer.

parse_count_total - parse_count_hard = soft parses. soft parses are much better then hard but should be minimized as well. Your parse/execute ratio should be as near zero as you can get it. Hopefully, you can open a cursor (prepared statement) once and reuse it over and over an over. Instead of executing:

prepare statement
bind statement
execute statement
close statement

in your code, you should prepare the statement ONCE per execution of your application and use it over and over.



Rating

  (4 ratings)

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

Comments

need help!

sivababu, May 05, 2001 - 12:30 pm UTC

Hello sir,
How can i post the questions?...
I didn't find anything to post...

id : a_sivababu@hotmail.com

thanks.

with regards,
sivababu

Randy Richardson, April 19, 2002 - 4:57 pm UTC

I would like a query that gets hard parse and soft parse counts for sql. I now have:

SELECT a.sorts, a.executions, a.parse_calls, b.username,
a.sql_text
FROM v$sqlarea a, all_users b
WHERE a.parsing_user_id = b.user_id
AND a.executions > 0
AND b.username NOT IN ('SYS', 'SYSTEM')

I assume parse_calls is both hard and soft. where can I get them appart? I would like to show for each SQL used by "user" the executions, hard_parse, soft_parse, sorts.

I use this report to help look for tuning areas during development.

Thanks.


Tom Kyte
April 19, 2002 - 6:16 pm UTC

Well, you don't really want to look at that at the STATEMENT level do you? You want to look at that in the AGGREGATE.

Consider:

a statement is hard parsed when not found in the shared pool
it is soft parsed when found in there

hence -- the existence in the shared pool means it'll be soft parsed, not hard. It'll be hard parsed once (or once per invalidation), soft parsed otherwise.

It doesn't make sense really to look at the hard parse count at the statement level!

statspack gives you the soft/hard parse ratio, that is what is important.

number of soft parses

A reader, November 27, 2002 - 6:45 am UTC

hi

according to note: 208918.1

soft parse = parse count (total) - parse count (hard) - session cursor cache hits

but my understanding of session cursor cache hits is parse calls doesnt even occur if the cursor is cached in session, is my understanding wrong? Or parse calls always occur no matter what

Tom Kyte
November 27, 2002 - 7:54 am UTC 

with session cached cursors there are now what I will call 4 types of parses:

hard parse (library cache miss, do the entire thing)
soft parse (library cache hit, do less work then hard)
softer soft parse (session cursor cache hit, less work then soft parse)
NO PARSE (well written program that reused cursors -- NO work performed)


A "softer" soft parse still incurrs (avoidable) overhead.  I would say the support note is a little aggresive in its math.  Maybe something like:

soft parse = parse count(total) - parse count(hard) - 0.5 * session cursor cache hits

(0.5 is arbitrary, illustrative)....

Consider -- we'll do 10,000 softer soft parses vs no parse and see that the softer soft parse still lots of extra (avoidable) work:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int primary key ) organization index;
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1 );
1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set session_cached_cursors = 100;
Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2      l_start number;
  3      l_run1  number;
  4      l_run2  number;
  5
  6      l_cnt   number;
  7  begin
  8      insert into run_stats select 'before', stats.* from stats;
  9
 10      l_start := dbms_utility.get_time;
 11      for i in 1 .. 10000
 12      loop
 13          execute immediate 'select count(*) from t' into l_cnt;
 14      end loop;
 15      l_run1 := (dbms_utility.get_time-l_start);
 16      dbms_output.put_line( l_run1 || ' hsecs' );
 17
 18      insert into run_stats select 'after 1', stats.* from stats;
 19      l_start := dbms_utility.get_time;
 20
 21      for i in 1 .. 10000
 22      loop
 23          select count(*) into l_cnt from t;
 24      end loop;
 25      l_run2 := (dbms_utility.get_time-l_start);
 26      dbms_output.put_line( l_run2 || ' hsecs' );
 27      dbms_output.put_line
 28      ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' );
 29
 30      insert into run_stats select 'after 2', stats.* from stats;
 31  end;
 32  /
279 hsecs
206 hsecs
run 1 ran in 135.44% of the time

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2,
  2         ( (c.value-b.value)-(b.value-a.value)) diff
  3    from run_stats a, run_stats b, run_stats c
  4   where a.name = b.name
  5     and b.name = c.name
  6     and a.runid = 'before'
  7     and b.runid = 'after 1'
  8     and c.runid = 'after 2'
  9     and (c.value-a.value) > 0
 10     and (c.value-b.value) <> (b.value-a.value)
 11   order by abs( (c.value-b.value)-(b.value-a.value))
 12  /

NAME                                 RUN1       RUN2       DIFF
------------------------------ ---------- ---------- ----------
...
STAT...session cursor cache hi      10000          3      -9997
ts

STAT...opened cursors cumulati      10005          6      -9999
ve

STAT...parse count (total)          10005          6      -9999
LATCH.shared pool                   10117        108     -10009

34 rows selected.



so that shows parse count does get incremented for each cursor cache hit -- and that there is some latching going on....

It is better then a soft parse for sure - but not as good as NO parse.

how to achive NO PARSE

A reader, December 02, 2002 - 6:05 am UTC

Hi

How can we avoid parses at all (after first parse)? It depends on Applications?

Tom Kyte
December 02, 2002 - 7:20 am UTC

It depends on the coder of the application using best practices, yes.

If they code:


loop
parse insert
bind insert
execute insert
close insert
end loop


they did it wrong they should have coded

parse insert
loop
bind insert
execute insert
end loop
close insert


Or, if they have a routine they know will be called many times, they should code something like:


...
if first_time then
parse insert
end

bind insert
execute insert
....

and just never close it. Best of all -- if you use PLSQL, this happens automagically, no extra code necessary. PLSQL does all of this for you transparently.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.