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.
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.
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.
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:
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:
With these changes you will see a decrease in the CPU usage and parsing time on the database:
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:
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:
- Bind Parameters: http://use-the-index-luke.com/sql/where-clause/bind-parameters
- Oracle Real World Performance Group: https://docs.oracle.com/database/121/TGSQL/tgsql_cursor.htm#TGSQL-GUID-1DEE6AD7-C30E-4ABB-9BFF-B5895A6E386B