We all knew to how to check sql monitoring for long running sql . But there were situations where sql were not monitored .
To avoid these issue we came across below 4 solutions :
1) /*+ MONITOR */ hint
2) sql_monitor event
3) DBMS_SQL_MONITOR.begin_operation
4) “_sqlmon_max_planlines” parameter
“_sqlmon_max_planlines” Parameter
There is a hidden parameter “_sqlmon_max_planlines” which states that any SQL with a plan in excess of 300 lines should not be monitored.
The solution is to change either the session or the system to allow monitoring to happen when the plan is over 300 lines.
alter system set "_sqlmon_max_planlines"=500 scope=memory sid='*';
or
alter session set "_sqlmon_max_planlines"=500;
The negative side effect it that the monitoring will use more resources (primarily memory and CPU), which is why there are default limits on this feature. You might want to change it back when you’re finished to conserve resources.
select ksppinm, ksppstvl, ksppdesc
from sys.x$ksppi a, sys.x$ksppsv b
where a.indx=b.indx
and lower(ksppinm) like lower('%sqlmon%')
order by ksppinm
;
/*+ MONITOR */ hint and sql_monitor event
The event sql_monitor specifies a list of SQL IDs for the statements to be monitored. A SQL statement specifies the /*+ MONITOR */ hint.
For example, the following statement forces instance-level monitoring for SQL IDs 5hc07qvt8v737 and 9ht3ba3arrzt3:
ALTER SYSTEM SET EVENTS 'sql_monitor [sql: 5hc07qvt8v737|sql:9ht3ba3arrzt3] force=true'
At each step of the SQL execution plan, the database tracks statistics by performance metrics such as elapsed time, CPU time, number of reads and writes, and I/O wait time.
These metrics are available in a graphical and interactive report called the SQL monitor active report.
sql_monitor event
ALTER SYSTEM SET EVENTS 'sql_monitor [sql: 6v717k15utxsf] force=true';
SET TERMOUT OFF
SELECT * FROM force_sqlmon;
SET TERMOUT ON
SELECT * FROM v$sql_monitor WHERE sql_text LIKE '%force_sqlmon%';
DBMS_SQL_MONITOR.begin_operation
We can monitor whole operation executed by user session
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
DBMS_SQL_MONITOR.end_operation (
dbop_name => 'db_op_1',
dbop_eid => :l_dbop_eid
);
END;
/
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
References :
https://docs.oracle.com/database/121/ARPLS/d_sql_monitor.htm#ARPLS74785
