In an effort to compete with simple databases such as SQL Server, Oracle
has undertaken to remove much of the inherent complexity by building
automated tools to manage storage, RAM and tuning.
This "umbrella of simplicity" is amazing and has allowed the world's most
flexible and robust database to be as simple to use as the lesser databases,
using this one-sized-fits-all technique of automation.
However, it is critical to understand that the Oracle automation tools
were developed for hands-off, small departmental applications, and
automation is not always the best approach for a large mission-critical
database with thousands of concurrent users. Remember, with today's
technology, any automated tool will never be able to optimize your database
as well as a human expert.
This is especially true for Oracle's automatic memory management (AMM),
which is not appropriate for large mission-critical databases, where a human
expert can always do a better job in optimizing RAM resources.
A competent Oracle professional will always be able to do a better job
than an automated tool at forecasting and optimizing RAM resources for SQL
statements. This is true for all automated tools, not just Oracle AMM,
and there is no automated substitute for human intuition and experience.
The biggest issue with AMM is that it is a "reactive" tool. Unlike
a proactive approach, AMM waits until it detects a problem before changing
the SGA pool sizes. But on the other hand, developing a proactive
approach is not easy either.
To be effective in developing predictive models for your database, you
must understand statistical regression techniques and be able to analyze
STATSPACK or AWR reports to identify RAM usage trends (called "signatures").
Once the repeating patterns of workload usage are identified (repeating in
cycles of hour-of-the-day, day-of-the-week, and day-of the-month), you can
automate scripts to re-optimize your SGA pools immediately before the
workload changes.
Using
predictive modeling techniques you can analyze historical trends,
develop signatures of RAM usage, and schedule jobs that morph the SGA
just-in-time, anticipating the upcoming workload change and fixing the
problem before if effects the end-users.
For complete details and scripts for building a proactive RAM monitoring
infrastructure, see my book
Oracle Tuning: The Definitive Reference.
Oracle designed AMM to simplify operations on small to midsize databases
where it does an adequate job in managing the Ram pool in the absence of a
DBA. However, in case after case on large systems, I have seen
frequent AMM re-size operations cause performance problems on Oracle
databases.
As of Oracle 11g, I do not recommend using automatic memory management for
large or for mission-critical databases. This is especially true in
cases when AMM is used with obsolete tools like shared servers (the
multi-threaded server, or MTS).
Never use MTS with AMM!
Using shared servers (MTS) with AMM and PL/SQL can hog all of the RAM on the
Oracle instance! Quest Software's Guy Harrison has this excellent
warning about using the AMM with MTS:
"When you use MTS and AMM (or ASMM) together, PL/SQL
programs that try to create large collections can effectively consume all
available server memory with disastrous consequences.
AMM allocates
virtually all memory on the system to the large pool in order to accommodate
the PL/SQL memory request. First it consumes the buffer cache, then it
reduces the pga_aggregate_target - all the way to zero!"
In sum, while the AMM is great for small hands-off databases with lots of
RAM resources, manual RAM allocation will do a better job, especially for
mission critical databases.
See my related notes on the perils of AMM:
-
-
-
-
-
 |
If you like Oracle tuning, you may enjoy the book
Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it
directly from the publisher and save 30%, and get instant access to the code depot of
Oracle tuning scripts. |
and include the URL for the page.
