1 PACKAGE dbms_sql_monitor AUTHID CURRENT_USER AS
2
3 -----------------------------------------------------------------------------
4 -- global constant declarations --
5 -----------------------------------------------------------------------------
6
7 MONITOR_TYPE_SQL CONSTANT NUMBER := 1;
8 MONITOR_TYPE_DBOP CONSTANT NUMBER := 2;
9 MONITOR_TYPE_ALL CONSTANT NUMBER := 3;
10
11 --
12 -- report type (possible values) constants
13 --
14 TYPE_TEXT CONSTANT VARCHAR2(4) := 'TEXT' ;
15 TYPE_XML CONSTANT VARCHAR2(3) := 'XML' ;
16 TYPE_HTML CONSTANT VARCHAR2(4) := 'HTML' ;
17
18 --
19 -- report level (possible values) constants
20 --
21 LEVEL_TYPICAL CONSTANT VARCHAR2(7) := 'TYPICAL' ;
22 LEVEL_BASIC CONSTANT VARCHAR2(5) := 'BASIC' ;
23 LEVEL_ALL CONSTANT VARCHAR2(3) := 'ALL' ;
24
25 --
26 -- report section (possible values) constants
27 --
28 SECTION_FINDINGS CONSTANT VARCHAR2(8) := 'FINDINGS' ;
29 SECTION_PLANS CONSTANT VARCHAR2(5) := 'PLANS' ;
30 SECTION_INFORMATION CONSTANT VARCHAR2(11):= 'INFORMATION';
31 SECTION_ERRORS CONSTANT VARCHAR2(6) := 'ERRORS' ;
32 SECTION_ALL CONSTANT VARCHAR2(3) := 'ALL' ;
33 SECTION_SUMMARY CONSTANT VARCHAR2(7) := 'SUMMARY' ;
34
35 -- some common date format
36 DATE_FMT constant varchar2(21) := 'mm/dd/yyyy hh24:mi:ss';
37
38 -- constant for forced tracking
39 FORCE_TRACKING CONSTANT VARCHAR2(30) := 'Y';
40 NO_FORCE_TRACKING CONSTANT VARCHAR2(30) := 'N';
41
42 -----------------------------------------------------------------------------
43 -- procedure / function declarations --
44 -----------------------------------------------------------------------------
45
46 --------------------------------- begin_operation ---------------------------
47 -- NAME:
48 -- begin_operation
49 --
50 -- DESCRIPTION
51 -- This function is called to start a operation in database to
52 -- get the current session monitored.
53 --
54 -- PARAMETERS:
55 -- dbop_name (IN) - the operation name
56 -- dbop_eid (IN) - the execution ID. It is used to connect
57 -- the different sessions of the same DBOP
58 -- execution
59 -- forced_tracking (IN) - it is for forcing the DB operation to be
60 -- tracked when the operation starts, otherwise
61 -- the operation will be tracked only when it is
62 -- expensive enough. The default value is
63 -- "NO_FORCE_TRACKING".
64 --
65 -- attribute_list (IN) - list of the user input attributes
66 -- it is s comma separated name-value pair.
67 -- For example, 'table_name=emp, operation=load'
68 --
69 -- RETURNS:
70 -- DB operation unique execution ID
71 --
72 -- EXCEPTIONS:
73 -- To be done
74 -----------------------------------------------------------------------------
75 FUNCTION begin_operation(
76 dbop_name IN VARCHAR2,
77 dbop_eid IN NUMBER := NULL,
78 forced_tracking IN VARCHAR2 := NO_FORCE_TRACKING,
79 attribute_list In VARCHAR2 := NULL)
80 RETURN NUMBER;
81
82 --------------------------------- end_operation -----------------------------
83 -- NAME:
84 -- end_operation
85 --
86 -- DESCRIPTION
87 -- This procedure is called to end the operation in the current session.
88 -- If there is no operation, this will be NO-OP.
89 --
90 -- PARAMETERS:
91 -- dbop_name (IN) - the operation name
92 -- dbop_eid (IN) - the execution ID
93 --
94 -- EXCEPTIONS:
95 -- To be done
96 -----------------------------------------------------------------------------
97 PROCEDURE end_operation(
98 dbop_name IN VARCHAR2,
99 dbop_eid IN NUMBER);
100
101
102 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
103 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
104 -- ------------------------------------------- --
105 -- SQL MONITORING REPORT FUNCTIONS/PROCEDURE --
106 -- ------------------------------------------- --
107 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
108 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
109 ------------------------------- report_sql_monitor --------------------------
110 -- NAME:
111 -- report_sql_monitor
112 --
113 -- DESCRIPTION:
114 --
115 -- This function builds a report (text, simple html, active html, xml)
116 -- for the monitoring information collected on behalf of the targeted
117 -- statement execution.
118 --
119 -- The target SQL statement for this report can be:
120 --
121 -- - the last SQL monitored by Oracle (default, no parameter)
122 -- - the last SQL executed by a specified session and monitored
123 -- by Oracle. The session is identified by its session id and
124 -- optionally it serial# (-1 is current session). For example, use
125 -- sess_id=>-1 for the current session or sess_id=>20,
126 -- sess_serial=>103 for session id 20, serial number 103.
127 -- - the last execution of a specific statement identified by
128 -- its sql_id.
129 -- - a specific execution of a SQL statement identified by the
130 -- triplet (sql_id, sql_exec_start and sql_exec_id).
131 --
132 -- PARAMETERS:
133 --
134 -- - sql_id: SQL_ID for which monitoring information should be
135 -- displayed. Use NULL (the default) to display
136 -- monitoring information for the last statement
137 -- monitored by Oracle.
138 --
139 -- - dbop_name DQOP_NAME for which DB operation should be displayed
140 --
141 -- - session_id: Target only the sub-set of statements executed and
142 -- monitored on behalf of the specified session.
143 -- Default is NULL. Use -1 or USERENV('SID') for current
144 -- seesion.
145 --
146 -- - session_serial:
147 -- In addition to the above <session_id> parameter, one
148 -- can also specify its session serial to ensure that
149 -- the desired session incarnation is targeted. Ignored
150 -- when <session_id> is NULL.
151 --
152 -- - (sql_exec_start, sql_exec_id):
153 -- Only applicable when <sql_id> is also specified and
154 -- can be used to display monitoring information for a
155 -- particular execution of <sql_id>. When NULL (the
156 -- default), the last execution of <sql_id> is shown.
157 --
158 -- - inst_id: Only look at queries started on the specified
159 -- instance. Use -1 to target the current instance.
160 -- The default, NULL will target all instances.
161 --
162 -- - start_time_filter:
163 -- If non NULL, the report will show only activity
164 -- (from V$ACTIVE_SESSION_HISTORY) started after this
165 -- date. If NULL, the reported activity will start when
166 -- the targeted SQL statement has started.
167 --
168 -- - end_time_filter:
169 -- If non NULL, the report will show only activity
170 -- (from V$ACTIVE_SESSION_HISTORY) collected before this
171 -- date. If NULL, the reported activity will end when
172 -- the targeted SQL statement has ended or SYSDATE if the
173 -- statement is still executing.
174 --
175 -- - instance_id_filter:
176 -- Only look at activity for the specified instance. Use
177 -- NULL (the default) to target all instances. Only
178 -- relevant if the query runs parallel.
179 --
180 -- - parallel_filter:
181 -- Parallel filter applies only to parallel execution and
182 -- allows to select only a subset of the processes
183 -- involved in the parallel execution. The string
184 -- parallel_filter can be:
185 -- - NULL (target all parallel execution servers + the
186 -- query coordinator)
187 -- - ['qc'][servers(<svr_grp>[,] <svr_set>[,] <srv_num>)]
188 -- where any NULL value is interpreted as ALL.
189 --
190 -- The following examples show how one can set
191 -- <parallel_filter> to target only a subset of the
192 -- parallel sessions:
193 -- - 'qc' to target only the query coordinator
194 -- - servers(1)': to target all px servers in group 1
195 -- servers(,2)': to target all px servers in set 1,
196 -- any group
197 -- - servers(1,1)': group 1, set 1
198 -- - servers(1,2,4)': group 1, set 3, server number 4
199 -- - qc servers(1,2,4)': same as above by also
200 -- including QC
201 --
202 -- - plan_line_filter:
203 -- This filter selects activity and execution stats for
204 -- the specified line number in the plan of a SQL
205 --
206 -- - event_detail:
207 -- When set to 'no', the activity is aggregated by
208 -- wait_class only. Use 'yes' (the default) to aggregate
209 -- by (wait_class, event_name)
210 --
211 -- The next 2 parameters are used to control the activity histogram. By
212 -- default, the maximum number of buckets is set to 128 and we derive the
213 -- bucket_interval based on this. Basically, <bucket_interval> (value is
214 -- in seconds) is computed such that it is the smallest possible power of
215 -- 2 value (starting at 1s) without causing to exceed the maximum number
216 -- of buckets. For example, if the query has executed for 600s, we will
217 -- pick a bucket_interval of 8s (a power of two) since 600/8 = 74 which
218 -- is less than 128 buckets maximum. Smaller than 8s would be 4s, but
219 -- that would cause to have more buckets than the 128 maximum.
220 -- If <bucket_interval> is specified, we will use that value instead of
221 -- deriving it from bucket_max_count.
222 --
223 -- - bucket_max_count:
224 -- If specified, this should be the maximum number of
225 -- histogram buckets created in the report
226 --
227 -- - bucket_interval:
228 -- If specified, this represents the exact time interval
229 -- in seconds, of all histogram buckets. If specified,
230 -- bucket_max_count is ignored.
231 --
232 -- - base_path: this is the URL path for flex HTML ressources since
233 -- flex HTML format requires to access external files
234 -- (java scripts and the flash swf file itself).
235 --
236 -- - last_refresh_time:
237 -- If not null (default is null), time when the
238 -- report was last retrieved (see sysdate attribute
239 -- of the report tag). Use this option when you want
240 -- to display the report of an running query and when
241 -- that report is refreshed on a regular basis. This
242 -- will optimize the size of the report since only
243 -- the new/changed information will be returned. In
244 -- particular, the following will be optimized:
245 -- - SQL text will not be returned when this option
246 -- is specified
247 -- - activity histogram will start at the bucket that
248 -- intersect that time. The entire content of the
249 -- bucket will be return, even if last_refresh_time
250 -- is after the start of that bucket
251 --
252 -- - report_level:
253 -- level of detail for the report, either 'none', 'basic',
254 -- 'typical' or 'all'. Default assumes 'typical'. Their
255 -- meanings are explained below.
256 --
257 -- In addition, individual report sections can also
258 -- be enabled/disabled by using a +/-<section_name>.
259 -- Several sections are defined: 'plan', 'xplan',
260 -- 'parallel', 'sessions', 'instance', 'binds', 'activity',
261 -- 'activity_histogram', 'plan_histogram', 'metrics',
262 -- 'other'.
263 -- Their meanings are as follows:
264 -- xplan : Show explain plan,
265 -- ON by default
266 -- plan : Show plan monitoring stats,
267 -- ON by default
268 -- sessions : Show session details. Applies only
269 -- to parallel queries
270 -- ON by default
271 -- instance : Show instance details. Applies only
272 -- to parallel and cross instance
273 -- queries
274 -- ON by default
275 -- parallel : An umbrella parameter for
276 -- specifying sessions+instance
277 -- details
278 -- activity : Show activity summary at global
279 -- level, plan line level and session/
280 -- instance level (if applicable).
281 -- ON by default
282 -- binds : Show bind information when available
283 -- ON by default
284 -- metrics : Show metric data (CPU, IOs, ...)
285 -- over time
286 -- ON by default
287 -- activity_histogram :
288 -- Show an histogram of the overall
289 -- query activity
290 -- ON by default
291 -- plan_histogram : Show activity histogram at plan
292 -- line level
293 -- OFF by default
294 -- other : Other info
295 -- ON by default
296 --
297 -- In addition, SQL text can be specified at different
298 -- levels:
299 -- -sql_text : No SQL text in report
300 -- +sql_text : OK with partial SQL text, i.e. upto
301 -- the first 2000 chars as stored in
302 -- gv$sql_monitor
303 -- -sql_fulltext : No full SQL text, i.e +sql_text
304 -- +sql_fulltext : Show full SQL text (default value)
305 --
306 -- The meanings of the three top-level report levels are:
307 -- none = the minimum possible
308 -- basic = sql_text-plan-xplan-sessions-instance
309 -- -activity_histogram-plan_histogram
310 -- -metrics
311 -- typical = everything but plan_histogram
312 -- all = everything
313 --
314 -- Only one of these 4 levels can be specified and if it
315 -- is, then it has to be at the start of the report_level
316 -- string
317 --
318 -- Examples:
319 -- Use 'basic+parallel' to show the basic
320 -- report with additional section reporting parallel
321 -- information. Use 'all-plan-instance' for full
322 -- report minus plan detail and instance information.
323 --
324 -- - type:
325 -- Report TYPE. Can be either 'TEXT' (text report, the default),
326 -- 'HTML' (simple HTML report, 'ACTIVE' (database active reports),
327 -- 'XML' (raw data for the report). Some information (activity
328 -- histogram, metrics, ...) are only shown when the ACTIVE report
329 -- type is selected.
330 --
331 -- - sql_plan_hash_value:
332 -- Target only those with the specified plan_hash_value.
333 -- Default is NULL.
334 --
335 -- - con_name: container name
336 --
337 -- RETURN:
338 -- The SQL monitor report, an XML document
339 --
340 -- NOTE:
341 -- The user tunning this function needs to have privilege to access the
342 -- following fixed views:
343 -- - GV$SQL_MONITOR
344 -- - GV$SQL_PLAN_MONITOR
345 -- - GV$ACTIVE_SESSION_HISTORY
346 -- - GV$SESSION_LONGOPS
347 -- - GV$SQL if SQL fulltext is asked and its length is > 2K
348 -----------------------------------------------------------------------------
349 FUNCTION report_sql_monitor(
350 sql_id in varchar2 default NULL,
351 dbop_name in varchar2 default NULL,
352 dbop_exec_id in number default NULL,
353 session_id in number default NULL,
354 session_serial in number default NULL,
355 sql_exec_start in date default NULL,
356 sql_exec_id in number default NULL,
357 inst_id in number default NULL,
358 start_time_filter in date default NULL,
359 end_time_filter in date default NULL,
360 instance_id_filter in number default NULL,
361 parallel_filter in varchar2 default NULL,
362 plan_line_filter in number default NULL,
363 event_detail in varchar2 default 'yes',
364 bucket_max_count in number default 128,
365 bucket_interval in number default NULL,
366 base_path in varchar2 default NULL,
367 last_refresh_time in date default NULL,
368 report_level in varchar2 default 'TYPICAL',
369 type in varchar2 default 'TEXT',
370 sql_plan_hash_value in number default NULL,
371 con_name in varchar2 default NULL)
372 RETURN clob;
373
374
375 ------------------------------- report_sql_monitor_xml ----------------------
376 -- NAME:
377 -- report_sql_monitor_xml
378 --
379 -- DESCRIPTION:
380 --
381 -- Same as above function (report_sql_monitor()) except that the result
382 -- is only XML, hence the return type is xmltype.
383 --
384 -----------------------------------------------------------------------------
385 FUNCTION report_sql_monitor_xml(
386 sql_id in varchar2 default NULL,
387 dbop_name in varchar2 default NULL,
388 dbop_exec_id in number default NULL,
389 session_id in number default NULL,
390 session_serial in number default NULL,
391 sql_exec_start in date default NULL,
392 sql_exec_id in number default NULL,
393 inst_id in number default NULL,
394 start_time_filter in date default NULL,
395 end_time_filter in date default NULL,
396 instance_id_filter in number default NULL,
397 parallel_filter in varchar2 default NULL,
398 plan_line_filter in number default NULL,
399 event_detail in varchar2 default 'yes',
400 bucket_max_count in number default 128,
401 bucket_interval in number default NULL,
402 base_path in varchar2 default NULL,
403 last_refresh_time in date default NULL,
404 report_level in varchar2 default 'TYPICAL',
405 auto_refresh in number default NULL,
406 sql_plan_hash_value in number default NULL,
407 con_name in varchar2 default NULL)
408 return xmltype;
409
410
411 ---------------------------- report_sql_monitor_list ------------------------
412 -- NAME:
413 -- report_sql_monitor_list
414 --
415 -- DESCRIPTION:
416 --
417 -- This function builds a report for all or a sub-set of statements
418 -- that have been monitored by Oracle. For each statement, it gives
419 -- key information and associated global statistics.
420 --
421 -- Use report_sql_monitor() to get detail monitoring information for
422 -- a single SQL statement
423 --
424 -- PARAMETERS:
425 --
426 -- - sql_id: SQL_ID for which monitoring information should be
427 -- displayed. Use NULL (the default) to display
428 -- monitoring information for the last statement
429 -- monitored by Oracle.
430 --
431 -- - dbop_name DQOP_NAME for which DB operation should be displayed
432 --
433 -- - monitor_type MONITOR_TYPE_SQL will only return SQLs
434 -- MONITOR_TYPE_DBOP will only return DB Operations
435 -- MONITOR_TYPE_ALL will return all types
436 --
437 -- - session_id: Target only the sub-set of statements executed and
438 -- monitored on behalf of the specified session.
439 -- Default is NULL. Use -1 (or USERENV('SID')) for
440 -- current session.
441 --
442 -- - session_serial:
443 -- In addition to the above <session_id> parameter, one
444 -- can also specify its session serial to ensure that
445 -- the desired session incarnation is targeted. Ignored
446 -- when <session_id> is NULL.
447 --
448 -- - inst_id: Only look at monitored statements originating from
449 -- the specified instance. Special value -1 can be used
450 -- to target the instance where the report executed.
451 -- To target all instances, use NULL (the default).
452 --
453 -- - active_since_date:
454 -- If not null (default is null), only returns monitored
455 -- statements that have been active since specified
456 -- time. This includes all statements that are still
457 -- executing plus all statements that have completed
458 -- their execution after the specified date/time.
459 --
460 -- - active_since_sec:
461 -- Same as above but the date is specified relativelly
462 -- to the current sysdate minus specified number of
463 -- seconds. For example, use 3600 to limit the report
464 -- to all statements that have been active in the past
465 -- 1 hour.
466 --
467 -- - last_refresh_time:
468 -- If not null (default is null), date/time when the
469 -- list report was last retrieved. This is to optimize
470 -- the case where an application shows the list and
471 -- refresh the report on a regular basis (say once every
472 -- 5s). In this case, the report will only show detail
473 -- about the execution of monitored queries that have
474 -- been active since the specified <last_refresh_time>.
475 -- For other queries, the report will only return the
476 -- execution key (i.e. sql_id, sql_exec_start,
477 -- sql_exec_id). Also, for queries that have their
478 -- first refresh time after the specified date, only
479 -- the SQL execution key and statistics are returned.
480 --
481 -- - report_level:
482 -- level of detail for the report. The level can be
483 -- either basic (SQL text up to 200 character),
484 -- typical (include full SQL text assuming that cursor
485 -- has not aged out, in which case the SQL text is
486 -- included up to 2000 characters). report_level can
487 -- also be all which is the same as typical for now.
488 --
489 --
490 -- - con_name: container name
491 --
492 -- RETURN:
493 -- A report (xml, text, html) for the list of SQL statements that have
494 -- been monitored.
495 --
496 -- NOTE:
497 -- The user tunning this function needs to have privilege to access the
498 -- following fixed views:
499 -- - GV$SQL_MONITOR and GV$SQL
500 -----------------------------------------------------------------------------
501 FUNCTION report_sql_monitor_list(
502 sql_id in varchar2 default NULL,
503 dbop_name in varchar2 default NULL,
504 monitor_type in number default MONITOR_TYPE_ALL,
505 session_id in number default NULL,
506 session_serial in number default NULL,
507 inst_id in number default NULL,
508 active_since_date in date default NULL,
509 active_since_sec in number default NULL,
510 last_refresh_time in date default NULL,
511 report_level in varchar2 default 'TYPICAL',
512 auto_refresh in number default NULL,
513 base_path in varchar2 default NULL,
514 type in varchar2 default 'TEXT',
515 con_name in varchar2 default NULL)
516 RETURN clob;
517
518
519 ---------------------------- report_sql_monitor_list_xml -------------------
520 -- NAME:
521 -- report_sql_monitor_list_xml
522 --
523 -- DESCRIPTION:
524 --
525 -- Same as above function (report_sql_monitor) except that the result
526 -- is only XML, hence the return type is xmltype
527 --
528 --
529 -- RETURN:
530 -- An XML document for the list of SQL statements that have been
531 -- monitored.
532 --
533 -- NOTE:
534 -- The user tunning this function needs to have privilege to access the
535 -- following fixed views:
536 -- - GV$SQL_MONITOR and GV$SQL
537 -----------------------------------------------------------------------------
538 FUNCTION report_sql_monitor_list_xml(
539 sql_id in varchar2 default NULL,
540 dbop_name in varchar2 default NULL,
541 monitor_type in number default MONITOR_TYPE_ALL,
542 session_id in number default NULL,
543 session_serial in number default NULL,
544 inst_id in number default NULL,
545 active_since_date in date default NULL,
546 active_since_sec in number default NULL,
547 last_refresh_time in date default NULL,
548 report_level in varchar2 default 'TYPICAL',
549 auto_refresh in number default NULL,
550 base_path in varchar2 default NULL,
551 con_name in varchar2 default NULL)
552 RETURN xmltype;
553
554
555 END dbms_sql_monitor;
[Home] [Help]