Stats overflow in Oracle is certainly something you should keep an eye out for, however, sometimes an overflow comes too early (and too unexpectedly).
Recently I’ve been puzzled with the performance difference observed between 11GR1 and 11GR2. As part of the investigation I’ve decided to compare session stats from both 11GR1 and 11GR2 and found that there is a big difference for session cursor cache hits. But the number of session cursor cache hits I’ve been observing in 11GR2 just didn’t made any sense while being absolutely fine in 11GR1. I’ve been running through a relatively low number of iterations and every time it kept coming back with some nonsensical number.
An overflow was certainly a possibility, though in 2010, it’s not like someone should be counting every bit especially if they didn’t in 11GR1! Anyway it was worth a shot so I’ve decided to check it out…
SQL> select statistic# from v$statname where name = 'session cursor cache hits';
STATISTIC#
----------
498
SQL> create table z_t as select 1 n from dual;
Table created
SQL> set serveroutput on
SQL> declare
2 l_n1 number:=0;
3 l_n2 number:=0;
4 begin
5 loop
6 for cur in (select n from z_t)
7 loop
8 null;
9 end loop;
10
11 select value into l_n2 from v$mystat where statistic#=498;
12
13 exit when l_n2 < l_n1;
14
15 l_n1 := l_n2;
16 end loop;
17
18 dbms_output.put_line(l_n1);
19 dbms_output.put_line(l_n2);
20 end;
21 /
65535
1
PL/SQL procedure successfully completed
Oh well… apparently session cursor cache hits is now backed up by 16-bit unsigned integer… uncool.
I don’t know whether any other stat experienced such a “downgrade” but for my particular case having it as 16-bit unsigned integer produced quite a bizarre results on the newest version of Oracle database where the previous one was behaving just fine.
Tests were done on Oracle 11.2.0.1 under Linux x64.
No comments