SQL statements with High Executions

SQL statements with High Executions

Executions in SQL stats tell how many times a SQL statement ran on the database during the time period.

SQL statements with high executions are potential to incur performance issues.

• One of the direct impacts of high executions of a SQL statement could be a parsing issue. When a statement has high execution, it will cause high parse calls if the statement is doing ‘one parse per execution’.  Whether parse is hard or soft parse, it involves shared pool allocation and management, consume CPU cycle which in turn causes contention.
• Another impact of high execution is high I/O demands and CPU spikes. Eg. A statement with high executions will also have impact on the high buffer gets and high CPU time.

Tuning strategies include.

1.       Find the SQL statements with high executions in context of application logic. Raise a question on the application logi Why the application is doing so many calls to the sql statement.?

Usually, OLTP type of applications will have short transactions and SQL statements with high executions and cannot be avoided in design. Under such circumstances, we need to have optimal plan for the statements and this would be critical.

2. We also need to pay attention to sql statements which are having high executions in short period of time. This could indicate a performance issue.  In datawarehouse environment, the statements will demand high amount of resources to run and change of their executions could transform the performance to database wide.

Note :

When you want to identify the SQL statements with high executions causing performance issues, Do not conclude the statement by using “SQL statements with HIGH executions in AWR”. Also, check whether the statements are doing any excess resource consumptions like High Buffer Gets, or disk reads or CPU time, etc.

References:  http://www.dbspeed.com/case_study/sql_with_high_executions.html