/
sqlmon.sql
94 lines (84 loc) · 3.62 KB
/
sqlmon.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
--------------------------------------------------------------------------------
--
-- File name: sqlmon.sql
-- Purpose: Spool a SQL Monitoring report to a local HTML file and open it
-- in your browser.
--
-- Authors: Adrian Billington and Tanel Poder
-- Copyright: (c) Gluent, Inc. [http://gluent.com]
--
-- Usage: @sqlmon <session_id>
--
-- Other: This script will report the latest/newest monitored SQL statement
-- of a provided session.
-- Ideally you should run this script from sqlplus running in your
-- workstation, as this way sqlplus will spool it to a local file
-- (and your local browser can open it)
--
--------------------------------------------------------------------------------
SET HEADING OFF LINESIZE 32767 PAGESIZE 0 TRIMSPOOL ON TRIMOUT ON LONG 9999999 VERIFY OFF LONGCHUNKSIZE 100000 FEEDBACK OFF
SET TERMOUT OFF
SET TIMING OFF
SET DEFINE ON
col _dbname NEW_VALUE _v_dbname
col _sid NEW_VALUE _v_sid
col _sql_id NEW_VALUE _v_sql_id
col _sql_exec_id NEW_VALUE _v_sql_exec_id
col _sql_exec_start NEW_VALUE _v_sql_exec_start
col _sql_exec_start_glu NEW_VALUE _v_sql_exec_start_glu
col _plan_hash_value NEW_VALUE _v_plan_hash_value
col _sql_child_number NEW_VALUE _v_sql_child_number
SELECT
SYS_CONTEXT('USERENV', 'INSTANCE_NAME') AS "_dbname"
, m.sid AS "_sid"
, MAX(m.sql_id) KEEP
(DENSE_RANK FIRST ORDER BY m.last_refresh_time DESC) AS "_sql_id"
, TO_CHAR(MAX(m.sql_exec_id) KEEP
(DENSE_RANK FIRST ORDER BY m.last_refresh_time DESC)) AS "_sql_exec_id"
, TO_CHAR(MAX(m.sql_exec_start) KEEP
(DENSE_RANK FIRST ORDER BY m.last_refresh_time DESC),
'YYMMDD_HH24MISS') AS "_sql_exec_start"
, TO_CHAR(MAX(m.sql_exec_start) KEEP
(DENSE_RANK FIRST ORDER BY m.last_refresh_time DESC),
'YYYYMMDD_HH24MISS') AS "_sql_exec_start_glu"
, MAX(m.sql_plan_hash_value) KEEP
(DENSE_RANK FIRST ORDER BY m.last_refresh_time DESC) AS "_plan_hash_value"
, MAX(s.child_number) KEEP
(DENSE_RANK FIRST ORDER BY m.last_refresh_time DESC) AS "_sql_child_number"
FROM v$sql_monitor m
INNER JOIN
v$sql s
ON ( s.sql_id = m.sql_id
AND s.child_address = m.sql_child_address)
WHERE m.sid = &1
AND UPPER(m.sql_text) NOT LIKE 'EXPLAIN PLAN%'
GROUP BY
m.sid
;
SPOOL sqlmon_&_v_dbname._&_v_sql_id._&_v_sql_exec_start._&_v_sql_exec_id..html
SELECT
REGEXP_REPLACE(
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
session_id => &_v_sid,
sql_id => '&_v_sql_id',
sql_exec_id => '&_v_sql_exec_id',
sql_exec_start => TO_DATE('&_v_sql_exec_start', 'YYMMDD_HH24MISS'),
report_level => 'ALL',
type => 'ACTIVE'),
'overflow:hidden', '')
FROM dual
/
SPOOL OFF
SET TERMOUT ON HEADING ON PAGESIZE 5000 LINESIZE 999 FEEDBACK ON
SET TIMING ON
HOST open sqlmon_&_v_dbname._&_v_sql_id._&_v_sql_exec_start._&_v_sql_exec_id..html
--HOST open http://localhost:8000/sqlmon_&_v_dbname._&_v_sql_id._&_v_sql_exec_start._&_v_sql_exec_id..html
undefine _v_dbname
undefine _v_sid
undefine _v_sql_id
undefine _v_sql_exec_id
undefine _v_sql_exec_start
undefine _v_plan_hash_value
undefine _v_sql_child_number