37 积分	0 好友	3 主题

发消息

[RAC Clusterware] 一条简单的sql在生产环境走全表，测试环境走索引

1^#

发表于 2013-2-20 00:29:23 | 查看: 4465| 回复: 8

sql：
SELECT SUM(FRZ_MONEY) FROM T_FREEZELOG WHERE USER_ID = 32138 AND FRZ_STATUS = 1
附件为生产和测试环境10053事件trc文件，请帮忙分析下，谢谢。

trc.rar

17.41 KB, 下载次数: 1080

分享0

收藏0 回复只看该作者道具举报

背向天堂

2^#

发表于 2013-2-20 15:56:08

测试环境：
  Access Path: index (AllEqRange)
Index: IX_FREEZELOG_USER_ID1
resc_io: 32748.00  resc_cpu: 461643837
ix_sel: 0.026316  ix_sel_with_filters: 0.026316
Cost: 32775.59  Resp: 32775.59  Degree: 1

只能看出

生产环境：
  Access Path: index (AllEqRange)
Index: IX_FREEZELOG_USER_ID1
resc_io: 567486.00  resc_cpu: 8372202873
ix_sel: 0.472381  ix_sel_with_filters: 0.472381
Cost: 567986.35  Resp: 567986.35  Degree: 1

生产环境： ix_sel: 0.472381  ix_sel_with_filters: 0.472381
要远大于
测试环境： ix_sel: 0.026316  ix_sel_with_filters: 0.026316

但是这2个索引的统计信息很相似：
测试环境：
  Index: IX_FREEZELOG_USER_ID1  Col#: 6
LVLS: 2  #LB: 55814  #DK: 38  LB/K: 1468.00  DB/K: 31277.00  CLUF: 1188526.00

生产环境：
  Index: IX_FREEZELOG_USER_ID1  Col#: 6
LVLS: 2  #LB: 43284  #DK: 38  LB/K: 1139.00  DB/K: 30474.00  CLUF: 1158041.00

不太清楚是那边计算产生的差异。

期待刘大来分析下。

回复只看该作者道具举报

背向天堂

3^#

发表于 2013-2-20 15:58:34

ix_sel: 0.026316 ix_sel_with_filters: 0.026316

1/#DK = 1/38=0.0263157894736842，但是生产环境的值与这个计算不一致。

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2013-2-20 16:11:10

1. 测试环境

============
Plan Table
============
--------------------------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
--------------------------------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 32K | | | |
| 1 | SORT AGGREGATE | | 1 | 11 | | | | |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID | T_FREEZELOG | 90K | 990K | 32K | 00:07:34 | ROW LOCATION| ROW LOCATION|
| 3 | INDEX RANGE SCAN | IX_FREEZELOG_USER_ID1| 360K | | 1476 | 00:00:18 | | |
--------------------------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
2 - filter("FRZ_STATUS"=1)
3 - access("USER_ID"=32138)

复制代码

回复只看该作者道具举报

Maclean Liu(刘相兵

5^#

发表于 2013-2-20 16:12:38

2. 你的生产环境

============
Plan Table
============
-------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
-------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 76K | | | |
| 1 | SORT AGGREGATE | | 1 | 11 | | | | |
| 2 | PARTITION RANGE ALL | | 32K | 353K | 76K | 00:16:32 | 1 | 17 |
| 3 | TABLE ACCESS FULL | T_FREEZELOG| 32K | 353K | 76K | 00:16:32 | 1 | 17 |
-------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
3 - filter(("FRZ_STATUS"=1 AND "USER_ID"=32138))

复制代码

回复只看该作者道具举报

Maclean Liu(刘相兵

6^#

发表于 2013-2-20 18:58:34

统计信息对比
测试环境：

SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T_FREEZELOG[T_FREEZELOG]
  ColGroup (#1, Index) PK_FRZLOG
Col#: 1 9 CorStregth: -1.00
  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial:
  Table: T_FREEZELOG  Alias: T_FREEZELOG
Card: Original: 14008159.000000  Rounded: 92159  Computed: 92158.94  Non Adjusted: 92158.94
  Access Path: TableScan
Cost:  76739.56  Resp: 76739.56  Degree: 0
   Cost_io: 76267.00  Cost_cpu: 7907220465
   Resp_io: 76267.00  Resp_cpu: 7907220465
  Access Path: index (AllEqRange)
Index: IX_FREEZELOG_USER_ID1
resc_io: 32748.00  resc_cpu: 461643837
ix_sel: 0.026316  ix_sel_with_filters: 0.026316
Cost: 32775.59  Resp: 32775.59  Degree: 1
  Best:: AccessPath: IndexRange
  Index: IX_FREEZELOG_USER_ID1
      Cost: 32775.59  Degree: 1  Resp: 32775.59  Card: 92158.94  Bytes: 0

生产环境

SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T_FREEZELOG[T_FREEZELOG]
  Column (#6):
NewDensity:0.000092, OldDensity:0.000000 BktCnt:5413, PopBktCnt:5410, PopValCnt:14, NDV:38
  Column (#12):
NewDensity:0.002125, OldDensity:0.000000 BktCnt:5413, PopBktCnt:5413, PopValCnt:3, NDV:4
  ColGroup (#1, Index) PK_FRZLOG
Col#: 1 9 CorStregth: -1.00
  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial:
  Table: T_FREEZELOG  Alias: T_FREEZELOG
Card: Original: 13935158.000000  Rounded: 32834  Computed: 32834.49  Non Adjusted: 32834.49
  Access Path: TableScan
Cost:  77604.19  Resp: 77604.19  Degree: 0
   Cost_io: 77133.00  Cost_cpu: 7884378045
   Resp_io: 77133.00  Resp_cpu: 7884378045
  Access Path: index (AllEqRange)
Index: IX_FREEZELOG_USER_ID1
resc_io: 567486.00  resc_cpu: 8372202873
ix_sel: 0.472381  ix_sel_with_filters: 0.472381
Cost: 567986.35  Resp: 567986.35  Degree: 1
  Best:: AccessPath: TableScan
      Cost: 77604.19  Degree: 1  Resp: 77604.19  Card: 32834.49  Bytes: 0

1. 就10053 看统计信息方面，2者是有差别的，测试环境中似乎没有显示有histogram
2. PK_FRZLOG 上的列有多列统计信息，但是这个10053 trace显示的这个multicolumn 统计信息似乎不完整

===》

删除原有 histogram 和 col group statistics，后再观察一下。

回复只看该作者道具举报