Automatic SGA Tuning

Page 61

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

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

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

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

DBA_THRESHOLDS Defines the threshold settings for the given instance.

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 10 g 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:

The log buffer

The keep buffer pool

The recycle buffer pool

The new streams pool

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).

Page 62

The new MMAN (Memory Manager) process controls the automatic allocation of the memory within the SGA. Its job (among other things) is to keep track of the sizes of the memory areas, monitor the database and its workload to ensure that memory distribution is optimal, and redistribute memory allocations as required. This is very useful for hybrid databases that have fluctuating demands on memory areas.

Self-Tuning Checkpointing

Oracle Database 10 g will now self-tune checkpoint operations so that Oracle Database 10 g can make the best use of the I/O bandwidth that is available to the system. To enable self- tuned checkpointing, you should set the fast_start_mttr_ target parameter to a non-zero value. Note that this parameter defaults to 0, which disables self-tuning checkpointing.

New Oracle Database 10 g Trace Functionality

Oracle Database 10 g offers new trace functionality that makes tracing of user sessions much easier. Now, you can enable tracing of all sessions of a specific user by using the dbms_monitor stored PL/SQL package. The client_id_trace_enable procedure allows you to enable tracing for all sessions started by a specific client. Here is an example of a call to this procedure:

Exec -
dbms_monitor.client_id_trace_enable(client_id=>'PROD_USER');

This command will result in the generation of a number of trace files. Oracle Database 10 g comes with the new trcsess utility that allows you to scan through all trace files and combine those produced by the user into a single trace file. Here is an example of the trcsess command in action:

Trcsess output=prod_user.trc clientid='PROD_USER' *.trc

Note that if you have old trace files, they will be included when the trcsess utility is run. So, if you do not wish to include these files, you need to either move them or rename them with a different extension. You can use the optional waits procedure to generate wait- related information, and the optional binds parameter instructs Oracle Database 10 g to include bind variable values in the trace files.

When you enable tracing of a session via the dbms_monitor PL/SQL package, the tracing will appear in the DBA_ENABLED_TRACES view. For example, when we enabled tracing for PROD_USER, the following showed up in the DBA_ENABLED_ TRACES view:

SQL> select trace_type, primary_id, waits, binds
from dba_enabled_traces;

Flylib.com

Category list

Similar pages

Automatic SGA Tuning

Automatic SGA Tuning

Self-Tuning Checkpointing

New Oracle Database 10 g Trace Functionality

Similar products

Similar products