Implement real-time database operation monitoring
Real-time monitoring can be activated automatically when more than five (5) seconds of CPU or I/O time is used with the default database settings. It also be activated explicitly through a SQL hint or through PL/SQL procedures and functions.
There appears to be a bug with retrieval of reports for monitored DB operations.
References
Procedure
All commands are run as the SYS user in the JAM0 PDB within the JAR CDB on PADSTOW .
Monitoring at the Database Level
SQL statements are monitored automatically if:
- The system parameter, STATISTICS_LEVEL , is set to TYPICAL or ALL .
- The system parameter, CONTROL_MANAGEMENT_ACCESS , is set to DIAGNOSTIC+TUNING .
- CPU time or I/O time exceeds five (5) seconds for the SQL statement.
Monitoring at the Statement Level
The MONITOR hint is added to the SQL statement as shown in the example below to enable Real Time monitoring:
SELECT /*+ MONITOR */
SUM(dbms_random.value())
FROM
dual
CONNECT BY
level < 1000000;
The NOMONITOR hint is added to the SQL statement disable Real Time monitoring.
Reporting on a Monitored SQL Statement
To report on the last monitored SQL statement, run the follow SQL statement using REPORT_SQL_MONITOR :
SET LINESIZE 180 SET PAGESIZE 1000 COLUMN report FORMAT A180 select dbms_sql_monitor.report_sql_monitor() as report from dual;
The sample output is:
REPORT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL Monitoring Report SQL Text ------------------------------ SELECT /*+ MONITOR */ SUM(dbms_random.value()) FROM dual CONNECT BY level < 100000 Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : SYS (73:40065) SQL ID : 61f4rabuwbcd6 SQL Execution ID : 16777216 Execution Started : 01/27/2020 12:17:03 First Refresh Time : 01/27/2020 12:17:03 Last Refresh Time : 01/27/2020 12:17:03 Duration : .675846s Module/Action : sqlplus@padstow.yaocm.id.au (TNS V1-V3)/- Service : jam0.yaocm.id.au Program : sqlplus@padstow.yaocm.id.au (TNS V1-V3) Fetch Calls : 1 Global Stats =========================================================== | Elapsed | Cpu | PL/SQL | Other | Fetch | Buffer | | Time(s) | Time(s) | Time(s) | Waits(s) | Calls | Gets | =========================================================== | 0.68 | 0.64 | 0.45 | 0.04 | 1 | 6 | =========================================================== SQL Plan Monitoring Details (Plan Hash Value=968211682) ===================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | ===================================================================================================================================== | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | | | 1 | SORT AGGREGATE | | 1 | | 2 | +0 | 1 | 1 | | | | 2 | CONNECT BY WITHOUT FILTERING | | | | 1 | +0 | 1 | 99999 | | | | 3 | FAST DUAL | | 1 | 2 | 1 | +0 | 1 | 1 | | | =====================================================================================================================================
Monitoring a DB Operation
The following anonymous PL/SQL block demonstrates the use of BEGIN_OPERATION and END_OPERATION to monitor several SQL statements as a single operation:
VARIABLE l_op_name VARCHAR2(50)
VARIABLE l_dbop_eid NUMBER
BEGIN
:l_op_name := 'CPU Load Test';
:l_dbop_eid := dbms_sql_monitor.begin_operation(:l_op_name, forced_tracking => 'Y');
END;
/
DECLARE
l_result NUMBER := 0;
BEGIN
FOR i IN 1..5 LOOP
SELECT
SUM(dbms_random.value())
INTO l_result
FROM
dual
CONNECT BY
level < 1000000;
END LOOP;
END;
/
EXEC dbms_sql_monitor.end_operation(:l_op_name, :l_dbop_eid);
The EM Express Performance HUB shows:
Display Statistics for Monitored Operation
For a run of the DB operation, 'CPU Load Test' , the following SQL is used to capture a snapshot of various statistics:
SELECT
n.display_name,
s."VALUE"
FROM
v$sql_monitor_sesstat s
INNER JOIN v$statname n USING ( statistic# )
INNER JOIN v$sql_monitor m USING ( "KEY" )
WHERE
m.dbop_name = 'CPU Load Test'
AND s."VALUE" > 0;
Sample output is:
| DISPLAY_NAME | VALUE |
|---|---|
| Requests to/from client | 4 |
| opened cursors cumulative | 182 |
| user calls | 6 |
| recursive calls | 552 |
| recursive cpu usage | 3224 |
| session logical reads | 754 |
| CPU used by this session | 3225 |
| DB time | 3534 |
| user I/O wait time | 45138 |
| non-idle wait time | 45145 |
| non-idle wait count | 14 |
| enqueue requests | 13 |
| enqueue releases | 13 |
| physical read total IO requests | 8 |
| physical read total bytes | 65536 |
| cell physical IO interconnect bytes | 65536 |
| consistent gets | 754 |
| consistent gets from cache | 754 |
| consistent gets pin | 435 |
| consistent gets pin (fastpath) | 427 |
| consistent gets examination | 319 |
| consistent gets examination (fastpath) | 319 |
| logical read bytes from cache | 6176768 |
| physical blocks read | 8 |
| physical reads cache | 8 |
| physical read IO requests | 8 |
| physical read bytes | 65536 |
| free buffer requested | 8 |
| shared hash latch upgrades - no wait | 1 |
| calls to kcmgcs | 20 |
| calls to get snapshot scn: kcmgss | 225 |
| data file io service time | 24 |
| data file io wait time | 45022 |
| no work - consistent read gets | 419 |
| table scans (short tables) | 8 |
| table scan rows gotten | 8 |
| table scan disk non-IMC rows gotten | 8 |
| table scan blocks gotten | 8 |
| table fetch by rowid | 161 |
| table fetch continued row | 1 |
| cluster key scans | 86 |
| cluster key scan block gets | 103 |
| rows fetched via callback | 2 |
| index fetch by key | 92 |
| index scans kdiixs1 | 114 |
| session cursor cache hits | 177 |
| session cursor cache count | 1 |
| cursor authentications | 9 |
| buffer is pinned count | 55 |
| buffer is not pinned count | 633 |
| workarea executions - optimal | 26 |
| parse time cpu | 2 |
| parse time elapsed | 4 |
| parse count (total) | 50 |
| parse count (hard) | 13 |
| execute count | 206 |
| bytes sent via SQL*Net to client | 154 |
| bytes received via SQL*Net from client | 745 |
| SQL*Net roundtrips to/from client | 4 |
| sorts (memory) | 95 |
| sorts (rows) | 1345 |
Display Status of a Monitored DB Operation
For a run of the DB operation, 'CPU Load Test' , the following SQL is used to capture a snapshot of various statistics:
SELECT
dbop_name,
dbop_exec_id,
status
FROM
v$sql_monitor
WHERE
dbop_name = 'CPU Load Test';
Sample output is:
| DBOP_NAME | DBOP_EXEC_ID | STATUS |
|---|---|---|
| CPU Load Test | 1 | DONE |
Reporting on Monitored DB Operations
For a run of the DB operation, 'CPU Load Test' , I attempted to produce a report using REPORT_SQL_MONITOR , but it failed as follows:
SQL> select dbms_sql_monitor.report_sql_monitor(dbop_name => 2 'CPU Load Test') from dual; Error starting at line : 1 in command - select dbms_sql_monitor.report_sql_monitor(dbop_name => 'CPU Load Test') from dual Error report - ORA-31011: XML parsing failed ORA-19213: error occurred in XML processing at lines 1 LPX-00288: CDATA-section-close delimiter is prohibited in element content ORA-06512: at "SYS.XMLTYPE", line 272 ORA-06512: at "SYS.DBMS_SQLTUNE", line 10320 ORA-06533: Subscript beyond count ORA-06512: at "SYS.DBMS_SQLTUNE", line 16211 ORA-06512: at "SYS.DBMS_SQLTUNE", line 16307 ORA-06512: at "SYS.DBMS_SQL_MONITOR", line 509 ORA-06512: at line 1