While there are several ways to improve Oracle Database Performance, Inefficient Database Access Patterns are among the top reasons why applications suffer from bad performance. Besides executing too many SQL Queries (N+1 Query Problem) or implementing Database Business Logic in the App (as compared to using Stored Procedures) developers often fail to use Prepared Statements and Bind Values which can drastically speed up SQL execution as the database can create and cache Execution Plans.

To better support analyzing database related application performance problems we at Dynatrace recently extended our existing support for database monitoring by providing a Database Agent for Oracle and SQL Server (more databases on the short list). In this blog I will focus on leveraging Execution Plan Caching via Prepared Statements and Bind Values:

Execution Plans Explained

Databases with an execution plan cache like Oracle Database can reuse an execution plan when executing the same statement multiple times. Before a SQL statement is executed, Oracle database checks the syntax and semantics of the SQL statement as well as the access rights of the user issuing the statement. Once this is done, an execution plan is created for this specific statement.

If the query is already in the cache, the database doesn’t need to go to that process. It can reuse the existing execution plan. Hard parsing a SQL statement before every execution is analogous to recompiling a C program before every execution.

Creating execution plans can be costly. Caching them will speed up the whole database!
Creating execution plans can be costly. Caching them will speed up the whole database!

Plans can only be reused if the SQL statement is exactly the same. If you put different values into the SQL statement, the database handles it like a different statement and has to create a new execution plan. Execution plans remain in the cache as long as there is enough memory to store them.

Bind variables are an alternative way to pass data to the database. Instead of writing the actual values, placeholders are inserted into the SQL statement. That way the statements do not change when executing them with different value. Bind variables are also the best way to prevent SQL injection.

Insert2

Impact of Hard Parsing in Oracle

Oracle strongly recommends reducing hard parsing as much as possible as CPU and memory overhead of hard parses in Oracle increases SQL statement execution time and decreases concurrency:

“Through decades of experience, the Real-World Performance group has found that applications that use literals are a frequent cause of performance, scalability, and security problems. In the real world, it is not uncommon for applications to be written quickly […] A classic example is a “screen scraping” application that copies the contents out of a web form, and then concatenates strings to construct the SQL statement dynamically.”

Using the Dynatrace Database Agent for Oracle automatically queries the key performance metrics from Oracle (via its v$ views). The two metrics you need to look for in Oracle is the CPU usage (remember the generation of execution plan is very CPU expensive) and the time spent on parsing as shown on the following screenshot of the Dynatrace Oracle Database Instance Dashboard.

Dynatrace Database Agent automatically provides all important metrics to identify CPU and SQL Parsing related issues
Dynatrace Database Agent automatically provides all important metrics to identify CPU and SQL Parsing related issues

To get insight into the actual SQL Statements executed by your application simply instrument your application with one of our agents (Java, .NET, PHP, …)  to see which SQL queries need to be adapted as they are currently not leveraging Prepared Statements and Bind Values:

Dynatrace Database Dashlet shows full SQL statements and highlights whether these statements are currently prepared or not!
Dynatrace Database Dashlet shows full SQL statements and highlights whether these statements are currently prepared or not!

With Dynatrace you do not only get the list of SQL Statements but you also get to see which line of code executes these statements by drilling to the PurePath Dashlet. For more information on how this is done feel free to watch our Diagnostics Tutorials on our Performance Clinic YouTube Channel or APM University.

Using Bind Variables to Improve Performance

Once the SQL queries have been adapted to use bind variables the Database Dashlet shows the updates statements as well as the fact that these statements really got prepared:

Verify that SQLs are now prepared and bind values are used.
Verify that SQLs are now prepared and bind values are used.
You also have the option to show each individual bind value per SQL Execution!
You also have the option to show each individual bind value per SQL Execution!

 With these changes you will see a decrease in the CPU usage and parsing time on the database:

Insert7

Since the database engine doesn’t have to spend the time generating an execution plan, it can process the queries quicker – in that example, the response times of queries in a system under load were improved by half:

Comparing individual transactions makes it easy to validate change between code commits or releases!
Comparing individual transactions makes it easy to validate change between code commits or releases!

If you want to do this type of analysis on your own application feel free to Download the Dynatrace Free Trial. Activate the Oracle Database Monitoring to check the Time Spent Breakdown and CPU utilization on your database instance and instrument your application with our Java, .NET, PHP or Node.js agent to check your SQL statements.

More information / Source: