16 Monitoring Database Operations

Creating a database operation involves explicitly defining its beginning and end points. You can start a database operation by using the DBMS_SQL_MONITOR.BEGIN_OPERATION function and end the operation by using the DBMS_SQL_MONITOR.END_OPERATION procedure.

Assumptions

This tutorial assumes the following:

• You are an administrator and want to query the number of items in the sh.sales table and the number of customers in the sh.customers table.

• You want these two queries to be monitored as a database operation named sh_count.

To create a database operation:

1. Start SQL*Plus and connect as a user with the appropriate privileges.

2. Define a variable to hold the execution ID.

   For example, run the following SQL*Plus command:

   VAR eid NUMBER
   

3. Begin the database operation.

   For example, execute the BEGIN_OPERATION function as follows:

   EXEC :eid := DBMS_SQL_MONITOR.BEGIN_OPERATION('sh_count');
   

4. Run the queries in the operation.

   For example, run the following statements:

   SQL> SELECT count(*) FROM sh.sales;
    
     COUNT(*)
   ----------
       918843
    
   SQL> SELECT COUNT(*) FROM sh.customers;
    
     COUNT(*)
   ----------
        55500
   

5. End the database operation.

   For example, execute the END_OPERATION procedure as follows:

   EXEC DBMS_SQL_MONITOR.END_OPERATION('sh_count', :eid);
   

6. Confirm that the database operation completed.

   For example, run the following query (sample output included):

   SELECT SUBSTR(DBOP_NAME, 1, 10), DBOP_EXEC_ID,
          SUBSTR(STATUS, 1, 10)
   FROM  V$SQL_MONITOR 
   WHERE DBOP_NAME IS NOT NULL
   ORDER BY EXEC_ID;
   
   DBOP_NAME     EXEC_ID STATUS
   ---------- ---------- ----------
   sh_count            1 DONE
   

By default, AWR automatically captures SQL monitoring reports in XML format. The reports capture only SQL statements that are not executing or queued and have finished execution since the last capture cycle. AWR captures reports only for the most expensive statements according to elapsed execution time.

The Monitored SQL Executions page in Cloud Control summarizes the activity for monitored statements. You can use this page to drill down and obtain additional details about particular statements. The Monitored SQL Executions Details page uses data from several views, including the following:

• GV$SQL_MONITOR

• GV$SQL_PLAN_MONITOR

• GV$SQL_MONITOR_SESSTAT

• GV$SQL

• GV$SQL_PLAN

• GV$ACTIVE_SESSION_HISTORY

• GV$SESSION_LONGOPS

• DBA_HIST_REPORTS

• DBA_HIST_REPORTS_DETAILS

Assumptions

This tutorial assumes the following:

• The user sh is executing the following long-running parallel query of the sales made to each customer:

  SELECT c.cust_id, c.cust_last_name, c.cust_first_name, 
         s.prod_id, p.prod_name, s.time_id
  FROM   sales s, customers c, products p
  WHERE  s.cust_id = c.cust_id
  AND    s.prod_id = p.prod_id
  ORDER BY c.cust_id, s.time_id;
  

• You want to ensure that the preceding query does not consume excessive resources. While the statement executes, you want to determine basic statistics about the database operation, such as the level of parallelism, the total database time, and number of I/O requests.

• You use Cloud Control to monitor statement execution.

  Note:

  To generate the SQL monitor report from the command line, run the REPORT_SQL_MONITOR function in the DBMS_SQLTUNE package, as in the following sample SQL*Plus script:

  VARIABLE my_rept CLOB
  BEGIN
    :my_rept :=DBMS_SQLTUNE.REPORT_SQL_MONITOR();
  END;
  /
  PRINT :my_rept
  

To monitor SQL executions:

1. Access the Monitored SQL Executions page, as described in "Monitored SQL Executions Page in Cloud Control".

   In the following graphic, the top row shows the parallel query.

   
   Description of the illustration mon_sql_exec_main.jpg

   In this example, the query has been executing for 1.4 minutes.

2. Click the value in the SQL ID column to see details about the statement.

   The Monitored SQL Details page appears.

   
   Description of the illustration mon_sql_exec_details.gif

   The preceding report shows the execution plan and statistics relating to statement execution. For example, the Timeline column shows when each step of the execution plan was active. Times are shown relative to the beginning and end of the statement execution. The Executions column shows how many times an operation was executed.

3. In the Overview section, click the link next to the SQL text.

   A message shows the full text of the SQL statement.

   
   Description of the illustration sql_id_tooltip.gif

4. In the Time & Wait Statistics section, next to Database Time, move the cursor over the largest portion on the bar graph.

   A message shows that user I/O is consuming over half of database time.

   
   Description of the illustration time_stats.gif

   Database Time measures the amount of time the database has spent working on this SQL statement. This value includes CPU and wait times, such as I/O time. The bar graph is divided into several color-coded portions to highlight CPU resources, user I/O resources, and other resources. You can move the cursor over any portion to view the percentage value of the total.

5. In the Details section, in the IO Requests column, move the cursor over the I/O requests bar to view the percentage value of the total.

   A message appears.

   
   Description of the illustration io_requests.gif

   In the preceding graphic, the IO Requests message shows the total number of read requests issued by the monitored SQL. The message shows that read requests form 80% of the total I/O requests.