1 PACKAGE dbms_advisor
2 authid current_user
3 IS
4
5 -------------------------------------------------------------------------------
6 -- Advisor names and ids
7 --
8 -- NOTE: DO NOT CHANGE THE ADVISOR ID NUMBERS!!!!!!
9 -- External code may rely on advisor names as well.
10 -- Do not change names or numbers between releases.
11 --
12 -------------------------------------------------------------------------------
13
14 ADV_NAME_DEFAULT constant varchar2(30) := 'Default Advisor';
15 ADV_NAME_ADDM constant varchar2(30) := 'ADDM';
16 ADV_NAME_SQLACCESS constant varchar2(30) := 'SQL Access Advisor';
17 ADV_NAME_UNDO constant varchar2(30) := 'Undo Advisor';
18 ADV_NAME_SQLTUNE constant varchar2(30) := 'SQL Tuning Advisor';
19 ADV_NAME_SEGMENT constant varchar2(30) := 'Segment Advisor';
20 ADV_NAME_SQLWM constant varchar2(30) := 'SQL Workload Manager';
21 ADV_NAME_TUNEMV constant varchar2(30) := 'Tune MView';
22 ADV_NAME_SQLPA constant varchar2(30) := 'SQL Performance Analyzer';
23 ADV_NAME_SQLREPAIR constant varchar2(30) := 'SQL Repair Advisor';
24 ADV_NAME_COMPRESS constant varchar2(30) := 'Compression Advisor';
25 ADV_NAME_SPM_EVOLVE constant varchar2(30) := 'SPM Evolve Advisor';
26
27 ADV_ID_DEFAULT constant number := 0;
28 ADV_ID_ADDM constant number := 1;
29 ADV_ID_SQLACCESS constant number := 2;
30 ADV_ID_UNDO constant number := 3;
31 ADV_ID_SQLTUNE constant number := 4;
32 ADV_ID_SEGMENT constant number := 5;
33 ADV_ID_SQLWM constant number := 6;
34 ADV_ID_TUNEMV constant number := 7;
35 ADV_ID_SQLPA constant number := 8;
36 ADV_ID_SQLREPAIR constant number := 9;
37 ADV_ID_COMPRESS constant number := 10;
38 ADV_ID_SPM_EVOLVE constant number := 11;
39
40 -------------------------------------------------------------------------------
41 -- Common constants
42 -------------------------------------------------------------------------------
43
44 ADVISOR_ALL constant number := -995;
45 ADVISOR_CURRENT constant number := -996;
46 ADVISOR_DEFAULT constant number := -997;
47 ADVISOR_UNLIMITED constant number := -998;
48 ADVISOR_UNUSED constant number := -999;
49
50 -------------------------------------------------------------------------------
51 -- SQL Access Advisor constants
52 -------------------------------------------------------------------------------
53
54 SQLACCESS_GENERAL constant varchar2(20) := 'SQLACCESS_GENERAL';
55 SQLACCESS_OLTP constant varchar2(20) := 'SQLACCESS_OLTP';
56 SQLACCESS_WAREHOUSE constant varchar2(20) := 'SQLACCESS_WAREHOUSE';
57
58 SQLACCESS_ADVISOR constant varchar2(30) := ADV_NAME_SQLACCESS;
59 TUNE_MVIEW_ADVISOR constant varchar2(30) := ADV_NAME_TUNEMV;
60 SQLWORKLOAD_MANAGER constant varchar2(30) := ADV_NAME_SQLWM;
61
62 -------------------------------------------------------------------------------
63 -- Common types
64 -------------------------------------------------------------------------------
65 -- this type is used to pass a list of task prameters to the execute_task
66 -- function. This is used only for advisor that support multi-execution.
67 TYPE argList IS TABLE OF sys.wri$_adv_parameters.value%TYPE;
68
69 -- this type is identical to VARCHAR2S from the DBMS_SQL package and
70 -- is redefined here due to bootstrapping problems
71 TYPE varchar2adv IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
72
73 -------------------------------------------------------------------------------
74 -- The following procedures are common to all advisors
75 -------------------------------------------------------------------------------
76
77 -- PROCEDURE DBMS_ADVISOR.CANCEL_TASK
78 -- PURPOSE: Cancels a currently executing task operation. All intermediate
79 -- and result data will be removed from the task.
80 -- ADVISOR SUPPORT: SQL Access Advisor
81 -- PARAMETERS:
82 -- TASK_NAME
83 -- Valid task name
84
85 procedure cancel_task (task_name in varchar2);
86
87 -- PROCEDURE DBMS_ADVISOR.CREATE_TASK
88 -- PURPOSE: Creates a new advisor task in the repository
89 -- ADVISOR SUPPORT: All advisors
90 -- PARAMETERS:
91 -- ADVISOR_NAME
92 -- Name of the advisor that will use the task for its operations.
93 -- Advisors are defined in the DBA_ADVISOR_DEFINITIONS table.
94 -- TASK_ID
95 -- Returns a system-generated task identifier number.
96 -- TASK_NAME
97 -- An optional task name. Task names must be unique to the user.
98 -- If not specified, a name will be generated by the system.
99 -- TASK_DESC
100 -- An optional value that provides a meaningful description of the
101 -- intended use of the task.
102 -- TEMPLATE
103 -- Optional task that will be used to set default values for the
104 -- new task.
105 -- IS_TEMPLATE
106 -- Optional boolean to set the new task as template
107 -- HOW_CREATED
108 -- Optional how identifier
109
110 procedure create_task (advisor_name in varchar2,
111 task_id out number,
112 task_name in out varchar2,
113 task_desc in varchar2 := null,
114 template in varchar2 := null,
115 is_template in varchar2 := 'FALSE',
116 how_created in varchar2 := null);
117
118 -- PROCEDURE DBMS_ADVISOR.CREATE_TASK
119 -- PURPOSE: Creates a new advisor task in the repository
120 -- ADVISOR SUPPORT: All advisors
121 -- PARAMETERS:
122 -- ADVISOR_NAME
123 -- Name of the advisor that will use the task for its operations.
124 -- Advisors are defined in the DBA_ADVISOR_DEFINITIONS table.
125 -- TASK_NAME
126 -- Task names must be unique to the user.
127 -- TASK_DESC
128 -- An optional value that provides a meaningful description of the
129 -- intended use of the task.
130 -- TEMPLATE
131 -- Optional task that will be used to set default values for the
132 -- new task.
133 -- IS_TEMPLATE
134 -- Optional boolean to set the new task as template
135 -- HOW_CREATED
136 -- Optional source identifier
137
138 procedure create_task (advisor_name in varchar2,
139 task_name in varchar2,
140 task_desc in varchar2 := null,
141 template in varchar2 := null,
142 is_template in varchar2 := 'FALSE',
143 how_created in varchar2 := null);
144
145 -- PROCEDURE DBMS_ADVISOR.CREATE_TASK
146 -- PURPOSE: Creates a new advisor task as a child task of an existing task.
147 -- This version of CREATE_TASK is for use by controlling advisors
148 -- such as HDM.
149 -- ADVISOR SUPPORT: All advisors
150 -- PARAMETERS:
151 -- PARENT_TASK_NAME
152 -- Name of the parent task that is starting the sub-advisor
153 -- operation.
154 -- REC_ID
155 -- The recommendation idenfier to which the new task will be
156 -- associated.
157 -- TASK_ID
158 -- Returns a system-generated task identifier number.
159 -- TASK_NAME
160 -- An optional task name. Task names must be unique to the user.
161 -- If not specified, a name will be generated by the system.
162 -- TASK_DESC
163 -- An optional value that provides a meaningful description of the
164 -- intended use of the task.
165 -- TEMPLATE
166 -- Optional task that will be used to set default values for the
167 -- new task.
168
169 procedure create_task (parent_task_name in varchar2,
170 rec_id in number,
171 task_id out number,
172 task_name in out varchar2,
173 task_desc in varchar2,
174 template in varchar2);
175
176 -- PROCEDURE DBMS_ADVISOR.DELETE_TASK
177 -- PURPOSE: Deletes the specified task from the repository
178 -- ADVISOR SUPPORT: All advisors
179 -- PARAMETERS:
180 -- TASK_NAME
181 -- Name of the task
182 --
183 procedure delete_task (task_name in varchar2);
184
185 -- PROCEDURE DBMS_ADVISOR.EXECUTE_TASK
186 -- PURPOSE: Executes the specified task. This procedure has two flavors.
187 -- ADVISOR SUPPORT: All advisors
188 -- PARAMETERS:
189 -- TASK_NAME
190 -- Name of the task
191 -- NOTICE:
192 -- This procedure is kept for backward compatibility
193 -- and for advisor that do not support multiple executions
194 -- tasks or simply because they do not want to be exposed to
195 -- the concept of execution
196 --
197 procedure execute_task(task_name IN VARCHAR2);
198
199 -- FUNCTION EXECUTE_TASK
200 -- PURPOSE: Executes the specified task. There two flavors of this procedure.
201 -- ADVISOR SUPPORT: All advisors
202 -- PARAMETERS:
203 -- TASK_NAME
204 -- Name of the task
205 -- EXECUTION_TYPE
206 -- Type of the action to perform by the function.
207 -- If NULL it will default to the value of the DEFAULT_EXECUTION_TYPE
208 -- parameter.
209 -- EXECUTION_NAME
210 -- A name to qualify and identify an execution. If not specified, it
211 -- be generated by the advisor and returned by function.
212 -- EXECUTION_DESC
213 -- A 256-length string describing the execution.
214 -- EXECUTION_PARAMS
215 -- List of parameters (name, value) for the specified execution.
216 -- Notice that execution parameters are real task parameters that are
217 -- have effect only on the execution they sepecified for.
218 -- Example: arglist('time_limit', 12, 'username', 'foo')
219 -- RETURN:
220 -- Name of the execution
221 FUNCTION execute_task(
222 task_name IN VARCHAR2,
223 execution_type IN VARCHAR2 := NULL,
224 execution_name IN VARCHAR2 := NULL,
225 execution_desc IN VARCHAR2 := NULL,
226 execution_params IN argList := NULL)
227 RETURN VARCHAR2;
228
229 -- PROCEDURE DBMS_ADVISOR.INTERRUPT_TASK
230 -- PURPOSE: Stops a currently executing task. The task will end its
231 -- operations as it would at a normal exit. The user will be able
232 -- to access any recommendations that exist to this point.
233 -- ADVISOR SUPPORT: SQL Access Advisor
234 -- PARAMETERS:
235 -- TASK_NAME
236 -- Name of the task
237 --
238 procedure interrupt_task (task_name in varchar2);
239
240 -- PROCEDURE DBMS_ADVISOR.MARK_RECOMMENDATION
241 -- PURPOSE: Sets the annotation_status for a particulare recommendation
242 -- ADVISOR SUPPORT: SQL Access Advisor
243 -- PARAMETERS:
244 -- TASK_NAME
245 -- Name of the task
246 -- ID
247 -- Recommendation identifier number
248 -- ACTION
249 -- Status of the recommendation
250
251 procedure mark_recommendation (task_name in varchar2,
252 id in number,
253 action in varchar2);
254
255 -- PROCEDURE DBMS_ADVISOR.RESET_TASK
256 -- PURPOSE: Resets a task to its initial state. All intermediate and
257 -- recommendation data will be deleted.
258 -- ADVISOR SUPPORT: All Advisors
259 -- PARAMETERS:
260 -- TASK_NAME
261 -- Name of the task
262 --
263 procedure reset_task(task_name in varchar2);
264
265 -- PROCEDURE DBMS_ADVISOR.RESUME_TASK
266 -- PURPOSE: Resumes a previously interrupted task.
267 -- ADVISOR SUPPORT: None
268 -- PARAMETERS:
269 -- TASK_NAME
270 -- Name of the task
271 --
272 procedure resume_task(task_name in varchar2);
273
274 -- PROCEDURE DBMS_ADVISOR.SET_TASK_PARAMETER
275 -- PURPOSE: Sets the specified task parameter value.
276 -- ADVISOR SUPPORT: All advisors
277 -- PARAMETERS:
278 -- TASK_NAME
279 -- Name of the task
280 -- PARAMETER
281 -- Name of the task parameter
282 -- VALUE
283 -- Value to be set
284
285 procedure set_task_parameter (task_name in varchar2,
286 parameter in varchar2,
287 value in varchar2);
288
289 procedure set_task_parameter (task_name in varchar2,
290 parameter in varchar2,
291 value in number);
292
293 -- PROCEDURE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAM
294 -- PURPOSE: Sets the specified task parameter value as default for
295 -- all new tasks of a specific type
296 -- ADVISOR SUPPORT: All advisors
297 -- PARAMETERS:
298 -- ADVISOR_NAME
299 -- Name of the advisor type
300 -- PARAMETER
301 -- Name of the task parameter
302 -- VALUE
303 -- Value to be set
304
305 procedure set_default_task_parameter (advisor_name in varchar2,
306 parameter in varchar2,
307 value in varchar2);
308
309 procedure set_default_task_parameter (advisor_name in varchar2,
310 parameter in varchar2,
311 value in number);
312
313 -- PROCEDURE DBMS_ADVISOR.CREATE_OBJECT
314 -- PURPOSE: Creates a new task object
315 -- ADVISOR SUPPORT: All advisors
316 -- PARAMETERS:
317 -- TASK_NAME
318 -- Name of the task
319 -- OBJECT_TYPE
320 -- Type of Advisor Object being created.
321 -- See dba_advisor_object_types
322 -- ATTR1
323 -- Attribute of the object
324 -- ATTR2
325 -- Attribute of the object
326 -- ATTR3
327 -- Attribute of the object
328 -- ATTR4
329 -- Attribute of the object
330 -- ATTR5
331 -- Attribute of the object
332 -- OBJECT_ID
333 -- OUT Param: Generated ID for the object
334
335 PROCEDURE create_object(task_name IN VARCHAR2 ,
336 object_type IN VARCHAR2 ,
337 attr1 IN VARCHAR2 := null,
338 attr2 IN VARCHAR2 := null,
339 attr3 IN VARCHAR2 := null,
340 attr4 IN clob := NULL,
341 object_id OUT NUMBER);
342
343
344 PROCEDURE create_object(task_name IN VARCHAR2 ,
345 object_type IN VARCHAR2 ,
346 attr1 IN VARCHAR2 := null,
347 attr2 IN VARCHAR2 := null,
348 attr3 IN VARCHAR2 := null,
349 attr4 IN clob := NULL,
350 attr5 IN VARCHAR2 := null,
351 object_id OUT NUMBER);
352
353 -- PROCEDURE DBMS_ADVISOR.UPDATE_OBJECT
354 -- PURPOSE: Updates an existing task object
355 -- Parameters that are NULL will have no effect on the
356 -- existing value of the column
357 -- ADVISOR SUPPORT: All advisors
358 -- PARAMETERS:
359 -- TASK_NAME
360 -- Name of the task
361 -- OBJECT_ID
362 -- Id of the object
363 -- ATTR1
367 -- ATTR3
364 -- Attribute of the object
365 -- ATTR2
366 -- Attribute of the object
368 -- Attribute of the object
369 -- ATTR4
370 -- Attribute of the object
371 -- ATTR5
372 -- Attribute of the object
373
374 PROCEDURE update_object(task_name IN VARCHAR2 ,
375 object_id IN NUMBER ,
376 attr1 IN VARCHAR2 := null,
377 attr2 IN VARCHAR2 := null,
378 attr3 IN VARCHAR2 := null,
379 attr4 IN clob := NULL,
380 attr5 IN VARCHAR2 := null);
381
382
383 -- PROCEDURE DBMS_ADVISOR.CREATE_FILE
384 -- PURPOSE: Creates an output file and writes the buffer to the
385 -- file.
386 -- ADVISOR SUPPORT: All Advisors
387 -- PARAMETERS:
388 -- BUFFER
389 -- Buffer to write to the file
390 -- LOCATION
391 -- Valid directory object where the file will be placed. A
392 -- directory object must be defined using the SQL CREATE
393 -- DIRECTORY command.
394 -- FILENAME
395 -- Name of the output file to receive the report information.
396
397 procedure create_file (buffer in clob,
398 location in varchar2,
399 filename in varchar2);
400
401 -- FUNCTION DBMS_ADVISOR.GET_TASK_REPORT
402 -- PURPOSE: Creates and returns a report for the specified task.
403 -- ADVISOR SUPPORT: All Advisors
404 -- PARAMETERS:
405 -- TASK_NAME
406 -- Name of the task from which the report will be created.
407 -- TYPE
408 -- Possible values are: TEXT.
409 -- Note that in the future, HTML and XML will be supported.
410 -- LEVEL
411 -- Possible values are BASIC, TYPICAL, and ALL
412 -- SECTION
413 -- Advisor-specific report sections
414 -- OWNER_NAME
415 -- Owner of the task. If specified the system will check to see
416 -- if the current user has read privileges to the task data.
417 -- EXECUTION_NAME
418 -- Identifier of a specific exectuion of the task.
419 -- This is needed by only advisors that allows their tasks
420 -- to be executed mutilple times.
421 -- OBJECT_ID
422 -- Identifier of an advisor object (from dba/user_advisor_objects)
423 -- that can be targeted by the report.
424 -- Returns:
425 -- Return buffer receiving the report
426 function get_task_report (task_name in varchar2,
427 type in varchar2 := 'TEXT',
428 level in varchar2 := 'TYPICAL',
429 section in varchar2 := 'ALL',
430 owner_name in varchar2 := NULL,
431 execution_name in varchar2 := NULL,
432 object_id in number := NULL)
433 return clob;
434
435 -- FUNCTION DBMS_ADVISOR.GET_TASK_SCRIPT
436 -- PURPOSE: Creates and returns executable script for the specified task.
437 -- ADVISOR SUPPORT: All Advisors
438 -- PARAMETERS:
439 -- TASK_NAME
440 -- Name of the task from which the script will be created.
441 -- TYPE
442 -- Script type. A set recommendation actions can exist that
443 -- either implement a change or undo a change. Valid values are:
444 -- IMPLEMENTATION
445 -- UNDO
446 -- REC_ID
447 -- Optional recommendation id to extract a single recommendation
448 -- ACT_ID
449 -- Optional action id to extract a single action as a script
450 -- OWNER_NAME
451 -- Optional task owner
452 -- EXECUTION_NAME
453 -- Identifier of a specific exectuion of the task.
454 -- This is needed by only advisors that allows their tasks
455 -- to be executed mutilple times.
456 -- OBJECT_ID
457 -- Identifier of an advisor object (from dba/user_advisor_objects)
458 -- that can be targeted by the script.
459 -- Returns:
460 -- Return buffer receiving the script
461 function get_task_script (task_name in varchar2,
462 type in varchar2 := 'IMPLEMENTATION',
463 rec_id in number := NULL,
464 act_id in number := NULL,
465 owner_name in varchar2 := NULL,
466 execution_name in varchar2 := NULL,
467 object_id in number := NULL)
468 return clob;
469
470 -- PROCEDURE DBMS_ADVISOR.IMPLEMENT_TASK
471 -- PURPOSE: Implements the recommendations of the specified task.
472 -- ADVISOR SUPPORT: SQL Access Advisor
473 -- PARAMETERS:
474 -- TASK_NAME
475 -- Name of the task
476 -- REC_ID
477 -- Optional recommendation id
478 -- EXIT_ON_ERROR
479 -- Optional boolean to exit on first error
480
481 procedure implement_task (task_name in varchar2,
482 rec_id in number := NULL,
486 -- PURPOSE: Performs an analysis given 1 to 3 simple attributes
483 exit_on_error in boolean := NULL);
484
485 -- PROCEDURE DBMS_ADVISOR.QUICK_TUNE
487 -- ADVISOR SUPPORT: All Advisors
488 -- PARAMETERS:
489 -- ADVISOR_NAME
490 -- Name of the advisor that will perform the analysis
491 -- TASK_NAME
492 -- Task names must be unique to the user.
493 -- ATTR1
494 -- Attribute 1 - advisor-specific data
495 -- ATTR2
496 -- Attribute 2 - advisor-specific data
497 -- ATTR3
498 -- Attribute 3 - advisor-specific data
499 -- TEMPLATE
500 -- Name of a task or template from which initial settings will
501 -- copied.
502 -- IMPLEMENT
503 -- Boolean to signal implementation
504 -- DESCRIPTION
505 -- Optional description of the task
506
507 procedure quick_tune (advisor_name in varchar2,
508 task_name in varchar2,
509 attr1 in clob := null,
510 attr2 in varchar2 := null,
511 attr3 in number := null,
512 template in varchar2 := null,
513 implement in boolean := FALSE,
514 description in varchar2 := null);
515
516 -- PROCEDURE DBMS_ADVISOR.TUNE_MVIEW
517 -- PURPOSE: Tune a Create Materialized View statement to
518 -- ADVISOR SUPPORT: SQL Access Advisor
519 -- PARAMETERS:
520 -- TASK_NAME
521 -- The user can pass in a user-defined task name or
522 -- get a returned system-generated task name.
523 -- MV_CREATE_STMT
524 -- CREATE MATERIALIZED VIEW SQL statement to tune
525
526 procedure tune_mview (task_name in out varchar2,
527 mv_create_stmt in clob);
528
529 -- PROCEDURE DBMS_ADVISOR.RESET_TASK
530 -- PURPOSE: Resets the specified task to its initial state. All
531 -- intermediate and recommendation data will be removed.
532 -- ADVISOR SUPPORT: All advisors
533 -- PARAMETERS:
534 -- TASK_NAME
535 -- Name of the task
536
537 -- PROCEDURE DBMS_ADVISOR.UPDATE_REC_ATTRIBUTES
538 -- PURPOSE: Updates an existing recommendation for the specified task
539 -- ADVISOR SUPPORT: SQL Access Advisor
540 -- PARAMETERS:
541 -- TASK_NAME
542 -- Name of the task
543 -- REC_ID
544 -- Recommendation identifier number
545 -- ACTION_ID
546 -- Action identifier number
547 -- ATTRIBUTE_NAME
548 -- Keyword name for the attribute
549 -- VALUE
550 -- Attribute value
551
552 procedure update_rec_attributes (task_name in varchar2,
553 rec_id in number,
554 action_id in number,
555 attribute_name in varchar2,
556 value in varchar2);
557
558 -- PROCEDURE DBMS_ADVISOR.GET_REC_ATTRIBUTES
559 -- PURPOSE: Retrievs an existing recommendation attribute for
560 -- the specified task
561 -- ADVISOR SUPPORT: SQL Access Advisor
562 -- PARAMETERS:
563 -- TASK_NAME
564 -- Name of the task
565 -- REC_ID
566 -- Recommendation identifier number
567 -- ACTION_ID
568 -- Action identifier number
569 -- ATTRIBUTE_NAME
570 -- Keyword name for the attribute
571 -- VALUE
572 -- Attribute value
573 -- OWNER_NAME
574 -- Optional task owner
575
576 procedure get_rec_attributes (task_name in varchar2,
577 rec_id in number,
578 action_id in number,
579 attribute_name in varchar2,
580 value out varchar2,
581 owner_name in varchar2 := NULL);
582
583 -- PROCEDURE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES
584 -- PURPOSE: Updates a task's attributes
585 -- ADVISOR SUPPORT: All advisors
586 -- PARAMETERS:
587 -- TASK_NAME
588 -- Name of the task
589 -- NEW_NAME
590 -- New task name (optional)
591 -- DESCRIPTION
592 -- New task description (optional)
593 -- READ_ONLY
594 -- TRUE if task is to be marked read-only (optional)
595 -- IS_TEMPLATE
596 -- TRUE if task is to be used as a template
597 -- HOW_CREATED
598 -- Sets the source attribute for a task
599
600 procedure update_task_attributes (task_name in varchar2,
601 new_name in varchar2 := null,
602 description in varchar2 := null,
603 read_only in varchar2 := null,
604 is_template in varchar2 := null,
605 how_created in varchar2 := null);
606
610
607 -------------------------------------------------------------------------------
608 -- Utility procedures
609 -------------------------------------------------------------------------------
611
612 -- FUNCTION DBMS_ADVISOR.FORMAT_MESSAGE_GROUP
613 -- PURPOSE: Retrieves and formats a set of messages from the advisor
614 -- message table.
615 -- ADVISOR SUPPORT: All advisors
616 -- PARAMETERS:
617 -- GROUP_ID
618 -- Message-set identifier number
619 -- RETURNS:
620 -- Formatted messages as varchar2
621
622 function format_message_group(group_id IN number, msg_type IN number := 0)
623 return varchar2;
624
625
626 -- FUNCTION DBMS_ADVISOR.FORMAT_MESSAGE
627 -- PURPOSE: Retrieves test from an Oracle Message file
628 -- ADVISOR SUPPORT: All advisors
629 -- PARAMETERS:
630 -- MSG_ID
631 -- Message identifier number (fac-nnnnn)
632 -- RETURNS:
633 -- Formatted messages as varchar2
634
635 function format_message(msg_id IN varchar2)
636 return varchar2;
637
638
639 -- PROCEDURE DBMS_ADVISOR.CHECK_PRIVS
640 -- PURPOSE: Checks for required advisor privileges
641 -- ADVISOR SUPPORT: All advisors
642 -- PARAMETERS:
643 -- none
644 --
645 procedure check_privs;
646
647 -- PROCEDURE DBMS_ADVISOR.CHECK_READ_PRIVS
648 -- PURPOSE: Checks whether the current user has read privileges for another
649 -- user's tasks. This is typically used only by DBAs to
650 -- access other users's data, hence we query the dba_* views for
651 -- now. General support can be added later on once we define
652 -- all_* views.
653 -- ADVISOR SUPPORT: All advisors
654 -- PARAMETERS:
655 -- OWNER_NAME : user name of the user whose tasks the current user
656 -- wishes to access.
657 --
658
659 procedure check_read_privs(owner_name IN VARCHAR2);
660
661 -- PROCEDURE DBMS_ADVISOR.SETUP_REPOSITORY
662 -- PURPOSE: Sets up advisor framework repository for use. Re-execution
663 -- of this procedure has no ill-effects.
664 -- PARAMETERS:
665 -- None
666
667 procedure setup_repository;
668
669 -- PROCEDURE DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT
670 -- PURPOSE: Adds a single statement to a workload.
671 -- PARAMETERS:
672 -- WORKLOAD_NAME
673 -- Name of the workload
674 -- MODULE
675 -- Application module name for the statement
676 -- ACTION
677 -- Application action for the statement
678 -- CPU_TIME
679 -- Total CPU time for the statement
680 -- ELAPSED_TIME
681 -- Total elapsed time for the statement
682 -- DISK_READS
683 -- Total disk-read count for the statement
684 -- BUFFER_GETS
685 -- Total buffer-get count for the statement
686 -- ROWS_PROCESSED
687 -- Total rows-processed count for the statement
688 -- OPTIMIZER_COST
689 -- Optimizer cost value
690 -- EXECUTIONS
691 -- Number of times statement would be executed
692 -- PRIORITY
693 -- User-specified priority
694 -- LAST_EXECUTION_DATE
695 -- Last time the statement was executed
696 -- STAT_PERIOD
697 -- Time interval in seconds from which statement stats were
698 -- calculated.
699 -- USERNAME
700 -- Oracle username under which the statement was executed
701 -- SQL_TEXT
702 -- SQL statement
703 -- ROWID_VALUE
704 -- RowId of the SQL statement to retrieve
705 -- OWNER
706 -- Owner name of the target table containing the SQL statement
707 -- TABLENAME
708 -- Table name of the target table containing the SQL statement
709 -- HASH_VALUE
710 -- Oracle hash value of the target SQL statement
711 -- ADDRESS
712 -- Oracle address of the target SQL statement
713
714 procedure add_sqlwkld_statement (workload_name in varchar2,
715 module in varchar2 := '',
716 action in varchar2 := '',
717 cpu_time in number := 0,
718 elapsed_time in number := 0,
719 disk_reads in number := 0,
720 buffer_gets in number := 0,
721 rows_processed in number := 0,
722 optimizer_cost in number := 0,
723 executions in number := 1,
724 priority in number := 2,
725 last_execution_date in date := SYSDATE,
726 stat_period in number := 0,
727 username in varchar2,
728 sql_text in clob);
729
730 -- PROCEDURE DBMS_ADVISOR.ADD_SQLWKLD_REF
731 -- PURPOSE: Adds a workload reference to an advisor task. A workload object
735 -- PARAMETERS:
732 -- can either be an Access Advisor workload object or a SQL tuning set
733 --
734 -- ADVISOR SUPPORT: SQL Access Advisor
736 -- TASK_NAME
737 -- Valid task name
738 -- WORKLOAD_NAME
739 -- Valid SQL Workload name
740 -- IS_STS
741 -- 1 = specified workload object is a SQL tuning set
742 -- 0 - specified workload object is an AA workload object
743
744 procedure add_sqlwkld_ref (task_name in varchar2,
745 workload_name in varchar2,
746 is_sts in number := 0);
747
748 -- PROCEDURE DBMS_ADVISOR.ADD_STS_REF
749 -- PURPOSE: Adds an STS reference to an advisor task. An STS object must
750 -- have an owner. The owner can be NULL, in which case the owner
751 -- is assumed to be the SESSION_USER. Note that the following
752 -- two calls are equivalent:
753 -- add_sqlwkld_ref(task_name, workload_name, 1);
754 -- add_sts_ref(task_name, NULL, workload_name);
755 --
756 -- ADVISOR SUPPORT: SQL Access Advisor
757 -- PARAMETERS:
758 -- TASK_NAME
759 -- Valid task name
760 -- STS_OWNER
761 -- Owner of STS. May be NULL, defaults to SESSION_USER
762 -- STS_NAME
763 -- Valid STS name
764
765 procedure add_sts_ref (task_name in varchar2,
766 sts_owner in varchar2,
767 workload_name in varchar2);
768
769 -- PROCEDURE DBMS_ADVISOR.CREATE_SQLWKLD
770 -- PURPOSE: Creates a new workload object
771 -- PARAMETERS:
772 -- WORKLOAD_NAME
773 -- Name of the workload. If not specified, a unique name will
774 -- generated by the system.
775 -- DESCRIPTION
776 -- Description of the workload
777 -- TEMPLATE
778 -- Optional name of an existing workload from which default settings
779 -- will be copied.
780 -- IS_TEMPLATE
781 -- Optional boolean to set the new task as template
782
783 procedure create_sqlwkld (workload_name in out varchar2,
784 description in varchar2 := null,
785 template in varchar2 := null,
786 is_template in varchar2 := 'FALSE');
787
788 -- PROCEDURE DBMS_ADVISOR.DELETE_SQLWKLD
789 -- PURPOSE: Deletes an entire workload object
790 -- PARAMETERS:
791 -- WORKLOAD_NAME
792 -- Name of the workload.
793
794 procedure delete_sqlwkld (workload_name in varchar2);
795
796 -- PROCEDURE DBMS_ADVISOR.DELETE_SQLWKLD_REF
797 -- PURPOSE: Removes a workload reference from the specified task.
798 -- ADVISOR SUPPORT: SQL Access Advisor
799 -- PARAMETERS:
800 -- TASK_NAME
801 -- Name of the task
802 -- WORKLOAD_NAME
803 -- Name of the workload to derefernce
804 -- IS_STS
805 -- 0 - SQL Workload object, 1 - SQL Tuning Set, 2 - Match any
806
807 procedure delete_sqlwkld_ref (task_name in varchar2,
808 workload_name in varchar2,
809 is_sts in number := 2);
810
811 -- PROCEDURE DBMS_ADVISOR.DELETE_STS_REF
812 -- PURPOSE: Removes an STS reference from an advisor task. An STS object
813 -- must have an owner. The owner can be NULL, in which case the
814 -- owner is assumed to be the SESSION_USER. Note that the following
815 -- two calls are equivalent:
816 -- delete_sqlwkld_ref(task_name, workload_name, 1);
817 -- delete_sts_ref(task_name, NULL, workload_name);
818 -- PURPOSE: Removes a workload reference from the specified task.
819 -- ADVISOR SUPPORT: SQL Access Advisor
820 -- PARAMETERS:
821 -- TASK_NAME
822 -- Name of the task
823 -- STS_OWNER
824 -- Owner of STS. May be NULL, defaults to SESSION_USER
825 -- WORKLOAD_NAME
826 -- Name of the workload to derefernce
827
828 procedure delete_sts_ref (task_name in varchar2,
829 sts_owner in varchar2,
830 workload_name in varchar2);
831
832 -- PROCEDURE DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT
833 -- PURPOSE: Deletes one or more statements from a workload
834 -- PARAMETERS:
835 -- WORKLOAD_NAME
836 -- Name of the workload.
837 -- SQL_ID
838 -- Unique identifier for a statement
839 -- SEARCH
840 -- Optional search condition used to refine the set of statements
841 -- to be deleted.
842 -- DELETED
843 -- Returns the number of statements removed by a searched delete.
844
845 procedure delete_sqlwkld_statement (workload_name in varchar2,
846 sql_id in number);
847
848 procedure delete_sqlwkld_statement (workload_name in varchar2,
849 search in varchar2,
850 deleted out number);
851
852 -- PROCEDURE DBMS_ADVISOR.IMPORT_SQLWKLD_STS
853 -- PURPOSE: Imports data into a workload from a SQL Tuning Set
857 -- STS_OWNER
854 -- PARAMETERS:
855 -- WORKLOAD_NAME
856 -- Name of the workload.
858 -- Optional SQL Tuning Set owner
859 -- STS_NAME
860 -- Name of existing SQL Tuning Set object
861 -- IMPORT_MODE
862 -- Specifies the replacement mode (APPEND,NEW,REPLACE)
863 -- PRIORITY
864 -- Specifies default priority for each statement (1,2,3)
865 -- SAVE_ROWS
866 -- Returns number of rows actually saved in the workload object
867 -- FAILED_ROWS
868 -- Returns number of statements that couldn't be saved due to
869 -- parsing and validation errors.
870
871 procedure import_sqlwkld_sts (workload_name in varchar2,
872 sts_owner in varchar2,
873 sts_name in varchar2,
874 import_mode in varchar2 := 'NEW',
875 priority in number := 2,
876 saved_rows out number,
877 failed_rows out number);
878
879 procedure import_sqlwkld_sts (workload_name in varchar2,
880 sts_name in varchar2,
881 import_mode in varchar2 := 'NEW',
882 priority in number := 2,
883 saved_rows out number,
884 failed_rows out number);
885
886 -- PROCEDURE DBMS_ADVISOR.IMPORT_SQLWKLD_SCHEMA
887 -- PURPOSE: Imports data into a workload from schema evidence
888 -- PARAMETERS:
889 -- WORKLOAD_NAME
890 -- Name of the workload.
891 -- IMPORT_MODE
892 -- Specifies the replacement mode (APPEND,NEW,REPLACE)
893 -- PRIORITY
894 -- Specifies default priority for each statement (1,2,3)
895 -- SAVE_ROWS
896 -- Returns number of rows actually saved in the workload object
897 -- FAILED_ROWS
898 -- Returns number of statements that couldn't be saved due to
899 -- parsing and validation errors.
900
901 procedure import_sqlwkld_schema (workload_name in varchar2,
902 import_mode in varchar2 := 'NEW',
903 priority in number := 2,
904 saved_rows out number,
905 failed_rows out number);
906
907
908 -- PROCEDURE DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE
909 -- PURPOSE: Imports data into a workload from the current SQL cache
910 -- PARAMETERS:
911 -- WORKLOAD_NAME
912 -- Name of the workload.
913 -- IMPORT_MODE
914 -- Specifies the replacement mode (APPEND,NEW,REPLACE)
915 -- PRIORITY
916 -- Specifies default priority for each statement (1,2,3)
917 -- SAVE_ROWS
918 -- Returns number of rows actually saved in the workload object
919 -- FAILED_ROWS
920 -- Returns number of statements that couldn't be saved due to
921 -- parsing and validation errors.
922
923 procedure import_sqlwkld_sqlcache (workload_name in varchar2,
924 import_mode in varchar2 := 'NEW',
925 priority in number := 2,
926 saved_rows out number,
927 failed_rows out number);
928
929
930 -- PROCEDURE DBMS_ADVISOR.IMPORT_SQLWKLD_SUMADV
931 -- PURPOSE: Imports data into a workload from a 9i Summary Advisor workload
932 -- PARAMETERS:
933 -- WORKLOAD_NAME
934 -- Name of the workload.
935 -- IMPORT_MODE
936 -- Specifies the replacement mode (APPEND,NEW,REPLACE)
937 -- PRIORITY
938 -- Specifies default priority for each statement (1,2,3)
939 -- SUMADV_ID
940 -- Summary Advisor workload identifier number
941 -- SAVE_ROWS
942 -- Returns number of rows actually saved in the workload object
943 -- FAILED_ROWS
944 -- Returns number of statements that couldn't be saved due to
945 -- parsing and validation errors.
946
947 procedure import_sqlwkld_sumadv (workload_name in varchar2,
948 import_mode in varchar2 := 'NEW',
949 priority in number := 2,
950 sumadv_id in number,
951 saved_rows out number,
952 failed_rows out number);
953
954 -- PROCEDURE DBMS_ADVISOR.IMPORT_SQLWKLD_USER
955 -- PURPOSE: Imports data into a workload from a specified user table
956 -- PARAMETERS:
957 -- WORKLOAD_NAME
958 -- Name of the workload.
959 -- IMPORT_MODE
960 -- Specifies the replacement mode (APPEND,NEW,REPLACE)
961 -- OWNER_NAME
962 -- Specifies the owner name of the user table.
963 -- TABLE_NAME
964 -- Specifies the name of the user table
965 -- SAVE_ROWS
969 -- parsing and validation errors.
966 -- Returns number of rows actually saved in the workload object
967 -- FAILED_ROWS
968 -- Returns number of statements that couldn't be saved due to
970
971 procedure import_sqlwkld_user (workload_name in varchar2,
972 import_mode in varchar2 := 'NEW',
973 owner_name in varchar2,
974 table_name in varchar2,
975 saved_rows out number,
976 failed_rows out number);
977
978 -- PROCEDURE DBMS_ADVISOR.COPY_SQLWKLD_TO_STS
979 -- PURPOSE: Copies workload object data into a user-specified STS. No filters
980 -- are supported.
981 -- PARAMETERS:
982 -- WORKLOAD_NAME
983 -- Name of the workload.
984 -- STS_NAME
985 -- Name of the target STS.
986 -- IMPORT_MODE
987 -- Specifies the replacement mode (APPEND,NEW,REPLACE)
988
989 procedure copy_sqlwkld_to_sts (workload_name in varchar2,
990 sts_name in varchar2,
991 import_mode in varchar2 := 'NEW');
992
993 -- PROCEDURE DBMS_ADVISOR.RESET_SQLWKLD
994 -- PURPOSE: Resets a workload to its initial state. All journal and
995 -- log messages are cleared. Workload statements will be
996 -- validated.
997 -- PARAMETERS:
998 -- WORKLOAD_NAME
999 -- Name of the workload
1000
1001 procedure reset_sqlwkld (workload_name in varchar2);
1002
1003 -- PROCEDURE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER
1004 -- PURPOSE: Sets the value of a workload parameter
1005 -- PARAMETERS:
1006 -- WORKLOAD_NAME
1007 -- Name of the workload.
1008 -- PARAMETER
1009 -- Workload parameter name
1010 -- VALUE
1011 -- Workload parameter value
1012
1013 procedure set_sqlwkld_parameter (workload_name in varchar2,
1014 parameter in varchar2,
1015 value in varchar2);
1016
1017 procedure set_sqlwkld_parameter (workload_name in varchar2,
1018 parameter in varchar2,
1019 value in number);
1020
1021 -- PROCEDURE DBMS_ADVISOR.SET_DEFAULT_SQLWKLD_PARAMETER
1022 -- PURPOSE: Sets the specified parameter value as default for
1023 -- all new Sql workload objects.
1024 -- PARAMETERS:
1025 -- PARAMETER
1026 -- Name of the task parameter
1027 -- VALUE
1028 -- Value to be set
1029
1030 procedure set_default_sqlwkld_parameter (parameter in varchar2,
1031 value in varchar2);
1032
1033 procedure set_default_sqlwkld_parameter (parameter in varchar2,
1034 value in number);
1035
1036 -- PROCEDURE DBMS_ADVISOR.UPDATE_WORKOAD_ATTRIBUTES
1037 -- PURPOSE: Updates a workload object
1038 -- PARAMETERS:
1039 -- WORKLOAD_NAME
1040 -- Name of the workload.
1041 -- NEW_NAME
1042 -- New workload name
1043 -- DESCRIPTION
1044 -- New workload description
1045 -- READ_ONLY
1046 -- TRUE if workload is to be used as a template
1047 -- IS_TEMPLATE
1048 -- TRUE if workload is to be used as a template
1049 -- HOW_CREATED
1050 -- Sets the source attribute for a workload
1051
1052 procedure update_sqlwkld_attributes (workload_name in varchar2,
1053 new_name in varchar2 := null,
1054 description in varchar2 := null,
1055 read_only in varchar2 := null,
1056 is_template in varchar2 := null,
1057 how_created in varchar2 := null);
1058
1059 -- PROCEDURE DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT
1060 -- PURPOSE: Updates one or more SQL statements in a workload
1061 -- PARAMETERS:
1062 -- WORKLOAD_NAME
1063 -- Name of the workload.
1064 -- SQL_ID
1065 -- Workload statement identifier
1066 -- UPDATED
1067 -- Returns the number of statements changed by a searched update.
1068 -- APPLICATION
1069 -- Optional application name
1070 -- ACTION
1071 -- Optional application action
1072 -- PRIORITY
1073 -- Optional priority value
1074 -- USERNAME
1075 -- Optional username value
1076 -- SEARCH
1077 -- Optional search condition to refine the set of updated
1078 -- statements.
1079
1080 procedure update_sqlwkld_statement (workload_name in varchar2,
1081 sql_id in number,
1082 application in varchar2 := null,
1083 action in varchar2 := null,
1084 priority in number := null,
1088 search in varchar2,
1085 username in varchar2 := null);
1086
1087 procedure update_sqlwkld_statement (workload_name in varchar2,
1089 updated out number,
1090 application in varchar2 := null,
1091 action in varchar2 := null,
1092 priority in number := null,
1093 username in varchar2 := null);
1094
1095 -- PROCEDURE DBMS_ADVISOR.SETUP_USER_ENVIRONMENT
1096 -- PURPOSE: Setups up user environment for Enterprise Manager.
1097 -- Typically, it is not necessary to call this routine as
1098 -- user setup is automatically done when a user creates
1099 -- a task. However, EM needs the environment set up prior
1100 -- to creating a task.
1101 -- PARAMETERS:
1102 -- ADVISOR_NAME
1103 -- - Name of advisor environment to setup
1104
1105 procedure setup_user_environment (advisor_name in varchar2);
1106
1107 -- PROCEDURE DBMS_ADVISOR.GET_ACCESS_ADVISOR_DEFAULTS
1108 -- PURPOSE: Returns default task and workload id numbers for
1109 -- the Access Advisor. This routine is typically only
1110 -- called by the Enterprise Manager SQL Access Advisor Wizard.
1111 -- PARAMETERS:
1112 -- TASK_NAME
1113 -- - returned task or template name
1114 -- TASK_ID_NUM
1115 -- - returned task or template id
1116 -- WORKLOAD_NAME
1117 -- - returned workload or template name
1118 -- WORK_ID_NUM
1119 -- - returned workload or template id
1120
1121 procedure get_access_advisor_defaults (task_name out varchar2,
1122 task_id_num out number,
1123 workload_name out varchar2,
1124 work_id_num out number);
1125
1126 -- PROCEDURE DBMS_ADVISOR.DELETE_DIRECTIVE
1127 -- PURPOSE: Deletes an instance of a directive. For task-based
1128 -- instances, the task may be required to be in an initial
1129 -- state to permit the delete operation.
1130 -- PARAMETERS:
1131 -- DIRECTIVE_ID
1132 -- Valid directive definition identifier number
1133 -- INSTANCE_NAME
1134 -- Valid instance name
1135 -- TASK_NAME
1136 -- Task to which the instance is associated. If null,
1137 -- default instance will be deleted.
1138
1139 procedure delete_directive (directive_id in number,
1140 instance_name in varchar2,
1141 task_name in varchar2 := NULL);
1142
1143 -- FUNCTION DBMS_ADVISOR.EVALUATE_DIRECTIVE
1144 -- PURPOSE: Evaluates a directive instance and returns the results
1145 -- PARAMETERS:
1146 -- DIRECTIVE_ID
1147 -- Valid base directive identifier number
1148 -- INSTANCE_NAME
1149 -- Valid instance name
1150 -- TASK_NAME
1151 -- Task to which the instance is associated. If null,
1152 -- a global instance will be retrieved.
1153 -- P1
1154 -- key (Optional).
1155 -- Type: filter - document to filter
1156 -- single-valued - Parameter name
1157 -- multi-valued - Parameter name
1158 -- conditional - Conditional name
1159 -- P2
1160 -- key (Optional).
1161 -- Type: filter - Unused
1162 -- single-valued - Unused
1163 -- multi-valued - Offset (1 based. A zero will
1164 -- return the number of values)
1165 -- conditional - Key name
1166 --
1167 -- RETURNS:
1168 -- Value of directive.
1169
1170 function evaluate_directive (directive_id in number,
1171 instance_name in varchar2,
1172 task_name in varchar2 := NULL,
1173 p1 in clob := NULL,
1174 p2 in clob := NULL)
1175 return clob;
1176
1177 -- PROCEDURE DBMS_ADVISOR.INSERT_DIRECTIVE
1178 -- PURPOSE: Creates an instance of a known directive.
1179 --
1180 -- For task-based instances, the task may be required to be
1181 -- in an initial state to permit this operation.
1182 --
1183 -- PARAMETERS:
1184 -- DIRECTIVE_ID
1185 -- Valid base directive identifier number
1186 -- INSTANCE_NAME
1187 -- Valid instance name. Must be unique to the directive.
1188 -- TASK_NAME
1189 -- Task to which the instance is associated. If null,
1190 -- default instance will be created.
1191 -- DOCUMENT
1192 -- The XML-document representing the directive instance.
1193
1194 procedure insert_directive (directive_id in number,
1195 instance_name in varchar2,
1196 task_name in varchar2,
1197 document in clob);
1198
1199 -- PROCEDURE DBMS_ADVISOR.UPDATE_DIRECTIVE
1200 -- PURPOSE: Updates an instance of a known directive.
1201 --
1202 -- For task-based instances, the task may be required to be
1203 -- in an initial state to permit an update operation.
1204 --
1205 -- PARAMETERS:
1206 -- DIRECTIVE_ID
1207 -- Valid base directive identifier number
1208 -- INSTANCE_NAME
1209 -- Valid instance name
1210 -- TASK_NAME
1211 -- Task to which the instance is associated. If null,
1212 -- default instance will be updated.
1213 -- DOCUMENT
1214 -- The XML-document representing the directive instance.
1215
1216 procedure update_directive (directive_id in number,
1217 instance_name in varchar2,
1218 task_name in varchar2,
1219 document in clob);
1220
1221
1222 END dbms_advisor;
[Home] [Help]