专栏首页数据和云Oracle 12c: arraysize会影响结果集么?

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。

本文分享自微信公众号 - 数据和云(OraNews),作者:盖国强

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2015-11-27

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL 8.0.14版本新功能详解

    作者:崔虎龙,云和恩墨-开源架构部-MySQL技术顾问,长期服务于数据中心(金融,游戏,物流)行业,熟悉数据中心运营管理的流程及规范,自动化运维 等方面。擅长M...

    数据和云
  • 详述一则DB2 Error Code 1639和SQL State 08001案例诊断和解决方案

    导读:在 db2inst1/sqllib/security 目录下,检查 db2ckpw 和 db2chpw 文件的权限。

    数据和云
  • 【云端起舞】Oracle云上一键安装数据库补丁集

    编辑手记:为数据库升级打补丁是一项常规的任务,在通常情况下 ,打补丁是一件繁琐的事情,需要考虑的细节比较多。但在云上,可以通过按钮一键式应用 相关补丁集,高效便...

    数据和云
  • 基于图卷积网络的运动结构图像检索(CS CV)

    用于运动结构(SfM)的常规图像检索技术受到有效识别重复模式的局限,无法保证以高精度和高召回率创建足够的匹配对。在本文中,我们提出了一种基于图卷积网络(GCN)...

    gaowanting
  • 一个好用的SAP ABAP工作进程跟踪工具

    As an ABAPer we have SAT, ST05 ( or sometimes ST12 ) for trace in our toolbox, a...

    Jerry Wang
  • Deploying to Amazon EC2 in Mulesoft

    The EC2 plugin allows you to create Amazon machine instances (AMIs) of your exis...

    用户6790598
  • nano:基本操作

    JNingWei
  • Fix Notification Switching Position Issue

    I once faced with a problem. I wrote a piece of code related with notifcation. E...

    技术小黑屋
  • 用数学为爱情保鲜

    16/5/22 数学的力量 爱情数学 心得: 数学的力量是很强大的,它存在于我们的生活中,影响着我们的生活,无处不在。 说得简单一些,数学就是一门研究...

    杨熹
  • From High Ceph Latency to Kernel Patch with eBPF/BCC

    There are a lot of tools for debugging kernel and userspace programs in Linux. M...

    Linux阅码场

扫码关注云+社区

领取腾讯云代金券