| |
 |
|
Oracle10g:
Viewing
Information About the SGA
Oracle Tips by Burleson |
Also see:
The following Oracle Database 10g views provide information about the
SGA components and their dynamic resizing:
v$sga -
Displays summary information about the system global area (SGA).
v$sgainfo -
Displays size information about the SGA, including the sizes of
different SGA components, the granule size, and free memory.
v$sgastat -
Displays detailed information about the SGA.
v$sga_dynamic_components -
Displays information about the dynamic SGA components. This view
summarizes information based on all completed SGA resize operations
since instance startup.
v$sga_dynamic_free_memory -
Displays information about the amount of SGA memory available for
future dynamic SGA resize operations.
v$sga_resize_ops -
Displays information about the last 100 completed SGA resize
operations.
v$sga_current_resize_ops -
Displays information about SGA resize operations which are currently
in progress. An operation can be a grow or a shrink of a dynamic SGA
component.
Oracle10g v$db_cache_advice
The v$db_cache_advice view is now run dynamically in the SYSAUX_xxx
views. It is similar to an Oracle7 utility that also predicted the
benefit of adding data buffers. The Oracle7 utility used the x$kcbrbh
view to track buffer hits and the x$kcbcbh view to track buffer
misses.
Bear in mind that the data buffer hit ratio can provide data similar
to v$db_cache_advice, and most Oracle tuning professionals use both
tools to monitor the effectiveness of their data buffers.
The following query can be used to perform the cache advice function,
once the v$db_cache_advice has been enabled and the database has run
long enough to give representative results.
In Oracle Database 10g, you can also OEM interface to see these
results in real-time. The output from the script is shown below.
Note that the values range from 10 percent of the current size
to double the current size of the db_cache_size
(Figure 1.6).
Estd Phys Estd Phys
Cache Size (MB) Buffers Read Factor Reads
---------------- ------------ ----------- ------------
30 3,802 18.70 192,317,943
ç
10% size
60 7,604 12.83 131,949,536
91 11,406 7.38 75,865,861
121 15,208 4.97 51,111,658
152 19,010 3.64 37,460,786
182 22,812 2.50 25,668,196
212 26,614 1.74 17,850,847
243 30,416 1.33 13,720,149
273 34,218 1.13 11,583,180
304 38,020 1.00 10,282,475 Current Size
334 41,822 .93 9,515,878
364 45,624 .87 8,909,026
395 49,426 .83 8,495,039
424 53,228 .79 8,116,496
456 57,030 .76 7,824,764
486 60,832 .74 7,563,180
517 64,634 .71 7,311,729
547 68,436 .69 7,104,280
577 72,238 .67 6,895,122
608 76,040 .66 6,739,731 ç
2x
The output shows neither a peak in
total disk I/O nor a marginal trend with additional buffer RAM. This
result is typical of a data warehouse database that reads large tables
with full-table scans. In this case, there is no specific “optimal”
setting for the db_cache_size parameter. Oracle Database 10g will
devour as much data buffer RAM as we feed to it, based on the maximum
value set by sga_target, and disk I/O will continue to decline.
However, there is no tangential line that indicates a point of
diminishing returns for this application.
Taking the above into account, Oracle Database 10g will apply this
simple rule: db_cache_size should be increased if spare memory is
available and marginal gains can be achieved by adding buffers.
Next let’s see how Oracle Database 10g automatically adjusts storage
within the shared_pool_size RAM region.
Get the complete Oracle10g story:
Get your code access instantly!
Click here:
http://www.rampant-books.com/book_2003_2_oracle10g.htm
|
