Library Cache The library cache holds information about SQL and PL/SQL statements that are
run against the database. In the library cache, because it is shared by all users, many different
database users can potentially share the same SQL statement.
Along with the SQL statement itself, the execution plan and parse tree of the SQL statement
are stored in the library cache. The second time an identical SQL statement is run, by the same
user or a different user, the execution plan and parse tree are already computed, improving the
execution time of the query or DML statement.
34 Oracle Database 11g DBA Handbook
If the library cache is sized too small, the execution plans and parse trees are flushed out of
the cache, requiring frequent reloads of SQL statements into the library cache. See Chapter 8 for
ways to monitor the efficiency of the library cache.
Data Dictionary Cache The data dictionary is a collection of database tables, owned by the
SYS and SYSTEM schemas, that contain the metadata about the database, its structures, and the
privileges and roles of database users. The data dictionary cache holds a subset of the columns
from data dictionary tables after first being read into the buffer cache. Data blocks from tables
in the data dictionary are used continually to assist in processing user queries and other DML
commands.
If the data dictionary cache is too small, requests for information from the data dictionary will
cause extra I/O to occur; these I/O-bound data dictionary requests are called recursive calls and
should be avoided by sizing the data dictionary cache correctly.
Pages:
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113