1 PACKAGE dbms_sqltune AUTHID CURRENT_USER AS
2 -----------------------------------------------------------------------------
3 -- global constant declarations --
4 -----------------------------------------------------------------------------
5 --
6 -- sqltune advisor name
7 ADV_SQLTUNE_NAME CONSTANT VARCHAR2(18) := 'SQL Tuning Advisor';
8
9 --
10 -- SQLTune advisor task scope parameter values
11 --
12 SCOPE_LIMITED CONSTANT VARCHAR2(7) := 'LIMITED';
13 SCOPE_COMPREHENSIVE CONSTANT VARCHAR2(13) := 'COMPREHENSIVE';
14
15 --
16 -- SQLTune advisor time_limit constants
17 --
18 TIME_LIMIT_DEFAULT CONSTANT NUMBER := 1800;
19
20 --
21 -- report type (possible values) constants
22 --
23 TYPE_TEXT CONSTANT VARCHAR2(4) := 'TEXT' ;
24 TYPE_XML CONSTANT VARCHAR2(3) := 'XML' ;
25 TYPE_HTML CONSTANT VARCHAR2(4) := 'HTML' ;
26
27 --
28 -- report level (possible values) constants
29 --
30 LEVEL_TYPICAL CONSTANT VARCHAR2(7) := 'TYPICAL' ;
31 LEVEL_BASIC CONSTANT VARCHAR2(5) := 'BASIC' ;
32 LEVEL_ALL CONSTANT VARCHAR2(3) := 'ALL' ;
33
34 --
35 -- report section (possible values) constants
36 --
37 SECTION_FINDINGS CONSTANT VARCHAR2(8) := 'FINDINGS' ;
38 SECTION_PLANS CONSTANT VARCHAR2(5) := 'PLANS' ;
39 SECTION_INFORMATION CONSTANT VARCHAR2(11):= 'INFORMATION';
40 SECTION_ERRORS CONSTANT VARCHAR2(6) := 'ERRORS' ;
41 SECTION_ALL CONSTANT VARCHAR2(3) := 'ALL' ;
42 SECTION_SUMMARY CONSTANT VARCHAR2(7) := 'SUMMARY' ;
43
44 -- some common date format
45 DATE_FMT constant varchar2(21) := 'mm/dd/yyyy hh24:mi:ss';
46
47 --
48 -- script section constants
49 --
50 REC_TYPE_ALL CONSTANT VARCHAR2(3) := 'ALL';
51 REC_TYPE_SQL_PROFILE CONSTANT VARCHAR2(8) := 'PROFILE';
52 REC_TYPE_SQL_PROFILES CONSTANT VARCHAR2(8) := 'PROFILES';
53 REC_TYPE_STATS CONSTANT VARCHAR2(10) := 'STATISTICS';
54 REC_TYPE_INDEX CONSTANT VARCHAR2(7) := 'INDEX';
55 REC_TYPE_INDEXES CONSTANT VARCHAR2(7) := 'INDEXES';
56 REC_TYPE_PX CONSTANT VARCHAR2(18) := 'PARALLEL_EXECUTION';
57 REC_TYPE_ALTER_PLAN CONSTANT VARCHAR2(17) := 'ALTERNATIVE_PLAN';
58 REC_TYPE_ALTER_PLANS CONSTANT VARCHAR2(17) := 'ALTERNATIVE_PLANS';
59
60 --
61 -- capture section constants
62 --
63 MODE_REPLACE_OLD_STATS CONSTANT NUMBER := 1;
64 MODE_ACCUMULATE_STATS CONSTANT NUMBER := 2;
65
66 --
67 -- SQL tuning set constants
68 --
69 SINGLE_EXECUTION CONSTANT POSITIVE := 1;
70 ALL_EXECUTIONS CONSTANT POSITIVE := 2;
71 LIMITED_COMMAND_TYPE CONSTANT BINARY_INTEGER := 1;
72 ALL_COMMAND_TYPE CONSTANT BINARY_INTEGER := 2;
73
74 --
75 -- SQL profile type
76 --
77 REGULAR_PROFILE CONSTANT VARCHAR2(11) := 'SQL PROFILE';
78 PX_PROFILE CONSTANT VARCHAR2(10) := 'PX PROFILE';
79
80 -- sqlset staging table constants
81 STS_STGTAB_10_2_VERSION CONSTANT NUMBER := 1;
82 STS_STGTAB_11_1_VERSION CONSTANT NUMBER := 2;
83 STS_STGTAB_11_2_VERSION CONSTANT NUMBER := 3;
84 STS_STGTAB_11_202_VERSION CONSTANT NUMBER := 4;
85 STS_STGTAB_12_1_VERSION CONSTANT NUMBER := 5;
86
87
88 -- constant for recursive sql filter
89 NO_RECURSIVE_SQL CONSTANT VARCHAR2(30) := 'N';
90 HAS_RECURSIVE_SQL CONSTANT VARCHAR2(30) := 'Y';
91
92 -- hash table for value-pair arguments
93 TYPE arglist IS TABLE OF VARCHAR2(32767) INDEX BY VARCHAR2(32767);
94
95 -- SQL monitoring types
96 MONITOR_TYPE_SQL CONSTANT NUMBER := 1;
97 MONITOR_TYPE_DBOP CONSTANT NUMBER := 2;
98 MONITOR_TYPE_ALL CONSTANT NUMBER := 3;
99
100 -----------------------------------------------------------------------------
101 -- procedure / function declarations --
102 -----------------------------------------------------------------------------
103
104 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
105 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
106 -- ----------------------------- --
107 -- SQL TUNE PROCEDURES/FUNCTIONS --
108 -- ----------------------------- --
109 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
110 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
111
112 --------------------- create_tuning_task - sql text format ------------------
113 -- NAME:
114 -- create_tuning_task - CRATE a TUNING TASK in order to tune a single SQL
115 -- statement (sql text format)
116 --
117 -- DESCRIPTION
118 -- This function is called to prepare the tuning of a single statement
119 -- given its text.
120 -- The function mainly creates an advisor task and sets its parameters.
121 --
122 -- PARAMETERS:
123 -- sql_text (IN) - text of a SQL statement
124 -- bind_list (IN) - a set of bind values
125 -- user_name (IN) - the username for who the statement will be tuned
126 -- scope (IN) - tuning scope (limited/comprehensive)
127 -- time_limit (IN) - maximum duration in second for the tuning session
128 -- task_name (IN) - optional tuning task name
129 -- description (IN) - maximum of 256 SQL tuning session description
130 -- con_name (IN) - name of a container in a CDB. It is either
131 -- for ROOT or a pluggable database.
132 --
133 -- RETURNS:
134 -- SQL tuning task unique name
135 --
136 -- EXCEPTIONS:
137 -- To be done
138 -----------------------------------------------------------------------------
139 FUNCTION create_tuning_task(
140 sql_text IN CLOB,
141 bind_list IN sql_binds := NULL,
142 user_name IN VARCHAR2 := NULL,
143 scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
144 time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
145 task_name IN VARCHAR2 := NULL,
146 description IN VARCHAR2 := NULL,
147 con_name IN VARCHAR2 := NULL)
148 RETURN VARCHAR2;
149
150 --------------------- create_tuning_task - sql_id format --------------------
151 -- NAME:
152 -- create_tuning_task - sql_id format
153 --
154 -- DESCRIPTION
155 -- This function is called to prepare the tuning of a single statement
156 -- from the Cursor Cache given its identifier.
157 -- The function mainly creates an advisor task and sets its parameters.
158 --
159 -- PARAMETERS:
160 -- sql_id (IN) - identifier of the statement
161 -- plan_hash_value (IN) - hash value of the sql execution plan
162 -- scope (IN) - tuning scope (limited/comprehensive)
163 -- time_limit (IN) - maximum tuning duration in second
164 -- task_name (IN) - optional tuning task name
165 -- description (IN) - maximum of 256 SQL tuning session description
166 -- con_name (IN) - name of a container in a CDB. It is either
167 -- for ROOT or a pluggable database.
168 --
169 -- RETURNS:
170 -- SQL tuning task unique name
171 --
172 -- EXCEPTIONS:
173 -- To be done
174 -----------------------------------------------------------------------------
175 FUNCTION create_tuning_task(
176 sql_id IN VARCHAR2,
177 plan_hash_value IN NUMBER := NULL,
178 scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
179 time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
180 task_name IN VARCHAR2 := NULL,
181 description IN VARCHAR2 := NULL,
182 con_name IN VARCHAR2 := NULL)
183 RETURN VARCHAR2;
184
185 -------------- create_tuning_task - workload repository format --------------
186 -- NAME:
187 -- create_tuning_task - workload repository format
188 --
189 -- DESCRIPTION
190 -- This function is called to prepare the tuning of a single statement
191 -- from the workload repository given a range of snapshot identifiers.
192 -- The function mainly creates an advisor task and sets its parameters.
193 --
194 -- PARAMETERS:
195 -- begin_snap (IN) - begin snapshot identifier
196 -- end_snap (IN) - end snapshot identifier
197 -- sql_id (IN) - identifier of the statement
198 -- plan_hash_value (IN) - plan hash value
199 -- scope (IN) - tuning scope (limited/comprehensive)
200 -- time_limit (IN) - maximum duration in second for tuning
201 -- task_name (IN) - optional tuning task name
202 -- description (IN) - maximum of 256 SQL tuning session description
203 -- cont_name (IN) - name of a container in a CDB. It is the name
204 -- of either ROOT or a pluggable database.
205 --
206 -- RETURNS:
207 -- SQL tuning task unique name
208 --
209 -- EXCEPTIONS:
210 -- To be done
211 -----------------------------------------------------------------------------
212 FUNCTION create_tuning_task(
213 begin_snap IN NUMBER,
214 end_snap IN NUMBER,
215 sql_id IN VARCHAR2,
216 plan_hash_value IN NUMBER := NULL,
217 scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
218 time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
219 task_name IN VARCHAR2 := NULL,
220 description IN VARCHAR2 := NULL,
221 con_name IN VARCHAR2 := NULL)
222 RETURN VARCHAR2;
223
224 ---------------------- create_tuning_task - sqlset format -------------------
225 -- NAME:
226 -- create_tuning_task - sqlset format
227 --
228 -- DESCRIPTION:
229 -- This function is called to prepare the tuning of a sqlset
230 -- The function mainly creates an advisor task and sets its parameters.
231 --
232 -- PARAMETERS:
233 -- sqlset_name (IN) - sqlset name
234 -- basic_filter (IN) - SQL predicate to filter the SQL from the STS
235 -- object_filter (IN) - object filter
236 -- rank(i) (IN) - an order-by clause on the selected SQL
237 -- result_percentage (IN) - a percentage on the sum of a ranking measure
238 -- result_limit (IN) - top L(imit) SQL from (filtered/ranked) SQL
239 -- scope (IN) - tuning scope (limited/comprehensive)
240 -- time_limit (IN) - maximum tuning duration in seconds
241 -- task_name (IN) - optional tuning task name
242 -- description (IN) - maximum of 256 SQL tuning session description
243 -- plan_filter (IN) - plan filter. It is applicable in case there
244 -- are multiple plans (plan_hash_value)
245 -- associated to the same statement. This filter
246 -- allows selecting one plan (plan_hash_value)
247 -- only. Possible values are:
248 -- + LAST_GENERATED: plan with most recent
249 -- timestamp.
250 -- + FIRST_GENERATED: opposite to LAST_GENERATED
251 -- + LAST_LOADED: plan with most recent
252 -- first_load_time stat info.
253 -- + FIRST_LOADED: opposite to LAST_LOADED
254 -- + MAX_ELAPSED_TIME: plan with max elapsed
255 -- time
256 -- + MAX_BUFFER_GETS: plan with max buffer gets
257 -- + MAX_DISK_READS: plan with max disk reads
258 -- + MAX_DIRECT_WRITES: plan with max direct
259 -- writes
260 -- + MAX_OPTIMIZER_COST: plan with max opt. cost
261 --
262 -- sqlset_owner (IN) - the owner of the sqlset, or null for current
263 -- schema owner
264 --
265 -- RETURNS:
266 -- SQL tuning task unique name
267 --
268 -- EXCEPTIONS:
269 -- To be done
270 -----------------------------------------------------------------------------
271 FUNCTION create_tuning_task(
272 sqlset_name IN VARCHAR2,
273 basic_filter IN VARCHAR2 := NULL,
274 object_filter IN VARCHAR2 := NULL,
275 rank1 IN VARCHAR2 := NULL,
276 rank2 IN VARCHAR2 := NULL,
277 rank3 IN VARCHAR2 := NULL,
278 result_percentage IN NUMBER := NULL,
279 result_limit IN NUMBER := NULL,
280 scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
281 time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
282 task_name IN VARCHAR2 := NULL,
283 description IN VARCHAR2 := NULL,
284 plan_filter IN VARCHAR2 := 'MAX_ELAPSED_TIME',
285 sqlset_owner IN VARCHAR2 := NULL)
286 RETURN VARCHAR2;
287
288 ---------------------- create_tuning_task - SPA Task format -----------------
289 -- NAME:
290 -- create_tuning_task - SQL Performance Analyzer (SPA) task format
291 --
292 -- DESCRIPTION:
293 -- This function is called to prepare the tuning of sql stmts obtained
294 -- from a SPA task. The specified (or latest) 'compare performance'
295 -- trial of the SPA task is checked for regressed SQLs and only those SQL
296 -- statements are tuned by the tuning task. The regressed SQLs are tuned
297 -- using the compilation environment captured during the second execution
298 -- trial of the SPA task.
299 -- The function mainly creates an advisor task and sets its parameters.
300 --
301 -- PARAMETERS:
302 -- spa_task_name (IN) - SPA task name
303 -- spa_task_owner (IN) - Owner of specified SPA task or null for
304 -- current user
305 -- spa_compare_exec (IN) - Execution name of Compare Performance trial
306 -- of SPA task. If NULL, we use the most recent
307 -- execution of the given SPA task, of type
308 -- 'compare performance'
309 -- basic_filter (IN) - SQL predicate to filter the SQL from the STS
310 -- time_limit (IN) - Execution time limit
311 -- task_name (IN) - optional tuning task name
312 -- description (IN) - maximum of 256 SQL tuning session description
313 --
314 -- RETURNS:
315 -- SQL tuning task unique name
316 --
317 -- EXCEPTIONS:
318 -- To be done
319 -----------------------------------------------------------------------------
320 FUNCTION create_tuning_task(
321 spa_task_name IN VARCHAR2,
322 spa_task_owner IN VARCHAR2 := NULL,
323 spa_compare_exec IN VARCHAR2 := NULL,
324 basic_filter IN VARCHAR2 := NULL,
325 time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
326 task_name IN VARCHAR2 := NULL,
327 description IN VARCHAR2 := NULL)
328 RETURN VARCHAR2;
329
330 -------------------------- set_tuning_task_parameter ------------------------
331 -- NAME:
332 -- set_tuning_task_parameter - set sql tuning task parameter value
333 --
334 -- DESCRIPTION:
335 -- This procedure is called to update the value of a sql tuning parameter
336 -- of type VARCHAR2.
337 -- The task must be set to its initial state before calling this
338 -- procedure. The possible tuning parameters that can be set by this
339 -- procedure are:
340 -- MODE : tuning scope (comprehensive, limited)
341 -- USERNAME : username under which the statement will be parsed
342 -- BASIC_FILTER : basic filter for sql tuning set
343 -- OBJECT_FILTER : object filter for sql tuning set
344 -- PLAN_FILTER : plan filter for sql tuning set (see select_sqlset
345 -- for possible values)
346 -- RANK_MEASURE1 : first ranking measure for sql tuning set
350 -- TEST_EXECUTE : FULL/AUTO/OFF whether the advisor should test-
347 -- RANK_MEASURE2 : second possible ranking measure for sql tuning set
348 -- RANK_MEASURE3 : third possible ranking measure for sql tuning set
349 -- RESUME_FILTER : a extra filter for sts besides basic_filter
351 -- execute sql statements to verify the recommendation
352 -- benefit.
353 -- - FULL test-executes up to the local time limit
354 -- - AUTO test-executes for an automatically-chosen
355 -- period
356 --
357 -- The following parameters are supported for the automatic tuning task
358 -- only:
359 -- ACCEPT_SQL_PROFILES : TRUE/FALSE whether the system should
360 -- accept SQL Profiles automatically
361 --
362 -- PARAMETERS:
363 -- task_name (IN) - identifier of the task to execute
364 -- parameter (IN) - name of the parameter to set
365 -- value (IN) - new value of the specified parameter
366 --
367 -- RETURNS:
368 -- NONE
369 --
370 -- EXCEPTIONS:
371 -- To be done
372 -----------------------------------------------------------------------------
373 PROCEDURE set_tuning_task_parameter(
374 task_name IN VARCHAR2,
375 parameter IN VARCHAR2,
376 value IN VARCHAR2);
377
378 -------------------------- set_tuning_task_parameter ------------------------
379 -- NAME:
380 -- set_tuning_task_parameter - set sql tuning task parameter value
381 --
382 -- DESCRIPTION:
383 -- This procedure is called to update the value of a sql tuning parameter
384 -- of type NUMBER. The task must be set to its initial state before
385 -- calling this procedure. The possible tuning parameters that can be set
386 -- by this procedure are:
387 -- DAYS_TO_EXPIRE : number of days until the task is deleted
388 -- EXECUTION_DAYS_TO_
389 -- EXPIRE : number of days until the tasks's executions
390 -- will be deleted (without deleting the task)
391 -- TARGET_OBJECTS : id of advisor framework object to tune
392 -- TIME_LIMIT : global time out
393 -- LOCAL_TIME_LIMIT : local time out
394 -- SQL_LIMIT : maximum number of sts statements to tune
395 -- SQL_PERCENTAGE : percentage filter of sts statements
396 -- COMMIT_ROWS : number of tuned statements after which tuning
397 -- results will be commited to be accessible by
398 -- the user.
399 --
400 -- The following parameters are supported for the automatic tuning task
401 -- only:
402 -- MAX_SQL_PROFILES_PER_EXEC : Max # of SQL Profiles to create per run
403 -- MAX_AUTO_SQL_PROFILES : Max # of automatic SQL Profiles allowed
404 -- on the system in total
405 --
406 -- PARAMETERS:
407 -- task_name (IN) - identifier of the task to execute
408 -- parameter (IN) - name of the parameter to set
409 -- value (IN) - new value of the specified parameter
410 --
411 -- RETURNS:
412 -- NONE
413 --
414 -- EXCEPTIONS:
415 -- To be done
416 -----------------------------------------------------------------------------
417 PROCEDURE set_tuning_task_parameter(
418 task_name IN VARCHAR2,
419 parameter IN VARCHAR2,
420 value IN NUMBER);
421
422 ----------------------------- set_tuning_task_parameter ---------------------
423 -- NAME:
424 -- set_tuning_task_parameter - set sql tuning task parameter
425 -- default value
426 --
427 -- DESCRIPTION:
428 -- This procedure is called to update the default value of a sql tuning
429 -- parameter of type VARCHAR2. The task must be set to its initial state
430 -- before calling this procedure (see set_tuning_task_parameter above
431 -- for more details about possible parameters and their possible values
432 -- that can be set by this procedure).
433 --
434 -- PARAMETERS:
435 -- parameter (IN) - name of the parameter to set
436 -- value (IN) - new value of the specified parameter
437 --
438 -- RETURNS:
439 -- NONE
440 --
441 -- EXCEPTIONS:
442 -- To be done
443 -----------------------------------------------------------------------------
444 PROCEDURE set_tuning_task_parameter(
445 parameter IN VARCHAR2,
446 value IN VARCHAR2);
447
448 ------------------------- set_tuning_task_parameter -------------------------
449 -- NAME:
450 -- set_tuning_task_parameter - set sql tuning task parameter
451 -- default value
452 --
453 -- DESCRIPTION:
454 -- This procedure is called to update the default value of a sql tuning
455 -- parameter of type NUMBER. The task must be set to its initial state
456 -- before calling this procedure (see set_tuning_task_parameter above
457 -- for more details about possible parameters and their possible values
458 -- that can be set by this procedure).
459 --
460 -- PARAMETERS:
461 -- parameter (IN) - name of the parameter to set
462 -- value (IN) - new value of the specified parameter
463 --
464 -- RETURNS:
465 -- NONE
466 --
467 -- EXCEPTIONS:
471 parameter IN VARCHAR2,
468 -- To be done
469 -----------------------------------------------------------------------------
470 PROCEDURE set_tuning_task_parameter(
472 value IN NUMBER);
473
474 ------------------------------ execute_tuning_task --------------------------
475 -- NAME:
476 -- execute_tuning_task - execute a sql tuning task
477 --
478 -- DESCRIPTION:
479 -- This procedure is called to execute a previously created tuning task
480 --
481 -- PARAMETERS:
482 -- task_name (IN) - identifier of the task to execute
483 -- execution_name (IN) - A name to qualify and identify an execution
484 -- If not specified, it be generated by
485 -- the advisor and returned by function.
486 -- execution_params (IN) - List of parameters (name, value) for
487 -- the specified execution. Notice that execution
488 -- parameters are real task parameters that
489 -- have effect only on the execution they
490 -- specified for.
491 -- Example:
492 -- dbms_advisor.arglist('time_limit', 12,
493 -- 'username', 'foo')
494 -- execution_desc (IN) - A 256-length string describing the execution.
495 --
496 -- RETURNS:
497 -- The function version returns the name of the new execution
498 --
499 -- EXCEPTIONS:
500 -- To be done
501 -----------------------------------------------------------------------------
502 FUNCTION execute_tuning_task(
503 task_name IN VARCHAR2,
504 execution_name IN VARCHAR2 := NULL,
505 execution_params IN dbms_advisor.argList := NULL,
506 execution_desc IN VARCHAR2 := NULL)
507 RETURN VARCHAR2;
508 --
509 PROCEDURE execute_tuning_task(
510 task_name IN VARCHAR2,
511 execution_name IN VARCHAR2 := NULL,
512 execution_params IN dbms_advisor.argList := NULL,
513 execution_desc IN VARCHAR2 := NULL);
514
515
516 ----------------------------- interrupt_tuning_task -------------------------
517 -- NAME:
518 -- interrupt_tuning_task - interrupt a sql tuning task
519 --
520 -- DESCRIPTION:
521 -- This procedure is called to interrupt the currently executing tuning
522 -- task. The task will end its operations as it would at a normal exit
523 -- so that the user will be able to access the intermediate results at
524 -- this point.
525 --
526 -- PARAMETERS:
527 -- task_name (IN) - identifier of the task to execute
528 --
529 -- RETURNS:
530 -- NONE
531 --
532 -- EXCEPTIONS:
533 -- To be done
534 -----------------------------------------------------------------------------
535 procedure interrupt_tuning_task(task_name IN VARCHAR2);
536
537 ----------------------------- cancel_tuning_task ----------------------------
538 -- NAME:
539 -- cancel_tuning_task - cancel a sql tuning task
540 --
541 -- DESCRIPTION:
542 -- This procedure is called to cancel the currently executing tuning
543 -- task. All intermediate result data will be removed from the task.
544 --
545 -- PARAMETERS:
546 -- task_name (IN) - identifier of the task to execute
547 --
548 -- RETURNS:
549 -- NONE
550 --
551 -- EXCEPTIONS:
552 -- To be done
553 -----------------------------------------------------------------------------
554 PROCEDURE cancel_tuning_task(task_name IN VARCHAR2);
555
556 ----------------------------- reset_tuning_task -----------------------------
557 -- NAME:
558 -- reset_tuning_task - reset a sql tuning task
559 --
560 -- DESCRIPTION:
561 -- This procedure is called to reset a tuning task to its initial state.
562 -- All intermediate result data will be deleted. Call this procedure on
563 -- a task that is not currently executing.
564 --
565 -- PARAMETERS:
566 -- task_name (IN) - identifier of the task to reset
567 --
568 -- RETURNS:
569 -- NONE
570 --
571 -- EXCEPTIONS:
572 -- To be done
573 -----------------------------------------------------------------------------
574 PROCEDURE reset_tuning_task(task_name IN VARCHAR2);
575
576 ------------------------------- drop_tuning_task ----------------------------
577 -- NAME:
578 -- drop_tuning_task - drop a sql tuning task
579 --
580 -- DESCRIPTION:
581 -- This procedure is called to drop a SQL tuning task.
582 -- The task and All its result data will be deleted.
583 --
584 -- PARAMETERS:
585 -- task_name (IN) - identifier of the task to execute
586 --
587 -- RETURNS:
588 -- NONE
589 --
590 -- EXCEPTIONS:
591 -- To be done
592 -----------------------------------------------------------------------------
593 PROCEDURE drop_tuning_task(task_name IN VARCHAR2);
594
595 ----------------------------- resume_tuning_task ----------------------------
596 -- NAME:
597 -- resume_tuning_task - resume a sql tuning task
598 --
599 -- DESCRIPTION:
603 -- task_name (IN) - identifier of the task to execute
600 -- This procedure is called to resume a previously interrupted task.
601 --
602 -- PARAMETERS:
604 -- basic_filter (IN) - a SQL predicate to filter the SQL from a STS.
605 -- Note that this filter will be applied in
606 -- conjunction with the basic filter
607 -- (i.e., parameter basic_filter) specified
608 -- when calling create_tuning_task.
609 -- RETURNS:
610 -- NONE
611 --
612 -- EXCEPTIONS:
613 -- To be done
614 -----------------------------------------------------------------------------
615 PROCEDURE resume_tuning_task(
616 task_name IN VARCHAR2,
617 basic_filter IN VARCHAR2 := NULL);
618
619 ------------------------------- report_tuning_task --------------------------
620 -- NAME:
621 -- report_tuning_task - report a SQL tuning task
622 --
623 -- DESCRIPTION:
624 -- This procedure is called to display the results of a tuning task.
625 --
626 -- PARAMETERS:
627 -- task_name (IN) - name of the task to report.
628 -- type (IN) - type of the report.
629 -- Possible values are: TEXT, HTML, XML.
630 -- level (IN) - format of the recommendations.
631 -- Possible values are TYPICAL, BASIC, ALL.
632 -- section (IN) - particular section in the report.
633 -- Possible values are:
634 -- SUMMARY,
635 -- FINDINGS,
636 -- PLAN,
637 -- INFORMATION,
638 -- ERROR,
639 -- ALL.
640 -- object_id (IN) - identifier of the advisor framework object that
641 -- represents a given statement in a SQL Tuning Set
642 -- (STS).
643 -- result_limit (IN) - number of statements in a STS for which the
644 -- report is generated.
645 -- owner_name (IN) - owner of the relevant tuning task. Defaults to
646 -- the current schema owner.
647 -- execution_name (IN) - name of the task execution to use. If NULL, the
648 -- report will be generated for the last task
649 -- execution.
650 -- RETURNS
651 -- A clob containing the desired report.
652 -----------------------------------------------------------------------------
653 FUNCTION report_tuning_task(
654 task_name IN VARCHAR2,
655 type IN VARCHAR2 := TYPE_TEXT,
656 level IN VARCHAR2 := LEVEL_TYPICAL,
657 section IN VARCHAR2 := SECTION_ALL,
658 object_id IN NUMBER := NULL,
659 result_limit IN NUMBER := NULL,
660 owner_name IN VARCHAR2 := NULL,
661 execution_name IN VARCHAR2 := NULL)
662 RETURN clob;
663
664 ------------------------------ script_tuning_task ---------------------------
665 -- NAME:
666 -- script_tuning_task - get a script to implement a subset of
667 -- recommendations.
668 --
669 -- DESCRIPTION:
670 -- This function will return a CLOB containing the PL/SQL calls
671 -- to be executed to implement the subset of recommendations dictated by
672 -- the arguments. This script should then by checked by the DBA and
673 -- executed.
674 --
675 -- Wrap with a call to dbms_advisor.create_file to put it into a file.
676 --
677 -- PARAMETERS:
678 -- task_name (IN) - name of the task to get a script for
679 -- rec_type (IN) - filter the script by types of recommendations
680 -- to include.
681 -- Any subset of the following separated by commas,
682 -- or 'ALL': 'PROFILES' 'STATISTICS' 'INDEXES'
683 -- 'ALTERNATIVE_PLANS'
684 -- e.g. script with profiles and stats:
685 -- 'PROFILES, STATISTICS'
686 -- object_id (IN) - optionally filter by a single object ID
687 -- result_limit (IN) - optionally show commands for only top N sql
688 -- (ordered by object id and ignored if an
689 -- object_id is also specified)
690 -- owner_name (IN) - owner of the relevant tuning task. Defaults to
691 -- the current schema owner.
692 -- execution_name (IN) - name of the task execution to use. If NULL, the
693 -- script will be generated for the last task
694 -- execution.
695 -- RETURNS
696 -- script as a CLOB
697 -----------------------------------------------------------------------------
698 FUNCTION script_tuning_task(
699 task_name IN VARCHAR2,
700 rec_type IN VARCHAR2 := REC_TYPE_ALL,
701 object_id IN NUMBER := NULL,
702 result_limit IN NUMBER := NULL,
703 owner_name IN VARCHAR2 := NULL,
704 execution_name IN VARCHAR2 := NULL)
705 RETURN CLOB;
706
707 --------------------- schedule_tuning_task - sql_id format ------------------
708 -- NAME:
709 -- schedule_tuning_task - sql_id format
710 --
711 -- DESCRIPTION
715 -- creates a dbms_scheduler job that executes the created tuning task
712 -- This function is called to schedule the tuning of a single statement
713 -- from the cursor cache given its SQL identifier.
714 -- The function mainly creates an SQL tuning advisor task and then
716 -- at the specified start time/date.
717 --
718 -- PARAMETERS:
719 -- sql_id (IN) - identifier of the statement
720 -- plan_hash_value (IN) - hash value of the sql execution plan
721 -- start_date (IN) - the date on which this schedule becomes valid.
722 -- If null then the task is immediately executed.
723 -- scope (IN) - tuning scope (limited/comprehensive)
724 -- time_limit (IN) - maximum tuning duration in second
725 -- task_name (IN) - optional tuning task name
726 -- description (IN) - maximum of 256 SQL tuning session description
727 -- con_name (IN) - name of a container in a CDB. It is either
728 -- for ROOT or a pluggable database.
729 -- RETURNS:
730 -- SQL tuning task unique name
731 --
732 -- NOTE:
733 -- - The task is scheduled once only
734 -- - The name of the scheduler job is created as follows:
735 -- Job name = sqltune_job_<task_id>_<ora_hash(systimestamp)>
736 -- - caller must possess privilege "create job" for the job
737 -- to be scheduled.
738 --
739 -- EXCEPTIONS:
740 -- To be done
741 -----------------------------------------------------------------------------
742 FUNCTION schedule_tuning_task(
743 sql_id IN VARCHAR2,
744 plan_hash_value IN NUMBER := NULL,
745 start_date IN TIMESTAMP WITH TIME ZONE := NULL,
746 scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
747 time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
748 task_name IN VARCHAR2 := NULL,
749 description IN VARCHAR2 := NULL,
750 con_name IN VARCHAR2 := NULL)
751 RETURN VARCHAR2;
752
753 -------------- schedule_tuning_task - workload repository format ------------
754 -- NAME:
755 -- schedule_tuning_task - workload repository format
756 --
757 -- DESCRIPTION
758 -- This function is called to schedule the tuning of a single statement
759 -- from the workload repository given a range of snapshot identifiers.
760 -- The function mainly creates an SQL tuning advisor task and then
761 -- creates a dbms_scheduler job that executes the created tuning task
762 -- at the specified start time/date.
763 --
764 -- PARAMETERS:
765 -- begin_snap (IN) - begin snapshot identifier
766 -- end_snap (IN) - end snapshot identifier
767 -- sql_id (IN) - identifier of the statement
768 -- plan_hash_value (IN) - plan hash value
769 -- start_date (IN) - the date on which this schedule becomes valid.
770 -- If null then the task is immediately executed.
771 -- scope (IN) - tuning scope (limited/comprehensive)
772 -- time_limit (IN) - maximum duration in second for tuning
773 -- task_name (IN) - optional tuning task name
774 -- description (IN) - maximum of 256 SQL tuning session description
775 -- cont_name (IN) - name of a container in a CDB. It is the name
776 -- of either ROOT or a pluggable database.
777 --
778 -- RETURNS:
779 -- SQL tuning task unique name
780 --
781 -- NOTE:
782 -- - The task is scheduled once only
783 -- - The name of the scheduler job is created as follows:
784 -- Job name = sqltune_job_<task_id>_<ora_hash(systimestamp)>
785 -- - caller must possess privilege "create job" for the job
786 -- to be scheduled.
787 --
788 -- EXCEPTIONS:
789 -- To be done
790 -----------------------------------------------------------------------------
791 FUNCTION schedule_tuning_task(
792 begin_snap IN NUMBER,
793 end_snap IN NUMBER,
794 sql_id IN VARCHAR2,
795 plan_hash_value IN NUMBER := NULL,
796 start_date IN TIMESTAMP WITH TIME ZONE := NULL,
797 scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
798 time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
799 task_name IN VARCHAR2 := NULL,
800 description IN VARCHAR2 := NULL,
801 con_name IN VARCHAR2 := NULL)
802 RETURN VARCHAR2;
803
804 ----------------------------- create_sql_plan_baseline ----------------------
805 -- NAME:
806 -- create_sql_plan_baseline - create a sql plan baseline
807 -- for an existing plan
808 --
809 -- DESCRIPTION:
810 -- SQL tune advisor will detect regressions. If there are better plans
811 -- than the original one, the advisor will recommend you to use this API
812 -- to prevent the regression in the future.
813 --
814 -- PARAMETERS:
815 -- task_name (IN) - name of the task to get a script for
816 -- object_id (IN) - the object ID which the sql is corresponding to
817 -- plan_hash_value (IN) - the plan to create plan baseline
818 -- owner_name (IN) - owner of the relevant tuning task. Defaults to
819 -- the current schema owner.
820 -- RETURNS
821 -- NONE
825 object_id IN NUMBER := NULL,
822 -----------------------------------------------------------------------------
823 PROCEDURE create_sql_plan_baseline(
824 task_name IN VARCHAR2,
826 plan_hash_value IN NUMBER,
827 owner_name IN VARCHAR2 := NULL);
828
829 ---------------------------- implement_tuning_task --------------------------
830 -- NAME:
831 -- implement_tuning_task - implement a set of recommendations from a
832 -- tuning task
833 --
834 -- DESCRIPTION:
835 -- This function will implement a set of SQL Profile recommendations made
836 -- by the SQL Tuning Advisor. Calling it is analogous to calling
837 -- script_tuning_task and then running the script.
838 --
839 -- PARAMETERS:
840 -- task_name (IN) - name of the task to get a script for
841 -- rec_type (IN) - filter the types of recs to implement.
842 -- Currently the only one supported is 'PROFILES'.
843 -- owner_name (IN) - owner of the relevant tuning task. Defaults to
844 -- the current schema owner.
845 -- execution_name (IN) - name of the task execution to use. If NULL, the
846 -- script will be generated for the last task
847 -- execution.
848 -- RETURNS
849 -- NONE
850 -----------------------------------------------------------------------------
851 PROCEDURE implement_tuning_task(
852 task_name IN VARCHAR2,
853 rec_type IN VARCHAR2 := REC_TYPE_SQL_PROFILES,
854 owner_name IN VARCHAR2 := NULL,
855 execution_name IN VARCHAR2 := NULL);
856
857
858 -----------------------------------------------------------------------------
859 -- automatic tuning task functions --
860 -----------------------------------------------------------------------------
861 -- NOTE that only an API for getting a report is provided here. For all --
862 -- other usage of the automatic tuning task, please see DBMS_AUTO_SQLTUNE. --
863 -----------------------------------------------------------------------------
864
865 -------------------------------- report_auto_tuning_task --------------------
866 -- NAME:
867 -- report_auto_tuning_task
868 --
869 -- DESCRIPTION:
870 -- Get a report from the automatic tuning task. This differs from the
871 -- report_tuning_task API in that it takes a range of subtasks to report
872 -- on.
873 --
874 -- PARAMETERS:
875 -- begin_exec (IN) - name of execution to begin the report from. Null
876 -- to get a report on the most recent run. See
877 -- DBA_ADVISOR_EXECUTIONS.
878 -- end_exec (IN) - name of execution to end the report at. Null to
879 -- get a report on the most recent run. See
880 -- DBA_ADVISOR_EXECUTIONS.
881 -- type (IN) - output type for report, one of:
882 -- TYPE_TEXT: text report
883 -- TYPE_HTML: html report
884 -- level (IN) - level of detail in the report:
885 -- LEVEL_BASIC: simple version of the report.
886 -- Just show info about the actions taken by
887 -- the advisor.
888 -- LEVEL_TYPICAL: show info about every statement
889 -- analyzed, including recs not implemented.
890 -- LEVEL_ALL: verbose report level, also give
891 -- annotations about statements skipped over.
892 -- section (IN) - sections of report to show (comma-separated):
893 -- SECTION_SUMMARY - summary information
894 -- SECTION_FINDINGS - tuning findings
895 -- SECTION_PLAN - explain plans
896 -- SECTION_INFORMATION - general information
897 -- SECTION_ERROR - statements with errors
898 -- SECTION_ALL - all statements
899 -- object_id (IN) - advisor framework object id that represents a
900 -- single statement to restrict reporting to. NULL
901 -- for all statements. Only valid for reports
902 -- that target a single execution.
903 -- result_limit (IN) - maximum number of SQL to show in the report
904 --
905 -- RETURNS:
906 -- CLOB with report content
907 --
908 -- PRIVILEGES:
909 -- Need SELECT privilege on the DBA_ADVISOR views
910 -----------------------------------------------------------------------------
911 FUNCTION report_auto_tuning_task(
912 begin_exec IN VARCHAR2 := NULL,
913 end_exec IN VARCHAR2 := NULL,
914 type IN VARCHAR2 := TYPE_TEXT,
915 level IN VARCHAR2 := LEVEL_TYPICAL,
916 section IN VARCHAR2 := SECTION_ALL,
917 object_id IN NUMBER := NULL,
918 result_limit IN NUMBER := NULL)
919 RETURN CLOB;
920
921 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
922 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
923 -- --------------------------- --
927 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
924 -- SQLSET PROCEDURES/FUNCTIONS --
925 -- --------------------------- --
926 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
928
929 -----------------------------------------------------------------------------
930 -- Examples --
931 -----------------------------------------------------------------------------
932 -- In the following we give two examples that show how to use the package in
933 -- order to create, populate, manipulate and drop a sqlset.
934 -- The first example shows how to build a new sqlset by extracting
935 -- data from the Cursor cache, while the second one explains how to build a
936 -- sqlset from a USER defined workload.
937 --
938 --------------------------------------------
939 -- EXAMPLE 1: select from the cursor cache --
940 --------------------------------------------
941 --
942 -- DECLARE
943 -- sqlset_name VARCHAR2(30); /* sqlset name */
944 -- sqltset_cur dbms_sqltune.sqlset_cursor; /* a sqlset cursor variable */
945 -- ref_id NUMBER; /* a reference on a sqlset */
946 -- BEGIN
947 --
948 -- /* Choose an name for the sqlset to create */
949 -- sqlset_name := 'SQLSET_TEST_1';
950 --
951 -- /* Create an empty sqlset. You automatically become the owner of
952 -- this sqlset */
953 -- dbms_sqltune.create_sqlset(sqlset_name, 'test purpose');
954 --
955 -- /***********************************************************************
956 -- * Call the select_cursor_cache table function to order the sql *
957 -- * statements in the cursor cache by cpu_time (ranking measure1) and *
958 -- * then, select only that subset of statements, which contribute to 90%*
959 -- * (result percentage) of total cpu_time, but not more than Only 100 *
960 -- * statements, i.e., top 100 which represents (result_limit). *
961 -- * Only the firts ranking measure is spefied and the content of *
962 -- * the cursor cache is not filtered. *
963 -- * *
964 -- * The OPEN-FOR statement associates the sqlset cursor variable *
965 -- * with the SELECT-FROM-TABLE dynamic query which is used to call the *
966 -- * table function and fetch its results. Notice that you need not to *
967 -- * close the cursor. When this cursor is used to populate a Sql Tuning *
968 -- * Set using the load_sqlset procedure, this later will close *
969 -- * it for you. *
970 -- * *
971 -- * Notice the use of function VALUE(P) which takes as its argument, *
972 -- * the table alias for the table function and returns object instances *
973 -- * corresponding to rows as retuned by the table function which are *
974 -- * instances of type SQLSET_ROW. *
975 -- * ********************************************************************/
976 -- OPEN sqlset_cur FOR
977 -- SELECT VALUE(P) /* use of function VALUE */
978 -- FROM TABLE(
979 -- dbms_sqltune.select_cursor_cache(NULL, /* basic filter */
980 -- NULL, /* object filter */
981 -- 'cpu_time', /* first ranking */
982 -- NULL, /* second ranking */
983 -- NULL, /* third ranking */
984 -- 0.9, /* percentage */
985 -- 100) /* top N */
986 -- ) P; /* table instance */
987 --
988 --
989 -- /***********************************************************************
990 -- * Call the load_sqlset procedure to populated the created *
991 -- * sqlset by the results of the cursor cache table function *
992 -- **********************************************************************/
993 -- dbms_sqltune.load_sqlset(sqlset_name, sqlset_cur);
994 --
995 -- /***********************************************************************
996 -- * Add a reference to the sqlset so that other users cannot *
997 -- * modified it, i.e., drop it, delete statement from it, update it or *
998 -- * load it. Like this, the sqlset is protected. User have only *
999 -- * a read-only access to the sqlset. *
1000 -- * The add_reference function returns a reference ID that will be used *
1001 -- * later to deactivate the sqlset. *
1002 -- **********************************************************************/
1003 -- ref_id :=
1004 -- dbms_sqltune.add_sqlset_reference(sqlset_name,
1005 -- 'test sqlset: '|| sqlset_name);
1006 --
1007 -- /* process your sqlset */
1008 -- ...
1009 -- ...
1010 -- ...
1011 --
1012 -- /**********************************************************************
1013 -- * When your are done, remove the reference on the sqlset, so that it *
1017 -- dbms_sqltune.remove_sqlset_reference(sqlset_name, ref_id);
1014 -- * can be modified either by you (owner) or by another user who has a *
1015 -- * supper privilege ADMINISTER ANY SQLSET, etc. *
1016 -- *********************************************************************/
1018 --
1019 --
1020 -- /* Call the drop procedure to drop the sqlset */
1021 -- dbms_sqltune.drop_sqlset(sqlset_name);
1022 -- ...
1023 -- END
1024 --
1025 -------------------------------------------
1026 -- EXAMPLE 2: select from a user workload --
1027 -------------------------------------------
1028 --
1029 -- DECLARE
1030 -- sqlset_name VARCHAR2(30); /* sqlset name */
1031 -- sqlset_cur dbms_sqltune.sqlset_cursor; /* a sqlset cursor variable */
1032 -- ref_id NUMBER; /* a reference on a sqlset */
1033 -- BEGIN
1034 --
1035 -- /* Choose an name for the sqlset to create */
1036 -- sqlset_name := 'SQLSET_TEST_2';
1037 --
1038 -- /* Create an empty sqlset. You automatically become the owner of
1039 -- this SQLSET */
1040 -- dbms_sqltune.create_sqlset(sqlset_name, 'test purpose');
1041 --
1042 -- /***********************************************************************
1043 -- * In this example we suppose that the user workload is stored in *
1044 -- * a single table USER_WORKLOAD_TABLE. We suppose that the table stores*
1045 -- * only the text of a set of SQL statements identified by their sql_id.*
1046 -- * Use the OPEN-FOR statement to associate the query that extracts the *
1047 -- * content of the user workload, with a sqlset cursor before loading it*
1048 -- * into the sqlset. *
1049 -- * Notice the use of the CONSTRUCTOR of the sqlset_row object type *
1050 -- * This is IMPORTANT because the cursor MUST contains instances *
1051 -- * of this type as required by the load_sql function. Otherwise an *
1052 -- * error will occur and the SQLSET will not be loaded. *
1053 -- **********************************************************************/
1054 -- OPEN sqlset_cur FOR
1055 -- SELECT
1056 -- SQLSET_ROW(sql_id, sql_text, null, null, null, null,
1057 -- null, 0, 0, 0, 0, 0, 0, 0, 0, 0, null, 0, 0, 0, 0
1058 -- ) AS row
1059 -- FROM user_workload_table;
1060 --
1061 -- /***********************************************************************
1062 -- * Call the load_sql procedure to populated the created sqlset by the *
1063 -- * results of the cursor *
1064 -- **********************************************************************/
1065 -- dbms_sqltune.load_sqlset(sqlsetname, sqlsetcur);
1066 --
1067 -- /* the rest of the steps are similar to those in example 1 */
1068 -- ...
1069 -- ...
1070 -- ...
1071 -- END;
1072 --
1073 -----------------------------------------------------------------------------
1074
1075 -----------------------------------------------------------------------------
1076 -- type declarations --
1077 -----------------------------------------------------------------------------
1078 ----------------------------------- sqlset_cursor ---------------------------
1079 -- NAME:
1080 -- sqlset_cursor
1081 --
1082 -- DESCRIPTION:
1083 -- define a cursor type for SQL statements with their related data.
1084 -- This type is mainly used by the load_sqlset procedure
1085 -- as an argument to populate a sqlset from a possible data
1086 -- source. See the load_sqlset description for more details.
1087 --
1088 -- NOTES:
1089 -- It is important to keep in mind that this cursor is WEAKLY DEFINED.
1090 -- A variable of type sqlStatCursor when it is used either as an input
1091 -- by the load_sql procedure or returned by all table functions, it MUST
1092 -- contains rows of type sqlset_row.
1093 ----------------------------------------------------------------------------
1094 TYPE sqlset_cursor IS REF CURSOR;
1095
1096
1097 -----------------------------------------------------------------------------
1098 -- procedure/function declarations --
1099 -----------------------------------------------------------------------------
1100 ---------------------------------- create_sqlset ----------------------------
1101 -- NAME:
1102 -- create_sqlset
1103 --
1104 -- DESCRIPTION:
1105 -- This procedure creates a sqlset object in the database.
1106 --
1107 -- PARAMETERS:
1108 -- sqlset_name (IN) - the sqlset name
1109 -- description (IN) - the description of the sqlset
1110 -- sqlset_owner (IN) - the owner of the sqlset, or null for current schema
1111 -- owner
1112 -----------------------------------------------------------------------------
1113 PROCEDURE create_sqlset(
1114 sqlset_name IN VARCHAR2,
1115 description IN VARCHAR2 := NULL,
1116 sqlset_owner IN VARCHAR2 := NULL);
1117
1118 ---------------------------------- create_sqlset ----------------------------
1119 -- NAME:
1120 -- create_sqlset
1121 --
1122 -- DESCRIPTION:
1123 -- This procedure creates a sqlset object in the database.
1124 --
1125 -- PARAMETERS:
1126 -- sqlset_name (IN) - the sqlset name, can be NULL or omitted
1130 -- owner
1127 -- (in which case a name is generated automatically)
1128 -- description (IN) - the description of the sqlset
1129 -- sqlset_owner (IN) - the owner of the sqlset, or null for current schema
1131 --
1132 -- RETURNS:
1133 -- name of sqlset created. This will be the name passed in or, if a name
1134 -- is omitted (or NULL arg passed), the name we automatically create for
1135 -- the sqlset
1136 -----------------------------------------------------------------------------
1137 FUNCTION create_sqlset(
1138 sqlset_name IN VARCHAR2 := NULL,
1139 description IN VARCHAR2 := NULL,
1140 sqlset_owner IN VARCHAR2 := NULL)
1141 RETURN VARCHAR2;
1142
1143 ----------------------------------- drop_sqlset -----------------------------
1144 -- NAME:
1145 -- drop_sqlset
1146 --
1147 -- DESCRIPTION:
1148 -- This procedure is used to drop a sqlset if it is not active.
1149 -- When a sqlset is referenced by one or more clients
1150 -- (e.g. SQL tune advisor), it cannot be dropped.
1151 --
1152 -- PARAMETERS:
1153 -- sqlset_name (IN) - the sqlset name.
1154 -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1155 -- schema owner
1156 -----------------------------------------------------------------------------
1157 PROCEDURE drop_sqlset(
1158 sqlset_name IN VARCHAR2,
1159 sqlset_owner IN VARCHAR2 := NULL);
1160
1161 -------------------------------- delete_sqlset ------------------------------
1162 -- NAME:
1163 -- delete_sqlset
1164 --
1165 -- DESCRIPTION:
1166 -- Allows the deletion of a set of SQL statements from a sqlset.
1167 --
1168 -- PARAMETERS:
1169 -- sqlset_name (IN) - the sqlset name
1170 -- basic_filter (IN) - SQL predicate to filter the SQL from the
1171 -- sqlset. This basic filter is used as
1172 -- a where clause on the sqlset content to
1173 -- select a desired subset of Sql from the Tuning Set
1174 -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1175 -- schema owner
1176 -----------------------------------------------------------------------------
1177 PROCEDURE delete_sqlset(
1178 sqlset_name IN VARCHAR2,
1179 basic_filter IN VARCHAR2 := NULL,
1180 sqlset_owner IN VARCHAR2 := NULL);
1181
1182 ---------------------------------- load_sqlset ------------------------------
1183 -- NAME:
1184 -- load_sqlset
1185 --
1186 -- DESCRIPTION:
1187 -- This procedure populates the sqlset with a set of selected SQL.
1188 --
1189 -- PARAMETERS:
1190 -- sqlset_name (IN) - the name of sqlset to populate
1191 -- populate_cursor (IN) - the cursor reference to populate from
1192 -- load_option (IN) - specifies how the statements will be loaded
1193 -- into the SQL tuning set.
1194 -- The possible values are:
1195 -- + INSERT (default): add only new statements
1196 -- + UPDATE: update existing the SQL statements
1197 -- + MERGE: this is a combination of the two
1198 -- other options. This option inserts
1199 -- new statements and updates the
1200 -- information of the existing ones.
1201 -- update_option (IN) - specifies how the existing statements will be
1202 -- updated. This parameter is considered only if
1203 -- load_option is specified with 'UPDATE'/'MERGE'
1204 -- as an option. The possible values are:
1205 -- + REPLACE (default): update the statement
1206 -- using the new statistics, bind list,
1207 -- object list, etc.
1208 -- + ACCUMULATE: when possible combine attributes
1209 -- (e.g., statistics like elapsed_time, etc.)
1210 -- otherwise just replace the old values
1211 -- (e.g., module, action, etc.) by the new
1212 -- provided ones. The SQL statement attributes
1213 -- that can be accumulated are: elapsed_time,
1214 -- buffer_gets, disk_reads, row_processed,
1215 -- fetches, executions, end_of_fetch_count,
1216 -- stat_period and active_stat_period.
1217 -- update_attributes (IN) - specifies the list of a SQL statement attributes
1218 -- to update during a merge or update operation.
1219 -- The possible values are:
1220 -- + NULL (default): the content of the input
1221 -- cursor except the execution context.
1222 -- On other terms, it is equivalent to ALL
1223 -- without execution context like module,
1224 -- action, etc.
1225 -- + BASIC: statistics and binds only.
1226 -- + TYPICAL: BASIC + SQL plans (without
1227 -- row source statistics) and without
1228 -- object reference list.
1232 -- update: EXECUTION_CONTEXT,
1229 -- + ALL: all attributes including the execution
1230 -- context attributes like module, action, etc
1231 -- + List of comma separated attribute names to
1233 -- EXECUTION_STATISTICS,
1234 -- SQL_BINDS,
1235 -- SQL_PLAN,
1236 -- SQL_PLAN_STATISTICS: similar to
1237 -- SQL_PLAN + row source statistics.
1238 -- update_condition (IN) - specifies a where clause to execute the update
1239 -- operation. The update is performed only if
1240 -- the specified condition is true. The condition
1241 -- can refer to either the data source or
1242 -- destination. The condition must use the following
1243 -- prefixes to refer to attributes from the source
1244 -- or the destination:
1245 -- + OLD: to refer to statement attributes from
1246 -- the SQL tuning set (destination)
1247 -- + NEW: to refer to statements attributes from
1248 -- the input statements (source)
1249 -- Example: 'new.executions >= old.executions'.
1250 -- ignore_null (IN) - If true do not update an attribute if the new
1251 -- value is null, i.e., do not override with null
1252 -- values unless it is intentional.
1253 -- commit_rows (IN) - if a value is provided, the load will commit
1254 -- after each set of that many statements is
1255 -- inserted. If NULL is provided, the load will
1256 -- commit only once, at the end of the operation.
1257 -- sqlset_owner (IN) - the owner of the sqlset or null for current
1258 -- schema owner.
1259 -- Exceptions:
1260 -- This procedure returns an error when sqlset_name is invalid
1261 -- or a corresponding sqlset does not exist, the populate_cursor
1262 -- is incorrect and cannot be executed.
1263 -- FIXME: other exceptions are raised by this procedure. Need to update
1264 -- comments.
1265 -----------------------------------------------------------------------------
1266 PROCEDURE load_sqlset(
1267 sqlset_name IN VARCHAR2,
1268 populate_cursor IN sqlset_cursor,
1269 load_option IN VARCHAR2 := 'INSERT',
1270 update_option IN VARCHAR2 := 'REPLACE',
1271 update_condition IN VARCHAR2 := NULL,
1272 update_attributes IN VARCHAR2 := NULL,
1273 ignore_null IN BOOLEAN := TRUE,
1274 commit_rows IN POSITIVE := NULL,
1275 sqlset_owner IN VARCHAR2 := NULL);
1276
1277 ---------------------------- capture_cursor_cache_sqlset --------------------
1278 -- NAME:
1279 -- capture_cursor_cache_sqlset
1280 --
1281 -- DESCRIPTION:
1282 -- This procedure captures a workload from the cursor cache into a SQL
1283 -- tuning set, polling the cache multiple times over a time period and
1284 -- updating the workload data stored there. It can execute over as long
1285 -- a period as required to capture an entire system workload.
1286 --
1287 -- Note that this procedure commits after each incremental capture of
1288 -- statements, so you can monitor its progress by looking at the sqlset
1289 -- views. This operation is much more efficient than
1290 -- select_cursor_cache/load_sqlset so it should be used whenever you need
1291 -- to repeatedly capture a workload from the cursor cache.
1292 --
1293 -- ** ALSO NOTE ** This function does not capture the SQL present
1294 -- in the cursor cache when it is invoked, but rather it collects those
1295 -- SQL run over the 'time_limit' period in which it is executing.
1296 --
1297 -- PARAMETERS:
1298 -- sqlset_name (IN)- the SQLSET name
1299 -- time_limit (IN)- the total amount of time, in seconds, to execute
1300 -- repeat_interval (IN)- the amount of time, in seconds, to pause
1301 -- between sampling
1302 -- capture_option (IN)- during capture, either insert new statements,
1303 -- update existing ones, or both. 'INSERT',
1304 -- 'UPDATE', or 'MERGE' just like load_option in
1305 -- load_sqlset
1306 -- capture_mode (IN)- capture mode (UPDATE and MERGE capture options).
1307 -- Possible values:
1308 -- + MODE_REPLACE_OLD_STATS - Replace statistics
1309 -- when the number of executions seen is greater
1310 -- than that stored in the STS
1311 -- + MODE_ACCUMULATE_STATS - Add new values to
1312 -- current values for SQL we already store.
1313 -- Note that this mode detects if a statement
1314 -- has been aged out, so the final value for a
1315 -- statistics will be the sum of the statistics
1316 -- of all cursors that statement existed under.
1317 -- basic_filter (IN)- filter to apply to cursor cache on each sampling
1318 -- (see select_xxx)
1319 -- sqlset_owner (IN)- the owner of the sqlset, or null for current
1323 PROCEDURE capture_cursor_cache_sqlset(
1320 -- schema owner
1321 -- recursive_sql (IN) - filter out the recursive SQL if NO_RECURSIVE_SQL
1322 -----------------------------------------------------------------------------
1324 sqlset_name IN VARCHAR2,
1325 time_limit IN POSITIVE := 1800,
1326 repeat_interval IN POSITIVE := 300,
1327 capture_option IN VARCHAR2 := 'MERGE',
1328 capture_mode IN NUMBER := MODE_REPLACE_OLD_STATS,
1329 basic_filter IN VARCHAR2 := NULL,
1330 sqlset_owner IN VARCHAR2 := NULL,
1331 recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL);
1332
1333 ----------------------------------- update_sqlset ---------------------------
1334 -- NAME:
1335 -- update_sqlset
1336 --
1337 -- DESCRIPTION:
1338 -- This procedure updates selected string fields for a SQL statement
1339 -- in a sqlset.
1340 -- Fields that could be updated are MODULE, ACTION, PARSING_SCHEMA_NAME
1341 -- and OTHER.
1342 --
1343 -- PARAMETERS:
1344 -- sqlset_name (IN) - the SQLSET name
1345 -- sql_id (IN) - identifier of the statement to update
1346 -- attribute_name (IN) - the name of the attribute to modify.
1347 -- attribute_value (IN) - the new value of the attribute
1348 -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1349 -- schema owner
1350 -----------------------------------------------------------------------------
1351 PROCEDURE update_sqlset(
1352 sqlset_name IN VARCHAR2,
1353 sql_id IN VARCHAR2,
1354 attribute_name IN VARCHAR2,
1355 attribute_value IN VARCHAR2 := NULL,
1356 sqlset_owner IN VARCHAR2 := NULL);
1357
1358 ----------------------------------- update_sqlset ---------------------------
1359 PROCEDURE update_sqlset(
1360 sqlset_name IN VARCHAR2,
1361 sql_id IN VARCHAR2,
1362 plan_hash_value IN NUMBER,
1363 attribute_name IN VARCHAR2,
1364 attribute_value IN VARCHAR2 := NULL,
1365 sqlset_owner IN VARCHAR2 := NULL);
1366
1367 ----------------------------------- update_sqlset ---------------------------
1368 -- NAME:
1369 -- update_sqlset
1370 --
1371 -- DESCRIPTION:
1372 -- This is an overloaded procedure of the previous one. It is provided
1373 -- to be able to set numerical attributes of a SQL in a sqlset.
1374 -- The only NUMBER attribute that could be updated is PRIORITY.
1375 -- If the statement has more than one plan (i.e., multiple plans with an
1376 -- entry for every different plan_hash_value in plan table),
1377 -- the attribute value will be then changed (replaced) for all plan
1378 -- entries of the statement using the same (new) value.
1379 -- To update the attribute value for a particular plan use the other
1380 -- version of this procedure that, besides sql_id, it takes
1381 -- a plan_hash_value as an argument.
1382 --
1383 -- PARAMETERS:
1384 -- sqlset_name (IN) - the sqlset name
1385 -- sql_id (IN) - identifier of the statement to update
1386 -- plan_hash_value (IN) - plan hash value of a particular plan of
1387 -- the SQL
1388 -- attribute_name (IN) - the name of the attribute to modify.
1389 -- attribute_value (IN) - the new value of the attribute
1390 -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1391 -- schema owner
1392 -----------------------------------------------------------------------------
1393 PROCEDURE update_sqlset(
1394 sqlset_name IN VARCHAR2,
1395 sql_id IN VARCHAR2,
1396 attribute_name IN VARCHAR2,
1397 attribute_value IN NUMBER := NULL,
1398 sqlset_owner IN VARCHAR2 := NULL);
1399
1400 ----------------------------------- update_sqlset ---------------------------
1401 PROCEDURE update_sqlset(
1402 sqlset_name IN VARCHAR2,
1403 sql_id IN VARCHAR2,
1404 plan_hash_value IN NUMBER,
1405 attribute_name IN VARCHAR2,
1406 attribute_value IN NUMBER := NULL,
1407 sqlset_owner IN VARCHAR2 := NULL);
1408
1409 ------------------------------ add_sqlset_reference -------------------------
1410 -- NAME:
1411 -- add_sqlset_reference
1412 --
1413 -- DESCRIPTION:
1414 -- This function adds a new reference to an existing sqlset
1415 -- to indicate its use by a client.
1416 --
1417 -- PARAMETERS:
1418 -- sqlset_name (IN) - the sqlset name.
1419 -- description (IN) - description of the usage of sqlset.
1420 -- sqlset_owner (IN) - the owner of the sqlset, or null for current schema
1421 -- owner
1422 --
1423 -- RETURN:
1424 -- The identifier of the added reference.
1425 -----------------------------------------------------------------------------
1426 FUNCTION add_sqlset_reference(
1427 sqlset_name IN VARCHAR2,
1428 description IN VARCHAR2 := NULL,
1429 sqlset_owner IN VARCHAR2 := NULL)
1430 RETURN NUMBER;
1431
1432 ------------------------------ remove_sqlset_reference ----------------------
1433 -- NAME:
1434 -- remove_sqlset_reference
1435 --
1436 -- DESCRIPTION:
1437 -- This procedure is used to deactivate a sqlset to indicate it
1438 -- is no longer used by the client.
1439 --
1440 -- PARAMETERS:
1441 -- name (IN) - the SQLSET name
1445 -----------------------------------------------------------------------------
1442 -- reference_id (IN) - the identifier of the reference to remove.
1443 -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1444 -- schema owner
1446 PROCEDURE remove_sqlset_reference(
1447 sqlset_name IN VARCHAR2,
1448 reference_id IN NUMBER,
1449 sqlset_owner IN VARCHAR2 := NULL);
1450
1451 ----------------------------------- select_sqlset ---------------------------
1452 -- NAME:
1453 -- select_sqlset
1454 --
1455 -- DESCRIPTION:
1456 -- This is a table function to read sql tuning set content.
1457 --
1458 -- PARAMETERS:
1459 -- sqlset_name (IN) - sqlset name to select from
1460 -- basic_filter (IN) - SQL predicate to filter the SQL statements
1461 -- from the specified sqlset
1462 -- object_filter (IN) - objects that should exist in the object list
1463 -- of selected SQL. Currently not supported.
1464 -- ranking_measure(i) (IN) - an order-by clause on the selected SQL
1465 -- result_percentage (IN) - a percentage on the sum of a ranking measure
1466 -- result_limit (IN) - top L(imit) SQL from the (filtered) source
1467 -- ranked by the ranking measure
1468 -- attribute_list (IN) - list of SQL statement attributes to return
1469 -- in the result.
1470 -- The possible values are:
1471 -- + BASIC: all attributes are
1472 -- returned except the plans and the object
1473 -- references. i.e., execution statistics
1474 -- and binds. The execution context is
1475 -- always part of the result.
1476 -- + TYPICAL (default): BASIC + SQL plan
1477 -- (without row source statistics) and
1478 -- without object reference list.
1479 -- + ALL: return all attributes
1480 -- + Comma separated list of attribute names:
1481 -- this allows to return only a subset of
1482 -- SQL attributes:
1483 -- EXECUTION_STATISTICS,
1484 -- SQL_BINDS,
1485 -- SQL_PLAN,
1486 -- SQL_PLAN_STATISTICS: similar to
1487 -- SQL_PLAN + row source statistics.
1488 -- plan_filter (IN) - plan filter. It is applicable in case there
1489 -- are multiple plans (plan_hash_value)
1490 -- associated to the same statement. This filter
1491 -- allows selecting one plan (plan_hash_value)
1492 -- only. Possible values are:
1493 -- + LAST_GENERATED: plan with most recent
1494 -- timestamp.
1495 -- + FIRST_GENERATED: opposite to LAST_GENERATED
1496 -- + LAST_LOADED: plan with most recent
1497 -- first_load_time stat info.
1498 -- + FIRST_LOADED: opposite to LAST_LOADED
1499 -- + MAX_ELAPSED_TIME: plan with max elapsed
1500 -- time
1501 -- + MAX_BUFFER_GETS: plan with max buffer gets
1502 -- + MAX_DISK_READS: plan with max disk reads
1503 -- + MAX_DIRECT_WRITES: plan with max direct
1504 -- writes
1505 -- + MAX_OPTIMIZER_COST: plan with max opt. cost
1506 -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1507 -- schema owner
1508 -- recursive_sql (IN) - filter out the recursive SQL
1509 -- if NO_RECURSIVE_SQL
1510 -- RETURN:
1511 -- This function returns a sqlset object.
1512 -----------------------------------------------------------------------------
1513 FUNCTION select_sqlset(
1514 sqlset_name IN VARCHAR2,
1515 basic_filter IN VARCHAR2 := NULL,
1516 object_filter IN VARCHAR2 := NULL,
1517 ranking_measure1 IN VARCHAR2 := NULL,
1518 ranking_measure2 IN VARCHAR2 := NULL,
1519 ranking_measure3 IN VARCHAR2 := NULL,
1520 result_percentage IN NUMBER := 1,
1521 result_limit IN NUMBER := NULL,
1522 attribute_list IN VARCHAR2 := 'TYPICAL',
1523 plan_filter IN VARCHAR2 := NULL,
1524 sqlset_owner IN VARCHAR2 := NULL,
1525 recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL)
1526 RETURN sys.sqlset PIPELINED;
1527
1528 ---------------------------- select_cursor_cache ----------------------------
1529 -- NAME:
1530 -- select_cursor_cache
1531 --
1532 -- DESCRIPTION:
1533 -- This function is provided to be able to collect SQL statements from
1534 -- the Cursor Cache.
1535 --
1536 -- PARAMETERS:
1537 -- basic_filter (IN) - SQL predicate to filter the SQL from the
1538 -- cursor cache.
1539 -- object_filter (IN) - specifies the objects that should exist in
1543 -- result_percentage (IN) - a percentage on the sum of a rank measure.
1540 -- the object list of selected SQL from the
1541 -- cursor cache. Currently not supported.
1542 -- ranking_measure(i) (IN) - an order-by clause on the selected SQL.
1544 -- result_limit (IN) - top L(imit) SQL from the (filtered) source
1545 -- ranked by the ranking measure.
1546 -- attribute_list (IN) - list of SQL statement attributes to return
1547 -- in the result.
1548 -- The possible values are:
1549 -- + BASIC: all attributes are
1550 -- returned except the plans and the object
1551 -- references. i.e., execution statistics
1552 -- and binds. The execution context is
1553 -- always part of the result.
1554 -- + TYPICAL (default): BASIC + SQL plan
1555 -- (without row source statistics) and
1556 -- without object reference list.
1557 -- + ALL: return all attributes
1558 -- + Comma separated list of attribute names:
1559 -- this allows to return only a subset of
1560 -- SQL attributes:
1561 -- EXECUTION_STATISTICS,
1562 -- SQL_BINDS,
1563 -- SQL_PLAN,
1564 -- SQL_PLAN_STATISTICS: similar
1565 -- to SQL_PLAN + row source statistics
1566 --
1567 -- recursive_sql (IN) - filter out the recursive SQL
1568 -- if NO_RECURSIVE_SQL
1569 -- RETURN:
1570 -- This function returns a sqlset object.
1571 -----------------------------------------------------------------------------
1572 FUNCTION select_cursor_cache(
1573 basic_filter IN VARCHAR2 := NULL,
1574 object_filter IN VARCHAR2 := NULL,
1575 ranking_measure1 IN VARCHAR2 := NULL,
1576 ranking_measure2 IN VARCHAR2 := NULL,
1577 ranking_measure3 IN VARCHAR2 := NULL,
1578 result_percentage IN NUMBER := 1,
1579 result_limit IN NUMBER := NULL,
1580 attribute_list IN VARCHAR2 := 'TYPICAL',
1581 recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL)
1582 RETURN sys.sqlset PIPELINED;
1583
1584 ------------------------- select_workload_repository ------------------------
1585 -- NAME:
1586 -- select_workload_repository
1587 --
1588 -- DESCRIPTION:
1589 -- This function is provided to be able to collect SQL statements from
1590 -- the workload repository. It is used to collect SQL statements from all
1591 -- snapshots between begin_snap and and end_snap or from a specified
1592 -- baseline.
1593 --
1594 -- PARAMETERS:
1595 -- begin_snap (IN) - begin snapshot
1596 -- end_snap (IN) - end snapshot
1597 -- baseline_name (IN) - the name of the baseline period.
1598 -- basic_filter (IN) - SQL predicate to filter the SQL from AWR.
1599 -- object_filter (IN) - specifies the objects that should exist in
1600 -- the object list of selected SQL from AWR.
1601 -- Currently not supported.
1602 -- ranking_measure(i) (IN) - an order-by clause on the selected SQL.
1603 -- result_percentage (IN) - a percentage on the sum of a rank measure.
1604 -- result_limit (IN) - top L(imit) SQL from the (filtered) source
1605 -- ranked by the ranking measure.
1606 -- attribute_list (IN) - list of SQL statement attributes to return
1607 -- in the result.
1608 -- The possible values are:
1609 -- + BASIC: all attributes are
1610 -- returned except the plans and the object
1611 -- references. i.e., execution statistics
1612 -- and binds. The execution context is
1613 -- always part of the result.
1614 -- + TYPICAL (default): BASIC + SQL plan
1615 -- (without row source statistics) and
1616 -- without object reference list.
1617 -- + ALL: return all attributes
1618 -- + Comma separated list of attribute names:
1619 -- this allows to return only a subset of
1620 -- SQL attributes:
1621 -- EXECUTION_STATISTICS,
1622 -- SQL_BINDS,
1623 -- SQL_PLAN,
1624 -- SQL_PLAN_STATISTICS: similar
1625 -- to SQL_PLAN + row source statistics
1626 -- recursive_sql (IN) - filter out the recursive SQL
1627 -- if NO_RECURSIVE_SQL
1628 -- RETURN:
1629 -- This function returns a sqlset object.
1630 -----------------------------------------------------------------------------
1631 FUNCTION select_workload_repository(
1632 begin_snap IN NUMBER,
1633 end_snap IN NUMBER,
1637 ranking_measure2 IN VARCHAR2 := NULL,
1634 basic_filter IN VARCHAR2 := NULL,
1635 object_filter IN VARCHAR2 := NULL,
1636 ranking_measure1 IN VARCHAR2 := NULL,
1638 ranking_measure3 IN VARCHAR2 := NULL,
1639 result_percentage IN NUMBER := 1,
1640 result_limit IN NUMBER := NULL,
1641 attribute_list IN VARCHAR2 := 'TYPICAL',
1642 recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL)
1643 RETURN sys.sqlset PIPELINED;
1644
1645 -------------------------- select_workload_repository -----------------------
1646 FUNCTION select_workload_repository(
1647 baseline_name IN VARCHAR2,
1648 basic_filter IN VARCHAR2 := NULL,
1649 object_filter IN VARCHAR2 := NULL,
1650 ranking_measure1 IN VARCHAR2 := NULL,
1651 ranking_measure2 IN VARCHAR2 := NULL,
1652 ranking_measure3 IN VARCHAR2 := NULL,
1653 result_percentage IN NUMBER := 1,
1654 result_limit IN NUMBER := NULL,
1655 attribute_list IN VARCHAR2 := 'TYPICAL',
1656 recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL)
1657 RETURN sys.sqlset PIPELINED;
1658
1659 ------------------------------ select_sql_trace -----------------------------
1660 -- NAME:
1661 -- select_sql_trace
1662 --
1663 -- DESCRIPTION:
1664 -- This table function reads the content of one or more trace
1665 -- files and returns the sql statements it finds in the format
1666 -- of sqlset_row.
1667 --
1668 -- PARAMETERS:
1669 -- directory (IN) - directory/location/path of the trace file(s).
1670 -- This field is mandatory.
1671 -- file_name (IN) - all or part of name of the trace file(s)
1672 -- to process. If NULL then the current or most
1673 -- recent file in the specified localtion/path
1674 -- will be used. '%' wildcards are supported for
1675 -- matching trace file names.
1676 -- mapping_table_name
1677 -- (IN) - the mapping table name. Note that
1678 -- the mapping table name is case insensitive.
1679 -- If the mapping table name is NULL, the mappings
1680 -- in the current database will be used.
1681 -- mapping_table_owner
1682 -- (IN) - the mapping table owner. If it is NULL, the
1683 -- current user will be used.
1684 -- select_mode (IN) - It is the mode for selecting sqls from the trace.
1685 -- SINGLE_EXECUTION: return one execution of a SQL.
1686 -- It is the default.
1687 -- ALL_EXECUTIONS: return all executions.
1688 --
1689 -- options (IN) - the options.
1690 -- LIMITED_COMMAND_TYPE: we only return the sqls
1691 -- with the command types: CREATE, INSERT, SELECT,
1692 -- UPDATE, DELETE, UPSERT. It is the default.
1693 -- ALL_COMMAND_TYPE: return the sqls with all
1694 -- command type.
1695 -- pattern_start (IN) - opening delimiting pattern of the trace file
1696 -- section(s) to consider. NOT USED FOR NOW.
1697 -- pattern_end (IN) - closing delimiting pattern of the trace file
1698 -- section(s) to process. NOT USED FOR NOW.
1699 -- result_limit (IN) - top SQL from the (filtered) source. Default
1700 -- to MAXSB4 if NULL;
1701 --
1702 -- return:
1703 -- This function returns a sqlset_row object.
1704 --
1705 ------------------------------------------------------------------------
1706 -- EXAMPLE: LOAD SQLs from SQL TRACE INTO STS and convert it into trial
1707 ------------------------------------------------------------------------
1708 -- /* turn on the SQL trace in the capture database */
1709 -- alter session set events '10046 trace name context forever, level 4'
1710 --
1711 -- /* create mapping table from the capture database */
1712 -- create table mapping as
1713 -- select object_id id, owner, substr(object_name, 1, 30) name
1714 -- from dba_objects
1715 -- where object_type NOT IN ('CONSUMER GROUP', 'EVALUATION CONTEXT',
1716 -- 'FUNCTION', 'INDEXTYPE', 'JAVA CLASS',
1717 -- 'JAVA DATA', 'JAVA RESOURCE', 'LIBRARY',
1718 -- 'LOB', 'OPERATOR', 'PACKAGE',
1719 -- 'PACKAGE BODY', 'PROCEDURE', 'QUEUE',
1720 -- 'RESOURCE PLAN', 'TRIGGER', 'TYPE',
1721 -- 'TYPE BODY', 'SYNONYM')
1722 -- union all
1723 -- select user_id id, username owner, null name
1724 -- from dba_users;
1725 --
1726 -- /* create the STS on the database running the SPA */
1727 -- dbms_sqltune.create_sqlset('my_sts', 'test purpose');
1728 --
1729 -- /* load the sqls into STS from SQL TRACE */
1730 -- DECLARE
1731 -- cur sys_refcursor;
1732 -- BEGIN
1733 -- OPEN cur for
1734 -- select value(p)
1735 -- from TABLE(
1736 -- dbms_sqltune.select_sql_trace(
1737 -- directory=>'SQL_TRACE_DIR',
1738 -- file_name=>'%trc',
1739 -- mapping_table_name=>'mapping')) p;
1740 -- dbms_sqltune.load_sqlset('my_sts', cur);
1741 -- END;
1742 -- /
1743 --
1744 -- /* create a trial from the STS */
1748 -- exec dbms_sqlpa.execute_analysis_task(task_name =>:aname,
1745 -- var aname varchar2(30)
1746 -- exec :aname := dbms_sqlpa.create_analysis_task(
1747 -- sqlset_name => 'my_sts');
1749 -- execution_type => 'convert sqlset');
1750 -----------------------------------------------------------------------------
1751 FUNCTION select_sql_trace(
1752 directory IN VARCHAR2,
1753 file_name IN VARCHAR2 := NULL,
1754 mapping_table_name IN VARCHAR2 := NULL,
1755 mapping_table_owner IN VARCHAR2 := NULL,
1756 select_mode IN POSITIVE := SINGLE_EXECUTION,
1757 options IN BINARY_INTEGER := LIMITED_COMMAND_TYPE,
1758 pattern_start IN VARCHAR2 := NULL,
1759 pattern_end IN VARCHAR2 := NULL,
1760 result_limit IN POSITIVE := NULL)
1761 RETURN sys.sqlset PIPELINED;
1762
1763 ----------------------------- select_sqlpa_task -----------------------------
1764 -- NAME:
1765 -- select_sqlpa_task
1766 --
1767 -- DESCRIPTION:
1768 -- This function is provided to be able to collect SQL statements from
1769 -- a SQL performance analyzer task. One example usage is for creating
1770 -- a SQL Tuning Set containing the subset of SQL statements that
1771 -- regressed during a SQL Performance Analyzer (SPA) experiment.
1772 -- Other arbitrary filters can also be specified.
1773 --
1774 -- PARAMETERS:
1775 -- task_name (IN) - name of the SQL Performance Analyzer task
1776 -- task_owner (IN) - owner of the SQL Performance Analyzer task.
1777 -- If NULL, then assume the current user.
1778 -- execution_name (IN) - name of the SQL Performance Analyzer task
1779 -- execution (type COMPARE PERFORMANCE) from
1780 -- which the change_filter will be applied.
1781 -- If NULL, then assume the most recent
1782 -- COMPARE PERFORMANCE execution.
1783 -- level_filter (IN) - filter to specify which subset of SQLs
1784 -- to include. Same format as DBMS_SQLPA.
1785 -- REPORT_ANALYSIS_TASK.LEVEL, with some
1786 -- possible strings removed.
1787 -- IMPROVED - improved SQL
1788 -- REGRESSED (default) - regressed SQL
1789 -- CHANGED - SQL w/ changed perf
1790 -- UNCHANGED - SQL w/ unchanged perf
1791 -- CHANGED_PLANS - SQL w/ plan changes
1792 -- UNCHANGED_PLANS - SQL w/ unchanged plans
1793 -- ERRORS - SQL with errors only
1794 -- MISSING_SQL - Missing SQLs (Across STS)
1795 -- NEW_SQL - New SQLs (Across STS)
1796 -- basic_filter (IN) - SQL predicate to filter the SQL in
1797 -- addition to the filters above.
1798 -- object_filter (IN) - specifies the objects that should exist in
1799 -- the object list of selected SQL from the
1800 -- cursor cache. Currently not supported.
1801 -- attribute_list (IN) - list of SQL statement attributes to return
1802 -- in the result.
1803 -- The possible values are:
1804 -- + BASIC: all attributes are
1805 -- returned except the plans and the object
1806 -- references. i.e., execution statistics
1807 -- and binds. The execution context is
1808 -- always part of the result.
1809 -- + TYPICAL (default): BASIC + SQL plan
1810 -- (without row source statistics) and
1811 -- without object reference list.
1812 -- + ALL: return all attributes
1813 -- + Comma separated list of attribute names:
1814 -- this allows to return only a subset of
1815 -- SQL attributes:
1816 -- EXECUTION_STATISTICS,
1817 -- SQL_BINDS,
1818 -- SQL_PLAN,
1819 -- SQL_PLAN_STATISTICS: similar
1820 -- to SQL_PLAN + row source statistics
1821 --
1822 -- RETURN:
1823 -- This function returns a sqlset object.
1824 -----------------------------------------------------------------------------
1825 FUNCTION select_sqlpa_task(
1826 task_name IN VARCHAR2,
1827 task_owner IN VARCHAR2 := NULL,
1828 execution_name IN VARCHAR2 := NULL,
1829 level_filter IN VARCHAR2 := 'REGRESSED',
1830 basic_filter IN VARCHAR2 := NULL,
1831 object_filter IN VARCHAR2 := NULL,
1832 attribute_list IN VARCHAR2 := 'TYPICAL')
1833 RETURN sys.sqlset PIPELINED;
1834
1835 -----------------------------------------------------------------------------
1836 -- Pack / Unpack SQL tuning set procedures and functions --
1837 -- --
1841 -- database link, etc), and then import them into the SQL tuning set --
1838 -- SQL tuning sets can be moved ("packed") from their location on a system --
1839 -- into an opaque table in any user schema. You can then move that table --
1840 -- to another system using the method of your choice (expdp/impdp, --
1842 -- schema on the new system ("unpack"). --
1843 -- --
1844 -----------------------------------------------------------------------------
1845 ---------------------------------
1846 -- EXAMPLE: PACK/UNPACK TWO STS --
1847 ---------------------------------
1848 -- /* Create a staging table to move to */
1849 -- dbms_sqltune.create_stgtab_sqlset(table_name => 'STAGING_TABLE');
1850 --
1851 -- /* Put two STS in the staging table */
1852 -- dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 'my_sts',
1853 -- staging_table_name => 'STAGING_TABLE');
1854 -- dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 'full_app_workload',
1855 -- staging_table_name => 'STAGING_TABLE');
1856 --
1857 -- /* transport STS_STAGING_TABLE to foreign system */
1858 -- ...
1859 --
1860 -- /* On new system, unpack both from staging table */
1861 -- dbms_sqltune.unpack_stgtab_sqlset(sqlset_name => '%',
1862 -- replace => TRUE,
1863 -- staging_table_name => 'STAGING_TABLE');
1864 --
1865 -----------------------------------------------------------------------------
1866
1867 ------------------------------- create_stgtab_sqlset ------------------------
1868 -- NAME:
1869 -- create_stgtab_sqlset
1870 --
1871 -- DESCRIPTION:
1872 -- This procedure creates a staging table to be used by the pack
1873 -- procedure. Call it once before issuing a pack call. It can
1874 -- be called on multiple schemas if you would like to have different
1875 -- tuning sets in different staging tables.
1876 --
1877 -- Note that this is a DDL operation, so it does not occur within a
1878 -- transaction. Users issuing the call must have permission to create
1879 -- a table in the schema provided.
1880 --
1881 -- PARAMETERS:
1882 -- table_name (IN) - name of table to create (case-sensitive)
1883 -- schema_name (IN - user schema to create table within, or
1884 -- NULL for current schema owner
1885 -- (case-sensitive)
1886 -- tablespace_name (IN) - tablespace to store the staging table in,
1887 -- or NULL for schema's default tablespace
1888 -- (case-sensitive)
1889 -- db_version (IN) - database version to decide the format of
1890 -- the staging table. It is possible to
1891 -- create an older DB version staging table
1892 -- so that an STS can be exported to an
1893 -- older DB version.
1894 -- It can take one of the following values:
1895 -- NULL (default) : current DB
1896 -- version
1897 -- STS_STGTAB_10_2_VERSION : 10.2 DB version
1898 -- STS_STGTAB_11_1_VERSION : 11.1 DB version
1899 -- STS_STGTAB_11_2_VERSION : 11.2 DB version
1900 -----------------------------------------------------------------------------
1901 PROCEDURE create_stgtab_sqlset(
1902 table_name IN VARCHAR2,
1903 schema_name IN VARCHAR2 := NULL,
1904 tablespace_name IN VARCHAR2 := NULL,
1905 db_version IN NUMBER := NULL);
1906
1907 ----------------------------- pack_stgtab_sqlset ----------------------------
1908 -- NAME:
1909 -- pack_stgtab_sqlset
1910 --
1911 -- DESCRIPTION:
1912 -- This function moves one or more STS from their location in the SYS
1913 -- schema to a staging table created by the create_stgtab_sqlset fct.
1914 -- It can be called several times to move more than one STS. Users can
1915 -- then move the populated staging table to another system using any
1916 -- method of their choice, such as database link or datapump (expdp/
1917 -- impdp functions). Users can then call unpack_stgtab_sqlset to create
1918 -- the STS on the other system.
1919 --
1920 -- Note that this fct commits after packing each STS, so if it raises
1921 -- an error mid-execution, some STS may already be in the staging table.
1922 --
1923 -- PARAMETERS:
1924 -- sqlset_name (IN) - name of STS to pack (not NULL).
1925 -- Wildcard characters ('%') are supported
1926 -- to move multiple STS in a single call.
1927 -- sqlset_owner (IN) - name of STS owner, or NULL for current
1928 -- schema owner. Wildcard characters ('%')
1929 -- are supported to pack STS from multiple
1930 -- owners in one call.
1931 -- staging_table_name (IN) - name of staging table, created by
1932 -- create_stgtab_sqlset (case-sensitive)
1933 -- staging_schema_owner (IN) - name of staging table owner, or NULL for
1937 -- pack an STS to an older DB version
1934 -- current schema owner (case-sensitive)
1935 -- db_version (IN) - database version to decide the format of
1936 -- the staging table. It is possible to
1938 -- staging table so that it can be exported
1939 -- to an that version.
1940 -- It can take one of the following values:
1941 -- NULL (default) : current DB
1942 -- version
1943 -- STS_STGTAB_10_2_VERSION : 10.2 DB version
1944 -- STS_STGTAB_11_1_VERSION : 11.1 DB version
1945 -- STS_STGTAB_11_2_VERSION : 11.2 DB version
1946 -----------------------------------------------------------------------------
1947 PROCEDURE pack_stgtab_sqlset(
1948 sqlset_name IN VARCHAR2,
1949 sqlset_owner IN VARCHAR2 := NULL,
1950 staging_table_name IN VARCHAR2,
1951 staging_schema_owner IN VARCHAR2 := NULL,
1952 db_version IN NUMBER := NULL);
1953
1954 --------------------------- unpack_stgtab_sqlset ----------------------------
1955 -- NAME:
1956 -- unpack_stgtab_sqlset
1957 --
1958 -- DESCRIPTION:
1959 -- Moves one or more STS from the staging table, as populated by a call
1960 -- to pack_stgtab_sqlset and moved by the user, into the STS schema,
1961 -- making them proper STS. Users can drop the staging table after this
1962 -- procedure completes successfully.
1963 --
1964 -- The unpack procedure commits after successfully loading each STS. If
1965 -- it fails with one, no part of that STS will have been unpacked, but
1966 -- those which it saw previously will exist. When failures occur due to
1967 -- sts name or owner conflicts, users should use the remap_stgtab_sqlset
1968 -- function to patch the staging table, and then call this procedure
1969 -- again to unpack those STS that remain.
1970 --
1971 -- PARAMETERS:
1972 -- sqlset_name (IN) - name of STS to unpack (not NULL).
1973 -- Wildcard characters ('%') are supported
1974 -- to unpack multiple STS in a single call.
1975 -- for example, just specify '%' to unpack
1976 -- all STS from the staging table.
1977 -- sqlset_owner (IN) - name of STS owner, or NULL for current
1978 -- schema owner. Wildcards supported
1979 -- replace (IN) - replace STS if they already exist.
1980 -- If FALSE, function errors when trying to
1981 -- unpack an existing STS
1982 -- staging_table_name (IN) - name of staging table, moved after a call
1983 -- to pack_stgtab_sqlset (case-sensitive)
1984 -- staging_schema_owner (IN) - name of staging table owner, or NULL for
1985 -- current schema owner (case-sensitive)
1986 -----------------------------------------------------------------------------
1987 PROCEDURE unpack_stgtab_sqlset(
1988 sqlset_name IN VARCHAR2 := '%',
1989 sqlset_owner IN VARCHAR2 := NULL,
1990 replace IN BOOLEAN,
1991 staging_table_name IN VARCHAR2,
1992 staging_schema_owner IN VARCHAR2 := NULL);
1993
1994 ------------------------------- remap_stgtab_sqlset -------------------------
1995 -- NAME:
1996 -- remap_stgtab_sqlset
1997 --
1998 -- DESCRIPTION:
1999 -- Changes the sqlset names and owners in the staging table so that they
2000 -- can be unpacked with different values than they had on the host
2001 -- system.
2002 -- Users should first check to see if the names they are changing to will
2003 -- conflict first -- this function does not enforce that constraint.
2004 --
2005 -- Users can call this procedure multiple times to remap more than one
2006 -- STS name/owner. Note that this procedure only handles one STS per
2007 -- call.
2008 --
2009 -- PARAMETERS:
2010 -- old_sqlset_name (IN) - name of STS to target for a name/owner
2011 -- remap. Wildcards are NOT supported.
2012 -- old_sqlset_owner (IN) - name of STS owner to target for a
2013 -- remap. NULL for current schema owner.
2014 -- new_sqlset_name (IN) - new name for STS. NULL to keep the same
2015 -- name.
2016 -- new_sqlset_owner (IN) - new owner name for STS. NULL to keep the
2017 -- same owner name.
2018 -- staging_table_name (IN) - name of staging table (case-sensitive)
2019 -- staging_schema_owner (IN) - name of staging table owner, or NULL for
2020 -- current schema owner (case-sensitive)
2021 -- old_con_dbid (IN) - old container db id to target for a
2022 -- remap. NULL to keep the same.
2023 -- new_con_dbid (IN) - new container db id to replace with.
2024 -- NULL to keep the same.
2025 -----------------------------------------------------------------------------
2026 PROCEDURE remap_stgtab_sqlset(
2027 old_sqlset_name IN VARCHAR2,
2028 old_sqlset_owner IN VARCHAR2 := NULL,
2032 staging_schema_owner IN VARCHAR2 := NULL,
2029 new_sqlset_name IN VARCHAR2 := NULL,
2030 new_sqlset_owner IN VARCHAR2 := NULL,
2031 staging_table_name IN VARCHAR2,
2033 old_con_dbid IN NUMBER := NULL,
2034 new_con_dbid IN NUMBER := NULL);
2035
2036 --------------------------- transform_sqlset_cursor -------------------------
2037 -- NAME:
2038 -- transform_sqlset_cursor
2039 --
2040 -- DESCRIPTION:
2041 -- This function transforms a user specified sql tuning set cursor to
2042 -- a table (function) so that the cursor can be queried in SQL query.
2043 -- The function is also used to transform an internal cursor created
2044 -- to contain all statements to be deleted from the sql tuning set using
2045 -- the delete_sqlset API.
2046 --
2047 --
2048 -- PARAMETERS:
2049 -- populate_cursor (IN) - cursor to transform.
2050 -- RETURN:
2051 -- rows of type sqlset_row.
2052 --
2053 -- NOTICE:
2054 -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
2055 -- ! This function exists for internal use and MUST NOT be documented !
2056 -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
2057 ----------------------------------------------------------------------------
2058 FUNCTION transform_sqlset_cursor(
2059 populate_cursor IN sqlset_cursor)
2060 RETURN sys.sqlset PIPELINED;
2061
2062
2063
2064
2065 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
2066 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
2067 -- -------------------------------- --
2068 -- SQL PROFILE PROCEDURES/FUNCTIONS --
2069 -- -------------------------------- --
2070 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
2071 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
2072 -------------------
2073 -- EXPORTED PROCEDURES/FUNCTIONS
2074 --
2075 --
2076
2077 -- SQL PROFILE OVERVIEW
2078 -- SQL profiles are collections of SQL compiler statistics that can
2079 -- be associated to a particular SQL text. During SQL parse
2080 -- (compilation) if a SQL profile is associated with the current
2081 -- SQL statement, the statistics within the profile will be made
2082 -- available to the compiler. Profiles are matched to compiling
2083 -- SQL if the normalized text of the SQL statement matches the
2084 -- normalized SQL text provided at SQL profile creation time. The
2085 -- normalization of the SQL text entails uppercasing all non-literal
2086 -- text and removal of all whitespace. The session
2087 -- performing the compilation must also have the same value for
2088 -- the parameter sqltune_category as the category under which the
2089 -- SQL Profile was created. Category allows multiple profiles to exist
2090 -- for the same SQL statement. It also allows a session to test
2091 -- profiles privately (by working and creating profiles in a unique
2092 -- category namespace).
2093 -- SQL profiles can only be used by certain SQL statement types. These
2094 -- include:
2095 -- SELECT statements
2096 -- UPDATE statements
2097 -- INSERT (but only with a SELECT clause) statements
2098 -- DELETE statements
2099 -- CREATE TABLE (but only with the AS SELECT clause)
2100 -- MERGE statements (the upsert operation)
2101 --
2102 -- Internally executed SQL statements against the data dictionary
2103 -- (referred to as recursive dictionary SQL) will ignore profiles. Also,
2104 -- any SQL executed before the database is open will not be able to
2105 -- lookup and use profiles.
2106 -- SQL profiles and stored outlines are related in that they influence
2107 -- the compilation of SQL. If a stored outline can be used for
2108 -- compiling a SQL statement, then any profiles are ignored. Note
2109 -- that a profile can be used during the SQL compilation that
2110 -- creates a stored outline. For example, if there is a profile on
2111 -- a SQL statement that has the CREATE OUTLINE statement executed for
2112 -- it (and categories match), the profile will be used to determine
2113 -- the plan that will then be saved as the stored outline.
2114 -- A profile's status can be enabled or disabled. A disabled profile
2115 -- will not be used for compiling cursors. When profiles are
2116 -- created/accepted they are enabled. Use the ALTER_SQL_PROFILE procedure
2117 -- to toggle between the enabled and disabled status.
2118 --
2119
2120 -------------------------
2121 -- PROFILE DDL OPERATIONS
2122 -------------------------
2123
2124 -- NAME: accept_sql_profile - accept a sqltune recommended SQL profile,
2125 -- FUNCTION version
2126 -- PURPOSE: This procedure accepts a SQL profile as recommended by the
2127 -- specified SQL tuning task.
2128 -- INPUTS: task_name - (REQUIRED) The name of the SQL tuning task.
2129 -- object_id - The identifier of the advisor framework object
2130 -- representing the SQL statement associated
2131 -- to the tuning task.
2132 -- name - This is the name of the profile. It cannot contain
2133 -- double quotation marks. The name is case sensitive.
2134 -- If not specified, the system will generate a unique
2138 -- category - This is the category name which must match the
2135 -- name for the SQL profile.
2136 -- description - A user specified string describing the purpose
2137 -- of this SQL profile. Max size of description is 500
2139 -- value of parameter SQLTUNE_CATEGORY in a session
2140 -- for the session to use this profile. It defaults
2141 -- to the value "DEFAULT". This is also the default
2142 -- of the SQLTUNE_CATEGORY parameter. The category
2143 -- must be a valid Oracle identifier. The category
2144 -- name specified is always converted to upper case.
2145 -- The combination of the normalized SQL text and
2146 -- category name create a unique key for a profile.
2147 -- An accept will fail if this combination is
2148 -- duplicated.
2149 -- task_owner - Owner of the tuning task. This is an optional
2150 -- parameter that has to be specified to accept
2151 -- a SQL Profile associated to a tuning task owned
2152 -- by another user. The current user is the default
2153 -- value.
2154 -- replace - If the profile already exists, it will be
2155 -- replaced if this argument is TRUE.
2156 -- It is an error to pass a name that is already
2157 -- being used for another signature/category pair,
2158 -- even with replace set to TRUE.
2159 -- force_match - If TRUE this causes SQL Profiles
2160 -- to target all SQL statements which have the same
2161 -- text after normalizing all literal values into
2162 -- bind variables. (Note that if a combination of
2163 -- literal values and bind values is used in a
2164 -- SQL statement, no bind transformation occurs.)
2165 -- This is analogous to the matching algorithm
2166 -- used by the "FORCE" option of the
2167 -- CURSOR_SHARING parameter. If FALSE, literals are
2168 -- not transformed. This is analogous to the
2169 -- matching algorithm used by the "EXACT" option of
2170 -- the CURSOR_SHARING parameter.
2171 -- profile_type - The profile type. If NULL, it means the SQL
2172 -- profile.
2173 --
2174 -- RETURNS: name - The name of the SQL profile.
2175 --
2176 -- REQUIRES: "ADMINISTER SQL MANAGEMENT OBJECT" privilege ("CREATE ANY
2177 -- SQL PROFILE" privilege deprecated)
2178 --
2179 FUNCTION accept_sql_profile(
2180 task_name IN VARCHAR2,
2181 object_id IN NUMBER := NULL,
2182 name IN VARCHAR2 := NULL,
2183 description IN VARCHAR2 := NULL,
2184 category IN VARCHAR2 := NULL,
2185 task_owner IN VARCHAR2 := NULL,
2186 replace IN BOOLEAN := FALSE,
2187 force_match IN BOOLEAN := FALSE,
2188 profile_type IN VARCHAR2 := REGULAR_PROFILE)
2189 RETURN VARCHAR2;
2190
2191 -- NAME: accept_sql_profile - accept a sqltune recommended SQL profile,
2192 -- PROCEDURE version
2193 -- PURPOSE: This procedure accepts a SQL profile as recommended by the
2194 -- specified SQL tuning task.
2195 -- INPUTS: task_name - (REQUIRED) The name of the SQL tuning task.
2196 -- object_id - Identifier of the advisor framework
2197 -- object representing the SQL statement associated
2198 -- to the tuning task.
2199 -- name - This is the name of the profile. It
2200 -- cannot contain double quotation marks. The name is
2201 -- case sensitive.
2202 -- description - A user specified string describing the purpose
2203 -- of this SQL profile. Max size of description is 500
2204 -- category - This is the category name which must match the
2205 -- value of parameter SQLTUNE_CATEGORY in a session
2206 -- for the session to use this profile. It defaults
2207 -- to the value "DEFAULT". This is also the default
2208 -- of the SQLTUNE_CATEGORY parameter. The category
2209 -- must be a valid Oracle identifier. The category
2210 -- name specified is always converted to upper case.
2211 -- The combination of the normalized SQL text and
2212 -- category name create a unique key for a profile.
2213 -- An accept will fail if this combination is
2214 -- duplicated.
2215 -- task_owner - Owner of the tuning task. This is an optional
2216 -- parameter that has to be specified to accept
2217 -- a SQL Profile associated to a tuning task owned
2218 -- by another user. The current user is the default
2219 -- value.
2220 -- replace - If the profile already exists, it will be
2221 -- replaced if this argument is TRUE.
2225 -- force_match - If TRUE this causes SQL Profiles
2222 -- It is an error to pass a name that is already
2223 -- being used for another signature/category pair,
2224 -- even with replace set to TRUE.
2226 -- to target all SQL statements which have the same
2227 -- text after normalizing all literal values into
2228 -- bind variables. (Note that if a combination of
2229 -- literal values and bind values is used in a
2230 -- SQL statement, no bind transformation occurs.)
2231 -- This is analogous to the matching algorithm
2232 -- used by the "FORCE" option of the
2233 -- CURSOR_SHARING parameter. If FALSE, literals are
2234 -- not transformed. This is analogous to the
2235 -- matching algorithm used by the "EXACT" option of
2236 -- the CURSOR_SHARING parameter.
2237 -- profile_type - The profile type. If NULL, it means the SQL
2238 -- profile.
2239 --
2240 -- REQUIRES: "ADMINISTER SQL MANAGEMENT OBJECT" privilege ("CREATE ANY
2241 -- SQL PROFILE" privilege deprecated)
2242 --
2243 PROCEDURE accept_sql_profile(
2244 task_name IN VARCHAR2,
2245 object_id IN NUMBER := NULL,
2246 name IN VARCHAR2 := NULL,
2247 description IN VARCHAR2 := NULL,
2248 category IN VARCHAR2 := NULL,
2249 task_owner IN VARCHAR2 := NULL,
2250 replace IN BOOLEAN := FALSE,
2251 force_match IN BOOLEAN := FALSE,
2252 profile_type IN VARCHAR2 := REGULAR_PROFILE);
2253
2254 --
2255 -- NAME: accept_all_sql_profiles - procedure to accept all sqltune
2256 -- recommended SQL profiles
2257 -- PURPOSE: This procedure accepts all SQL profiles as recommended by the
2258 -- specified SQL tuning task.
2259 -- INPUTS:
2260 -- autotune_period (IN) - auto-sqltune time period. Applies only
2261 -- to automatic sql tuning advisor task.
2262 -- It is ignored if the specified task is not
2263 -- auto-sqltune's.
2264 -- Possible values are for this argument:
2265 -- - NULL or negative value (default): meaning
2266 -- all or full. The result includes all task
2267 -- executions
2268 -- - 0: result of the current/last task execution
2269 -- - 1 (day): last 24 hours results
2270 -- - 7 (days): last week (7 days)
2271 -- - any other value will be interpreted as
2272 -- follows:
2273 -- time of the last task execution
2274 -- MINUS
2275 -- the value of this argument.
2276 -- execution_name (IN) - name of the task execution to use. If NULL,
2277 -- the report will be generated for the last task
2278 -- execution.
2279 --
2280 -- NOTES:
2281 -- for the rest of input, see inputs of accept_sql_profile
2282 --
2283 PROCEDURE accept_all_sql_profiles(
2284 task_name IN VARCHAR2,
2285 category IN VARCHAR2 := NULL,
2286 replace IN BOOLEAN := FALSE,
2287 force_match IN BOOLEAN := FALSE,
2288 profile_type IN VARCHAR2 := REGULAR_PROFILE,
2289 autotune_period IN NUMBER := NULL,
2290 execution_name IN VARCHAR2 := NULL,
2291 task_owner IN VARCHAR2 := NULL,
2292 description IN VARCHAR2 := NULL);
2293
2294
2295 -- NAME: drop_sql_profile - drop a SQL profile
2296 -- PURPOSE: This procedure drops the named SQL profile from the database.
2297 -- INPUTS: name - (REQUIRED)Name of profile to be dropped. The name
2298 -- is case sensitive.
2299 -- ignore - Ignore errors due to object not existing.
2300 -- REQUIRES: "ADMINISTER SQL MANAGEMENT OBJECT" privilege ("DROP ANY
2301 -- SQL PROFILE" privilege deprecated)
2302 --
2303 PROCEDURE drop_sql_profile(
2304 name IN VARCHAR2,
2305 ignore IN BOOLEAN := FALSE);
2306
2307 -- NAME: alter_sql_profile - alter a SQL profile attribute
2308 -- PURPOSE: This procedure alters specific attributes of an existing
2309 -- SQL profile object. The following attributes can be altered
2310 -- (using these attribute names):
2311 -- "STATUS" -> can be set to "ENABLED" or "DISABLED"
2312 -- "NAME" -> can be reset to a valid name (must be
2313 -- a valid Oracle identifier and must be
2314 -- unique).
2315 -- "DESCRIPTION" -> can be set to any string of size no
2316 -- more than 500
2317 -- "CATEGORY" -> can be reset to a valid category name (must
2318 -- be valid Oracle identifier and must be unique
2319 -- when combined with normalized SQL text)
2320 -- INPUTS: name - (REQUIRED)Name of SQL profile to alter. The name
2324 -- See list above for valid attribute names.
2321 -- is case sensitive.
2322 -- attribute_name - (REQUIRED)The attribute name to alter (case
2323 -- insensitive).
2325 -- value - (REQUIRED)The new value of the attribute. See list
2326 -- above for valid attribute values.
2327 -- REQUIRES: "ADMINISTER SQL MANAGEMENT OBJECT" privilege ("ALTER ANY
2328 -- SQL PROFILE" privilege deprecated)
2329 --
2330 PROCEDURE alter_sql_profile(
2331 name IN VARCHAR2,
2332 attribute_name IN VARCHAR2,
2333 value IN VARCHAR2);
2334
2335 -- NAME: import_sql_profile - import a SQL profile
2336 -- PURPOSE: This procedure is only used by import.
2337 -- INPUTS: (see accept_sql_profile)
2338 -- REQUIRES: "ADMINISTER SQL MANAGEMENT OBJECT" privilege ("CREATE ANY
2339 -- SQL PROFILE" privilege deprecated)
2340 --
2341 PROCEDURE import_sql_profile(
2342 sql_text IN CLOB,
2343 profile IN sqlprof_attr,
2344 name IN VARCHAR2 := NULL,
2345 description IN VARCHAR2 := NULL,
2346 category IN VARCHAR2 := NULL,
2347 validate IN BOOLEAN := TRUE,
2348 replace IN BOOLEAN := FALSE,
2349 force_match IN BOOLEAN := FALSE);
2350
2351 PROCEDURE import_sql_profile(
2352 sql_text IN CLOB,
2353 profile_xml IN CLOB,
2354 name IN VARCHAR2 := NULL,
2355 description IN VARCHAR2 := NULL,
2356 category IN VARCHAR2 := NULL,
2357 validate IN BOOLEAN := TRUE,
2358 replace IN BOOLEAN := FALSE,
2359 force_match IN BOOLEAN := FALSE);
2360
2361 -- NAME: sqltext_to_signature - sql text to its signature
2362 -- PURPOSE: This function returns a sql text's signature.
2363 -- The signature can be used to identify sql text in dba_sql_profiles.
2364 -- INPUTS: sql_text - (REQUIRED) sql text whose signature is required
2365 -- force_match - If TRUE this causes SQL Profiles
2366 -- to target all SQL statements which have the same
2367 -- text after normalizing all literal values into
2368 -- bind variables. (Note that if a combination of
2369 -- literal values and bind values is used in a
2370 -- SQL statement, no bind transformation occurs.)
2371 -- This is analogous to the matching algorithm
2372 -- used by the "FORCE" option of the
2373 -- CURSOR_SHARING parameter. If FALSE (the default),
2374 -- literals are not transformed. This is analogous to
2375 -- the matching algorithm used by the "EXACT" option
2376 -- of the CURSOR_SHARING parameter.
2377 -- RETURNS: the signature of the specified sql text
2378 -- REQUIRES:
2379 --
2380 FUNCTION sqltext_to_signature(sql_text IN CLOB,
2381 force_match IN BOOLEAN := FALSE)
2382 RETURN NUMBER;
2383
2384 -- NAME: sqltext_to_signature - sql text to its signature
2385 -- PURPOSE: This function returns a sql text's signature.
2386 -- The signature can be used to identify sql text in dba_sql_profiles.
2387 -- INPUTS: sql_text - (REQUIRED) sql text whose signature is required
2388 -- force_match - If 1, this causes SQL Profiles
2389 -- to target all SQL statements which have the same
2390 -- text after normalizing all literal values into
2391 -- bind variables. (Note that if a combination of
2392 -- literal values and bind values is used in a
2393 -- SQL statement, no bind transformation occurs.)
2394 -- This is analogous to the matching algorithm
2395 -- used by the "FORCE" option of the
2396 -- CURSOR_SHARING parameter. If 0, the default
2397 -- literals are not transformed. This is analogous to
2398 -- the matching algorithm used by the "EXACT" option
2399 -- of the CURSOR_SHARING parameter.
2400 -- RETURNS: the signature of the specified sql text
2401 -- COMMENTS: To enable calling from sql so that integer can be passed
2402 -- 0 is FALSE rest is TRUE
2403 -- REQUIRES:
2404 --
2405 FUNCTION sqltext_to_signature(sql_text IN CLOB,
2406 force_match IN BINARY_INTEGER)
2407 RETURN NUMBER;
2408
2409 -------------------------
2410 -- PROFILE PACK/UNPACK
2411 -------------------------
2412 -- Profiles can be exported out of one system and imported into another
2413 -- by means of a staging table, provided by procedures in this package. Like
2414 -- with SQL tuning sets, the operation of inserting into the staging table
2415 -- is called a "pack", and the operation of creating profiles from staging
2416 -- table data is the "unpack".
2417 -- DBAs should perform a pack/unpack as follows:
2418 --
2419 -- 1) Create a staging table through a call to create_stgtab_sqlprof
2420 -- 2) Call pack_stgtab_sqlprof one or more times to write SQL profile
2424 -- 4) Call unpack_stgtab_sqlprof to create sql profiles on the new system
2421 -- data into the staging table
2422 -- 3) Move the staging table through the means of choice (e.g. datapump,
2423 -- database link, etc)
2425 -- from the profile data in the staging table
2426 --
2427 --
2428 -- EXAMPLES:
2429 --
2430 -- 1) Create a staging table owned by user 'SCOTT':
2431 -- exec dbms_sqltune.create_stgtab_sqlprof(table_name => 'STAGING_TABLE',
2432 -- schema_name => 'SCOTT');
2433 -- 2) Copy data for all SQL profiles in the DEFAULT category into a staging
2434 -- table owned by the current schema owner.
2435 -- exec dbms_sqltune.pack_stgtab_sqlprof(
2436 -- staging_table_name => 'STAGING_TABLE');
2437 -- 3) Copy data for sql profile SP_FIND_EMPLOYEE only into a staging table
2438 -- owned by the current schema owner.
2439 -- exec dbms_sqltune.pack_stgtab_sqlprof(
2440 -- profile_name => 'SP_FIND_EMPLOYEE',
2441 -- staging_table_name => 'STAGING_TABLE');
2442 -- 4) Change the name in the data for the SP_FIND_EMPLOYEE profile stored
2443 -- in the staging table to 'SP_FIND_EMP_PROD':
2444 -- exec dbms_sqltune.remap_stgtab_sqlprof(
2445 -- old_profile_name => 'SP_FIND_EMPLOYEE',
2446 -- new_profile_name => 'SP_FIND_EMP_PROD',
2447 -- staging_table_name => 'STAGING_TABLE');
2448 -- 5) Create profiles for all the data stored in the staging table,
2449 -- replacing those that already exist
2450 -- exec dbms_sqltune.unpack_stgtab_sqlprof(
2451 -- replace => TRUE,
2452 -- staging_table_name => 'STAGING_TABLE');
2453
2454 -- NAME: create_stgtab_sqlprof
2455 -- PURPOSE: This procedure creates the staging table used for transporting
2456 -- sql profiles from one system to another (just like SQL tuning
2457 -- set pack/unpack)
2458 -- INPUTS: table_name - (REQUIRED) the name of the table to create
2459 -- (case-sensitive)
2460 -- schema_name - schema to create the table in, or NULL for
2461 -- current schema (case-sensitive)
2462 -- tablespace_name - tablespace to store the staging table within,
2463 -- or NULL for current user's default tablespace
2464 -- (case-sensitive)
2465 -- REQUIRES: "CREATE TABLE" privilege and tablespace quota
2466 --
2467 PROCEDURE create_stgtab_sqlprof(
2468 table_name IN VARCHAR2,
2469 schema_name IN VARCHAR2 := NULL,
2470 tablespace_name IN VARCHAR2 := NULL);
2471
2472 -- NAME: pack_stgtab_sqlprof
2473 -- PURPOSE: This procedure packs into the staging table created by a call
2474 -- to create_stgtab_sqlprof. It moves profile data out of the SYS
2475 -- schema into the staging table.
2476 --
2477 -- By default, we move all SQL profiles in category DEFAULT. See
2478 -- the examples section above for details. Note that this function
2479 -- issues a COMMIT after packing each sql profile, so if an error is
2480 -- raised mid-execution, some profiles may be in the staging table.
2481 --
2482 -- INPUTS: profile_name - name of profile to pack (% wildcards OK)
2483 -- (case-sensitive)
2484 -- profile_category - category to pack profiles from
2485 -- (% wildcards OK, case-insensitive)
2486 -- staging_table_name - (REQUIRED) the name of the table to use
2487 -- (case-sensitive)
2488 -- staging_schema_owner - schema where the table resides, or NULL
2489 -- for current schema (case-sensitive)
2490 -- REQUIRES: "ADMINISTER SQL PLAN MANAGEMENT OBJECT" privilege,
2491 -- "INSERT" privilege on staging table
2492 --
2493 PROCEDURE pack_stgtab_sqlprof(
2494 profile_name IN VARCHAR2 := '%',
2495 profile_category IN VARCHAR2 := 'DEFAULT',
2496 staging_table_name IN VARCHAR2,
2497 staging_schema_owner IN VARCHAR2 := NULL);
2498
2499 -- NAME: unpack_stgtab_sqlprof
2500 -- PURPOSE: This procedure unpacks from the staging table populated by a call
2501 -- to pack_stgtab_sqlprof. It uses the profile data stored in the
2502 -- staging table to create profiles on this system. Users can opt
2503 -- to replace existing profiles with profile data when they exist
2504 -- already. In this case, note that we can only replace profiles
2505 -- referring to the same statement if the names are the same (see
2506 -- accept_sql_profile).
2507 --
2508 -- By default, we move all SQL profiles in the staging table. The
2509 -- function commits after successfully loading each profile. If it
2510 -- fails creating an individual profile, it raises an error and does
2511 -- not proceed to the remaining ones in the staging table. For
2512 -- profile name or category errors, users should use the
2513 -- remap_stgtab_sqlprof function to patch the staging table and then
2514 -- call unpack again to create the remaining profiles.
2515 --
2516 --
2520 -- (% wildcards OK, case-insensitive)
2517 -- INPUTS: profile_name - name of profile to unpack (% wildcards OK)
2518 -- (case-sensitive)
2519 -- profile_category - category to unpack profiles from
2521 -- replace - replace profiles if they already exist?
2522 -- Note that profiles cannot be replaced if
2523 -- one in the staging table has the same name
2524 -- as an active profile on different SQL.
2525 -- If FALSE, this function errors whenever a
2526 -- profile we try to create already exists.
2527 -- staging_table_name - (REQUIRED) the name of the table to use
2528 -- (case-sensitive)
2529 -- staging_schema_owner - schema where the table resides, or NULL
2530 -- for current schema (case-sensitive)
2531 -- REQUIRES: "ADMINISTER SQL MANAGEMENT OBJECT" privilege and "SELECT"
2532 -- privilege on staging table ("CREATE ANY SQL PROFILE" privilege
2533 -- deprecated)
2534 --
2535 PROCEDURE unpack_stgtab_sqlprof(
2536 profile_name IN VARCHAR2 := '%',
2537 profile_category IN VARCHAR2 := '%',
2538 replace IN BOOLEAN,
2539 staging_table_name IN VARCHAR2,
2540 staging_schema_owner IN VARCHAR2 := NULL);
2541
2542 -- NAME: remap_stgtab_sqlprof
2543 -- PURPOSE: This procedure allows DBAs to change the profile data values
2544 -- kept in the staging table prior to performing a unpack operation.
2545 -- It can be used, for example, to change the name of a profile if
2546 -- one already exists on the system with the same name.
2547 --
2548 -- INPUTS: old_profile_name - (REQUIRED) the name of the profile to
2549 -- target for a remap operation
2550 -- (case-sensitive)
2551 -- new_profile_name - new name for profile, or NULL to remain
2552 -- the same (case-sensitive)
2553 -- new_profile_category - new category for the profile, or NULL to
2554 -- remain the same (case-insensitive)
2555 -- staging_table_name - (REQUIRED) the name of the table to
2556 -- perform the remap operation
2557 -- (case-sensitive)
2558 -- staging_schema_owner - schema where the table resides, or NULL
2559 -- for current schema (case-sensitive)
2560 -- REQUIRES: "UPDATE" privilege on staging table
2561 --
2562 PROCEDURE remap_stgtab_sqlprof(
2563 old_profile_name IN VARCHAR2,
2564 new_profile_name IN VARCHAR2 := NULL,
2565 new_profile_category IN VARCHAR2 := NULL,
2566 staging_table_name IN VARCHAR2,
2567 staging_schema_owner IN VARCHAR2 := NULL);
2568
2569
2570
2571 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
2572 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
2573 -- ------------------------------------------- --
2574 -- SQL MONITORING RELATED FUNCTIONS/PROCEDURE --
2575 -- ------------------------------------------- --
2576 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
2577 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
2578
2579 ------------------------------- report_sql_monitor --------------------------
2580 -- NAME:
2581 -- report_sql_monitor
2582 --
2583 -- DESCRIPTION:
2584 --
2585 -- This function builds a report (text, simple/active html, xml) for
2586 -- the monitoring information collected on behalf of the targeted
2587 -- statement execution.
2588 --
2589 -- The target SQL statement for this report can be:
2590 --
2591 -- - the last SQL monitored by Oracle (default, no parameter)
2592 -- - the last SQL executed by a specified session and monitored
2593 -- by Oracle. The session is identified by its session id and
2594 -- optionally it serial# (-1 is current session). For example, use
2595 -- sess_id=>-1 for the current session or sess_id=>20,
2596 -- sess_serial=>103 for session id 20, serial number 103.
2597 -- - the last execution of a specific statement identified by
2598 -- its sql_id.
2599 -- - a specific execution of a SQL statement identified by the
2600 -- triplet (sql_id, sql_exec_start and sql_exec_id).
2601 --
2602 -- PARAMETERS:
2603 --
2604 -- - sql_id: SQL_ID for which monitoring information should be
2605 -- displayed. Use NULL (the default) to display
2606 -- monitoring information for the last statement
2607 -- monitored by Oracle.
2608 --
2609 -- - session_id: Target only the sub-set of statements executed and
2610 -- monitored on behalf of the specified session.
2611 -- Default is NULL. Use -1 or USERENV('SID') for current
2612 -- seesion.
2613 --
2614 -- - session_serial:
2615 -- In addition to the above <session_id> parameter, one
2619 --
2616 -- can also specify its session serial to ensure that
2617 -- the desired session incarnation is targeted. Ignored
2618 -- when <session_id> is NULL.
2620 -- - (sql_exec_start, sql_exec_id):
2621 -- Only applicable when <sql_id> is also specified and
2622 -- can be used to display monitoring information for a
2623 -- particular execution of <sql_id>. When NULL (the
2624 -- default), the last execution of <sql_id> is shown.
2625 --
2626 -- - inst_id: Only look at queries started on the specified
2627 -- instance. Use -1 to target the current instance.
2628 -- The default, NULL will target all instances.
2629 --
2630 -- - start_time_filter:
2631 -- If non NULL, the report will show only activity
2632 -- (from V$ACTIVE_SESSION_HISTORY) started after this
2633 -- date. If NULL, the reported activity will start when
2634 -- the targeted SQL statement has started.
2635 --
2636 -- - end_time_filter:
2637 -- If non NULL, the report will show only activity
2638 -- (from V$ACTIVE_SESSION_HISTORY) collected before this
2639 -- date. If NULL, the reported activity will end when
2640 -- the targeted SQL statement has ended or SYSDATE if the
2641 -- statement is still executing.
2642 --
2643 -- - instance_id_filter:
2644 -- Only look at activity for the specified instance. Use
2645 -- NULL (the default) to target all instances. Only
2646 -- relevant if the query runs parallel.
2647 --
2648 -- - parallel_filter:
2649 -- Parallel filter applies only to parallel execution and
2650 -- allows to select only a subset of the processes
2651 -- involved in the parallel execution. The string
2652 -- parallel_filter can be:
2653 -- - NULL (target all parallel execution servers + the
2654 -- query coordinator)
2655 -- - ['qc'][servers(<svr_grp>[,] <svr_set>[,] <srv_num>)]
2656 -- where any NULL value is interpreted as ALL.
2657 --
2658 -- The following examples show how one can set
2659 -- <parallel_filter> to target only a subset of the
2660 -- parallel sessions:
2661 -- - 'qc' to target only the query coordinator
2662 -- - servers(1)': to target all px servers in group 1
2663 -- servers(,2)': to target all px servers in set 1,
2664 -- any group
2665 -- - servers(1,1)': group 1, set 1
2666 -- - servers(1,2,4)': group 1, set 3, server number 4
2667 -- - qc servers(1,2,4)': same as above by also
2668 -- including QC
2669 --
2670 -- - plan_line_filter:
2671 -- This filter selects activity and execution stats for
2672 -- the specified line number in the plan of a SQL
2673 --
2674 -- - event_detail:
2675 -- When set to 'no', the activity is aggregated by
2676 -- wait_class only. Use 'yes' (the default) to aggregate
2677 -- by (wait_class, event_name)
2678 --
2679 -- The next 2 parameters are used to control the activity histogram. By
2680 -- default, the maximum number of buckets is set to 128 and we derive the
2681 -- bucket_interval based on this. Basically, <bucket_interval> (value is
2682 -- in seconds) is computed such that it is the smallest possible power of
2683 -- 2 value (starting at 1s) without causing to exceed the maximum number
2684 -- of buckets. For example, if the query has executed for 600s, we will
2685 -- pick a bucket_interval of 8s (a power of two) since 600/8 = 74 which
2686 -- is less than 128 buckets maximum. Smaller than 8s would be 4s, but
2687 -- that would cause to have more buckets than the 128 maximum.
2688 -- If <bucket_interval> is specified, we will use that value instead of
2689 -- deriving it from bucket_max_count.
2690 --
2691 -- - bucket_max_count:
2692 -- If specified, this should be the maximum number of
2693 -- histogram buckets created in the report
2694 --
2695 -- - bucket_interval:
2696 -- If specified, this represents the exact time interval
2697 -- in seconds, of all histogram buckets. If specified,
2698 -- bucket_max_count is ignored.
2699 --
2700 -- - base_path: this is the URL path for flex HTML ressources since
2701 -- flex HTML format requires to access external files
2702 -- (java scripts and the flash swf file itself).
2703 --
2704 -- - last_refresh_time:
2705 -- If not null (default is null), time when the
2706 -- report was last retrieved (see sysdate attribute
2707 -- of the report tag). Use this option when you want
2708 -- to display the report of an running query and when
2709 -- that report is refreshed on a regular basis. This
2710 -- will optimize the size of the report since only
2714 -- is specified
2711 -- the new/changed information will be returned. In
2712 -- particular, the following will be optimized:
2713 -- - SQL text will not be returned when this option
2715 -- - activity histogram will start at the bucket that
2716 -- intersect that time. The entire content of the
2717 -- bucket will be return, even if last_refresh_time
2718 -- is after the start of that bucket
2719 --
2720 -- - report_level:
2721 -- level of detail for the report, either 'none', 'basic',
2722 -- 'typical' or 'all'. Default assumes 'typical'. Their
2723 -- meanings are explained below.
2724 --
2725 -- In addition, individual report sections can also
2726 -- be enabled/disabled by using a +/-<section_name>.
2727 -- Several sections are defined: 'plan', 'xplan',
2728 -- 'parallel', 'sessions', 'instance', 'binds', 'activity',
2729 -- 'activity_histogram', 'plan_histogram', 'metrics',
2730 -- 'other'.
2731 -- Their meanings are as follows:
2732 -- xplan : Show explain plan,
2733 -- ON by default
2734 -- plan : Show plan monitoring stats,
2735 -- ON by default
2736 -- sessions : Show session details. Applies only
2737 -- to parallel queries
2738 -- ON by default
2739 -- instance : Show instance details. Applies only
2740 -- to parallel and cross instance
2741 -- queries
2742 -- ON by default
2743 -- parallel : An umbrella parameter for
2744 -- specifying sessions+instance
2745 -- details
2746 -- activity : Show activity summary at global
2747 -- level, plan line level and session/
2748 -- instance level (if applicable).
2749 -- ON by default
2750 -- binds : Show bind information when
2751 -- available ON by default
2752 -- metrics : Show metric data (CPU, IOs, ...)
2753 -- over time
2754 -- ON by default
2755 -- activity_histogram :
2756 -- Show an histogram of the overall
2757 -- query activity
2758 -- ON by default
2759 -- plan_histogram : Show activity histogram at plan
2760 -- line level
2761 -- OFF by default
2762 -- other : Other info
2763 -- ON by default
2764 --
2765 -- In addition, SQL text can be specified at different
2766 -- levels:
2767 -- -sql_text : No SQL text in report
2768 -- +sql_text : OK with partial SQL text, i.e. upto
2769 -- the first 2000 chars as stored in
2770 -- gv$sql_monitor
2771 -- -sql_fulltext : No full SQL text, i.e +sql_text
2772 -- +sql_fulltext : Show full SQL text (default value)
2773 --
2774 -- The meanings of the three top-level report levels are:
2775 -- none = the minimum possible
2776 -- basic = sql_text-plan-xplan-sessions-instance
2777 -- -activity_histogram-plan_histogram
2778 -- -metrics
2779 -- typical = everything but plan_histogram
2780 -- all = everything
2781 --
2782 -- Only one of these 4 levels can be specified and if it
2783 -- is, then it has to be at the start of the report_level
2784 -- string
2785 --
2786 -- Examples:
2787 -- Use 'basic+parallel' to show the basic
2788 -- report with additional section reporting parallel
2789 -- information. Use 'all-plan-instance' for full
2790 -- report minus plan detail and instance information.
2791 --
2792 -- - type:
2793 -- Report TYPE. Can be either 'TEXT' (text report, the default),
2794 -- 'HTML' (simple HTML report, 'ACTIVE' (database active reports),
2795 -- 'XML' (raw data for the report). Some information (activity
2796 -- histogram, metrics, ...) are only shown when the ACTIVE report
2797 -- type is selected.
2798 --
2799 -- - sql_plan_hash_value:
2800 -- Target only those with the specified plan_hash_value.
2801 -- Default is NULL.
2802 --
2803 -- - con_name: container name
2804 --
2808 -- - dbop_name DQOP_NAME for which DB operation should be displayed
2805 -- - report_id: id of report in auto-report repository.
2806 -- report ids can be found in dba_hist_reports.
2807 --
2809 -- Use NULL to display the most recent monitored DB
2810 -- operations or SQL
2811 --
2812 -- - dbop_exec_id the execution ID of the specified DBOP
2813 --
2814 -- RETURN:
2815 -- The SQL monitor report, an XML document
2816 --
2817 -- NOTE:
2818 -- The user tunning this function needs to have privilege to access the
2819 -- following fixed views:
2820 -- - GV$SQL_MONITOR
2821 -- - GV$SQL_PLAN_MONITOR
2822 -- - GV$ACTIVE_SESSION_HISTORY
2823 -- - GV$SESSION_LONGOPS
2824 -- - GV$SQL if SQL fulltext is asked and its length is > 2K
2825 -----------------------------------------------------------------------------
2826 FUNCTION report_sql_monitor(
2827 sql_id in varchar2 default NULL,
2828 session_id in number default NULL,
2829 session_serial in number default NULL,
2830 sql_exec_start in date default NULL,
2831 sql_exec_id in number default NULL,
2832 inst_id in number default NULL,
2833 start_time_filter in date default NULL,
2834 end_time_filter in date default NULL,
2835 instance_id_filter in number default NULL,
2836 parallel_filter in varchar2 default NULL,
2837 plan_line_filter in number default NULL,
2838 event_detail in varchar2 default 'yes',
2839 bucket_max_count in number default 128,
2840 bucket_interval in number default NULL,
2841 base_path in varchar2 default NULL,
2842 last_refresh_time in date default NULL,
2843 report_level in varchar2 default 'TYPICAL',
2844 type in varchar2 default 'TEXT',
2845 sql_plan_hash_value in number default NULL,
2846 con_name in varchar2 default NULL,
2847 report_id in number default NULL,
2848 dbop_name in varchar2 default NULL,
2849 dbop_exec_id in number default NULL)
2850 RETURN clob;
2851
2852
2853 ------------------------------- report_sql_monitor_xml ----------------------
2854 -- NAME:
2855 -- report_sql_monitor_xml
2856 --
2857 -- DESCRIPTION:
2858 --
2859 -- Same as above function (report_sql_monitor()) except that the result
2860 -- is only XML, hence the return type is xmltype and two additional
2861 -- input parameters for DB operations
2862 --
2863 -- - dbop_name DQOP_NAME for which DB operation should be displayed
2864 -- Use NULL to display any monitored DB operations
2865 --
2866 -- - dbop_exec_id The execution ID of the given DBOP
2867 --
2868 -- - report_id: id of report in auto-report repository.
2869 -- report ids can be found in dba_hist_reports.
2870 --
2871 -----------------------------------------------------------------------------
2872 FUNCTION report_sql_monitor_xml(
2873 sql_id in varchar2 default NULL,
2874 session_id in number default NULL,
2875 session_serial in number default NULL,
2876 sql_exec_start in date default NULL,
2877 sql_exec_id in number default NULL,
2878 inst_id in number default NULL,
2879 start_time_filter in date default NULL,
2880 end_time_filter in date default NULL,
2881 instance_id_filter in number default NULL,
2882 parallel_filter in varchar2 default NULL,
2883 plan_line_filter in number default NULL,
2884 event_detail in varchar2 default 'yes',
2885 bucket_max_count in number default 128,
2886 bucket_interval in number default NULL,
2887 base_path in varchar2 default NULL,
2888 last_refresh_time in date default NULL,
2889 report_level in varchar2 default 'TYPICAL',
2890 auto_refresh in number default NULL,
2891 sql_plan_hash_value in number default NULL,
2892 dbop_name in varchar2 default NULL,
2893 dbop_exec_id in number default NULL,
2894 con_name in varchar2 default NULL,
2895 report_id in number default NULL)
2896 return xmltype;
2897
2898
2899 ---------------------------- report_sql_monitor_list ------------------------
2900 -- NAME:
2901 -- report_sql_monitor_list
2902 --
2903 -- DESCRIPTION:
2904 --
2905 -- This function builds a report for all or a sub-set of statements
2906 -- that have been monitored by Oracle. For each statement, it gives
2907 -- key information and associated global statistics.
2908 --
2909 -- Use report_sql_monitor() to get detail monitoring information for
2910 -- a single SQL statement
2911 --
2912 -- PARAMETERS:
2913 --
2914 -- - sql_id: SQL_ID for which monitoring information should be
2918 --
2915 -- displayed. Use NULL (the default) to display
2916 -- monitoring information for the last statement
2917 -- monitored by Oracle.
2919 -- - session_id: Target only the sub-set of statements executed and
2920 -- monitored on behalf of the specified session.
2921 -- Default is NULL. Use -1 (or USERENV('SID')) for
2922 -- current session.
2923 --
2924 -- - session_serial:
2925 -- In addition to the above <session_id> parameter, one
2926 -- can also specify its session serial to ensure that
2927 -- the desired session incarnation is targeted. Ignored
2928 -- when <session_id> is NULL.
2929 --
2930 -- - inst_id: Only look at monitored statements originating from
2931 -- the specified instance. Special value -1 can be used
2932 -- to target the instance where the report executed.
2933 -- To target all instances, use NULL (the default).
2934 --
2935 -- - active_since_date:
2936 -- If not null (default is null), only returns monitored
2937 -- statements that have been active since specified
2938 -- time. This includes all statements that are still
2939 -- executing plus all statements that have completed
2940 -- their execution after the specified date/time.
2941 --
2942 -- - active_since_sec:
2943 -- Same as above but the date is specified relativelly
2944 -- to the current sysdate minus specified number of
2945 -- seconds. For example, use 3600 to limit the report
2946 -- to all statements that have been active in the past
2947 -- 1 hour.
2948 --
2949 -- - active_before_date:
2950 -- If not null (default is null), only returns monitored
2951 -- statements that have been active before the specified
2952 -- time.
2953 --
2954 -- - last_refresh_time:
2955 -- If not null (default is null), date/time when the
2956 -- list report was last retrieved. This is to optimize
2957 -- the case where an application shows the list and
2958 -- refresh the report on a regular basis (say once every
2959 -- 5s). In this case, the report will only show detail
2960 -- about the execution of monitored queries that have
2961 -- been active since the specified <last_refresh_time>.
2962 -- For other queries, the report will only return the
2963 -- execution key (i.e. sql_id, sql_exec_start,
2964 -- sql_exec_id). Also, for queries that have their
2965 -- first refresh time after the specified date, only
2966 -- the SQL execution key and statistics are returned.
2967 --
2968 -- - dbop_name DQOP_NAME for which DB operation should be displayed
2969 -- Use NULL to display any monitored DB operations
2970 --
2971 -- - monitor_type MONITOR_TYPE_SQL will only return SQLs
2972 -- MONITOR_TYPE_DBOP will only return DB Operations
2973 -- MONITOR_TYPE_ALL will return all types
2974 --
2975 -- - max_sqltext_length
2976 -- Maximum length of the SQL text. Default is null
2977 -- (no limit).
2978 --
2979 -- - top_n_count not null if we should only get the top-N SQL
2980 -- statements. In that case, the parameter specifies
2981 -- what N should be.
2982 --
2983 -- - top_n_rankby only used when top_n_count is not null. Specifies the
2984 -- attribute to rank on. Potential values are:
2985 -- 'last_active_time': rank based on when the SQL was
2986 -- last active (top N most recent)
2987 -- 'duration': rank on total duration
2988 -- 'db_time': rank on db time
2989 -- 'cpu_time': rank on cpu time
2990 -- 'io_requests' rank on IO requests
2991 -- 'io_bytes' rank on IO bytes
2992 --
2993 -- - report_level:
2994 -- level of detail for the report. The level can be
2995 -- either basic (SQL text up to 200 character),
2996 -- typical (include full SQL text assuming that cursor
2997 -- has not aged out, in which case the SQL text is
2998 -- included up to 2000 characters). report_level can
2999 -- also be all which is the same as typical for now.
3000 --
3001 -- - con_name: container_name
3002 --
3003 -- - top_n_detail_count
3004 -- not null if we should get SQL monitor details
3005 -- (i.e., sql monitor report for individual SQL),
3006 -- but only for the top-N SQL statements (up to 10)
3007 -- among all statements captured in the list report
3008 -- and which is controlled by top_n_count parameter.
3009 --
3010 --
3011 --
3012 -- RETURN:
3016 -- NOTE:
3013 -- A report (xml, text, html) for the list of SQL statements that have
3014 -- been monitored.
3015 --
3017 -- The user tunning this function needs to have privilege to access the
3018 -- following fixed views:
3019 -- - GV$SQL_MONITOR and GV$SQL
3020 -----------------------------------------------------------------------------
3021 FUNCTION report_sql_monitor_list(
3022 sql_id in varchar2 default NULL,
3023 session_id in number default NULL,
3024 session_serial in number default NULL,
3025 inst_id in number default NULL,
3026 active_since_date in date default NULL,
3027 active_since_sec in number default NULL,
3028 active_before_date in date default NULL,
3029 last_refresh_time in date default NULL,
3030 dbop_name in varchar2 default NULL,
3031 monitor_type in number default MONITOR_TYPE_ALL,
3032 max_sqltext_length in number default NULL,
3033 top_n_count in number default NULL,
3034 top_n_rankby in varchar2 default 'last_active_time',
3035 report_level in varchar2 default 'TYPICAL',
3036 auto_refresh in number default NULL,
3037 base_path in varchar2 default NULL,
3038 type in varchar2 default 'TEXT',
3039 con_name in varchar2 default NULL,
3040 top_n_detail_count in number default NULL)
3041 RETURN clob;
3042
3043 ---------------------------- report_sql_monitor_list_xml -------------------
3044 -- NAME:
3045 -- report_sql_monitor_list_xml
3046 --
3047 -- DESCRIPTION:
3048 --
3049 -- Same as above function (report_sql_monitor) except that the result
3050 -- is only XML, hence the return type is xmltype
3051 --
3052 -- - dbop_name DQOP_NAME for which DB operation should be displayed
3053 -- Use NULL to display any monitored DB operations
3054 --
3055 -- - monitor_type MONITOR_TYPE_SQL will only return SQLs
3056 -- MONITOR_TYPE_DBOP will only return DB Operations
3057 -- MONITOR_TYPE_ALL will return all types
3058 --
3059 -- - dbop_name DQOP_NAME for which DB operation should be displayed
3060 -- Use NULL to display any monitored DB operations
3061 --
3062 -- - monitor_type MONITOR_TYPE_SQL will only return SQLs
3063 -- MONITOR_TYPE_DBOP will only return DB Operations
3064 -- MONITOR_TYPE_ALL will return all types
3065 --
3066 -- - max_sqltext_length
3067 -- Maximum length of the SQL text. Default is null
3068 -- (no limit).
3069 --
3070 -- - top_n_count not null if we should only get the top-N SQL
3071 -- statements. In that case, the parameter specifies
3072 -- what N should be.
3073 --
3074 -- - top_n_rankby only used when top_n_count is not null. Specifies the
3075 -- attribute to rank on. Potential values are:
3076 -- 'last_active_time': rank based on when the SQL was
3077 -- last active (top N most recent)
3078 -- 'duration': rank on total duration
3079 -- 'db_time': rank on db time
3080 -- 'cpu_time': rank on cpu time
3081 -- 'io_requests' rank on IO requests
3082 -- 'io_bytes' rank on IO bytes
3083 --
3084 -- - top_n_detail_count
3085 -- not null if we should get SQL monitor details
3086 -- (i.e., sql monitor report for individual SQL),
3087 -- but only for the top-N SQL statements (up to 10)
3088 -- among all statements captured in the list report
3089 -- and which is controlled by top_n_count parameter.
3090 --
3091 -- - con_name container name
3092 --
3093 -- - compress_xml compress resulting xml report
3094 --
3095 -- RETURN:
3096 -- A report (xml, text, html) for the list of SQL statements that have
3097 -- been monitored.
3098 --
3099 -- NOTE:
3100 -- The user tunning this function needs to have privilege to access the
3101 -- following fixed views:
3102 -- - GV$SQL_MONITOR and GV$SQL
3103 -----------------------------------------------------------------------------
3104 FUNCTION report_sql_monitor_list_xml(
3105 sql_id in varchar2 default NULL,
3106 session_id in number default NULL,
3107 session_serial in number default NULL,
3108 inst_id in number default NULL,
3109 active_since_date in date default NULL,
3110 active_since_sec in number default NULL,
3111 active_before_date in date default NULL,
3112 last_refresh_time in date default NULL,
3113 report_level in varchar2 default 'TYPICAL',
3114 auto_refresh in number default NULL,
3115 base_path in varchar2 default NULL,
3116 dbop_name in varchar2 default NULL,
3117 monitor_type in number default MONITOR_TYPE_ALL,
3118 max_sqltext_length in number default NULL,
3122 con_name in varchar2 default NULL,
3119 top_n_count in number default NULL,
3120 top_n_rankby in varchar2 default 'last_active_time',
3121 top_n_detail_count in number default NULL,
3123 compress_xml in binary_integer := 0)
3124 RETURN xmltype;
3125
3126 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
3127 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
3128 -- ------------------------------------------- --
3129 -- SQL DETAIL RELATED FUNCTIONS/PROCEDURE --
3130 -- ------------------------------------------- --
3131 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
3132 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
3133
3134 ------------------------------- report_sql_detail ---------------------------
3135 -- NAME:
3136 -- report_sql_detail
3137 --
3138 -- DESCRIPTION:
3139 --
3140 -- This function builds a report for a specific sql_id. For each sql_id
3141 -- it gives various statistics and details as obtained from the v$ views
3142 --
3143 -- PARAMETERS:
3144 -- - sql_id: SQL_ID for which SQL statistics and details should
3145 -- be displayed. If NULL (default) display statistics
3146 -- for the sql_id of the last sql statement executed
3147 -- in the current session.
3148 --
3149 -- - sql_plan_hash_value:
3150 -- Displays SQL statistics and details for a
3151 -- specific plan_hash_value.
3152 -- If NULL (default) displays statistics and details
3153 -- for all plans of the sql_id.
3154 --
3155 -- - start_time: If specified, shows SQL activity
3156 -- (from gv$active_session_history) starting at
3157 -- this time.
3158 -- Default is NULL.
3159 --
3160 -- - duration: duration of activity (ASH) in seconds for the report.
3161 -- If NULL (default), then 3600 seconds
3162 --
3163 -- - inst_id: target_instance to get sql details from
3164 -- If NULL, uses data from all instances
3165 -- If 0 or -1, uses current instance.
3166 --
3167 -- - dbid: target dbid to get sql details from
3168 -- If NULL, uses current dbid
3169 --
3170 -- - event_detail:
3171 -- When set to 'no', the activity is aggregated by
3172 -- wait_class only. Use 'yes' (the default) to aggregate
3173 -- by (wait_class, event_name)
3174 --
3175 -- The next 2 parameters are used to control the activity histogram. By
3176 -- default, the maximum number of buckets is set to 128 and we derive the
3177 -- bucket_interval based on this. Basically, <bucket_interval> (value is
3178 -- in seconds) is computed such that it is the smallest possible power of
3179 -- 2 value (starting at 1s) without causing to exceed the maximum number
3180 -- of buckets. For example, if the query has executed for 600s, we will
3181 -- pick a bucket_interval of 8s (a power of two) since 600/8 = 74 which
3182 -- is less than 128 buckets maximum. Smaller than 8s would be 4s, but
3183 -- that would cause to have more buckets than the 128 maximum.
3184 -- If <bucket_interval> is specified, we will use that value instead of
3185 -- deriving it from bucket_max_count.
3186 --
3187 -- - bucket_max_count:
3188 -- If specified, this should be the maximum number of
3189 -- histogram buckets created in the report
3190 --
3191 -- - bucket_interval:
3192 -- If specified, this represents the exact time interval
3193 -- in seconds, of all histogram buckets. If specified,
3194 -- bucket_max_count is ignored.
3195 --
3196 -- - top_n: Controls the number of entries to display per dimension
3197 -- in the top dimensions section. DEFAULT 10
3198 --
3199 -- - report_level: level of detail for the report, either 'basic',
3200 -- 'typical' or 'all'.
3201 -- NULL (Default) assumes 'typical'. Their
3202 -- meanings are explained below.
3203 --
3204 -- In addition, individual report sections can also
3205 -- be enabled/disabled by using a +/-<section_name>.
3206 -- Several sections are defined, as follows:
3207 -- Their meanings are as follows:
3208 -- top : show top values for the
3209 -- ASH dimensions for a SQL statement
3210 -- ON by default
3211 -- spm : show existing plan baselines
3212 -- for a SQL statement
3213 -- ON by default
3214 -- sqltune : show SQL Tuning history for a
3215 -- SQL statement
3216 -- ON by default
3217 -- sqlpatch : show SQL Patches for a
3218 -- SQL statement
3222 -- ON by default
3219 -- ON by default
3220 -- tunehist : show SQL Tuning history for a
3221 -- SQL statement
3223 -- mismatch : show reasons for creating
3224 -- new child cursors; i.e.
3225 -- sharing criteria violations
3226 -- OFF by default
3227 -- stats : Show SQL execution stats per plan
3228 -- from gv$sqlarea_plan_hash
3229 -- ON by default
3230 -- activity : Show top activity from ASH for
3231 -- each plan of a SQL statement
3232 -- ON by default
3233 -- activity_all : Show top activity from ASH
3234 -- for each line of the plan for a
3235 -- SQL statement
3236 -- OFF by default
3237 -- histogram : Show activity histogram for
3238 -- each plan of a SQL statement
3239 -- (plan timeline histogram)
3240 -- ON by default
3241 -- sessions : Show activity for top sessions for
3242 -- each plan of a SQL satement
3243 -- OFF by default
3244 -- monitor : Show one monitored SQL exectuion
3245 -- per execution plan
3246 -- ON by default
3247 -- xplan : show execution plans
3248 -- ON by default
3249 -- binds : show captured bind data
3250 -- ON by default
3251 --
3252 -- In addition, SQL text can be specified at different
3253 -- levels:
3254 -- -sql_text : No SQL text in report
3255 -- +sql_text : OK with partial SQL text, i.e. upto
3256 -- the first 2000 chars as stored in
3257 -- gv$sql
3258 -- -sql_fulltext : No full SQL text, i.e +sql_text
3259 -- +sql_fulltext : Show full SQL text (default value)
3260 --
3261 -- The meanings of the three top-level report levels are:
3262 -- basic = sql_text+stats+activity
3263 -- plan_histogram
3264 -- typical = sql_fulltext+top+binds+stats+activity
3265 -- +histogram+xplan+monitor+spm+sqltune
3266 -- +sqlpatch+tunehist
3267 -- all = sql_fulltext+top+spm+sqltune+sqlpatch
3268 -- +tunehist
3269 -- +mismatch
3270 -- +binds+stats+activity_all+histogram+sessions
3271 -- +monitor+xplan
3272 --
3273 -- Only one of these 3 levels can be specified and if it
3274 -- is, then it has to be at the start of the report_level
3275 -- string
3276 --
3277 -- Examples:
3278 -- Use 'basic+histogram' to show the basic
3279 -- report with additional section reporting activity
3280 -- information
3281 --
3282 -- - type: 'text','html' or 'xml'. only 'xml' is implemented
3283 -- currently.
3284 --
3285 -- - data_source: determines data source of sql data
3286 -- 'memory' - gv$ views
3287 -- 'disk' - dba_hist_*
3288 -- 'auto' - automatically determines source based on
3289 -- time frame (default)
3290 --
3291 -- - end_time: If specified, shows SQL activity from start_time
3292 -- to end_time
3293 -- If NULL (default), systimestamp
3294 --
3295 -- - duration_stats: duration of additional SQL execution statistics
3296 -- from AWR (in hours), for the report.
3297 -- If NULL (default) then 24 hours
3298 --
3299 -- - con_name: container name in CDB
3300 --
3301 -- Behavior of time parameters:
3302 -- if start_time and end_time are specified then
3303 -- duration and duration_stats are ignored
3304 -- activity and historical stats are retrieved from start_time to
3305 -- end_time
3306 -- if start_time is specified, but end_time is not specified then
3307 -- activity (ASH) is retrieved from start_time to duration
3308 -- (default duration is 3600 seconds)
3309 -- SQL execution stats is retrieved from start_time
3310 -- to duration_stats (default duration_stats is 24 hours)
3311 -- if start_time is not specified (DEFAULT), then
3312 -- activity (ASH) is retrieved for past duration seconds (default
3313 -- 3600s) from end_time (if end_time is NULL, then use
3314 -- systimestamp)
3318 -- The default behavior is the past hour (3600s) of ASH activity
3315 -- SQL execution stats is retrieved for past duration_stats hours
3316 -- (default 24 hours) from end_time (if end_time is NULL,
3317 -- then use systimestamp)
3319 -- and the past 24 hours for SQL execution statistics.
3320 --
3321 -- RETURN
3322 -- The SQL Statistics Detail report, a CLOB document
3323 --
3324 -- NOTE:
3325 -- The user generating the report needs to have privileges to access
3326 -- the following fixed views:
3327 -- - GV$ASH_INFO
3328 -- - GV$ACTIVE_SESSION_HISTORY
3329 -- - GV$SQLAREA_PLAN_HASH
3330 -- - GV$SQL
3331 -- - GV$SQL_SHARED_CURSOR
3332 -- - GV$SQL_BIND_CAPTURE
3333 -- - V$DATABASE
3334 -- - V$SESSION
3335 -- - GV$PARAMETER
3336 -- - DBA_HIST_* views
3337 -- - DBA_SQL_PROFILES
3338 -- - CDB_SQL_PROFILES
3339 -- - DBA_SQL_PLAN_BASELINES
3340 -- - CDB_SQL_PLAN_BASELINES
3341 -- - DBA_SERVICES, DBA_USERS, DBA_OBJECTS, DBA_PROCEDURES
3342 -- - CDB_SERVICES, CDB_USERS, CDB_OBJECTS, CDB_PROCEDURES
3343 -- - DBA_ADVISOR_* views
3344 -- - CDB_ADVISOR_* views
3345 -- - DBA_ADDM_* views
3346 -- and privileges required by the following packages
3347 -- - DBMS_XPLAN
3348 -- - DBMS_SQLTUNE.SQL_MONITOR
3349 -- - DBMS_SQLTUNE.REPORT_SQL_MONITOR
3350 -- - DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST
3351 -----------------------------------------------------------------------------
3352 FUNCTION report_sql_detail(
3353 sql_id in varchar2 default NULL,
3354 sql_plan_hash_value in number default NULL,
3355 start_time in date default NULL,
3356 duration in number default NULL,
3357 inst_id in number default NULL,
3358 dbid in number default NULL,
3359 event_detail in varchar2 default 'yes',
3360 bucket_max_count in number default 128,
3361 bucket_interval in number default NULL,
3362 top_n in number default 10,
3363 report_level in varchar2 default NULL,
3364 type in varchar2 default 'ACTIVE',
3365 data_source in varchar2 default 'auto',
3366 end_time in date default NULL,
3367 duration_stats in number default NULL,
3368 con_name in varchar2 default NULL)
3369 RETURN clob;
3370
3371
3372 ------------------------------- report_sql_detail_xml -----------------------
3373 -- NAME:
3374 -- report_sql_detail_xml
3375 --
3376 -- DESCRIPTION:
3377 --
3378 -- This function builds a XML report on behalf of report_sql_detail().
3379 -- Please refer to that function for a full description.
3380 -----------------------------------------------------------------------------
3381 FUNCTION report_sql_detail_xml(
3382 sql_id in varchar2 default NULL,
3383 sql_plan_hash_value in number default NULL,
3384 start_time in date default NULL,
3385 duration in number default NULL,
3389 bucket_max_count in number default 128,
3386 inst_id in number default NULL,
3387 dbid in number default NULL,
3388 event_detail in varchar2 default 'yes',
3390 bucket_interval in number default NULL,
3391 top_n in number default 10,
3392 report_level in varchar2 default NULL,
3393 data_source in varchar2 default 'auto',
3394 end_time in date default NULL,
3395 duration_stats in number default NULL,
3396 con_name in varchar2 default NULL)
3397 return xmltype;
3398
3399
3400 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
3401 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
3402 -- ---------------------------- --
3403 -- UTILITY PROCEDURES/FUNCTIONS --
3404 -- ---------------------------- --
3405 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
3406 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
3407 --------------------------------- extract_bind ------------------------------
3408 -- NAME:
3409 -- extract_bind
3410 --
3411 -- DESCRIPTION:
3412 -- Given the value of a bind_data column captured in v$sql and a
3413 -- bind position, this function returns the value of the bind
3414 -- variable at that position in the SQL statement. Bind position
3415 -- start at 1. This function returns value and type information for
3416 -- the bind (see object type SQL_BIND).
3417 --
3418 -- PARAMETERS:
3419 -- bind_data (IN) - value of bind_data column from v$sql
3420 -- position (IN) - bind position in the statement (starts from 1)
3421 --
3422 -- RETURN:
3423 -- This function will return NULL if one of the condition below is
3424 -- true:
3425 -- - the specified bind variable was not captured (only interesting
3426 -- bind values used by the optimizer are captured)
3427 -- - bind position is invalid or out-of-bound
3428 -- - the specified bind_data is NULL.
3429 --
3430 -- NOTE:
3431 -- name of the bind in SQL_BIND object is not populated by this function
3432 -----------------------------------------------------------------------------
3433 FUNCTION extract_bind(
3434 bind_data IN RAW,
3435 bind_pos IN PLS_INTEGER) RETURN SQL_BIND;
3436
3437 --------------------------------- extract_binds -----------------------------
3438 -- NAME:
3439 -- extract_binds
3440 --
3441 -- DESCRIPTION:
3442 -- Given the value of a bind_data column captured in v$sql
3443 -- this function returns the collection (list) of bind values
3444 -- associated to the corresponding SQL statement.
3445 --
3446 -- PARAMETERS:
3447 -- bind_data (IN) - value of bind_data column from v$sql
3448 --
3449 -- RETURN:
3450 -- This function returns collection (list) of bind values of
3451 -- type sql_bind.
3452 --
3453 -- NOTE:
3454 -- For the content of a bind value, refer to function extract_bind
3455 ----------------------------------------------------------------------------
3456 FUNCTION extract_binds(
3457 bind_data IN RAW)
3458 RETURN SQL_BIND_SET PIPELINED;
3459
3460
3461 ----------------------------------------------------------------------------
3462 -- --
3463 -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! --
3464 -- !!! UNDOCUMENTED FUNCTIONS AND PROCEDURES. FOR INTERNAL USE ONLY !!! --
3465 -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! --
3466 -- --
3467 ----------------------------------------------------------------------------
3468
3469 -- DEPRECATED: see DBMS_AUTO_SQLTUNE
3470 PROCEDURE set_auto_tuning_task_parameter(
3471 parameter IN VARCHAR2,
3472 value IN VARCHAR2);
3473
3474 -- DEPRECATED: see DBMS_AUTO_SQLTUNE
3475 PROCEDURE set_auto_tuning_task_parameter(
3476 parameter IN VARCHAR2,
3477 value IN NUMBER);
3478
3479 --
3480 FUNCTION build_stash_xml(
3481 session_id in number default NULL,
3482 session_serial in number default NULL,
3483 session_inst_id in number default NULL,
3484 px_mode in varchar2 default 'yes',
3485 start_time in date default NULL,
3486 end_time in date default NULL,
3487 missing_seconds in number default NULL,
3488 instance_low_filter in number default 0,
3489 instance_high_filter in number default 10000,
3490 bucket_max_count in number default 128,
3491 bucket_interval in number default NULL,
3492 report_level in varchar2 default 'TYPICAL',
3493 cpu_cores in binary_integer default NULL,
3494 is_hyper in varchar2 default NULL)
3495 RETURN xmltype;
3496
3497 --
3498 PROCEDURE check_sqlset_privs(
3499 sqlset_name IN VARCHAR2,
3500 sqlset_owner IN VARCHAR2,
3501 sqlset_create IN BOOLEAN := false,
3502 read_only IN BOOLEAN := false);
3503
3504 --
3505 PROCEDURE check_sql_profile_priv(priv IN VARCHAR2);
3506
3507 --
3508 PROCEDURE cap_sts_cbk(
3509 sqlset_name IN VARCHAR2,
3510 iterations IN POSITIVE,
3511 cap_option IN VARCHAR2,
3515 sqlset_owner IN VARCHAR2 := NULL);
3512 cap_mode IN NUMBER,
3513 cbk_proc_name IN VARCHAR2,
3514 basic_filter IN VARCHAR2 := NULL,
3516
3517 --
3518 FUNCTION prepare_sqlset_statement(
3519 sqlset_name IN VARCHAR2,
3520 sqlset_owner IN VARCHAR2,
3521 basic_filter IN VARCHAR2 := NULL,
3522 stmt_filter IN BOOLEAN := FALSE,
3523 object_filter IN VARCHAR2 := NULL,
3524 plan_filter IN VARCHAR2 := NULL,
3525 rank1 IN VARCHAR2 := NULL,
3526 rank2 IN VARCHAR2 := NULL,
3527 rank3 IN VARCHAR2 := NULL,
3528 result_percentage IN NUMBER := 1,
3529 result_limit IN NUMBER := NULL,
3530 attribute_list IN VARCHAR2 := NULL,
3531 attribute_selected IN OUT NOCOPY BINARY_INTEGER,
3532 wrap_obj_ctor IN BOOLEAN := FALSE,
3533 check_binds IN BOOLEAN := TRUE,
3534 sts_id OUT NUMBER,
3535 first_rows_hint IN BOOLEAN := TRUE)
3536 RETURN VARCHAR2;
3537
3538 --
3539 FLAG_PREPAWR_WRAPCTOR CONSTANT NUMBER := POWER(2, 0);
3540 FLAG_PREPAWR_NOCKBINDS CONSTANT NUMBER := POWER(2, 1);
3541 FLAG_PREPAWR_INCLBID CONSTANT NUMBER := POWER(2, 2);
3542
3543 FUNCTION prepare_awr_statement(
3544 begin_snap IN NUMBER,
3545 end_snap IN NUMBER,
3546 basic_filter IN VARCHAR2 := NULL,
3547 stmt_filter IN BOOLEAN := FALSE,
3548 object_filter IN VARCHAR2 := NULL,
3549 rank1 IN VARCHAR2 := NULL,
3550 rank2 IN VARCHAR2 := NULL,
3551 rank3 IN VARCHAR2 := NULL,
3552 result_percentage IN NUMBER := 1,
3553 result_limit IN NUMBER := NULL,
3554 attribute_list IN VARCHAR2 := NULL,
3555 attribute_selected IN OUT NOCOPY BINARY_INTEGER,
3556 flags IN NUMBER := 0)
3557 RETURN VARCHAR2;
3558
3559 --
3560 PROCEDURE sqlset_progress_stats(
3561 sqlset_name IN VARCHAR2,
3562 sqlset_owner IN VARCHAR2,
3563 basic_filter IN VARCHAR2 := NULL,
3564 plan_filter IN VARCHAR2 := NULL,
3565 rank1 IN VARCHAR2 := NULL,
3566 rank2 IN VARCHAR2 := NULL,
3567 rank3 IN VARCHAR2 := NULL,
3568 result_percentage IN NUMBER := 1,
3569 result_limit IN NUMBER := NULL,
3570 sql_count OUT NUMBER,
3571 workload_time OUT NUMBER,
3572 exec_type# IN PLS_INTEGER);
3573
3574 --
3575 PROCEDURE examine_stgtab(
3576 stgtab_owner IN VARCHAR2,
3577 stgtab IN VARCHAR2,
3578 sts_name OUT VARCHAR2,
3579 sts_owner OUT VARCHAR2);
3580
3581 ------------------------- report_tuning_task_list_xml ----------------------
3582 -- NAME:
3583 -- report_tuning_task_list_xml - report a SQL tuning task list xml
3584 --
3585 -- DESCRIPTION:
3586 -- This procedure is called to display the list of SQL tuning tasks
3587 -- in XML format
3588 --
3589 -- PARAMETERS:
3590 -- result_limit (IN) - top N most recent tasks based on their
3591 -- last execution (start) time
3592 -- autotune_period (IN) - auto-sqltune time period. Applies only
3593 -- to automatic sql tuning advisor task.
3594 -- Possible values are for this argument:
3595 -- - NULL: do not embed auto-sqltune task
3596 -- to the task list XML
3597 -- - negative value (default): meaning
3598 -- all or full. The result includes all task
3599 -- executions
3600 -- - 0: result of the current/last task execution
3601 -- - 1 (day): last 24 hours results
3602 -- - 7 (days): last week (7 days)
3603 -- - any other value will be interpreted as
3604 -- follows:
3605 -- time of the last task execution
3606 -- MINUS
3607 -- the value of this argument.
3608 --
3609 -- RETURNS
3610 -- A clob containing the desired report.
3611 ----------------------------------------------------------------------------
3612 FUNCTION report_tuning_task_list_xml(
3613 result_limit IN NUMBER := 100,
3614 autotune_period IN NUMBER := NULL)
3615 RETURN xmltype;
3616
3617 ------------------------- report_tuning_task_xml ---------------------------
3618 -- NAME:
3619 -- report_tuning_task_xml - report a SQL tuning task xml
3620 --
3621 -- DESCRIPTION:
3622 -- This procedure is called to display the results of a tuning task
3623 -- in XML format
3624 --
3625 -- PARAMETERS:
3626 -- task_name (IN) - name of the task to report.
3627 -- level (IN) - format of the recommendations.
3628 -- Possible values are TYPICAL, BASIC, ALL.
3629 -- section (IN) - particular section in the report.
3630 -- Possible values are:
3631 -- SUMMARY,
3632 -- ALL.
3633 -- object_id (IN) - identifier of the advisor framework object
3637 -- the report is generated. Default is 160 which
3634 -- that represents a given statement in a STS
3635 -- (STS) or AWR.
3636 -- result_limit (IN) - number of statements in a STS or AWR for which
3638 -- corresponds to 20 * 8 categories of SQLs (with
3639 -- profile, index, restructure SQL, alternate
3640 -- plan, statistics, errors, informational,
3641 -- and no-findings)
3642 --
3643 -- owner_name (IN) - owner of the relevant tuning task. Defaults to
3644 -- the current schema owner.
3645 -- execution_name (IN) - name of the task execution to use. If NULL,
3646 -- the report will be generated for the last task
3647 -- execution.
3648 -- autotune_period (IN) - auto-sqltune time period. Applies only
3649 -- to automatic sql tuning advisor task.
3650 -- It is ignored if the specified task is not
3651 -- auto-sqltune's.
3652 -- Possible values are for this argument:
3653 -- - NULL or negative value (default): meaning
3654 -- all or full. The result includes all task
3655 -- executions
3656 -- - 0: result of the current/last task execution
3657 -- - 1 (day): last 24 hours results
3658 -- - 7 (days): last week (7 days)
3659 -- - any other value will be interpreted as
3660 -- follows:
3661 -- time of the last task execution
3662 -- MINUS
3663 -- the value of this argument.
3664 -- report_tag (IN) - name of the root xml tag. By default
3665 -- is the report reference generated
3666 -- by the reporting framework
3667 --
3668 -- RETURNS
3669 -- A clob containing the desired report.
3670 ----------------------------------------------------------------------------
3671 FUNCTION report_tuning_task_xml(
3672 task_name IN VARCHAR2 := NULL,
3673 level IN VARCHAR2 := LEVEL_TYPICAL,
3674 section IN VARCHAR2 := SECTION_ALL,
3675 object_id IN NUMBER := NULL,
3676 result_limit IN NUMBER := 160,
3677 owner_name IN VARCHAR2 := NULL,
3678 execution_name IN VARCHAR2 := NULL,
3679 autotune_period IN NUMBER := NULL,
3680 report_tag IN VARCHAR2 := NULL)
3681 RETURN xmltype;
3682
3683 ------------------------------ check_tuning_task_status --------------------
3684 -- NAME:
3685 -- check_tuning_task - check status of the specified tuning task
3686 --
3687 -- DESCRIPTION
3688 -- This function is called to check the status of a sqltune tuning task.
3689 -- It first checks the status of the specified task as recorded in
3690 -- the advisor framework which could have one of the following values:
3691 -- - INITIAL
3692 -- - EXECUTING
3693 -- - COMPLETED
3694 -- - INTERRUPTED
3695 -- - CANCELED
3696 -- - FATAL ERROR
3697 --
3698 -- Second, the task's status being "INITIAL" could mean that the task
3699 -- was created, but its execution is scheduled (by dbms_scheduler via
3700 -- schedule_tuning_task APIs) for later. In this case, the function
3701 -- does an extra work and checks the status/state of the scheduler job
3702 -- used to run the tuning task if any. Therefore, the status might have
3703 -- one of the following job states (see dba/all_scheduler_jobs):
3704 -- - JOB DISABLED
3705 -- - JOB SCHEDULED
3706 -- - JOB RETRY SCHEDULED
3707 -- - JOB BROKEN
3708 -- - JOB FAILED
3709 --
3710 -- PARAMETERS :
3711 -- task_name (IN) - tuning task name
3712 -- task_owner (IN) - optional tuning task owner
3713 --
3714 -- RETURNS:
3715 -- SQL tuning task status
3716 --
3717 -- EXCEPTIONS:
3718 -- To be done
3719 ----------------------------------------------------------------------------
3720 FUNCTION check_tuning_task_status(
3721 task_name IN VARCHAR2,
3722 task_owner IN VARCHAR2 := NULL)
3723 RETURN VARCHAR2;
3724
3725 -------------------------- implement_tuning_task_xml -----------------------
3726 -- NAME:
3727 -- implement_tuning_task_xml - implement one or a set of recommendations
3728 -- from a tuning task
3729 --
3730 -- DESCRIPTION:
3731 -- This function will implement one or a set of recommendations made
3732 -- by the SQL Tuning Advisor. Calling it is analogous to calling
3733 -- script_tuning_task and then running the script. This function is
3734 -- mainly called/used by EM express
3735 --
3736 -- PARAMETERS:
3737 -- task_name (IN) - name of the task to implement the rec for
3738 -- rec_type (IN) - filter the types of recs to implement.
3739 -- Currently the only one supported is 'PROFILES'.
3740 -- object_id (IN) - object identifying a given SQL statement
3741 -- owner_name (IN) - owner of the relevant tuning task. Defaults to
3742 -- the current schema owner.
3743 -- execution_name (IN) - name of the task execution to use. If NULL, the
3744 -- command will be generated for the last task
3745 -- execution.
3746 -- plan_hash (IN) - plan hash value of the plan to create the SQL
3747 -- plan baseline for.
3751 -- text after normalizing all literal values into
3748 -- category (IN) - sqltune category
3749 -- force_match (IN) - If different than 0 this causes SQL Profiles
3750 -- to target all SQs which have the same
3752 -- bind variables.
3753 -- autotune_period (IN) - auto-sqltune time period. Applies only
3754 -- to automatic sql tuning advisor task.
3755 -- It is ignored if the specified task is not
3756 -- auto-sqltune's.
3757 -- Possible values are for this argument:
3758 -- - NULL or negative value (default): meaning
3759 -- all or full. The result includes all task
3760 -- executions
3761 -- - 0: result of the current/last task execution
3762 -- - 1 (day): last 24 hours results
3763 -- - 7 (days): last week (7 days)
3764 -- - any other value will be interpreted as
3765 -- follows:
3766 -- time of the last task execution
3767 -- MINUS
3768 -- the value of this argument.
3769 -- show_sql_only (IN) - Use <> 0 if you would like to show the SQL
3770 -- without running it. The SQL begin executed
3771 -- will be returned in the XM
3772 -- RETURNS
3773 -- xml fragment with the show sql list of commands
3774 ----------------------------------------------------------------------------
3775 FUNCTION implement_tuning_task_xml(
3776 task_name IN VARCHAR2,
3777 rec_type IN VARCHAR2 := REC_TYPE_SQL_PROFILES,
3778 object_id IN NUMBER := NULL,
3779 owner_name IN VARCHAR2 := NULL,
3780 execution_name IN VARCHAR2 := NULL,
3781 plan_hash IN NUMBER := NULL,
3782 category IN VARCHAR2 := NULL,
3783 force_match IN NUMBER := 0,
3784 autotune_period IN NUMBER := NULL,
3785 show_sql_only IN NUMBER := 0)
3786 RETURN XMLTYPE;
3787
3788 ---------------------------- alter_tuning_task_xml -------------------------
3789 -- NAME:
3790 -- alter_tuning_task_xml - alter an existing tuning task
3791 --
3792 -- DESCRIPTION:
3793 -- This function is called to alter an existing SQL tuning advisor task
3794 --
3795 -- PARAMETERS:
3796 -- task_name (IN) - name of the task to alter
3797 -- action_type (IN) - type of action to perform on the task.
3798 -- The list of possible values are:
3799 -- - drop
3800 -- - interrupt
3801 -- - resume
3802 -- - cancel
3803 -- - reset
3804 --
3805 -- show_sql_only (IN) - Use <> 0 if you would like to show the SQL
3806 -- without running it. The SQL begin executed
3807 -- will be returned in the XML
3808 -- RETURNS
3809 -- xml fragment with the show sql list of commands
3810 ----------------------------------------------------------------------------
3811 FUNCTION alter_tuning_task_xml(
3812 task_name IN VARCHAR2,
3813 action_type IN VARCHAR2,
3814 show_sql_only IN NUMBER := 0)
3815 RETURN XMLTYPE;
3816
3817 --------------------------- configure_tuning_task_xml -----------------------
3818 -- NAME:
3819 -- configure_tuning_task_xml - configure an existing tuning task
3820 --
3821 -- DESCRIPTION:
3822 -- This function is called to configure an existing SQL tuning advisor
3823 -- task
3824 --
3825 -- PARAMETERS:
3826 -- task_name (IN) - name of the task to configure
3827 -- task_owner (IN) - owner of the task
3828 -- task_parameters (IN) - task parameters to update
3829 -- The list of possible values are:
3830 -- - status
3831 -- - autoimpl_status
3832 -- - persql_time_limit
3833 -- - exec_max_profiles
3834 -- - auto_max_profiles
3835 --
3836 -- show_sql_only (IN) - Use <> 0 if you would like to show the SQL
3837 -- without running it. The SQL begin executed
3838 -- will be returned in the XML
3839 -- RETURNS
3840 -- xml fragment with the show sql list of commands
3841 ----------------------------------------------------------------------------
3842 FUNCTION configure_tuning_task_xml(
3843 task_name IN VARCHAR2,
3844 task_parameters IN arglist,
3845 task_owner IN VARCHAR2 := NULL,
3846 show_sql_only IN NUMBER := 0)
3847 RETURN XMLTYPE;
3848
3849 END dbms_sqltune;
[Home] [Help]