X

An Oracle blog about Oracle Cloud

More parses in 12c ?

Abel Macias
Senior Principal Support Engineer
More parses in 12.1 ?

Some customers upgrading from 11.2.0.3 to 12.1 might get alarmed because they see a spike in total number of parse count.
But not to worry, it is not a problem in 12.1. The problem is in 11.2.0.3.

Lets take the following example :

select a.name, b.value 
   from v$statname a, v$mystat b 
  where a.statistic# =  b.statistic# 
    and ( a.name like '%parse count%' 
       or a.name like 'session cursor cache%') 
    and value>0; 

 begin 
 for n in 1..100 loop 
 begin 
   execute immediate 'alter session set optimizer_goal=choose'; 
 exception 
  when others then 
  null; 
 end; 
 end loop; 
 end; 

 select a.name, b.value 
   from v$statname a, v$mystat b 
  where a.statistic# =  b.statistic# 
    and ( a.name like '%parse count%' 
       or a.name like 'session cursor cache%') 
    and value>0; 

I got the output of the queries and put them side by side in each version.

NAME                            11.1 VALUE   11.2 VALUE   12.1 VALUE
-----------------------------   ----------   ----------   ----------
session cursor cache hits                2            3            1
session cursor cache count               3           10           10
parse count (total)                     12           11           12
parse count (hard)                                    1   

PL/SQL procedure successfully completed.

NAME                            11.1 VALUE   11.2 VALUE   12.1 VALUE
-----------------------------   ----------   ----------   ----------
session cursor cache hits                3            3            1
session cursor cache count               4           11           11
parse count (total)                    114           13          114
parse count (hard)                     100          101          100
parse count (failures)                                           100


Notice the number of "parse count (total)" in 11.2 is less than "parse count (hard)" which makes no sense since "parse count (total)" should include Hard , Soft and Failed parses.

This might sound like an extreme case but there are other soft parsing scenarios that also reproduce this oddity.

For customers moving from 11.1 to 11.2.0.3 it might have looked as an improvement that there were less parses in total and then moving from 11.2.0.3 to 12.1 they would probably notice the spike and raise concerns.

So do not be alarmed, 12.1 does not do more parses, nor 11.2.0.3 does less parses, it is just a bug in the instrumentation of the stats, specifically :

Bug 13837105  statistics "parse count (total)" and "session cursor cache hits" miscounted [ID 13837105.8]

Join the discussion

Comments ( 2 )
  • guest Tuesday, August 6, 2013

    Thanks for the metalink note reference! I just noticed this but the opposite really... a lot "less" parsing in 11.2.0.3 vs. 11.1.0.7. From what I can see this also affects queries against v$sql and such...


  • guest Tuesday, June 14, 2016

    You might be running into this situation due to Oracle 12c new feature Adaptive query optimization's underlying function SQL Plan Directives. If cardinality estimates, statistics seems not right, AQO makes runs time adjustments to your SQL to provide better execution plan. This feature sometimes causes high sql parses (soft), which you can verify from AWR reports. Try to turn AQO off using optimizer_adaptive_reporting_only=TRUE

    optimizer_adaptive_features=FALSE

    Once you disable this feature, you can re-verify if parsing issue is resolved. While the underlying cause of bad SQL code should be resolved first, but above parameter change can bring immediate resolution to the parsing issue.

    Also read - How to Approach Issues that Appear Related to Adaptive Features Such as Adaptive Query Optimization ( Doc ID 2068807.1 )

    Thanks,

    Gaurav Batta


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha