1 PACKAGE dbms_sqlpa AUTHID CURRENT_USER AS
2
3 -----------------------------------------------------------------------------
4 -- global constant declarations --
5 -----------------------------------------------------------------------------
6 ERR_NO_EXEC2 CONSTANT NUMBER := -15740;
7 ERR_NO_COMPARE_EXEC CONSTANT NUMBER := -15741;
8 ERR_INV_EXEC_NAME CONSTANT NUMBER := -15742;
9
10 -----------------------------------------------------------------------------
11 -- procedure / function declarations --
12 -----------------------------------------------------------------------------
13
14 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
15 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
16 -- ----------------------------- --
17 -- MAIN PROCEDURES/FUNCTIONS --
18 -- ----------------------------- --
19 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
20 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
21
22 --------------------- create_analysis_task - sql text format ----------------
23 -- NAME:
24 -- create_analysis_task - CRATE an ANALYSIS TASK in order to process
25 -- and analyzer perfromance of a single SQL statement (sql text format)
26 --
27 -- DESCRIPTION
28 -- This function is called to prepare the analysis of a single statement
29 -- given its text. The function mainly creates an advisor task and sets
30 -- its parameters.
31 --
32 -- PARAMETERS:
33 -- sql_text (IN) - text of a SQL statement
34 -- bind_list (IN) - a set of bind values
35 -- parsing_schema (IN) - the username for who the statement will be tuned
36 -- task_name (IN) - optional analysis task name
37 -- description (IN) - maximum of 256 SQL analysis description
38 -- con_dbid (IN) - dbid of a container in a CDB. It is either
39 -- for ROOT or a pluggable database.
40 --
41 -- RETURNS:
42 -- SQL analysis task unique name
43 --
44 -- EXCEPTIONS:
45 -- To be done
46 -----------------------------------------------------------------------------
47 FUNCTION create_analysis_task(
48 sql_text IN CLOB,
49 bind_list IN sql_binds := NULL,
50 parsing_schema IN VARCHAR2 := NULL,
51 task_name IN VARCHAR2 := NULL,
52 description IN VARCHAR2 := NULL,
53 con_dbid IN NUMBER := NULL)
54 RETURN VARCHAR2;
55
56 -------------------- create_analysis_task - sql_id format -------------------
57 -- NAME:
58 -- create_analysis_task - sql_id format
59 --
60 -- DESCRIPTION
61 -- This function is called to prepare the analysis of a single statement
62 -- from the cursor cache given its identifier. The function mainly
63 -- creates an advisor task and sets its parameters.
64 --
65 -- PARAMETERS:
66 -- sql_id (IN) - identifier of the statement
67 -- plan_hash_value (IN) - hash value of the sql execution plan
68 -- task_name (IN) - optional analysis task name
69 -- description (IN) - maximum of 256 SQL analysis description
70 -- con_name (IN) - name of a container in a CDB. It is either
71 -- for ROOT or a pluggable database.
72 --
73 -- RETURNS:
74 -- SQL analysis task unique name
75 --
76 -- EXCEPTIONS:
77 -- To be done
78 -----------------------------------------------------------------------------
79 FUNCTION create_analysis_task(
80 sql_id IN VARCHAR2,
81 plan_hash_value IN NUMBER := NULL,
82 task_name IN VARCHAR2 := NULL,
83 description IN VARCHAR2 := NULL,
84 con_name IN VARCHAR2 := NULL)
85 RETURN VARCHAR2;
86
87 -------------- create_analysis_task - workload repository format ------------
88 -- NAME:
89 -- create_analysis_task - workload repository format
90 --
91 -- DESCRIPTION
92 -- This function is called to prepare the analysis of a single statement
93 -- from the workload repository given a range of snapshot identifiers.
94 -- The function mainly creates an advisor task and sets its parameters.
95 --
96 -- PARAMETERS:
97 -- begin_snap (IN) - begin snapshot identifier
98 -- end_snap (IN) - end snapshot identifier
99 -- sql_id (IN) - identifier of the statement
100 -- plan_hash_value (IN) - plan hash value
101 -- task_name (IN) - optional analysis task name
102 -- description (IN) - maximum of 256 SQL analysis description
103 -- con_name (IN) - name of a container in a CDB. It is either
104 -- for ROOT or a pluggable database.
105 --
106 -- RETURNS:
107 -- SQL analysis task unique name
108 --
109 -- EXCEPTIONS:
110 -- To be done
111 -----------------------------------------------------------------------------
112 FUNCTION create_analysis_task(
113 begin_snap IN NUMBER,
114 end_snap IN NUMBER,
115 sql_id IN VARCHAR2,
116 plan_hash_value IN NUMBER := NULL,
117 task_name IN VARCHAR2 := NULL,
118 description IN VARCHAR2 := NULL,
119 con_name IN VARCHAR2 := NULL)
120 RETURN VARCHAR2;
121
122 ---------------------- create_analysis_task - sqlset format -----------------
123 -- NAME:
124 -- create_analysis_task - sqlset format
125 --
126 -- DESCRIPTION:
127 -- This function is called to prepare the analysis of a sql tuning set.
128 -- The function mainly creates an advisor task and sets its parameters.
129 --
130 -- PARAMETERS:
131 -- sqlset_name (IN) - sqlset name
132 -- basic_filter (IN) - SQL predicate to filter the SQL from the STS
133 -- order_by (IN) - an order-by clause on the selected SQL
134 -- top_sql (IN) - top N SQL after filtering and ranking
135 -- task_name (IN) - optional analysis task name
136 -- description (IN) - maximum of 256 SQL analysis description
137 --
138 -- sqlset_owner (IN) - the owner of the sqlset, or null for current
139 -- schema owner
140 --
141 -- RETURNS:
142 -- SQL analysis task unique name
143 --
144 -- EXCEPTIONS:
145 -- To be done
146 -----------------------------------------------------------------------------
147 FUNCTION create_analysis_task(
148 sqlset_name IN VARCHAR2 := NULL,
149 basic_filter IN VARCHAR2 := NULL,
150 order_by IN VARCHAR2 := NULL,
151 top_sql IN NUMBER := NULL,
152 task_name IN VARCHAR2 := NULL,
153 description IN VARCHAR2 := NULL,
154 sqlset_owner IN VARCHAR2 := NULL)
155 RETURN VARCHAR2;
156
157 -------------------------- set_analysis_task_parameter ----------------------
158 -- NAME:
159 -- set_analysis_task_parameter - set sql analysis task parameter value
160 --
161 -- DESCRIPTION:
162 -- This procedure updates the value of a task analysis parameter
163 -- of type VARCHAR2. The possible analysis parameters that can be set
164 -- by this procedure are:
165 -- MODE : analysis scope (comprehensive, limited)
166 -- BASIC_FILTER : basic filter for sql analysis set
167 -- PLAN_FILTER : plan filter for sql tuning set (see select_sqlset
168 -- for possible values)
169 -- RANK_MEASURE1 : first ranking measure for sql analysis set
170 -- RANK_MEASURE2 : second possible ranking measure for sql analysis set
171 -- RANK_MEASURE3 : third possible ranking measure for sql analysis set
172 -- RESUME_FILTER : a extra filter for sts besides basic_filter
173 --
174 --
175 -- PARAMETERS:
176 -- task_name (IN) - identifier of the task to execute
177 -- parameter (IN) - name of the parameter to set
178 -- value (IN) - new value of the specified parameter
179 --
180 -- RETURNS:
181 -- NONE
182 --
183 -- EXCEPTIONS:
184 -- To be done
185 -----------------------------------------------------------------------------
186 PROCEDURE set_analysis_task_parameter(
187 task_name IN VARCHAR2,
188 parameter IN VARCHAR2,
189 value IN VARCHAR2);
190
191 -------------------------- set_analysis_task_parameter ----------------------
192 -- NAME:
193 -- set_analysis_task_parameter - set sql analysis task parameter value
194 --
195 -- DESCRIPTION:
196 -- This procedure updates the value of a sql analysis parameter
197 -- of type NUMBER. The possible analysis parameters that can be set
198 -- by this procedure are:
199 -- DAYS_TO_EXPIRE : number of days until the task is deleted
200 -- TIME_LIMIT : global time out
201 -- LOCAL_TIME_LIMIT : local time out
202 -- SQL_LIMIT : maximum number of sts statements to tune
203 -- SQL_PERCENTAGE : percentage filter of sts statements
204 -- EXECUTE_COUNT : multiple execution count to be used
205 -- in the test execute. We intend to execute
206 -- them multiple times in test execute.
207 --
208 -- PARAMETERS:
209 -- task_name (IN) - identifier of the task to execute
210 -- parameter (IN) - name of the parameter to set
211 -- value (IN) - new value of the specified parameter
212 --
213 -- RETURNS:
214 -- NONE
215 --
216 -- EXCEPTIONS:
217 -- To be done
218 -----------------------------------------------------------------------------
219 PROCEDURE set_analysis_task_parameter(
220 task_name IN VARCHAR2,
221 parameter IN VARCHAR2,
222 value IN NUMBER);
223
224 ----------------------- set_analysis_default_parameter ----------------------
225 -- NAME:
226 -- set_analysis_default_parameter - set sql analysis task parameter
227 -- default value
228 --
229 -- DESCRIPTION:
230 -- This procedure is called to update the DEFAULT value of an analyzer
231 -- parameter of type VARCHAR2.
232 --
233 -- PARAMETERS:
234 -- parameter (IN) - name of the parameter to set
235 -- value (IN) - new value of the specified parameter
236 --
237 -- RETURNS:
238 -- NONE
239 --
240 -- EXCEPTIONS:
241 -- To be done
242 -----------------------------------------------------------------------------
243 PROCEDURE set_analysis_default_parameter(
244 parameter IN VARCHAR2,
245 value IN VARCHAR2);
246
247 ------------------------ set_analysis_default_parameter ---------------------
248 -- NAME:
249 -- set_analysis_default_parameter - set sql analysis task parameter
250 -- default value
251 --
252 -- DESCRIPTION:
253 -- This procedure is called to update the default value of an analyzer
254 -- parameter of type NUMBER.
255 --
256 -- PARAMETERS:
257 -- parameter (IN) - name of the parameter to set
258 -- value (IN) - new value of the specified parameter
259 --
260 -- RETURNS:
261 -- NONE
262 --
263 -- EXCEPTIONS:
264 -- To be done
265 -----------------------------------------------------------------------------
266 PROCEDURE set_analysis_default_parameter(
267 parameter IN VARCHAR2,
268 value IN NUMBER);
269
270 ----------------------------- execute_analysis_task -------------------------
271 -- NAME:
272 -- execute_analysis_task - execute a sql analysis task
273 --
274 -- DESCRIPTION:
275 -- This procedure is called to execute a previously created analysis task
276 --
277 -- PARAMETERS:
278 -- task_name (IN) - identifier of the task to execute
279 -- execution_type (IN) - type of the action to perform. Possible
280 -- values are: [TEST] EXECUTE (default),
281 -- EXPLAIN [PLAN],
282 -- COMPARE [PERFORMANCE]
283 -- If NULL it will default to the value of
284 -- the DEFAULT_EXECUTION_TYPE parameter.
285 -- execution_name (IN) - A name to qualify and identify an execution.
286 -- If not specified, it be generated by
287 -- the advisor and returned by function.
288 -- execution_params (IN) - List of parameters (name, value) for
289 -- the specified execution.
290 -- Note that execution parameters are real
291 -- task parameters that have effect only on
292 -- the execution they specified for.
293 -- Example:
294 -- dbms_advisor.arglist('time_limit',
295 -- 1000,
296 -- 'COMPARE_METRIC',
297 -- 'buffer_gets * 10')
298 -- execution_desc (IN) - A 256-length string
299 --
300 -- RETURNS:
301 -- The function version returns the name of the new execution
302 -----------------------------------------------------------------------------
303 -- function flavor
304 FUNCTION execute_analysis_task(
305 task_name IN VARCHAR2,
306 execution_type IN VARCHAR2 := 'test execute',
307 execution_name IN VARCHAR2 := NULL,
308 execution_params IN dbms_advisor.argList := NULL,
309 execution_desc IN VARCHAR2 := NULL)
310 RETURN VARCHAR2;
311
312 -- procedure flavor
313 PROCEDURE execute_analysis_task(
314 task_name IN VARCHAR2,
315 execution_type IN VARCHAR2 := 'test execute',
316 execution_name IN VARCHAR2 := NULL,
317 execution_params IN dbms_advisor.argList := NULL,
318 execution_desc IN VARCHAR2 := NULL);
319
320 ----------------------------- interrupt_analysis_task -----------------------
321 -- NAME:
322 -- interrupt_analysis_task - interrupt a sql analysis task
323 --
324 -- DESCRIPTION:
325 -- This procedure interrupts the currently executing analysis task.
326 -- The task will end its operations as it would at a normal exit
327 -- so that the user will be able to access the intermediate results at
328 -- this point.
329 --
330 -- PARAMETERS:
331 -- task_name (IN) - identifier of the task to execute
332 -----------------------------------------------------------------------------
333 procedure interrupt_analysis_task(task_name IN VARCHAR2);
334
335 ----------------------------- cancel_analysis_task --------------------------
336 -- NAME:
337 -- cancel_analysis_task - cancel a sql analysis task
338 --
339 -- DESCRIPTION:
340 -- This procedure is called to cancel the currently executing analysis
341 -- task. All intermediate result data will be removed from the task.
342 --
343 -- PARAMETERS:
344 -- task_name (IN) - identifier of the task to execute
345 ----------------------------------------------------------------------------
346 PROCEDURE cancel_analysis_task(task_name IN VARCHAR2);
347
348 ----------------------------- reset_analysis_task --------------------------
349 -- NAME:
350 -- reset_analysis_task - reset a sql analysis task
351 --
352 -- DESCRIPTION:
353 -- This procedure resets an analysis task to its initial state.
354 -- All intermediate result data will be deleted. Call this procedure on
355 -- a task that is not currently executing.
356 --
357 -- PARAMETERS:
358 -- task_name (IN) - identifier of the task to reset
359 -----------------------------------------------------------------------------
360 PROCEDURE reset_analysis_task(task_name IN VARCHAR2);
361
362 ------------------------------- drop_analysis_task --------------------------
363 -- NAME:
364 -- drop_analysis_task - drop a sql analysis task
365 --
366 -- DESCRIPTION:
367 -- This procedure is called to drop a SQL analysis task.
368 -- The task and All its result data will be deleted.
369 --
370 -- PARAMETERS:
371 -- task_name (IN) - identifier of the task to execute
372 -----------------------------------------------------------------------------
373 PROCEDURE drop_analysis_task(task_name IN VARCHAR2);
374
375 ----------------------------- resume_analysis_task --------------------------
376 -- NAME:
377 -- resume_analysis_task - resume a sql analysis task
378 --
379 -- DESCRIPTION:
380 -- This procedure resumes a previously interrupted task execution.
381 --
382 -- PARAMETERS:
383 -- task_name (IN) - identifier of the task to execute
384 -- basic_filter (IN) - a SQL predicate to filter the SQL from a STS.
385 -- Note that this filter will be applied in
386 -- conjunction with the basic filter
387 -- (i.e., parameter basic_filter) specified
388 -- when calling create_analysis_task.
389 -- RETURNS:
390 -- NONE
391 --
392 -- EXCEPTIONS:
393 -- To be done
394 -----------------------------------------------------------------------------
395 PROCEDURE resume_analysis_task(
396 task_name IN VARCHAR2,
397 basic_filter IN VARCHAR2 := NULL);
398
399 ------------------------------- report_analysis_task ------------------------
400 -- NAME:
401 -- report_analysis_task - report a SQL analysis task
402 --
403 -- DESCRIPTION:
404 -- This procedure is called to display the results of a analysis task.
405 --
406 -- PARAMETERS:
407 -- task_name (IN) - name of the task to report.
408 -- type (IN) - type of the report.
409 -- Possible values are: TEXT (default), HTML, XML.
410 -- level (IN) - format of the recommendations. Possible values:
411 -- BASIC - currently, same as typical
412 -- TYPICAL(default)- SQL with perf. changes+errors
413 -- ALL - details of all SQL
414 -- IMPROVED - only improved SQL
415 -- REGRESSED - only regressed SQL
416 -- CHANGED - only SQL with changed perf.
417 -- UNCHANGED - only SQL with unchanged perf.
418 -- CHANGED_PLANS - only SQL with plan changes
419 -- UNCHANGED_PLANS - only SQL with unchanged plans
420 -- ERRORS - SQL with errors only
421 -- section (IN) - particular section in the report.
422 -- Possible values are:
423 -- SUMMARY (default) - workload summary only
424 -- ALL - summary + details on SQL
425 -- object_id (IN) - identifier of the advisor framework object that
426 -- represents a given SQL in a tuning set (STS).
427 -- top_sql (IN) - number of statements in a STS for which the
428 -- report is generated.
429 -- execution_name (IN) - name of the task execution to use. If NULL, the
430 -- report will be generated for the last task
431 -- execution.
432 -- task_owner (IN) - owner of the relevant analysis task.
433 -- Defaults to the current schema owner.
434 -- order_by (IN) - how to sort SQL statements in the report
435 -- (summary and body). Possible values are:
436 -- + NULL (default) : order by impact on workload
437 -- + workload_impact: same as null
438 -- + sql_impact : order by change impact on SQL
439 -- + metric_delta/change_diff: order by change
440 -- difference in SQL perfomance in terms
441 -- of the Comparison Metric.
442 -- RETURNS
443 -- A clob containing the desired report.
444 --
445 -- NOTE:
446 -- So far order_by can be used only one report is generated for
447 -- a comparison and not for a single test execute or explain plan.
448 -----------------------------------------------------------------------------
449 FUNCTION report_analysis_task(
450 task_name IN VARCHAR2,
451 type IN VARCHAR2 := 'text',
452 level IN VARCHAR2 := 'typical',
453 section IN VARCHAR2 := 'summary',
454 object_id IN NUMBER := NULL,
455 top_sql IN NUMBER := 100,
456 execution_name IN VARCHAR2 := NULL,
457 task_owner IN VARCHAR2 := NULL,
458 order_by IN VARCHAR2 := NULL)
459 RETURN clob;
460
461 -----------------------------------------------------------------------------
462 -- NAME:
463 -- get_sess_optimizer_env - get session optimizer env
464 --
465 -- DESCRIPTION:
466 -- This function is a callout function to get the compilation
467 -- environment from the session for a remote SPA trial. The CE
468 -- itself is returned in its compact linear representation as a
469 -- RAW data type.
470 --
471 -- PARAMETERS:
472 -- NONE
473 --
474 -- RETURNS
475 -- A raw containing the compilation environment
476 --
477 -----------------------------------------------------------------------------
478 FUNCTION get_sess_optimizer_env
479 RETURN RAW;
480
481 ----------------------------------------------------------------------------
482 -- --
483 -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! --
484 -- !!! UNDOCUMENTED FUNCTIONS AND PROCEDURES. FOR INTERNAL USE ONLY !!! --
485 -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! --
486 -- --
487 ----------------------------------------------------------------------------
488 --
489 ----------------------------------------------------------------------------
490 -- procedure / function declarations --
491 ----------------------------------------------------------------------------
492 ----------------------------- remote_process_sql ---------------------------
493 PROCEDURE remote_process_sql(
494 sql_text IN CLOB,
495 parsing_schema IN VARCHAR2,
496 bind_data IN RAW,
497 bind_list IN SQL_BINDS,
498 action IN VARCHAR2,
499 time_limit IN NUMBER,
500 plan_hash1 OUT NUMBER,
501 buffer_gets OUT NUMBER,
502 cpu_time OUT NUMBER,
503 elapsed_time OUT NUMBER,
504 disk_reads OUT NUMBER,
505 disk_writes OUT NUMBER,
506 rows_processed OUT NUMBER,
507 optimizer_cost OUT NUMBER,
508 parse_time OUT NUMBER,
509 err_code OUT NUMBER,
510 err_mesg OUT VARCHAR2,
511 trace_flags IN BINARY_INTEGER := 0,
512 extra_res OUT NOCOPY VARCHAR2,
513 other_xml IN OUT NOCOPY VARCHAR2,
514 physical_read_requests OUT NUMBER,
515 physical_write_requests OUT NUMBER,
516 physical_read_bytes OUT NUMBER,
517 physical_write_bytes OUT NUMBER,
518 user_io_time OUT NUMBER,
519 plan_hash2 OUT NUMBER,
520 io_interconnect_bytes OUT NUMBER,
521 action_flags IN BINARY_INTEGER := 0,
522 control_options_xml IN VARCHAR2 := NULL,
523 con_dbid IN NUMBER := NULL,
524 con_name OUT VARCHAR2);
525
526
527 END dbms_sqlpa;
[Home] [Help]