Any proactive action refers to preventive measure. Reactive active refers applying a solution when problem arises.
Oracle 10g DBA has to respond to the problems reactively and proactively. Reactive monitoring involves monitoring a database environment after a performance or management issue has arisen. Most of the new features in Enterprise Manager (EM) Database Control are geared toward proactive monitoring.
The monitoring tools available in EM Database Control collect their information from a variety of sources such as data dictionary views, dynamic performance views, and the operating system. In addition, cost based optimizer statistics are also used in Oracle. All these sources of information are accessed by the Automatic Workload Repository feature.
Oracle 10g introduces two new background processes—Memory Monitor (MMON) and Memory Monitor Light (MMNL). These processes work together to collect performance statistics directly from the System Global Area (SGA). The MMON process does most of the work by waking up every 60 minutes and gathering statistical information from the data dictionary views, dynamic performance views, and optimizer and then storing this information in the database.
The tables that store these statistics are called the Automatic Workload Repository (AWR). These tables are owned by the user SYSMAN and are stored in the SYSAUX tablespace.
To activate the AWR feature, you must set the PFILE/SPFILE parameter STATISTICS_LEVEL to the appropriate value. The values assigned to this parameter determine the depth of the statistics that the MMON process gathers. The following table shows the values that can be assigned to the STATISTICS_LEVEL parameter.
|BASIC||Disables the AWR and most other diagnostic monitoring and advisory activities. Few database statistics are gathered at each collection interval when operating the instance in this mode.|
|TYPICAL||Activates the standard level of collection activity. This is the default value for AWR and is appropriate for most environments.|
|ALL||Captures all the statistics gathered by the TYPICAL collection level, plus the execution plans and timing information from the operating system.|
Once gathered, the statistics are stored in the AWR for a default duration of 7 days. However, both the frequency of the snapshots and the duration for which they are saved in the AWR can be modified.
SQL> execute dbms_workload_repository.modify_snapshot_settings
PL/SQL procedure successfully completed.
The 30-day retention value shown above is expressed in minutes: 60 minutes per hour ? 24 hours per day ? 30 days = 43,200 minutes.
Gathering snapshots too frequently requires additional space in the SYSAUX tablespace and adds additional database overhead each time the statistics are collected. Once AWR snapshots are taken and stored in the database, the Automatic Database Diagnostic feature uses the statistics as described in the next section.
Following each AWR statistics collection process, the Automated Database Diagnostic Monitoring (ADDM) feature automatically analyzes the gathered statistics and compares them to the statistics gathered by the previous two AWR snapshots. By comparing the current statistics to these two previous snapshots, the ADDM can easily identify potential database problems such as CPU and I/O bottlenecks, resource- intensive SQL or PL/SQL, lock contention, and the utilization of Oracle’s memory structures within the SGA.
Based on these findings, the ADDM may recommend possible remedies. The goal of these recommendations is to minimize DB Time. DB Time is composed of two types of time measures for non-idle database users: CPU time and wait time. This information is stored as the cumulative time that all database users have spent either using CPU resources or waiting for access to resources such as CPU, I/O, or Oracle’s memory structures. High or increasing values for DB Time indicate that users are requesting increasingly more server resources and may also be experiencing waits for those resources, which can lead to less than optimal performance. In this way, minimizing DB Time is a much better way to measure overall database performance than Oracle’s old ratio-based tuning methodologies.
DB Time is calculated by combining all the times from all non-idle user sessions into one number. Therefore, it is possible for the DB Time value to be larger than the total time that the instance has been running.
Once ADDM completes its comparison of the newly collected statistics to the previously collected statistics, the results are stored in the AWR. You can use these statistics to establish baselines against which future performance will be compared, and you can use deviations from these baseline measures to identify areas that need attention. In this manner, ADDM allows you to not only better detect and alert yourself to potential management and performance problems in the database, but also allows you to take corrective actions to rectify those problems quickly and with little or no manual intervention.
EM Database Control graphically displays the results of the ADDM analysis on several screens, including: The Performance Findings link under the Diagnostic Summary section of the EM Database Control main screen The Performance tab of the EM Database Control main screen The ADDM screen located by clicking the Advisor Central link at the bottom of the EM Database Control main screen
The EM Database Control main screen contains a section called Diagnostic Summary. One of the links under this section is called Performance Findings.
ADDM suggests three options for improving the performance of the I/O on this system: Stripe and mirror (also known as SAME) all datafiles across multiple disk drives. Increase the number of physical disk drives. Consider implementing Oracle’s Automatic Storage Management feature.
The SAME, or Stripe and Mirror Everything, methodology refers to a database file configuration strategy that is described in this white paper on the Oracle Technology Network: http://otn.oracle.com/ deploy/availability/pdf/OOW2000_same_ppt.pdf.
ADDM has essentially identified the SALES_HISTORY table as the source of excessive I/O and recommends that you run the Segment Advisor utility against this table to generate recommendations for improving its performance.
You can also click the Performance tab on the EM Database Control main screen to view performance data collected by AWR and analyzed by ADDM. ADDM uses its findings to populate the Sessions: Waiting And Working section of the Performance screen, Using this section of the Performance screen, you can drill down into detailed information in 11 areas that have been identified as having an impact on performance, from User I/O thorough CPU Used. By clicking the User I/O link, you can drill down into detailed information about user I/O.
The lines on the graph show which of the events in the graph’s legend experienced the most activity during that snapshot period. The graph output indicates that most of the user I/O activity is experiencing waits for the database event "db file scattered read." This event is caused by the I/O activity that occurs when Oracle experiences a wait while performing a sequential disk read of contiguous blocks from a datafile into the buffer cache—usually when a table is being accessed using a full table scan or fast full index scan.
The Performance screen also contains a Performance Overview section near the bottom that summarizes, in pie graphs, the top SQL and top session wait events identified by ADDM.
Clicking the links in the boxes next to either of these graphs displays details about that item. For example, clicking the link for the SQL statement that experienced the most wait time (35 percent on the graph).
Clicking the link at the bottom of this same screen allows you to view the execution plan for this statement.
If the execution plan for this query shows that a full table scan of the 900,000+ row SALES_ HISTORY table is occurring, then you can see how it might experience I/O waits while retrieving its rows. To view the tuning recommendations that ADDM has generated for this statement, click the Run SQL Tuning Advisor button at the bottom of the screen.
The Advisor Central screen also contains ADDM findings. The link for the Advisor Central screen is at the bottom of the EM Database Control main screen. Click this link to display the Advisor Central screen, the top portion of which is shown in
Click the ADDM link in the Advisors section of this screen to display a graph, shown in
As stated earlier, the ADDM automatically compares the most recent AWR snapshot with the last two AWR snapshots when formulating its recommendations. However, you can use this Create ADDM Task screen to manually select any two AWR snapshot times and formulate ADDM recommendations for activity that occurred between those two points in time. To start this process, click the Period Start Time radio button and then select a start date and time by clicking the point in the graph’s timeline that corresponds to the beginning period that you want to use. Repeat this process to specify the end process time stamp.
Click OK to analyze the database for possible performance problems between the two specified points in time. You can also manually perform an ADDM analysis without the use of EM Database Control by using the addmrpt.sql script located in $ORACLE_HOME/rdbms/ admin on Unix systems and %ORACLE_HOME%\rdbms\admin on Windows systems. See Chapter 6 of Oracle Database Performance Tuning Guide 10 g Release 1 (10.1), Part Number B10752-01, for details on how to use this script. The results of this analysis is displayed at the bottom of the ADDM screen that is displayed when the analysis is complete.
Although using EM Database Control to view ADDM results is by far the simplest way to review ADDM recommendations, you can also query the ADDM data dictionary views directly as well. Some of these data dictionary views are discussed in the following section.
You can use more than 20 data dictionary views to examine the results of ADDM’s activities. Four commonly used ADDM views that store the recommendation information we saw in the EM Database Control pages are described in the following table.
|DBA_ADVISOR_FINDINGS||Describes the findings identified by the ADDM analysis|
|DBA_ADVISOR_OBJECTS||Describes the objects that are referenced in the ADDM findings and recommendations|
|DBA_ADVISOR_RECOMMENDATIONS||Describes the recommendations made based on ADDM findings|
|DBA_ADVISOR_RATIONALE||Describes the rationale behind each ADDM finding|
The following SQL statement shows a sample query on the DBA_ADVISOR_FINDINGS data dictionary view that identifies the type of performance problem that is causing the most impact on the database:
SQL> SELECT task_id, type, message
2 FROm dba_advisor_findings
3 WHERE impact= (select MAX(impact) FROM dba_advisor_findings);
TASK_ID TYPE MESSAGE
------- -------- -------------------------------------------------
164 PROBLEM SQL statements consuming significant database time were found.
The output from this query shows that SQL statements being executed in the database are contributing to the poor database performance. By itself, the DBA_ADVISOR_FINDINGS data dictionary view does not identify which SQL statements are consuming the database time. Instead, these are shown in the DBA_ADVISOR_OBJECTS data dictionary view and are identified by the TASK_ID value shown in the query on DBA_ADVISOR_FINDINGS. A query on that view, using the TASK_ID of 164 returned by the ADDM session that had the potential for the greatest database impact, returns the SQL statements shown here:
SQL> SELECT attr4
2 FROM dba_advisor_objects
3 WHERE task_id = 164;
UPDATE customers SET credit_limit=credit_limit*1.15 WHERE cust_id=:B1
DELETE FROM sales WHERE time_id BETWEEN ’01-JAN-00’ and ’01-
JAN-01’; UPDATE sales_history SET quantity_sold =
CHANNEL_ID := B1
SELECT COUNT(*) FROM Sales_history;
SELECT DISTINCT channel_id FROM sales_history;
This query shows all the SQL statements that were captured by the AWR during the snapshot period and that were used in the ADDM analysis for that same period.
The DBA_ADVISOR_ACTIONS data dictionary view shows the ADDM recommendations for each finding. The following query shows the recommendations for correcting the performance issues associated with TASK_ID 164, which was identified earlier as being the costliest database activity:
SQL> SELECT TRIM(attr1) ATTR1, TRIM(attr2) ATTR2, TRIM(attr3) ATTR3
2 FROM dba_advisor_actions
3 WHERE task_id = 164;
ATTR1 ATTR2 ATTR3
---------- ------ ----------
log_buffer 262144 15728640
db_cache_size 25165824 50331648
undo_retention 900 363
This output indicates that ADDM recommends that the values for LOG_BUFFER, DB_CACHE_ SIZE, and UNDO_RETENTION all be changed from their current values to 15,728,640 bytes, 50,331,648 bytes, and 363 seconds, respectively.
If you want to see the rationale behind each of the actions shown in DBA_ADVISOR_ACTIONS, query the DBA_ADVISOR_RATIONALE data dictionary view. The DBA_ADVISOR_RATIONALE view stores the ADDM recommendations that ADDM has formulated based on the AWR data like those stored in DBA_ADVISOR_FINDINGS and DBA_ADVISOR_OBJECTS. The following example shows a sample query on the DBA_ADVISOR_RATIONALE view using the TASK_ID of 164 identified earlier:
SQL> SELECT message
2 FROM dba_advisor_rationale
3 WHERE task_id = 164;
Buffer cache writes due to small log files were consuming
significant database time.
The buffer cache was undersized causing significant read I/O. The value of "undo retention" was 900 seconds and the longest running query lasted only 330 seconds. This extra retention caused unnecessary I/O.
As you can see from the complexity of these examples, examining the ADDM results via the EM Database Control is much easier than accessing the data dictionary views via SQL. From a practical standpoint, you would run SQL queries against these ADDM views only if the EM Database Control were unavailable.
To gain further insight into the recommendations and information gathered by the ADDM, Oracle 10g also provides several advisor utilities in the EM Database Control.