 |
|
Oracle
Metric shared pool
Oracle Tips by Burleson Consulting
|
The shared pool
Oracle metric caches information that can be shared among
users. Some examples:
·
SQL statements are cached so that they can be reused
·
Information from the data dictionary such as user
account data, table and index descriptions, and privileges is cached
for quick access and reusability
·
Stored procedures, which are executable code that is
stored in the database, can be cached for faster access
An
important area of Oracle instance tuning is the Oracle shared pool.
The Oracle shared pool contains Oracle's library cache, which is
responsible for collecting, parsing, interpreting, and executing all
of the SQL statements that go against the Oracle database. Hence,
the shared pool is a key component, so it's necessary for the Oracle
database administrator to check for shared pool contention.
When the Oracle shared pool is stressed, Oracle reports can quickly
tell you if shared pool thrashing is occurring. One of the most
common causes of Oracle shared pool problems occurs when an
application does not utilize reusable SQL.
Oracle's parsing algorithm ensures that identical SQL statements do
not have to be parsed each time they're executed. However, many
Oracle admins fail to insert host variables into SQL statements and
instead ship the SQL statements with the liberal host values.
My notes on shared pool tuning include:
This, and many other Oracle performance metrics are discussed in
my book "Oracle
Tuning" by Rampant TechPress. You can buy it directly from
the publisher and save 30% at this link:
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
|
