All DBAs, at one time or another, worry that their jobs will become obsolete if Oracle delivers on their promise to deliver a self-managing database. Oracle wants to penetrate into smaller-scale Oracle installations where, in practice, no database tuning and maintenance takes place and at the same time serve large enterprises where DBAs now have to manage hundreds of separate servers and instances. In large enterprises, Oracle thinks DBA resources can be better utilized by being more pro-actively involved in the design and architecture of new systems rather than panicking about existing ones that have stopped working properly.

Automatic Manageability Features

A self-management infrastructure would allow the database to learn about itself and use this information to adapt to workload variations and to automatically fix any potential problem. The information required to learn about potential problems is in a persist store called Automatic Workload Repository. A DBA needs to make sure that they have a firm understanding of 10g's Common Manageability Infrastructure before beginning a review of the individual tuning advisors.

Automatic Workload Repository (AWR):

The MMON process generates snapshots of performance statistics once every hour and retains them in the database for 7 days. This data is both in memory and stored in the database.

AWR collects and stores the following statistics:

• Wait events (latch contention, buffer contention, and I/O contention) used to identify performance problems.
• Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
• Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
• Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
• Object access and usage statistics.
• Resource intensive SQL statements

The monitoring and advisory tools listed below use these stats for advisory and reporting purposes.

• Automatic Database Diagnostic Monitor (ADDM)
• SQL Tuning Advisor
• Undo Advisor
• Segment Advisor

What is SYSAUX Tablespace?

Similar to the SYSTEM tablespace, the SYSAUX tablespace is meant to store Oracle System objects. Although the database will not prevent users from placing non-system objects in the tablespace, it is, in general, a good practice for users to avoid doing that. In Oracle 10g, there are around 20 occupants of the SYSAUX tablespace and one of them is Automatic Workload repository (AWR). Other occupants include Oracle Streams, LogMiner, etc. Each occupant has its own space consumption policy. The current amount of space occupied by each SYSAUX occupant can be obtained by querying the V$SYSAUX_OCCUPANTS view. The SPACE_USAGE_KBYTES column shows the number of Kbytes used by each of the occupants.

Sizing the SYSAUX Tablespace:

Size estimates can be obtained differently, based on two following scenarios:

1. Scenario X: You are creating a new SYSAUX tablespace (i.e., either during the creation of a new Oracle 10g database or migrating from a release prior to Oracle 10g), you should use the following rough guidelines. For a small system, allocate 500MB. A system with 2 CPUs, average of 10 active* sessions, and 500 user objects (tables and indexes) is considered a small system. For a medium size system, allocate 2 GB of space. A system with 8 CPUs, average of 20 active sessions, and 5,000 user objects (tables/indexes) is considered a mid-size system. For a large system, allocate 5 GB of space. A system with 32 CPUs, average of 100 active sessions, and 50,000 user objects (tables/indexes) is considered a large system. Note: An active session is a session that is executing a call (i.e., doing work). This is not to be confused with connected sessions, which are the number of logons to the database.
   
2. Scenario Y: Your Oracle 10g database has been running for more than a day. In this case, you can obtain a more accurate estimate of the size of SYSAUX when it reaches its steady state. You can use the SQL script UTLSYXSZ.sql in the $ORACLE_HOME/rdbms/admin directory to estimate the amount of space needed in SYSAUX tablespace, based on the current configuration of the database (e.g., INTERVAL and RETENTION settings of AWR) and an extrapolation from the activities that have been seen in your database since it was created. Please keep in mind that the UTLSYXSZ.sql does not take into consideration user objects that may be created in SYSAUX tablespace.

SQL> Select * from v$sysaux_occupants where occupant name like '%AWR%';
 

If the space used by AWR is exhausted, we can reduce the space consumption by increasing the snapshot interval and reducing the retention period. When reducing the retention period, note that several Oracle self-managing features depend on AWR data function properly. Not having enough data can affect the validity and accuracy of these components

The DBA can control the interval and retention of snapshot generation by the dbms_workload_repository.modify_snapshot_settings procedure. For example:

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (21600, 30);

In above example, the retention period is specified as 15 days (21600 min) and the interval between each snapshot is 30 min.

Note that taking manual snapshots is also supported in conjunction with the automatic snapshots that the system generates. For this, the dbms_workload_repository.create_snapshot procedure is used.

The snapshots are used for computing the rate of change of a statistic. This is mainly used for performance analysis. A snapshot sequence numer (snap_id) identifies each snapshot, which is unique within the Workload Repository.

Typically, the DBA would be able to view the AWR data through Oracle Enterprise Manager or AWR reports. However, we can view the statistics by querying the following views as well.

1. V$ACTIVE_SESSION_HISTORY: This view displays the active database session activity, sampled once every second.
2. V$ metric views provide metric data to track the performance of the system. The metric views are organized into various groups, such as event, event class, system, session, service, file, and tablespace metrics. These groups are identified in the V$METRICGROUP view.
3. DBA_HIST views: The DBA_HIST views contain historical data stored in the database.

AWR Report: To generate a text report for a range of snapshot Ids, run the awrrpt.sql script at the SQL prompt:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Enter value for report_type: text

Enter value for num_days: 2

After the list displays, you are prompted for the beginning and ending snapshot Id for the workload repository report.

Enter value for begin_snap: 100

Enter value for end_snap: 120

Next, accept the default report name or enter a report name. The default name is accepted in the following example:

Enter value for report_name:

Using the report name awrrpt_1_100_120

The workload repository report is thus generated.

Below is a sample output of the AWR Report.

WORKLOAD REPOSITORY report for

Report Summary

Cache Sizes (end)

Load Profile

Instance Efficiency Percentages (Target 100%)

Shared Pool Statistics

Top 5 Timed Events

AWR Compare Period Report: AWR compare period report provides comparison of two snapshot periods. Output results of this report are ordered by the statistical differences between the periods. Statistics are normalized by "DB time". AWR Compare Period Report is generated by running AWRDDRPT.sql

AWR ASH Report: One of the important components of the AWR repository is the Active Session History (ASH), which collects every second samples of active sessions (waiting for non-idle events, or on the CPU working) from v$session (inactive sessions are not captured). When saved in the AWR repository, by default 1 in 10 samples of the ASH data are saved persistently. This information provides ADDM with the data to drill-down on problems identified. For example, for "db file scattered read" wait event, the ASH data helps the advisor identify the specific blocks and files that are referenced the most when the wait event was present. Views to access information are v$active_session_history and dba_hist_acitve_session_history. ASH report can be generated by running ashrpot.sql, which gives detailed analysis of data along time, session’s etc...

AWR SQL Detail Report: This report provides the information about one or more SQL statements over a snapshot range. This is very useful especially for finding plan changes over a period. This report can be generated by running AWR SQRPT.sql

Automatic System Tasks:

Oracle Scheduler enables a DBA to create time windows, during which jobs are automatically run. A typical Scheduler window defines a start time, duration, and optionally a resource plan to activate. A Scheduler job can then name a window as its schedule. In addition, windows can be combined into window groups, and if a job names a window group as its schedule instead of naming a window, the job runs whenever any of the windows in the window group opens.

Two Scheduler windows are predefined upon installation of Oracle Database:

• WEEKNIGHT_WINDOW starts at 10 p.m. and ends at 6 a.m. every Monday through Friday.
• WEEKEND_WINDOW covers whole days Saturday and Sunday.

Together, these windows constitute the MAINTENANCE_WINDOW_GROUP in which all system maintenance tasks are scheduled. Oracle Database uses the maintenance windows for automatic statistics collection, for space management, and for some other internal system maintenance jobs.

Predefined Automatic System Tasks

The following jobs are among the automatic system tasks that are predefined to run in the maintenance windows:

Automatic Statistics Collection Job

A Scheduler job, GATHER_STATS_JOB, is predefined upon installation of Oracle Database. GATHER_STATS_JOB collects optimizer statistics for all objects in the database for which there are no statistics or only stale statistics.

If you prefer to manage statistics collections manually, then do the following:

EXECUTE DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

Automatic Segment Advisor Job

A Scheduler job, AUTO_SPACE_ADVISOR_JOB, is also predefined upon installation. AUTO_SPACE_ADVISOR_JOB runs the Automatic Segment Advisor, which identifies segments that have space available for reclamation, and then makes recommendations that you can view with Enterprise Manager or a set of PL/SQL package procedures.

You can run the Segment Advisor manually to obtain more up-to-the-minute recommendations or to obtain recommendations on segments that the Automatic Segment Advisor did not examine for possible space reclamation

In conclusion:

This article has discussed two concepts which are an integral pieces of the server self manageability functionality, AWR and AST (Automatic System Tasks). As you know, these features reduce the cost of managing the IT environment by greatly reducing configuration and intuitive day-to-day administration but it does require someone who is familiar with this architecture. The pressure of cost reduction no longer allows the growth of a DBA's staff in the enterprises to keep pace with database growth rate, which results in what some refer to as the "Management Gap". DBAs need better tools that increase their productivity and help automate many of their manual tasks. Irrespective of how many tasks are being automated, it is critical to both large and small companies that there is always going to be a need for someone who understands how the database server actually works, especially where organizations push the database to its limits.

Back to DBAsupport.com