Oracle 12c: arraysize會影響結果集麼？

SQL*Plus中ArraySize的設置會影響結果集的數量麼？先不要輕易說“NO”，我們來看看Jonathan Lewis最近一篇文章中披露的一個案例。

當確定的查詢在arraysize改變的情況下，結果集的數量出現不同：

SQL> set transaction read only;

Transaction set.

SQL> set arraysize 1

SQL> select ...

...

541 rows selected.

SQL> set arraysize 4

SQL> select ...

...

599 rows selected.

SQL> set arraysize 10

SQL> select ...

...

620 rows selected.

SQL> set arraysize 32

SQL> select ...

...

616 rows selected.

從查詢結果看，隨著arraysize的不同，結果集的數量忽多忽少，這顯然不科學，一定是BUG導致的。

在執行計劃中，可以看到12c的主要的特殊之處在於：rowset 的出現。這是Oracle 12c中引入的一個新特性。

當然，對於每一個新特性，Oracle都會提供隱含參數去控製，我們可以通過設置 _rowsets_enable=false 去關閉這一新特性。

參考鏈接：

https://jonathanlewis.wordpress.com/2015/11/09/wrong-results/

Oracle Support很快響應這一問題並給出進一步的WorkAround，可以通過設置Event 10055的特定級別修複該問題：

event = "10055 trace name context forever, level 2097152"

這個Bug的補丁也很快被提供出來，大家可以通過MOS找到這個BUG的修複補丁：Bug 22173980 : WRONG RESULTS WHEN "_ROWSETS_ENABLED" = TRUE 。

其影響的範圍是 12.1.0.1 和 12.1.0.2 。參考：Note 2079913.1 。對於CDB環境下，還有另外一個參數 _rowsets_cdb_view_enabled 與這一特性有關，必要時需要關閉這個特性。BUG 17016479 也值得參考。

公開的BUG信息指出，這個問題出現的原因在於：當HASH JOIN在RowSet之間進行多次處理時，在特定情況下，其索引（ridx）定位行有問題，導致返回的結果集出現錯誤。

Internal Problem Description

----------------------------

The failing query involved several nested loops on top of a hash join.The

hash join consumed rowsets from its two inputs and produced one row at a time for its parent.The top nested loops returned the rows to kpofcr, which returned rows=0 when the array fetch size was reached (the default was 15). After all the fetch functions returned, the release functions were called, in order to end the call.

In the release function of the hash join, after the child release was done, it restored the last row it returned to its parent.This involved a call to qesrSetupOneCtxRow to use a row in the rowset where it saved away the input rows.If the last row it returned happened to be the first row in the rowset,and the index (ridx) was set to 0,the release was incorrectly restoring the last row in the rowset instead of the first.This led to wrong results.


Internal Fix Description

------------------------

Modified the hash join release function to use the saved row index when the yet2finishrset_qerhjm flag is set, even if it is 0. That is,if it still has rows to process in the last rowset it received from the child, then the value stored in ridx is the last row that was returned to the parent.  Only in the case that it finished processing the input rowset (and yet2finishrset_qerhjm is false) will a value of 0 for ridx indicate that the last row returned was the last row in the last input rowset.

參考Jonathan和Mos的文檔可以了解這個有趣的Bug。

文章轉自數據和雲公眾號，原文鏈接