SQL with High Executions
 Concept

The 'executions' in SQL stats tells how many times a SQL statement ran on the database during a time period. A SQL statement with high executions has the potential to incur performance issues.

  •   One of the direct impacts of high executions could be parsing issue. SQL statements with high executions will cause high parse calls if the pattern of 'one parse per execution' is adopted. No matter the parse call becomes hard parse or soft parse, it involves shared pool allocation and management, consuming CPU cycle, and also causes contention.

  •   Another impact of high executions could be high I/O demands and CPU spikes. For example, a SQL statement with high executions could also end up with high buffer gets and high CPU time.

The key tuning strategies include:

  •   We need to investigate those SQL statements with high executions in the context of application logic. What is the application logic behind making so many calls of the same SQL statement? The OLTP type of database has the characteristic of processing short transactions and SQL statements with high executions are unavoidable by design. Under that situation, ensuring optimal SQL plans for those SQL statments become critical.

  •   Also, we need pay attention to those SQL statements which have significant increase of its executions within a short period of time. This could indicate performanace issue. Some SQL statements demand a large amount of resources to run, and the change of their executions could transform the performance landscape at database wide. This is a more common situation at data warehouse environment.

 Case Study Summary

The following example describes a case scenario of how to troubleshooting SQL statements with high executions.

The list of steps include:

 Step 1 : Check execution rate change at instance level

Check the 'AWR Instance Load Profile' to get an idea of the execution rate change at instance level. It indicates when the change starts.

 Step 2 : A SQL with high executions may not be a problem

Check ‘AWR Top SQL by Executions’ to get a list of SQL statements with high executions. Please don’t start investigating from top because the SQL statement with the highest execution may not be the problem.

For example, the SQL statement with the highest execution was identified.

However, by performing resource consumption analysis, it turned out that this SQL statement did not generated any I/O workload

By checking SQL plan, we understand this SQL statements was just to test database heartbeat and it was not querying any real table.

 Step 3 : Check the pattern when a SQL with high executions becomes a problem

Let us go back to the original ‘AWR Top SQL by Executions’ list. Identify the SQL statement not only with high executions, but also with other resource consumptions.

The resource consumption analysis confirmed the pattern of how high executions incurred the performance issue.

Copyright © 2011 Actrace. All Rights Reserved.
Home | Product | Case Study | Support | Download | Contact Us