1 PACKAGE dbms_workload_repository AS
2
3 -- ************************************ --
4 -- DBMS_WORKLOAD_REPOSITORY Constants
5 -- ************************************ --
6
7 -- Minimum and Maximum values for the
8 -- Snapshot Interval Setting (in minutes)
9 MIN_INTERVAL CONSTANT NUMBER := 10; /* 10 minutes */
10 MAX_INTERVAL CONSTANT NUMBER := 52560000; /* 100 years */
11
12 -- Minimum and Maximum values for the
13 -- Snapshot Retention Setting (in minutes)
14 MIN_RETENTION CONSTANT NUMBER := 1440; /* 1 day */
15 MAX_RETENTION CONSTANT NUMBER := 52560000; /* 100 years */
16
17
18 -- *********************************** --
19 -- DBMS_WORKLOAD_REPOSITORY Routines
20 -- *********************************** --
21
22 --
23 -- create_snapshot()
24 -- Creates a snapshot in the workload repository.
25 --
26 -- This routine will come in two forms: procedure and function.
27 -- The function returns the snap_id for the snapshot just taken.
28 --
29 -- Input arguments:
30 -- flush_level - flush level for the snapshot:
31 -- either 'DEAFULT', 'TYPICAL' or 'ALL'
32 -- dbid - optional: If specified, a snapshot
33 -- will be taken for the (mapped) dbid.
34 -- If not specified, Source_Name will
35 -- be used as the ID.
36 -- source_name - optional: If specified, a snapshot
37 -- will be taken for the database with
38 -- the registered Source Name.
39 -- If not specified, the DBID parameter
40 -- value will be used.
41 --
42 -- (Note: The parameter dbid and source_name are optional. Callers
43 -- are only allowed to specified no more than one of them
44 -- as ID for the snapshot database.
45 --
46 -- If none of the two parameters is specified, the Local
47 -- database is assumed.
48 --
49 -- If any one of the two parameters is specified, the
50 -- registered database that matches the specified parameter
51 -- will be chosen.
52 --
53 -- If both parameters are specified, an error will be raised.
54 --
55 -- End of notes)
56 --
57 --
58 -- Returns:
59 -- NUMBER - snap_id for snapshot just taken.
60 --
61
62 PROCEDURE create_snapshot(
63 flush_level IN VARCHAR2 DEFAULT 'BESTFIT',
64 dbid IN NUMBER DEFAULT NULL,
65 source_name IN VARCHAR2 DEFAULT NULL
66 );
67
68
69 FUNCTION create_snapshot(
70 flush_level IN VARCHAR2 DEFAULT 'BESTFIT',
71 dbid IN NUMBER DEFAULT NULL,
72 source_name IN VARCHAR2 DEFAULT NULL
73 ) RETURN NUMBER;
74
75 --
76 -- drop_snapshot_range()
77 -- purge the snapshots for the given range of snapshots.
78 --
79 -- Input arguments:
80 -- low_snap_id - low snapshot id of snapshots to drop
81 -- high_snap_id - high snapshot id of snapshots to drop
82 -- dbid - database id (default to local DBID)
83 --
84
85 PROCEDURE drop_snapshot_range(low_snap_id IN NUMBER,
86 high_snap_id IN NUMBER,
87 dbid IN NUMBER DEFAULT NULL
88 );
89
90
91 --
92 -- modify_snapshot_settings()
93 -- Procedure to adjust the settings of the snapshot collection.
94 --
95 -- Input arguments:
96 -- retention - new retention time (in minutes). The
97 -- specified value must be in the range:
98 -- MIN_RETENTION (1 day) to
99 -- MAX_RETENTION (100 years)
100 --
101 -- If ZERO is specified, snapshots will be
102 -- retained forever. A large system-defined
103 -- value will be used as the retention setting.
104 --
105 -- If NULL is specified, the old value for
106 -- retention is preserved.
107 --
108 -- ***************
109 -- NOTE: The retention setting must be
110 -- greater than or equal to the window
111 -- size of the 'SYSTEM_MOVING_WINDOW'
112 -- baseline. If the retention needs
113 -- to be less than the window size,
114 -- the 'modify_baseline_window_size'
115 -- routine can be used to adjust the
116 -- window size.
117 -- ***************
118 --
119 -- interval - the interval between each snapshot, in
120 -- units of minutes. The specified value
121 -- must be in the range:
122 -- MIN_INTERVAL (10 minutes) to
123 -- MAX_INTERVAL (100 years)
124 --
125 -- If ZERO is specified, automatic and manual
126 -- snapshots will be disabled. A large
127 -- system-defined value will be used as the
128 -- interval setting.
129 --
130 -- If NULL is specified, the
131 -- current value is preserved.
132 --
133 -- topnsql (NUMBER) - Top N SQL size. The number of Top SQL
134 -- to flush for each SQL criteria
135 -- (Elapsed Time, CPU Time, Parse Calls,
136 -- Shareable Memory, Version Count).
137 --
138 -- The value for this setting will be not
139 -- be affected by the statistics/flush level
140 -- and will override the system default
141 -- behavior for the AWR SQL collection. The
142 -- setting will have a minimum value of 30
143 -- and a maximum value of 50000.
144 --
145 -- IF NULL is specified, the
146 -- current value is preserved.
147 --
148 -- topnsql (VARCHAR2) - Users are allowed to specify the following
149 -- values: ('DEFAULT', 'MAXIMUM', 'N')
150 --
151 -- Specifying 'DEFAULT' will revert the system
152 -- back to the default behavior of Top 30 for
153 -- level TYPICAL and Top 100 for level ALL.
154 --
155 -- Specifying 'MAXIMUM' will cause the system
156 -- to capture the complete set of SQL in the
157 -- cursor cache. Specifying the number 'N' is
158 -- equivalent to setting the Top N SQL with
159 -- the NUMBER type.
160 --
161 -- Specifying 'N' will cause the system
162 -- to flush the Top N SQL for each criteria.
163 -- The 'N' string is converted into the number
164 -- for Top N SQL.
165 --
166 -- dbid - database identifier for the database to
167 -- adjust setting. If NULL is specified, the
168 -- local dbid will be used.
169 --
170 -- For example, the following statement can be used to set the
171 -- Retention and Interval to their minimum settings:
172 --
173 -- dbms_workload_repository.modify_snapshot_settings
174 -- (retention => DBMS_WORKLOAD_REPOSITORY.MIN_RETENTION
175 -- interval => DBMS_WORKLOAD_REPOSITORY.MIN_INTERVAL)
176 --
177 -- The following statement can be used to set the Retention to
178 -- 8 days and the Interval to 60 minutes and the Top N SQL to
179 -- the default setting:
180 --
181 -- dbms_workload_repository.modify_snapshot_settings
182 -- (retention => 11520, interval => 60, topnsql => 'DEFAULT');
183 --
184 -- The following statement can be used to set the Top N SQL
185 -- setting to 200:
186 -- dbms_workload_repository.modify_snapshot_settings
187 -- (topnsql => 200);
188 --
189
190 PROCEDURE modify_snapshot_settings(retention IN NUMBER DEFAULT NULL,
191 interval IN NUMBER DEFAULT NULL,
192 topnsql IN NUMBER DEFAULT NULL,
193 dbid IN NUMBER DEFAULT NULL
194 );
195
196
197 PROCEDURE modify_snapshot_settings(retention IN NUMBER DEFAULT NULL,
198 interval IN NUMBER DEFAULT NULL,
199 topnsql IN VARCHAR2,
200 dbid IN NUMBER DEFAULT NULL
201 );
202
203
204 --
205 -- add_colored_sql()
206 -- Routine to add a colored SQL ID. If an SQL ID is colored, it will
207 -- always be captured in every snapshot, independent of its level
208 -- of activities (i.e. does not have to be a TOP SQL). Capturiing
209 -- will occur if the SQL is found in the cursor cache at
210 -- snapshot time.
211 --
212 -- To uncolor the SQL, call remove_colored_sql().
213 --
214 -- Input arguments:
215 -- dbid - optional dbid, default to Local DBID
216 -- sql_id - the 13-chararcter external SQL ID
217 --
218 -- Returns:
219 -- none.
220 --
221
222 PROCEDURE add_colored_sql(sql_id IN VARCHAR2,
223 dbid IN NUMBER DEFAULT NULL
224 );
225
226
227 --
228 -- remove_colored_sql()
229 -- Routine to remove a colored SQL ID, i.e. uncolored. After a
230 -- SQL is uncolored, it will no longer be captured in a snapshot
231 -- automatically, unless it makes the TOP list.
232 --
233 -- Input arguments:
234 -- dbid - optional dbid, default to Local DBID
235 -- sql_id - the 13-chararcter external SQL ID
236 --
237 -- Returns:
238 -- none.
239 --
240
241 PROCEDURE remove_colored_sql(sql_id IN VARCHAR2,
242 dbid IN NUMBER DEFAULT NULL
243 );
244
245
246 --
247 -- create_baseline()
248 -- Routine to create a baseline. A baseline is set of
249 -- of statistics defined by a (begin, end) pair of snapshots.
250 --
251 -- This routine will come in two forms: procedure and function.
252 -- The function returns the baseline_id for the baseline just created.
253 --
254 -- Input arguments:
255 -- start_snap_id - start snapshot sequence number for baseline
256 -- end_snap_id - end snapshot sequence number for baseline
257 -- baseline_name - name of baseline (required)
258 -- dbid - optional dbid, default to Local DBID
259 -- expiration - expiration in number of days for the
260 -- baseline. If NULL, then the expiration
261 -- is infinite, meaning do not drop baseline
262 -- ever. Defaults to NULL.
263 --
264 -- Returns:
265 -- NUMBER - baseline_id for the baseline just created
266 --
267
268 PROCEDURE create_baseline(start_snap_id IN NUMBER,
269 end_snap_id IN NUMBER,
270 baseline_name IN VARCHAR2,
271 dbid IN NUMBER DEFAULT NULL,
272 expiration IN NUMBER DEFAULT NULL
273 );
274
275 FUNCTION create_baseline(start_snap_id IN NUMBER,
276 end_snap_id IN NUMBER,
277 baseline_name IN VARCHAR2,
278 dbid IN NUMBER DEFAULT NULL,
279 expiration IN NUMBER DEFAULT NULL
280 ) RETURN NUMBER;
281
282 --
283 -- create_baseline()
284 -- Routine to create a baseline. This version of create_baseline()
285 -- will take in as input a time range.
286 --
287 -- Input arguments:
288 -- start_time - start time
289 -- end_time - end time
290 -- baseline_name - name of baseline (required)
291 -- dbid - optional dbid, default to Local DBID
292 -- expiration - expiration in number of days for the
293 -- baseline. If NULL, then the expiration
294 -- is infinite, meaning do not drop baseline
295 -- ever. Defaults to NULL.
296 --
297 -- Returns:
298 -- NUMBER - baseline_id for the baseline just created
299 --
300 PROCEDURE create_baseline(start_time IN DATE,
301 end_time IN DATE,
302 baseline_name IN VARCHAR2,
303 dbid IN NUMBER DEFAULT NULL,
304 expiration IN NUMBER DEFAULT NULL
305 );
306
307 FUNCTION create_baseline(start_time IN DATE,
308 end_time IN DATE,
309 baseline_name IN VARCHAR2,
310 dbid IN NUMBER DEFAULT NULL,
311 expiration IN NUMBER DEFAULT NULL
312 ) RETURN NUMBER;
313
314 --
315 -- select_baseline_details()
316 -- Routine to select the stats for a baseline. This table function
317 -- is used to fill in the stats for the WRM$_BASELINE_DETAILS table,
318 -- and to retrieve the stats for the Moving Window Baseline.
319 --
320 -- Input arguments:
321 -- baseline_id - Baseline Id to view the stats for. If the
322 -- baseline id is 0, then we are getting stats
323 -- for the moving window baseline.
324 -- dbid - database id, default to Local DBID
325 --
326 -- Returns:
327 -- awrbl_details_type_table - AWR Baseline Details Table
328 --
329 FUNCTION select_baseline_details(l_baseline_id IN NUMBER,
330 l_beg_snap IN NUMBER DEFAULT NULL,
331 l_end_snap IN NUMBER DEFAULT NULL,
332 l_dbid IN NUMBER DEFAULT NULL)
333 RETURN awrbl_details_type_table PIPELINED;
334
335 --
336 -- select_baseline_metrics()
337 -- Routine to select the metric stats for a baseline. This table function
338 -- will return the baseline metric stats for the user.
339 --
340 -- Input arguments:
341 -- baseline_name - Baseline Name to view the stats for
342 -- dbid - database id, default to Local DBID
343 -- instance_num - instance id, default to Local Instance Number
344 --
345 -- Returns:
346 -- awrbl_metric_type_table - AWR Baseline Metric Table
347 --
348 FUNCTION select_baseline_metric(l_baseline_name IN VARCHAR2,
349 l_dbid IN NUMBER DEFAULT NULL,
353 --
350 l_instance_num IN NUMBER DEFAULT NULL)
351 RETURN awrbl_metric_type_table PIPELINED;
352
354 -- rename_baseline()
355 -- Routine to rename a baseline.
356 --
357 -- This routine will allow the user to rename the Baseline.
358 --
359 -- Input arguments:
360 -- old_baseline_name - old baseline name
361 -- new_baseline_name - new baseline name
362 -- dbid - optional dbid, default to Local DBID
363 --
364 PROCEDURE rename_baseline(old_baseline_name IN VARCHAR2,
365 new_baseline_name IN VARCHAR2,
366 dbid IN NUMBER DEFAULT NULL
367 );
368
369 --
370 -- modify_baseline_window_size()
371 -- Routine to modify the window size for the default
372 -- moving window baseline
373 --
374 -- This routine modifies the window size for the Default Moving
375 -- Window Baseline. The user will input the size of the window
376 -- in number of days.
377 --
378 -- Input arguments:
379 -- window_size - New Window size for the default Moving Window
380 -- Baseline, in number of days
381 --
382 -- ***************
383 -- NOTE: The window size must be less than or equal to
384 -- the AWR retention setting. If the window size
385 -- needs to be greater than the retention setting,
386 -- the 'modify_snapshot_settings' routine can be
387 -- used to adjust the retention setting.
388 -- ***************
389 --
390 -- dbid - optional dbid, default to Local DBID
391 --
392 PROCEDURE modify_baseline_window_size(window_size IN NUMBER,
393 dbid IN NUMBER DEFAULT NULL
394 );
395
396 --
397 -- drop_baseline()
398 -- drops a baseline (by name)
399 --
400 -- Input arguments:
401 -- baseline_name - name of baseline to drop
402 -- dbid - database id, default to local DBID
403 -- cascade - if TRUE, the range of snapshots associated
404 -- with the baseline will also be dropped.
405 -- Otherwise, only the baseline is removed.
406 --
407 PROCEDURE drop_baseline(baseline_name IN VARCHAR2,
408 cascade IN BOOLEAN DEFAULT false,
409 dbid IN NUMBER DEFAULT NULL
410 );
411
412 -- **************************** --
413 -- Baseline Template routines --
414 -- **************************** --
415
416 --
417 -- create_baseline_template() - Single Time
418 -- This particular routine will create a Baseline Template for a
419 -- single time period. There will be a MMON task that will use
420 -- these inputs to create a Baseline for the time period when the
421 -- time comes.
422 --
423 -- Input arguments:
424 -- start_time - Start Time for the Baseline to be created
425 -- end_time - End Time for the Baseline to be created
426 -- baseline_name - Name of Baseline to be created.
427 -- template_name - Name for the Template
428 -- expiration - expiration in number of days for the baseline.
429 -- If NULL, then the expiration is infinite, meaning
430 -- do not drop baseline ever. Defaults to NULL.
431 -- dbid - Database Identifier for Baseline.
432 -- If NULL, then use the database identifier for the
433 -- local database. Defaults to NULL.
434 --
435 PROCEDURE create_baseline_template(start_time IN DATE,
436 end_time IN DATE,
437 baseline_name IN VARCHAR2,
438 template_name IN VARCHAR2,
439 expiration IN NUMBER DEFAULT NULL,
440 dbid IN NUMBER DEFAULT NULL
441 );
442
443 --
444 -- create_baseline_template() - Repeating Time
445 -- This particular routine will create a Baseline Template for
446 -- creating and dropping Baselines based on repeating time periods.
447 --
448 -- There will be a MMON task that will use these inputs to create the
449 -- Baseline for the relevant time periods and automatically drop
450 -- the Baselines based on their expiration.
451 --
452 -- Input arguments:
453 -- day_of_week - Day of week that the Baseline should repeat on.
454 -- Specify one of the following values:
455 -- ('SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY',
456 -- 'THURSDAY', 'FRIDAY', 'SATURDAY', 'ALL')
457 -- hour_in_day - Value of 0-23 to specify the Hour in the Day the
458 -- Baseline should start
459 -- duration - Duration (in number of hours) after hour in the day
460 -- that the Baseline should last.
461 -- start_time - Start Time for the Baseline to be created
462 -- end_time - End Time for the Baseline to be created
463 -- baseline_name_prefix - Prefix for the Name of Baseline to be created.
467 -- do not drop baseline ever.
464 -- template_name - Name for the Template
465 -- expiration - expiration in number of days for the baseline.
466 -- If NULL, then the expiration is infinite, meaning
468 -- Defaults to 35 days (5 weeks).
469 -- dbid - Database Identifier for Baseline.
470 -- If NULL, then use the database identifier for the
471 -- local database. Defaults to NULL.
472 --
473 PROCEDURE create_baseline_template(day_of_week IN VARCHAR2,
474 hour_in_day IN NUMBER,
475 duration IN NUMBER,
476 start_time IN DATE,
477 end_time IN DATE,
478 baseline_name_prefix IN VARCHAR2,
479 template_name IN VARCHAR2,
480 expiration IN NUMBER DEFAULT 35,
481 dbid IN NUMBER
482 DEFAULT NULL
483 );
484
485 --
486 -- drop_baseline_template()
487 -- This particular routine will drop a Baseline Template. The user
488 -- provides the name of the Baseline Template they would like to drop.
489 --
490 -- Input arguments:
491 -- template_name - Name of the Baseline Template to drop
492 -- dbid - Database Identifier for Baseline.
493 -- If NULL, then use the database identifier for the
494 -- local database. Defaults to NULL.
495 --
496 PROCEDURE drop_baseline_template(template_name IN VARCHAR2,
497 dbid IN NUMBER DEFAULT NULL
498 );
499
500
501 -- ***********************************************************
502 -- awr_report_text and _html (FUNCTION)
503 -- This is the table function that will display the
504 -- AWR report in either text or HTML. The output will be
505 -- one column of VARCHAR2(80) or (1500), respectively
506 --
507 -- The report will take as input the following parameters:
508 -- l_dbid - database identifier
509 -- l_inst_num - instance number
510 -- l_bid - Begin Snap Id
511 -- l_eid - End Snapshot Id
512 -- ***********************************************************
513 FUNCTION awr_report_text(l_dbid IN NUMBER,
514 l_inst_num IN NUMBER,
515 l_bid IN NUMBER,
516 l_eid IN NUMBER,
517 l_options IN NUMBER DEFAULT 0)
518 RETURN awrrpt_text_type_table PIPELINED;
519
520 FUNCTION awr_report_html(l_dbid IN NUMBER,
521 l_inst_num IN NUMBER,
522 l_bid IN NUMBER,
523 l_eid IN NUMBER,
524 l_options IN NUMBER DEFAULT 0)
525 RETURN awrrpt_html_type_table PIPELINED;
526
527 -- ***********************************************************
528 -- awr_global_report_text and _html (FUNCTION)
529 -- This is the table function that will display the
530 -- Global AWR report in either text or HTML. The output will be
531 -- one column of VARCHAR2(320) or (1500), respectively
532 --
533 -- The report will take as input the following parameters:
534 -- l_dbid - database identifier
535 -- l_inst_num - list of instance numbers to be included in report.
536 -- if set to NULL, all instances for which begin and
537 -- end snapshots are available, and which have not
538 -- been restarted between snapshots,
539 -- will be included in the report.
540 -- l_bid - Begin Snap Id
541 -- l_eid - End Snapshot Id
542 -- ***********************************************************
543 FUNCTION awr_global_report_text(l_dbid IN NUMBER,
544 l_inst_num IN AWRRPT_INSTANCE_LIST_TYPE,
545 l_bid IN NUMBER,
546 l_eid IN NUMBER,
547 l_options IN NUMBER DEFAULT 0)
548 RETURN awrdrpt_text_type_table PIPELINED;
549 --
550 -- This version accepts a comma-separated list of instance numbers
551 -- No leading zeroes are allowed and no more than 1023 characters total
552 --
553 FUNCTION awr_global_report_text(l_dbid IN NUMBER,
554 l_inst_num IN VARCHAR2,
555 l_bid IN NUMBER,
556 l_eid IN NUMBER,
557 l_options IN NUMBER DEFAULT 0)
558 RETURN awrdrpt_text_type_table PIPELINED;
559
560 FUNCTION awr_global_report_html(l_dbid IN NUMBER,
561 l_inst_num IN AWRRPT_INSTANCE_LIST_TYPE,
562 l_bid IN NUMBER,
566 --
563 l_eid IN NUMBER,
564 l_options IN NUMBER DEFAULT 0)
565 RETURN awrrpt_html_type_table PIPELINED;
567 -- This version accepts a comma-separated list of instance numbers
568 -- No leading zeroes are allowed and no more than 1023 characters total
569 --
570 FUNCTION awr_global_report_html(l_dbid IN NUMBER,
571 l_inst_num IN VARCHAR2,
572 l_bid IN NUMBER,
573 l_eid IN NUMBER,
574 l_options IN NUMBER DEFAULT 0)
575 RETURN awrrpt_html_type_table PIPELINED;
576
577 -- ***********************************************************
578 -- awr_sql_report_text (FUNCTION)
579 -- This is the function that will return the
580 -- AWR SQL Report in text format
581 -- Output will be one column of VARCHAR2(120)
582 --
583 -- awr_sql_report_html (FUNCTION)
584 -- This is the function that will return the
585 -- AWR SQL Report in html format
586 -- Output will be one column of VARCHAR2(500)
587 --
588 -- The report will take as input the following parameters:
589 -- l_dbid - database identifier
590 -- l_inst_num - instance number
591 -- l_bid - Begin Snapshot Id
592 -- l_eid - End Snapshot Id
593 -- l_sqlid - SQL Id of statement to be analyzed
594 -- l_options - Report level (not used yet)
595 FUNCTION awr_sql_report_text(l_dbid IN NUMBER,
596 l_inst_num IN NUMBER,
597 l_bid IN NUMBER,
598 l_eid IN NUMBER,
599 l_sqlid IN VARCHAR2,
600 l_options IN NUMBER DEFAULT 0)
601 RETURN awrsqrpt_text_type_table PIPELINED;
602
603 FUNCTION awr_sql_report_html(l_dbid IN NUMBER,
604 l_inst_num IN NUMBER,
605 l_bid IN NUMBER,
606 l_eid IN NUMBER,
607 l_sqlid IN VARCHAR2,
608 l_options IN NUMBER DEFAULT 0)
609 RETURN awrrpt_html_type_table PIPELINED;
610
611
612 -- ***********************************************************
613 -- awr_diff_report_text (FUNCTION)
614 -- This is the table function that will display the
615 -- AWR Compare Periods Report in text format. The output
616 -- will be one column of VARCHAR2(320).
617 --
618 -- The report will take as input the following parameters:
619 -- dbid1 - 1st database identifier
620 -- inst_num1 - 1st instance number
621 -- bid1 - 1st Begin Snap Id
622 -- eid1 - 1st End Snapshot Id
623 -- dbid2 - 2nd database identifier
624 -- inst_num2 - 2nd instance number
625 -- bid2 - 2nd Begin Snap Id
626 -- eid2 - 2nd End Snapshot Id
627 -- ***********************************************************
628 FUNCTION awr_diff_report_text(dbid1 IN NUMBER,
629 inst_num1 IN NUMBER,
630 bid1 IN NUMBER,
631 eid1 IN NUMBER,
632 dbid2 IN NUMBER,
633 inst_num2 IN NUMBER,
634 bid2 IN NUMBER,
635 eid2 IN NUMBER)
636 RETURN awrdrpt_text_type_table PIPELINED;
637
638 -- ***********************************************************
639 -- awr_diff_report_html (FUNCTION)
640 -- This is the table function that will display the
641 -- AWR Compare Periods Report in HTML format. The output
642 -- will be one column of VARCHAR2(1500).
643 --
644 -- The report will take as input the following parameters:
645 -- dbid1 - 1st database identifier
646 -- inst_num1 - 1st instance number
647 -- bid1 - 1st Begin Snap Id
648 -- eid1 - 1st End Snapshot Id
649 -- dbid2 - 2nd database identifier
650 -- inst_num2 - 2nd instance number
651 -- bid2 - 2nd Begin Snap Id
652 -- eid2 - 2nd End Snapshot Id
653 -- ***********************************************************
654 FUNCTION awr_diff_report_html(dbid1 IN NUMBER,
655 inst_num1 IN NUMBER,
656 bid1 IN NUMBER,
657 eid1 IN NUMBER,
658 dbid2 IN NUMBER,
659 inst_num2 IN NUMBER,
660 bid2 IN NUMBER,
661 eid2 IN NUMBER)
662 RETURN awrrpt_html_type_table PIPELINED;
663
664 -- ***********************************************************
665 -- awr_global_diff_report_text (FUNCTION)
666 -- This is the table function that will display the
667 -- Global AWR Compare Periods Report in text format. The output
668 -- will be one column of VARCHAR2(320).
669 --
670 -- The report will take as input the following parameters:
671 -- dbid1 - 1st database identifier
672 -- inst_num1 - 1st list of instance numbers
673 -- if set to NULL, all instances for which begin and
677 -- bid1 - 1st Begin Snap Id
674 -- end snapshots are available, and which have not
675 -- been restarted between snapshots,
676 -- will be included in the report.
678 -- eid1 - 1st End Snapshot Id
679 -- dbid2 - 2nd database identifier
680 -- inst_num2 - 2nd list of instance numbers
681 -- if set to NULL, all instances for which begin and
682 -- end snapshots are avalable, and which have not
683 -- been restarted between snapshots,
684 -- will be included in the report.
685 -- bid2 - 2nd Begin Snap Id
686 -- eid2 - 2nd End Snapshot Id
687 -- ***********************************************************
688 FUNCTION awr_global_diff_report_text(dbid1 IN NUMBER,
689 inst_num1 IN AWRRPT_INSTANCE_LIST_TYPE,
690 bid1 IN NUMBER,
691 eid1 IN NUMBER,
692 dbid2 IN NUMBER,
693 inst_num2 IN AWRRPT_INSTANCE_LIST_TYPE,
694 bid2 IN NUMBER,
695 eid2 IN NUMBER)
696 RETURN awrdrpt_text_type_table PIPELINED;
697 --
698 -- This version accepts comma-separated lists of instance numbers
699 -- for inst_num1 and inst_num2
700 -- No leading zeroes are allowed and no more than 1023 characters each
701 --
702 --
703 FUNCTION awr_global_diff_report_text(dbid1 IN NUMBER,
704 inst_num1 IN VARCHAR2,
705 bid1 IN NUMBER,
706 eid1 IN NUMBER,
707 dbid2 IN NUMBER,
708 inst_num2 IN VARCHAR2,
709 bid2 IN NUMBER,
710 eid2 IN NUMBER)
711 RETURN awrdrpt_text_type_table PIPELINED;
712
713 -- ***********************************************************
714 -- awr_global_diff_report_html (FUNCTION)
715 -- This is the table function that will display the
716 -- Gobal AWR Compare Periods Report in HTML format. The output
717 -- will be one column of VARCHAR2(1500).
718 --
719 -- The report will take as input the following parameters:
720 -- dbid1 - 1st database identifier
721 -- inst_num1 - 1st list of instance numbers
722 -- if set to NULL, all instances for which begin and
723 -- end snapshots are available, and which have not
724 -- been restarted between snapshots,
725 -- will be included in the report.
726 -- bid1 - 1st Begin Snap Id
727 -- eid1 - 1st End Snapshot Id
728 -- dbid2 - 2nd database identifier
729 -- inst_num2 - 2nd list of instance numbers
730 -- if set to NULL, all instances for which begin and
731 -- end snapshots are available, and which have not
732 -- been restarted between snapshots,
733 -- will be included in the report.
734 -- bid2 - 2nd Begin Snap Id
735 -- eid2 - 2nd End Snapshot Id
736 -- ***********************************************************
737 FUNCTION awr_global_diff_report_html(dbid1 IN NUMBER,
738 inst_num1 IN AWRRPT_INSTANCE_LIST_TYPE,
739 bid1 IN NUMBER,
740 eid1 IN NUMBER,
741 dbid2 IN NUMBER,
742 inst_num2 IN AWRRPT_INSTANCE_LIST_TYPE,
743 bid2 IN NUMBER,
744 eid2 IN NUMBER)
745 RETURN awrrpt_html_type_table PIPELINED;
746 --
747 -- This version accepts comma-separated lists of instance numbers
748 -- for inst_num1 and inst_num2
749 -- No leading zeroes are allowed and no more than 1023 characters each
750 --
751 FUNCTION awr_global_diff_report_html(dbid1 IN NUMBER,
752 inst_num1 IN VARCHAR2,
753 bid1 IN NUMBER,
754 eid1 IN NUMBER,
755 dbid2 IN NUMBER,
756 inst_num2 IN VARCHAR2,
757 bid2 IN NUMBER,
758 eid2 IN NUMBER)
759 RETURN awrrpt_html_type_table PIPELINED;
760
761
762 -- ***********************************************************
763 -- ash_report_text (FUNCTION)
764 -- This is the function that will return the
765 -- ASH Spot report in text format.
766 -- Output will be one column of VARCHAR2(80)
767 --
768 -- ash_report_html (FUNCTION)
769 -- This is the function that will return the
770 -- ASH Spot report in html format.
771 -- Output will be one column of VARCHAR2(500)
772 --
773 -- The report will take as input the following parameters:
774 -- l_dbid - Database identifier
775 -- l_inst_num - Instance number
776 -- l_btime - Begin time
777 -- l_etime - End time
781 -- should be. This argument is optional, and if it is
778 -- l_options - Report level (not used yet)
779 -- l_slot_width - Specifies (in seconds) how wide the slots used
780 -- in the "Top Activity" section of the report
782 -- not specified the time interval between l_btime and
783 -- l_etime is appropriately split into not
784 -- more than 10 slots.
785 --
786 -- The rest of the arguments are optional. All but the last one, l_data_src,
787 -- are used to specify 'report targets'. Before getting to the targets,
788 --
789 -- l_data_src - Can be used to specify a data source
790 -- 1 => memory (i.e., V$ACTIVE_SESION_HISTORY)
791 -- 2 => disk (i.e., DBA_HIST_ACTIVE_SESS_HISTORY)
792 -- 0 => both (this is the default value. Here, the
793 -- begin and end time parameters are used to
794 -- get the samples from the appropriate data
795 -- source, which can be memory, disk, or both
796 -- )
797 --
798 -- Now for 'report targets' - this is if you want to generate the ASH Report
799 -- on a particular target like a sql statement, or a session, or a
800 -- Service/Module combination.
801 --
802 -- In other words, these arguments can be specified
803 -- to restrict the ASH rows that would be used to generate the report.
804 --
805 -- For example, to generate an ASH report on a
806 -- particular SQL statement, say SQL_ID 'abcdefghij123'
807 -- pass that sql_id value to the l_sql_id argument:
808 -- l_sql_id => 'abcdefghij123'
809 --
810 -- Any combination of those optional arguments can be passed in, and
811 -- the only rows in ASH that satisfy all of those 'report targets' will
812 -- be used. In other words, if multiple 'report targets' are specified
813 -- AND conditional logic is used to connect them.
814 --
815 -- For example, to generate an ASH report on
816 -- MODULE "PAYROLL" and ACTION "PROCESS"
817 -- one can use the following predicate:
818 -- l_module => 'PAYROLL', l_action => 'PROCESS'
819 --
820 -- Valid SQL wildcards can be used in all the arguments that are of type
821 -- VARCHAR2.
822 --
823 -- =============== ================================= =========
824 -- Argument Comment Wildcards
825 -- Name Allowed?
826 -- =============== ================================= =========
827 -- l_sid Session id No
828 -- eg. V$SESSION.SID
829 --
830 -- l_sql_id SQL id Yes
831 -- eg. V$SQL.SQL_ID
832 --
833 -- l_wait_class Wait class name Yes
834 -- eg. V$EVENT_NAME.WAIT_CLASS
835 --
836 -- l_service_hash Service name hash No
837 -- eg. V$ACTIVE_SERVICES.NAME_HASH
838 --
839 -- l_module Module name Yes
840 -- eg. V$SESSION.MODULE
841 --
842 -- l_action Action name Yes
843 -- eg. V$SESSION.ACTION
844 --
845 -- l_client_id Client identifier for Yes
846 -- end-to-end tracing
847 -- eg. V$SESSION.CLIENT_IDENTIFIER
848 --
849 -- l_plsql_entry Name of PL/SQL entry subprogram Yes
850 -- e.g. "SYS.DBMS_LOB.*"
851 --
852 -- l_container Name of container Yes
853 -- e.g. V$PDBS
854 --
855 -- =============== ================================= =========
856 --
857 -- ***********************************************************
858 FUNCTION ash_report_text(l_dbid IN NUMBER,
859 l_inst_num IN NUMBER,
860 l_btime IN DATE,
861 l_etime IN DATE,
862 l_options IN NUMBER DEFAULT 0,
863 l_slot_width IN NUMBER DEFAULT 0,
864 l_sid IN NUMBER DEFAULT NULL,
865 l_sql_id IN VARCHAR2 DEFAULT NULL,
866 l_wait_class IN VARCHAR2 DEFAULT NULL,
867 l_service_hash IN NUMBER DEFAULT NULL,
868 l_module IN VARCHAR2 DEFAULT NULL,
869 l_action IN VARCHAR2 DEFAULT NULL,
870 l_client_id IN VARCHAR2 DEFAULT NULL,
871 l_plsql_entry IN VARCHAR2 DEFAULT NULL,
872 l_data_src IN NUMBER DEFAULT 0,
873 l_container IN VARCHAR2 DEFAULT NULL
874 )
875 RETURN awrrpt_text_type_table PIPELINED;
876
877 FUNCTION ash_report_html(l_dbid IN NUMBER,
878 l_inst_num IN NUMBER,
879 l_btime IN DATE,
883 l_sid IN NUMBER DEFAULT NULL,
880 l_etime IN DATE,
881 l_options IN NUMBER DEFAULT 0,
882 l_slot_width IN NUMBER DEFAULT 0,
884 l_sql_id IN VARCHAR2 DEFAULT NULL,
885 l_wait_class IN VARCHAR2 DEFAULT NULL,
886 l_service_hash IN NUMBER DEFAULT NULL,
887 l_module IN VARCHAR2 DEFAULT NULL,
888 l_action IN VARCHAR2 DEFAULT NULL,
889 l_client_id IN VARCHAR2 DEFAULT NULL,
890 l_plsql_entry IN VARCHAR2 DEFAULT NULL,
891 l_data_src IN NUMBER DEFAULT 0,
892 l_container IN VARCHAR2 DEFAULT NULL
893 )
894 RETURN awrrpt_html_type_table PIPELINED;
895 FUNCTION ash_global_report_text(l_dbid IN NUMBER,
896 l_inst_num IN VARCHAR2,
897 l_btime IN DATE,
898 l_etime IN DATE,
899 l_options IN NUMBER DEFAULT 0,
900 l_slot_width IN NUMBER DEFAULT 0,
901 l_sid IN NUMBER DEFAULT NULL,
902 l_sql_id IN VARCHAR2 DEFAULT NULL,
903 l_wait_class IN VARCHAR2 DEFAULT NULL,
904 l_service_hash IN NUMBER DEFAULT NULL,
905 l_module IN VARCHAR2 DEFAULT NULL,
906 l_action IN VARCHAR2 DEFAULT NULL,
907 l_client_id IN VARCHAR2 DEFAULT NULL,
908 l_plsql_entry IN VARCHAR2 DEFAULT NULL,
909 l_data_src IN NUMBER DEFAULT 0,
910 l_container IN VARCHAR2 DEFAULT NULL
911 )
912 RETURN awrdrpt_text_type_table PIPELINED;
913 FUNCTION ash_global_report_html(l_dbid IN NUMBER,
914 l_inst_num IN VARCHAR2,
915 l_btime IN DATE,
916 l_etime IN DATE,
917 l_options IN NUMBER DEFAULT 0,
918 l_slot_width IN NUMBER DEFAULT 0,
919 l_sid IN NUMBER DEFAULT NULL,
920 l_sql_id IN VARCHAR2 DEFAULT NULL,
921 l_wait_class IN VARCHAR2 DEFAULT NULL,
922 l_service_hash IN NUMBER DEFAULT NULL,
923 l_module IN VARCHAR2 DEFAULT NULL,
924 l_action IN VARCHAR2 DEFAULT NULL,
925 l_client_id IN VARCHAR2 DEFAULT NULL,
926 l_plsql_entry IN VARCHAR2 DEFAULT NULL,
927 l_data_src IN NUMBER DEFAULT 0,
928 l_container IN VARCHAR2 DEFAULT NULL
929 )
930 RETURN awrrpt_html_type_table PIPELINED;
931
932 -- ***********************************************************
933 -- NAME: ash_report_analytics
934 -- This is the function that will return the
935 -- ASH (analytics) active report. The report format is html.
936 --
937 -- PARAMETERS:
938 -- The report will take as input the following parameters:
939 -- dbid - database identifier - default to current dbid
940 -- inst_id - instance number - default to current instance
941 -- begin_time - begin time
942 -- end_time - end time
943 -- report_level - report level. For example, <sqlid>{histogram}.
944 -- describes a list of components to build.
945 -- DEFAULT = <wait_class>{histogram}
946 -- filter_list - describes a list of filters to be applied
947 -- DEFAULT = NULL (no filters)
948 -- RETURN
949 -- html active report as a clob for ash analytics
950 --
951 FUNCTION ash_report_analytics(dbid IN NUMBER := NULL,
952 inst_id IN NUMBER := NULL,
953 begin_time IN date,
954 end_time IN date,
955 report_level IN VARCHAR2 := NULL,
956 filter_list IN VARCHAR2 := NULL)
957 RETURN CLOB;
958
959 --
960 -- control_restricted_snapshot()
961 -- This routine controls if AWR snapshots are allowed to occur
962 -- even if the restricted session mode has been enabled for the
963 -- database. Calling this with allow=true will allow the AWR
964 -- snapshot capture for the local instance from which this routine
965 -- is called. Calling this with allow=false will disallow the AWR
966 -- snapshot capture in restricted session mode.
967 --
968 -- By default, if the database is in restricted session mode, AWR
969 -- snapshots are NOT allowed.
970 --
971 -- This routine must be called on each instance if the user wants
972 -- the snapshots to happen for each instance.
973 --
977 -- Input arguments:
974 -- This routine only affects the behavior of the following procedure:
975 -- DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT()
976 --
978 -- allow - boolean to allow snapshots in restricted session mode
979 --
980 PROCEDURE control_restricted_snapshot(allow IN BOOLEAN);
981
982 -- *************************************************************************
983 -- awr_set_report_thresholds (PROCEDURE)
984 -- Allows configuring of specified report thresholds. Allows control of
985 -- number of rows in the report.
986 --
987 -- Parameters
988 -- top_n_events - number of most significant wait events to be included
989 -- top_n_files - number of most active files to be included
990 -- top_n_segments - number of most active segments to be included
991 -- top_n_services - number of most active services to be included
992 -- top_n_sql - number of most significant SQL statements to be
993 -- included
994 -- top_n_sql_max - number of SQL statements to be included if their
995 -- activity is greater than that specified by
996 -- top_sql_pct.
997 -- top_sql_pct - significance threshold for SQL statements between
998 -- top_n_sql and top_n_max_sql
999 -- shmem_threshold - shared memory low threshold
1000 -- versions_threshold - plan version count low threshold
1001 -- top_n_disks - number of cell disks with most I/O
1002 -- outlier_pct - percentage of maximum capacity before we display
1003 -- outliers for Exadata sections
1004 -- outlier_cpu_pct - threshold for mean % cpu to display outliers
1005 --
1006 -- Note: effect of each setting depends on the type of report being
1007 -- generated as well as on the underlying AWR data. Not all
1008 -- settings are meaningful for each report type.
1009 -- Invalid settings (such as negative numbers, etc,) are ignored.
1010 -- *************************************************************************
1011 PROCEDURE awr_set_report_thresholds(top_n_events IN NUMBER DEFAULT NULL,
1012 top_n_files IN NUMBER DEFAULT NULL,
1013 top_n_segments IN NUMBER DEFAULT NULL,
1014 top_n_services IN NUMBER DEFAULT NULL,
1015 top_n_sql IN NUMBER DEFAULT NULL,
1016 top_n_sql_max IN NUMBER DEFAULT NULL,
1017 top_sql_pct IN NUMBER DEFAULT NULL,
1018 shmem_threshold IN NUMBER DEFAULT NULL,
1019 versions_threshold IN NUMBER DEFAULT NULL,
1020 top_n_disks IN NUMBER DEFAULT NULL,
1021 outlier_pct IN NUMBER DEFAULT NULL,
1022 outlier_cpu_pct IN NUMBER DEFAULT NULL
1023 );
1024
1025 --
1026 -- purge_sql_details()
1027 -- This routine purges rows from the AWR SQL details tables
1028 -- (WRH$_SQLTEXT and WHR$_SQL_PLAN) that are no longer required.
1029 --
1030 -- This may be helpful in an environment that uses a lot of
1031 -- literals (leading to SQL statements that are only run once)
1032 -- and AWR baselines (preserved snapshots).
1033 --
1037 -- Input arguments:
1034 -- Note that this routine does not rebuild segments, nor
1035 -- does it do any other kind of DDL.
1036 --
1038 -- numrows - maximum number of rows to purge at a time
1039 -- dbid - database identifier
1040 --
1041 PROCEDURE purge_sql_details(numrows IN NUMBER DEFAULT NULL,
1042 dbid IN NUMBER DEFAULT NULL);
1043
1044 --
1045 -- update_object_info()
1046 -- This routine updates rows of WRH$_SEG_STAT_OBJ table
1047 -- that represent objects in the local database.
1048 --
1049 -- This routine attempts to determine current names for all objects
1050 -- belonging to the local database, except those with 'MISSING' and/or
1051 -- 'TRANSIENT' values in the name columns.
1052 -- Amount of work performed at each invocation of this routine may be
1053 -- controlled by appropriate settings of the input parameters.
1054 -- modval are selected for validation. Default settings of modbase (1) and
1055 -- modval (0) cause all rows to be examined.
1056 --
1057 --
1058 -- Input arguments:
1059 -- maxrows - maximum number of rows that will be updated
1060 -- during each invocation of this routine.
1061 -- Default value, 0, means there is no limit.
1062 --
1063
1064 PROCEDURE update_object_info(maxrows IN NUMBER DEFAULT 0);
1065
1066 --
1067 -- update_datafile_info()
1068 -- This routine updates WRH$_DATAFILE rows for the datafile name and
1069 -- tablespace name. Whenever this procedure runs, it will update these
1070 -- values with the current information in the database.
1071 --
1072 -- This routine is useful when a datafile/tablespace has been moved or
1073 -- renamed. As this change is generally not always captured in the next
1074 -- snapshot in AWR. This change will be captured at max after some
1075 -- (generally 50) snapshots. So the AWR and AWR report may be wrong with
1076 -- respect to data file name or tablespace name for that duration.
1077 --
1078 -- To fix this problem, we can use this procedure to sync the table
1079 -- WRH$_DATAFILE with the current information in database.
1080 --
1081
1082 PROCEDURE update_datafile_info;
1083
1084 END dbms_workload_repository;
[Home] [Help]