Viewing SQL statement summary data

To view summary data for each SQL statement:

1. On STRAC, on the SUMMARIES EXPAND line, navigate to the SQL option, and press Enter.The SQL Summary view is displayed. This view displays important statistics for each SQL statement, including totals and averages across all executions. If the plan contains multiple DBRMs, the statements are sorted by package/DBRM (program).

   BMC SOFTWARE --------------  SUMMARY TRACE ENTRY    -----------PERFORMANCE MGMT
   SERV ==> STRAC            INPUT   12:09:36  INTVL=> 3  LOG=> N  TGT==> JXODEEO
   PARM ==> CB000001,SEQ=3,SQL,SORT=PGM                 ROW 1 OF 17 SCROLL=> CSR  
   EXPAND:  MON(WKLD), DETAIL, CURRENT                                            
           ACCOUNTING: ENV, ELAP, SQLCNTS, BPOOL, LOCKS, PRLL, PKG, RTN, ACC, DDF
           SUMMARIES:  SQL, SCANS, IO/LOCK, SORTS                                 
                                                                                 
   - - - - - - - - - - - - SQL SUMMARY (DETAIL TRACE ONLY)  - - - - - - - - - - -
                                                                                 
     STMT                  AVG.     %      AVG.     %    SORT   PAGES SCANNED     
     TYPE   STMT  COUNT   ELAPSED  ELAP    CPU     CPU   RECS  INDX DATA WORK+    
   ------- -----  -----  -------- -----  -------- -----  ----  ---- ---- ----     
   PREPARE   190     16  4,370 us   1.4    817 us  19.9     0   250   42    0     
   DROP      289      1  4,199 ms  83.3     42 ms  64.1     0   574   41    0     
   CREATE    289      1    771 ms  15.3  9,555 us  14.6     0   100   35    0     
   INSERT    289     10    148 us   0.0     65 us   1.0     0     0   23    0     
   OPEN      264      1    169 us   0.0    161 us   0.2    10     0    3    0     
   FETCH     257     11      7 us   0.0      7 us   0.1     0     0    0    0     
   CLOSE     271      1     20 us   0.0     17 us   0.0     0     0    0    0     
   PGM:DSNESM68      41           100.0           100.0    10   924  144    0     
                                                                                 
   ** TOTALS ***     41                                    10   924  144    0
2. Scan the PERCENT ELAPSED (% ELAP) column.The percent tells you how much this statement is contributing to the total thread elapsed time. If an average is high, but the statement is only executed a few times, it might not be worth tuning it.
3. Scan the SORT RECS column to see which statements invoked a sort.Although EXPLAIN tells you a sort will be used, it cannot tell you whether many rows will be selected and sorted, or a few. This column does. (Of course, as always, you must adjust the value by any differences between your test and production tables.)
4. Scan the PAGES SCANNED - INDX column.This column tells you whether an index was accessed and how many pages were scanned. If this value does not meet your expectations, there is more information about index accesses a little further along.
5. To check for referential integrity processing. See the PAGES SCANNED - REF column.
6. Sort the display by PAGES SCANNED - DATA.PARM ==> traceID ,SEQ= nn ,SQL, SORT=PD

7. Sort the display by PAGES SCANNED - DATA within Program.PARM ==> traceID ,SEQ= nn ,SQL, SORT=PDP

   The first characters of the column header are used to request a sort. Adding P as the third character keeps all statements for each DBRM together. All options are defined in the HELP panels (F1). (The sort is supported only when you expand to the section, not when you scroll to it.)

8. Place the cursor on one of the SQL statement lines and press Enter.This pop-up display shows the complete statistics for that statement, displayed as averages per execution. Where the single line shows total counts of pages scanned, the pop-up display shows averages for pages and rows that have been accessed.

   BMC SOFTWARE --------------  SUMMARY TRACE ENTRY    -----------PERFORMANCE MGMT
   SERV ==> STRAC            INPUT   13:01:09  INTVL=> 3  LOG=> N  TGT==> WTNDEEO
   PARM ==> TEST,SEQ=2,SQL,SORT=PGM                     ROW 1 OF 21 SCROLL=> CSR  
   EXPAND:  EXPLAIN                                                              
   STATEMENT: 264 OPEN     CURSOR                   NUMBER OF EXECUTIONS:      1
   PLAN:      DSNESPRR               ELAPSED:  AVERAGE   214 ms   TOTAL   214 ms  
   PROGRAM:   DSNESM68               CPU:      AVERAGE    25 ms   TOTAL    25 ms  
                                                                                 
   PACKAGE:  DSNESPRR                --------------------------------------------
                                        INDEX SEQ-DATA                            
                                     -------- --------                            
   ROWS PROCESSED ALL TYPES             2,000    2,001                            
   ROWS PROCESSED CORRECT TYPE          1,999        1                            
   ROWS QUALIFIED BY DM (STAGE 1)           0    2,001                            
   ROWS QUALIFIED BY RDS (STAGE 2)          0    1,001                            
   ROWS INSERTED                            0        0                            
   ROWS UPDATED                             0        0                            
   ROWS DELETED                             0        0                            
   PAGES SCANNED                        4,008    4,004                            
   REFERENTIAL INTEGRITY PROCESSING:                                              
    PAGES SCANNED                           0        0                            
    ROWS DELETED/SET NULL                   0        0                            
   LOB- PAGES SCANNED                       0        0                            
      - PAGES UPDATED                       0        0                            
   ROWS SKIPPED-INCOMPATIBLE LOCK                                                 
   
   

   These are the statistics that allow you to evaluate the SQL predicates. For example, ROWS QUALIFIED BY RDS are caused by Stage 2 predicates and are more expensive than rows qualified in the first stage by the Data Manager (ROWS QUALIFIED BY DM). Of course, there might be variations per execution depending on host variable values. You will see how to find the statistics per single statement execution later, see Viewing-detail-event-statistics.

9. If the MainView for DB2 - Data Collector component is available, you can access more thorough EXPLAIN information by using the EXPLAIN button on the DUSER display, or by hyperlinking on the MVDB2/DC Admin/Archive option on the EZDB2 Menu and then selecting Option E, EXPLAIN Interface. You can view static SQL EXPLAIN data from the plan or package SQL data, or from PLAN_TABLE data that has been previously populated.
10. Press F3 to return to STRAC.