ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 1536|回复: 21

[性能调整] 分区表收集统计信息后,某sql语句性能反而降低了上千倍

[复制链接]
论坛徽章:
0
发表于 2017-3-30 14:00 | 显示全部楼层 |阅读模式
本帖最后由 running_life 于 2017-3-30 14:13 编辑

rhel 6.8
oracle 11.2.0.4.161018 单机  ASM存储

某表 table_a range-hash复合分区,先按照数据创建时间created_time划分range分区,一天一个分区,然后再按照里面的网点名称channel_name做hash子分区,分16个分区

语句如下:
select count(*)
  from table_a t
where t.provno = '10040'
   AND t.bstatus = 30
   AND t.mstatus = 20;

table_a的数据量有1000万数据左右
其中bstatus = 30的数据量非常少,0-100之间,这个字段有索引,普通的索引
mstatus = 20的数据量很多,接近表的总量,无索引。

1、我收集了统计信息,选择收集所有分区,子分区的,global的统计信息
begin
  dbms_stats.gather_table_stats(ownname          => 'USER_A',
                                tabname          => 'TABLE_A',
                                granularity => 'ALL',
                                estimate_percent => null,
                                method_opt       => 'for all indexed columns',
                                degree           => 6,
                                cascade          => true,
                                no_invalidate    => false);
END;
完成后查询该语句,是全表扫描的执行计划

2、granularity => 'AUTO',收集后执行还是全表扫描

3、granularity => 'GLOBAL'收集后,执行语句还是全表扫描

4、删除统计信息,删除后执行语句,走了bstatus的索引。
begin
  dbms_stats.delete_table_stats(ownname         => 'USER_A',
                                tabname         => upper('TABLE_A'),
                                cascade_parts   => true,
                                cascade_columns => true,
                                cascade_indexes => true,
                                no_invalidate   => false);
end;

疑问:为什么准确收集统计信息后他还走错了,这么简单的语句,执行计划还走错

我看了10053,oracle估算出来的走全表的cost要小于走bstatus字段索引的cost


论坛徽章:
1
2017金鸡报晓
日期:2017-02-08 14:09:13
发表于 2017-3-30 14:11 | 显示全部楼层
bstatus列建的索引是本地分区索引还是普通索引?建成普通索引,再收集直方图,走索引的机会很大。
这个表的分区方式对上面的sql一点卵用都没有,没分区剪裁,这么点数据一天一个分区,分区后还有hash子分区,请问是出于什么考虑?

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2017-3-30 14:13 | 显示全部楼层
cuicuigo 发表于 2017-3-30 14:11
bstatus列建的索引是本地分区索引还是普通索引?建成普通索引,再收集直方图,走索引的机会很大。
这个表的 ...

不好意思,忘记说了,这个是普通索引

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2017-3-30 14:14 | 显示全部楼层
cuicuigo 发表于 2017-3-30 14:11
bstatus列建的索引是本地分区索引还是普通索引?建成普通索引,再收集直方图,走索引的机会很大。
这个表的 ...

分区是对这个sql没用的,我只是描述下表的具体结构。对其他语句有用

使用道具 举报

回复
论坛徽章:
1
2017金鸡报晓
日期:2017-02-08 14:09:13
发表于 2017-3-30 14:15 | 显示全部楼层
能发一下全表和走索引的执行计划吗

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2017-3-30 14:31 | 显示全部楼层
本帖最后由 running_life 于 2017-3-30 14:37 编辑
cuicuigo 发表于 2017-3-30 14:15
能发一下全表和走索引的执行计划吗

图片
数据量有所夸大,不过这是后期会出现的情况,这个是开发测试中遇到的问题
index.png
full.png

使用道具 举报

回复
论坛徽章:
1
2017金鸡报晓
日期:2017-02-08 14:09:13
发表于 2017-3-30 14:48 | 显示全部楼层
看起来是收集统计信息时没有收集直方图,重新收集统计信息并收集直方图,再跑sql之前注意清除旧的执行计划。

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2017-3-30 15:03 | 显示全部楼层
cuicuigo 发表于 2017-3-30 14:48
看起来是收集统计信息时没有收集直方图,重新收集统计信息并收集直方图,再跑sql之前注意清除旧的执行计划 ...

我收集时用的method参数设置的for all indexed columns,这个是收集了直方图的哦

使用道具 举报

回复
论坛徽章:
1
2017金鸡报晓
日期:2017-02-08 14:09:13
发表于 2017-3-30 15:11 | 显示全部楼层
select COLUMN_NAME,NUM_DISTINCT,NUM_NULLS,NUM_BUCKETS,LAST_ANALYZED,AVG_COL_LEN,HISTOGRAM from DBA_TAB_COL_STATISTICS where TABLE_NAME=upper('&tablename');
这个看看统计信息情况如何

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2017-3-30 15:15 | 显示全部楼层
cuicuigo 发表于 2017-3-30 15:11
select COLUMN_NAME,NUM_DISTINCT,NUM_NULLS,NUM_BUCKETS,LAST_ANALYZED,AVG_COL_LEN,HISTOGRAM from DBA_T ...

直方图
直方图.png

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

久等啦!10张门票开启你的DTCC2017之旅~

2017中国数据库技术大会将于2017年5月11-13日如约而至,本届大会以“数据驱动•价值发现”为主题,共设定2大主场和21个技术专场,云集海内外120+位技术大牛,共同探讨Oracle、MySQL、NoSQL、云端数据库、区块链、深度学习等领域的前瞻性热点话题。
即日起,填写DTCC2017会前调查问卷,即有机会赢取价值2600元的大会门票1张!仅限10张!
----------------------------------------
活动截止时间:2017年5月5日统一公布

问卷入口>>
TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表