A recent survey of over 300 Oracle customers showed that over 68 percent of customers are waiting for the next release of Oracle Database 11g to upgrade their current databases. This means that Oracle Database 10g will be around for at least another year in many organizations. So database administrators will keep on using database statistics-gathering tools to improve the performance of their databases, till they upgrade to the improved Oracle Database 11g.

The Automatic Workload Repository (AWR) in Oracle Database 10g has a report generation mechanism to produce summary reports based on database statistics stored in the AWR repository, similar to STATSPACK in previous database versions. This analysis is done on statistics over a period of time. All these reports can be generated as html pages or text reports. The awrrpt.sql shows all the AWR snapshots available and prompts you for options on how and where the reports are generated. The awrrpti.sql script displays the statistics for a range of snapshot ids on a specified database and instance. The awrddrpt.sql script shows statistics of a particular SQL statement for a range of snapshot ids, which will help you to debug the performance of a SQL statement. The awrddrpi.sql script shows the statistics of a SQL statement for a range of snapshot ids on a specified database and instance.

Oracle Database 10g metrics are tracked using AWR. Each metric has threshold values associated with them. Whenever a metric value reaches its threshold, the database will generate an alert. The database usage metrics are divided into two broad categories -database feature usage and High Water Mark (HWM) value of certain database attributes. These metrics are useful in getting an idea of any resource usage within the database and for determining how often a particular feature is used. Oracle Database 10g helps to reduce the time spent by the DBA on routine database monitoring tasks by automatically sending messages with information on performance or resources allocation issues and suggesting remedial actions.

Server-generated alerts are of two types - threshold alerts and non-threshold alerts. Database alerts based on threshold levels can be triggered at warning and critical levels. These threshold levels can be set internally or by the customer or customer altered from preset values. These threshold alerts are also known as stateful alerts, which are automatically cleared on fixing the alert condition. Other non-threshold alerts are called stateless alerts. All these alerts can be sent to pagers or email addresses. For sending alerts pushed to a third party tool or your own paging software, subscribe to alert_que with the dbms_aqadm.add_subscriber procedure.

Background processes periodically flush the data to the Automatic Workload Repository. Thus the AWR captures a history of metric values. The alert_history table and alert_que are automatically purged on a periodical basis by the system. All threshold,s except for space-related alerts, should be explicitly defined, or your alerts related to database storage will not be accurate.

Your database should have enough data to provide accurate and valid results with inbuilt monitoring and advisory tools like Automatic Database Diagnostic Monitor, SQL Tuning Advisor, SQL Access Advisor, Undo Advisor, and Segment Advisor. So Oracle recommends that you set a larger AWR retention period to capture at least one complete workload cycle, which could be over a month for most companies. If your databases have weekly workload cycles like major batch jobs during the weekend, you can use the default AWR retention period of 7 days.

When your are upgrading an older version of database to Oracle Database 10g, the optimizer statistics will be calculated for those dictionary tables which lack statistics. The statistics collection will be time-consuming and proportional to the number of dictionary tables whose statistics are lacking or have changed during the upgrade process. When you upgrade from Oracle 9i, you can decrease the database downtime by collecting statistics for the dictionary tables prior to the database upgrade.

About the Author

Dr. Arun Kumar R., data architect at Cingular Wireless, has over a decade of experience in Oracle technologies. He has authored two Oracle Database 10g books and over 50 technical papers. Arun can be reached at arundbta@dbatrends.com.