PreparedStatement caching size on Oracle and SQL Server

Question

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?

Answer 1

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 being parsed by the database before (and is still in the share pool), the precedent 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.

Answer 2

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.