一条SQL语句的执行计划变化探究(r10笔记第9天)

继续上次分析的一个问题,一个简单的SQL语句执行计划有些奇怪,明明可以走唯一性索引但是却走了另外一个索引。 当然了,最后逐步定位,发现是在直方图的地方有一些差别。取消直方图之后,执行计划立刻恢复了正常。 当然问题来了,这个是为什么呢,收集统计信息中的auto选项是什么含义呢。为什么两个数据类型一样的(varchar2(64))的列,境遇却大大不同。 我们来看看一些统计信息的数据。

为了跟进一步验证数据的分布律和选取代价,我们查询它的直方图信息。 SQL> select to_char(endpoint_value) value,endpoint_number,column_name from dba_tab_histograms where table_name = 'OP_ORDER' and column_name in ('ORDER_ID','USER_ID') ORDER BY endpoint_number;

可以这两条结果对应的查询结果有248行,ORDER_ID只有两行,而USER_ID却又246行,也就意味着USER_ID对应有246个bucket,对于数据的分布情况统计更为周密。 这又是为什么呢,两个字段都是varchar2,怎么会差别这么大呢。 我们取出几条数据来。 SQL> select order_id from ordermob.OP_ORDER where rownum<10; ORDER_ID ---------------------------------------------------------------- 160526163113314574 160526163122274152 160526163130777725 160526164612542552 160526172953321536 160526173306557175 160526173335364777 160526180054556153 160526180101316451 看得出来签名的很多位都是一样的,这种订单业务的数据,订单号都有一定的规范,签名的值还是有一定的规律可循。 SQL> select to_char(endpoint_value) value,endpoint_number,column_name from dba_tab_histograms where table_name = 'OP_ORDER' and column_name in ('ORDER_ID','USER_ID') ORDER BY endpoint_number VALUE ENDPOINT_NUMBER COLUMN_NAME ---------------------------------------- --------------- ---------------------------------------- 255521615291332000000000000000000000 0 ORDER_ID 255521616530467000000000000000000000 1 ORDER_ID 可以看到端点值(endpoint_value),endpoint_value就是列的值,非数字类型(VARCHAR2,CHAR,NVARCHAR2,NCHAR)必须进行转换,仅取前六个字节(不是字符)。从10g实测数据来看取前15个字节,前30个字符有效转换,其他都会忽略。也就是收集直方图相当于只对字段B的substr(B,1,30)收集桶信息。 这个信息怎么进行确认呢。我们取出一条数据来测试。 以max(order_id)为例,先取得dump的元数据信息。 SQL> select to_char(substrb(dump(max("ORDER_ID"),16,0,32),1,120)) from "ORDERMOB"."OP_ORDER" t ; TO_CHAR(SUBSTRB(DUMP(MAX("ORDER_ID"),16,0,32),1,120)) ------------------------------------------------------------------------------------------------------------------------ Typ=1 Len=18: 31,36,30,38,32,36,31,35,35,30,33,38,33,35,31,33,32,35 然后进行转换,转换进制。 SQL> select to_number('313630383236313535303338333531','xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') aa from dual; AA -------------------------------------------- 255521616530467185179705496063653169 看看这个转换后的值是否为 255521616530467,也就是转换进制后的前15位保留值。 SQL> select length('255521616530467') from dual; LENGTH('255521616530467') ------------------------- 15 发现确实如此。 而整个串有36位。对于这类场景来看就很难去区分出数据的细粒度差别来。 SQL> select length('255521616530467000000000000000000000') from dual; LENGTH('255521616530467000000000000000000000') ---------------------------------------------- 36 所以对于order_id的直方图信息就会只分配2个bucket,而这个过程如何验证,那就是使用经典的10046事件了。 里面的计算方式 to_char(substrb(dump(max("ORDER_ID"),16,0,32),1,120)) from "ORDERMOB"."OP_ORDER" 正式出自10046的trace文件。

当然可以自己找个环境继续验证一下。 > create table test_stats (order_id varchar2(64),user_id varchar2(64),channel_id number); Table created. > insert into test_stats values('0000000000001241414','test',1); 1 row created. > insert into test_stats values('0000000000001251414','test2',2); 1 row created. > insert into test_stats values('0000000000001251514','test3',2); 1 row created. > commit; Commit complete. 生成10046事件来查看。 ALTER SESSION SET EVENTS '10046 trace name context forever, level 12' exec dbms_stats.gather_table_stats(tabname => 'test_stats',ownname => 'TEST',method_opt => 'FOR ALL COLUMNS SIZE AUTO'); ALTER SESSION SET EVENTS '10046 trace name context off'

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2016-09-01

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏desperate633

浅谈索引的优缺点和建立索引的原则

13210
来自专栏学习有记

聚集索引:SQL Server 进阶 Level 3

18030
来自专栏高性能服务器开发

数据库进阶2 Mysql高并发优化

如果不能设计一个合理的数据库模型,不仅会增加客户端和服务器段程序的编程和维护的难度,而且将会影响系统实际运行的性能。所以,在一个系统开始实施之前,完备的数据库模...

24510
来自专栏数据和云

让SQL成为一种生活方式:认识分页查询

? 1分页查询的书写方式 分页的目的就是控制输出结果集大小,将结果尽快的返回。 Oracle的分页查询语句有两种基本格式。第一种格式如下: ? 上面给出的这个...

32260
来自专栏学习有记

深入非聚集索引:SQL Server索引进阶 Level 2

12730
来自专栏Java帮帮-微信公众号-技术文章全总结

【数据库】数据库优化(SQL优化)

【数据库】数据库优化(SQL优化) sql语句优化 1.查询的模糊匹配 尽量避免在一个复杂查询里面使用 LIKE '%parm1%'—— 红色标识位置的百分...

46140
来自专栏idba

死锁案例之九

死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋...

13130
来自专栏美团技术团队

美团点评SQL优化工具SQLAdvisor开源

介绍 在数据库运维过程中,优化 SQL 是 DBA 团队的日常任务。例行 SQL 优化,不仅可以提升程序性能,还能够降低线上故障的概率。 目前常用的 SQL 优...

47160
来自专栏大数据挖掘DT机器学习

50多条实用mysql数据库优化建议

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 缺省情况下建立的索引是非群集索引,但有时它并不是最...

63660
来自专栏IT技术精选文摘

MySQL索引设计概要

在关系型数据库中设计索引其实并不是复杂的事情,很多开发者都觉得设计索引能够提升数据库的性能,相关的知识一定非常复杂。 ? 然而这种想法是不正确的,索引其实并不是...

39560

扫码关注云+社区

领取腾讯云代金券