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
