Purposes of Monitoring Database Operations

In general, database operation monitoring is useful for the following users:

• DBAs whose responsibilities include identifying expensive (high response time) SQL statements and PL/SQL functions

• DBAs who manage batch jobs in a data warehouse or OLTP system

• Application or database developers who need to monitor the activities related to particular operations, for example, Data Pump operations

Monitoring database operations is useful for performing the following tasks:

• Tracking and reporting

  Tracking requires first defining a composite database operation. When the operation begins, the database infrastructure determines what to track on behalf of the operation. For example, your tuning task may involve determining which SQL statements run on behalf of a specific batch job, what their execution statistics were, what was occurring in the database when the operation was executing, and so on. In Cloud Control, you can view the reports for the composite database operation and for the simple database operations that comprise the composite database operation. You can save the reports to disk.

• Monitoring execution progress

  This task involves monitoring a currently executing database operation. The information is particularly useful when you are investigating why an operation is taking a long time to complete.

• Monitoring resource usage

  You may want to detect when a SQL execution uses excessive CPU, issues an excessive amount of I/O, or takes a long time to complete. With the Oracle Database Resource Manager (Resource Manager), you can configure thresholds for each consumer group that specify the maximum resource usage for all SQL executions in the group. When a SQL operation reaches a specified threshold, Resource Manager can switch the operation into a lower-priority consumer group, terminate the session, or cancel and quarantine the SQL operation. In Cloud Control, you can examine these SQL operations.

  See Also: Oracle Database Administrator’s Guide for more information about consumer group switching by setting resource limits using the Resource Manager

• Tuning for response time

  When tuning a database operation, you typically want to improve the response time. Often the database operation performance issues are mainly SQL performance issues.