As of Oracle 11g R1, long-running queries or queries that we run with the / * MONITOR * / hint are automatically monitored. During the monitoring, statistics about SQL execution are collected every second.
The value of the CONTROL_MANAGEMENT_PACK_ACCESS parameter must be ‘DIAGNOSTIC + TUNING’, and the value of STATISTICS_LEVEL parameter should be ALL or TYPICAL for monitoring.
Monitoring will start automatically when the following conditions occur.
- If the query has consumed more than 5 seconds of CPU time
- If the query has consumed more than 5 seconds of I / O time
- If the query was run in parallel
- If the query was run with / * + MONITOR * / hint
If we want to monitor a query, we can run it with / * + MONITOR * /. If we don’t want to monitor, we can run it with / * + NO_MONITOR * / hint.
Information about the monitored queries can be found in the V$SQL_MONITOR and V$SQL_PLAN_MONITOR views.
V$SQL_MONITOR: Displays SQL statements monitored by Oracle. A record is added here each time the monitored query runs.
V$SQL_PLAN_MONITOR: Displays plan-level monitoring statistics for each SQL statement in V$SQL_MONITOR.
There are some hidden parameters related to monitoring:
_sqlmon_max_plan: The maximum number of plan entries that can be monitored. The default value is 20 per CPU.
_sqlmon_max_planlines: Number of plan lines in which a plan cannot be tracked The default value is 300.
It is useful to increase the values of these hidden parameters to display the monitoring results.
You can query as follows by changing parameter values session-based.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
ALTER SESSION SET "_SQLMON_MAX_PLAN"=4020; ALTER SESSION SET "_SQLMON_MAX_PLANLINES"=4000; SELECT sql_exec_id, sql_plan_hash_value, mon.process_name, mon.status, mon.sid, mon.px_qcsid, round((LAST_REFRESH_TIME - FIRST_REFRESH_TIME)*24*60) AS elapsed_min, FIRST_REFRESH_TIME, LAST_REFRESH_TIME, px_maxdop, px_servers_requested, mon.sql_text, mon.* FROM v$SQL_MONITOR mon WHERE sql_id = '2kspy7v98wv35' ORDER BY mon.sql_exec_id, mon.sql_plan_hash_value, mon.process_name; |
With the monitored data, the dbms_sqltune.report_sql_monitor function can be used to generate a report for a query in V$SQL_MONITOR. We can execute this function with sql id information for a specific query as follows.
|
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 |
SQL Monitoring Report SQL Text ------------------------------ DELETE FROM AKTARIM_KUYRUK WHERE ID = :B1 Global Information ------------------------------ Status : DONE Instance ID : 3 Session : ADURUOZ (7492:36208) SQL ID : 2kspy7v98wv35 SQL Execution ID : 56701488 Execution Started : 11/27/2018 12:11:28 First Refresh Time : 11/27/2018 12:11:31 Last Refresh Time : 11/27/2018 12:11:31 Duration : 3s Module/Action : aktarim.exe/- Service : ORCL Program : aktarim.exe PLSQL Entry Ids (Object/Subprogram) : 233559,1 PLSQL Current Ids (Object/Subprogram) : 233559,1 Binds ======================================================================================================================== | Name | Position | Type | Value | ======================================================================================================================== | :B1 | 1 | VARCHAR2(32) | 5L0DQRO028H2WGPO | ======================================================================================================================== Global Stats =================================================================== | Elapsed | Cpu | IO | Cluster | Buffer | Read | Read | | Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | =================================================================== | 2.96 | 0.76 | 2.13 | 0.07 | 194K | 6914 | 54MB | =================================================================== SQL Plan Monitoring Details (Plan Hash Value=2154625852) =================================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) | =================================================================================================================================================================================== | 0 | DELETE STATEMENT | | | | 1 | +3 | 1 | 0 | | | | | | 1 | DELETE | AKTARIM_KUYRUK | | | 1 | +3 | 1 | 0 | 1172 | 9MB | 33.33 | cell single block physical read (1) | | 2 | PARTITION RANGE ALL | | 36 | 91 | 1 | +3 | 1 | 13204 | | | | | | 3 | INDEX RANGE SCAN | IX_AKTARIM_KUYRUK | 36 | 91 | 3 | +1 | 30 | 13204 | 5742 | 45MB | 66.67 | cell single block physical read (2) | =================================================================================================================================================================================== |
