1 package dbms_spd authid current_user is
2
3 ---------------------------------------------------------------------------
4 ---------------------------------------------------------------------------
5 /*
6 Package: DBMS_SPD
7
8 This package provides subprograms for managing Sql Plan
9 Directives(SPD). SPD are objects generated automatically by Oracle
10 server. For example, if server detects that the single table cardinality
11 estimated by optimizer is off from the actual number of rows returned
12 when accessing the table, it will automatically create a directive to
13 do dynamic sampling for the table. When any Sql statement referencing
14 the table is compiled, optimizer will perform dynamic sampling for the
15 table to get more accurate estimate.
16
17 Notes:
18
19 DBMSL_SPD is a invoker-rights package. The invoker requires ADMINISTER
20 SQL MANAGEMENT OBJECT privilege for executing most of the subprograms of
21 this package. Also the subprograms commit the current transaction (if any),
22 perform the operation and commit it again.
23
24 DBA view dba_sql_plan_directives shows all the directives created in
25 the system and the view dba_sql_plan_dir_objects displays the objects that
26 are included in the directives.
27
28 */
29 ---------------------------------------------------------------------------
30 ---------------------------------------------------------------------------
31
32
33 ---------------------------------------------------------------------------
34 -- TYPES AND CONSTANTS
35 ---------------------------------------------------------------------------
36
37 -- Default value for SPD_RETENTION_WEEKS
38 SPD_RETENTION_WEEKS_DEFAULT CONSTANT varchar2(4) := '53';
39
40
41 -- Objects in the directive
42 type ObjectElem is record (
43 owner dbms_quoted_id, -- owner of the object
44 object_name dbms_quoted_id, -- name of the object
45 object_type varchar2(6) -- 'TABLE'
46 );
47 type ObjectTab is table of ObjectElem;
48
49 ---------------------------------------------------------------------------
50 -- EXCEPTIONS
51 ---------------------------------------------------------------------------
52 /*
53 Exception: insufficient_privilege
54
55 The user does not have proper privilege to perform the operation
56 */
57 insufficient_privilege exception;
58 pragma exception_init(insufficient_privilege, -38171);
59
60 /*
61 Exception: object_does_not_exist
62
63 The specified object does not exist.
64 */
65 object_does_not_exist exception;
66 pragma exception_init(object_does_not_exist, -13158);
67
68
69 /*
70 Exception: invalid_input
71
72 The input value is not valid
73 */
74 invalid_input exception;
75 pragma exception_init(invalid_input, -28104);
76
77 /*
78 Exception: invalid_schema
79
80 The input schema does not exist
81 */
82 invalid_schema exception;
83 pragma exception_init(invalid_schema, -44001);
84
85 /*
86 Exception: table_already_exists
87
88 The specified table already exists.
89 */
90 table_already_exists exception;
91 pragma exception_init(table_already_exists, -13159);
92
93 /*
94 Exception: tablespace_missing
95
96 The specified tablespace does not exist.
97 */
98 tablespace_missing exception;
99 pragma exception_init(tablespace_missing, -29304);
100
101 /*
102 Exception: invalid_stgtab
103
104 The specified staging table is invalid or does not exist
105 */
106 invalid_stgtab exception;
107 pragma exception_init(invalid_stgtab, -19374);
108
109 ---------------------------------------------------------------------------
110 -- SUBPROGRAMS
111 ---------------------------------------------------------------------------
112
113 /*
114 Procedure: alter_sql_plan_directive
115
116 This procedure can be used to change different attributes of a
117 SQL Plan Directive.
118
119 Parameters:
120 directive_id - SQL Plan Directive id
121 attribute_name - One of the attribute names as below
122 attribute_value - Values of the above attributes as below
123
124 The following attribute(s) are supported.
125
126
127 | Attribute_name : Attribute_value : Description
128
129 | ENABLED : YES : Directive is enabled and may
130 | be used.
131 | NO : Directive is not enabled and will
132 | not be used.
133 |
134 | AUTO_DROP : YES : Directive will be dropped
135 | automatically if not
136 | used for SPD_RETENTION_WEEKS.
137 | This is the default behavior.
138 | NO : Directive will not be dropped
139 | automatically.
140
141 Exceptions:
142 - <insufficient_privilege>
143 - <object_does_not_exist>
144 - <invalid_input>
145
146 Notes:
147 "Administer SQL Management Object" privilege is required to execute
148 this procedure.
149
150 Examples:
151 | begin
152 | dbms_spd.alter_sql_plan_directive(12345, 'STATE', 'PERMANENT');
153 | end;
154
155 Returns:
156 Nothing.
157 */
158 procedure alter_sql_plan_directive(
159 directive_id number,
160 attribute_name varchar2,
161 attribute_value varchar2);
162
163 /*
164 Procedure: drop_sql_plan_directive
165
166 This procedure can be used to drop a SQL Plan Directive.
167
168 Parameters:
169 directive_id - SQL Plan Directive id
170
171 Exceptions:
172 - <insufficient_privilege>
173 - <object_does_not_exist>
174 - <invalid_input>
175
176 Notes:
177 "Administer SQL Management Object" privilege is required to execute
178 this procedure.
179
180 If null is passed for directive_id, it will drop all directives not
181 used for last SPD_RETENTION_WEEKS. The directives with AUTO_DROP set to
182 NO will not be dropped.
183
184 Examples:
185 | begin
186 | dbms_spd.drop_sql_plan_directive(12345);
187 | end;
188
189 Returns:
190 Nothing.
191 */
192 procedure drop_sql_plan_directive(
193 directive_id number);
194
195 /*
196 Procedure: flush_sql_plan_directive
197
198 This procedure allows manually flushing the Sql Plan directives that
199 are automatically recorded in SGA memory while executing sql
200 statements. The information recorded in SGA are periodically flushed
201 by oracle background processes. This procedure just provides a way to
202 flush the information manually.
203
204 Parameters:
205
206 Exceptions:
207 - <insufficient_privilege>
208
209 Notes:
210 "Administer SQL Management Object" privilege is required to execute
211 this procedure.
212
213 Examples:
214 | begin
215 | dbms_spd.flush_sql_plan_directive;
216 | end;
217
218 Returns:
219 Nothing.
220 */
221 procedure flush_sql_plan_directive;
222
223 /*
224 Procedure: create_stgtab_directive
225
226 This procedure creates a staging table to pack (export) Sql Plan
227 directives into it.
228
229 Parameters:
230 table_name - Name of staging table.
231 table_owner - Name of schema owner of staging table.
232 Default is current schema.
233 tablespace_name - Name of tablespace.
234 Default NULL means create staging table in the
235 default tablespace.
236 Exceptions:
237 - <insufficient_privilege>
238 - <invalid_input>
239 - <invalid_schema>
240 - <table_already_exists>
241 - <tablespace_missing>
242
243 Notes:
244 "Administer SQL Management Object" privilege is required to execute
245 this procedure.
246
247 Examples:
248 | begin
249 | dbms_spd.create_stgtab_directive('mydirtab');
250 | end;
251
252 Returns:
253 Nothing
254 */
255 procedure create_stgtab_directive(table_name in varchar2,
256 table_owner in varchar2 := user,
257 tablespace_name in varchar2 := null);
258
262 This procedure packs (exports) SQL Plan Directives into a staging
259 /*
260 Procedure: pack_stgtab_directive
261
263 table.
264
265 Parameters:
266 table_name - Name of staging table.
267 table_owner - Name of schema owner of staging table.
268 Default is current schema.
269 directive_id - SQL Plan Directive id
270 Default NULL means all directives in the system.
271 obj_list - This argument can be used to filter the
272 directives to be packed based on the objects used in
273 directives. if obj_list is not null, a directive is
274 packed only if all the objects in the directive
275 exists in obj_list.
276
277 Exceptions:
278 - <insufficient_privilege>
279 - <object_does_not_exist>
280 - <invalid_input>
281 - <invalid_schema>
282 - <invalid_stgtab>
283
284 Notes:
285 "Administer SQL Management Object" privilege is required to execute
286 this procedure.
287
288 Examples:
289 | -- Pack all directives in the system
290 | select dbms_spd.pack_stgtab_directive('mydirtab') from dual;
291 |
292 | set serveroutput on;
293 | -- Pack directives relevant to objects in SH schema
294 | declare
295 | my_list dbms_spd.objecttab := dbms_spd.ObjectTab();
296 | dir_cnt number;
297 | begin
298 | my_list.extend(1);
299 | my_list(1).owner := 'SH'; -- schema name
300 | my_list(1).object_name := null; -- all tables in SH
301 | my_list(1).object_type := 'TABLE'; -- type of object
302 |
303 | dir_cnt :=
304 | dbms_spd.pack_stgtab_directive('mydirtab', obj_list => my_list);
305 | dbms_output.put_line('dir_cnt = ' || dir_cnt);
306 | end;
307 |
308 | -- Pack directives relevant to tables SALES and CUSTOMERS in SH schema
309 | declare
310 | my_list dbms_spd.objecttab := dbms_spd.ObjectTab();
311 | dir_cnt number;
312 | begin
313 | my_list.extend(2);
314 |
315 | -- SALES table
316 | my_list(1).owner := 'SH';
317 | my_list(1).object_name := 'SALES';
318 | my_list(1).object_type := 'TABLE';
319 |
320 | -- CUSTOMERS table
321 | my_list(2).owner := 'SH';
322 | my_list(2).object_name := 'CUSTOMERS';
323 | my_list(2).object_type := 'TABLE';
324 |
325 | dir_cnt :=
326 | dbms_spd.pack_stgtab_directive('mydirtab', obj_list => my_list);
327 | dbms_output.put_line('dir_cnt = ' || dir_cnt);
328 | end;
329 |
330
331 Returns:
332 Number of Sql Plan Directives packed.
333 */
334 function pack_stgtab_directive(table_name in varchar2,
335 table_owner in varchar2 := user,
336 directive_id in number := null,
337 obj_list in ObjectTab := null)
338 return number;
339
340 /*
341 Procedure: unpack_stgtab_directive
342
343 This procedure unpacks (imports) SQL Plan Directives from a staging
344 table.
345
346 Parameters:
347 table_name - Name of staging table.
348 table_owner - Name of schema owner of staging table.
349 Default is current schema.
350 directive_id - SQL Plan Directive id
351 Default NULL means all directives in the staging
352 table.
353 obj_list - This argument can be used to filter the
354 directives to be unpacked based on the objects used in
355 directives. if obj_list is not null, a directive is
356 unpacked only if all the objects in the directive
357 exists in obj_list.
358
359 Exceptions:
360 - <insufficient_privilege>
361 - <object_does_not_exist>
362 - <invalid_input>
363 - <invalid_schema>
364 - <invalid_stgtab>
365
366 Notes:
367 "Administer SQL Management Object" privilege is required to execute
368 this procedure.
369
370 Examples:
371 | -- Unack all directives in the staging table
372 | select dbms_spd.unpack_stgtab_directive('mydirtab') from dual;
373 |
374 | set serveroutput on;
375 | -- Unpack directives relevant to objects in SH schema
376 | declare
377 | my_list dbms_spd.objecttab := dbms_spd.ObjectTab();
378 | dir_cnt number;
379 | begin
380 | my_list.extend(1);
381 | my_list(1).owner := 'SH'; -- schema name
382 | my_list(1).object_name := null; -- all tables in SH
383 | my_list(1).object_type := 'TABLE'; -- type of object
384 |
385 | dir_cnt :=
386 | dbms_spd.unpack_stgtab_directive('mydirtab', obj_list => my_list);
387 | dbms_output.put_line('dir_cnt = ' || dir_cnt);
388 | end;
389 |
390 | -- Unpack directives relevant to tables SALES and CUSTOMERS in SH schema
391 | declare
392 | my_list dbms_spd.objecttab := dbms_spd.ObjectTab();
393 | dir_cnt number;
394 | begin
395 | my_list.extend(2);
396 |
397 | -- SALES table
398 | my_list(1).owner := 'SH';
399 | my_list(1).object_name := 'SALES';
400 | my_list(1).object_type := 'TABLE';
401 |
402 | -- CUSTOMERS table
403 | my_list(2).owner := 'SH';
404 | my_list(2).object_name := 'CUSTOMERS';
405 | my_list(2).object_type := 'TABLE';
406 |
407 | dir_cnt :=
408 | dbms_spd.unpack_stgtab_directive('mydirtab', obj_list => my_list);
409 | dbms_output.put_line('dir_cnt = ' || dir_cnt);
410 | end;
411 |
412
413 Returns:
414 Number of Sql Plan Directives unpacked.
415 */
416 function unpack_stgtab_directive(table_name in varchar2,
417 table_owner in varchar2 := user,
418 directive_id in number := null,
419 obj_list in ObjectTab := null)
420 return number;
421
422 /*
423 Procedure: set_prefs
424
425 This procedures allows setting different preferences for Sql
426 Plan Directives.
427
428 Parameters:
429
430 pname - preference name
431 pvalue - preference value
432
433 Exceptions:
434 - <insufficient_privilege>
435 - <invalid_input>
436
437 Notes:
438 "Administer SQL Management Object" privilege is required to execute
439 this procedure.
440
441 The procedure supports the following preference.
442
443 SPD_RETENTION_WEEKS - Sql Plan Directives are purged if not used for
444 more than the value set for this preference. Default is 53
445 (SPD_RETENTION_WEEKS_DEFAULT) weeks, which means a directive is
446 purged if it has been left unused for little over a year. It can be
447 set to any value greater than or equal to 0. Also value null can be
448 passed to set the preference to default.
449
450 Examples:
451 | begin
452 | dbms_spd.set_prefs('SPD_RETENTION_WEEKS', '4');
453 | end;
454
455 Returns:
456 Nothing.
457 */
458 procedure set_prefs(pname in varchar2,
459 pvalue in varchar2);
460
461 /*
462 Function: get_prefs
463
464 This function gets the values for preferences for Sql Plan
465 Directives.
466
467 Parameters:
468
469 pname - preference name
470
471 Exceptions:
472 - <insufficient_privilege>
473 - <invalid_input>
474
475 Notes:
476 "Administer SQL Management Object" privilege is required to execute
477 this function.
478
479 The function supports the following preference.
480
481 SPD_RETENTION_WEEKS - Sql Plan Directives are purged if not used for
482 more than the value set for this preference.
483
484 Examples:
485 |
486 | select dbms_spd.get_prefs('SPD_RETENTION_WEEKS') from dual;
487 |
488
489 Returns:
490 Preference value
491 */
492 function get_prefs(pname in varchar2) return varchar2;
493
494 ------------------- FOR INTERNAL USE OF DATAPUMP ONLY --------------------
495
496 procedure transfer_spd_for_dp(
497 objlist_tabf varchar2,
498 dblinkf varchar2,
499 operation number);
500
501 ------------------- FOR INTERNAL USE OF DATAPUMP ONLY --------------------
502
503 end dbms_spd;
[Home] [Help]