Monitoring SQL Statements in Real Time Using Enterprise Manager Database Console

This tutorial shows you how to monitor SQL statements in real time using the Enterprise Manager (EM) Database (DB) Console.

Approximately 30 minutes

This tutorial discusses the following:

Overview
Prerequisites
Summary

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so, depending on your Internet connection, may result in a slow response time.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

In the Oracle Database 11gR2, the SQL Monitoring feature has a new graphical interface. It is not only available from the Grid Control, but also from EM DB Control. By default, SQL monitoring is automatically started when a SQL command runs parallel, or when it has consumed at least five seconds of the CPU or I/O time in a single execution.

After monitoring is initiated, an entry is added to the dynamic performance V$SQL_MONITOR view. This entry tracks key performance metrics collected for the execution, including the elapsed time, CPU time, number of reads and writes, I/O wait time, and various other wait times. These statistics are refreshed in near real time as the command executes, generally once every second.

After the execution ends, monitoring information is not deleted immediately, but is kept in the V$SQL_MONITOR view for at least one minute. The entry is eventually deleted so its space can be reclaimed as new commands are monitored.

Back to Topic List

Before starting this tutorial, you should first complete the following steps:

1.
2.

This tutorial requires the creation of the MONI_TBS tablespace (100 MB), the MONI DBA user and the MONI_TEST table.

Note: In this practice, simple and easy to remember passwords are used in order not to detract from the purpose of the exercise. In real development and production environments, use strong passwords following the guidelines in Oracle Database Security Guide. The practice passwords are oracle for the SYS user and moni for the MONI user.

Perform the following:

1.

NOTE: Your timings maybe different than the timings in these screenshots.

To view a long-running SQl statement with the EM DB control interface, perform the following steps:

1.

Start a web browser and enter your URL in the following format:
https://<your host name>:<port>/em, for example:

Move your mouse over this icon to see the image

In Oracle classroom environments, you may receive a security warning about the website certificate, which originates in the fact that you have been given a newly installed server. Accept the certificate (permanently, if possible) or if asked, add it to your website exception. The exact messages (and the number of windows) depend on your browser.

 

2.

On the Database Login page, enter SYS as User Name, your Password, select SYSDBA from the Connect As drop-down list, and then click Login

Move your mouse over this icon to see the image

 

3.

To view long-running SQL statements, perform the follwoing steps:

a. Click the Performance tabbed page.

Move your mouse over this icon to see the image

b. You should see system activity. Your graph will look different.

Move your mouse over this icon to see the image

c. Scroll to the bottom of the page and click SQL Monitoring in the Additional Monitoring Links section.

Move your mouse over this icon to see the image

d. The Monitored SQL Executions page is displayed.

Move your mouse over this icon to see the image

 

To view details about your SQL execution, perform the following steps:

1.

On the Monitored SQL Execution page, click the SQL ID link.

Move your mouse over this icon to see the image

 

2.

The Monitored SQL Execution Details page is displayed.

Move your mouse over this icon to see the image

In the top part of the page, you see Overview information. In the top right view you see IO and Wait Statistics.

Move your mouse over this icon to see the image

 

3.

The Details section has as a minimum two tabbed pages: Plan Statistics and Activity. If you have Parallel execution of queries, then there is also a Parallel tabbed page.

Move your mouse over this icon to see the image

To the right you see more details of the Plan Statistics page.

Move your mouse over this icon to see the image

 

4.

Click the Activity tabbed page.

Move your mouse over this icon to see the image

You see on this graph that your SELECT statement is using all CPU resources.

Move your mouse over this icon to see the image

 

To view additional monitoring information, perform the following steps:

1.

On your Monitored SQL Execution Details page, click View Report in the top right.

Move your mouse over this icon to see the image

 

2.

The SQL Monitoring Report is displayed with the same information that you saw earlier on the graphic pages. Scroll down.

Move your mouse over this icon to see the image

 

3.

At the bottom you see the SQL Plan Monitoring Details. Click OK.

Move your mouse over this icon to see the image

 

4.

On your Monitored SQL Execution page, click the session link to display session information for the current user.

Move your mouse over this icon to see the image

 

5.

Review the session information.

Move your mouse over this icon to see the image

There are several tabbed pages. When you finished reviewing the session information, click the SQL Monitoring tabbed page on the right.

Move your mouse over this icon to see the image

 

6.

Click the SQL ID to return to the Monitored SQL Execution Details page.

Move your mouse over this icon to see the image

 

7.

To view SQL details, click the SQL ID on the Monitored SQL Execution Details page.

Move your mouse over this icon to see the image

 

8.

Review the SQL details.

Move your mouse over this icon to see the image

When you are finished, click Logout in the top-right part of the page.

To clean up your environment, perform the following step:

1.

When your long-running query is finished, return to your SQL*Plus session. Connect as SYSDBA and then execute the moni_cleanup.sql script:

connect / as sydba

@moni_cleanup

Move your mouse over this icon to see the image

Move your mouse over this icon to see the image

 

In this tutorial, you've learned how to use the EM DB Control to view the execution of SQL statements in real time.

Back to Topic List

Move your mouse over this icon to hide all screenshot