Re: Total Parses

In article <300217f3.0206250337.885275a_at_posting.google.com>, muthy69_at_yahoo.com says...
>
>How do i find the Total Parses in the system ?
>I had given these 2 queries
>1)select name,value from v$sysstat where name like '%parse count%';
>NAME VALUE
>-------------------------------- -----------
>parse count (total) 94850
>parse count (hard) 11857
>
>2) select namespace,gets,gethits,pins,reloads from v$librarycache
>where namespace like '%SQL%';
>SQL>
>NAMESPACE GETS GETHITS
>---------------- ------- --------
>SQL AREA 91008 83262
>
>Aren't GETS in V$LIBRARYCACHE supposed to show the total number of
>parse calls ?
>This is what steve adams says in ixora web site.
>http://www.ixora.com.au/q+a/library.htm#end
>Why is there a difference in the parse count(total) in first query and
>the gets in the second query?
>Thanks in advance

You are seeing effects of new features changing what might have been true in the past. Everything changes (thats what makes it exciting right?)

Anyway -- consider this:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter session set session_cached_cursors=0   2 /
Session altered.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select 'STAT', name,value from v$sysstat where name like '%parse count%'
  2 union all
3 select 'LIB ', namespace,gets from v$librarycache where namespace like '%SQL%'
  4 /

'STA NAME                                VALUE
---- ------------------------------ ----------
STAT parse count (total)                  1860
STAT parse count (hard)                    190
LIB  SQL AREA                             1801

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> /

'STA NAME                                VALUE
---- ------------------------------ ----------
STAT parse count (total)                  1861
STAT parse count (hard)                    190
LIB  SQL AREA                             1802

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> /

'STA NAME                                VALUE
---- ------------------------------ ----------
STAT parse count (total)                  1862
STAT parse count (hard)                    190
LIB  SQL AREA                             1803

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> /

'STA NAME                                VALUE
---- ------------------------------ ----------
STAT parse count (total)                  1863
STAT parse count (hard)                    190
LIB  SQL AREA                             1804

So, see how the gets on the sql area and parse count are going up in lockstep there. One or one (used the union to make that easier to see). Now, let's change that behaviour:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter session set session_cached_cursors=100   2 /

Session altered.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select 'STAT', name,value from v$sysstat where name like '%parse count%'
  2 union all
3 select 'LIB ', namespace,gets from v$librarycache where namespace like '%SQL%'
  4 /

'STA NAME                                VALUE
---- ------------------------------ ----------
STAT parse count (total)                  1865
STAT parse count (hard)                    190
LIB  SQL AREA                             1806

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> /

'STA NAME                                VALUE
---- ------------------------------ ----------
STAT parse count (total)                  1866
STAT parse count (hard)                    190
LIB  SQL AREA                             1806

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> /

'STA NAME                                VALUE
---- ------------------------------ ----------
STAT parse count (total)                  1867
STAT parse count (hard)                    190
LIB  SQL AREA                             1806

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>

Now, parse count goes up -- gets don't (it's already "got"). This is a "softer soft parse" with your session caches the cursor and doesn't bother the server for it. the parse count ticks up -- but it is a softer soft parse -- better then a soft parse but not as good as NO parse at all. Useful for applications that close cursors they will reuse again real soon.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp