5.12 Monitoring the Database

The reports available on the Database Monitor page provide a database-wide view of the database sessions, system statistics, SQL statements, and longer operations.

You can use these reports to identify poorly performing SQL and to better understand the workload of the database.

Tip:

To access any of the icons on the Database Monitor page, you must have a database account that has been granted a database administrator (DBA) role.

• Accessing Session Page Reports
  A session is the connection of a user to an Oracle database instance.
• System Statistics
  Displays the System Statistics page statistics.
• Viewing System Statistics
  Learn how to view the System Statistics page.
• About Top SQL Page
  The top SQL statements represent the SQL statements that are executed most often, that use more system resources than other SQL statements, or that use system resources more frequently than other SQL statements.
• Viewing Top SQL Page
  Use the Top SQL page to identify poorly performing SQL.
• About Long Operations Page
  The Long Operations page displays the status of various operations that run for longer than 6 seconds (in absolute time).
• Viewing the Long Operations Page
  Learn how to view the Long Operations page.

5.12.1 Accessing Session Page Reports

A session is the connection of a user to an Oracle database instance.

A session lasts from the time the user connects until the time the user disconnects or exits the database application.

• Accessing Reports on the Session Page
  You must have database administrator privileges to access the Sessions page.
• Creating Sessions Reports
  The Sessions Report displays information about the current sessions in the database.
• Controlling the Number of Rows for Locks Reports
  The Locks report displays a report of sessions which have locks that are blocking other session(s).
• Creating Waits Reports
  The Waits report displays the wait events for each session.
• Creating I/O Reports
  The I/0 report displays details about the I/O for each session.
• Creating SQL Reports
  The SQL report displays details about the current or last SQL statement executed for each session.
• Creating Open Cursors Report
  The Open Cursors report displays details about the number of open cursors for each session.

5.12.1.1 Accessing Reports on the Session Page

You must have database administrator privileges to access the Sessions page.

A session is the connection of a user to an Oracle database instance. A session lasts from the time the user connects until the time the user disconnects or exits the database application.

To access reports on the Sessions page:

1. On the Workspace home page, click SQL Workshop.
2. Click Utilities.
3. Click Database Monitor.
4. Click Sessions.
5. If prompted, enter the appropriate database administrator user name and password and click Login.

   The Sessions page appears.

6. To view a report, select one of the following tabs at the top of the page:

   • Sessions

   • Locks

   • Waits

   • I/O

   • SQL

   • Open Cursors

5.12.1.2 Creating Sessions Reports

The Sessions Report displays information about the current sessions in the database.

Use the controls at the top of page to narrow the view:

• Search. Enter search criteria and click Go. For search details, click the Search label.

• Status. Select a status and click Go.

• Show. Select how many columns to display and click Go.

• Rows. Select the number of rows to appear in the report and click Go.

To view session details, click the Session ID (SID). The Session Details page appears. To remove the current session, navigate to the Session Details page and click Kill Session.

5.12.1.3 Controlling the Number of Rows for Locks Reports

The Locks report displays a report of sessions which have locks that are blocking other session(s).

To control the number of rows that appear, make a selection from the Rows list and click Go.

5.12.1.4 Creating Waits Reports

The Waits report displays the wait events for each session.

Use the controls at the top of page to narrow the view:

• Search. Enter search criteria and click Go. For search details, click the Search label.

• Status. Select a status and click Go.

• Show. Select how many columns to display and click Go.

• Rows. Select the number of rows to appear in the report and click Go.

To view session details, click the Session ID (SID). The Session Details page appears. To remove the current session, click Kill Session.

5.12.1.5 Creating I/O Reports

The I/0 report displays details about the I/O for each session.

Use the controls at the top of page to narrow the view:

• Search. Enter search criteria and click Go. For search details, click the Search label.

• Rows. Select the number of rows to appear in the report and click Go.

To view session details, click the Session ID (SID). The Session Details page appears. To remove the current session, click Kill Session.

5.12.1.6 Creating SQL Reports

The SQL report displays details about the current or last SQL statement executed for each session.

Use the controls at the top of page to narrow the view:

• Search. Enter search criteria and click Go. For search details, click the Search label.

• Status. Select a status and click Go.

• Show. Select how many columns to display and click Go.

• Rows. Select the number of rows to appear in the report and click Go.

To view session details, click the Session ID (SID). The Session Details page appears. To remove the current session, click Kill Session.

5.12.1.7 Creating Open Cursors Report

The Open Cursors report displays details about the number of open cursors for each session.

Use the controls at the top of page to narrow the view:

• Search. Enter search criteria and click Go. For search details, click the Search label.

• Status. Select a status and click Go.

• Rows. Select the number of rows to appear in the report and click Go.

To view details about a specific open cursor count, click the numeric link under the Open Cursor Count column.

To view session details, click the Session ID (SID). The Session Details page appears. To remove the current session, click Kill Session.

5.12.2 System Statistics

Displays the System Statistics page statistics.

The System Statistics page displays statistics for:

• Physical I/O. A physical I/O is an I/O that requires disk access. This report displays disk access statistics for physical reads and writes.

• Logical I/O. A logical I/O is an I/O that is satisfied in memory or disk. Displays the sum of buffer reads which might be consistent gets or current mode gets. Redo is the buffer in the SGA that contains information about changes.

• Memory Statistics. Displays memory consumption of the database.

• Time Statistics. Shows various times consumed by the database.

• SQL Cursor Statistics. Displays statistics about the cursors in the Oracle database.

• Transaction Statistics. Shows the number of transactions performed.

5.12.3 Viewing System Statistics

Learn how to view the System Statistics page.

To view the System Statistics page:

1. On the Workspace home page, click SQL Workshop.
2. Click Utilities.
3. Click Database Monitor.
4. Click System Statistics.
5. If prompted, enter the appropriate administrator user name and password and click Login.

   The System Statistics page appears.

Additional controls on the System Statistics page include:

• Refresh Report - Refreshes the System Statistics report.

• Save Statistics - Saves the current report.

• Show delta between current and saved values - Click this check box to display actual statistic values, or display deltas between a saved value and the current value.

  See Also:

  "Oracle Database Performance Tuning Guide"

5.12.4 About Top SQL Page

The top SQL statements represent the SQL statements that are executed most often, that use more system resources than other SQL statements, or that use system resources more frequently than other SQL statements.

Use the Top SQL page to identify poorly performing SQL.

5.12.5 Viewing Top SQL Page

Use the Top SQL page to identify poorly performing SQL.

To view the Top SQL page:

1. On the Workspace home page, click the SQL Workshop.
2. Click Utilities.
3. Click Database Monitor.
4. Click Top SQL.
5. If prompted, enter the appropriate administrator user name and password and click Login.

   The Top SQL page appears. Use the search fields and lists and the top of the page and click Go to narrow the display. For details on each field or list, click the label.

6. To access the SQL Plan page, click the View icon.

   The SQL Plan page appears, containing the following sections:

   • Query Plan - Contains a color coded explain plan. Note that unindexed columns display in red.

   • SQL Text - Displays the full text of the SQL statement.

   • Indexes - Displays all indexes on the table in the query. There is a checkmark when that index is used in the query.

   • Table Columns - Shows all columns on all tables or views in the query.

5.12.6 About Long Operations Page

The Long Operations page displays the status of various operations that run for longer than 6 seconds (in absolute time).

These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release.

5.12.7 Viewing the Long Operations Page

Learn how to view the Long Operations page.

To view the Long Operations page:

1. On the Workspace home page, click the SQL Workshop.
2. Click Utilities.
3. Click Database Monitor.
4. Click Long Operations.
5. If prompted, enter the appropriate administrator user name and password and click Login.

   See Also:

   "V$SESSION_LONGOPS"in Oracle Database Reference