topic Re: SQL Trace Analysis in Application Development and Automation Discussions

SQL Trace Analysis

Former Member — Mon, 02 Jun 2008 10:06:51 GMT

This is the SELECT statement:

SELECT DISTINCT vbak~vbeln

vbkd~zzvextzau

vbak~auart

INTO TABLE zauth_itab

FROM vbak

INNER JOIN vbap

ON vbap_{mandt = vbak}mandt

AND vbap_{vbeln = vbak}vbeln

INNER JOIN vbup

ON vbup_{mandt = vbak}mandt

AND vbup_{vbeln = vbak}vbeln

INNER JOIN vbkd

ON vbkd_{mandt = vbak}mandt

AND vbkd_{vbeln = vbak}vbeln

AND vbkd~posnr = 0

WHERE vbak~vbeln IN s_vbeln

AND vbap~matnr IN s_matnr

AND vbap~kondm IN s_kondm

AND vbak~auart IN s_auart

AND vbak~vkorg IN s_vkorg

AND vbak~guebg IN r_guebg

AND ( vbak~gueen > sy-datum OR

vbak~gueen = 0 )

AND vbup~rfsta EQ 'C'.

Why is there a difference in the execution flow between the dev't box and sys test box? The code is exactly the same and it is the same Transport Request.

I ran SQL Trace (Trans ST05) for both boxes and found a difference:

Why is it that in the TEST BOX, the first exec was TABLE ACCESS FULL but in the DEV BOX it is INDEX RANGE SCAN?

Result of Execution Flow (from SQL Trace ST05) in System Test Box:

SELECT STATEMENT ( Estimated Costs = 72,542 , Estimated #Rows = 568 )

- 11 SORT UNIQUE

( Estim. Costs = 72,542 , Estim. #Rows = 568 )

- 10 FILTER

- 9 NESTED LOOPS

- 7 NESTED LOOPS

- 4 NESTED LOOPS

- 1 TABLE ACCESS FULL VBAK

- 3 TABLE ACCESS BY INDEX ROWID VBKD

- 2 INDEX UNIQUE SCAN VBKD~0

- 6 TABLE ACCESS BY INDEX ROWID VBUP

- 5 INDEX RANGE SCAN VBUP~0

- 8 INDEX RANGE SCAN VBAP~0

Result of Execution Flow (from SQL Trace ST05) in Development Box:

SELECT STATEMENT ( Estimated Costs = 38 , Estimated #Rows = 2 )

- 12 SORT UNIQUE

( Estim. Costs = 38 , Estim. #Rows = 2 )

Estim. CPU-Costs = 4 Estim. IO-Costs = 38

- 11 FILTER

- 10 NESTED LOOPS

- 8 NESTED LOOPS

- 5 NESTED LOOPS

- 2 TABLE ACCESS BY INDEX ROWID VBAK

- 1 INDEX RANGE SCAN VBKAK~Z05

- 4 TABLE ACCESS BY INDEX ROWID VBKD

- 3 INDEX UNIQUE SCAN VBKD~0

- 7 TABLE ACCESS BY INDEX ROWID VBUP

- 6 INDEX RANGE SCAN VBUP~0

- 9 INDEX RANGE SCAN VBAP~0

Re: SQL Trace Analysis

Former Member — Mon, 02 Jun 2008 10:27:59 GMT

hI,

Index is picked by the optimiser at the runtime it is not possible to know which one will be picked until runtime.

In your case, the development system u have Z05 index which is partially or fully satisfying your WHERE condition.

Check for the index in ur test system. if u want , u can force the optimiser to pick the index u want using %_oracle hints.

pls avoid SELECT DISTINCT.

regards,

madhu

Re: SQL Trace Analysis

Former Member — Mon, 02 Jun 2008 10:43:54 GMT

to explain the difference is very simple here::

It is this information

( Estim. Costs = 72,542 , Estim. #Rows = 568 )

( Estimated Costs = 38 , Estimated #Rows = 2 )

The optimizer expects much higher costs to determine the the rows you want and it expects much higher numbers of rows coming back.

First you should check whether the statistics of your test system are up-to-date.

See explain, double click on table U(usually with blue background) then you get statistcial information, about selectivity and with a date.

Check whether this data are recently updated!

Try again, maybe you will get no full table scan up.

Otherwise add index hint and try again.

Siegfried

Re: SQL Trace Analysis

Former Member — Mon, 02 Jun 2008 11:09:57 GMT

Thanks both!

I was wondering if the points were added? I tried to reward both of you points. Thank you so much! I hope the points were rewarded.

Che