Monitoring General Database State and Workload

The Database Home page enables you to monitor the state and workload of your database. It provides a central place for general database state information and is updated periodically.

To monitor the general database state and workload:

1. Go to the Database Home page.

   See "Accessing the Database Home Page".

2. (Optional) Click the Refresh button to update the information displayed.

   By default, the Database Home page automatically refreshes every 60 seconds. You can prevent automatic refresh by selecting Manually in the View Data list at the top right-hand corner of the page. You must then click Refresh to view the latest information.

   The date and time that data was last collected from the database is displayed to the left of the Refresh button.

3. Get a quick overview of the database state in the General section, which includes the following information:

   • Status of the database instance, Up or Down

     Click the Status link to drill down to database availability details.

   • Time the database was last started

   • Instance name

   • Oracle Database version

   • Host name

     Click the Host link to drill down to host details.

   • Listener name

     Click the Listener link to drill down to listener details.

   Click View All Properties to see the Oracle home path and whether the database is read-only or read/write.

4. View CPU utilization in the Host CPU section, which includes the following information:

   • Bar chart

     This chart shows the percentage of CPU time used by the database and other processes. The chart legend contains links for the database instance and for other CPU processes.

     Click the Other link in the chart legend to see how the utilization of CPU, memory, and disk I/O change over time.

     Click the instance name link in the chart legend to see the Top Activity page. It includes a graph of active sessions over time, details about SQL statements issued, and the most active sessions.

   • CPU load

     This is the average number of processes waiting to be scheduled for the CPU in the previous minute.

     Click the Load link to see how the utilization of CPU, memory, and disk I/O change over time.

   • Paging

     This is the number of memory pages (fixed-length block of instructions, data, or both) that are paged out (moved out of active memory) each second.

     Click the Paging link to see how the utilization of CPU, memory, and disk I/O change over time.

5. Investigate the Active Sessions section, where you can further explore the cause of performance problems, such as your database taking up most of the CPU time on the server. This section displays a bar graph with the following information:

   • Waits

     This is the value for all wait classes combined, excluding user I/O and idle wait events. Wait classes are groupings of wait events based on the type of wait.

     If other processes are taking up most of your CPU time, then this indicates that some other application running on the database host computer could be causing performance problems.

     Click the Wait link to go to the Performance page to view potential problems inside and outside the database.

   • User I/O

     This is the average number of active sessions waiting for user I/O. User I/O means that the workload originating from the user causes the database to read data from disk or write data to disk.

     Click the User I/O link to go to the Performance page to view potential problems inside and outside the database.

   • CPU

     This is the average active sessions using CPU.

     Click the CPU link to see a chart showing more detailed information about active sessions over time.

6. View the Diagnostic Summary section, which includes the following information:

   • ADDM Findings

     This shows the count of ADDM findings from the most recent ADDM run. Click the number adjacent to the ADDM Findings link to go to the ADDM page.

   • Period Start Time

     This is the start time of the time period most recently analyzed by ADDM. It is shown only if there are ADDM findings.

   • Alert Log

     This is the timestamp of the most recent alert log entry that describes an ORA- error.

     Click the Alert Log link to go to the Alert Log Errors page, which shows a list of log entries that contain errors.

   • Active Incidents

     This shows the count of active incidents, which are occurrences of critical errors in the database. You are encouraged to investigate critical errors and report them to Oracle Support Services. Click the count to go to the Support Workbench home page.

   • Database Instance Health

     Click Database Instance Health to display the Database Instance Health page, which includes graphical timelines of incidents, ADDM findings, and alerts. You can use these graphs for identifying correlations between incidents and alerts generated and performance issues on the system.

7. View the SQL Response Time section.

   This is the current response time of a tracked set of SQL statements as compared to the response time for the reference collection. A reference collection, or SQL Tuning Set, is set of SQL statements that represents the typical SQL workload on your production system. If the current response time and reference collection response time are equal, then the system is running as it should. If the current response time is greater than the reference collection response time, then one or more SQL statements are performing more slowly than they should. The lower the current response time, the more efficiently the tracked SQL statements run.

   Click the SQL Response Time link to see response time metrics for the previous 24 hours. If the reference collection is empty, then click Reset Reference Collection to go to a page where you can create a reference collection.

8. View the Space Summary section.

   If the number adjacent to the Segment Advisor Recommendations label is not zero, then it means the Segment Advisor has identified candidate segments for space defragmentation. Click the number to view recommendations for how to defragment these segments.

9. View the Alerts section, which includes the following items:

   • Category list

     Optionally choose a category from the list to view alerts only in that category.

   • Critical

     This is the number of metrics that have exceeded critical thresholds plus the number of other critical alerts, such as those caused by incidents (critical errors).

   • Warning

     This is the number of metrics that have exceeded warning thresholds.

   • Alerts table

     Click the message to learn more about the alert.

10. View the ADDM Performance Analysis section, if present. This section contains the following items:

    • Period Start Time

      This is the start time of the period most recently analyzed by ADDM.

    • Period Duration in minutes

      This is the duration of the period most recently analyzed by ADDM.

    • Instance name

    • ADDM findings table

      This table lists the ADDM findings, their estimated impact on database performance, a description of the finding, and the number of times the finding occurred in snapshots collected during the previous 24 hours. For example, a finding with Occurrences listed as 34 of 43 has occurred in 34 of the last 43 snapshots.

    Click the finding to view finding details, to view recommendations, and in some cases to implement recommendations or start advisors.

To view database performance over time:

1. At the top of the Database Home page, click Performance.

   The Performance page appears, displaying a summary of CPU utilization, average active sessions, instance disk I/O, and instance throughput for the recent time period.

2. Use the Additional monitoring links to drill down to Top Activity and other data.

The types of actions you can take to improve host performance depends on your system, and can include eliminating unnecessary processes, adding memory, or adding CPUs.