Identifying SQL Performance Fluctuation

Objective

After completing this lesson, you will be able to analyze SQL performance fluctuations using diagnostic tools.

Content

Identify target SQL statements using SQL trace to capture statements while reproducing the program.
Pinpoint slow or fluctuating queries affecting performance by reviewing the trace.
Check query performance for fluctuations using the M_SQL_PLAN_CACHE monitoring view.
Analyze MIN_EXECUTION_TIME and MAX_EXECUTION_TIME to understand execution time variations.
Examine MIN_EXECUTION_MEMORY_SIZE and MAX_EXECUTION_MEMORY_SIZE for memory usage fluctuations.
Confirm SQL performance fluctuation if there is a significant gap between maximum and minimum execution times or memory usage, and max_execution_time is slow enough to affect business.