Automatic SGA Tuning


Page 61

image V$SYSMETRIC_HISTORY Maintains a historical view of the metrics previously in V$SYSMETRIC. This view is time limited.

image DBA_HIST_SYSMETRIC_HISTORY Provides a longer-term view of historical system metrics.

image DBA_OUTSTANDING_ALERTS Contains current database alerts (this view was introduced earlier in this section).

image DBA_ALERT_HISTORY Provides a history of threshold alerts that have been resolved, and provides a history of nonthreshold alerts.

image DBA_THRESHOLDS Defines the threshold settings for the given instance.

image V$ALERT_TYPES Provides information on various alert types.

Automatic SGA Tuning

If you have ever asked yourself, ''How much memory should I allocate to the database buffer cache?" then Automatic Shared Memory Management (ASMM) might just be the thing for you. Of course, if you are a control freak, then you might just not like it at all. Oracle 10g allows you to define the size of a new parameter, sga_target, and it will allocate that memory as it thinks best to the default buffer cache, the shared pool, the large pool, and the java pool. Setting sga_target to 0 disables ASMM, which is the default setting.

Regardless of the setting of sga_target, you still need to manually set the following parameters:

image The log buffer

image The keep buffer pool

image The recycle buffer pool

image The new streams pool

image Nondefault block-sized buffer cache pools

Also, you must set statistics_level to TYPICAL or ALL to use ASMM. Also, if one of the ASMM parameters (e.g., shared_pool_size) is set to a non-zero value, then the value that the parameter is set to will be considered a minimum size for that memory area.

The sga_target parameter is a dynamic parameter, so if you find that you need additional memory allocated to your database while it's up and running, all you need to do is issue an alter system command and allocate more memory to the database (assuming the memory is available).


Oracle Database 10g New Features
Oracle Database 10g New Features (Osborne ORACLE Press Series)
ISBN: 0072229470
EAN: 2147483647
Year: 2006
Pages: 80

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: