SQL Monitoring

SQL Monitoring
Real-Time SQL Monitoring

set lines 1000 pages 9999
col sid 		for 9999
col serial 		for 999999
col status 		for a15
col username 		for a20
col sql_text 		for a30
col module 		for a30
col program 		for a30
col SQL_EXEC_START 	for a20
col kill 		for a17
SELECT * FROM
(SELECT ''''||sid ||','|| session_serial#||',@'||inst_id ||'''' kill,con_id,status
,username,sql_id,SQL_PLAN_HASH_VALUE,
sql_exec_id ,
MODULE,program,
TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
ROUND(elapsed_time/1000000)                      AS "Elapsed (s)",
ROUND(cpu_time    /1000000)                      AS "CPU (s)",
substr(sql_text,1,30) sql_text
FROM gv$sql_monitor 
where 1=1 
and status='EXECUTING' 
and module not like '%emagent%'
ORDER BY sql_exec_start  desc
);




set lines 1000 pages 300

SELECT 
''''||sid ||','|| session_serial#||',@'||inst_id ||'''' kill,
sql_id,
sql_exec_id,
con_id,
ROUND(elapsed_time    /1000000)     	      AS "Elapsed (s)",
     ROUND(cpu_time             /1000000,3)   AS "CPU (s)",
     ROUND(queuing_time         /1000000,3)   AS "Queuing (s)",
     ROUND(application_wait_time/1000000,3)   AS "Appli wait (s)",
     ROUND(concurrency_wait_time/1000000,3)   AS "Concurrency wait (s)",
     ROUND(cluster_wait_time    /1000000,3)   AS "Cluster wait (s)",
     ROUND(user_io_wait_time    /1000000,3)   AS "User io wait (s)",
     ROUND(physical_read_bytes  /(1024*1024)) AS "Phys reads (MB)",
     ROUND(physical_write_bytes /(1024*1024)) AS "Phys writes (MB)",
     buffer_gets                              AS "Buffer gets",
     ROUND(plsql_exec_time/1000000,3)         AS "Plsql exec (s)",
     ROUND(java_exec_time /1000000,3)         AS "Java exec (s)",
    module,
    substr(sql_text,1,10) sql_text
     FROM gv$sql_monitor
     WHERE 1=1
    --and sql_id = '8cnh50qfgwg73'
    -- AND sql_exec_id = 16777270
     AND sql_exec_start > SYSTIMESTAMP - INTERVAL '60' second
and status='EXECUTING' 
and module not like '%emagent%'
;



set long 30000 pagesize 500 linesize 300
col frm         heading from 
select * from (select 'gv$sql' frm ,  sql_fulltext sql_text from gv$sql where sql_id='&&sql_id'
               union all
               select 'dba_hist', sql_text from dba_hist_sqltext where sql_id='&&sql_id' 
	);



select xmltype(binds_xml) from gv$sql_monitor where sid = &sid and status = 'EXECUTING';



alter session set "_rowsource_execution_statistics" = TRUE;
col PLAN_TABLE_OUTPUT for a200
SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '&sql_id', cursor_child_no => 0, FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE'));




SET lines 300  pages 1000  LONG 999999  longchunksize 200
SELECT dbms_sqltune.report_sql_monitor_list(sql_id=>'&sql_id',report_level=>'ALL') AS report FROM dual;


SET lines 200 pages 1000 LONG 999999 longchunksize 200
SELECT dbms_sqltune.report_sql_monitor(sql_id=>'5137dabysdzpw',sql_exec_id=>16777218,sql_exec_start=> TO_DATE('29-nov-2022 05:59:27','dd-mon-yyyy hh24:mi:ss')
,report_level=>'ALL') AS report FROM dual;
 

-- sql html !!!

SET LONG 1000000  LONGCHUNKSIZE 1000000  LINESIZE 1000  PAGESIZE 0  TRIM ON  TRIMSPOOL ON ECHO OFF FEEDBACK OFF

SPOOL report_sql_detail.html

SELECT DBMS_SQLTUNE.report_sql_detail(
  sql_id       => '&sql_id',
  type         => 'ACTIVE',
  report_level => 'ALL'
  ) AS report
FROM dual;

======
                             
select sysdate,sysdate - 30/(24*60) "-30min" ,sysdate - 10/(24*60) "-10min" ,sysdate - 5/(24*60) "-5min" from dual;



SYSDATE          -30min           -10min           -5min
---------------- ---------------- ---------------- ----------------
30-11-2022 13:52 30-11-2022 13:22 30-11-2022 13:42 30-11-2022 13:47

set long 250000  longchunksize 65536  pagesize 100 trimspool on heading off
--set linesize 600
set  linesize 254
 column text_line format a254
select DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST(
         INST_ID => NULL
       , active_since_date => sysdate - 5/(24*60)
       , report_level => 'BASIC'
--       , SQL_ID => 'dqbzzupk4xuz5'
 ,type                    => 'TEXT'
       )
  from dual;

set heading on 

===

set linesize 255  pagesize 200  trimspool on long 200000
 
set heading off
 
 
column text_line format a254
define m_sql_id = '4mtury8zcpvkx'
 
spool rep_mon
 
SELECT  dbms_sqltune.report_sql_monitor(
                sql_id=> v.sql_id,
                sql_exec_id => v.max_sql_exec_id
				--  ,active_since_date => sysdate - 5/(24*60)
        ) text_line
from     (
        select
                sql_id,
                max(sql_exec_id)        max_sql_exec_id
        from     v$sql_monitor
        where 1=1
           and   sql_id = '&m_sql_id'
   and     status like 'DONE%'
    group by
                sql_id
        )       v
;
 

==========

#1: Issue an alter session set "_sqlmon_max_planlines" = 300;  <<<< SQL with a plan in excess of 300 lines should not be monitored

to change 

alter system  set "_sqlmon_max_planlines"=500 scope=memory sid='*';
or
alter session set "_sqlmon_max_planlines"=500;

#2: Use a /*+ monitor */ hint when running the query.




set linesize 300 pagesize 300
col KSPPINM for a35
col KSPPSTVL for a15
col KSPPDESC for a90
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;

KSPPINM                             KSPPSTVL        KSPPDESC
----------------------------------- --------------- ------------------------------------------------------------------------------------------
_sqlmon_binds_xml_format            default         format of column binds_xml in [G]V$SQL_MONITOR
_sqlmon_max_plan                    1600            Maximum number of plans entry that can be monitored. Defaults to 20 per CPU
_sqlmon_max_planlines               300             Number of plan lines beyond which a plan cannot be monitored
_sqlmon_recycle_time                5               Minimum time (in s) to wait before a plan entry can be recycled
_sqlmon_threshold                   5               CPU/IO time threshold before a statement is monitored. 0 is disabled