Oracle问题排查记录

本篇开始记录日常工作中遇到的Oracle数据库相关问题和排查过程，隐去了敏感信息，内容整理形式为个人备忘录，一些问题受限于个人能力和时间不会进行全面的解析。

一. 慢查询SQL

慢查询SQL

相关内容：《SQL规范和优化》查看优化案例内容。

慢查询SQL-大表关联

--     1.查询当前在活动的会话获得SQL_ID值
select a.USERNAME,
       a.SQL_ID,
       a.SID,
       a.MACHINE,
       a.PROGRAM,
       a.sql_hash_value,
       a.blocking_session_status
from v$session a
where a.status = 'ACTIVE'
  AND a.SCHEMA# > 0;

--     2.获得此sql_id对应的sql语句 
select sql_id, sql_fulltext from v$sql where sql_id = '9j2c5zhzvsfd7';

--     3.查询此sql_id历史执行信息
select a.INSTANCE_NUMBER,
       a.snap_id,
       a.sql_id,
       a.plan_hash_value,
       b.begin_interval_time
from dba_hist_sqlstat a, dba_hist_snapshot b
where sql_id = '9j2c5zhzvsfd7'
  and a.snap_id = b.snap_id
order by instance_number, snap_id;

--     4.查询变更前后的执行计划
select sql_id,
       plan_hash_value,
       id,
       operation,
       options,
       object_owner,
       object_name,
       depth,
       cost,
       timestamp
from DBA_HIST_SQL_PLAN
where sql_id = 'dxx8pvcttf5qv'
  and plan_hash_value in (1904478120, 2125777269);

--     5.根据执行计划的不同点查找原因-使用coe_xfr_sql_profile.sql可以发现两种执行计划的效率(AVG_ET_SECS)
select *
from dba_objects
where object_name = 'MOD_LOTHISTORY_IDX01'
  and owner = 'MDWMGR'
order by last_ddl_time desc;

慢查询-数据倾斜，且对绑定变量不敏感

（1）问题表象

查询SQL执行超时，耗时几个小时才能返回。

select xxx
from table_a a  
left join table_m m 
on a.xxx_code=m.xxx_code and a.xxx_id=m.xxx_id and a.xxx_type=m.xxx_type and a.xxx_date=m.xxx_date
where m.xxx_date >= :begin_date and m.xxx_date <= :end_date and m.xxx_code =:xxx_code and a.xxx_code=:xxx_code

（2）排查思路

1.生产环境PLSQL执行超时SQL，并查看执行计划。未发现性能问题。SQL为大表驱动大表，两表数据规模都为500W左右。

2.取下超时发生时间段的AWR报告（注意最好间隔1小时的取，时间跨度太大会掩盖掉一些信息）。从报告中未发现明显的资源紧张问题，CPU使用率也不高，未发现硬解析过高等问题。有两处注意点：

• 行锁等待时间耗时比较明显，可能是大事务或者DML操作过于频繁，且SQL效率不高导致。
• 超时的SQL是耗时最久的SQL_ID。

3.从AWR报告未能看出导致这个SQL变慢的原因。测试库相同数据未能复现问题，所以怀疑为生产库实际执行选择的执行计划过于糟糕。

继续查询生产环境下对应SQL的会话信息，是否有异常的等待事件：

SELECT * FROM V$SESSION WHERE SQL_ID = 'gtw99bxf2k87d' or prev_sql_id = 'gtw99bxf2k87d';

发现该SQL发生了Latch争用事件，造成latch: cache buffers chains事件的原因大概率是SQL不够优化，产生了大量的逻辑读（逻辑读指从Buffer Cache中读取数据块，对应的物理读则是从磁盘读入Buffer Cache的过程），每次要访问一个block，就需要获得其对应latch，由于有大量的逻辑读，那么就增加了latch:cache buffers chains争用的机率。 查看AWR报告，发现该SQL确认产生了大量的逻辑读，但根据设想的执行计划分析不会产生如此多的逻辑读。

所以查看下该SQL_ID数据库中真实的执行计划：

select * from table(dbms_xplan.display_cursor('gtw99bxf2k87d'));

发现问题，驱动表选择了tableA，而不是tableM，对于A只能通过code过滤，过滤后仍有300W规模数据量（表的一半数据量），相当于全表扫了一遍，再同过滤出的数千条M进行关联过滤，性能可想而知是很糟糕的。

但是为什么数据库会选择这种执行计划呢？根据数据行和成本估计可以看出，生成该执行计划的那次硬解析判断合约表只会命中一条数据，综合此时的各项信息和排序后选择了该执行计划。

按照经验来说，Oracle的CBO模式应该会根据字段的取值比重调整对应的执行计划。为什么会一直采用此执行计划呢？

补充知识：11g之前对于使用绑定变量窥探的SQL语句，Oracle会根据第一次执行使用的绑定变量值来用于以后的执行，即第一次做硬解析的时候，窥探了变量值，之后的软解析，不会再窥视。11g之后，有了ACS自适应游标的新特性，会根据绑定变量值的情况可以重新生成执行计划，因此这种问题得到了缓解。缓解了绑定变量窥探的副作用，相应地代价是可能会导致生成很多子游标。11g默认绑定变量窥探是开启的，由optim peek user binds隐藏参数控制。

查看生产库该SQL是否对绑定变量敏感，结果为false：

SELECT SQL_ID,SQL_TEXT,IS_BIND_SENSITIVE,IS_BIND_AWARE from v$sql t where t.SQL_ID = 'gtw99bxf2k87d';

查看ACS是否开启：

-- 查看ACS相关配置
select name, value from v$parameter where name in ('optimizer_mode', 'optimizer_features_enable', 'optimizer_capture_sql_plan_baselines', 'cursor_sharing');

-- 需要用SYS
select a.ksppinm name, 
       b.ksppstvl value,
      b.ksppstdf  isdefault
from sys.x$ksppi a, sys.x$ksppcv b
where a.inst_id = userenv('Instance') 
  and b.inst_id = userenv('Instance') 
  and a.indx = b.indx 
  and a.ksppinm in ('_optim_peek_user_binds', '_optimizer_adaptive_cursor_sharing', '_optimizer_extended_cursor_sharing', '_optimizer_extended_cursor_sharing_rel')
order by translate(a.ksppinm, ' _', ' ');

• optimizer_mode：优化器模式，表示选择使用CBO还是RBO
• optimizer_features_enable：使用优化器特性版本，是否支持ACS。
• optimizer_capture_sql_plan_baselines=false：关闭了SQL执行计划baseline管理机制；
• cursor_sharing=EXACT：完全匹配的绑定变量机制；
• _optim_peek_user_binds：是否开启绑定变量机制；

之后的三个隐藏参数与ACS开启与否相关：

• _optimizer_adaptive_cursor_sharing
• optimizer_extended_cursor_sharing
• _optimizer_extended_cursor_sharing_rel

发现都未开启。但开启ACS需要DBA来判断，如果贸然的因为这个问题取修改Oracle配置似乎不太说的过去，即使理应开启以避免其它有数据倾斜情况的表出现同样的问题。但尝试在测试环境造各种情况数据后，都未能复现出生产的以合约信息表为驱动的执行计划，所以无法验证ACS是否有效。

（3）修改方案

建议DBA评估开启ACS功能，从而能根据绑定变量的值来选择最优的执行计划，避免绑定变量窥探问题。

程序修改，在SQL中添加hint注释LEADING(M)，强制以M表为驱动表。

开发团队进行预警和经验分享，避免相同问题排查解决消耗过多资源。

压测环境性能问题排查

（1）问题表象

性能机压测时发现任务耗时相比以往有明显增加，同事排查后确认大致为数据库问题，遂取任务执行一小时的AWR报告进行分析。

（2）排查思路

1. 发现数据库有硬解析过多问题，可能和未使用绑定变量有关。
2. 发现等待事件中Concurrency占比较大，cursor: mutex X 尤为明显，还有 db file sequential read 、cursor: mutex S 、row cache mutex 等。
3. 发现有多个SQL的Version Count过大，查看子游标产生原因时，发现都是bind_equiv_failure和ROLL_INVALID_MISMATCH导致。前者因为绑定变量与实际字段类型不匹配，后者一般和自动统计信息有关。
4. 查看问题SQL的实际字段类型发现为NUMBER，绑定变量则为VARCHAR。
5. 并关联到一个Oracle BUG：Bug 28794230 - 12.2 Cursor Mutex X Due To Sql Not Shared Because Of BIND_EQUIV_FAILURE (Doc ID 28794230.8)
6. 将此问题反馈给同事。

（3）修改方案

• 分析问题SQL，尝试优化分解。
• 修复Oracle BUG，执行补丁或升级版本。

二. 其他

ORA-24347

（1）现象

某生产环境突然发现异常情况，SQL查询一直报错，导致后续流程无法执行。远程查看生产日志后发现报错信息为：ORA-24347: Warning of a NULL column in an aggregate function but no null field 。

有问题的SQL：

select  a.XXX, ...,
        (case when b.xxx is null then 0 else b.xxx end) xxx,
from table_1 a
left join (select min(xxx) xxx, ... 
           from table_2 
           group by ...) c  
on ...
left join table_2 b 
on ...
where a.xxx_1 = :xxx_1 and a.xxx_date >= :begin_date and a.xxx_date <= :end_date

（2）分析

根据异常信息可知问题是聚合函数中Column字段中存在Null值，但该系统所有字段应该都有加非空约束，所以首先排查了表结构和约束是否有构建成功：

select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,DATA_DEFAULT
from user_tab_columns 
where TABLE_NAME = 'table_2'
ORDER BY DATA_LENGTH,TABLE_NAME,COLUMN_NAME,DATA_TYPE;

结果显示字段xxx有非空约束，远程连接生产库，替换参数后执行该SQL，并无问题。查看该表数据也无Null值字段返回。Oracle官网查看相关资料后，并未发现一样的案例，但该问题似乎有过多次BUG出现，所以怀疑有两种可能：

1. 现场有多个库环境，我远程看的是正常数据的库，但实际程序执行SQL是异常的库环境（但这个猜想被现场同事否认）。
2. PLSQL工具查看时隐藏了异常数据，导致我们无法通过查询命中到Null值的数据（生产无其他工具可供使用）。

（3）解决

所以先临时修改SQL，将 min(xxx) 改为 min(NVL(xxx, ' ')) ，挑了一个空闲间隔重启了程序。重新执行相关功能后，发现问题解决。证明异常数据存在，但暂时无法定位到。