Nalanta

The SYSAUX tablespace is the new feature introduced in Oracle Database 10g. It serves as an auxiliary tablespace to the SYSTEM tablespace. Because it is the default tablespace for many Oracle Database features and products that previously required their own tablespaces.

 

Hence it reduces the number of tablespaces required by the database and also reduces the load on the SYSTEM tablespace. The SYSAUX tablespace is occupied by a number of database components and its total size is governed by the space consumed by those components. The space consumed by the components, in turn, depends on which features or functionality are being used and on the nature of the database workload. The largest portion of the SYSAUX tablespace is occupied by the Automatic Workload Repository (AWR) which is mainly used by Automatic Database Diagnostic Monitor (ADDM) for performance analysis. When problems occur with a system, it is important to perform accurate and timely diagnosis of the problem before making any changes to a system. The statistical data needed for accurate diagnosis of a problem is stored in the Automatic Workload Repository. The Automatic Workload Repository collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. The Automatic Database Diagnostic Monitor will analyze the AWR data on a regular basis and diagnoses the root causes of performance problems. It also provides recommendations for correcting any problems.  

 

This Document would depict the various occupants and the space management of SYSAUX tablespace. Also, this would give a brief idea about AWR and the impact of their configuration over the space management in SYSAUX tablespace.

Key Words: SYSAUX tablespace, AWR, ADDM

 

 

 

 

 

 

TABLE OF CONTENTES

 

 

1.      INTRODUCTION                                                            …………………………… 4

2.      SYSAUX TABLESPACE                                     …………………………….4

2.1.   BENEFITS OF THE SYSAUX TABLESPACE         …………………………….5

2.2.   MANAGING SYSAUX TABLESPACE                   …………………………….5

2.3.   RELOCATING THE SYSAUX OCCUPANTS         …………………………….7

3.      AUTOMATIC WORKLOAD REPOSITORY (AWR)     …………………………….7

3.1.   AWR CONFIGURATION                                        …………………………….8

3.2.   SPACE CONSUMPTION OF AWR IN SYSAUX   …………………………….9

4.      CASE STUDY                                                                  ……………………………11

5.      IMPORTANCE OF AWR FOR ADDM                           ……………………………12

6.      CONCLUSION                                                                ……………………………14

7.      REFERENCES                                                                  ……………………………15

8.      APPENDIX I                                                                     ……………………………16

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database’s data. Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running. A database’s data is collectively stored in the datafiles that constitute each tablespace of the database. Tablespaces are divided into logical units of storage called segments, which are further divided into extents. There are many tablespace that the database must have for its operation. Some of the examples are SYSTEM, UNDO, TEMP tablespaces. Some of the tablespaces are used for the user operations. Some of the tablespaces are used for the features and products of the Oracle Database. The SYSAUX tablespace is one such a tablespace marked as the accommodation of all the special features and products in Oracle 10g. The space allocation of the sysaux tablespace is mainly governed by its occupants which in turn measured by the configuration of those features. So the space management in sysaux tablespace becomes important in Storage point of view.

 

 

 

 

Fig1 – Description about Tablespaces

 

2.

 

The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEM tablespace. Because it is the default tablespace for many Oracle Database features and products that previously required their own tablespaces. The SYSAUX tablespace provides storage of non-sys-related tables and indexes that traditionally were placed in the SYSTEM tablespace. The tables and indexes that were previously owned by the system user can now be specified for a SYSAUX tablespace. With the new SYSAUX tablespace, Oracle comes closer to providing all the needed tablespaces for a truly OFA-compliant database and less number of tablespaces required by the database. It also reduces the load on the SYSTEM tablespace.

 

The SYSAUX tablespace provides a centralized repository for all auxiliary database metadata that does not reside in the SYSTEM tablespace. It is important to note that if this tablespace is unavailable, some of the database features THAT use SYSAUX might fail or function with limited capability.

 

 

2.1. OF THE SYSAUX TABLESPACE

 

Ø      Reduced number of tablespaces:

 

o       As mentioned earlier, some database components that formerly created and used separate tablespaces now occupy the SYSAUX tablespace. For example the XDB and OLAP require their own tablespace as part of the feature installation. Now in Oracle 10g, the need for these additional tablespaces has been removed. The SYSAUX tablespace is the default tablespace for these Oracle features.

 

Ø      Easier RAC management:

 

o       For RAC users who are using raw devices, a separate raw device had to be allocated for each tablespace. Managing these raw devices can be quite a difficult task for a DBA. By consolidating the tablespaces into a single SYSAUX tablespace, the need for many raw devices has also reduced.

 

Ø      Load Reduction on the SYSTEM tablespace

 

o       Since data is now shared with the SYSAUX tablespace there is an improvement in performance of the SYSTEM tablespace.

 

 

2.2. SYSAUX TABLESPACE

 

The SYSAUX tablespace is occupied by a number of database components (refer the table below), and its total size is governed by the space consumed by those components. The space consumed by the components, in turn, depends on which features or functionality are being used and on the nature of the database workload.

 

 

 

 

Component Using SYSAUX	Tablespace in Earlier Releases
Analytical Workspace Object Table	SYSTEM
Enterprise Manager Repository	OEM_REPOSITORY
Log Miner	SYSTEM
Logical Standby	SYSTEM
OLAP API History Tables	CWMLITE
Oracle Data Mining	ODM
Oracle Spatial	SYSTEM
Oracle Streams	SYSTEM
Oracle Text	DRSYS
Oracle Ultra Search	DRSYS
Oracle interMedia ORDPLUGINS Components	SYSTEM
Oracle interMedia ORDSYS Components	SYSTEM
Oracle interMedia SI_INFORMTN_SCHEMA Components	SYSTEM
Server Manageability Components	New in Oracle Database 10g
Statspack Repository	User-defined
Unified Job Scheduler	New in Oracle Database 10g
Workspace Manager	SYSTEM

 

Table1 – Occupants of SYSAUX tablespace

 

We can monitor the occupants of the SYSAUX tablespace using the V$SYSAUX_ OCCUPANTS view. This view lists the following information about the occupants of the SYSAUX tablespace:

 

Ø      Name of the occupant

Ø      Occupant description

Ø      Schema name

Ø      Move procedure

Ø      Current space usage

 

The largest portion of the SYSAUX tablespace is occupied by the Automatic Workload Repository (AWR). The complete description about AWR and its impact of configurations over the SYSAUX tablespace are specified at the later part of the document.

 

Another major occupant of the SYSAUX tablespace is the embedded Enterprise Manager (EM) repository. This repository is used by Oracle Enterprise Manager Database Control to store its metadata. The size of this repository depends on database activity and on configuration-related information stored in the repository. Other database components in the SYSAUX tablespace will grow in size only if their associated features (for example, Oracle UltraSearch, Oracle Text, Oracle Streams) are in use. If the features are not used, then these components do not have any significant effect on the size of the SYSAUX tablespace. The best method to manage is to relocate the occupants to their own tablespaces.

 

 

2.3. THE SYSAUX OCCUPANTS

 

One of the methods of managing the SYSAUX tablespace is to relocate the occupants to its own or another tablespace if it occupies too much space. After the creation of the SYSAUX tablespace, there is a possibility that one or more components use up a lot of its space.

 

At first, we need to get the major occupants of the SYSAUX tablespace by querying SPACE_USAGE_KBYTES column of the V$SYSAUX_OCCUPANTS;

 

SQL> SELECT OCCUPANT_NAME, SPACE_USAGE_KBYTES FROM

   V$SYSAUX_OCCUPANTS;

 

We can also get the appropriate move Procedure by quering the MOVE_PROCEDURE column of the V$SYSAUX_OCCUPANTS view,

 

SQL> SELECT OCCUPANT_NAME, SCHEMA_NAME, MOVE_PROCEDURE FROM

   V$SYSAUX_OCCUPANTS;

 

 Assume that you install Oracle Ultra Search into the default tablespace, which is SYSAUX. Later you discover that Ultra Search is using up too much space. To alleviate this space pressure on SYSAUX, you can call a PL/SQL move procedure specified in the V$SYSAUX_OCCUPANTS view to relocate Ultra Search to another tablespace say DRSYS, then you would issue a statement like:

 

SQL> EXEC WKSYS.MOVE_WK (’DRSYS’);

The move procedure also lets you move a component from another tablespace into the SYSAUX tablespace. If we need to relocate Ultra Search from DRSYS tablespace to SYSAUX tablespace, then you would issue a statement like;

 

SQL> EXEC WKSYS.MOVE_WK (’SYSAUX’); 

 

But we cannot relocate STREAMS, SMC, STATSPACK, ORDIM, ORDIM/PLUGINS, ORDIM/SQLMM and JOB_SCHEDULER occupants.

 

Now we will explore about Automatic Workload Repository and the space management in SYSAUX tablespace by the configuration of AWR.

 

3. WORKLOAD REPOSITORY (AWR)

 

The AWR is the repository of performance information collected by the database to aid in the tuning process for DBAs. Oracle 10g Diagnostic pack includes a built in repository which contains operational statistics about that particular database and other such information. At regular intervals the Oracle database takes a snapshot of all its vital statistics and workload information and stores them in AWR. The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. Replaying these past events can shed light on current problems, so continuously capturing relevant statistics becomes important for performance analysis.

 

Historically people used bstat and estat to collect Oracle statistics over a time period and then compare them to each other. The bstat/estat approach was replaced with statspack available in Oracle 8i. Statspack was a package provided by oracle that did roughly the same thing but better. Statspack has now surpassed in functionality by AWR which is always collecting execution statistics for future analysis and analysis and tuning performed by all of the export components provided Oracle.

 

3.1. CONFIGURATION

 

AWR is automatically installed and running with 10g. The new MMON Process takes snapshots of performance data regular intervals and inserts that data into AWR tables under SYSAUX tablespace. We can use DBMS_WORKLOAD_REPOSITORY procedures to manually create, drop, and modify the snapshots.

 

Fig.2 Structure of AWR

 

The AWR captures not only statistics, but the derived metrics as well. This collection of details in AWR will be helpful for the ADDM to analyze on the performance issues.

 

The report contains useful information like

 

• Wait Events Statistics
• SQL Statistics
• Instance Activity Statistics
• IO Stats
• Buffer Pool Statistics
• Advisory Statistics
• Wait Statistics
• Undo Statistics
• Latch Statistics
• Segment Statistics
• Dictionary Cache Statistics
• Library Cache Statistics
• Memory Statistics
• Streams Statistics
• Resource Limit Statistics
• init.ora Parameters

 

We can manually create snapshots with the CREATE_SNAPSHOT procedure if you want to capture statistics at times different than those of the automatically generated snapshots. We can view this snapshot in the DBA_HIST_SNAPSHOT view.

 

 BEGIN

  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

END;

/

 

3.2. CONSUMPTION OF AWR IN SYSAUX

 

The space consumed by the Automatic Workload Repository is determined by several factors:

 

Ø      Number of active sessions in the system at any given time

Ø      Snapshot interval

o       The snapshot interval determines the frequency at which snapshots are captured. A smaller snapshot interval increases the frequency, which increases the volume of data collected by the Automatic Workload Repository.

Ø      Historical data retention period

o       The retention period determines how long this data is retained before being purged. A longer retention period increases the space consumed by the Automatic Workload Repository.

 

With these default settings, a typical system with an average of 10 concurrent active sessions can require approximately 200 to 300 MB of space for its AWR data. The Automatic Workload Repository space consumption can be reduced by the increasing the snapshot interval and reducing the retention period.

 

To save space, the collected data is automatically purged after 7 days. Both the snapshot frequency and retention time can be modified by the user. To see the present settings, you could use:

 

SQL> SELECT SNAP_INTERVAL, RETENTION FROM DBA_HIST_WR_CONTROL;

 

SNAP_INTERVAL       RETENTION

——————- ——————-

+00000 00:30:00.0   +00007 00:00:00.0

 

The frequency of data collection is 30 minutes by default but that can be adjusted. To change the settings–say, for snapshot intervals of 20 minutes and a retention period of two days–you would issue the following. The parameters are specified in minutes.

 

BEGIN

   DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS

(

      INTERVAL => 20,

      RETENTION => 2*24*60

   )

END;

 

You can drop a range of snapshots using the DROP_SNAPSHOT_RANGE procedure. To view a list of the snapshot Ids along with database Ids, check the DBA_HIST_SNAPSHOT view. For example, you can drop the following range of snapshots:

 

BEGIN

  DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE

(low_snap_id => 22, high_snap_id => 32, dbid => 3310949047);

END;

/

 

When reducing the retention period, note that several Oracle self-managing features depend on AWR data for proper functioning. Not having enough data can affect the validity and accuracy of these components and features, including:

 

• Automatic Database Diagnostic Monitor
• SQL Tuning Advisor
• Undo Advisor
• Segment Advisor

 

If possible, Oracle recommends that you set the AWR retention period large enough to capture at least one complete workload cycle. If your system experiences weekly workload cycles, such as OLTP workload during weekdays and batch jobs during the weekend, you do not need to change the default AWR retention period of 8 days. However if your system is subjected to a monthly peak load during month end book closing, you may have to set the retention period to one month.

 

Now we will have review of one of the Case Study which we faced in Our Environment.

 

4. STUDY

 

PROBLEM

 

We have come across ORA-1688 unable to extend the table SYS.WRH$_LATCH_ CHILDREN in SYSAUX tablespace alert in one of our Production Database. But we could see that the tablespace size is around 16GB but the freespace is only 2MB.

 

TABLESPACE           SIZE_IN_MB FREE_IN_MB MAX_IN_MB  FREE_PCT   FRAGS#

——————-             —————–   ——————   —————-   ————–    ———–

SYSAUX                        16392               2.69                1.94               .02                2

 

RESOLUTION

 

Step 1:

 

So we have started analyzing the occupants and started cleaning up the SYSAUX tablespace. Upon Investigation, we found that almost 8530 AWR reports are occupied in the particular tablespace. When analyzing the configurations we could see the AWR reports are configured with the snap interval of 15 mins and 90 days of retention.

 

SQL> SELECT * FROM DBA_HIST_WR_CONTROL;

 

     DBID         SNAP_INTERVAL              RETENTION                     TOPNSQL

     ——-          ———————–                        —————–                ————–

3915437546   +00000 00:15:00.0             +00090 00:00:00.0                DEFAULT

 

Step 2:

 

As we don’t have the special Diagnostic pack license to Configure AWR for this database, we have decided to drop all the snap shots and disable AWR reports.So we have queried DBA_HIST_SNAPSHOT for the minimum and maximum snap ID of the AWR reports which are currently occupying our tablespace.

 

SQL> SELECTSNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME FROM

DBA­­_HIST_SNAPSHOT ORDER BY SNAP_ID;

 

 

SNAP_ID           BEGIN_INTERVAL_TIME                                      END_INTERVAL_TIME

————            ————————————-                                   ——————————-

12248               16-JUN-08 05.00.35.210 PM                                   16-JUN-08 05.15.59.391 PM

.

 20823              14-SEP-08 04.04.28.725 PM                                   14-SEP-08 04.30.38.846 PM

 

Step 3:

 

Then we have dropped all the snapshots using DBMS_WORKLOAD_REPOSITORY package by specifying the above min & max snap IDs.

 

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE

(LOW_SNAP_ID =>12248, HIGH­_SNAP_ID =>20823);

 

Step 4:

 

After dropping all the AWR reports, we could find almost 90% freespace in the SYSAUX tablespace.

 

TABLESPACE           SIZE_IN_MB   FREE_IN_MB     MAX_IN_MB   FREE_PCT   FRAGS#

——————–             ——————  ——————– ——————  —————–   ————–

SYSAUX                          16522            14979.31             740                  90.66             1441

 

Step 5:

 

Then we have analysed the different ways to disable the AWR reports. Finally, as per the suggestion from Metalink (Doc ID: Note: 436386.1) We have installed and executed dbmsnoawr.plb package.

 

To install, run the package as SYS from SQL*Plus:

SQL> @dbms_noawr.plb

 

To execute the package use the command:

BEGIN

DBMS_AWR.DISABLE_AWR( );

END;

 

RESULT:

 

Now we could see that AWR doesn’t generating any reports and the growth of the SYSAUX tablespace is very minimal and sufficient freespace is available.

 

5. OF AWR FOR ADDM

 

When problems occur with a system, it is important to perform accurate and timely diagnosis of the problem before making any changes to a system. Oftentimes, a database administrator (DBA) simply looks at the symptoms and immediately starts changing the system to fix those symptoms. However, an accurate diagnosis of the actual problem in the initial stage significantly increases the probability of success in resolving the problem.

 

Automatic Database Diagnostic Monitor (ADDM) periodically analyzes the AWR data, locates the root causes of performance problems and provides recommendations for resolving the problems. It also identified non-problem areas of the systems running Oracle Database 10g to help DBAs to avoid misdiagnoses. To enable ADDM to accurately diagnose performance problems, it is important that it has detailed knowledge of database activities and the workload the database is supporting. The information that is stored in AWR is used for ADDM to diagnoses the problem areas.

 

Within the database statistics are gathered automatically every 60 minutes and stored in the automatic workload repository (AWR), in the form of snapshots. The information gathered is very precise. The ADDM is scheduled to run automatically by the MMON background process. Every time a snapshot is taken, the ADDM is triggered to do an analysis of the period corresponding to the last two snapshots.

 

The types of problems that ADDM considers include:

 

• CPU bottlenecks
• Undersized Memory Structures
• I/O capacity issues
• High load SQL statements
• High load PL/SQL execution and compilation, as well as high load Java usage
• RAC specific issues
• Sub-optimal use of Oracle by the application
• Database configuration issues  
• Concurrency issues
• Hot objects and top SQL for various problem areas

 

In addition to problem diagnostics, ADDM recommends possible solutions. When appropriate, ADDM recommends multiple solutions for the DBA to choose from. ADDM considers a variety of changes to a system while generating its recommendations.

 

Recommendations include:

 

• Hardware changes

Adding CPUs or changing the I/O subsystem configuration

• Database configuration

Changing initialization parameter settings

• Schema changes

Hash partitioning a table or index, or using automatic segment-space management (ASSM)

• Application changes

Using the cache option for sequences or using bind variables

• Using other advisors

Running the SQL Tuning Advisor on high load SQL or running the Segment Advisor on hot objects.

 

Fig.3 Relation between AWR and ADDM

 

ADDM runs automatically after each new AWR snapshot is taken over. So the Schedule of ADDM is depends of the AWR Configurations. If the snapshot interval is too low means, more amount of AWR reports will be generated that will affect the tablespace free space. If the snapshot interval is high, then we may not be able to grasp the exact performance issue in the Database. So the configuration of the AWR reports is significant.

 

 

6.

 

            This document is intended to be merely an introduction to the space management in SYSAUX tablespace and a brief description about the elementary aspects of the AWR. Furthermore, an excellent exposition on AWR and ADDM are available at ORACLE Documentations and Oracle supported Sites. Hope this document would give a brief idea about SYSAUX and AWR which would be very helpful to resolve your performance Issues.

 

 

 

 

 

 

 

 

1. Oracle Database 10g New Features: Oracle10g Reference for Advanced Tuning and Administration, by Rampant TechPress
2. Oracle 10g Administration I by Chip Dawes, Bob Bryla, Joseph C Johnson and Matthew Weishan
3. https://metalink.oracle.com – Note:436386.1
4. http://download.oracle.com
5. http://www.acs.ilstu.edu
6. http://neworacledba.blogspot.com
7. https://metalink.oracle.com

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

COMMANDS FOR QUICK REFERENCE

 

1. To query the Occupants of SYSAUX tablespace:

 

SQL> SELECT OCCUPANT_NAME, SPACE_USAGE_KBYTES FROM

 V$SYSAUX_OCCUPANTS;

 

2. To get the appropriate Move Procedure to relocate the occupants:

 

SQL> SELECT OCCUPANT_NAME, SCHEMA_NAME, MOVE_PROCEDURE FROM

   V$SYSAUX_OCCUPANTS;

 

3. To query the snapshot Settings

 

SQL> SELECT SNAP_INTERVAL, RETENTION FROM DBA_HIST_WR_CONTROL;

 

4. To get the snapshot details in AWR:

 

SQL> SELECTSNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME FROM

DBA­­_HIST_SNAPSHOT ORDER BY SNAP_ID;

 

5. To create snapshot for AWR Report

 

BEGIN

             DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

END;

 

 

6. To modify the snapshot settings:

 

BEGIN

              DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS

(          

            INTERVAL => 20,

            RETENTION => 2*24*60

             )

END;

 

7. To Drop the snapshot with snap id:

 

BEGIN

             DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE

(low_snap_id => 22, high_snap_id => 32, dbid => 3310949047);

END;

.