Explain Plain with access and filtre

[Former Member]

Hi;

I have two sand box identicals systems, with the samme database and the sammes "z" programs.

If execute the next select in one system the results is ok, but if execute in the other the select take a long time and the CPU is in exclusive for the select. The direrence is that in the good system i have the next access;

1 - filter(ROWNUM<=:A2)

3 - access("MANDT"=:A0 AND "ZZCOD_PARQUE"=:A1)

In the bad system i have ;

1 - filter(ROWNUM<=:A2)

3 - access("MANDT"=:A0 AND "ZZCOD_PARQUE"=:A1)

filter("ZZCOD_PARQUE"=:A1)

The select is;

SELECT /*+ FIRST_ROWS (1) */ "OBJNR" FROM "IFLOT" WHERE "MANDT" = :A0 AND

"ZZCOD_PARQUE" = :A1 AND ROWNUM <= :A2

In the good system use the index Z02 and in the bad system use the index Z01

Index Z01;

MANDT, FLTYP,ZZCOD_PARQUE

Index Z02;

MANDT,ZZCOD_PARQUE

Because Use different indexes if the systems are indenticals? and because in the bad system use the filter ZZCOD_PARQUE?

Best regards

[former_member524429 · Accepted solution]

Hi,

Please check that the Table IFLOT is having up-to-date Table Statistics in the affected System. If not, then you can execute RSANAORA report using SE38 and run "Compute statistic" for the affected Table(s).

If Update of Table Statistics does not help then perform SQL Trace using ST05 and perform diagnosis along with the Developer.

Because Use different indexes if the systems are indenticals?

Both systems are having same number of Indexes with same tables fields ?

Both systems are having same H/W configuration, SAP/DB parameter configurations ?

The selection of Index also depends on the Clustering Factor ratio of the indexes in Database.

Regards,

Bhavik G. Shroff

[Former Member]

Hi;

Thanks a lot for the reply.

The tow tests is in the samme system, but with different way of make the first record.

The optimizer merge fix, i Think is good idea, and check the parameters also. I will do new test.

Thanks snd best regards

Edited by: Daniel Aguirre Vallés on Nov 25, 2010 4:53 PM

[Former Member]

Hi;

Thanks to all.

I attached the reports.

http://www.megaupload.com/?d=Y5VHSER0

[fidel_vales]

Hi,

you only attached information for one system.

Therefore, it is not possible to explain why the same query uses different indexes on the different systems.

The information you provide if for the query with and without "FIRST ROWS"

First to note that both queries are different. therefore it is "normal" that the CBO makes a different decision.

In one Oracle has to optimize to return ONE row as fast as possible, in the other Oracle has to optimize to return ALL rows as fast as possible.

that simple fact can make the Optimizer make a different decision.

In this case is making a "wrong" decision, and I would bet it is because you are hitting one of the "FIRST_ROWS" bugs solved in the Optimizer merge fix as the one you have installed is from 2008.

My advice,

- if you want to get an answer why on both systems the CBO behaves differently, provide information about both systems

- if you want to improve the performance in your system, read the output of the parameter check that you have provided (parametros.txt). You have quite a high number of parameters related to performance that do not follow SAP recommendations (SAP Note 830576)

[Former Member]

Hi;

I have the solution but i not undertard because........

This is the bad select; Use the index Z01 and very use of CPU. Time 1400 Seconds.

SELECT SINGLE objnr

FROM iflot

INTO v_objnr_ut

WHERE zzcod_parque = v_pspid_ut.

With the new select; Use the index Z02 and the time is 400 seconds.

SELECT objnr

FROM iflot

INTO v_objnr_ut

WHERE zzcod_parque = v_pspid_ut.

exit.

endselect.

Any idea because is better the second select?

[stefan_koehler]

Hello Daniel,

 Any idea because is better the second select?

If you don't specify the ABAP keyword "SINGLE" in your SQL .. the DBSL does not insert a "FIRST_ROWS (1)" hint.

In this case the behaviour of the oracle database optimizer is different.

Regards

Stefan

[volker_borowski2]

Well,

the index without FLTYP is better, because your statement makes no uses of that field.

On the other side I have no idea, why, esp. with a first_rows hint it is taking that index with FLTYP.

Is it doing a RANGE or a SKIP scan in the worse case ?

Can you check the DB parameters in sqlplus on both systems?

show parameter opti

would be a good start.

Do you have diffrent bugfixes on both systems?

Esp. diffrent optimizer-merge-fixes can do such strange things.

compare "opatch lsinventory" on both systems if they a really identical.

please check on both systems in sqlplus, if the output of

desc your_tablename

is the same.

Volker

[fidel_vales]

Hi,

Please, do the following on both systems

1) find the SQL_ID of the query (has to be the same in both system)

2) execute the sql_id collector from SAP Note 1257075

3) execute the parameter check script from SAP Note 1171650

4) put the output of all those files into a zip file and upload it somewhere (do not paste it here, it is too much for this forum)

5) provide the link to such information

[Former Member]

Hi;

I have one test. If use Select single the index is Z01 (bad), if use select (normal) the index is Z02 (good). But the strange is that with select single in one system work fine and in the other use the index Z01.

NONUNIQUE Index IFLOT~Z01

Column Name #Distinct

MANDT 1

FLTYP 9

ZZCOD_PARQUE 680

Last statistics date 23.11.2010

Analyze Method ample 1.065.584 Rows

Levels of B-Tree 2

Number of leaf blocks 2.859

Number of distinct keys 1.362

Average leaf blocks per key 2

Average data blocks per key 57

Clustering factor 77.935

NONUNIQUE Index IFLOT~Z02

Column Name #Distinct

MANDT 1

ZZCOD_PARQUE 680

Last statistics date 23.11.2010

Analyze Method ample 1.065.584 Rows

Levels of B-Tree 2

Number of leaf blocks 2.563

Number of distinct keys 681

Average leaf blocks per key 3

Average data blocks per key 106

Clustering factor 72.364

[Former Member]

Hi; Thanks for yo reply

I have execute the statistics of the IFLOT table in the two systems and not resolve the problem.

The tables is identical and have the sammes indexes.

The hardware is the samme and the parameters of Oracle.

Best regards

[Former Member]

Hi,

Does the table/index has the same "Analyze method" and number of distinct keys when you select the object in the explain plan?

Could be that it's being calculated with another value...

Kind regards,

Mark