Oracle Optimizer Developer Question

[Former Member]

Hi All,

I am from the Abap side trying to figure out the following issue we have.

If I go to transaction ST05, and explain he following SQL statements I get the results as shown below.

Any help or suggestions greatly appreciated.

We have this problem in our Prod environment, but not in QA.

-

---

SELECT * FROM "VBAK" WHERE "MANDT" = :A0 AND "AUFNR" = :A1

This will do a sequential table access

SELECT * FROM "VBAK" WHERE "MANDT" = '500' AND "AUFNR" = '000475151026'

This will use a custom index that was created.

-

---

Index Z01 contains the fields MANDT and AUFNR.

Also, the explain shows for the index access - "ACCESS PREDICATES" but for the full table access it shows - "FILTER PREDICATES"

[stefan_koehler · Accepted solution]

Hello Neelan,

> Also, the explain shows for the index access - "ACCESS PREDICATES" but for the full table access it shows - "FILTER PREDICATES"

This is a semantic/logical issue.. oracle can "ACCESS" the index with the values of MANDT and AUFNR to get the corresponding rows (ROWIDs).. but if you perform a full table scan oracle can only "FILTER" the values after/while reading all data.. there is no "way to access".

There is a nice article on hotsos for that: http://support.hotsos.com/default.asp?hotsos.11.552.2

> SELECT * FROM "VBAK" WHERE "MANDT" = :A0 AND "AUFNR" = :A1

> SELECT * FROM "VBAK" WHERE "MANDT" = '500' AND "AUFNR" = '000475151026'

At first you need to know that these statements are two different ones (wit the SAP standard database settings).

We need to know more about your system:

- Oracle version?

- Are histograms collected for the columns of the table VBAK?

- How does the statistic look like for the table and the index Z01

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan,

Thanks for the reply.

> > SELECT * FROM "VBAK" WHERE "MANDT" = :A0 AND "AUFNR" = :A1

> > SELECT * FROM "VBAK" WHERE "MANDT" = '500' AND "AUFNR" = '000475151026'

> At first you need to know that these statements are two different ones (wit the SAP standard database settings).

What do you mean by two different statements?

What I forgot to mention is that the binding parameters A0 has the value "500" and A1 the value "000475151026"

Thus from my point of view, the 2 statements are the same.

This is from the SQL trace (ST05) where the actual SQL statement is invoked, in SAP standard code.

The VERY slow responce (On a Copy of Production):

|Duration |Obj. na |Op. |Recs.|RC |Statement

|--

---

283|VBAK |PREPARE|---|---0|SELECT WHERE "MANDT" = :A0 AND "AUFNR" = :A1

|--

---

3|VBAK |OPEN--|---|---0|SELECT WHERE "MANDT" = '500' AND "AUFNR" = '000475151026'

| 141345597 |VBAK |FETCH---|---0|1403|

The QA box:

|Duration |Obj. na |Op. |Recs.|RC |Statement

|--

---

229|VBAK |PREPARE|---|---0|SELECT WHERE "MANDT" = :A0 AND "AUFNR" = :A1

|--

---

3|VBAK |OPEN--|---|---0|SELECT WHERE "MANDT" = '500' AND "AUFNR" = '000475151026'

| ----

---

9,732 |VBAK |FETCH---|---0|1403|

And here is what I could get from one of the DBA's:

> - Oracle version?

10.2.0.2.0

> - Are histograms collected for the columns of the table VBAK?

Yes, they are collected because the cascade option that we are using.

> - How does the statistic look like for the table and the index Z01

I'm still waiting for this answer...

Cheers,

[fidel_vales · Accepted solution]

Hi Neelan:

> Thus from my point of view, the 2 statements are the same.

But they are not.

In one you use bind variables and on the other you use fixed values. Both are different and the CBO will behave different in both cases.

When you use bind variables, histograms are not of much use for the optimizer, for example.

So, if you want to test something you must use exactly the same syntax as SAP (bind variables)

> Yes, they are collected because the cascade option that we are using.

O_O

sorry I do not get this, could you rephrase it?

Nevertheless, bind variables are being used, so no much use for histograms (now)

Ideally, I'd like to see the statistics of the table and all indexes (you know, in the explain plan, when you click on the table and then the button "index stats")

And wounderfull would be to get that info for both systems.

[stefan_koehler · Accepted solution]

Hello Neelan,

> What do you mean by two different statements?

> What I forgot to mention is that the binding parameters A0 has the value "500" and A1 the value "000475151026"

In a SAP environment the bind peeking is disabled (_OPTIM_PEEK_USER_BINDS=FALSE) and the values behind the bind variables are not interpreted. So the both statements are returning the same result set, but for the database optimizer these are two different statements (logical and syntactical). So it doesn't matter that the value behind the bind variables are the same as the literal ones.

> Yes, they are collected because the cascade option that we are using.

Ok and in this case it makes sense to me, that you get another execution plan when using literals instead of bind variables. With histograms oracle knows more about the "data distribution" (different values) of columns and maybe can calculate better execution plans like in your case. But if you are using bind variables oracle does not know which value should be compared with this information and so you haven't any benefit with that.

Regards

Stefan

[Former Member · Accepted solution]

Hi Fidel,

Thanks for the reply.

> > Thus from my point of view, the 2 statements are the same.

> But they are not.

> In one you use bind variables and on the other you use fixed values. Both are different and the CBO will behave different in both cases.

> When you use bind variables, histograms are not of much use for the optimizer, for example.

> So, if you want to test something you must use exactly the same syntax as SAP (bind variables)

From where I'm coming, there's no option to use binding variables or not...

I have a select statement in abap code. SAP then translates that into the SQL statement with the binding variables. And then Oracle does it's magic...

> > Yes, they are collected because the cascade option that we are using.

> O_O

> sorry I do not get this, could you rephrase it?

Sorry, but as I stated previously, this is the gargled reply I got from the DBA. I cannot explain it any more even if life depended on it.

> Ideally, I'd like to see the statistics of the table and all indexes (you know, in the explain plan, when you click on the table and then the button "index stats")

> And wounderfull would be to get that info for both systems.

Here is the statistics from ST05(I only include the table stats, prim index, and index in question):

QA system (the one that is working)

Table VBAK

Last statistics date 08.08.2008

Analyze Method ample 5,334,244 Rows

Number of rows 5,334,244

Number of blocks allocated 383,560

Number of empty blocks 0

Average space 0

Chain count 0

Average row length 531

Partitioned NO

UNIQUE Index VBAK~0

Column Name #Distinct

MANDT 4

VBELN 5,286,334

Last statistics date 08.08.2008

Analyze Method ample 5,334,244 Rows

Levels of B-Tree 2

Number of leaf blocks 18,763

Number of distinct keys 5,334,244

Average leaf blocks per key 1

Average data blocks per key 1

Clustering factor 2,284,050

NONUNIQUE Index VBAK~Z01

Column Name #Distinct

MANDT 4

AUFNR 1

Last statistics date 08.08.2008

Analyze Method ample 5,334,244 Rows

Levels of B-Tree 2

Number of leaf blocks 12,759

Number of distinct keys 4

Average leaf blocks per key 3,189

Average data blocks per key 95,822

Clustering factor 383,290

-

---

Prod system (the one that is not working)

Table VBAK

Last statistics date 26.08.2008

Analyze Method Sample 799,072 Rows

Number of rows 7,990,720

Number of blocks allocated 589,692

Number of empty blocks 0

Average space 0

Chain count 0

Average row length 539

Partitioned NO

UNIQUE Index VBAK~0

Column Name #Distinct

MANDT 1

VBELN 7,990,720

Last statistics date 26.08.2008

Analyze Method Sample 821,350 Rows

Levels of B-Tree 2

Number of leaf blocks 28,620

Number of distinct keys 8,213,500

Average leaf blocks per key 1

Average data blocks per key 1

Clustering factor 4,454,510

NONUNIQUE Index VBAK~Z01

Column Name #Distinct

MANDT 1

AUFNR 1

Last statistics date 26.08.2008

Analyze Method Sample 816,570 Rows

Levels of B-Tree 2

Number of leaf blocks 19,350

Number of distinct keys 1

Average leaf blocks per key 19,350

Average data blocks per key 610,760

Clustering factor 610,760

[Former Member · Accepted solution]

Hi Stefan,

Thanks for the reply.

I have forwarded it to our Basis guy. I'll give you feedback asap.

[Former Member]

The problem seems to be that AUFNR has only 1 distinct value. As a consequence the CBO assumes that all records fulfill the AUFNR condition and so the index may not be picked (also dependent on other factors like system statistics). Is it really the case that there is only 1 distinct value (initial?) for AUFNR? I assume that earlier or later different AUFNR values will exist, then the problem will be gone. For the moment you could adapt the CBO statistics according to SAP note 724545 by increasing DISTCNT for this column to e.g. 10.

Regards

Martin

[Former Member]

Hi Martin,

Thanks for that info.

I went and checked both systems.

I was shocked to find there are NO values at all in that field.

Thus the one distinct key is space...

But my question is still, why is it very vast in the one system, and very slow in the other.

I'll let basis have a look at the note you suggest.

Thanks.

[Former Member]

In this "extreme" scenario small differences in CBO statistics, system statistics or Oracle parameters can lead to a different CBO decision. Usually we should always expect a full table scan in this kind of situation but in SAP environments the CBO is configured rather index friendly and so it is possible to see also an index access. I do not think that it is worth to spend time in analyzing the difference. If you want to do it, you have to consider note 750631 in order to understand the CBO cost calculation.

Regards

Martin

[Former Member]

The difference is obvious: the QAS system has 4 values on the mandt column, that is why the CBO uses the index. And because there is probably no row returned, the index access works very well...

Indexes containing fields with only one value are bad, unless you select a value which does not exist at all!

Regards

Michael