Thursday Jul 11, 2013

More parses in 12c ?

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]

About

I am Abel Macias email, an Oracle support engineer that specialized in Performance that belongs to Exadata Support.
Disclaimer This blog looks to broadcast my experiences while diagnosing performance problems on Oracle's RDBMS, mostly on Exadata.
The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today