The Dark Side of Parsing

Differences in performance with Oracle when using native queries vs. prepared statements

Generally speaking, native database queries in an application complicate its maintainability and the possibility to develop independently of a specific database. Within the framework of a performance analysis of an old Java application with an Oracle database, we discovered another aspect that constitutes an argument against using this kind of query: the performance compared to prepared statements in the context of an Oracle database.

The starting point was an analysis carried out by Oracle’s Automatic Database Diagnostic Monitor (ADDM)[1]. This feature analyzes SQL queries, among other things, and makes recommendations. In this case, it produced the following message for a number of queries:

Hard parse due to literal usage: SQL statements were not shared due to the usage of literals. This resulted in additional hard parses which were consuming significant database time.

Investigate application logic for possible use of bind variables instead of literals.

Usage of literals in this context means the insertion of the actual values into the conditions, e.g.:

    SELECT * FROM my_table WHERE my_field = 123

By contrast, when you use bind variables, the specific value is replaced by a placeholder, and it is inserted only in the subsequent step.

Performance analysis: Literal vs. bind variables

Now, how do these two options affect the performance of the database queries, and what causes these effects? To answer that, we need to look at the way Oracle processes queries. Before actually executing it, the query needs to be parsed in order to identify possible execution plans. For example, the system checks whether and in what way an index can be used. This “hard parse” is necessary to determine the optimal access plan, and it can take longer than the execution itself. However, Oracle saves such execution plans for a query, i.e. if the query is to be executed again, the parse overhead can be avoided. If you use literals, this works only if the values are identical.

Conversely, if you use bind variables, Oracle creates the execution plan for the query with the placeholders and saves it like that. This way, it does not matter if the query is later executed with different values: it is always compared with the query with the bind variables. The previously determined execution plan can thus be used again and again—without parsing.

How much of a difference does that really make? As the old Java application we were examining exclusively uses JDBC drivers, we wrote a small sample application that also only uses JDBC and submits identical queries both as a native query and by way of a prepared statement. When using prepared statements, JDBC takes care of transmitting the bind variables to Oracle. The specific queries in this context were based on the ones that the ADDM had flagged as problematic. For the tests, we executed several hundred queries with different values and against various Oracle instances; in effect, the prepared statements were always executed 10 times faster, and in some instances up to 100 times faster! We had not expected the difference to be this great, but then, this corresponds to the tests under [2]. We then repeated the tests using JPA and EclipseLink, and the time response was on a level with that of the prepared statements.

As a result, we definitely recommend modifying the Java application, or at least the relevant components, so as to use prepared statements. The necessary changes to the code are manageable, as the following example shows:

    import java.sql.Connection;
    import java.sql.Statement;
    import java.sql.PreparedStatement;
    ...
    // 1. Query with literal as native query
    Statement stmt = connection.createStatement();
    stmt.executeQuery("select * from my_table where my_field = 123");
    // 2. Query with bind variable using a PreparedStatement
    String query = "select * from my_table where my_field = ?";
    PreparedStatement stmt2 = connection.prepareStatement(query);
    stmt2.setInt(1, 123);
    stmt2.executeQuery();

Cursor-sharing – Tuning the database

Lastly, we would like to point out an option provided directly on the Oracle database itself, for example if it is not possible to modify the respective code. The analysis of the ADDM produced the following note:

Alternatively, you may set the parameter “cursor_sharing” to “force”.

Normally, only identical queries are treated identically, i.e. not parsed again, which is the advantage of using bind variables and/or prepared statements. But if you set this parameter to “force”, queries that only differ in the literals used are also treated as identical. That sounds like a quick win at first, making changes to the code unnecessary. However, Oracle selbst advises against doing so (but without elaborating). If you are interested, you can find a more comprehensive description of the parameter and its advantages and disadvantages here.

Conclusion

Small changes to the code can help to noticeably improve the performance even of older Java applications that use many native queries—at least if the database is an Oracle database. Switching to prepared statements also pays off, especially if the application frequently has to execute similar queries. We were not able to verify whether other DBMS present similar differences in performance with literals vs. bind variables, but it is conceivable.

---

See also:

[1] More information on ADDM: Oracle 12c: Database tuning and monitoring (only German)
[2] Details on the hard parse and its performance: Bind variables – The key to application performance

This post was written by: