Lesson 1

In the previous module, we examined tuning using Oracle data structures including

1. abstract data types,
2. VARRAYs, and
3. nested tables.

We will now turn our attention to tuning using the Oracle data dictionary. During the time period of Oracle8i, Oracle provided a limited tool set for monitoring their database. That has since changed. As a result, many third party monitoring tools have appeared. These include the Q product by Savant, DB-Vision by Platinum, Patrol by BMC, and a host of others. However, Oracle does provide us with the tools to view the events within the active Oracle instance and create our own monitors and alert mechanisms. The next lesson looks at the tools Oracle provides for viewing the performance of our database.
By the time you complete this module you should be able to:

1. Use Oracle performance views for monitoring
2. Write an SGA snapshot routine to query the V$ views
3. Build a quick and effective Oracle alert monitor

The Oracle Database makes it easy to proactively monitor the health and performance of your database. It monitors the vital signs (or metrics) related to database health, analyzes the workload running against the database, and automatically identifies any issues that need your attention as an administrator. The identified issues are either presented as alerts and performance findings in Enterprise Manager or, if you prefer, can be sent to you through e-mail.This section discusses the following topics:

1. Alerts
2. Performance Self-Diagnostics: Automatic Database Diagnostics Monitor
3. Monitoring General Database State and Workload
4. Managing Alerts

• Oracle's Data Dictionary
  Oracle's data dictionary stores all the information that is used to manage the objects in the database. Although the data dictionary is usually the domain of database administrators (DBAs), it also is a source of valuable information for developers and end users. The data dictionary is Oracle's internal directory from the perspective of an end user. The data dictionary tables and views are not listed alphabetically, but rather are grouped by function (i.e., tables, security, etc.).
  This organization is designed to let you quickly find your way to the information you need. Depending on which Oracle configuration options you are using, some of the groups will not apply to your database. The most commonly used views are listed first. As new features are added, it is expected that the data dictionary will be modified with each future release of Oracle. This is not intended to be an exhaustive list of all the data dictionary views. Those listed here are the most commonly accessed by user queries.

With some exceptions, the names of the objects in the Oracle data dictionary begin with one of three prefixes:

1. USER,
2. ALL,or
3. DBA.

Records in the USER views usually show information about objects owned by the account performing the query. Records in the ALL views include the USER records plus information about objects on which privileges have been granted to PUBLIC or to the user. DBA views encompass all of the database objects, regardless of owner. For most database objects, USER, ALL, and DBA views are available.In keeping with the user focus of this module, the emphasis will be on the USER views or those that are accessible to all users. The "ALL" and "DBA" views will be described when they are applicable.