 |
|
Oracle System Global
Area (SGA)
Oracle Tips by
Burleson
|
Oracle’s System Global Area ( SGA ) is essentially
short-term memory for the database. As the database references data
and stored PL/SQL objects, the database stores information in the SGA
on the assumption that you will be referencing this information again.
Figure 2.6 illustrates an SGA composed of these four distinct
areas:
-
Shared SQL pool stores SQL statements.
When you execute an SQL statement, it is matched against statements
stored in the shared SQL pool.
-
Data block buffers contain the data
being accessed by the users.
-
Dictionary cache contains information
about the structures that hold the data in the data block buffers.
-
Redo log buffers store information about
changes made to the data in the database.
When the database needs to add more information to the SGA and no
more memory is available, the database removes the oldest data and
objects from the SGA until there is sufficient free memory to hold the
new data and objects.
Oracle also allows you to pin data and stored PL/SQL objects into
the SGA. For instance, lookup tables are often very small; the
contents of these tables can often be pinned entirely in the SGA to
make accesses to the descriptions and codes contained in the table
extremely fast. Objects that are pinned in the SGA are not removed
from the SGA when Oracle needs to make room for new data and code.
TIP: Standardizing SQL And PL/SQL Code
Oracle allows you to pin data and
stored PL/SQL objects into the SGA. To take maximum advantage of this
ability, you’ll need to create some common stored PL/SQL objects for
your system that you and other developers can reuse. Have the DBA
modify the database startup script so that these common objects get
pinned into the SGA when your
database is started. By making frequent use of these objects, you can
improve the performance of your applications.
In addition, you can improve your
system performance by implementing and following a set of coding
standards for your system. Appendix D contains a sample SQL and PL/SQL
coding standard, which can be modified to suit the needs of your
organization.
This is an
excerpt from the book "High Performance Oracle Database
Automation" by Jonathan Ingram and Donald K. Burleson, Series
Editor. |
