Monthly Archives: 11月 2019

继《分页语句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列的数据分布：

我们执行如下目标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;

已关闭评论

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

2019 年 11 月 21 日 – 10:50

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

此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

我们来看一下执行计划：

我们用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;

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

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

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

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

已关闭评论

ORA-07445案例一则

2019 年 11 月 18 日 – 14:00

上周末两天早上6点左右，公司一套数据库alert日志中均会报出ORA-07445错误，详情如下：

ORA-07445: exception encountered: core dump [kkorminl()+306] [SIGSEGV] [ADDR:0x7FFED335CFF8] [PC:0x957496C] [Address not mapped to object] []

我们知道，类似于ORA-00600与ORA-07445的错误一般不能忽视，多数为数据库内部关键“致命”报错，可能是因为数据库Bug、数据库出现异常问题或操作系统异常多种原因造成的结果，对于此类错误，在公司众多套Oracle中并不罕见，有时会严重影响数据库可用性，有时仅仅抛出一个错误并不会造成任何的影响，针对此类错误我们要具体问题具体分析，严重与否并不能轻易的“经验主义”，针对ORA-00600与ORA-07445的麻痹大意往往会在你以为的风平浪静的潜意识中，给予你最措手不及的考验。

聚焦于本案例，周末两天均在6点左右报出，存在一定的周期性，已经出现两天，照这个节奏不知要持续多少天，为了优化我们的值班生态与避免此错误给大家造成针对ORA-07445麻痹大意的可能性，决定定位根因，解决问题。

数据库alert日志如下：

可以从日志中看到，该错误trace file为JOB进程抛出，契合了此错误周期性的特性，

从具体的Trace文件中可以看到此JOB作业归属于SQL_TUNNING_ADVISOR，

现在我针对此错误大概心里有个数了，随即用目前已得到的信息在Oracle MetLink中找到了解决方法：（Core dump file generation and ORA-07445 [kkorminl()] error (Doc ID 2198790.1)），经过对应数据库版本，Call_Stack信息基本可以确认为Bug导致：

同时，Oracle也给出了解决方案,打Patch/禁用SQL Tunning Advisor/升级至12.1.0.2

基于Oracle的三种针对此错误的解决方案，我采用了禁用SQL Tunning Advisor功能来规避此错误的发生，经过一周的观察后，该报错不再出现。

已关闭评论

一则RAC核心业务数据库异常宕机案例

2019 年 11 月 14 日 – 16:22

13日凌晨，公司一套RAC数据库节点2出现数据库节点异常宕机故障，并且重启数据库CRS失败，最终值班人员重启数据库服务器后解决问题，但是数据库异常原因当时并没有找到，今日有空来好好分析分析。

首先来看一下该节点的数据库alert日志：

我们可以从alert日志中看到，数据库在0点左右出现异常现象：IPC Send timeout detected. Receiver ospid 31239 ，LMS进程出现通信异常，随后LMD进程终止了实例“Instance terminated by LMD0, pid = 31217”

随后，据同事反馈，数据库并没有启动成功，直到服务器重启才恢复，我们来看当时的

已关闭评论

有关自适应游标（ACS）的问题

2019 年 11 月 12 日 – 22:02

上周五，公司一位同事提出一个问题，Oracle每次自适应游标触发时都会进行硬解析吗？我的回答是：不会，下面我做了一个实验来证明Oracle ACS自适应游标的机制，并回答他的问题：

（1）实验环境： Oracle 11.2.0.4

测试表数据分布如下：

Oracle11g引入了ACS（自适应游标）其中一个原因是为了解决Peek Bind带来的如下问题：如果Oracle触发绑定变量窥探的动作的条件之一：只有在SQL硬解析时才会Peeking该条SQL对应的绑定变量的值，我们知道，谓词条件随着具体输入的值的不同，可选择率与对应的目标结果集也会发生不同的变化，又因CBO优化器对于该SQL执行计划的评估是由谓词选择率与结果集行数进行Cost估算的，所以Oracle去Peeking绑定变量的具体值的目的是去力求寻评估该SQL最佳的执行计划，但是，该窥探动作只有在目标SQL硬解析时才会触发，这就会造成当SQL软解析或软软解析时，目标SQL的执行计划会被暂时”固定“下来，会沿用之前硬解析时的执行计划，并不会再去Peek bind，那么软解析或软软解析时，传入的BIND值会持续变化，沿用原硬解析时的执行计划未必是最佳的选择。

测试1：

已关闭评论

Oracle VNCR特性（Valid Node Checking For Registration）修复Poison Attack

2019 年 11 月 10 日 – 12:31

近日，朋友公司安全监管部门扫描漏洞时监测出该公司生产重要数据库存在”TNS Listener Poison Attack”，需要进行紧急修复，打来电话询问是否可以忽略？碰巧，这个问题之前研究过，我们先来说一下什么是”TNS Listener Poison Attack”？

据上述文章描述，该问题会导致数据库服务器被远程注册，此问题并不能简单忽略，询问朋友Oracle数据库版本为11.2.0.4，可以简单的利用Oracle VNCR特性（Valid Node Checking For Registration）进行修复，以下步骤为我之前自己测试的配置流程，验证了VNCR的有效性：

（1）测试目的：

已关闭评论

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

2019 年 11 月 07 日 – 20:09

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优化器方面应该没有不同之处，新老环境的执行计划如下：

老环境：

已关闭评论

核心系统中的ORA-1000案例

2019 年 11 月 05 日 – 13:11

上周五，公司一套重中之重的生产系统应用大量抛出ORA-1000报错，某航司核心应用逻辑调用出现严重异常。登录数据库查看，果然有些许会话open_cursor已达到上限。

数据库中，open_cursor参数设置为了1000，代表着单个数据库进程允许打开的游标数目，上图显示，有许多会话open_cursor已达到999。

为了方便后续的根因定位，没有立即破坏故障现场，随即做了1000的errorstack与数据库的system dump确认一下进程游标open情况与会话此时在干些什么？

(1) alter system set events '1000 trace name errorstack level 3';
(2) oradebug dump systmstate 266;

已关闭评论

TravelskyDBA

Monthly Archives: 11月 2019

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

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

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

ORA-07445案例一则

一则RAC核心业务数据库异常宕机案例

有关自适应游标（ACS）的问题

Oracle VNCR特性（Valid Node Checking For Registration）修复Poison Attack

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

核心系统中的ORA-1000案例

About

Search

Archive

Meta