ORACLE動態效能檢視統計值溢位

今天同事在查詢V$SQL檢視的時候發現BUFFER_GET列出現了負值。研究了一下，感覺應該是Oracle的整型數溢位了。

查詢Oracle的V$SQL檢視，發現其中一條SQL：SELECT 1 FROM DUAL的BUFFER_GETS變成了負值：

SQL> select to_char(buffer_gets) from v$sql where sql_text = 'SELECT 1 FROM DUAL';

TO_CHAR(BUFFER_GETS)
----------------------------------------
-2069050280

1 row selected.

為什麼會出現負數呢，除了bug外似乎沒有別的解釋，不過即使是bug，Oracle也沒有道理寫一個負數到動態檢視中的。

觀察一下執行次數：

SQL> select executions, to_char(buffer_gets) from v$sql where sql_text = 'SELECT 1 FROM DUAL';

EXECUTIONS TO_CHAR(BUFFER_GETS)
---------- ----------------------------------------
742004326 -2068982372

對於DUAL表的掃描，一般每次執行都是3個邏輯讀。

SQL> select 742004326*3 from dual;

742004326*3
-----------
2226012978

數量級是一樣的，只不過buffer_gets的值是負的。

一般來說出現負值都是由於儲存數值的變數發生溢位造成的。Oracle的number型別是38位，儲存最大數值可以達到10的125次方，不可能在這麼小的數值發生溢位。

不過Oracle的核心程式是用C語言寫的，C的int型變數是32位的：

SQL> select to_char(power(2, 32)) from dual;

TO_CHAR(PO
----------
4294967296

這個上限值是對於unsigned int而言的，對於可以表示正書和負數的int型別而言，上限僅僅是這個值的一半。

SQL> select 742004326 * 3 - power(2, 32) / 2 from dual;

742004326*3-POWER(2,32)/2
-------------------------
78529330

顯然是由於BUFFER_GETS的值已經超出了這個上限值。而且C採用的補碼的演算法，當超過最大值2147483647後，數值開始從-2147483648向-1遞增。

觀察上面兩次對BUFFER_GETS的查詢，該值已經從-2069050280增加到了-2068982372。

Oracle在這裡應該使用UNSIGNED INT型別或者LONG型別來儲存數值，就不會出現這個問題了。