Non optimum index selected for a select on BPCG

[benoit-schmid]

Good morning,

On my DEV system, I have a select that goes very fast.

The query plan is the following.

SQL Statement

-

---

SELECT

/*+

FIRST_ROWS (1)

*/

*

FROM

"BPCG"

WHERE

"MANDT" = :A0 AND "WRTTP" = :A1 AND "GEBER" = :A2 AND ROWNUM <= :A3

Execution Plan

-

---

System: DEV

-

---

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

-

---

0	SELECT STATEMENT		1	37	2 (50)
1	COUNT STOPKEY
2	TABLE ACCESS BY INDEX ROWID	BPCG	1	37	1 (0)
3	INDEX RANGE SCAN	BPCG~1	1241		1 (0)

-

---

Predicate Information (identified by operation id):

-

---

1 - filter(ROWNUM<=TO_NUMBER(:A3))

2 - filter("WRTTP"=:A1)

3 - access("GEBER"=:A2 AND "MANDT"=:A0)

On my TST system it is very slow because it is using a non optimum index.

SQL Statement

-

---

SELECT

/*+

FIRST_ROWS (1)

*/

*

FROM

"BPCG"

WHERE

"MANDT" = :A0 AND "WRTTP" = :A1 AND "GEBER" = :A2 AND ROWNUM <= :A3

Execution Plan

-

---

System: TST

Plan hash value: 1280127048

-

---

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

-

---

0	SELECT STATEMENT		1	36	36 (3)	00:00:01
1	COUNT STOPKEY
2	INDEX RANGE SCAN	BPCG~0	1	36	35 (0)	00:00:01

-

---

Query Block Name / Object Alias (identified by operation id):

-

---

1 - SEL$1

2 - SEL$1 / BPCG@SEL$1

Predicate Information (identified by operation id):

-

---

1 - filter(ROWNUM<=TO_NUMBER(:A3))

2 - access("MANDT"=:A0 AND "WRTTP"=:A1 AND "GEBER"=:A2)

filter("GEBER"=:A2 AND "WRTTP"=:A1)

Column Projection Information (identified by operation id):

-

---

1 - "MANDT"[VARCHAR2,3], "BPCG"."OBJNR"[VARCHAR2,22],

"BPCG"."POSIT"[VARCHAR2,8], "BPCG"."TRGKZ"[VARCHAR2,1],

"WRTTP"[VARCHAR2,2], "GEBER"[VARCHAR2,10]

2 - "MANDT"[VARCHAR2,3], "BPCG"."OBJNR"[VARCHAR2,22],

"BPCG"."POSIT"[VARCHAR2,8], "BPCG"."TRGKZ"[VARCHAR2,1],

"WRTTP"[VARCHAR2,2], "GEBER"[VARCHAR2,10]

1. Would you know why there is this difference in behaviour?

2. Would you know how I could make it take the optimum index (~1) on my TST system?

Thanks in advance for your answer.

[Former Member · Accepted solution]

Hi,

There are many reasons may cause to choose different access paths to the same sql statement. Index selectivity process is based on CBO statistics. So, CBO use these values, while finding correct access path to the required data, during the process. Under these circumstance, you can check "Check and update optimizer statistics" job, in DB13 whether it is running without any problem or not. Secondly, check db index quality of the in questioned tables, by using DB20 t-code.

Additionaly, you use Oracle hints while accessing the data. But, you can do this in native sql block, not open SQL. Under this circumstance, I should indicate that SAP table buffers will not be used and the system will get data from database directly.

Best regards,

Orkun Gedik

[benoit-schmid · Accepted solution]

Hello,

I have had the same problem with fmifiit table.

It is bug 7434973 from Oracle.

SQL> select index_name, distinct_keys from dba_indexes where owner='SAPPRD' and index_name like '%BPCG%' ;

INDEX_NAME DISTINCT_KEYS

-

---

-

---

BPCG~0 14271934

BPCG~1 0

Would I have a way to tell sap to use ~1 index instead of ~o whithout having to rollback Oracle patch 7434973.

Thanks in advance for your help.

[Former Member · Accepted solution]

Hi,

If you think that you hit the Oracle bug, I recommend that you open a ticket to OSS and ask for it. Otherwise, I don't think that you should use some workarounds, something like using Oracle hints, instead of finding the root cause.

Best regards,

Orkun Gedik

[benoit-schmid · Accepted solution]

Hello,

I had allready opened an OSS call for this problem on fmifiit.

They said that I should backport this Oracle patch and they also said that I could continue to work like this because this bug is rare.

Backporting patches to this db is always risky.

Therefore I would like to know if I have a way to tell sap to use the other index.

To avoid the bug, I have run the stats with a compute instead of an estimate.

But Oracle still uses the non optimum index.

Therefore I would like to know how I could tell sap to use the optimum one.

Regards,

[Former Member · Accepted solution]

Does the ~1 index really have 0 distinct keys? How is it on the DEV system?

Cheers Michael

[benoit-schmid · Accepted solution]

Hello,

In it does not have 0 distinct key.

But now, in TST it does not have 0 distinct key because I have run a compute instead of estimate on this index.

This has bypassed the Oracle bug because it occurs only on estimate (not on compute).

But even so it is not 0 in TST, Oracle still use ~0 index instead of ~1 index.

How could I tell Sap to use ~1 instead of ~0.

Thanks in advance for your answer.

[Former Member · Accepted solution]

Well, given if the stats are correct now, and still the wrong index is chosen this still leaves a few possibilities open:

- the data is different, than on the DEV system (for example DEV has several clients while TST has only one) or TST has millions of rows while DEV only has a few

- the database parameters (especially opti* ) or patches are not the same (optimizer patch)

As Orkun mentioned possible workarounds are to either fake the table stats, you could even copy the stats from the DEV system. See note [724545 - Adjusting the CBO statistics manually using DBMS_STATS|https://service.sap.com/sap/support/notes/724545] for the details. Or you could try a hint like /*+ INDEX(BPCG,"BPCG~1" */

Cheers Michael

[benoit-schmid · Accepted solution]

Hello Michael,

The data are different, but it is in the same order.

I will try to post on the forum, but I have problems with the format.

Thanks for your answer

[benoit-schmid · Accepted solution]

DEV (Fast / Using ~1)

-

---

Table BPCG

-

---

Last statistics date 22.11.2010

Analyze Method Sample 287,824 Rows

Number of rows 9,594,133

Number of blocks allocated 55,758

Number of empty blocks 0

Average space 0

Chain count 0

Average row length 37

Partitioned NO

-

---

-

---

NONUNIQUE Index BPCG~1

-

---

Column Name #Distinct

-

---

GEBER 7,728

MANDT 1

OBJNR 953

-

---

Last statistics date 22.11.2010

Analyze Method Sample 276,701 Rows

Levels of B-Tree 2

Number of leaf blocks 43,800

Number of distinct keys 7,728

Average leaf blocks per key 18

Average data blocks per key 2,760

Clustering factor 6,414,933

-

---

-

---

UNIQUE Index BPCG~0

-

---

Column Name #Distinct

-

---

MANDT 1

OBJNR 953

POSIT 347

TRGKZ 1

WRTTP 2

GEBER 7,728

-

---

Last statistics date 22.11.2010

Analyze Method Sample 292,082 Rows

Levels of B-Tree 3

Number of leaf blocks 65,867

Number of distinct keys 9,736,067

Average leaf blocks per key 1

Average data blocks per key 1

Clustering factor 192,200

-

---

[benoit-schmid · Accepted solution]

TST (Slow using ~1)

-

---

Table BPCG

-

---

Last statistics date 08.12.2010

Analyze Method mple 14,271,934 Rows

Number of rows 14,271,934

Number of blocks allocated 82,661

Number of empty blocks 0

Average space 0

Chain count 0

Average row length 36

Partitioned NO

-

---

-

---

UNIQUE Index BPCG~0

-

---

Column Name #Distinct

-

---

MANDT 1

OBJNR 1,083

POSIT 354

TRGKZ 1

WRTTP 2

GEBER 10,798

-

---

Last statistics date 08.12.2010

Analyze Method mple 14,271,934 Rows

Levels of B-Tree 3

Number of leaf blocks 114,756

Number of distinct keys 14,271,934

Average leaf blocks per key 1

Average data blocks per key 1

Clustering factor 3,186,276

-

---

-

---

NONUNIQUE Index BPCG~1

-

---

Column Name #Distinct

-

---

GEBER 10,798

MANDT 1

OBJNR 1,083

-

---

Last statistics date 08.12.2010

Analyze Method mple 14,271,934 Rows

Levels of B-Tree 3

Number of leaf blocks 74,613

Number of distinct keys 52,474

Average leaf blocks per key 1

Average data blocks per key 159

Clustering factor 8,359,365

-

---

[benoit-schmid · Accepted solution]

Hello Michael,

>

> Well, given if the stats are correct now, and still the wrong index is chosen this still leaves a few possibilities open:

>

> - the data is different, than on the DEV system (for example DEV has several clients while TST has only one) or TST has millions of rows while DEV only has a few

> - the database parameters (especially opti* ) or patches are not the same (optimizer patch)

>

For that I have, provided the details.

First question:

If some has an idea on why it uses ~0 in TST, I am interested.

> As Orkun mentioned possible workarounds are to either fake the table stats, you could even copy the stats from the DEV system. See note [724545 - Adjusting the CBO statistics manually using DBMS_STATS|https://service.sap.com/sap/support/notes/724545] for the details. Or you could try a hint like /*+ INDEX(BPCG,"BPCG~1" */

Thanks for Note 724545.

It recommends

1. Create more exact -> This is what I have done and it has not been succesfull

2. Create histograms -> Tested, but it does not help as we are using bind variables

3. Specify Hint -> I have to ask my developper

Second question:

Can I use a hint in open sql abap program or is it mandatory to use native oracle sql abap call ?

I have tried to use you hint in st05 it seems to work, but it does not use bind variables.

Therefore I need to test with my developers.

Thanks in advance for you answer to my first or second questions.

[Former Member · Accepted solution]

Can I use a hint in open sql abap program

Absolutely, the syntax is something like:

select * from bpcg
appending table lt_bpcg
where ...
%_hints oracle
'INDEX("&TABLE&" "BPCG~1")'

You can replace the literals in ST05 with :A0 etc, no problem. If i find time i try to check your stats and try to find an explanation for the wrong plan, at first glance the only thing i can imagine is a CBO bug.

Cheers Michael

[benoit-schmid · Accepted solution]

Hello Michael,

I have tried the open sql hint in st05 explain hint.

But I got an error with clicking on explan of st05 with the following query:

select * from BPCG where wrttp in '43' and geber in 'DIP'

%_hints oracle

'INDEX("BPCG" "BPCG~1")'

1. What am I doing wrong?

2. Could you please tell me how to run with bind variables the following query in st05 "Enter SQL Statement"?

select

/*+ INDEX(BPCG,"BPCG~1" */

• from bpcg where wrttp in '43' and geber in 'DIP'

3, How can you test on your site with my stats?

Thanks in advance for your answer.

[Former Member · Accepted solution]

You got me wrong, you cannot use %_hint in ST05, only in abap.

2. just take the original statement you posted at the beginning (i exchanged FIRST_ROWS with the INDEX hint):

SELECT /*+ INDEX(BPCG,"BPCG~1" */ * FROM

"BPCG" WHERE "MANDT" = :A0 AND "WRTTP" = :A1 AND "GEBER" = :A2 AND ROWNUM <= :A3

Do not use IN when there is only one value.

3. I don't test with your stats, i don't know your database parameters and exact software release. It would be pointless. All i do is switch my brain in CBO mode ))

Cheers Michael