Explain from v$sql_plan to possible

[Former Member]

Hi folks, I've upgrade our ERP system to Oracle 10.2.0.4 / Unix Solaris 10. We've been running 10G in our development and test environment for over 1 year without issue. Problem is now in our production system there's lots of Sequential Read's as seen in transaction SM50.

Looking at one query in question via transaction ST04 -> detailed analysis menu -> Oracle Session then double clicking on the session I see

SELECT (whatever) from MSEG where MANDT= AND (CHARG LIKE OR CHARG LIKE) and LGORT=something.

When I click on the "Display execution plan" button I see in the report under "Execution Plan" :

Explain from v$sql_plan not possible, Explain from PLAN_TABLE is displayed.

Why is this not possible?

Also, this select statement as seen from transaction ST04 is showing "INDEX SKIP SCAN MSEG~M" followed by a Table access by index rowid.

Index MSEG~M is on columns MANDT,MATNR,WEKS,LGORT,BWART,SOBKZ. This isn't ideal query plan.

However, at the database level via sqlplus, if i connect as the sapprd user and truncate the plan table, then run

Explain plan for

SELECT *

FROM

MSEG

WHERE

MANDT='090'

AND (CHARG like 'ABC' or CHARG like 'BCd' or charg like 'EFF') and LGORT ='ABC'

;

Followed by @/oracle/PRD/102_64/rdbms/admin/utlxpls.sql

I see the correct index is indeed chosen by the optimizer, and that's a custom index named Z1 on table MSEG, columns mandt,charg,bwart

The overall COST %CPU, Number of rows is much lower on index MSEG_{Z1 than index MSEG}M.

So where is transaction ST04 getting this information? Thanks

[fidel_vales]

Hi,

Lets go slowly.

First lets take a look at the documentation (as usual, the first place to look for something):

> V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.

Now we know what V$SQL_PLAN plan is, it is very easy to know why SAP cannot retrieve the "plan" information from this view. It is not there any more, it has been displaced/replaced from the library cache. Therefore, instead will perform a "normal" explain.

Also, keep in mind the meaning of "sequential reads" in SM50 and/or SM66

> Index MSEG~M is on columns MANDT,MATNR,WEKS,LGORT,BWART,SOBKZ. This isn't ideal query plan.

It is not ideal for you, but not for Oracle, clearly

Unfortunately, without knowing all details (parameters, patches, statistics, other indexes, etc) anything would be a pure guess.

> However, at the database level via sqlplus

This is a typical/common mistake. At SQLPLUS you have NOT executed the same query. I leave to you to think on this statement. Please, give me your thoughts and I'll tell you if you are correct.

Finaly, the index that you mention is quite bad for the query stated:

> SELECT (whatever) from MSEG where MANDT= AND (CHARG LIKE OR CHARG LIKE ) and LGORT = something.

The index should be MANDT LGORT CHARG (may be also whatever depending how big it is)

[stefan_koehler]

Hello Fidel,

> This is a typical/common mistake. At SQLPLUS you have NOT executed the same query. I leave to you to think on this statement

I am thinking about it too and can not find out what you are meaning. If you execute the query with bind variables in SQLPlus you should get the same execution plan. Of course SQLPlus has some different fetching mechanism (array buffer size, and so on) .. but this should have no influence on the execution plan itself.

Maybe you tell us about your thoughts.

Regards

Stefan

[fidel_vales]

Hi Stefan,

> If you execute the query with bind variables in SQL*Plus you should get the same execution plan.

You are correct, but the common mistake I find a lot of times (and here) is that the execution is not done at SQLPLUS with bind variables.

this is the query from the OP:

 
Explain plan for
SELECT *
FROM
MSEG
WHERE
MANDT='090'
AND (CHARG like 'ABC' or CHARG like 'BCd' or charg like 'EFF') and LGORT ='ABC'

• No bind variables, therefore Oracle has more information

• the "LIKE" does not contain any wildcard, therefore Oracle will treat it as "=" not as "LIKE".

For those reasons the index is taken in SQLPLUS.

Again, I do not have enough information to explain why is not taken with bind variables, I'm quite sure the reason is that, in the index CHARG is in the second position. with bind variables Oracle does not known the selectivity, it "guesses" it and you have 3 "guesses" concatenated.

Putting LGORT i the second position could make oracle to choose this index, depends on its selectivity (and parameters and ...)

Of course, I could be wrong. it won't be the first nor the last time.

comments are welcomed

[Former Member]

Hi Fidel, using ST05 i was able to get the last statement executed with the real values. Thanks for the explanation hint on SM50/SM66. Looked that up again. What i have been noticing is that the last sql statement executed as seen in SQL trace was:

SELECT

CHARG,MATNR,MENGE

FROM

MSEG

WHERE

MANDT='090'

AND (CHARG like 'J637231%' or CHARG like 'J639432%' or charg like 'J640917%') and LGORT ='ASRS' and UMLGO='HDS'

;

Running explain plan at the sqlplus prompt shows a different query plan than transaction ST04. You have answered my original question.

The above statement is the last statement as seen in the ST05 trace. After I got to the end of the trace file in ST05, I ran then Unix truss command on the pid for the dialog process and it seems to go to sleep and no further reads nor memory consumption is seen in transaction SM50. Then the dialog process is restarted when it hits the rdisp/max_wprun_time.

This is having an effect on all process / queries being executed and has almost brought our plant to a halt. We have a call opened to OSS.

Thanks again

[Former Member]

Although my original question has been answered, I would like to post to everyone what the problem appears to be.

It's the Oracle initialization parameter optimpeek_user_binds=false

We noticed that i did not set this parameter in our test environment. After setting it and restarting the test instance the problem appeared.

Taking the initialization parameter out and the problem went away. To further test with this parameter, I created a query using bind variables and checking the plan table output.

sql statment was as follows:

First, alter system flush shared_pool

ALTER SESSION SET "_optim_peek_user_binds"=FALSE;

var VAR1 number;

var VAR2 char(10);

var VAR3 char(10);

exec :VAR1 := 090;

exec :VAR2 := 'J636813%';

exec :VAR3 := 'J636818%';

select CHARG,MATNR,MENGE FROM MSEG

WHERE MANDT = :VAR1 AND ( CHARG LIKE :VAR2 OR CHARG LIKE :VAR3 )

AND LGORT = 'ASRS'

AND UMLGO='HDS'

The Query never returns, i assume a full table scan is going on.

Explain plan output shows INDEX SKIP SCAN MSEG~M -> TABLE ACCESS BY INDEX ROWID

Run the same query with out doing an alter session set "_optim_peek_user_binds"=FALSE and the query returns in 2 seconds. Plan table output is the same.

Using literals WHERE MANDT = '090' AND ( CHARG LIKE 'J636813%' OR CHARG LIKE 'J636818%') with the parameter optimpeek_user_binds=FALSE set or not set in the session and the query returns in 2 seconds, plan table output is the same.

I see in metalink :

Bug 5082178 Bind peeking may occur when it should not

Bind peeking can occur for user binds even if "_optim_peek_user_binds" is set to FALSE.

But this was fixed in Oracle 10.2.0.4 which we are running. I have 27 interim patches applied after i upgraded to 10.2.0.4

So, I am wondering if anyone else running 10.2.0.4 and can reproduce this problem or is it just our environment?

[Former Member]

Forgot to mention the same SAP kernel is in our Test system as our production system 640_EX2 patch 191

and Oracle patch 10.2.0.4 and otherInterm Oracle Patches

[Former Member]

Just a guess:

Check whether index MSEG~Z1 exists in ABAP dictionary.

Maybe it is there in Oracle dictionary only?

[Former Member]

Hi Joe, the index exists in the ABAP dictionary as seen in transaction SE11.

Thanks

[Former Member]

And forgot to mention i tried the following in our Production system, thinking the Shared Cache might need to be cleaned:

Alter system flush shared_pool