Skip to Content
0
Former Member
Jun 02, 2008 at 10:06 AM

SQL Trace Analysis

140 Views

This is the SELECT statement:

SELECT DISTINCT vbak~vbeln

vbkd~zzvextzau

vbak~auart

INTO TABLE zauth_itab

FROM vbak

INNER JOIN vbap

ON vbapmandt = vbakmandt

AND vbapvbeln = vbakvbeln

INNER JOIN vbup

ON vbupmandt = vbakmandt

AND vbupvbeln = vbakvbeln

INNER JOIN vbkd

ON vbkdmandt = vbakmandt

AND vbkdvbeln = vbakvbeln

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