触类旁通:那些关于 TBL$OR$IDX$PART$NUM 的诡异案例和知识

你是否留意过数据库中有一个奇怪的函数 TBL$OR$IDX$PART$NUM ,你是否留意过很多场景下都出现过它的身影?

让我们通过几个案例、几个特性,了解一下 Oracle 的隐藏世界。

故事一:ORA-1008 错误诊断

某日,在客户的运行场景下,特定SQL遭遇 ORA-00604 / ORA-01008, 导致某些报表无法正常运行。

数据库版本为 11.2.0.4.4,操作系统 Linux,执行的错误信息如下:

ORA-604: error occurred at recursive SQL level 1

ORA-1008: not all variables bound

用户仔细检查了 n 次 SQL,并未发现绑定变量赋值问题,但是SQL操作的主要表是复合分区表,启用 10046 事件跟踪 或者 Errorstack,可以通过后台日志找到了可疑的问题SQL和详细信息。

原来出现问题的并不是用户SQL,而是在后台执行的一条递归SQL语句,这个数据库自身的SQL语句调用,因为缺少变量输入,而出现了 ORA-01008 错误:

----- Error Stack Dump ----- ORA-01008: not all variables bound ----- Current SQL Statement for this session (sql_id=7ughmqbx14mfz) ----- SELECT distinct TBL$OR$IDX$PART$NUM("TERRY", 0, 2, 0, "ID") FROM (SELECT "B"."ID" "ID" FROM "G_DAW" "B" WHERE "B"."SALE_END_TIME">=TO_DATE(:B1,'yyyy-mm-dd hh24:mi:ss') AND "B"."RDC_ID"=TO_NUMBER(:B2) AND "B"."RDC_ID"=TO_NUMBER(:B3) AND "B"."SALE_BEGIN_TIME"<=TO_DATE(:B4,'yyyy-mm-dd hh24:mi:ss')) ORDER BY 1

注意:在很多数据库问题的诊断过程中,我们都能发现递归SQL的身影,当递归 SQL 出现在 AWR 报告的 TOP SQL中时,就需要注意,是否触发了BUG。

通过 TBL$OR$IDX$PART$NUM 关键字和 ORA-1008 错误号,在 MOS 上很容易找到这个问题的相关BUG:

Bug 17258090 Recursive SQL fails with ORA-1008

这个BUG的描述大致是:

对于复合分区表的某些分区剪裁,可能会发生这种情况。如果子分区级别的子查询剪裁在执行时出现 ORA-604 / ORA-1008,那就可能和这个BUG相关。 其典型特征就是,出错时指向如下SQL: SELECT distinct TBL$OR$IDX$PART$NUM ....

简单来说,就是Oracle在子分区级别执行子查询分区剪裁时,调用的内部递归SQL,因为缺少变量传入而出错,其解决方案是,禁用子查询谓词分区剪裁。

这个特性是由参数控制的,在会话级别禁用:

alter session set "_subquery_pruning_enabled"=false;

或者修改初始化参数,在全局禁用之:

alter system set “_subquery_pruning_enabled”=false;

这个特性是在 Oracle 10g 引入的,在执行计划中,当出现 PARTITION RANGE SUBQUERY ,我们可以看到在 Pstart 和 Pstop 执行了 分区剪裁,就是使用到了这个特性。当禁用这个参数,将会影响这一部分执行计划:

通过 x$ksppi 表,可以找到所有隐含参数,及了解其含义:

SQL> select ksppinm,ksppdesc from x$ksppi where ksppinm='_subquery_pruning_enabled'; KSPPINM -------------------------------------------------------------------------------- KSPPDESC -------------------------------------------------------------------------------- _subquery_pruning_enabled enable the use of subquery predicates to perform pruning

该参数的作用就是:允许使用子查询谓词来执行分区剪裁。和这个参数相关的还有几个参数,通过参数描述就可以知道其作用:

SQL> select ksppinm,ksppdesc from x$ksppi where ksppinm like '_subquery_pruning%'; KSPPINM ----------------------------------- KSPPDESC ------------------------------------------------------ _subquery_pruning_cost_factor subquery pruning cost factor _subquery_pruning_reduction subquery pruning reduction factor _subquery_pruning_enabled enable the use of subquery predicates to perform pruning _subquery_pruning_mv_enabled enable the use of subquery predicates with MVs to perform pruning

这个BUG的主要影响范围是 12.1.0.1 (Base Release) 和 11.2.0.4 ,大家可以酌情参考。

故事二:MERGE 的缓慢之夜

看完了这个小案例之后,我们想起了近期遇到的另外一个问题,在一个客户的分析系统中,遭遇到性能问题,从 32K 的块大小可以看出,这是一个仓库型应用:

在AWR报告中的 TOP SQL 中,我们注意到前两个SQL语句 MERGE 操作,执行时间超长。第一条 SQL 执行了 167,844 秒,对照AWR的采样时间 780 分钟,接近是 4 倍 的 Elappsed Time:

为什么TOP SQL执行如此之久,并且执行时间( 167844/780/60 = 3.59 )远超单CPU周期 ,我们看一下这条SQL就一目了然了,因为其使用了 4 个并行度,并行执行时间被累计:

MERGE /*+ APPEND NOLOGGING PARALLEL ("TRANS_DEPOSITS", 4) */ INTO "TRANS_DEPOSITS" USING (SELECT ....

这个问题,进一步怎么分析呢?虽然我们可以分析执行计划、阻塞、锁定等相关因素,但是第三个SQL引起了我的关注。通过上一个案例的分析,我们已经不陌生了,这是一条递归SQL,执行时间同样超长,存在问题。

展开这条 SQL ,仔细看一下,显然同样是对于分区表的内部操作:

SELECT distinct TBL$OR$IDX$PART$NUM("TRANS_DEPOSITS", 0, 1, 0, "TRAN_DATE") FROM (SELECT "BANS_INCT01"."D2_TRN_DATE" "TRAN_DATE" FROM "ODS"."BANS_INCT01" "BANS_INCT01") ORDER BY 1

这条SQL有什么特殊之处呢?多看一秒钟

如果注意看,你会发现这个 SQL 的子查询 FROM 子句中没有 WHERE 条件。

有了这些基本分析之后,我们判断这可能是另外一个 BUG 了,结合 TBL$OR$IDX$PART$NUM 关键字 和 MERGE 操作,在 MOS 上很快能够匹配到以下 BUG:

Bug 18794814 MERGE statement is slow due to recursive query on TBL$OR$IDX$PART$NUM

这个 BUG 离我们更近了一步,影响范围是 12.1.0.2 (Server Patch Set) 和 11.2.0.4 ,修正版本是:12.2.0.1 或者 12.1.0.2.170718 。

这个 BUG 的触发条件:

如果发现 MERGE SQL 执行缓慢并跟踪/堆栈,发现如下递归SQL: SELECT distinct TBL$OR$IDX$PART$NUM(.... 并且这个SQL没有连接条件,则可能遇到此BUG。

这个问题的临时解决方案和上一个案例相同:

设置 _subquery_pruning_enabled = false ,或者找到适合的补丁应用。

注意:由于在很多场景下遇到问题,初始化配置数据库时,可以考虑将参数 _subquery_pruning_enabled 设置为 False,以规避可能遇到的种种问题。

故事三:TBL$OR$IDX$PART$NUM 的身世之谜

我们已经反复看到了 TBL$OR$IDX$PART$NUM 的身影,那么现在是时候研究一下这是一个什么函数了。

TBL$OR$IDX$PART$NUM 函数是一个内部函数,用于确定特定记录所属的分区。这个函数经常用于各种内部操作,前面我们已经见到了几个案例。 函数调用的基本格式如下: TBL$OR$IDX$PART$NUM(PARTITIONED_TABLE_NAME,0,d#,p#,COLUMN_NAME) .

这个函数没有文档说明,需要通过测试来进行一些验证。如下创建测试分区表:

create table enmotech ( depid number, kpiid varchar2(1), ename varchar2(10), npscr number) partition by range (depid,kpiid) subpartition by range (npscr) subpartition template ( subpartition G1 values less than(60), subpartition G2 values less than(90), subpartition G3 values less than(100)) ( partition "P1" values less than (10,'A'), partition "P2" values less than (10,'B'), partition "P3" values less than (10,'C'), partition "P4" values less than (20,'A'), partition "P5" values less than (20,'B'), partition "P6" values less than (20,'C')); insert into enmotech values(10, 'A','ANG', 95); insert into enmotech values(10, 'B','ING', 85); insert into enmotech values(10, 'C','ION', 75); insert into enmotech values(20, 'A','HUA', 95); insert into enmotech values(20, 'B','JUU', 85); commit;

如果我们想找出 (depid,kpiid)为 (20,'B')的记录所属分区,以下查询即可支持:

select TBL$OR$IDX$PART$NUM("C##EYGLE"."ENMOTECH", 0, 1, 0, 20, 'B') pt from dual; PT ---------- 6

查询一下表中现有记录及分区情况:

SQL> SELECT en.*, TBL$OR$IDX$PART$NUM("C##EYGLE"."ENMOTECH", 0, 1, 0, en."DEPID", en."KPIID") pt FROM ENMOTECH en; DEPID K ENAME NPSCR PT ---------- - ---------- ---------- ---------- 10 A ANG 95 2 10 B ING 85 3 10 C ION 75 4 20 A HUA 95 5 20 B JUU 85 6

根据ROWID转换对象号出来:

SQL> select tbl$or$idx$part$num("C##EYGLE"."ENMOTECH", 0, 4, 0, "ROWID") objn from enmotech ; OBJN ---------- 96047 96048 96049 96050 96051

有了这个对象号,关联 DBA_OBJECTS 或者 USER_OBJECTS ,可以找到对象名称等信息,整个转换链路就可以非常灵活.

注意,以下查询是在 11.2.0.4 版本上的查询输出,我们可以清晰的看到每一条记录所属的对象和分区:

故事四:来自 12.2 的未知 BUG

再请注意,当我们在 12.2 版本上执行这个SQL时,确触发了内部错误,这是一个未知的BUG,使用的是 12.2 多租户版本,使用 common 用户执行测试:

在告警日志中记录的错误如下,在 MOS 上没有匹配的已知BUG,也请谨慎测试:

Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x10EA885B, lnxsni()+2059] [flags: 0x0, count: 1] Errors in file /u01/trace/enmo12c_ora_3508.trc (incident=256928) (PDBNAME=CDB$ROOT): ORA-07445: exception encountered: core dump [lnxsni()+2059] [SIGSEGV] [ADDR:0x0] [PC:0x10EA885B] [SI_KERNEL(general_protection)] []

检查一下跟踪文件,注意标红部分,最后执行这个递归查询之后,进入SQL执行阶段,出现错误,这个基于view$的查询,传入一个绑定变量 ROWID:

我们来检查一下,这个递归SQL的执行作用。

首先这个ROWID 00002F93.0006.0001 是一个历史版本的限制 ROWID格式,需要转换成扩展 ROWID:

SQL> select DBMS_ROWID.ROWID_TO_EXTENDED ('00002F93.0006.0001','SYS','VIEW$',0) from dual; DBMS_ROWID.ROWID_T ------------------ AAAABfAABAAAC+TAAG

这个SQL返回的对象是什么呢?确认一下:

SQL> select obj# from view$ where rowid=DBMS_ROWID.ROWID_TO_EXTENDED ('00002F93.0006.0001','SYS','VIEW$',0); OBJ# ---------- 4440 SQL> select object_name from dba_objects where object_id=4440; OBJECT_NAME -------------------------------------------------------------------------------- _CURRENT_EDITION_OBJ

竟然是 版本对象 惹的祸。

再来检查一下这个SQL查询,其中 enmotech 是一个记录表,dba_objects 是一个复杂视图,显然是在这个视图关联和展开时遇到了和版本对象相关的 BUG,将视图查询结果实体化,可以绕过这个问题:

在这个查询中,类似的结果集可以通过 dbms_rowid.rowid_object 方式获取:

那么除此之外,在哪些场景下还可能用到 TBL$OR$IDX$PART$NUM 这个函数呢?

故事五:12c 的延迟索引维护特性

在 Oracle 12c 中,有一个新特性被称为:延迟全局索引维护 - Delayed Global Index Maintenance 。

大家知道,在分区表中,如果创建全局索引,当我们对分区进行删除、截断等操作时,全局索引会失效,除非增加关键字 UPDATE GLOBAL INDEXES 同时维护索引,但是增加这个关键字又可能导致分区维护过慢。

在 12c 中,增加了一个特性,延迟全局索引维护,可以将索引维护工作异步延迟进行,在数据库中是通过 SYS.PMO_DEFERRED_GIDX_MAINT_JOB 任务,在每天02:00运行。

那么问题来了,延迟维护的全局索引,在查询中还会生效吗?

基于以上测试表创建一个全局索引:

SQL> create index enmotech_idx1 on enmotech (depid) global ; Index created.

索引在查询中生效:

删除一个分区,再来检查索引状态,在 orphaned_entries 字段已经记录了索引出现孤儿条目:

SQL> alter table enmotech drop partition p2 update indexes; Table altered. SQL> COL INDEX_NAME FOR A20 SQL> select index_name, status,orphaned_entries from dba_indexes where index_name='ENMOTECH_IDX1'; INDEX_NAME STATUS ORP -------------------- -------- --- ENMOTECH_IDX1 VALID YES

注意此时的执行计划,仍然使用了索引,但是多了一个谓词过滤条件,是通过 TBL$OR$IDX$PART$NUM 函数对 ROWID 进行判断过滤,以保证索引中的孤儿条目不会被执行,当然有了这个步骤,我们看到递归调用的数量大大增加了:

我们可以检查一下 PMO_DEFERRED_GIDX_MAINT_JOB 的工作情况,其执行是调用了dbms_part的索引维护包:

SQL> exec print_table('select JOB_NAME,LAST_START_DATE,RUN_COUNT from dba_scheduler_jobs where job_name=''PMO_DEFERRED_GIDX_MAINT_JOB'''); JOB_NAME : PMO_DEFERRED_GIDX_MAINT_JOB LAST_START_DATE : 11-JUL-18 02.00.00.356202 AM UTC RUN_COUNT : 73 ----------------- SQL> select program_action from dba_SCHEDULER_PROGRAMS where program_name='PMO_DEFERRED_GIDX_MAINT' / PROGRAM_ACTION ------------------------------------------------------------ dbms_part.cleanup_gidx_internal( noop_okay_in => 1);

现在手工调用,消除索引的孤儿状态,查询即刻恢复到正常的状态:

SQL> exec dbms_part.cleanup_gidx_internal(noop_okay_in => 1); PL/SQL procedure successfully completed. SQL> select index_name, status,orphaned_entries from dba_indexes where index_name='ENMOTECH_IDX1'; INDEX_NAME STATUS ORP -------------------- -------- --- ENMOTECH_IDX1 VALID NO

在 Oracle 故障处理和学习的过程中,归纳汇总,聚点滴于一体,就能逐步让我们的知识成为体系,四通八达,所以,走多远,钻多深,取决于我们自己

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2018-07-19

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

一个SQL性能问题的优化探索(二)(r11笔记第38天)

继续前几天的一个案例一个SQL性能问题的优化探索(一)(r11笔记第33天) 如下的SQL语句存在索引字段CARD_NO,但是执行的时候却走了全表扫描,因为这是...

3568
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第六篇(r2笔记91天)

生产环境中有大量的sql语句在运行,尽管有awr,ash做数据的收集统计,但是dba的调优工作大多数情况都是在问题已经发生后做排查的,有些sql语句可能执行的时...

2784
来自专栏杨建荣的学习笔记

海量数据迁移之外部表并行抽取(99天)

在10g开始的新特性中,外部表是一个不容忽视的好工具。对于大型项目中海量数据使用sqlloader是一种全新的方式,不过很明显,sqlloader的可扩展性更强...

3695
来自专栏杨建荣的学习笔记

深度解析dba_segments和sys.seg$中的细节差异(上) (r5笔记第27天)

今天在查看系统空间使用情况的时候,发现一个细节的问题,自己死磕了一把,还是发现了不少有价值的东西。 事情的起因是我在使用脚本在某个环境中查看每个用户所占有的空间...

3548
来自专栏乐沙弥的世界

Oracle 数据字典和数据字典视图

是元数据的集合,从逻辑上和物理上描述了数据库及内容,存储于SYSTEM与SYSAUX表空间内的若干段。

793
来自专栏数据库新发现

在Mac上通过Docker部署Oracle Database 12.2版本

Oracle 已经宣布支持了Docker部署,这也让我们在Mac上部署 Oracle 数据库有了多一个选择,这是我的第一个Docker应用,非常简便快速的就完...

2756
来自专栏乐沙弥的世界

高水位线和全表扫描

   高水位线好比水库中储水的水位线,用于描述数据库中段的扩展方式。高水位线对全表扫描方式有着至关重要的影响。当使用delete 操作 表记录时,高水位线并不...

802
来自专栏杨建荣的学习笔记

执行计划的偏差导致的性能问题(r3笔记第12天)

在生产环境中有一条sql语句,查看执行计划来看,效果还是可以接受的。 sql语句类似下面的样子,可以看到里面还使用了比较纠结的外连接。从执行计划来说,默认是走n...

2734
来自专栏大内老A

WCF版的PetShop之二:模块中的层次划分[提供源代码下载]

上一篇文章主要讨论的是PetShop的模块划分,在这一篇文章中我们来讨论在一个模块中如何进行层次划分。模块划分应该是基于功能的,一个模块可以看成是服务于某项功能...

25110
来自专栏杨建荣的学习笔记

只言片语分析datapump的工作原理(r2第18天)

datapump是从oracle 10g推出的新的数据导入导出工具,可以说是exp/imp的加强版,主要的亮点在于服务端,结合了direct+parallel,...

2383

扫码关注云+社区

领取腾讯云代金券