DBA Hub

📋Steps in this guide1/7

Real-Time Database Operation Monitoring in Oracle Database 12c Release 1 and 2 (12.1 and 12.2)

Oracle 12c extends real-time SQL monitoring to allow monitoring of multiple SQL and PL/SQL calls as a single operation.

oracle 12cconfigurationintermediate
by OracleDba
12 views
1

Real-Time SQL Monitoring Updates

The basics of the real-time SQL monitoring functionality are unchanged from Oracle 11g, except the , routines have been moved from the package to the package.
2

Real-Time SQL Database Operation Monitoring

Oracle 12c allows you to monitor a series of SQL and PL/SQL calls containing SQL and group them together as a composite database operation. The start and end of the operation are signalled by the and routines from the package. The function accepts the following parameters. - : A user-defined name for the composite database operation. - : An optional unique identifier for the current execution of the composite database operation. If it is NULL, a unique execution identifier is created. - : When set to the constant (Y), the operation is tracked. When set to (N), the default, the operation is only tracked if it has consumed 5 seconds of CPU or I/O time. - : An optional comma-separated list of name-value pairs of attributes to restrict the calls that are monitored during the operating period. The uses just the first two of those parameters, but both are mandatory. The examples below use the following objects. Begin an operation with forced tracking. Perform an operation that combines references to T1 and T2. End the operation. We can see the operation was monitored using the view.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 AS
SELECT level AS id,
       'Description for ' || level AS description
FROM   dual
CONNECT BY level <= 1000000;

CREATE TABLE t2 AS
SELECT level AS id,
       'Description for ' || level AS description
FROM   dual
CONNECT BY level <= 1000000;

VARIABLE l_dbop_eid NUMBER;

BEGIN
  :l_dbop_eid := DBMS_SQL_MONITOR.begin_operation (
                   dbop_name       => 'db_op_1',
                   dbop_eid        => :l_dbop_eid,
                   forced_tracking => DBMS_SQL_MONITOR.force_tracking
                 );
END;
/

BEGIN
  FOR cur_rec IN (SELECT * FROM t1) LOOP
    NULL;
  END LOOP;

  FOR cur_rec IN (SELECT * FROM t2) LOOP
    NULL;
  END LOOP;
END;
/

BEGIN
  DBMS_SQL_MONITOR.end_operation (
    dbop_name       => 'db_op_1',
    dbop_eid        => :l_dbop_eid
  );
END;
/

SET LINESIZE 200

SELECT dbop_name, dbop_exec_id, status
FROM   v$sql_monitor
WHERE  username = 'TEST';

DBOP_NAME                      DBOP_EXEC_ID STATUS
------------------------------ ------------ -------------------
db_op_1                                   3 DONE
db_op_1                                   2 DONE
db_op_1                                   1 DONE

SQL>
3

REPORT_SQL_MONITOR

The usage of the function for real-time SQL monitoring is unchanged compared to 11g ( see here ), but is has been moved to the package. For monitoring DB operations, all we need to do is specify the parameter and we will get a report on the latest execution of the specified database operation. Alternatively, we can specify the parameter if we don't want the latest execution. Examples of the output for each available are displayed below. - TEXT - HTML - XML - ACTIVE

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL /host/report_sql_monitor.htm
SELECT DBMS_SQL_MONITOR.report_sql_monitor(
  dbop_name    => 'db_op_1',
  type         => 'HTML',
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF
4

REPORT_SQL_MONITOR_LIST

The function works in the same way it did in 11gR2, but is has been moved to the package and now supports active reports. Examples of the output for each available are displayed below. - TEXT - HTML - XML - ACTIVE

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL /host/report_sql_monitor_list.htm
SELECT DBMS_SQL_MONITOR.report_sql_monitor_list(
  type         => 'HTML',
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF
5

REPORT_SQL_DETAIL

The function is still located in the package. You can see an example of its use here .
6

Views

There are some minor changes to the views compared to 11g, but they are still used in the same way. - V$SQL_MONITOR - V$SQL_PLAN_MONITOR
7

12cR2 Updates

The main change in Oracle Database 12.2 is the addition of two new parameters to function. - : The ID of the session to be monitored. If NULL the current session is monitored. - : The serial number of the session to be monitored. If NULL the serial number derived based on the session ID. These allow an operation to be started in a separate session. For more information see: - DBMS_SQL_MONITOR - Real-Time SQL Monitoring using DBMS_SQLTUNE Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!