Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - Database Administration » Performance improvements, diagnostics, and monitoring » Performance » Tips for improving performance

Tip: Identify the cause of slow statements

The statement performance summary feature returns execution times for statements when troubleshooting slow statement performance. The SQL Anywhere Profiler uses this feature to display the statement performance summary results.

The statement performance summary feature uses the sp_top_k_statements and sp_find_top_statements system procedures to report the statement/plan combinations that take the longest time to run.

Use the statement performance summary feature to answer questions like:
  • Is this statement running slower today than it was before?
  • Is the amount of data being returned or modified by the statement the same today as it was yesterday?
  • Has the execution plan for the statement changed?
  • Has one execution plan been used more yesterday than today?
  • Is the maximum runtime for the statement much higher than the average runtime?
  • Is the maximum/average runtime for the statement for one plan very different from the maximum/average runtime for the other statement? If so, do invocations with one plan process or return more rows than those with another plan?

The SQL for statements with a maximum runtime of 0.005 seconds or higher can be found in the GTSYSPERFCACHESTMT system view. The graphical plan for every statement or plan with the maximum runtime of 0.05 seconds can be found in the GTSYSPERFCACHEPLAN system view.

To disable statement performance summary collection on a single database, set the CollectStmtPerfStats option of the sa_db_option system procedure. Setting this option requires MONITOR privileges.

The statement performance summary feature only maintains information for a maximum of 10,000 statements with the highest maximum runtimes.