SQL Tracer: Execution plan analysis

[Former Member]

Hi Folks,

0 SELECT STATEMENT ( Estimated Costs = 3,757E+03 [timerons] )

1 RETURN

2 NLJOIN

3 [O] TBSCAN

4 SORT

5 TBSCAN GENROW

6 <i> FETCH PROJ

7 IXSCAN PROJ~0 #key columns: 2

What does the above execution plan tell?

Thanks in advance.

Regards,

Younus

[Former Member]

Hi,

first of all the costs are high.

Second it would be less guessing, if you add the statement which is always displayed.

Third it is a nested loop join

+ one table is accessed by table scan ... name is missing

+ one Table proj is accessed by key 0, primary key, and 2 columns are used.

Maybe it is FAE statement, because some databases process FAE as a join of the a temporary

table with a database table.

Anyway, it is probably not fast.

Siegfried

[HermannGahm]

Hi Younus,

> What does the above execution plan tell?

you are running on a DB2 for LUW (DB6)....

Besides that:

This part:

3 [O] TBSCAN

4 SORT

5 TBSCAN GENROW

is normally from an IN List or OR Concatenation. As already

mentioned it may be a FAE (FOR ALL ENTRIES), or a OR or a IN e.g. from a RANGE or a SELECT_OPTION. If it is a FAE you may have up to 60 Values in this list (depends on parameters).

In case of IN or OR clause in ABAP OPEN SQL it can be even more.

This part:

6 FETCH PROJ

7 IXSCAN PROJ~0 #key columns: 2

indicates a unique key access which is performed with 2 columns (MANDT, PSPNR) which should be the primary key. This index unique scan is performed multiple times (fore each value of your list).

You can trace your program with ST05 and check the "Min Time/R" in the Statement Summary. It should not be more than 10000 microsecs (asuming it is a concatenated index unique scan).

The "Records" tells you how big your result set is and the nr. of "?" in the where clause of the execution plan tells you the lenght of the list (how often Step 6 and 7 of your execution plan is executed).

Kind regards,

Hermann

[Former Member]

Hi Younus,

probably you have had something like this:

SELECT  *                                                                                
FROM atab AS t0, 
       (SELECT *
          FROM ( VALUES ( CAST (? AS VARCHAR(10) ), CAST (? AS VARCHAR(50) ) ),
                        ( CAST (? AS VARCHAR(10) ), CAST (? AS VARCHAR(50) ) ),
                        ( CAST (? AS VARCHAR(10) ), CAST (? AS VARCHAR(50) ) ),
                        ( CAST (? AS VARCHAR(10) ), CAST (? AS VARCHAR(50) ) ),
                        ( CAST (? AS VARCHAR(10) ), CAST (? AS VARCHAR(50) ) ) ) AS t1_tmp (tabname, varkey)
      GROUP BY tabname, varkey ) AS t1
 WHERE t0.tabname = t1.tabname
   AND t0.varkey = t1.varkey                                                                                
Access Plan      Opt Level = 5 ; Parallelism = None                                                                                
0 SELECT STATEMENT ( Estimated Costs =  1,135E+02 [timerons] ) num_rows tot_cost i/o_cost       
   -     1 RETURN
       -     2 NLJOIN
           -     3 [O] TBSCAN
               -     4 SORT
                         - TBSCAN GENROW
           -     6 <i> FETCH ATAB
                     7 IXSCAN ATAB~0 #key columns:  2

This is normally created by a FOR ALL ENTRIES statement on a DB6 system with SAP kernel 7.00.

  SELECT *
    FROM atab
     FOR ALL ENTRIES IN itab
   WHERE tabname = itab-tabname
     AND varkey = itab-varkey.

This means, that the database interface builds a temprary table T1 which contains in the example five rows (could be more in your case). This table gets sorted and is used as the outer table [O] of a nested loop join. The inner table \[ I\] is a primay key access with two columns (to table ATAB in my example). This is usually fast if the selectivity of the first two columns is high. If not, this statement could run for a while.

Help this makes things more clear.

Regards

Ralph