1 PACKAGE dbms_spm AUTHID CURRENT_USER AS
2
3 -- -------------------------------------------------------------------------
4 -- DBMS_SPM CONSTANTS SECTION
5 -- -------------------------------------------------------------------------
6
7 --
8 -- Special values for time_limit parameter of evolve_sql_plan_baseline().
9 --
10 NO_LIMIT CONSTANT INTEGER := 2147483647;
11 AUTO_LIMIT CONSTANT INTEGER := 2147483647 - 1;
12
13 --
14 -- List of names as an input parameter to evolve_sql_plan_baseline().
15 --
16 TYPE name_list IS TABLE OF VARCHAR2(30);
17
18
19 -- -------------------------------------------------------------------------
20 -- DBMS_SPM PUBLIC FUNCTIONS/PROCEDURES
21 -- -------------------------------------------------------------------------
22
23 -- -------------------------------------------------------------------------
24 -- NAME:
25 -- configure
26 --
27 -- DESCRIPTION:
28 -- Set configuration options for the SQL Management Base (SMB) as well as
29 -- the maintenance of SQL plan baselines.
30 --
31 -- PARAMETERS:
32 -- parameter_name - One of the following possible values:
33 -- 'SPACE_BUDGET_PERCENT',
34 -- 'PLAN_RETENTION_WEEKS'.
35 -- parameter_value - One of the following possible values:
36 -- NULL, or a value within the range 1..50 when
37 -- parameter_name = 'SPACE_BUDGET_PERCENT'.
38 -- NULL, or value within the range 5..523 when
39 -- parameter_name = 'PLAN_RETENTION_WEEKS'.
40 --
41 -- NOTES:
42 -- When parameter_value is NULL, the system default value is used.
43 -- The default space budget for SMB is 10%, which means no more than 10%
44 -- of SYSAUX tablespace is supposed to be used to store sql management
45 -- objects (i.e. plan baselines, sql profiles, etc.). When the space usage
46 -- exceeds the allowable percentage then alerts are generated.
47 -- The default unused plan retention period is one year and one week,
48 -- which means a plan will be automatically purged once it has been left
49 -- unused for little over a year.
50 --
51 -- REQUIRE:
52 -- "Administer SQL Management Object" privilege
53 -- -------------------------------------------------------------------------
54
55 PROCEDURE configure( parameter_name IN VARCHAR2,
56 parameter_value IN NUMBER := NULL
57 );
58
59 -- -------------------------------------------------------------------------
60 -- NAME:
61 -- load_plans_from_sqlset
62 --
63 -- DESCRIPTION:
64 -- Load plans from SQL tuning set as SQL plan baselines.
65 -- This function can be used to seed the SQL Management Base (SMB) with
66 -- SQL plan baselines created for a set of SQL statements using the plans
67 -- that are loaded from a SQL tuning set (STS).
68 -- To load plans from a remote system, the user has to first create an STS
69 -- with plan information on remote system, export/import the STS from
70 -- remote to local system, and then use this function.
71 -- To load plans from Automatic Workload Repository (AWR), the user has to
72 -- first create an STS using plan information stored in AWR snapshots, and
73 -- then use this function.
74 -- Upgrade to 11g is an interesting use case of this function. The user
75 -- can capture pre-upgrade plans for a set of SQL statements into an STS,
76 -- and then use this function to load the plans into SQL plan baselines.
77 --
78 -- PARAMETERS:
79 -- sqlset_name - Name of the STS from where to load the plans from.
80 -- sqlset_owner - Owner of STS. NULL means current schema is the owner.
81 -- basic_filter - A textual representation of a filter expression to be
82 -- applied to select only the qualifying plans from STS.
83 -- NULL means all plans in STS are selected.
84 -- fixed - Default 'NO' means the loaded plans will not change
85 -- the current 'fixed' property of the SQL plan baseline
86 -- into which they are loaded.
87 -- enabled - Default 'YES' means the loaded plans will be
88 -- considered by the optimizer.
89 -- commit_rows - Number of SQL plans to load before doing a periodic
90 -- commit. This helps to shorten the undo log.
91 --
92 -- RETURN:
93 -- Number of plans loaded.
94 --
95 -- REQUIRE:
96 -- "Administer SQL Management Object" privilege
97 -- -------------------------------------------------------------------------
98
99 FUNCTION load_plans_from_sqlset( sqlset_name IN VARCHAR2,
100 sqlset_owner IN VARCHAR2 := NULL,
101 basic_filter IN VARCHAR2 := NULL,
102 fixed IN VARCHAR2 := 'NO',
103 enabled IN VARCHAR2 := 'YES',
104 commit_rows IN NUMBER := 1000
105 )
106 RETURN PLS_INTEGER;
107
108 -- -------------------------------------------------------------------------
109 -- NAME:
110 -- load_plans_from_cursor_cache (multiple statements form)
111 --
112 -- DESCRIPTION:
113 -- Load plans from cursor cache as SQL plan baselines.
114 -- This function can be used to load one or more plans present in the
115 -- cursor cache for one or more SQL statements based on a plan selection
116 -- criterion represented by argument pair attribute_name/attribute_value.
117 -- The plans are selected from cursor cache based on the values specified
118 -- for attribute_name and attribute_value.
119 --
120 -- PARAMETERS:
121 -- attribute_name - One of the following possible attribute names:
122 -- 'SQL_TEXT',
123 -- 'PARSING_SCHEMA_NAME',
124 -- 'MODULE',
125 -- 'ACTION'
126 -- attribute_value - The attribute value is used as a search pattern of
127 -- LIKE predicate if attribute name is 'SQL_TEXT'.
128 -- Otherwise, it is used as an equality search value.
129 -- (e.g. specifying attribute_name=>'SQL_TEXT', and
130 -- attribute_value=>'% HR-123 %' means applying
131 -- SQL_TEXT LIKE '% HR-123 %' as a selection filter.
132 -- Similarly, specifying attribute_name=>'MODULE',
133 -- and attribute_value=>'HR' means applying
134 -- MODULE = 'HR' as a plan selection filter).
135 -- The attribute value is upper-cased except when it
136 -- is enclosed in double quotes or attribute name is
137 -- 'SQL_TEXT'.
138 -- fixed - Default 'NO' means the loaded plans will not change
139 -- the current 'fixed' property of SQL plan baseline
140 -- into which they are loaded.
141 -- enabled - Default 'YES' means the loaded plans will be
142 -- considered by the optimizer.
143 --
144 -- RETURN:
145 -- Number of plans loaded.
146 --
147 -- REQUIRE:
148 -- "Administer SQL Management Object" privilege
149 -- --------------------------------------------------------------------------
150
151 FUNCTION load_plans_from_cursor_cache( attribute_name IN VARCHAR2,
152 attribute_value IN VARCHAR2,
153 fixed IN VARCHAR2 := 'NO',
154 enabled IN VARCHAR2 := 'YES'
155 )
156 RETURN PLS_INTEGER;
157
158 -- -------------------------------------------------------------------------
159 -- NAME:
160 -- load_plans_from_cursor_cache (single statement form)
161 --
162 -- DESCRIPTION:
163 -- Load plans from cursor cache as SQL plan baselines.
164 -- This function can be used to load one or more plans present in the
165 -- cursor cache for a single SQL statement.
166 --
167 -- PARAMETERS:
168 -- sql_id - SQL statement identifier, which is used to identify
169 -- the plans in the cursor cache, and the SQL signature
170 -- associated to it is used to identify the SQL plan
171 -- baseline into which the plans are loaded. If the
172 -- SQL plan baseline doesn't exist it is created.
173 -- plan_hash_value - Plan identifier. Default NULL means load all plans
174 -- present in the cursor cache for given SQL statement.
175 -- fixed - Default 'NO' means the loaded plans will not change
176 -- the current 'fixed' property of SQL plan baseline
177 -- into which they are loaded.
178 -- enabled - Default 'YES' means the loaded plans will be
179 -- considered by the optimizer.
180 --
181 -- RETURN:
182 -- Number of plans loaded.
183 --
184 -- REQUIRE:
185 -- "Administer SQL Management Object" privilege
186 -- --------------------------------------------------------------------------
187
188 FUNCTION load_plans_from_cursor_cache( sql_id IN VARCHAR2,
189 plan_hash_value IN NUMBER := NULL,
190 fixed IN VARCHAR2 := 'NO',
191 enabled IN VARCHAR2 := 'YES'
192 )
193 RETURN PLS_INTEGER;
194
195 -- -------------------------------------------------------------------------
196 -- NAME:
197 -- load_plans_from_cursor_cache (single statement, sql text form)
198 --
199 -- DESCRIPTION:
200 -- Load plans from cursor cache as SQL plan baselines.
201 -- This function can be used to load one or more plans present in the
202 -- cursor cache for a single SQL statement.
203 --
204 -- PARAMETERS:
205 -- sql_id - SQL statement identifier, which is used to identify
206 -- the plans in the cursor cache.
207 -- plan_hash_value - Plan identifier. Default NULL means load all plans
208 -- present in the cursor cache for given SQL statement.
209 -- sql_text - SQL text to use in identifying the SQL plan baseline
210 -- into which the plans are loaded. If the SQL plan
211 -- baseline does not exist, it is created. The use of
212 -- SQL text is crucial when the user tunes a statement
213 -- possibly by adding hints to it and then wants to load
214 -- the resulting plan(s) into SQL plan baseline of the
215 -- original SQL statement.
216 -- fixed - Default 'NO' means the loaded plans will not change
217 -- the current 'fixed' property of SQL plan baseline
218 -- into which they are loaded.
219 -- enabled - Default 'YES' means the loaded plans will be
220 -- considered by the optimizer.
221 --
222 -- RETURN:
223 -- Number of plans loaded.
224 --
225 -- REQUIRE:
226 -- "Administer SQL Management Object" privilege
227 -- --------------------------------------------------------------------------
228
229 FUNCTION load_plans_from_cursor_cache( sql_id IN VARCHAR2,
230 plan_hash_value IN NUMBER := NULL,
231 sql_text IN CLOB,
232 fixed IN VARCHAR2 := 'NO',
233 enabled IN VARCHAR2 := 'YES'
234 )
235 RETURN PLS_INTEGER;
236
237 -- -------------------------------------------------------------------------
238 -- NAME:
239 -- load_plans_from_cursor_cache (single statement, sql handle form)
240 --
241 -- DESCRIPTION:
242 -- Load plans from cursor cache as SQL plan baselines.
243 -- This function can be used to load one or more plans present in the
244 -- cursor cache for a single SQL statement.
245 --
246 -- PARAMETERS:
247 -- sql_id - SQL statement identifier, which is used to identify
248 -- the plans in the cursor cache.
249 -- plan_hash_value - Plan identifier. Default NULL means load all plans
250 -- present in the cursor cache for given SQL statement.
251 -- sql_handle - SQL handle to use in identifying the plan baseline
252 -- into which the plans are loaded. The sql handle must
253 -- denote an existing SQL plan baseline. The use of sql
254 -- handle is crucial when the user tunes a SQL statement
255 -- possibly by adding hints to it and then wants to load
256 -- the resulting plan(s) into the SQL plan baseline of
257 -- original SQL statement.
258 -- fixed - Default 'NO' means the loaded plans will not change
259 -- the current 'fixed' property of SQL plan baseline
260 -- into which they are loaded.
261 -- enabled - Default 'YES' means the loaded plans will be
262 -- considered by the optimizer.
263 --
264 -- RETURN:
265 -- Number of plans loaded.
266 --
267 -- REQUIRE:
268 -- "Administer SQL Management Object" privilege
269 -- --------------------------------------------------------------------------
270
271 FUNCTION load_plans_from_cursor_cache( sql_id IN VARCHAR2,
272 plan_hash_value IN NUMBER := NULL,
273 sql_handle IN VARCHAR2,
274 fixed IN VARCHAR2 := 'NO',
275 enabled IN VARCHAR2 := 'YES'
276 )
277 RETURN PLS_INTEGER;
278
279 -- -------------------------------------------------------------------------
280 -- NAME:
284 -- This function can be used to change the status/name/description of a
281 -- alter_sql_plan_baseline
282 --
283 -- DESCRIPTION:
285 -- single plan, or status/description of all plans of a SQL statement
286 -- using the attribute name/value format. The function can be called
287 -- numerous times, each time altering a different attribute of same plan
288 -- or different plans of a sql statement.
289 --
290 -- PARAMETERS:
291 -- sql_handle - SQL statement handle. It identifies plans associated
292 -- with a SQL statement that are to be altered. If NULL
293 -- then plan name must be specified.
294 -- plan_name - Unique plan name. It identifies a specific plan.
295 -- Default NULL means alter all plans associated with
296 -- the SQL statement identified by sql_handle. If NULL
297 -- then sql handle must be specified.
298 -- attribute_name - One of the following possible attribute names:
299 -- 'ENABLED',
300 -- 'FIXED',
301 -- 'AUTOPURGE',
302 -- 'PLAN_NAME',
303 -- 'DESCRIPTION'
304 -- attribute_value - If the attribute name denotes a plan status then
305 -- the legal values are: 'YES', 'NO'.
306 -- If the attribute name denotes a plan name then the
307 -- supplied value should not conflict with already
308 -- stored plan name.
309 -- If the attribute name denotes plan description then
310 -- any character string is allowed.
311 --
312 -- RETURN:
313 -- Number of plans altered.
314 --
315 -- REQUIRE:
316 -- "Administer SQL Management Object" privilege
317 -- -------------------------------------------------------------------------
318
319 FUNCTION alter_sql_plan_baseline( sql_handle IN VARCHAR2 := NULL,
320 plan_name IN VARCHAR2 := NULL,
321 attribute_name IN VARCHAR2,
322 attribute_value IN VARCHAR2
323 )
324 RETURN PLS_INTEGER;
325
326 -- -------------------------------------------------------------------------
327 -- NAME:
328 -- drop_sql_plan_baseline
329 --
330 -- DESCRIPTION:
331 -- This function can be used to drop a single plan, or all plans of a
332 -- SQL statement.
333 --
334 -- PARAMETERS:
335 -- sql_handle - SQL statement handle. It identifies plans associated
336 -- with a SQL statement that are to be dropped. If NULL
337 -- then plan_name must be specified.
338 -- plan_name - Unique plan name. It identifies a specific plan.
339 -- Default NULL means drop all plans associated with
340 -- the SQL statement identified by sql_handle. If NULL
341 -- then sql handle must be specified.
342 --
343 -- RETURN:
344 -- Number of plans dropped.
345 --
346 -- REQUIRE:
347 -- "Administer SQL Management Object" privilege
348 -- -------------------------------------------------------------------------
349
350 FUNCTION drop_sql_plan_baseline( sql_handle IN VARCHAR2 := NULL,
351 plan_name IN VARCHAR2 := NULL
352 )
353 RETURN PLS_INTEGER;
354
355
356 -- -------------------------------------------------------------------------
357 -- NAME:
358 -- evolve_sql_plan_baseline
359 --
360 -- DESCRIPTION:
361 -- This function can be used to evolve SQL plan baselines associated with
362 -- one or more SQL statements. A SQL plan baseline is evolved when one or
363 -- more of its non-accepted plans are changed to accepted plans. If asked
364 -- by the user (i.e. parameter verify = 'YES'), the execution performance
365 -- of each non-accepted plan is compared against the performance of a plan
366 -- chosen from the associated SQL plan baseline. If the non-accepted plan
367 -- performance is found to be better than SQL plan baseline performance
368 -- then non-accepted plan is changed to an accepted plan, provided such
369 -- action is permitted by the user (i.e. parameter commit = 'YES').
370 --
371 -- PARAMETERS:
372 -- sql_handle - SQL statement identifier. Unless plan_name specified,
373 -- NULL means consider all statements with non-accepted
374 -- plans in their SQL plan baselines.
375 -- plan_name - Plan identifier. Default NULL means consider all non-
376 -- accepted plans in the SQL plan baseline of either the
377 -- identified SQL statement or all SQL statements if
378 -- sql_handle is NULL.
379 -- time_limit - Time limit in number of minutes. This applies only if
380 -- verify = 'YES' (see next parameter). The time limit
381 -- is global and it is used in the following manner. The
382 -- time limit for first non-accepted plan verification
383 -- is set equal to the input value. The time limit for
384 -- second non-accepted plan verification is set equal to
385 -- (input value - time spent in first plan verification)
389 -- The value DBMS_SPM.NO_LIMIT means no time limit.
386 -- and so on. The default DBMS_SPM.AUTO_LIMIT means let
387 -- the system choose an appropriate time limit based on
388 -- the number of plan verifications required to be done.
390 -- A positive integer value represents a user specified
391 -- time limit.
392 -- verify - Whether to actually execute the plans and compare the
393 -- performance before changing non-accepted plans into
394 -- accepted plans. A performance verification involves
395 -- executing a non-accepted plan and a plan chosen from
396 -- corresponding SQL plan baseline and comparing their
397 -- performance statistics. If non-accepted plan shows
398 -- performance improvement, it is changed to an accepted
399 -- plan. Default 'YES' means verify that a non-accepted
400 -- plan gives better performance before changing it to
401 -- an accepted plan. And 'NO' means do not execute plans
402 -- but simply change non-accepted plans into accepted
403 -- plans.
404 -- commit - Whether to update the ACCEPTED status of non-accepted
405 -- plans from 'NO' to 'YES'. Default 'YES' means perform
406 -- updates of qualifying non-accepted plans and generate
407 -- a report that shows the updates and the result of
408 -- performance verification when verify = 'YES'. And
409 -- 'NO' means generate a report without any updates.
410 -- Note that commit = 'NO' and verify = 'NO' represents
411 -- a no-op.
412 --
413 -- RETURN:
414 -- A CLOB containing a formatted text report showing non-accepted plans
415 -- in sequence, each with a possible change of its ACCEPTED status, and
416 -- if verify = 'YES' the result of their performance verification.
417 --
418 -- REQUIRE:
419 -- "Administer SQL Management Object" privilege
420 -- -------------------------------------------------------------------------
421
422 FUNCTION evolve_sql_plan_baseline(
423 sql_handle IN VARCHAR2 := NULL,
424 plan_name IN VARCHAR2 := NULL,
425 time_limit IN INTEGER := DBMS_SPM.AUTO_LIMIT,
426 verify IN VARCHAR2 := 'YES',
427 commit IN VARCHAR2 := 'YES'
428 )
429 RETURN CLOB;
430
431
432 -- -------------------------------------------------------------------------
433 -- NAME:
434 -- evolve_sql_plan_baseline (plan list format)
435 --
436 -- DESCRIPTION:
437 -- This function can be used to evolve SQL plan baselines associated with
438 -- one or more SQL statements. A SQL plan baseline is evolved when one or
439 -- more of its non-accepted plans are changed to accepted plans. If asked
440 -- by the user (i.e. parameter verify = 'YES'), the execution performance
441 -- of each non-accepted plan is compared against the performance of a plan
442 -- chosen from the associated SQL plan baseline. If the non-accepted plan
443 -- performance is found to be better than SQL plan baseline performance
444 -- then non-accepted plan is changed to an accepted plan, provided such
445 -- action is permitted by the user (i.e. parameter commit = 'YES').
446 --
447 -- PARAMETERS:
448 -- plan_list - A list of plan names. Each plan in the list can belong
449 -- to same or different SQL statement.
450 -- time_limit - Time limit in number of minutes. This applies only if
451 -- verify = 'YES' (see next parameter). The time limit
452 -- is global and it is used in the following manner. The
453 -- time limit for first non-accepted plan verification
454 -- is set equal to the input value. The time limit for
455 -- second non-accepted plan verification is set equal to
456 -- (input value - time spent in first plan verification)
457 -- and so on. The default DBMS_SPM.AUTO_LIMIT means let
458 -- the system choose an appropriate time limit based on
459 -- the number of plan verifications required to be done.
460 -- The value DBMS_SPM.NO_LIMIT means no time limit.
461 -- A positive integer value represents a user specified
462 -- time limit.
463 -- verify - Whether to actually execute the plans and compare the
464 -- performance before changing non-accepted plans into
465 -- accepted plans. A performance verification involves
466 -- executing a non-accepted plan and a plan chosen from
467 -- corresponding SQL plan baseline and comparing their
468 -- performance statistics. If non-accepted plan shows
469 -- performance improvement, it is changed to an accepted
470 -- plan. Default 'YES' means verify that a non-accepted
471 -- plan gives better performance before changing it to
472 -- an accepted plan. And 'NO' means do not execute plans
473 -- but simply change non-accepted plans into accepted
474 -- plans.
478 -- a report that shows the updates and the result of
475 -- commit - Whether to update the ACCEPTED status of non-accepted
476 -- plans from 'NO' to 'YES'. Default 'YES' means perform
477 -- updates of qualifying non-accepted plans and generate
479 -- performance verification when verify = 'YES'. And
480 -- 'NO' means generate a report without any updates.
481 -- Note that commit = 'NO' and verify = 'NO' represents
482 -- a no-op.
483 --
484 -- RETURN:
485 -- A CLOB containing a formatted text report showing non-accepted plans
486 -- in sequence, each with a possible change of its ACCEPTED status, and
487 -- if verify = 'YES' the result of their performance verification.
488 --
489 -- REQUIRE:
490 -- "Administer SQL Management Object" privilege
491 -- -------------------------------------------------------------------------
492
493 FUNCTION evolve_sql_plan_baseline(
494 plan_list IN DBMS_SPM.NAME_LIST,
495 time_limit IN INTEGER := DBMS_SPM.AUTO_LIMIT,
496 verify IN VARCHAR2 := 'YES',
497 commit IN VARCHAR2 := 'YES'
498 )
499 RETURN CLOB;
500
501 -- -------------------------------------------------------------------------
502 -- NAME:
503 -- create_stgtab_baseline
504 --
505 -- DESCRIPTION:
506 -- This procedure creates a staging table that will be used to pack
507 -- (import) SQL plan baselines into it.
508 --
509 -- PARAMETERS:
510 -- table_name - Name of staging table.
511 -- table_owner - Name of schema owner of staging table.
512 -- Default NULL means current schema is the owner.
513 -- tablespace_name - Name of tablespace.
514 -- Default NULL means create staging table in the
515 -- default tablespace.
516 --
517 -- REQUIRE:
518 -- 1. 'CREATE TABLE' and 'ADMINISTER SQL MANAGEMENT OBJECT' privilege
519 -- 2. tablespace quota
520 -- -------------------------------------------------------------------------
521
522 PROCEDURE create_stgtab_baseline( table_name IN VARCHAR2,
523 table_owner IN VARCHAR2 := NULL,
524 tablespace_name IN VARCHAR2 := NULL
525 );
526
527
528 -- -------------------------------------------------------------------------
529 -- NAME:
530 -- pack_stgtab_baseline
531 --
532 -- DESCRIPTION:
533 -- This function packs (exports) SQL plan baselines into a staging table.
534 --
535 -- PARAMETERS:
536 -- table_name - name of the staging table (case insensitive
537 -- unless double quoted)
538 -- table_owner - name of the schema owner of staging table
539 -- (case insensitive unless double quoted)
540 -- sql_handle - sql handle (case sensitive)
541 -- plan_name - plan name (case sensitive, % wildcards OK)
542 -- sql_text - sql text (case sensitive, % wildcards OK)
543 -- creator - creator of plan baseline (case insensitive
544 -- unless double quoted)
545 -- origin - origin of plan baseline, should be
546 -- 'MANUAL-LOAD', 'AUTO-CAPTURE',
547 -- 'MANUAL_SQLTUNE' or 'AUTO-SQLTUNE' (case
548 -- insensitive)
549 -- enabled - should be either 'YES' or 'NO' (case
550 -- insensitive)
551 -- accepted - should be either 'YES' or 'NO' (case
552 -- insensitive)
553 -- fixed - should be either 'YES' or 'NO' (case
554 -- insensitive)
555 -- module - module (case sensitive)
556 -- action - action (case sensitive)
557 --
558 -- RETURN:
559 -- Number of plan baselines packed
560 --
561 -- REQUIRE:
562 -- "Administer SQL Management Object" privilege
563 --
564 -- Note:
565 -- We support predefined filters rather than user-defined filters. We do
566 -- not allow users to inject an arbitrary filter into the query.
567 -- -------------------------------------------------------------------------
568
569 FUNCTION pack_stgtab_baseline ( table_name IN VARCHAR2,
570 table_owner IN VARCHAR2 := NULL,
571 sql_handle IN VARCHAR2 := NULL,
572 plan_name IN VARCHAR2 := '%',
573 sql_text IN CLOB := '%',
574 creator IN VARCHAR2 := NULL,
575 origin IN VARCHAR2 := NULL,
576 enabled IN VARCHAR2 := NULL,
577 accepted IN VARCHAR2 := NULL,
578 fixed IN VARCHAR2 := NULL,
579 module IN VARCHAR2 := NULL,
583
580 action IN VARCHAR2 := NULL
581 )
582 RETURN NUMBER;
584
585 -- -------------------------------------------------------------------------
586 -- NAME:
587 -- unpack_stgtab_baseline
588 --
589 -- DESCRIPTION:
590 -- This function unpacks (imports) plan baselines from a staging table.
591 --
592 -- Parameters:
593 -- table_name - name of the staging table (case insensitive
594 -- unless double quoted)
595 -- table_owner - name of the schema owner of staging table
596 -- (case insensitive unless double quoted)
597 -- sql_handle - sql handle (case sensitive)
598 -- plan_name - plan name (case sensitive, % wildcards OK)
599 -- sql_text - sql text (case sensitive, % wildcards OK)
600 -- creator - creator of plan baseline (case insensitive
601 -- unless double quoted)
602 -- origin - origin of plan baseline, should be
603 -- 'MANUAL-LOAD', 'AUTO-CAPTURE',
604 -- 'MANUAL-SQLTUNE' or 'AUTO-SQLTUNE' (case
605 -- insensitive)
606 -- enabled - should be either 'YES' or 'NO' (case
607 -- insensitive)
608 -- accepted - should be either 'YES' or 'NO' (case
609 -- insensitive)
610 -- fixed - should be either 'YES' or 'NO' (case
611 -- insensitive)
612 -- module - module (case sensitive)
613 -- action - action (case sensitive)
614 --
615 -- RETURN:
616 -- Number of plans unpacked
617 --
618 -- REQUIRE:
619 -- "Administer SQL Management Object" privilege
620 --
621 -- Note:
622 -- We support predefined filters rather than user-defined filters. We do
623 -- not allow users to inject an arbitrary filter into the query.
624 -- -------------------------------------------------------------------------
625
626 FUNCTION unpack_stgtab_baseline ( table_name IN VARCHAR2,
627 table_owner IN VARCHAR2 := NULL,
628 sql_handle IN VARCHAR2 := NULL,
629 plan_name IN VARCHAR2 := '%',
630 sql_text IN CLOB := '%',
631 creator IN VARCHAR2 := NULL,
632 origin IN VARCHAR2 := NULL,
633 enabled IN VARCHAR2 := NULL,
634 accepted IN VARCHAR2 := NULL,
635 fixed IN VARCHAR2 := NULL,
636 module IN VARCHAR2 := NULL,
637 action IN VARCHAR2 := NULL
638 )
639 RETURN NUMBER;
640
641
642 -- -------------------------------------------------------------------------
643 -- NAME:
644 -- migrate_stored_outline
645 --
646 -- DESCRIPTION:
647 -- This function can be used to migrate stored outlines for one or more
648 -- sql statements to sql plan baselines in SMB.
649 --
650 -- PARAMETERS:
651 -- attribute_name - One of the following possible attribute names:
652 -- 'OUTLINE_NAME',
653 -- 'SQL_TEXT',
654 -- 'CATEGORY',
655 -- 'ALL'
656 -- attribute_value - The attribute value is used as an equality search
657 -- value. The attribute value used as a search pattern
658 -- of LIKE predicate is NOT supported, mainly because
659 -- sql_text of a stored outline is internally stored as
660 -- LONG instead of CLOB.
661 --
662 -- (e.g., specifying attribute_name=>'CATEGORY',
663 -- and attribute_value=>'HR' means applying
664 -- CATEGORY = 'HR' as an outline selection filter. In
665 -- this case all the outlines under the 'HR' category
666 -- will be migrated to SQL plan baselines).
667 --
668 -- Similarly, specifying attribute_name=>'SQL_TEXT',
669 -- and attribute_value=>'% HR-123 %' will result in
670 -- applying SQL_TEXT = '% HR-123 %' as an outline
671 -- selection filter. The LIKE predicate will not be
672 -- applied in this case.
673 --
674 -- attribute_value cannot be NULL if attribute_name is
675 -- set to 'OUTLINE_NAME', 'SQL_TEXT' or 'CATEGORY'.
676 --
677 -- attribute_value wrapped in single quotes will be
678 -- converted to upper case. e.g. specifying
679 -- attribute_name=>'outline_name' and
680 -- attribute_value=>'ms01' will result in applying
681 -- OUTLINE_NAME = 'MS01' as selection filter.
682 --
683 -- attribute_value wrapped in double quotes will retain
687 -- attribute_name=>'outline_name' and
684 -- its upper and lower cases. The double quotes will be
685 -- stripped off before applying the attribute_value as
686 -- selection filter. e.g. specifying
688 -- attribute_value=>'"ms01"' will result in applying
689 -- OUTLINE_NAME = 'ms01' as selection filter.
690 --
691 -- fixed - Whether the new SQL plan baselines created as the
692 -- results of migration should be fixed or not. A fixed
693 -- SQL plan baseline has higher priority to be chosen
694 -- over other non-fixed plans for the same SQL
695 -- statement. However, a SQL plan baseline containing a
696 -- fixed plan cannot be evolved. The default value is
697 -- 'NO'.
698 -- RETURN:
699 -- A CLOB containing a text summary report showing the number of successes
700 -- and number of failures during the stored outline migration. In case of
701 -- failures, the report will also show the causes of failure.
702 --
703 -- REQUIRE:
704 -- "Administer SQL Management Object" privilege
705 -- "ALTER ANY OUTLINE" privilege
706 -- -------------------------------------------------------------------------
707 FUNCTION migrate_stored_outline( attribute_name IN VARCHAR2,
708 attribute_value IN CLOB := NULL,
709 fixed IN VARCHAR2 := 'NO'
710 )
711 RETURN CLOB;
712
713 -- -------------------------------------------------------------------------
714 -- NAME:
715 -- migrate_stored_outline (outline list format)
716 --
717 -- DESCRIPTION:
718 -- This function can be used to migrate stored outlines to sql plan
719 -- baselines in SMB given one or more outline names.
720 --
721 -- PARAMETERS:
722 -- outln_list - a list of stored outline names
723 --
724 -- fixed - Whether the new SQL plan baselines created as the
725 -- results of migration should be fixed or not. A fixed
726 -- SQL plan baseline has higher priority to be chosen
727 -- over other non-fixed plans for the same SQL
728 -- statement. However, a SQL plan baseline containing a
729 -- fixed plan cannot be evolved. The default value is 'NO'.
730 -- RETURN:
731 -- A CLOB containing a text summary report showing the number of successes
732 -- and number of failures during the stored outline migration. In case of
733 -- failures, the report will also show the causes of failure.
734 --
735 -- REQUIRE:
736 -- "Administer SQL Management Object" privilege
737 -- "ALTER ANY OUTLINE" privilege
738 -- -------------------------------------------------------------------------
739 FUNCTION migrate_stored_outline( outln_list IN DBMS_SPM.NAME_LIST,
740 fixed IN VARCHAR2 := 'NO'
741 )
742 RETURN CLOB;
743
744
745 -- -------------------------------------------------------------------------
746 -- NAME:
747 -- drop_migrated_stored_outline
748 --
749 -- DESCRIPTION:
750 -- This function can be used to drop all stored outlines that are already
751 -- migrated to SQL plan baselines.
752 --
753 -- PARAMETERS:
754 -- None
755 -- RETURN:
756 -- Number of outlines dropped.
757 --
758 -- REQUIRE:
759 -- "Administer SQL Management Object" privilege
760 -- "DROP ANY OUTLINE" privilege
761 -- "select on dba_outlines" privilege
762 -- -------------------------------------------------------------------------
763 FUNCTION drop_migrated_stored_outline
764 RETURN PLS_INTEGER;
765
766
767 ----------------- create_evolve_task - SQL handle format -------------------
768 -- NAME:
769 -- create_evolve_task - Create an evolve task in order to evolve one or
770 -- more plans for a given SQL statement (SQL handle format)
771 --
772 -- DESCRIPTION
773 -- This function is called to prepare the evolution of one or more plans
774 -- for a SQL statement given its handle. The function mainly creates an
775 -- advisor task and sets its parameters.
776 --
777 -- PARAMETERS:
778 -- sql_handle (IN) - Handle of a SQL statement. The default NULL means
779 -- consider all SQL statements with non-accepted
780 -- plans.
781 -- plan_name (IN) - Plan identifier. The default NULL means consider
782 -- all non-accepted plans of the specified SQL handle
783 -- or all SQL statements if the SQL handle is NULL.
784 -- time_limit (IN) - Time limit in number of minutes. The time limit
785 -- is global and it is used in the following manner.
786 -- The time limit for first non-accepted plan is equal
787 -- to the input value. The time limit for the second
788 -- non-accepted plan is equal to (input value - time
789 -- spent in first plan verification) and so on. The
790 -- default DBMS_SPM.AUTO_LIMIT means let the system
791 -- choose an appropriate time limit based on the
792 -- number of plan verifications required to be done.
793 -- The value DBMS_SPM.NO_LIMIT means no time limit.
794 -- task_name (IN) - Evolve task name
795 -- description (IN) - Description of the task (maximum 256 characters)
796 --
797 -- RETURNS:
798 -- SQL evolve task unique name
799 --
800 -- EXCEPTIONS:
801 -- To be done
802 -----------------------------------------------------------------------------
803 FUNCTION create_evolve_task( sql_handle IN VARCHAR2 := NULL,
804 plan_name IN VARCHAR2 := NULL,
805 time_limit IN NUMBER := DBMS_SPM.AUTO_LIMIT,
806 task_name IN VARCHAR2 := NULL,
807 description IN VARCHAR2 := NULL
808 )
809 RETURN VARCHAR2;
810
811
812 ------------------- create_evolve_task - plan list format -------------------
813 -- NAME:
814 -- create_evolve_task - Create an evolve task in order to evolve one or
815 -- more given plans (plan list format)
816 --
817 -- DESCRIPTION
818 -- This function is called to prepare the evolution of one or more given
819 -- plans. The function mainly creates an advisor task and sets its
820 -- parameters.
821 --
822 -- PARAMETERS:
823 -- plan_list (IN) - A list of plan names. The plans may belong to
824 -- different SQL statements.
825 -- time_limit (IN) - Time limit in number of minutes. The time limit
826 -- is global and it is used in the following manner.
827 -- The time limit for first non-accepted plan is equal
828 -- to the input value. The time limit for the second
829 -- non-accepted plan is equal to (input value - time
830 -- spent in first plan verification) and so on. The
831 -- default DBMS_SPM.AUTO_LIMIT means let the system
832 -- choose an appropriate time limit based on the
833 -- number of plan verifications required to be done.
834 -- The value DBMS_SPM.NO_LIMIT means no time limit.
835 -- task_name (IN) - Evolve task name
836 -- description (IN) - Description of the task (maximum 256 characters)
837 --
838 -- RETURNS:
839 -- SQL evolve task unique name
840 --
841 -- EXCEPTIONS:
842 -- To be done
843 -----------------------------------------------------------------------------
844 FUNCTION create_evolve_task( plan_list IN DBMS_SPM.NAME_LIST,
845 time_limit IN NUMBER := DBMS_SPM.AUTO_LIMIT,
846 task_name IN VARCHAR2 := NULL,
850
847 description IN VARCHAR2 := NULL
848 )
849 RETURN VARCHAR2;
851
852 -------------------------- set_evolve_task_parameter ------------------------
853 -- NAME:
854 -- set_evolve_task_parameter - Set a parameter of an evolve task
855 --
856 -- DESCRIPTION
857 -- This procedure is called to update the value of an evolve task
858 -- parameter of type NUMBER. The possible parameters are:
859 -- TIME_LIMIT : Global time limit (default DBMS_SPM.AUTO_LIMIT) in
860 -- minutes. This is the total time allowed for the
861 -- task.
862 --
863 -- PARAMETERS:
864 -- task_name (IN) - Identifier of task
865 -- parameter (IN) - Name of the parameter to set
866 -- value (IN) - New value of the parameter
867 --
868 -- RETURNS:
869 -- Nothing
870 --
871 -- EXCEPTIONS:
872 -- To be done
873 -----------------------------------------------------------------------------
874 PROCEDURE set_evolve_task_parameter( task_name IN VARCHAR2,
875 parameter IN VARCHAR2,
876 value IN NUMBER
877 );
878
879
880 -------------------------- set_evolve_task_parameter ------------------------
881 -- NAME:
882 -- set_evolve_task_parameter - Set a parameter of an evolve task
883 --
884 -- DESCRIPTION
885 -- This procedure is called to update the value of an evolve task
886 -- parameter of type VARCHAR2. The possible parameters are:
887 -- ACCEPT_PLANS : This parameter is only valid for the automatic
888 -- evolve task, SYS_AUTO_SPM_EVOLVE_TASK. When
889 -- set to TRUE, all plans recommended by the task
890 -- will be automatically accepted. When set to FALSE,
891 -- the task will verify the plans, but will not
892 -- implement any recommendations. The default value
893 -- is TRUE.
894 --
895 -- PARAMETERS:
896 -- task_name (IN) - Identifier of task
897 -- parameter (IN) - Name of the parameter to set
898 -- value (IN) - New value of the parameter
899 --
900 -- RETURNS:
901 -- Nothing
902 --
903 -- EXCEPTIONS:
904 -- To be done
905 -----------------------------------------------------------------------------
906 PROCEDURE set_evolve_task_parameter( task_name IN VARCHAR2,
907 parameter IN VARCHAR2,
908 value IN VARCHAR2
909 );
910
911
912 ----------------------------- execute_evolve_task ---------------------------
913 -- NAME:
914 -- execute_evolve_task - Execute an evolve task
915 --
916 -- DESCRIPTION
917 -- This function is called to execute a previously created evolve task.
918 --
919 -- PARAMETERS:
920 -- task_name (IN) - Identifier of task to execute
921 -- execution_name (IN) - A name to qualify and identify an execution.
922 -- If not specified, it is generated by the
923 -- advisor and returned by the function.
924 -- execution_desc (IN) - Description of the execution (maximum 256
925 -- characters)
926 --
927 -- RETURNS:
928 -- Name of the new execution
929 --
930 -- EXCEPTIONS:
931 -- To be done
932 -----------------------------------------------------------------------------
933 FUNCTION execute_evolve_task( task_name IN VARCHAR2,
934 execution_name IN VARCHAR2 := NULL,
935 execution_desc IN VARCHAR2 := NULL
936 )
937 RETURN VARCHAR2;
938
939
940 ---------------------------- interrupt_evolve_task --------------------------
941 -- NAME:
942 -- interrupt_evolve_task - Interrupt an evolve task
943 --
944 -- DESCRIPTION
945 -- This function is called to interrupt a currently executing evolve
946 -- task. The task will end its operations as it would at a normal exit
947 -- and the user will be able to access the intermediate results. The
948 -- task may also be later resumed.
949 --
950 -- PARAMETERS:
951 -- task_name (IN) - Identifier of task to interrupt
952 --
953 -- RETURNS:
954 -- Nothing
955 --
956 -- EXCEPTIONS:
957 -- If the task is not currently executing, you will get an ORA-13609
958 -- error.
959 -----------------------------------------------------------------------------
960 PROCEDURE interrupt_evolve_task( task_name IN VARCHAR2
961 );
962
963
964 ----------------------------- cancel_evolve_task ----------------------------
965 -- NAME:
966 -- cancel_evolve_task - Cancel an evolve task
967 --
968 -- DESCRIPTION
969 -- This function is called to cancel a currently executing evolve task.
970 -- All intermediate results will be removed from the task.
971 --
972 -- PARAMETERS:
973 -- task_name (IN) - Identifier of task to cancel
974 --
975 -- RETURNS:
976 -- Nothing
977 --
978 -- EXCEPTIONS:
979 -- If the task is not currently executing, you will get an ORA-13609
980 -- error.
984
981 -----------------------------------------------------------------------------
982 PROCEDURE cancel_evolve_task( task_name IN VARCHAR2
983 );
985
986 ----------------------------- reset_evolve_task -----------------------------
987 -- NAME:
988 -- reset_evolve_task - Reset an evolve task
989 --
990 -- DESCRIPTION
991 -- This function is called to reset an evolve task to its initial state.
992 -- All intermediate results will be removed from the task. Call this
993 -- procedure on a task that is not currently executing.
994 --
995 -- PARAMETERS:
996 -- task_name (IN) - Identifier of task to reset
997 --
998 -- RETURNS:
999 -- Nothing
1000 --
1001 -- EXCEPTIONS:
1002 -- To be done
1003 -----------------------------------------------------------------------------
1004 PROCEDURE reset_evolve_task( task_name IN VARCHAR2
1005 );
1006
1007
1008 ----------------------------- resume_evolve_task ----------------------------
1009 -- NAME:
1010 -- resume_evolve_task - Resume an evolve task
1011 --
1012 -- DESCRIPTION
1013 -- This function is called to resume a previously interrupted task.
1014 --
1015 -- PARAMETERS:
1016 -- task_name (IN) - Identifier of task to resume
1017 --
1018 -- RETURNS:
1019 -- Nothing
1020 --
1021 -- EXCEPTIONS:
1022 -- To be done
1023 -----------------------------------------------------------------------------
1024 PROCEDURE resume_evolve_task( task_name IN VARCHAR2
1025 );
1026
1027
1028 ------------------------------ drop_evolve_task -----------------------------
1029 -- NAME:
1030 -- drop_evolve_task - Drop an evolve task
1031 --
1032 -- DESCRIPTION
1033 -- This function is called to drop an evolve task.
1034 --
1035 -- PARAMETERS:
1036 -- task_name (IN) - Identifier of task to drop
1037 --
1038 -- RETURNS:
1039 -- Nothing
1040 --
1041 -- EXCEPTIONS:
1042 -- To be done
1043 -----------------------------------------------------------------------------
1044 PROCEDURE drop_evolve_task( task_name IN VARCHAR2
1045 );
1046
1047
1048 ----------------------------- report_evolve_task ----------------------------
1049 -- NAME:
1050 -- report_evolve_task - Report an evolve task
1051 --
1052 -- DESCRIPTION
1053 -- This function is called to display the results of an evolve task.
1054 --
1055 -- PARAMETERS:
1056 -- task_name (IN) - Identifier of task to report
1057 -- type (IN) - Type of the report. Possible values are TEXT,
1058 -- HTML, XML.
1059 -- level (IN) - Format of the report. Possible values are
1060 -- BASIC, TYPICAL, ALL.
1061 -- section (IN) - Particular section in the report.
1062 -- Possible values are:
1063 -- SUMMARY,
1064 -- FINDINGS,
1065 -- PLANS,
1066 -- INFORMATION,
1067 -- ERRORS,
1068 -- ALL.
1069 -- object_id (IN) - Identifier of the advisor framework object that
1070 -- represents a single plan. If NULL, the report
1071 -- will be generated for all objects.
1072 -- task_owner (IN) - Owner of the evolve task. Defaults to the
1073 -- current schema owner.
1074 -- execution_name (IN) - A name to qualify and identify an execution.
1075 -- If NULL, the report will be generated for the
1076 -- last task execution.
1077 --
1078 -- RETURNS:
1079 -- The report
1080 --
1081 -- EXCEPTIONS:
1082 -- To be done
1083 -----------------------------------------------------------------------------
1084 FUNCTION report_evolve_task( task_name IN VARCHAR2,
1085 type IN VARCHAR2 := 'TEXT',
1086 level IN VARCHAR2 := 'TYPICAL',
1087 section IN VARCHAR2 := 'ALL',
1088 object_id IN NUMBER := NULL,
1089 task_owner IN VARCHAR2 := NULL,
1090 execution_name IN VARCHAR2 := NULL)
1091 RETURN CLOB;
1092
1093
1094 -------------------------- accept_sql_plan_baseline -------------------------
1095 -- NAME:
1096 -- accept_sql_plan_baseline - Accept plan based on recommendation of
1097 -- evolve task
1098 --
1099 -- DESCRIPTION
1100 -- This function is called to accept a plan based on the recommendation
1101 -- of an evolve task.
1102 --
1103 -- PARAMETERS:
1104 -- task_name (IN) - Identifier of task to implement
1105 -- object_id (IN) - Identifier of the advisor framework object that
1106 -- represents a single plan
1107 -- task_owner (IN) - Owner of the evolve task. Defaults to the
1108 -- current schema owner.
1109 -- force (IN) - Accept the plan even if the advisor did not
1110 -- recommend such an action. The default is
1111 -- FALSE meaning only accept the plan if the
1112 -- plan was verified and showed sufficient
1113 -- improvement in benefit.
1114 --
1118 PROCEDURE accept_sql_plan_baseline( task_name IN VARCHAR2,
1115 -- EXCEPTIONS:
1116 -- To be done
1117 -----------------------------------------------------------------------------
1119 object_id IN NUMBER,
1120 task_owner IN VARCHAR2 := NULL,
1121 force IN BOOLEAN := FALSE
1122 );
1123
1124
1125 --------------------------- implement_evolve_task ---------------------------
1126 -- NAME:
1127 -- implement_evolve_task - Implement recommendations of evolve task
1128 --
1129 -- DESCRIPTION
1130 -- This function is called to implement the recommendations of an evolve
1131 -- task.
1132 --
1133 -- PARAMETERS:
1134 -- task_name (IN) - Identifier of task to implement
1135 -- task_owner (IN) - Owner of the evolve task. Defaults to the
1136 -- current schema owner.
1137 -- execution_name (IN) - A name to qualify and identify an execution.
1138 -- If NULL, the action will be taken for the
1139 -- last task execution.
1140 -- force (IN) - Accept all plans even if the advisor did not
1141 -- recommend such an action. The default is
1142 -- FALSE meaning only accept those plans that
1143 -- were verified and showed sufficient
1144 -- improvement in benefit.
1145 --
1146 -- RETURNS:
1147 -- The number of plans accepted
1148 --
1149 -- EXCEPTIONS:
1150 -- To be done
1151 -----------------------------------------------------------------------------
1152 FUNCTION implement_evolve_task( task_name IN VARCHAR2,
1153 task_owner IN VARCHAR2 := NULL,
1154 execution_name IN VARCHAR2 := NULL,
1155 force IN BOOLEAN := FALSE
1156 )
1157 RETURN NUMBER;
1158
1159
1160 --------------------------- report_auto_evolve_task -------------------------
1161 -- NAME:
1162 -- report_auto_evolve_task - Report automatic evolve task
1163 --
1164 -- DESCRIPTION
1165 -- This function is called to display the results of an execution of
1166 -- the automatic evolve task.
1167 --
1168 -- PARAMETERS:
1169 -- type (IN) - Type of the report. Possible values are TEXT,
1170 -- HTML, XML.
1171 -- level (IN) - Format of the report. Possible values are
1172 -- BASIC, TYPICAL, ALL.
1173 -- section (IN) - Particular section in the report.
1174 -- Possible values are:
1175 -- SUMMARY,
1176 -- FINDINGS,
1177 -- PLANS,
1178 -- INFORMATION,
1179 -- ERRORS,
1180 -- ALL.
1181 -- object_id (IN) - Identifier of the advisor framework object that
1182 -- represents a single plan. If NULL, the report
1183 -- will be generated for all objects.
1184 -- execution_name (IN) - A name to qualify and identify an execution.
1185 -- If NULL, the report will be generated for the
1186 -- last task execution.
1187 --
1188 -- RETURNS:
1189 -- The report
1190 --
1191 -- EXCEPTIONS:
1192 -- To be done
1193 -----------------------------------------------------------------------------
1194 FUNCTION report_auto_evolve_task(type IN VARCHAR2 := 'TEXT',
1195 level IN VARCHAR2 := 'TYPICAL',
1196 section IN VARCHAR2 := 'ALL',
1197 object_id IN NUMBER := NULL,
1198 execution_name IN VARCHAR2 := NULL)
1199 RETURN CLOB;
1200
1201
1202 END dbms_spm;
[Home] [Help]