Database Reference
In-Depth Information
SGA has three mandatory areas and two optional areas as shown
in Figure 4-6. Optional area means you can have it if the
requirement arises. In mandatory areas we have, Shared Pool,
Database Buffer Cache and Redo Log Buffer Cache whereas in
optional areas we have Large Pool and Java Pool.
Shared Pool is utilizes for SQL requests coming from the
connected users, PL/SQL objects execution, data dictionary
views, locks and so on. But the main purpose is SQL, PL/SQL,
locking & data dictionary that's how you can remember. There is
a special area inside Shared Pool that deals with SQL and
PL/SQL, is called the Library Cache. If one user has executed
the SQL statement that statement will get parsed and complied
utilizing this area. If someone else has submitted the same SQL,
it will be used from this area pre parsed and complied. So the
time it takes to execute the second SQL statement will be much
less compared to the first one.
The “Data Dictionary Cache” holds the most recently used
database dictionary information. Data Dictionary is basically the
information about the data inside the database or in other words
metadata in the form of tables and view about the database.
Oracle uses it at the time of parsing of SQL statement. The size
of shared pool can be adjusted dynamically using
SHARED_POOL_SIZE of init.ora file.
Database Buffer Cache holds the DB Blocks recently read from
the data files. Database Buffer Cache has further three sub-
caches. One is called KEEP, second one is RECYCLE and the
third one is DEFAULT. In KEEP, DB blocks will be retained in
the RAM and will not get aged out. Oracle Instance will remove
stuff palced in RECYCLE from the RAM as soon as they are not
needed. DB Blocks without any assigned attribute (KEEP or
RECYCLE) are placed in the DEFAULT buffer pool. Two
init.ora parameters I really like to mention over here that are
related to KEEP and RECYCLE are
DB_RECYCLE_CACHE_SIZE and DB_KEEP_CACHE_SIZE.
We will see the init.ora file and specially its application in
chapters ahead.
Search WWH ::
Custom Search