一个SQL性能问题的优化探索(二)(r11笔记第38天)-阿里云开发者社区

开发者社区> jeanron100> 正文

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

简介: 继续前几天的一个案例一个SQL性能问题的优化探索(一)(r11笔记第33天) 如下的SQL语句存在索引字段CARD_NO,但是执行的时候却走了全表扫描,因为这是一个核心表,数据量很大,导致数据库负载很高。
+关注继续查看

继续前几天的一个案例一个SQL性能问题的优化探索(一)(r11笔记第33天)

如下的SQL语句存在索引字段CARD_NO,但是执行的时候却走了全表扫描,因为这是一个核心表,数据量很大,导致数据库负载很高。

SQL_FULLTEXT
----------------------------------------------------------------------------------------------------
SELECT ID,CN,CARD_NO,TO_CHAR(CHARGE_DATE,'yyyy-MM-dd HH24:mi:ss') AS CHARGE_DATE ,IP,POINT,PRESENT_P
OINT,SUCCESS,CARD_TYPE,PRODUCTNUM,SALEMETHOD,ITEM_ID,SERVICEID,ORDER_NO,TO_CHAR(EXPIREDATE,'yyyy-MM-
dd HH24:mi:ss') AS EXPIREDATE,REMARK FROM RECHARGE_LOG WHERE CARD_NO = TO_NUMBER(:1)

     对于这个问题,苦于无法复现,通过各种途径进行分析,有些信息似乎有些矛盾。如果说是统计信息的问题,但是查看直方图的信息是有的。而且分为了200多个bucket,索引列card_no的统计信息也没有发现什么问题。而且比较奇怪的是问题发生后查看执行计划情况,后面的语句是可以走索引的。这个问题怎么来进一步分析呢。

    我们可以化被动为主动,我对这个列对应的索引添加了索引监控,即alter index xxxx monitoring usage;然后有了这个标识之后,就可以随时查看v$object_usage查看索引是否被启用,当然这个视图得到的信息毕竟很有限。我们只能知道索引是否被启用,但是每次启用的时间等这些没法定位,而且因为走了索引本身执行效率就很高,所以在快照中是很可能不会被收编进来的。

  这个问题还有一个疑点就是统计信息的收集时间,和同事确认问题发生当天他是没有收集统计信息的,但是查看字段last_analyzed的时间却恰恰是发生收集以后。

   问题暂时搁置下来,在这两天问题有了进一步的发现,另外一个报警引起了我的注意。报警是CPU使用率较高,查看数据库层面的负载也突然有了较大的抖动。

0?wx_fmt=png
定位到相关的语句如下:

   SNAP_ID SQL_ID        EXECUTIONS_DELTA ELAPSED_TI PER_TOTAL
---------- ------------- ---------------- ---------- ----------
    108829 bbtwpb1aamud2                0 1766s      45%
    108829 7zmnkpfaumuk1                1 1141s      29%
    108829 4y30fbyjmrrqr                1 432s       11%
    108829 f7h0xrkw1wy48                1 192s       4%
    108829 63t40nvuuwrdp           258730 116s       2%

可以看到明显有几个SQL执行效率较差。

查看TOP 1的语句发现竟然是一个JOB在运行。

SQL_FULLTEXT
----------------------------------------------------------------------------------------------------
DECLARE job BINARY_INTEGER := :job;  next_date TIMESTAMP WITH TIME ZONE := :mydate;  broken BOOLEAN
:= FALSE;  job_name VARCHAR2(30) := :job_name;  job_subname VARCHAR2(30) := :job_subname;  job_owner
 VARCHAR2(30) := :job_owner;  job_start TIMESTAMP WITH TIME ZONE := :job_start;  job_scheduled_start
 TIMESTAMP WITH TIME ZONE := :job_scheduled_start;  window_start TIMESTAMP WITH TIME ZONE := :window
_start;  window_end TIMESTAMP WITH TIME ZONE := :window_end;  BEGIN  BEGIN
dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'RECHARGE_LOG',cascade=>true);
END;  :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

语句的核心就是dbms_stats的部分,竟然是收集表recharege_log的统计信息。

后面的几个语句也是收集统计相关的递归SQL,我列举出一个是因为这里面有非常重要的一个信息。先卖个关子。

$sh showsqltext.sh 7zmnkpfaumuk1
insert /*+ append */ into sys.ora_temp_1_ds_33892 select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dy
namic_sampling(0) no_monitoring */"CHARGE_DATE","SERVICEID","ORDER_NO","EXPIREDATE", rowid SYS_DS_ALIAS_0 from "TEST"."RECHARGE_LOG" sample ( 12.12452
80643) t

这个时候查看语句的执行计划情况,只有全表扫描的执行计划。0?wx_fmt=png
明显不合理嘛。看起来问题又想回到了原点。

这个时候不要气馁,我们重新捋一捋信息,列的统计信息的情况如下:

>  select column_name,histogram from dba_tab_col_statistics
where table_name='RECHARGE_LOG';
COLUMN_NAME          HISTOGRAM
-------------------- ---------------
ID                   NONE
CN                   HEIGHT BALANCED
CARD_NO              HEIGHT BALANCED
CHARGE_DATE          HEIGHT BALANCED
IP                   HEIGHT BALANCED
POINT                FREQUENCY
PRESENT_POINT        NONE
SUCCESS              FREQUENCY
CARD_TYPE            FREQUENCY
PRODUCTNUM           NONE
SALEMETHOD           FREQUENCY
ITEM_ID              FREQUENCY
SERVICEID            NONE
ORDER_NO             HEIGHT BALANCED
REMARK               NONE
EXPIREDATE           NONE
ID                   NONE
CN                   NONE
CARD_NO              NONE
CHARGE_DATE          NONE
IP                   NONE
POINT                NONE
....

看起来有点不大对劲,因为CARD_NO的直方图信息是空的。

查看另外一个数据字典,发现这个列的直方图信息竟然是存在的,输出有上百行。

select to_char(endpoint_value) value,endpoint_number,column_name ,ENDPOINT_ACTUAL_VALUE from dba_tab_histograms where table_name = 'RECHARGE_LOG' and column_name in ('CARD_NO') ORDER BY endpoint_number

所以这个问题就比较有意思了,存在直方图的列的统计信息,但是两个视图中信息却有些冲突。

由此一来可以推断是这个地方导致全表扫描的一个原因。

我们来看看另外一个潜在的问题,收集统计信息的部分,可以看到是在凌晨3点运行,明显就是一个定时任务。所以同事所说的没有手工收集统计信息是真实的。

select table_name,
        to_char(LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
        from dba_tables where TABLE_NAME='RECHARGE_LOG';
TABLE_NAME                     LAST_ANALYZED
------------------------------ -------------------
RECHARGE_LOG                   2017-01-07 03:40:45 

  还有一个问题,那就是收集统计信息,怎么会有这些影响呢,我们得看看dbms_stats.gather_table_stats的参数method_opt,因为method_opt:决定histograms信息是怎样被统计的.method_opt的取值(默认值为FOR ALL COLUMNS SIZE AUTO),也就意味着是一个动态的过程。这不card_no摊上事情了,直接忽略了,没收集。

    关于这个问题,可以参考一个bug 

Bug 5949981 - Bad cardinality with histogram (Doc ID 5949981.8)

 而问题原因跟进一步,为什么card_no没有收集统计信息。可以参考一条SQL语句的执行计划变化探究(r10笔记第9天) 原因也是大同小异。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
魏红斌冬季实战营第一期:玩转云服务器(有干货)
本文均为本人实操整理,请勿搬运,学习交流可以随时评论---魏红斌
9 0
【Typescript入门手册】类型进阶
【Typescript入门手册】类型进阶
8 0
软件工程专业如何就业!
引言:曾经有两个强盗向他们的大哥说如果世上没有绞刑架该多好,大哥却回答说如果做强盗的代价那么小,人人都去干了,那么行业的竞争压力该有多大啊,正是因为不容易,做的人少,才有去做的价值啊。 在班会的开篇我给大家讲了这样一个故事,我认为人生就是不断地克服困难向上攀登,阻力是不可避免的,定的目标高一些,辛苦一些,当战胜它之后我们会发现这边风景独好。
8 0
面试题:说一说es6新增方法
面试题:说一说es6新增方法
13 0
备战5月软考--软件设计师
1.上午场需要做的就是不停的刷题,每个科目的题都要会个大概(大概有11个大考点) 2.下午场需要做的也是不停的做题,下午场的题型比较固定,做来做出都是那几类,自己做完总结适合自己的做题技巧第一题:数据流图方面的设计(实体Ei的名称、数据存储Di的名称,数据流图等)第二题:数据库设计(E-R图、完整性约束等)第三题:用例描述(识别参与者、用例名称、类名等)第四题:算法题第五题:设计模式的Java代码第六题:设计模式的C++代码
4 0
祝贺!我的同事李飞飞当选ACM Fellow、IEEE Fellow
因在数据库查询处理和优化以及云数据库系统方面所做出的卓越贡献而入选
5 0
数据库中间件 MyCAT源码分析 —— PreparedStatement 重新入门
数据库中间件 MyCAT源码分析 —— PreparedStatement 重新入门
5 0
关于“冬季实战营第一期”的学习报告
冬奥云小宝带你玩转五期实战营,初识上云基础,非常适合新手上路,上云必备环境准备让您轻松上云。
58 0
什么是前端开发?什么是后端开发?
什么是前端开发?什么是后端开发?
9 0
冬季实战营第一期:从零到一上手玩转云服务器场景体验报告之使用 PolarDB 和 ECS 搭建门户网站
冬季实战营第一期:从零到一上手玩转云服务器场景体验报告之使用 PolarDB 和 ECS 搭建门户网站
14 0
+关注
jeanron100
Oracle ACE,《Oracle DBA工作笔记》作者 现就职于国内某互联网公司,擅长数据管理,数据迁移,性能优化,目前专注于开源技术,运维自动化和性能优化。
1180
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载