Chapter 30
Piranhas in the Pool: Investigations into Literal SQL and Oracle Shared Pool Performance
John Beresniewicz
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 ...
Get Oracle Internals now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
