|
 |
|
Oracle
SGA Sizing
for Windows
Oracle Database Tips by Donald Burleson |
Question:
I'm consolidating
15 Oracle instances onto a single server and I need advice on the
proper way to size my SGA for each instance.
Answer:
In a shared
environment, it's important to observe the optimal sizes for each
shared_pool_size (v$shared_pool_advice)
and the optimal data buffer size (using v$db_cache_advice). I have
complete Oracle SGA sizing details in my book
"Oracle
Tuning: The Definitive Reference".
Make sure to tread these
detailed
notes on SGA sizing.
2007 PGA
Update: Oracle technology is constantly
changing, so don't miss my new notes on updates
to Oracle PGA behavior. Also see these important notes on over-riding
the
Oracle PGA
defaults.
In 10g, you may consider using Automatic Memory Management (AMM)
and set the sga_max_size parameter to give the total SGA allocation.
Wasting expensive RAM resources is a bad thing
and the goal of the Oracle DBA is to maximize the SGA region with
intelligent SGA sizing techniques.
If you only have Oracle on the server, start by
reserving 10% of RAM for Linux or 20% or RAM for Windows. With
whatever RAM is left-over:
-
SGA Sizing
- Optimize the instance
by determining the optimal size for db_cache_size,
shared_pool_size, etc.
-
PGA Sizing - Determine the optimal
total RAM for PGA regions (pga_aggregate_target) to minimize
disk sorts and maximize hash joins
Sizing your SGA
The SGA sizing
tasks include optimizing settings for sga_max_size (not
recommended for large databases where manual allocation is more
effective) and the
various configuration parameters for db_cache_size,
db_xk_cache_size, shared_pool_size, large_pool_size, and
other memory objects give the dba better control of system global
area (SGA) component sizing. Some
parameters to consider in SGA sizing include:
-
sga_max_size
-- This parameter sets
the hard limit up to which sga_target can dynamically adjust
sizes. Usually, sga_max_size and sga_target will be the same
value, but there may be times when you want to have the
capability to adjust for peak loads. By setting this parameter
higher than sga_target, you allow dynamic adjustment of the
sga_target parameter.
-
sga_target
-- This parameter is new
in Oracle Database 10g and reflects the total size of memory
footprint a SGA can consume. It includes in its boundaries the
fixed SGA and other internal allocations, the (redo) log
buffers, the shared pool, Java pool, streams pool, buffer cache,
keep/recycle caches, and if they are specified, the non-standard
block size caches.
SGA Sizing on a dedicated
server
For dedicated Oracle servers, the maximum
total RAM SGA size can be computed as follows:
·
OS Reserved RAM - This is RAM required
to run the OS kernel and system functions, 20% of total RAM for
MS-Windows, and 10% of total RAM for UNIX/Linux
·
Oracle Database Connections RAM - Each
Oracle connection requires OS RAM regions for sorting and hash
joins. (This does not apply when using the Oracle multi-threaded
server or pga_aggregate_target .) The maximum amount
of RAM required for a session is as follows:
2 megabytes RAM session overhead
+ sort_area_size
+ hash_area_size
·
Oracle SGA Sizing for RAM - This is determined
by the Oracle parameter settings. The total is easily found by
either the show sga command or the value of the
sga_max_size parameter.
We should subtract 20 percent from the total
available RAM to allow for MS-Windows overhead. Windows uses RAM
resources even when idle, and the 20 percent deduction is necessary
to get the real free RAM on an idle server. Once the amount of RAM
on the server is known, we will be in a position to size the Oracle
SGA for optimal RAM usage.
Oracle RAM on
MS-Windows
To see how much RAM you have on your MS-Windows server, you can go to start
-->
settings > control panel --> system, and click on the "general" tab (refer to
Figure 1). Here we see that this server has 1,250 megabytes of RAM.
The MS-windows system display screen.
Now that we
know how to tell the size of our MS-Windows RAM and the size of the SGA, we
have to consider the RAM usage for Oracle connections.
Monitoring
Server Resources in MS-Windows
In MS-Windows we can use the performance manager screen to observe the
resource consumption of the Oracle Windows server (refer to Figure 2). The
performance manager is hidden deep inside the Windows menus, but can be found
by following start > settings > control panel > administrative tools >
performance.
The MS-Windows server performance monitor.
The MS-Windows
performance monitor plots three metrics:
Let's take a
closer look at the MS-Windows performance monitor. Figure 2 is a time-based
snapshot of an Oracle databases resource consumption at startup time. These
lines form signatures (known usage patterns) that reveals some interesting
patterns inside Oracle:
1. RAM Usage
The yellow line is RAM usage, and we see the first spike in the RAM when the
SGA is allocated and a short spike in RAM as the database is mounted.
2. DISK
Usage The blue line is the disk I/O, and we see the disk I/O activity
peg at the point where we mount the database. This is because Oracle must
touch every data file header to read the system change number (SCN).
3. CPU Usage
The green line is CPU and it is interesting to note that the CPU never
goes above 50% during Oracle database startup.
Conclusion
For PGA sizing, we need to know the high
water mark (HWM) of Oracle connections and the average RAM needs to
minimize disk sorts and maximize hash joins.
The high water mark of connected Oracle
sessions can be determined in several ways. One popular method uses
Oracle login and logoff system-level triggers to record sessions in
a statistics table.
Another method uses Oracle STATSPACK to display
the values from the stats$sysstat table, or the
v$resource_limit view (only after release 8.1.7, because of a
bug).
In sum, SGA sizing is relatively
straightforward push-pull of the competing RAM needs of the internal
SGA pools (buffer, library cache), the external RAM for PGA, and the
OS overhead.
 |
If you like Oracle tuning, you may enjoy my new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
and include the URL for the page.
