Piranhas in the Pool: Investigations into Literal SQL and Oracle Shared Pool Performance
The Oracle shared pool is a highly sophisticated mechanism designed to improve performance generally through the sharing of SQL statements, stored PL/SQL, and other objects among users. The shared pool’s library cache is designed to conserve both CPU and memory resources by avoiding redundant parsing, optimization, and memory allocation steps for commonly executed SQL. These efficiencies are based on storing execution information in complex data structures for subsequent reuse by identical SQL.
Unfortunately, it is also possible for application SQL to be particularly troublesome for the shared pool to manage. The very mechanisms ...