Oracle 10g New Features: Advisory Framework - ADDM
Advisors are the server components that provide you with useful feedback about resource utilization and performance. The most important of all advisors is the Automatic Database Diagnostic Monitor (ADDM). ADDM does analysis of the system, identifies problems and their potential causes, and comes up with recommendations for fixing the problems. It can call all other advisors also.
The main features of the ADDM are as follows:
- ADDM runs automatically in the background process MMON whenever a snapshot of in-memory statistics is taken. ADDM does analysis of the statistics collected between two snapshots.
- ADDM analysis results are written back to the workload repository for further use.
- ADDM uses the new wait and time statistics model, where activities with high time consumption are analyzed on a priority basis. This is where the big impact lies.
- ADDM can also be invoked manually
The results of the proactive analysis performed by the ADDM module are posted to the workload repository and then they are available to users through the OEM console or by other SQL query means. ADDM analysis is also the driving point for Server Alerts whenever the threshold is exceeded for the specified metrics. The new OEM or Grid Control has many web pages devoted to displaying ADDM findings.
Other advisors include:
- SQL Tuning Advisor: This advisor is responsible for providing tuning advice for a SQL statement. We have covered this topic in detail in Chapter 2, Database Tuning and Performance Improvements.
- SQL Access Advisor: This provides expert advice on materialized views, indexes, and materialized view logs. A full detailed account of its utility and usage is covered in Chapter 16, Business Intelligence.
- Segment Advisor: This advisor is responsible for space issues involving a database object. It analyzes the growth trends. It can also extend advice on the shrink possibility for the segments.
- Undo Advisor: This advisor suggests parameter values, and how much additional space would be needed to support flashback for a specified time. However, most of the undo tuning (like undo retention) is automatic in Oracle Database 10g.
- Redo Logfile Size Advisor: This determines the optimal smallest online redo log file size, based on the current fast_start_mttr_target setting and MTTR statistics.
Server Alert Mechanism
As we have noted, the Oracle 10g database collects and stores various statistics into the workload repository. Those statistics are then analyzed to produce various metrics.
Server-generated alerts pretty much depend on the derived 'metrics' available in the workload repository. MMON wakes up every minute to compute the metric values. In addition, for all the metrics that have thresholds defined, MMON verifies the thresholds and generates the alerts, if required. Then the alert is queued into the predefined persistent queue alert_que owned by SYS.
Based on the values obtained in the alert_que, OEM manages the notification mechanism. Administrators are notified using e-mail, or pager. Server-Generated Alerts are always displayed on the OEM console.
Previously available OEM alerts and the newly introduced 10g Server Alerts mainly differ in the way they are generated. Server Alerts depend on the metric's computation and threshold validations, which are performed by the Oracle Database and access the SGA directly, whereas the OEM alerts depend on statistics collected by intelligent agents.
Pro-Active Space Management
In Oracle 10g, the tablespace disk space utilization is proactively managed by the database. The Server Alert Mechanism monitors Tablespace disk space utilization.
Information gathered into the AWR is also used to do the growth trend analysis and capacity planning of the database. The background process (MMON) verifies tablespace thresholds. The threshold is reached if the allocated space in the tablespace has reached a certain percentage of the total size of the tablespace (if the threshold is configured as a percentage), or when the total allocated space has reached a certain value that is set by you. An alert is triggered when the threshold has been reached.
Another important and useful feature introduced in 10g is the facility to shrink the segment. In the previous releases of the Oracle database, moving or redefining the segment was the only way to free space once allocated below the segment's HWM.
In Oracle 10g, you can now shrink segments. When a segment is shrunk, its data is compacted, its HWM is pushed down, and unused space is released back to the tablespace containing the segment. This is possible for the segments in Automatic Segment Space Managed (ASSM) tablespaces only. For example, to shrink the table sales_items, use the statement:
ALTER TABLE sales_items SHRINK SPACE CASCADE;
Shared Server Configuration
In shared server architecture, the listener assigns each new client session to one of the dispatchers. As the user makes requests, the dispatcher sends the request to the shared server. It is also possible that a different set of shared servers are utilized for a given user session. The dispatchers act as the coordinating agents between the user sessions and the shared servers.
A dispatcher is capable of supporting multiple client connections concurrently. Each client connection is bound to a virtual circuit. A virtual circuit is a piece of shared memory used by the dispatcher for the client connection requests and replies.
An idle shared server process picks up the virtual circuit from the common queue, services the request, and relinquishes the virtual circuit before attempting to retrieve another virtual circuit from the common queue. In this way, a small number of server processes are able to service a large number of clients or users. This method also supports an increased number of users with less system resources.
Note that not all applications are certified to use shared servers, but that server-side load balancing in a RAC may benefit from using shared servers.
As seen in Figure 9.5, the listener communicates with the dispatchers on behalf of the user or client sessions. Once the user sessions establish connectivity with dispatchers, the shared servers service them.
Figure 9.5 Shared Server Architecture
Prior to the release of Oracle Database 10g, you needed to set up at least one dispatcher for the shared server configuration to be enabled. You normally needed to set the dispatchers initialization parameter to configure the information about dispatchers.
With Oracle Database 10g, even without specifying a dispatcher with the dispatchers parameter, you can enable shared server by setting shared_servers to a nonzero value. The default behavior is that Oracle creates one dispatcher for the TCP protocol automatically. This way, it is easier to configure a shared server environment.
The equivalent dispatchers initialization parameter for this configuration would be:
DISPATCHERS="(PROTOCOL=tcp)"
When you need to use shared servers while the system is running, you can simply set the dynamic shared_servers initialization parameter to a value greater than zero with an ALTER SYSTEM command.
As with other parameters, you can change just the current instance with this command and, if you are using an SPFILE, you can change the parameter for future instances as well. For example, to activate three shared servers in the current instance and the SPFILE, enter this command:
SQL> ALTER SYSTEM SET SHARED_SERVERS=3 SCOPE=BOTH;
There are several other parameters that can be set in the shared server environment, but they are not required. Once you set shared_servers, your system will be running in shared server mode.
[NOTE: When you need to configure another protocol other than TCP/IP, configure a protocol address with one of the following attributes: ADDRESS, DESCRIPTION, or PROTOCOL.]
Parameters with the prefix MTS are now obsolete. This means if you try to start an instance using these parameters you will receive the following error: 'ORA-25138: [parameter] initialization parameter has been made obsolete'
Even if you try to set mts_servers during the runtime of an instance:
SQL&ht; ALTER SYSTEM SET MTS_SERVERS = 2; ALTER SYSTEM SET MTS_SERVERS = 2 * ERROR at line 1: ORA-25138: MTS_SERVERS initialization parameter has been made obsolete
All the replacement parameters listed in the table are dynamic, meaning that you can change the values while the instance is running. Table 9.1 shows the replaced parameters.
| OBSOLETE PARAMETER | REPLACED BY PARAMETER |
|---|---|
| mts_servers | shared_servers |
| mts_max_servers | max_shared_servers |
| mts_dispatchers | dispatchers |
| mts_max-dispatchers | max_dispatchers |
| mts_circuits | circuits |
| mts_sessions | shared_server_sessions |
| mts_listener_address mts_multiple_listeners | local_listener |
In the case of the dispatchers parameter, the results of the change will depend on which attributes you modify. Since several of the attributes affect the network session layer when a dispatcher is started, they cannot be changed for dispatchers already started. These attributes are: protocol, address, description, presentation, connections, sessions, ticks, and multiplex[/i].
You can dynamically modify the other attributes (listener and service) and affect existing as well as new dispatchers of the same configuration.
There is a new view, v$dispatcher_config, that shows more information about existing dispatchers. This view displays information about the dispatcher configurations, including attributes that were not specified and were given a default value. The column CONF_INDX in v$dispatcher_config can be joined to the conf_indx column in v$dispatcher to see all of the detailed information about a given dispatcher. This information helps you to make more informed decisions on what attributes to modify and helps determine if you need to add or remove dispatchers.
For example, to get service and other details about dispatchers, use the following query:
SQL> select name, dispatchers, substr(service,1,20) service, idle, busy
from v$dispatcher,v$dispatcher_config
where v$dispatcher.conf_indx =
v$dispatcher_config.conf_indx ;
NAME DISPATCHERS SERVICE IDLE BUSY
---- ----------- ------------- ---------- --------
D000 1 LONDBXDB 1641097 8
Transaction Manageability
The user can roll back Oracle database transactions with the ROLLBACK statement and also during instance recovery by the Oracle database. When the system crashes and instance recovery begins, the SMON process scans all the rollback/Undo segments and recovers the unfinished transactions.
In the case of instance recovery, if the unfinished transactions are large enough, the SMON process spawns parallel transaction recovery servers to recover. Prior to Oracle Database 10g, you could monitor the parallel transaction recovery with two views: v$fast_start_servers and v$fast_start_transactions.
However, you could not monitor normal transaction rollback or transactions recovered by SMON. With the changes introduced in Oracle Database 10g, it is possible to view and monitor the real-time normal transaction rollback and transaction recovery with the SMON process.
Now, you can even view the historical information about the transaction recovery and also work out the average rollback duration. With the current state of the recovery, you can find out how much work has been done and how much work remains. Thus, it becomes possible to estimate transaction recovery time and set the fast_start_parallel_rollback parameter suitably to optimize the system performance.
New Columns:
The view v$fast_start_transactions records information about the progress of the transactions that Oracle is recovering and has recovered. There are certain new columns added to this view, which aids in understanding the identity of the transactions. They are shown in the following table:
| NAME | DATA TYPE | DESCRIPTION |
|---|---|---|
| XID | RAW(8) | Transaction ID |
| PXID | RAW(8) | Transaction ID of the ParentTransaction |
| RCVSERVERS | NUMBER | Servers working on this transaction |
The view v$fast_start_servers provides information about all the recovery servers performing, or that have performed, parallel transaction recovery. One additional column is added to this view: XID, which gives you the transaction ID of the transaction a particular server is working on.
The following statement is used to track the transaction recovery after instance startup. The first output shows that the transaction is recovering and then the second statement output shows that the transaction has recovered. Total undo blocks recovered is shown also shown.
SELECT state, undoblocksdone, undoblockstotal, cputime FROM V$FAST_START_TRANSACTIONS;STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL CPUTIME
-------- -------------- --------------- -------
RECOVERING 324 1145 12?
SQL> /
STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL CPUTIME
-------- -------------- --------------- -------
RECOVERED 1145 1145 28
Changes to v$session_longops:
More operations are being added to v$session_longops. This view displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release. In the 10g release, this view keeps track of ROLLBACK and ROLLBACK TO operations also.
MAXTRANS and Maximum Concurrency:
In previous releases of the Oracle Database, the MAXTRANS values used to represent a physical attribute for objects such as table, index, or cluster. MAXTRANS represented the maximum number of concurrent update transactions for any given data block belonging to the segment.
With Oracle Database 10g, these objects are preconfigured for maximum concurrency. Oracle now allows up to 255 concurrent update transactions for any data block depending on the available space inside the block.
[NOTE: For backward compatibility reasons, even if you specify the MAXTRANS parameter while creating a table, an error is not flagged, and internally the value is ignored.]
In the next section, we will examine the changes regarding the statistics collection mechanism. Oracle Database 10g introduces a statistics collection method for the data dictionary tables, as well as predefined jobs, to gather statistics regularly.
Statistics Collection
It is a well-known practice for database administrators to collect statistics for tables and indexes located in the user or application schemas. With the impending de-support for RBO and wide adaptability of the CBO method, it becomes, more than ever, crucial and significant to collect statistics for the objects.
Oracle Database 10g introduces many new statistics-gathering features. These include the ability to collect data dictionary statistics, as well as many changes in the existing Oracle supplied packages.
The above is an excerpt from the bestselling Oracle10g book Oracle Database 10g New Features by Mike Ault, Madhu Tumma and Daniel Liu, published by Rampant TechPress.
- admin's blog
- Log in to post comments