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?
migrated from stackoverflow.com Mar 4 '13 at 9:51
This question came from our site for professional and enthusiast programmers.
There is no difference in treatment in Oracle between a java
The difference between the two kinds of statement is that
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:
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.
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.
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.