Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I understand that PreparedStatements are complied and cached by database servers.

I would like to know if all the preparedStatements are complied and cached on the server, in which case what is the cache size on database?

share|improve this question

migrated from stackoverflow.com Mar 4 '13 at 9:51

This question came from our site for professional and enthusiast programmers.

add comment

3 Answers 3

up vote 3 down vote accepted

There is no difference in treatment in Oracle between a java PreparedStatement and a standard Statement: both are treated as cursors. They are both parsed (="compiled"), executed and (for queries only) fetched.

The difference between the two kinds of statement is that PreparedStatement:

  • can be executed multiple times without re-parsing
  • can use binds

Both actions (binding and re-executing) are client behaviours. To Oracle a client that uses a cursor only once or another client that uses a cursor a thousand times are treated exactly the same way:

  1. The statement is initially parsed.
  2. If the statement has already been parsed by the database before (and is still in the share pool), the previous execution plan will be re-used. This is called a soft parse.
  3. If the statement is new or too much dissimilar to previous statements, a new plan is computed. This is called a hard parse.
  4. The statement is then executed (once the client asks for it).
  5. A fetchable result set is returned for queries.
  6. The client can re-use the cursor to run the same query, possibly with different variable values (jump to 4.)

Note that standard statements, even though they are destined to be used only once, will occupy a space in the shared pool (until they are aged away according to an LRU algorithm).

Prepared statements are therefore a client optimization. They allow the client to limit the number of hard parses by using bind variables and reduce further the number of soft parses by reusing the same cursor multiple times when possible.

Not using prepared statements usually leads to a shared pool full of slightly different statements that are used only once each. The number of parses increases dramatically, which can be a source of contention and therefore bad database performance.

You shouldn't worry too much about the size of the shared pool: use prepared statements and Oracle will keep the statements that are used the most at the top, they shouldn't age away and their plans should be available at all time.

share|improve this answer
add comment

In Oracle, SQL and PL/SQL code is cached inside shared pool. It's cached on best effort basis - nothing can guarantee that it will stay in memory - if flushed out of shared pool between two executions, it will be recreated again. To get details look at v$sql and v$sqlarea views.

share|improve this answer
add comment

Yes, they are compiled and stored on the database. In SQL Server, ad-hoc queries (without binding parameters) are compiled and stored, too, filling up the plan cache (except when ad-hoc optimization is enabled). But ad-hoc plans age out quickly and get removed, while frequently used, parameterized query plans will last longer in cache.

However, I think prepared SQL is a bit dated and error prone. Both Oracle and SQL Server support parameterized queries for quite a while now, without the need for an extra roundtrip to prepare a statement. By using "sp_executesql" (MSSQL) or "execute immediate" (Oracle), and their support through database interfaces like ADO.NET, people can create parameterized queries identified by SQL string equality, without an extra database trip, and still use them when the original DB connection and command are long out of scope, even across applications - as long as the SQL string is equal (including capitalization, spaces etc.). This is also great for dynamic SQL built within stored procedures, where table or column names may vary.

The scope of a prepared statement quickly gets lost in an application, requiring repeated prepare again and again. And I've seen faulty code calling "prepare" before every SQL query, forcing re-calculation or cursor creation every time, even when sending dozens of equivalent queries.

There may be a minimum performance gain by sending only a handle to the database, instead of a full SQL string, but it seems no remarkable advantage, far outweighed by the extra trip and the scope/application limit of prepared statements.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.