Force monitor Oracle Database Operations / Sql -- Monitoring Settings to capture Sql that are not monitored

 

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