Oracle 10g: A Self Managed Database?
August 10, 2006
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:
The monitoring and advisory tools listed below use these stats for advisory and reporting purposes.
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:
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.
AWR Report: To generate a text report for a range of snapshot Ids, run the
First, you need to specify whether you want an HTML or a text report.
Enter value for report_type: text
Specify the number days for which you want to list snapshot Ids.
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
Cache Sizes (end)
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, sessions 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:
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,
If you prefer to manage statistics collections manually, then do the following:
Automatic Segment Advisor Job
A Scheduler job,
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
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.