Skip to Content
author's profile photo Former Member
Former Member

SQL Tracer: Execution plan analysis

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    Posted on Jan 19, 2009 at 11:22 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 19, 2009 at 02:42 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 19, 2009 at 02:46 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.