|
 |
|
Oracle dynamic memory management tips
Oracle Database Tips by Donald BurlesonDecember 10, 2015
|
See these important notes scripts for
detecting AMM resize operations.
When I first wrote my book "Creating a self tuning Oracle
database" (hence revised in
"Oracle
Tuning: The Definitive Reference"), I noted that the future of
Oracle is a proactive method whereby the major Oracle SGA RAM regions (shared
pool, buffer caches) can be adjusted in anticipation of changes to processing
needs.
When Oracle9i first allowed "alter system" commands to
morph the SGA, Oracle 10g introduced Automatic Memory Management, a reactive
tool to re-size the RAM regions.
Note: When using AMM by setting sga_target
and sga_memory_max, the values for the 'traditional? pool
parameters (db_cache_size, shared_pool_size, &c) are not ignored.
Rather, they will specify the minimum size that Oracle will always maintain
for each sub-area in the SGA.
While AMM is fine for smaller systems,
there are reports of AMM causing performance problems:
In sum, most shops continue to use self-created memory
management tools because of the performance hits and the reactive nature of AMM,
which does not anticipate upcoming changes in possessing, predictions which can
now be made by analyzing STATSPACK and AWR data.
However, in a highly active database, dynamic memory
reconfiguration can cause sporadic performance issues.
A case study in dynamic memory management

The IBM P590 AIX
Mainframe |
I was at a busy OLTP shop recently that had a one terabyte database with
a ten gigabyte data buffer cache, running an IBM P590 with 18 CPU's and
128 gig RAM, split into logical partitions (LPAR's) for competing
applications. |
With only 1% of the data space available for caching, this
database was heavily I/O bound, a typical workload profile for a well-tuned
large OLTP environment. Note that this database carries almost 100,000
reads per second, and over 4,000 user calls and about 300 transactions per
second, with over 50% of wait times of sequential reads:
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache:
11,504M Std Block Size: 8K
Shared Pool Size:
592M Log Buffer: 1,280K
Load Profile Per
Second Per Transaction
--------------- ---------------
Redo size:
1,816,614.40 6,098.30
Logical reads: 89,851.90
301.63
Block changes:
7,266.68 24.39
Physical reads:
3,227.66 10.84
Physical writes:
1,233.78 4.14
User calls:
4,044.55 13.58
Parses:
154.38 0.52
Hard
parses: 3.64 0.01
Sorts:
330.61 1.11
Logons: 0.22 0.00
Executes:
2,885.80 9.69
Transactions: 297.89
. . .
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ %
Total
Event Waits Time (s) Ela
Time
-------------------------------------------- ------------ -----------
--------
db
file sequential read 568,948 4,375 66.15
CPU
time 1,983 29.99
db
file scattered read 106,287 65 .99
log file
sync 7,053 50 .75
log buffer
space 1,717 47 .71
Problems in paradise
The DBA related that he starting to get panicked phone
calls from their end-users and he noted a huge rush against their library cache
caused by a flood incoming SQL requests.
At the time, a 10 minute snapshot revealed that this system
was experiencing 36,000 logical reads per seconds and it was hung on library
cache load locks and pin events, taking over 80% of system elapsed time.
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache:
11,504M Std Block Size: 8K
Shared
Pool Size: 400M Log Buffer: 1,280K
Load Profile Per Second Per
Transaction
--------------- ---------------
Redo
size: 196,617.69 632.75
Logical
reads: 36,311.98
116.86
Block
changes: 1,114.83 3.59
Physical
reads: 2,164.71
6.97
Physical
writes: 958.11 3.08
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ %
Total
Event Waits Time (s) Ela
Time
-------------------------------------------- ------------ -----------
--------
library
cache load lock 9,708 13,194 49.20
library
cache pin 8,084 9,425 35.14
CPU
time 1,538 5.73
kksfbc child
completion 152,610 1,495 5.57
db file sequential
read 190,283 738 2.75
In this case, the DBA decided to increase his
shared_pool_size, a act which was not necessarily the root cause of this
problem, which I suspect was a flood of non-reusable SQL statements from an
ad-hoc reporting tool.
Another STATSPACK snapshot revealed Oracle in the process
of adding RAM to the shared pool:
Note that the database continued to process the load, but
we see a rare time-5 timed event "background parameter adjustment", as
the "alter system set shared_pool_size=592m" command reconfigured the
SGA:
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache:
11,504M Std Block Size: 8K
Shared Pool Size:
592M Log Buffer: 1,280K
Load Profile Per Second Per Transaction
--------------- ---------------
Redo size:
417,109.35 1,414.69
Logical reads:
156,148.30 529.60
Block changes:
2,122.03 7.20
Physical reads:
6,358.78 21.57
Physical writes:
1,041.54 3.53
User calls:
2,765.77 9.38
Parses:
164.26 0.56
Hard
parses: 2.59 0.01
Sorts:
184.62 0.63
Logons: 0.21 0.00
Executes:
1,704.78 5.78
Transactions:
294.84
. . .
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ %
Total
Event Waits Time (s) Ela
Time
-------------------------------------------- ------------ -----------
--------
CPU
time 2,733 44.85
db file sequential
read 677,443 2,494 40.93
background parameter
adjustment 58,882 580 9.51
db file scattered
read 174,315 153 2.50
SQL*Net message from dblink
113 53 .87
While I've not yet analyzed the details of the DBA's
analysis of this problem, it provides a fascinating insight into the behavior of
a large OLTP system when the SGA of a busy OLTP system is altered.
Warnings about AMM
Quest Software's Guy Harrison has
this warning
about using the AMM:
"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!"
Here are my related noted on Oracle dynamic memory
management:
 |
If you like Oracle tuning, you
might enjoy my book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts. |
|