Databases Reference
In-Depth Information
as not to double count caching between the file system and the database. In situations
where the database server appears to have a beneficial need for more memory and mem-
ory is available on the server, the RDBMS simply consumes more memory. The situa-
tion is more complicated when multiple databases are operating on the same server. In
these cases the aggressiveness of the memory consumption is biased by the relative needs
of each database. Databases that have a larger performance need for memory will con-
sume memory more aggressively (with a lower goal for the amount of free memory to
leave on the system). As each database competes for memory, the more aggressive mem-
ory consumers consume enough memory so that the free memory on the server is less
than the free memory objective of the less needy databases, which indirectly forces the
less needy databases to reduce their memory consumption. At this time Oracle does not
yet support automatic tuning of total memory allocation.
The more difficult problem is determining how to wisely distribute memory in a
zero-sum game for the database's many varied purposes. SQL Server uses a strategy that
is largely effective in normal database operational domains, as follows:
•
Lock memory is allocated up to a reasonably large maximum, to avoid lock
escalations.
•
Sort memory is allocated based on the projected needs of each sort operated as
predicted by the query optimizer's cost modeling, provided the sort is small
enough to fit in memory. If a spill is expected, the larger memory allocations for
sort have very marginal gains, so a minimal amount of memory is given to the
sort.
•
The remaining tunable memory is given to the primary data cache.
This strategy works well in many scenarios, but can lead to over allocation of lock
memory, and does not tune the most complex environments for business intelligence
and data warehousing where optimizer estimates can be off by an order of magnitude
and ad hoc workloads are common. SQL Server 2000 and beyond have also included
self-tuning memory capabilities, using an approach that is based on backpressure from
the operating system to determine total database memory allocation. Further details on
how SQL Server chooses to distribute memory to various heaps within the database ker-
nel have not yet been published in detail.
To solve the tuning problems in more complex environments, Oracle and DB2 use
simulation techniques to model the benefit of adding memory to sort, hash join, or
buffer pools (the data caches). Oracle performs this simulation on regular intervals. From
product documentation and Oracle-published white papers, it is evident that Oracle 10g
has an automated memory tuning feature. The Oracle Automatic Shared Memory Man-
agement feature is able to determine values for several configuration parameters includ-
ing the “shared pool,” the “buffer cache,” and the “Java pool.” It also is advertised that
the feature works adaptively to modify memory distribution based on workload charac-
