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 | |
|
Setting up a Long-Running Statement | |
|
Viewing Monitored SQL Executions | |
|
Viewing Monitored SQL Execution Details | |
|
Viewing Additional Pages: Report, Session and SQL | |
|
Cleanup | |
|
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.
Before starting this tutorial, you should first complete the following steps:
| 1. | Ensure that the EM DB Console is up and accessible through a web browser. You can do this as oracle OS user in a terminal window, by entering: emctl status dbconsole
Note the host name and port. You will use it later.
|
|
| 2. | Download and unzip the sqlmoni.zip file into your working directory which contains the files needed to perform this tutorial.
|
|
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. | Navigate to your working directory. Open SQL*Plus. Login as the sys user and execute the moni_setup.sql script. sqlplus / as sysdba @moni_setup
Continue with this tutorial while this SELECT statement is running. |
|
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:
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
|
| 3. | To view long-running SQL statements, perform the follwoing steps: a. Click the Performance tabbed page.
b. You should see system activity. Your graph will look different.
c. Scroll to the bottom of the page and click SQL Monitoring in the Additional Monitoring Links section.
d. The Monitored SQL Executions page is displayed.
|
To view details about your SQL execution, perform the following steps:
| 1. | On the Monitored SQL Execution page, click the SQL ID link.
|
| 2. | The Monitored SQL Execution Details page is displayed.
In the top part of the page, you see Overview information. In the top right view you see IO and Wait Statistics.
|
| 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.
To the right you see more details of the Plan Statistics page.
|
| 4. | Click the Activity tabbed page.
You see on this graph that your SELECT statement is using all CPU resources.
|
To view additional monitoring information, perform the following steps:
| 1. | On your Monitored SQL Execution Details page, click View Report in the top right.
|
| 2. | The SQL Monitoring Report is displayed with the same information that you saw earlier on the graphic pages. Scroll down.
|
| 3. | At the bottom you see the SQL Plan Monitoring Details. Click OK.
|
| 4. | On your Monitored SQL Execution page, click the session link to display session information for the current user.
|
| 5. | Review the session information.
There are several tabbed pages. When you finished reviewing the session information, click the SQL Monitoring tabbed page on the right.
|
| 6. | Click the SQL ID to return to the Monitored SQL Execution Details page.
|
| 7. | To view SQL details, click the SQL ID on the Monitored SQL Execution Details page.
|
| 8. | Review the SQL details.
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
|
In this tutorial, you've learned how to use the EM DB Control to view the execution of SQL statements in real time.
Move your mouse over this icon to hide all screenshot