Saturday, February 4, 2023

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


No comments:

Post a Comment