Improve Performance by Controlling Processing Options

You can reduce the execution time of Strobe SQL Analysis by controlling the following processing options:

• The requested 

  Strobe

   SQL Analysis reports. The Observations and Catalog Statistics reports require the most time to produce; the EXPLAIN output and Translation reports require much less. To minimize execution time, request only the reports you need.

• Using background processing. Because of the time it takes to execute, you may prefer to invoke SQL Analysis in the background. On the Strobe - Produce a Performance Profile panel, enter B for Background processing.
• Minimizing statements to process. You can reduce the resource requirements of SQL Analysis and the corresponding contention for database resources by processing only the most resource-consumptive SQL statements. To limit the SQL statements processed by SQL Analysis, enter the appropriate CPU or wait threshold values in the fields listed below that appear on the Strobe - SQL Analysis Feature Report Settings panel.
  • TOTAL CPU PERCENT
  • TOTAL WAIT PERCENT
  • TOP N USERS
  • TOP N WAIT
  • DBRM NAME
  • USE EXISTING PLAN TABLE ROWS.
• Minimizing processing time and eliminating the need for EXPLAIN Authority. Using existing plan_table rows that have been populated during BIND processing or some other previous time reduces the processing time for SQL Analysis and also eliminates the need to grant users EXPLAIN authority. However, this method produces data that is not current.

If you are using SQL Analysis with DDF reports, you can limit the number of SQL statements processed by SQL Analysis by using DDF thresholds and filters. For more information, see the Db2 section of the Strobe Options Guide.