Cursor 触发了OPTIMIZER_MISMATCH=Y的Bug?

近日,一个数据服务器CPU基本每日触发报警,Idle已经接近于0,并且存在一定的周期性规律,CPU Idle如下图所示:

20200105-11

定位到具体进程后,发现CPU TIME主要被两个SQL因为创建合适的索引导致大量全表扫描耗尽。经过简单优化后,SQL效率提升,但却发现了一个奇怪的问题:

优化前:

20200105-12

优化后:

20200105-13

使得游标立即失效后,此SQL会立即进行硬解析,但是却硬解析了2个Child Cursor:

20200105-14

20200105-15可以看到,两个Cursor确认是因为客户端NLS_SORT与NLS_COMP参数不一致,导致了Cursor 2无法利用上索引,从而选择了全表扫描的执行计划。但是从V$SQL_SHARED_CURSOR中发现了异常现象,LANGUAGE_MISMATCH很好理解(因为NLS_SORT与NLS_COMP参数不一),但是OPTIMIZER_MISMATCH为何也是’Y’?

20200105-16

利用alter system set events ‘trace[rdbms.SQL_Optimizer.*][sql_id]’ ;查看Trace文件与查询v$sql_optimizer_env确认游标优化器参数时,并没有发现异常现象:

20200105-17

20200105-18如果优化器参数不一,那么cursor执行计划中的Outline Data CBO参数部分会有所区别,但是上图中两个Cursor 执行计划中显示的CBO参数是一致的。虽然Oracle Support中并未发现相关Bug记录,但是猜测很有可能是触发了Bug导致。

Oracle 12c中分页查询增强-TOP N SQL

继之前文章中提到的分页查询SQL优化思路提到的分页框架:

SELECT /*+ gather_plan_statistics */ *
  FROM (
   SELECT *
      FROM (
    SELECT rownum AS rn, r.owner, r.object_id
   FROM (
    SELECT /*+ index(t t_idx01) --分页语句 */ owner, object_id
   FROM t
  ORDER BY object_id) r
 )
 WHERE rownum <= 10)
WHERE rn >= 1;

我们前文中提到过,需要在排序列中创建索引,借助索引有序特性来避免排序并且利用正确的分页框架实现COUNT STOPKEY特性,在Oracle 12c版本之后,Oracle提供了新的分页TOP N功能,来实现上述分页需求并且极大的减少了代码复杂程度。

参考Oracle 官方文档中的的描述:

20191208

我们可以利用FETCH ROWS ONLY来实现文章分页TOP-N需求代码:

20191208-2

继续阅读

Oracle12C TABLE ACCESS BY INDEX ROWID BATCHED引发的疑问

今天,公司同事问了我一个问题,Oracle 12c在执行计划中出现了TABLE ACCESS BY INDEX ROWID BATCHED“批量回表”特性,是否等同于我们消除了Nested Loop时被驱动表扫描多少次就需要回表多少次的现状?

答案是否定的,我们先看一下如下测试实验:

我们在12c环境下执行如下SQL:

select /*+ gather_plan_statistics use_hash(a,t2) */
a.owner,a.object_name,a.object_id from t a,t2 where a.owner=t2.username
and a.owner='SYSTEM'

因12c中TABLE ACCESS BY INDEX ROWID BATCHED特性受隐含参数控制(12C 默认为TRUE),我们分别将该参数改为FLASE,与TRUE:

(1)关闭TABLE ACCESS BY INDEX ROWID BATCHED特性,同ora11g版本:

2019203-2

继续阅读

分页语句SQL优化思路总结-3(多表JOIN)

前两篇文章中讲述总结了几种分页语句的优化方法后,碰巧生产系统中有如下SQL需要优化,我们看到这个SQL是多表JOIN的需求,也是我们在生产系统中常见的分页语句场景:

经过将分页框架等价改写正确后,生产系统SQL如下:

SELECT  * FROM ( SELECT * FROM (
 SELECT rownum AS rn, r.owner, r.object_id
 FROM ( SELECT t.owner, t.object_id, t1.account_status
 FROM t, t1 WHERE t.owner = t1.username AND t.owner = 'SYS'
 ORDER BY t.owner) r)
 WHERE rownum <= 10)
WHERE rn >= 1;

多表JOJN-1 20191117

继续阅读

分页语句SQL优化思路总结-2(分页语句中存在谓词条件)

继《分页语句SQL优化思路总结-1(分页语句中无谓词条件)》中我们实验了目标分页SQL存在排序的优化,该分页SQL是不存在谓词过滤条件的“select owner,object_id from t order by object_id”,但是我们99%的情况遇到的应用SQL都是存在谓词过滤条件,例如”select owner,object_id from t where owner=’TRAVELSKYDBA’ order by object_id;”的情况。我们上文提到,可以利用索引有序特性将object_id(排序列)创建索引,但是如果遇到等值查询,我们是否仍需要将排序列创建索引呢?我们继续试验论证,我们来看一下测试数据表T的OWNER列的数据分布:

20191117-分页(4)

我们执行如下目标SQL:

SELECT /*+ gather_plan_statistics */ *
  FROM (
    SELECT *
        FROM (
   SELECT rownum AS rn, r.owner, r.object_id
      FROM (
   SELECT /*+index(t t_idx01) */ owner, object_id
   FROM t WHERE OWNER='TRAVELSKYDBA'
    ORDER BY object_id) r
  )
   WHERE rownum <= 10)
WHERE rn >= 1;

20191117-分页(5)

继续阅读

分页语句SQL优化思路总结-1(分页语句中无谓词条件)

今日,继续总结SQL优化相关原理总结-分页语句。现有如下SQL:

20191117-分页(1)

此SQL的目的是在T表中取出已排序后的10条记录,该SQL中走的是T表的全表扫描执行计划,并且进行排序后才取出10条记录,显然此执行计划并非是最优的,如果表的数据量很大,会造成严重的性能问题(全表扫描+排序),也就是说将表中数据全部扫描处理后再进行排序。但是,该SQL的目的是取出已排序好的10条记录,那么我们是否可以利用INDEX索引有序的特征,扫描到第10条后进行count stopkey停止扫描呢?

因为目标SQL是以OBJECT_ID列作为排序列,我们可以在此列上建立索引,根据索引原理,索引数据本身是有序的,我们可以利用这一特性减少目标SQL对表的访问block

我们将目标SQL先改写为如下形式:

create index t_idx01 on t(object_id,0)

select /*+ gather_plan_statistics */ * from (select /*+ index(t
t_idx01) */ owner,object_id from t order by object_id) where rownum <=10

我们来看一下执行计划:

20191117-分页(2)

我们用HINT强制SQL走了T_IDX01的索引的执行计划,并CBO选择了INDEX_FULL_SCAN索引全扫描,但是我们发现,A-ROWS只扫描了10行数据,处理的总行数等同于我们需要返回的行数,并且执行计划中我们没有看到SORT ORDER BY,在此案例中并没有进行排序,而是利用索引有序的特性,直接扫描INDEX返回即可。

我们将目标SQL改写成如下方式即可:

SELECT /*+ gather_plan_statistics */ *
  FROM (
   SELECT *
      FROM (
    SELECT rownum AS rn, r.owner, r.object_id
   FROM (
    SELECT /*+index(t t_idx01) --分页语句 */ owner, object_id
   FROM t
  ORDER BY object_id) r
 )
 WHERE rownum <= 10)
WHERE rn >= 1;

20191117-分页(3)

犹如预期所愿,目标SQL只扫描处理了10行便COUNT STOPKEY,这是理想的执行计划。

经过以上实验,我们可以得出几个个结论,

(1)分页语句中如果存在排序列(本例中为order by object_id),可以在此列上建立索引,可以避免排序SORT ORDER BY;

(2)例如本文开头的案例,扫描了所有的行数后最后却只要10行数据,那么在错误的分页框架中即使存在索引时,执行计划也会走INDEX FULL SCAN扫描所有的INDEX LEAF BLOCK,所以我们要利用ROWNUM来实现COUNT STOPKEY从而控制扫描的行数,避免扫描多余的行数,尽量只需要处理我们需要返回的行数即可。

 

 

一则系统迁移过后的SQL缓慢案例

11月初,某重要航司的一套生产库进行了机房迁移工作,原数据库实例A(Oracle 11.2.0.3版本)利用DG迁移至新机房,数据库实例A(Oracle 11.2.0.3),数据库版本维持原样,没有进行软件升级,但是,一个应用SQL在新环境下运行时非常缓慢,数据库服务器CPU飙高,Idle接近于0,SQL信息如下:

SELECT /*+ parallel(16) */
 nodeValue
 from (SELECT DISTINCT LPAD(BEGTIME, 4, '0') || '-' ||
 LPAD(ENDTIME, 4, '0') nodeValue
 FROM TRAVELSKYDBA
 WHERE CATEGORY = 'OND'
 UNION
 SELECT DISTINCT DEPARTURE_TIME
 from TRAVELSKYDBA1
 UNION
 SELECT DISTINCT DEPARTURE_TIME
 from TRAVELSKYDBA2
 UNION
 SELECT DISTINCT DEPARTURE_TIME
 from TRAVELSKYDBA3
 UNION
 SELECT DISTINCT DEPARTURE_TIME
 from TRAVELSKYDBA4
 UNION
 SELECT DISTINCT DEPARTURE_TIME
 from TRAVELSKYDBA5)
 WHERE UPPER(nodeValue) <> 'ALL'

既然数据库并没有进行版本升级,CBO优化器方面应该没有不同之处,新老环境的执行计划如下:

老环境:

od-1

继续阅读

DELETE与SELECT计算回表COST的区别

前几日,一位同事发来微信,发送过来一个问题共同探讨,SELECT时采用了全表扫描,忽略了索引,为何在DELETE时选择了此索引?具体SQL如下图所示:

1028-2

其中,A_SCHEMA表记录数量为:176941,B表记录数量为:5224320,通过SELECT的查询计划来看,A,B表进行HASH_JOIN过滤时,A表作为驱动表,未采用索引,采用了全表扫描的执行计划,我们看一下把SELECT改成DELETE,如下图SQL:

1028-3

从上图中的执行计划中看到,DELETE时,A表走了A_SCHEMA_ID索引,问题1,为什么SELECT时不走索引呢?

1028-4

可以看到,该索引的CLUSTERING_FACTOR非常大,基本接近于表的记录数,而表的BLOCK数仅为755,说明在SELECT用到此索引进行回表时,IO COST影响了此路径最终的成本:

SELECT采用此索引时的成本,表连接方式为Nested_loop:

1028-7

继续阅读

如何找出两个结果集中不同的数据?

周末,一个朋友微信我咨询了一个问题,简单描述来说:利用CTAS方式同步一张表时,where rownum 条件写错,现在源表(A)与目标表数据(B)不一致,并且无法直观的看到A,B表的差异,并且A表没有停止数据写入,通过应用逻辑规则原本可以明确得知需要补充的数据,但是因为ROWNUM条件写错,无法确认。此时需要对比两个表中不同的数据,采取补救数据措施。

举例说明:

现有emp(源表),emp2(问题目标表):

full_join_1可以利用SQL表连接的FULL JOIN方式,找寻emp与emp2表的不同记录:

SELECT r1.ename r1_ename,r1.job r1_job,r1.sal r1_sal,r1.num r1_num,
 r2.ename r2_ename,r2.job r2_job,r2.sal r2_sal,r2.num r2_num
FROM 
 (SELECT a.ename, a.job,a.sal,count(*) AS num FROM emp a
 GROUP BY ename,job,sal) r1 full JOIN 
 (SELECT b.ename,b.job,b.sal,count(*) AS num
 FROM emp2 b GROUP BY ename,job,sal) r2
 ON (r1.ename=r2.ename AND r1.job=r2.job AND r1.sal=r2.sal AND r1.num=r2.num);

继续阅读

SQL关联方式-视图合并 (View Merging)

周末时近日有网友朋友向我提问什么是视图合并?多次看到这个名词,但是每次都是扎入到执行计划中去分析了,但是对这个概念和功能完全不清楚,干啥用的?” 对于这个概念,趁赶着这个问题,自己也好好复习一下。

一. 什么是视图合并(View Merging)?

我们通过一个SQL案例来说明,现有如下SQL:

SQL1:
select emp1.empno, emp1.ename, emp1.comm
 from emp1,
 (select emp.ename, emp.deptno
 from emp, dept
 where emp.deptno = dept.deptno) a
 where emp1.ename = a.ename

执行计划如下:

view-merge-2

我们看到,SQ中存在如下的子查询结果集,区别于标量子查询,该子查询是在from 与Where之间的,从执行计划上来看,emp表和dep表进行关联过滤限定后在再EMP1进行最后关联,也就是说如下查询结果集作为一个整体看待,这个比较符合SQL写法的认知逻辑,但是强大的Oracle CBO默认会按照如此的逻辑进行关联么?

select emp.ename, emp.deptno
 from emp, dept
 where emp.deptno = dept.deptno

可以看到,我在SQL中添加了一个 Hint:no_merge,说明此执行计划是人为控制后的产物,我们来看一下去除掉Hint,CBO默认选择什么执行计划?

view-merge-1

我们看到,CBO默认进行的Table Join方案为,EMP与EMP1进行 HASH JOIN后,最后一步进行与DEPT的JOIN工作。这便是Oracle的视图合并。

视图合并是Oracle CBO针对SQL的自动优化措施进行了查询转换,Oracle会保证转换前后的SQL语义是等价的,不再将视图内部(子查询)单独处理与执行,进行视图(子查询)进行拆分,把视图定义或子查询中的表与外部查询的表进行合并(emp与emp1),说了这么多,目的是什么,目的只有1个:让CBO有更多执行路径可以选择,更多路径成本比较,选择出一种最优的执行路径。

视图合并有诸多前提条件,但是宏观上讲,视图或子查询中不能出现UNION,MINUS等集合运算,start with connect by,rownum,cube,rollup等关键字,因为视图合并后,会影响结果集,无法确认合并后语义是否一致。

二. 视图合并的种类 (观点结论引用于著名的 《基于Oracle的SQL优化》- 崔华 著 一书):

(1)简单视图合并:

简单视图合并,是指针对那些不含外连接,以及所带视图的视图定义SQL语句中不包含distinct,group by等聚合行数的目标SQL进行视图合并;进行简单视图合并的SQL,经过CBO的等价改写,一定是小于或等于未做简单视图合并的原SQL成本值,但是如果成本值相同的情况,CBO更倾向于选择进行视图合并的执行路径。

(2)外连接视图合并:

外连接视图合并,是指针对于使用了Outer Join,并且自带视图定义的SQL语句中不含DISTINCT,GROUP BY等聚合函数的目标SQL的视图合并,当目标SQL在和外部查询的表连接时(上例的emp1.ename = a.ename)变为视图与外表之间的连接,有2个前提条件

a.视图(子查询)作为外连接的驱动表;

or

b.视图如果作为被驱动表,但是视图定义SQL语句中只包含一个表;

并且

(3)复杂视图合并:

复杂视图合并,为所带视图定义的SQL语句中含有,外连接、简单视图合并中的禁忌条件:DISTINCT与GROUP BY 的情形。对于复杂视图合并的意义在于,可以将DISTINCT与GROUP BY在视图内部(子查询)中先过滤一部分数据,试图将嫌少中间结果集。但是合并结果并不能一定带来目标SQL的性能提升,CBO选择复杂视图合并的情况:会采取合并改写后的SQL的Cost < 未合并改写后的Cost前提下。