Defining the Problem

Problems can arise at any time. A proactive DBA watches for problems and corrects them before they are noticed by users. In the past, the discovery and definition step has been tedious and frequently dependent on listening to user feedback. User feedback is important, but is often subjective and not reproducible. In Oracle Database 11g, many of the following information sources can be viewed from the Enterprise Manager interface:

• Monitor the current state of the database instance and compare it to a previous state.
• Use Statspack or AWR to collect performance metrics regularly. Changes can point to issues before they become noticeable to users. 
• Use OS or EM tools to check for CPU and disk queuing, disk utilization, and memory swapping. These are the signs of an overloaded system. 
• Examine AWR or Statspack reports and instance files carefully.
• Use the available tools, such as Statspack or AWR reports, to identify SQL statements in the applications that are consuming the most resources. Have these changed?
• Check the alert logs, and trace files for error messages that might give a quick clue to the nature of the problem. Do not overlook system- and application-specific logs.
• Ensure that the initialization parameter settings make sense for the system. 
• Collect instance and OS statistics. Statspack reports point to components where the greatest waits and the greatest use of resources occur. ADDM goes further by focusing on those components with the greatest potential benefit