Library Cache vs Data Dictionary Cache in Oracle

Library Cache

This part of the Shared Pool memory structure is used for storing the recently executed SQL and PL/SQL statements and hence these statements if encountered again will be shared which will subsequently boost the performance of the database.

This memory structure is managed by a LRU (Least Recently Used) algorithm and this memory structure is also composed of two structures. One, Shared SQL area - for storing the most recently executed SQL statements and Two, Shared PL/SQL area - for storing the most recently executed PL/SQL statements.

Data Dictionary Cache

This part of the Shared Pool memory structure is used for storing the most recently used data definitions in the Oracle DB. These data definitions may include information about: database files, tables, indexes, privileges, users, etc.

Caching these inforamtion in memory improves the performance especially for queries and updates using DML. During the parsing phase, the server process scans this memory structure to resolve the database object names and validate access.

Library Cache vs Data Dictionary Cache

Thus we see that Library Cache is used to store the recently executed SQL and PL/SQL statements, which eliminates the need for parsing and compiling the statements again if used subsequently and hence improves performance. Whereas, the Data Dictionary Cache is used to store the information which improves the validation phase of the execution of those SQL and PL/SQL statements. So, both the memory structures can be visualized as being complimentary to each other.