oracle执行计划选择了不合适的索引

woshiyiziyuma

最新的sap ags回复：
Dear Mr. Sun,

Sorry for late reply. Your message has been forwarded to SV-BO
component to check performanc related issues.

Due to no variant for issue reproduction, and the SQL statement doesn't
exist in SQL cache anymore. I fetched the SQL from AWR and found,
in the index COEP~Z01 you mentioned as 'right index', the first column
is 'MATNR', which is not specified in where clause. That's reason
why CBO doesn't use this index and it is the normal behaviour but not a
bug.

I have noticed that you have added hint to force using index~Z01 in
ABAP coding ZCOR051. And I have found the second column of COEP~1 is
LEDNR with distinct 1. If it can be hardcoded in ABAP then index COEP~1
access should be more optimal.

I'll discuss with you later on the phone.

BR,
Alex Li
Support Consultant
SAP AGS China

我不能赞同他说的关于不走索引coep~z01的解释，他给的解释是正确的吗?
索引的相关信息：

liqy103

woshiyiziyuma 发表于 2011-9-15 23:59
索引COEP~1和COEP~Z01的信息，如图所示：

我已经分析过表，analyze table SAPSR3.COEP  compute statist ...

"我已经分析过表，analyze table SAPSR3.COEP  compute statistics；"

Note that ANALYZE command is deprecated in 10g, use DBMS_STATS instead.

Yong Huang

OK. I think you're hitting this bug
Bug 8795753: FULL INDEX SCAN NOT CHOSEN EVEN THOUGH COST IS CHEAPER

Also read
CBO Biases FTS Over The Cheaper Index Full Scan (Doc ID 953273.1)

The case in the bug is that higher-cost full table scan is chosen instead of cheaper full index scan. Your case is actually the same, except your lower optimizer_index_cost_adj masked the FTS, i.e. changed it to using the higher cost index coep~1. (Again, please change the wrong index name in your msg #8! It confuses other readers.)

The workaround is exactly what you did: use the index hint. I can't find more information about this bug.

Yong Huang

woshiyiziyuma

Yong Huang 发表于 2011-9-21 01:42
OK. I think you're hitting this bug
Bug 8795753: FULL INDEX SCAN NOT CHOSEN EVEN THOUGH COST IS CHE ...

再次感谢Yong Huang，我已经在8楼改过了。

woshiyiziyuma

我看了953273.1和8795753，但是我觉得文章里描述的bug和我的情况不太像，文章里描述的bug是全表扫描和索引全扫描，而我这里是索引范围扫描和索引全扫描。
不过我不想再纠结这个问题了，因为根据sap的建议，我在where 子句里加上了lednr = “00”，该程序的运行时间大大的缩短了，由原来的至少30分钟减小到了1分钟。

Yong Huang

> 文章里描述的bug是全表扫描和索引全扫描，而我这里是索引范围扫描和索引全扫描

You didn't read my words:
"The case in the bug is that higher-cost full table scan is chosen instead of cheaper full index scan. Your case is actually the same, except your lower optimizer_index_cost_adj masked the FTS, i.e. changed it to using the higher cost index coep~1."

Normally you should not mess with optimizer_index_cost_adj. It's a parameter to be used by beginners or people not willing to know more about Oracle's CBO.

> 运行时间大大的缩短

Adding lednr = “00” is probably good. But never base your conclusion on execution time. Check buffer gets or consistent gets.

Yong Huang

woshiyiziyuma

Yong Huang 发表于 2011-9-24 01:10
> 文章里描述的bug是全表扫描和索引全扫描，而我这里是索引范围扫描和索引全扫描

You didn't read my wo ...

Note 830576 - Parameter recommendations for Oracle 10g.pdf (147.51 KB, 下载次数: 9)

2011-9-24 08:27 上传

点击文件名下载附件

optimizer_index_cost_adj  是sap建议我们改成20的，附件是sap 的notes。

Yong Huang

> optimizer_index_cost_adj  是sap建议我们改成20的，附件是sap 的notes。

It's OK. SAP or not, messing with that parameter indicates not putting enough effort into finding the real problem. But since you're running vendor's app, you may not have a choice other than following their suggestion. Once we were told by a vendor to set cursor_sharing=similar in spite of our explanation of its bugs, its ineffectiveness and other issues. I think the DBA managing that app obliged, because the vendor may desupport us if we don't.

Yong Huang