Interpreting the result

The example shows details of the Sort Activity block of an SQL Activity trace.

SQL Activity workload highlight block

Following the example in this section, you suspect that the application has problems with sorting or scanning data. The Sort Activity block of the report is printed under the event that triggered the sort, together with the workload highlights, and any other requested workload.



OPEN                 20:16:13.15    0.000015           STMT#   493 CURSOR: CUR_WAREHOUSE      ISO(CS) SQLSTATE: 00000 SQLCODE:     0
                                                       REOPTIMIZED(NO)  KEEP UPDATE LOCKS: NO
        --- WORKLOAD HILITE ----------------------------------------------------------------------------------------------------------
       SCANS   :     2  RECS/SORT:  1.00  I/O REQS:        1  SUSPENDS   :      N/P  EXITS    :      N/P  AMS              :      N/P
       ROWSPROC:     2  WORK/SORT:  1.00  AET/I/O : 0.000006  AET/SUSP   :      N/P  AET/EXIT :      N/P  AET/AMS          :      N/P
       PAGESCAN:    30  PASS/SORT:  0.00  DATACAPT:      N/P  RIDS UNUSED:      N/P  CHECKCON :      N/P  DEGREE REDUCTION :      N/P
       LOB_PAGSCAN:        0         LOB_UPD_PAGE :        0
       --- SORT ACTIVITY ------------------------------------------------------------------------------------------
       MEMBER      : N/P       WORKFILES             :     1.00  RECORDS     :     1.00  MAX REQUESTED   :      N/P
       TOTAL SORTS :        1  INITIAL WORKFILES     :     1.00  RECORD SIZE :    15.00  AVG REQUESTED   :      N/P
       SORT KEYS   :     1.00  WORKFILES PARTITIONED :     0.00  KEY SIZE    :    10.00  MAX NOT ACQUIRED:      N/P
       SORT COLUMNS:     2.00  PARTITIONING          :       NO  DATA SIZE   :    12.00  AVG NOT ACQUIRED:      N/P
       AET/SORT    : 0.000012  PARTITIONING & SORTING:       NO  ROWS DELETED:     0.00  MAX RETURN CODE :        0
       SORT TYPE   : ESA       PARTITION TYPE        :     NONE  MERGE PASSES:     0.00

Verify the average elapsed times for sorts (AET/SORT field. Approximately 0.00001 seconds (0.01 milliseconds) per row sorted is acceptable. Significantly higher values indicate a sort problem.

Sort problems could be caused by any of the following factors:

  • Statements with DISTINCT
  • Noncorrelated subqueries
  • UNION
  • Missing index

If your analysis indicates no significant sort problems, the problem could be a scan problem. Look for SQL statements with:

  • Nonindexable predicates (WHERE (COL1*COL2) > (COL3*COL4))
  • OR connections
  • Noncorrelated subqueries
  • Use of static SQL rather than dynamic SQL
  • Missing index