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?
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.