SQL调优问题求助（关于MAX/MIN)

hctech

生产系统有一个表：ZZCD1，有很多这样的查询：
SELECT MAX( "ZNO" ) FROM "ZZCD1" WHERE "MANDT" = :A0 AND "EBELN" = :A1
该表有一个PK索引：ZZCD1~1：MANDT,ZNO,EBELN,EBELP
其中MANDT只有，100,800,900这些类型的值（没办法。。。SAP系统就这样）。
在生产系统该SQL的执行计划为：
-------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|         |     1 |    26 |  2635   (1)| 00:00:32 |        |
|   1 |  SORT AGGREGATE        |         |     1 |    26 |            |          |        |
|*  2 |   INDEX RANGE SCAN     | ZZCD1~0 |     9 |   234 |  2635   (1)| 00:00:32 |    LEP |
-------------------------------------------------------------------------------------------

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

   2 - access("MANDT"=:A0 AND "EBELN"=:A1)
       filter("EBELN"=:A1)

Note
-----
   - fully remote statement
   
   
接下来尝试优化，尝试将其变为：INDEX RANGE SCAN (MIN/MAX)的访问路径。
于是将表expdp出来impdp到了另外一个系统，啥都没做，相同的sql语句执行计划却不同了：
-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE      |         |     1 |    26 |     3   (0)| 00:00:01 |        |
|   1 |  SORT AGGREGATE              |         |     1 |    26 |            |          |        |
|   2 |   FIRST ROW                  |         |   151 |  3926 |     3   (0)| 00:00:01 |        |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| ZZCD1~0 |   151 |  3926 |     3   (0)| 00:00:01 |   TEST |
-------------------------------------------------------------------------------------------------

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

   3 - access("MANDT"=:A0)
       filter("EBELN"=:A1)

Note
-----
   - fully remote statement
   
这个就是预期想要的执行计划，但是在生产库死活出不来，重新统计了表，重组了索引，执行计划仍然没有改变。
请教这个是怎么回事啊，还有哪方面的因素在其中影响，谢谢~！

Maclean Liu(刘相兵

是否可以提供这2个系统上的10053 event trace?

szkking

如那个student guide一类的，关于SQL方面，从基础到晋级一类。

wangzk0206

对于这个SQL而言，建议调整下主键的顺序：
MANDT,ZNO,EBELN,EBELP   ==>MANDT,EBELN,ZNO,EBELP

根据业务看下，调整成这个顺序是否会对其他业务SQL有影响，如果没有影响，建议调整成这个样子

hctech

10053做的是level 1的，不知道行不行

wangzk0206

从跟踪文件中看   两个系统的统计信息不一样哦
生产系统的MANDT列的NDV好像不太准吧。

hctech

全部重新收集统计信息，执行计划仍然没有改变。
重新做10053如下：

奇怪的是为什么生产系统不判断index（max/min）的cost

难道某些参数有问题还是版本的问题？生产库是10.2.0.2，测试是10.2.0.4

hctech

将测试机：optimizer_features_enable=10.2.0.2
执行计划变为INDEX FAST FULL SCAN ，看来和优化版本有关了，但是为什么10.2.0.2的优化器不能生成（MAX/MIN)的方式呢~这个访问路径应该是从9I就有了的吧~~？

Maclean Liu(刘相兵

你的生产环境：

kkofmx: index filter:"ZZCD1"."EBELN"='0067031439'
  Access Path: index (skip-scan)
    SS sel: 2.0497e-06  ANDV (#skips): 702586
    SS io: 702586.00 vs. index scan io: 13103.00
    Skip Scan rejected
  Access Path: index (IndexOnly)
    Index: ZZCD1~0
    resc_io: 13105.00  resc_cpu: 538999471
    ix_sel: 0.5  ix_sel_with_filters: 2.0497e-06
    Cost: 2635.01  Resp: 2635.01  Degree: 1
  Best:: AccessPath: IndexRange  Index: ZZCD1~0
         Cost: 2635.01  Degree: 1  Resp: 2635.01  Card: 9.14  Bytes: 0


没有产生Access Path: index (Min/Max)  相关的执行路径

且 没有问题的环境 10.2.0.4
     有问题的环境      10.2.0.2


尝试在问题环境执行以下加入SQL的语句 并生成10053 trace:

SELECT /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPT_PARAM('optimizer_index_cost_adj' 20)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "ZZCD1"@"SEL$1" ("ZZCD1"."MANDT" "ZZCD1"."ZNO" "ZZCD1"."EBELN" "ZZCD1"."EBELP"))
    END_OUTLINE_DATA
  */
MAX("ZNO")
  FROM sapsr3."ZZCD1"
WHERE "MANDT" = '800'
   AND "EBELN" = '0067031439';

hctech

10053如下，好像并没有改变

Maclean Liu(刘相兵

你显示 设置了   optimizer_features_enable           = 10.2.0.1 ， 去掉试试

      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      OPT_PARAM('query_rewrite_enabled' 'false')
      OPT_PARAM('_optim_peek_user_binds' 'false')
          OPT_PARAM('star_transformation_enabled' 'true')

把这几个设置的参数也去掉， 是指该系统设置的实例参数

hctech

生产系统，不敢这样弄，后果不堪设想
我在测试系统模仿生产系统几个非默认的参数以及您提出的这几个参数设置，均无影响，
只有修改optimizer_features_enable执行计划才会改变，但是不管是10.2.0.1,10.2.0.2,10.2.0.3都是走的INDEX FAST FULL SCAN，10.2.0.4才会MAX/MIN。

Maclean Liu(刘相兵

不需要再 system级别修改 ， 在 session级别 alter session即可 ， 这几个都是 优化器参数 在session级别可以随意修改

hctech

进过测试，执行计划没有任何改变，实在是郁闷了

汇报个情况，主键索引是ZZCD1~1：MANDT,ZNO,EBELN,EBELP
而实际上当使用mandt，zno，ebeln的组合是有重复值的，这3个字段的组合每种对应的EBELP都有3-10个左右的值，那么是否是因为10.2.0.4之前的优化器认为这种数据的结构不能使用index scan max/min，而到了10.2.0.4才开始可以为这种数据结构进行优化的查询。

谢谢刘大了~

hctech

最终情况：
发现两套正式的SAP系统中有一套是10.2.0.4的（之前没发现），除了版本不一样其它配置应该基本相同（都是SAP默认配置）。
于是在上面查看该SQL的执行计划，发现是正常的INDEX SCAN MAX/MIN，现在看来应该确实是跟优化器的版本有关系了。

hctech

调优完成，针对
SELECT MAX( "ZNO" ) FROM "ZZCD1" WHERE "MANDT" = :A0 AND "EBELN" = :A1
这个SQL，建立索引(mandt,ebeln,zno desc)，以zno降序排列，得出的执行计划cost为1~
了结一桩心事~

kevinlin.ora

zno升序不行吗

fufuh2o

没看懂~~~对比2边的执行环境,统计信息10053 中可看参数