查看: 8187|回复: 14

[性能调整] 【讨论】11G ORACLE SQL优化一列

[复制链接]
论坛徽章:
15
马上加薪
日期:2014-12-03 12:48:23双子座
日期:2016-01-15 10:47:09秀才
日期:2016-01-13 12:14:26秀才
日期:2015-12-25 15:31:10双子座
日期:2015-12-15 09:58:52巨蟹座
日期:2015-11-17 17:17:02白羊座
日期:2015-11-09 13:49:13双子座
日期:2015-11-02 10:25:11秀才
日期:2015-09-21 09:46:16水瓶座
日期:2015-08-25 15:30:04
发表于 2015-2-4 14:49 | 显示全部楼层 |阅读模式
前几天生产环境的一个案例,在ORACLE也开了case文了,把大概过程和大家分享下 。
表RPT_LOAN_M  964700行 BLOCK  121901。
语句: select * from RPT_LOAN_M where INPUTDATE = '2015/01';
索引:
现在是sql会走全部扫描,TRACE如下。


SQL> select * from yrlrpt.RPT_LOAN_M where INPUTDATE = '2015/01';

43704 rows selected.

Elapsed: 00:00:22.44

Execution Plan
----------------------------------------------------------
Plan hash value: 4093137959

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40830 | 33M| 26814 (1)| 00:05:22 |
|* 1 | TABLE ACCESS FULL| RPT_LOAN_M | 40830 | 33M| 26814 (1)| 00:05:22 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("INPUTDATE"='2015/01')


Statistics
----------------------------------------------------------
1 recursive calls
1 db block gets
124497 consistent gets
63771 physical reads
0 redo size
24401976 bytes sent via SQL*Net to client
32563 bytes received via SQL*Net from client
2915 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
43704 rows processed

SQL> select /*+INDEX(RPT_LOAN_M RPT_LOAN_M_PK)*/ * from yrlrpt.RPT_LOAN_M where INPUTDATE = '2015/01';

43704 rows selected.

Elapsed: 00:00:07.19

Execution Plan
----------------------------------------------------------
Plan hash value: 2478910763

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40830 | 33M| 32078 (1)| 00:06:25 |
| 1 | TABLE ACCESS BY INDEX ROWID| RPT_LOAN_M | 40830 | 33M| 32078 (1)| 00:06:25 |
|* 2 | INDEX RANGE SCAN | RPT_LOAN_M_PK | 40830 | | 325 (1)| 00:00:04 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("INPUTDATE"='2015/01')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
38364 consistent gets
0 physical reads
0 redo size
38271668 bytes sent via SQL*Net to client
32563 bytes received via SQL*Net from client
2915 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
43704 rows processed

论坛徽章:
15
马上加薪
日期:2014-12-03 12:48:23双子座
日期:2016-01-15 10:47:09秀才
日期:2016-01-13 12:14:26秀才
日期:2015-12-25 15:31:10双子座
日期:2015-12-15 09:58:52巨蟹座
日期:2015-11-17 17:17:02白羊座
日期:2015-11-09 13:49:13双子座
日期:2015-11-02 10:25:11秀才
日期:2015-09-21 09:46:16水瓶座
日期:2015-08-25 15:30:04
 楼主| 发表于 2015-2-4 14:52 | 显示全部楼层
本帖最后由 anwll 于 2015-2-4 14:57 编辑

可以看到oracle认为全表扫描的成本更低。
贴上 SQLT里跑出来的10053
sqlt_s38462_10053_execute.trc

FileComment
-----------------
Table Stats::
Table: RPT_LOAN_M Alias: AA
#Rows: 964700 #Blks: 121901 AvgRowLen: 850.00 ChainCnt: 0.00
Index Stats::
Index: RPT_LOAN_M_PK Col#: 1 2
LVLS: 2 #LB: 7588 #DK: 996595 LB/K: 1.00 DB/K: 1.00 CLUF: 749069.00

SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for RPT_LOAN_M[AA]
Column (#1):
NewDensity:0.013854, OldDensity:0.000001 BktCnt:9817, PopBktCnt:9817, PopValCnt:28, NDV:28
Column (#1): INPUTDATE(
AvgLen: 8 NDV: 28 Nulls: 0 Density: 0.013854
Histogram: Freq #Bkts: 28 UncompBkts: 9817 EndPtVals: 28
Table: RPT_LOAN_M Alias: AA
Card: Original: 964700.000000 Rounded: 40830 Computed: 40830.48 Non Adjusted: 40830.48
Access Path: TableScan
Cost: 26813.99 Resp: 26813.99 Degree: 0
Cost_io: 26668.00 Cost_cpu: 1221920857
Resp_io: 26668.00 Resp_cpu: 1221920857

Access Path: index (RangeScan)
Index: RPT_LOAN_M_PK
resc_io: 32029.00 resc_cpu: 409049092
ix_sel: 0.042325 ix_sel_with_filters: 0.042325
Cost: 32077.87 Resp: 32077.87 Degree: 1

Best:: AccessPath: TableScan
Cost: 26813.99 Degree: 1 Resp: 26813.99 Card: 40830.48 Bytes: 0

_db_file_optimizer_read_count = 16
===========


表上有个主键,INPUTDATE和SERIALNO,其中INPUTDATE都是例如2015/01这样的值,从2012年到现在共28个月的数据,每个月数据量相差不多。
PLAIN        RPT_LOAN_M_PK        Y        YES        1        INPUTDATE        Asc        1        YRLRPT
PLAIN        RPT_LOAN_M_PK        Y        YES        1        SERIALNO        Asc        2        YRLRPT
表的统计信息很新没有问题的,
部分直方图信息:
YRLRPT.RPT_LOAN_M.INPUTDATE - Histogram

#        Endpoint Number        Endpoint Value1        Endpoint Actual Value1        Estimated Endpoint Value1        Estimated Cardinality        Estimated Selectivity
......
27        9401        260592296917952000000000000000000000        "2014/12"        "2014/12"        39995        0.041459
28        9817        260592297227433000000000000000000000        "2015/01"        "2015/01"        40880        0.042375


使用道具 举报

回复
论坛徽章:
15
马上加薪
日期:2014-12-03 12:48:23双子座
日期:2016-01-15 10:47:09秀才
日期:2016-01-13 12:14:26秀才
日期:2015-12-25 15:31:10双子座
日期:2015-12-15 09:58:52巨蟹座
日期:2015-11-17 17:17:02白羊座
日期:2015-11-09 13:49:13双子座
日期:2015-11-02 10:25:11秀才
日期:2015-09-21 09:46:16水瓶座
日期:2015-08-25 15:30:04
 楼主| 发表于 2015-2-4 14:58 | 显示全部楼层
anwll 发表于 2015-2-4 14:52
可以看到oracle认为全表扫描的成本更低。
贴上 SQLT里跑出来的10053
sqlt_s38462_10053_execute.trc [/ba ...

ORACLE的回复:

Oracle并未有公开优化器Cost的算法,不过在早期Oracle 9i的文档中曾经提到一个计算模型,参考:

http://docs.oracle.com/cd/B10501 ... 3/ex_plan.htm#19598

Cost = (#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed ) / sreadtim

where:

#SRDs is the number of single block reads
#MRDs is the number of multi block reads
#CPUCycles is the number of CPU Cycles *)
sreadtim is the single block read time
mreadtim is the multi block read time
cpuspeed is the CPU cycles per second
CPUCycles includes CPU cost of query processing (pure CPU cost) and CPU cost of data retrieval (CPU cost of the buffer cache get).

This model is straightforward for serial execution. For parallel execution, necessary adjustments are made while computing estimates for #SRD, #MRD, and #CPUCycles.

其中:
sreadtim = ioseektim + db_block_size/iotfrspeed
mreadtim = ioseektim + MBRC * db_block_size / iotrfspeed
MBRC = _db_file_optimizer_read_count
#MRds = #Blks/MBRC
注意:#Blks指表的block总数,以上其他信息可以从sys.aux_stats$获取:
select pname, pval1 from sys.aux_stats$;

这样可以计算FULL TABLE SCAN和INDEX RANGE SCAN的cost:

FULL TABLE SCAN (FTS)
====================
(Workload statistics are gathered)
(CPU Costing is enabled)
FTS Cost = I/O Cost + CPU Cost

Note:
I/O Cost = 1 + CEIL(#MRds * (mreadtim/sreadtim))
#MRds = #Blks/MBRC

CPU Cost = ROUND(#CPUCycles/cpuspeed/1000/sreadtim)

B*TREE INDEX RANGE SCAN (IRS)
============================
(Workload statistics are gathered)
(CPU Costing is enabled)
IRS Cost = I/O Cost + CPU Cost

Note:
I/O Cost = Index Access I/O Cost + Table Access I/O Cost
Index Access I/O Cost = LVLS + CEIL(#LB * ix_sel)
Table Access I/O Cost = CEIL(CLUF * ix_sel_with_filters)

CPU Cost = ROUND(#CPUCycles/cpuspeed/1000/sreadtim)

不过这只在早期版本上吻合,并且没有考虑parallel 以及 optimizer_index_cost_adj的影响。
事实上即使我们知道了cost的算法,我们也改变不了什么。

基于以上的一些描述,结合您的10053:

Access Path: TableScan Cost: 26813.99 <<<<
Access Path: index (RangeScan)
Index: RPT_LOAN_M_PK
resc_io: 32029.00 resc_cpu: 409049092
ix_sel: 0.042325 ix_sel_with_filters: 0.042325
Cost: 32077.87 <<<<

全表扫描的cost远小于RPT_LOAN_M_PK的RangeScan,因此Oracle选择了TableScan。

如果您确认采用RPT_LOAN_M_PK性能更好,建议采用如下措施:

Suggestion Plan
===========
1. 可以适度减少RPT_LOAN_M_PK的CLUSTERING_FACTOR,目前为749069,减小到600000应当足以让优化器选择index range scan了

begin
dbms_stats.set_index_stats(ownname => 'YRLRPT',
indname => 'RPT_LOAN_M_PK',
clstfct => 600000,
no_invalidate => false,
force => true);
end;
/

2. 目前收集统计信息的采样率不是100%,表总记录数显示964700 采样为 9817,建议加大采样率看RPT_LOAN_M_PK的CLUSTERING_FACTOR是否减小:
begin
dbms_stats.unlock_table_stats(ownname => 'YRLRPT',
tabname => 'RPT_LOAN_M');
dbms_stats.gather_table_stats(ownname => 'YRLRPT',
tabname => 'RPT_LOAN_M',
estimate_percent => 100,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
cascade => true,
no_invalidate => false);
end;
/

3. 将db_file_multiblock_read_count恢复成默认值,查看此时全表扫描性能是否仍然低于索引扫描。

4. 考虑使用sql profile固定使用索引的执行计划。

使用道具 举报

回复
论坛徽章:
15
马上加薪
日期:2014-12-03 12:48:23双子座
日期:2016-01-15 10:47:09秀才
日期:2016-01-13 12:14:26秀才
日期:2015-12-25 15:31:10双子座
日期:2015-12-15 09:58:52巨蟹座
日期:2015-11-17 17:17:02白羊座
日期:2015-11-09 13:49:13双子座
日期:2015-11-02 10:25:11秀才
日期:2015-09-21 09:46:16水瓶座
日期:2015-08-25 15:30:04
 楼主| 发表于 2015-2-4 15:09 | 显示全部楼层
本帖最后由 anwll 于 2015-2-4 16:41 编辑

这个表的block只有121901,CLUSTERING_FACTOR是681378,行数是90多万,CLUSTERING_FACTOR是使用索引扫描全表时如果当前rowid和前一个rowid不是同一个block,这个值就增加1,现在CLUSTERING_FACTOR比全部的数据块还多,说明按照主键扫描表时我的数据非常分散,也就让oracle认为走索引cost更高。

有索引有统计信息还要注意索引的CLUSTERING_FACTOR值,现在在考虑是否要把这些只插入不删除的表,和经常插入删除或者会delete重建的表分在不同的表空间。

其实这张表从创建起到现在就只增加数据不删除,但是表空间里其他表数据确实变动的很多,我也查了一些资料,一个索引的CLUSTERING_FACTOR永远会是越来越大,这样的表我自己理解,要么是迁出来独立占用一个空间,要么就过段时间重新整理下。
用这样的方法 insert /*+append */ into rpt_loan_m select * from tt order inputdate,SERIALNO;
单独的去导入导出一张表业并不一定能解决CLUSTERING_FACTOR高的问题。后来在测试环境做了个测试。





SQL> select * from yrlrpt.rpt_loan_m where inputdate='2014/08';

39674 rows selected.

Elapsed: 00:00:06.23

Execution Plan
----------------------------------------------------------
Plan hash value: 2478910763

--------------------------------------------------------------------------------
-------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU
)| Time     |

--------------------------------------------------------------------------------
-------------

|   0 | SELECT STATEMENT            |               | 34559 |    23M| 27127   (1
)| 00:05:26 |

|   1 |  TABLE ACCESS BY INDEX ROWID| RPT_LOAN_M    | 34559 |    23M| 27127   (1
)| 00:05:26 |

|*  2 |   INDEX RANGE SCAN          | RPT_LOAN_M_PK | 34559 |       |   159   (1
)| 00:00:02 |

--------------------------------------------------------------------------------
-------------
  
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("INPUTDATE"='2014/08')

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      35095  consistent gets
          0  physical reads
          0  redo size
   29546159  bytes sent via SQL*Net to client
      29604  bytes received via SQL*Net from client
       2646  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      39674  rows processed
SQL> select * from yrlrpt.rpt_loan_m where inputdate='2014/08';
39674 rows selected.

Elapsed: 00:00:06.25
Execution Plan
----------------------------------------------------------
Plan hash value: 2478910763

--------------------------------------------------------------------------------
-------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU
)| Time     |

--------------------------------------------------------------------------------
-------------

|   0 | SELECT STATEMENT            |               | 34559 |    23M|  3780   (1
)| 00:00:46 |

|   1 |  TABLE ACCESS BY INDEX ROWID| RPT_LOAN_M    | 34559 |    23M|  3780   (1
)| 00:00:46 |

|*  2 |   INDEX RANGE SCAN          | RPT_LOAN_M_PK | 34559 |       |   159   (1
)| 00:00:02 |

--------------------------------------------------------------------------------
-------------
  
Predicate Information (identified by operation id):
---------------------------------------------------
    2 - access("INPUTDATE"='2014/08')

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       9392  consistent gets
          0  physical reads
          0  redo size
   29546159  bytes sent via SQL*Net to client
      29604  bytes received via SQL*Net from client
       2646  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      39674  rows processed

使用道具 举报

回复
论坛徽章:
55
山治
日期:2017-01-03 16:19:442014年新春福章
日期:2014-05-15 10:20:51马上有钱
日期:2014-06-19 14:11:34路虎
日期:2014-06-19 14:11:34马上加薪
日期:2014-06-19 14:11:342014年新春福章
日期:2014-06-19 14:11:34马上有对象
日期:2014-02-18 16:44:082014年新春福章
日期:2014-02-18 16:44:08阿斯顿马丁
日期:2014-06-19 14:11:34问答徽章
日期:2013-11-21 09:21:29
发表于 2015-2-4 15:22 | 显示全部楼层
Good。

我认为最主要的问题还是CLUF。行数据分布在太多块上,不得不全表扫描获取到数据。
按列INPUTDATE的排序重建表可以减小CLUF,但这种操作对很多生产环境来说不允许的。

使用道具 举报

回复
论坛徽章:
15
马上加薪
日期:2014-12-03 12:48:23双子座
日期:2016-01-15 10:47:09秀才
日期:2016-01-13 12:14:26秀才
日期:2015-12-25 15:31:10双子座
日期:2015-12-15 09:58:52巨蟹座
日期:2015-11-17 17:17:02白羊座
日期:2015-11-09 13:49:13双子座
日期:2015-11-02 10:25:11秀才
日期:2015-09-21 09:46:16水瓶座
日期:2015-08-25 15:30:04
 楼主| 发表于 2015-2-4 16:38 | 显示全部楼层
xing2999 发表于 2015-2-4 15:22
Good。

我认为最主要的问题还是CLUF。行数据分布在太多块上,不得不全表扫描获取到数据。

是的,我在测试重构表后就可以了。我有一个疑问,
如果索引值inx1在块blk1上,inx2在块blk2上,inx3在块blk1上。
遍历inx1,2,3其实会扫描两次blk1咯。

使用道具 举报

回复
认证徽章
论坛徽章:
40
2014年新春福章
日期:2014-02-18 16:43:09喜羊羊
日期:2015-05-18 16:24:25慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47白羊座
日期:2015-09-08 10:39:06天枰座
日期:2015-09-17 21:41:53摩羯座
日期:2015-10-29 21:07:02
发表于 2015-2-4 16:48 | 显示全部楼层
anwll 发表于 2015-2-4 15:09
这个表的block只有121901,CLUSTERING_FACTOR是681378,行数是90多万,CLUSTERING_FACTOR是使用索引扫描全表 ...

学习了。

使用道具 举报

回复
论坛徽章:
68
2012新春纪念徽章
日期:2012-01-04 11:51:22奥运会纪念徽章:举重
日期:2012-08-02 22:17:14ITPUB 11周年纪念徽章
日期:2012-10-09 18:07:312013年新春福章
日期:2013-02-25 14:51:24慢羊羊
日期:2015-03-04 14:51:352015年新春福章
日期:2015-03-06 11:57:312015年新春福章
日期:2015-06-11 12:54:06
发表于 2015-2-4 17:33 | 显示全部楼层
这种问题比较常见,计算公式就是这样,CF是很重要的

使用道具 举报

回复
论坛徽章:
0
发表于 2015-2-4 23:12 | 显示全部楼层
解释的很详细啊,学习学习!

使用道具 举报

回复
论坛徽章:
15
马上加薪
日期:2014-12-03 12:48:23双子座
日期:2016-01-15 10:47:09秀才
日期:2016-01-13 12:14:26秀才
日期:2015-12-25 15:31:10双子座
日期:2015-12-15 09:58:52巨蟹座
日期:2015-11-17 17:17:02白羊座
日期:2015-11-09 13:49:13双子座
日期:2015-11-02 10:25:11秀才
日期:2015-09-21 09:46:16水瓶座
日期:2015-08-25 15:30:04
 楼主| 发表于 2015-2-5 09:32 | 显示全部楼层
howard_zhang 发表于 2015-2-4 17:33
这种问题比较常见,计算公式就是这样,CF是很重要的

以前碰上很多统计信息由问题的,CF导致的还是第一次。

使用道具 举报

回复

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

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表