1 package dbms_stats authid current_user is
2
3 --
4 -- This package provides a mechanism for users to view and modify
5 -- optimizer statistics gathered for database objects.
6 -- The statistics can reside in two different locations:
7 -- 1) in the dictionary
8 -- 2) in a table created in the user's schema for this purpose
9 -- Only statistics stored in the dictionary itself will have an
10 -- impact on the cost-based optimizer.
11 --
12 -- This package also facilitates the gathering of some statistics
13 -- in parallel.
14 --
15 -- The package is divided into three main sections:
16 -- 1) procedures which set/get individual stats.
17 -- 2) procedures which transfer stats between the dictionary and
18 -- user stat tables.
19 -- 3) procedures which gather certain classes of optimizer statistics
20 -- and have improved (or equivalent) performance characteristics as
21 -- compared to the analyze command.
22 --
23 -- Most of the procedures include the three parameters: statown,
24 -- stattab, and statid.
25 -- These parameters are provided to allow users to store statistics in
26 -- their own tables (outside of the dictionary) which will not affect
27 -- the optimizer. Users can thereby maintain and experiment with "sets"
28 -- of statistics without fear of permanently changing good dictionary
29 -- statistics. The stattab parameter is used to specify the name of a
30 -- table in which to hold statistics and is assumed to reside in the same
31 -- schema as the object for which statistics are collected (unless the
32 -- statown parameter is specified). Users may create
33 -- multiple such tables with different stattab identifiers to hold separate
34 -- sets of statistics. Additionally, users can maintain different sets of
35 -- statistics within a single stattab by making use of the statid
36 -- parameter (which can help avoid cluttering the user's schema).
37 --
38 -- For all of the set/get procedures, if stattab is not provided (i.e., null),
39 -- the operation will work directly on the dictionary statistics; therefore,
40 -- users need not create these statistics tables if they only plan to
41 -- modify the dictionary directly. However, if stattab is not null,
42 -- then the set/get operation will work on the specified user statistics
43 -- table, not the dictionary.
44 --
45 -- lock_*_stats/unlock_*_stats procedures: When statistics on a table is
46 -- locked, all the statistics depending on the table, including table
47 -- statistics, column statistics, histograms and statistics on all
48 -- dependent indexes, are considered to be locked.
49 -- set_*, delete_*, import_*, gather_* procedures that modify statistics
50 -- in dictionary of an individual table/index/column will raise an error
51 -- if statistics of the object is locked. Procedures that operates on
52 -- multiple objects (eg: gather_schema_stats) will skip modifying the
53 -- statistics of an object if it is locked. Most of the procedures have
54 -- force argument to override the lock.
55 --
56 -- Whenever statistics in dictionary are modified, old versions of statistics
57 -- are saved automatically for future restoring. Statistics can be restored
58 -- using RESTORE procedures. These procedures use a time stamp as an argument
59 -- and restore statistics as of that time stamp.
60 -- There are dictionary views that display the time of statistics
61 -- modifications. These views are useful in determining the time stamp to
62 -- be used for statistics restoration.
63 --
64 -- Catalog view DBA_OPTSTAT_OPERATIONS contain history of
65 -- statistics operations performed at schema and database level
66 -- using DBMS_STATS.
67 --
68 -- The views *_TAB_STATS_HISTORY views (ALL, DBA, or USER) contain
69 -- history of table statistics modifications.
70 --
71 -- The old statistics are purged automatically at regular intervals based on
72 -- the statistics history retention setting and the time of the recent
73 -- analyze of the system. Retention is configurable using the
74 -- ALTER_STATS_HISTORY_RETENTION procedure. The default value is 31 days,
75 -- which means that you would be able to restore the optimizer statistics to
76 -- any time in last 31 days.
77 -- Automatic purging is enabled when STATISTICS_LEVEL parameter is set
78 -- to TYPICAL or ALL. If automatic purging is disabled, the old versions
79 -- of statistics need to be purged manually using the PURGE_STATS procedure.
80 --
81 -- Other related functions:
82 -- GET_STATS_HISTORY_RETENTION: This function can be used to get the
83 -- current statistics history retention value.
84 -- GET_STATS_HISTORY_AVAILABILITY: This function gets the oldest time stamp
85 -- where statistics history is available. Users cannot restore statistics
86 -- to a time stamp older than the oldest time stamp.
87 --
88 --
89 -- When a dbms_stats subprogram modifies or deletes the statistics
90 -- for an object, all the dependent cursors are invalidated by
91 -- default and corresponding statements are subject to recompilation
92 -- next time so that new statistics have immediate effects. This
93 -- behavior can be altered with the no_invalidate argument when
94 -- applicable.
95 --
96 -- Extended Statistics: This package allows you to collect statistics for
97 -- column groups and expressions (known as "statistics extensions"). The
98 -- statistics collected for column groups and expressions are called
99 -- "extended statistics". Statistics on Column groups are used by optimizer for
100 -- accounting correlation between columns. For example, if query has predicates
101 -- c1=1 and c2=1 and if there are statistics on (c1, c2), optimizer will use
102 -- this statistics for estimating the combined selectivity of the predicates.
103 -- The expression statistics are used by optimizer for estimating selectivity
104 -- of predicates on those expressions. The extended statistics are similar to
105 -- column statistics and the procedures that take columns names will accept
106 -- extension names in place of column names.
107 --
108 -- The following procedures can be used for managing extensions.
109 -- create_extended_stats - create extensions manually or based on
110 -- groups of columns seen in workload.
111 -- drop_extended_stats - drop an extension
112 -- show_extended_stats_name - show name of an extension
113 --
114 -- seed_col_usage - record usage of column (group)s in a
115 -- workload
116 -- reset_col_usage - delete recorded column (group)s usage
117 -- information
118 -- report_col_usage - generate a report of column group(s)
119 -- usage.
120 --
121 -- Comparing statistics:
122 --
123 -- diff_table_stats_* functions can be used to compare statistics for a table
124 -- from two different sources. The statistics can be from
125 --
126 -- - two different user statistics tables
127 -- - a single user statistics table containing two sets of
128 -- statistics that can be identified using statid's
129 -- - a user statistics table and dictionary
130 -- - history
131 -- - pending statistics
132
133 -- The functions also compares the statistics of the dependent objects
134 -- (indexes, columns, partitions).
135 -- They displays statistics of the object(s) from both sources if the
136 -- difference between those statistics exceeds a certain threshold (%).
137 -- The threshold can be specified as an argument to the function, with
138 -- a default of 10%.
139 -- The statistics corresponding to the first source (stattab1 or time1)
140 -- will be used as basis for computing the diff percentage.
141 --
142 -- Pending Statistics:
143 --
144 -- Optimizer statistics are gathered and saved in a pending state for tables
145 -- that have FALSE value for the PUBLISH preference (see set_*_prefs()).
146 -- The default value of the PUBLISH preference is TRUE.
147 -- Pending statistics can be published, exported, or deleted.
148 -- See the section corresponding to each of these procedures for details.
149 --
150 -- Pending statistics are not used by the Query Optimizer unless parameter
151 -- optimizer_use_pending_statistics is set to TRUE (system or session level).
152 -- The default value of this parameter is FALSE.
153 -- Pending statistics provide a mechanism to verify the impact of the new
154 -- statistics on query plans before making them available for general use.
155 --
156 -- There are two scenarios to verify the query plans:
157 -- 1. export the pending statistics (use export_pending_stats) to a test
158 -- system, then run the query workload and check the performance or plans.
159 -- 2. set optimizer_use_pending_statistics to TRUE in a session on the system
160 -- where pending statistics have been gathered, run the workload, and
161 -- check the performance or plans.
162 --
163 -- Once the performance or query plans have been verified, the pending
164 -- statistics can be published (run publish_pending_stats) if the performance
165 -- are acceptable or delete (run delete_pending_stats) if not.
166 --
167 -- Related procedures:
168 -- publish_pending_stats
169 -- export_pending_stats
170 -- delete_pending_stats
171 --
172 -- Calibration Statistics (Processing Rates):
173 --
174 -- The calibration statistics or the processing rates are added as part of the
175 -- AutoDOP project. They are used to compute the DOP based on the CPU
176 -- cost of the operators in the plan. These stats are exposed using the view
177 -- v$optimizer_processing_rate. The PL/SQL procedures which can be used
178 -- to manipulate them are set_processing_rate(), delete_processing_rate() and
179 -- gather_processing_rate(). There are three sources to these stats:
180 -- default (default values), manual (set by the user using
181 -- set_processing_rate()), calibration (values obtained by running
182 -- gather_processing_rate()).
183 --
184 -- Nearly all the procedures in this package (more specifically, the
185 -- set_*, delete_*, export_*, import_*, gather_*, and *_stat_table
186 -- procedures) commit the current transaction, perform the operation,
187 -- and then commit again.
188 --
189
190 -- types for minimum/maximum values and histogram endpoints
191 type numarray is varray(2050) of number;
192 type datearray is varray(2050) of date;
193 type chararray is varray(2050) of varchar2(4000);
194 type rawarray is varray(2050) of raw(2000);
195 type fltarray is varray(2050) of binary_float;
196 type dblarray is varray(2050) of binary_double;
197
198 type StatRec is record (
199 epc number,
200 minval raw(2000),
201 maxval raw(2000),
202 bkvals numarray,
203 novals numarray,
204 chvals chararray,
205 eavals rawarray,
206 rpcnts numarray,
207 eavs number);
208
209 -- type for objects whose statistics may be gathered
210 -- make sure to maintain satisfy_obj_filter when ObjectElem type
211 -- is changed
212 type ObjectElem is record (
213 ownname dbms_quoted_id, -- owner
214 objtype varchar2(6), -- 'TABLE' or 'INDEX'
215 objname dbms_quoted_id, -- table/index
216 partname dbms_quoted_id, -- partition
217 subpartname dbms_quoted_id -- subpartition
218 );
219 type ObjectTab is table of ObjectElem;
220
221
222 -- type for displaying stats difference report
223 type DiffRepElem is record (
224 report clob, -- stats difference report
225 maxdiffpct number); -- max stats difference (percentage)
226 type DiffRepTab is table of DiffRepElem;
227
228 -- type for gather_table_stats context -- internal only
229 type CContext is varray(30) of varchar2(4000);
230 CCTX_SIZE CONSTANT NUMBER := 30;
231
232 -- oracle decides whether to collect stats for indexes or not
233 AUTO_CASCADE CONSTANT BOOLEAN := null;
234
235 -- oracle decides when to invalidate dependend cursors
236 AUTO_INVALIDATE CONSTANT BOOLEAN := null;
237
238 -- constant used to indicate auto sample size algorithms should
239 -- be used.
240 AUTO_SAMPLE_SIZE CONSTANT NUMBER := 0;
241
242 -- constant to indicate use of the system default degree of
243 -- parallelism determined based on the initialization parameters.
244 DEFAULT_DEGREE CONSTANT NUMBER := 32767;
245 -- force serial execution if the object is relatively small.
246 -- use the system default degree of parallelism otherwise.
247 AUTO_DEGREE CONSTANT NUMBER := 32768;
248 -- DEFAULT_DEGREE_VALUE is defined as 32766;
249 -- NVL_TABPREF_AUTO_DEGREE (used in prvtstat.sql) is defined as 32765;
250 -- make sure any new degree values do not conflict with the existing
251 -- values
252
253 -- constant used to specify that we want the table cached block
254 -- to be automatically computed
255 AUTO_TABLE_CACHED_BLOCKS CONSTANT INTEGER := 0;
256
257
258 --
259 -- Default values for key parameters passed to dbms_stats procedures
260 -- These values are specified in the DEFAULT clause when declaring the
261 -- corresponding parameter in any of the dbms_stats procedures.
262 --
263 DEFAULT_CASCADE CONSTANT BOOLEAN := null;
264 DEFAULT_DEGREE_VALUE CONSTANT NUMBER := 32766;
265 DEFAULT_ESTIMATE_PERCENT CONSTANT NUMBER := 101;
266 DEFAULT_METHOD_OPT CONSTANT VARCHAR2(1) := 'Z';
267 DEFAULT_NO_INVALIDATE CONSTANT BOOLEAN := null;
268 DEFAULT_GRANULARITY CONSTANT VARCHAR2(1) := 'Z';
269 DEFAULT_PUBLISH CONSTANT BOOLEAN := true;
270 DEFAULT_INCREMENTAL CONSTANT BOOLEAN := false;
271 DEFAULT_STALE_PERCENT CONSTANT NUMBER := 10;
272 DEFAULT_AUTOSTATS_TARGET CONSTANT VARCHAR2(1) := 'Z';
273 DEFAULT_STAT_CATEGORY CONSTANT VARCHAR2(100) := 'Z';
274 DEFAULT_DEL_STAT_CATEGORY CONSTANT VARCHAR2(100) := 'OBJECT_STATS, SYNOPSES';
275 -- delete both object stats and synopses
276 DEFAULT_OPTIONS CONSTANT VARCHAR2(1) := 'Z';
277
278 -- options in transfer_stats
279 ADD_GLOBAL_PREFS CONSTANT NUMBER := 1;
280 -- transfer global preferences etc
281
282 --
283 -- Defining the options for EXPORT_STATS_FOR_DP
284 --
285 DP_OPTIONS_FULL CONSTANT NUMBER := ADD_GLOBAL_PREFS;
286 -- full data pump export or import
287
288 -- Constant which is used as an indicator that purge_stats should
289 -- purge everything (i.e., truncate) in stats history tables.
290 PURGE_ALL CONSTANT TIMESTAMP WITH TIME ZONE :=
291 TO_TIMESTAMP_TZ('1001-01-0101:00:00-00:00','YYYY-MM-DDHH:MI:SSTZH:TZM');
292
293 -- Constant which is used for reclaiming synopsis table space
294 RECLAIM_SYNOPSIS CONSTANT TIMESTAMP WITH TIME ZONE :=
295 TO_TIMESTAMP_TZ('1002-01-0101:00:00-00:00','YYYY-MM-DDHH:MI:SSTZH:TZM');
296 --
297 -- This set of procedures enable the storage and retrieval of
298 -- individual column-, index-, table- and system- related statistics
299 --
300 -- The procedures are:
301 --
302 -- prepare_column_values*
303 --
304 -- set_column_stats
305 -- set_index_stats
306 -- set_table_stats
307 -- set_system_stats
308 --
309 -- convert_raw_value*
310 --
311 -- get_column_stats
312 -- get_index_stats
313 -- get_table_stats
314 -- get_system_stats
315 --
316 -- delete_column_stats
317 -- delete_index_stats
318 -- delete_table_stats
319 -- delete_schema_stats
320 -- delete_database_stats
321 -- delete_system_stats
322 -- delete_fixed_objects_stats
323 -- delete_dictionary_stats
324 --
325
326 procedure prepare_column_values(
327 srec in out NOCOPY StatRec, charvals chararray);
328 pragma restrict_references(prepare_column_values, WNDS, RNDS, WNPS, RNPS);
329 procedure prepare_column_values(
330 srec in out NOCOPY StatRec, datevals datearray);
331 pragma restrict_references(prepare_column_values, WNDS, RNDS, WNPS, RNPS);
332 procedure prepare_column_values(
333 srec in out NOCOPY StatRec, numvals numarray);
334 pragma restrict_references(prepare_column_values, WNDS, RNDS, WNPS, RNPS);
335 procedure prepare_column_values(
336 srec in out NOCOPY StatRec, fltvals fltarray);
337 pragma restrict_references(prepare_column_values, WNDS, RNDS, WNPS, RNPS);
338 procedure prepare_column_values(
339 srec in out NOCOPY StatRec, dblvals dblarray);
340 pragma restrict_references(prepare_column_values, WNDS, RNDS, WNPS, RNPS);
341 procedure prepare_column_values(
342 srec in out NOCOPY StatRec, rawvals rawarray);
343 pragma restrict_references(prepare_column_values, WNDS, RNDS, WNPS, RNPS);
344 procedure prepare_column_values_nvarchar(
345 srec in out NOCOPY StatRec, nvmin nvarchar2, nvmax nvarchar2);
346 pragma restrict_references(prepare_column_values, WNDS, RNDS, WNPS, RNPS);
347 procedure prepare_column_values_rowid(
348 srec in out NOCOPY StatRec, rwmin rowid, rwmax rowid);
349 pragma restrict_references(prepare_column_values, WNDS, RNDS, WNPS, RNPS);
350 --
351 -- Convert user-specified minimum, maximum, and histogram endpoint
352 -- datatype-specific values into Oracle's internal representation
353 -- for future storage via set_column_stats.
354 --
355 -- Generic input arguments:
356 -- srec.epc - The number of values specified in charvals, datevals,
357 -- numvals, or rawvals. This value must be between 2 and 2050 inclusive.
358 -- Should be set to 2 for procedures which don't allow histogram
359 -- information (nvarchar and rowid).
360 -- The first corresponding array entry should hold the minimum
361 -- value for the column and the last entry should hold the maximum.
362 -- If there are more than two entries, then all the others hold the
363 -- remaining histogram endpoint values
364 -- (with in-between values ordered from next-smallest to next-largest).
365 -- This value may be adjusted to account for compression, so the
366 -- returned value should be left as is for a call to set_column_stats.
367 -- srec.bkvals - If a frequency or hybrid histogram is desired, this array
368 -- contains the number of occurrences of each distinct value specified in
369 -- charvals, datevals, numvals, or rawvals. Otherwise, it is merely an
370 -- output argument and must be set to null when this procedure is
371 -- called.
372 -- srec.rpcnts - If a hybrid histogram is desired, this array contains
373 -- the total frequency of values that are less than or equal to each
374 -- distinct value specified in charvals, datevals, numvals, or rawvals.
375 -- Otherwise, it is merely an output argument and must be set to null
376 -- when this procedure is called.
377 -- As an example, for a given array numvals with numvals(i)=4,
378 -- rpcnts(i)=13 means that there are 13 rows in the column which
379 -- are less than or equal to 4.
380 --
381 -- ** Note that whenever srec.rpcnts is populated, srec.bkvals must be
382 -- populated as described above, too.
383 --
384 -- ** Also note that, whenever bkvals and/or rpcnts are populated, there
385 -- should not be any duplicates in charvals, datevals, numvals, or
386 -- rawvals.
387 --
388 --
389 -- Datatype specific input arguments (one of these):
390 -- charvals - The array of values when the column type is character-based.
391 -- Up to the first 64 bytes of each string should be provided.
392 -- Arrays must have between 2 and 2050 entries, inclusive.
393 -- If the datatype is fixed char, the strings must be space padded
394 -- to 15 characters for correct normalization.
395 -- datevals - The array of values when the column type is date-based.
396 -- numvals - The array of values when the column type is numeric-based.
397 -- rawvals - The array of values when the column type is raw.
398 -- Up to the first 64 bytes of each strings should be provided.
399 -- nvmin,nvmax - The minimum and maximum values when the column type
400 -- is national character set based (NLS). No histogram information
401 -- can be provided for a column of this type.
402 -- If the datatype is fixed char, the strings must be space padded
403 -- to 15 characters for correct normalization.
404 -- rwmin,rwmax - The minimum and maximum values when the column type
405 -- is rowid. No histogram information can be provided for a column
406 -- of this type.
407 --
408 -- Output arguments:
409 -- srec.minval - Internal representation of the minimum which is
410 -- suitable for use in a call to set_column_stats.
411 -- srec.maxval - Internal representation of the maximum which is
412 -- suitable for use in a call to set_column_stats.
413 -- srec.bkvals - array suitable for use in a call to set_column_stats.
414 -- srec.novals - array suitable for use in a call to set_column_stats.
415 -- srec.eavals - array suitable for use in a call to set_column_stats.
416 -- srec.rpcnts - array suitable for use in a call to set_column_stats.
417 --
418 -- Exceptions:
419 -- ORA-20001: Invalid or inconsistent input values
420 --
421
422 procedure set_param(
423 pname in varchar2,
424 pval in varchar2);
425 --
426 -- WARNING ** WARNING ---> obsoleted <--- WARNING ** WARNING
427 -- Please use SET_GLOBAL_PREFS() instead.
428 -- WARNING ** WARNING ---> obsoleted <--- WARNING ** WARNING
429 --
430 -- This procedure can be used to set default value for parameters
431 -- of dbms_stats procedures.
432 --
433 -- The function get_param can be used to get the current
434 -- default value of a parameter.
435 --
436 -- To run this procedure, you must have the SYSDBA OR
437 -- both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.
438 --
439 -- Input arguments:
440 -- pname - parameter name
441 -- The default value for following parameters can be set.
445 -- DEGREE
442 -- CASCADE - The default value for CASCADE set by set_param
443 -- is not used by export/import procedures.
444 -- It is used only by gather procedures.
446 -- ESTIMATE_PERCENT
447 -- METHOD_OPT
448 -- NO_INVALIDATE
449 -- GRANULARITY
450 -- AUTOSTATS_TARGET
451 -- This parameter is applicable only for auto stats
452 -- collection. The value of this parameter controls
453 -- the objects considered for stats collection
454 -- It takes the following values
455 -- 'ALL' -- statistics collected
456 -- for all objects in system
457 -- 'ORACLE' -- statistics collected
458 -- for all oracle owned objects
459 -- 'AUTO' -- oracle decide which objects
460 -- to collect stats
461 -- pval - parameter value.
462 -- if null is specified, it will set the oracle default value
463 --
464 -- Notes:
465 -- Both arguments are of type varchar2 and values are enclosed in quotes,
466 -- even when they represent numbers
467 --
468 -- Examples:
469 -- dbms_stats.set_param('CASCADE','DBMS_STATS.AUTO_CASCADE');
470 -- dbms_stats.set_param('ESTIMATE_PERCENT','5');
471 -- dbms_stats.set_param('DEGREE','NULL');
472 --
473 -- Exceptions:
474 -- ORA-20000: Insufficient privileges
475 -- ORA-20001: Invalid or Illegal input values
476 --
477
478 function get_param(
479 pname in varchar2)
480 return varchar2;
481 --
482 -- WARNING ** WARNING ---> obsoleted <--- WARNING ** WARNING
483 -- Please use GET_PREFS() instead.
484 -- WARNING ** WARNING ---> obsoleted <--- WARNING ** WARNING
485 --
486 -- Get default value of parameters of dbms_stats procedures
487 --
488 -- Input arguments:
489 -- pname - parameter name
490 --
491 -- Exceptions:
492 -- ORA-20001: Invalid input values
493 --
494
495 procedure reset_param_defaults;
496 --
497 -- WARNING ** WARNING ---> obsoleted <--- WARNING ** WARNING
498 -- Please use RESET_GLOBAL_PREF_DEFAULTS() instead.
499 -- WARNING ** WARNING ---> obsoleted <--- WARNING ** WARNING
500 --
501 -- This procedure resets the default of parameters to ORACLE
502 -- recommended values.
503 --
504
505 procedure reset_global_pref_defaults;
506 --
507 -- This procedure resets the global preference to the default values
508 --
509
510 procedure set_global_prefs(
511 pname varchar2,
512 pvalue varchar2);
513 --
514 -- This procedure is used to set the global statistics preferences.
515 -- This setting is honored only of there is no preference specified
516 -- for the table to be analyzed.
517 --
518 -- To run this procedure, you need to have the SYSDBA OR
519 -- both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.
520 --
521 -- Input arguments:
522 -- pname - preference name
523 -- The default value for following preferences can be set.
524 -- CASCADE
525 -- DEGREE
526 -- ESTIMATE_PERCENT
527 -- METHOD_OPT
528 -- NO_INVALIDATE
529 -- GRANULARITY
530 -- PUBLISH
531 -- INCREMENTAL
532 -- INCREMENTAL_LEVEL
533 -- INCREMENTAL_STALENESS
534 -- GLOBAL_TEMP_TABLE_STATS
535 -- STALE_PERCENT
536 -- AUTOSTATS_TARGET
537 -- CONCURRENT
538 -- TABLE_CACHED_BLOCKS
539 -- OPTIONS
540 -- STAT_CATEGORY
541 -- WAIT_TIME_TO_UPDATE_STATS
542 --
543 -- pvalue - preference value.
544 -- if null is specified, it will set the oracle default value.
545 --
546 --
547 -- CASCADE: Please see CASCADE in gather_table_stats
548 --
549 -- DEGREE: Please see DEGREE in gather_table_stats
550 --
551 -- ESTIMATE_PERCENT: Please see ESTIMATE_PERCENT in gather_table_stats
552 --
553 -- METHOD_OPT: Please see METHOD_OPT in gather_table_stats
554 --
555 -- NO_INVALIDATE: Please see NO_INVALIDATE in gather_table_stats
556 --
557 -- GRANULARITY: Please see GRANULARITY in gather_table_stats
558 --
559 -- PUBLISH: The "PUBLISH" value determines whether or not newly gathered
560 -- statistics will be published once the gather job has completed.
561 -- Prior to 11g, once a statistic gathering job completed, the new
562 -- statistics were automatically published into the dictionary tables.
563 -- The user now has the ability to gather statistics but not publish
564 -- them immediately. This allows the DBA to test the new statistics
565 -- before publishing them.
566 --
567 -- INCREMENTAL: The "INCREMENTAL" value determines whether or not the global
568 -- statistics of a partitioned table will be maintained without doing a
569 -- full table scan. With partitioned tables it is very common to load new
570 -- data into a new partition. As new partitions are added and data loaded,
571 -- the global table statistics need to be kept up to date. Oracle will
572 -- update the global table statistics by scanning only the partitions that
576 -- (3) the user specifies AUTO_SAMPLE_SIZE for estimate_percent and AUTO for
573 -- have been changed instead of the entire table if the following conditions
574 -- hold: (1) the INCREMENTAL value for the partitioned table is set to TRUE;
575 -- (2) the PUBLISH value for the partitioned table is set to TRUE; and
577 -- granularity when gathering statistics on the table.
578 -- If the INCREMENTAL value for the partitioned table was set to FALSE
579 -- (default value), then a full table scan would be used to maintain the
580 -- global statistics.
581 --
582 -- INCREMENTAL_LEVEL: INCREMENTAL_LEVEL controls what synopses to collect when
583 -- 'INCREMENTAL' preference is set to 'TRUE'. It takes two values:
584 -- table -- table level synopses are gathered. This is used when user
585 -- wants to exchange this table with a partition. User can run
586 -- gather_table_stats on this table with 'INCREMENTAL' to 'TRUE'
587 -- and 'INCREMENTAL_LEVEL' to 'TABLE' before the exchange. Then table
588 -- level synopses are gathered on this table (for now we only support
589 -- table level synopses on non partitoned table). Then we do the
590 -- exchange. After the exchange, the partition will have synopses
591 -- which come from the table level synopses of the table before
592 -- exchange. This preference value can be only used in set_table_prefs.
593 -- It is not allowed in set_global/database/schema_prefs.
594 -- partition -- partition level synopses are gathered. This is the
595 -- default value. If 'partition' is set on a non partitioned table,
596 -- no synopses will be gathered.
597 --
598 -- INCREMENTAL_STALENESS: INCREMENTAL_STALENESS controls how we decide
599 -- a partition or subpartition as stale. It takes an enumeration of
600 -- values, i.e., 'USE_STALE_PERCENT', 'USE_LOCKED_STATS'. Multiple values
601 -- are allowed, e.g., 'USE_STALE_PERCENT, USE_LOCKED_STATS'
602 -- 'USE_STALE_PERCENT': a partition/subpartition is NOT considered as
603 -- stale if DML changes are less the stale_percent preference value
604 -- 'USE_LOCKED_STATS': locked partitions/subpartitions stats are NOT
605 -- considered as stale, regardless of dml changes
606 -- null - this is the default value, meaning a partition/subpartition
607 -- is considered as stale as long as it has any DML changes. When the
608 -- default value is used, statistics gathered in incremental mode are
609 -- guaranteed to be the same as the statistics gathered in non incremental
610 -- mode. When a non default value is used, the statistics gathered in
611 -- incremental mode might be less accurate than those gathered in non-
612 -- incremental mode.
613 -- example of usage case:
614 -- case 1: stale_percent is 10; null is specified for incrmental_
615 -- staleness; a partition has 5% of DML changes. Statistics of the
616 -- partition are regathered
617 -- case 2: stale_percent is 10; 'use_stale_percent' is specified; a
618 -- partition has 5% of DML changes. Statistis of the partition are
619 -- NOT regathered
620 -- case 3: stale_percent is 10; 'use_stale_percent' is specified;
621 -- a partition has 20% of DML changes; the partition is also locked.
622 -- The partition is considered as stale. Since it is locked, statistics
623 -- are not regathered. We fall back to the non incremental mode for
624 -- global statistics gathering
625 -- case 4: stale_percent is 10; 'use_stale_percent' is specified; a
626 -- partition has 5% of DML changes; the partition is also locked.
627 -- The partition is not considered as stale and its statistics are
628 -- not gathered. Its existing statistics are used to derive global
629 -- statistics
630 -- case 5: stale_percent is 10; 'use_locked_stats, use_stale_percent' is
631 -- specified; a partition has 20% of DML changes; the partition is also
632 -- locked. The partition is NOT considered as stale. Its existing statistics
633 -- are used to derive global statistics
634 --
635 -- GLOBAL_TEMP_TABLE_STATS: GLOBAL_TEMP_TABLE_STATS controls whether
636 -- the statistics gathered for a global temporary table should be stored
637 -- as shared statistics or session statistics. It takes two values:
638 -- 'SHARED': all sessions can see the statistics
639 -- 'SESSION': only the session in which the statistics are collected can
640 -- see the statistics
641 --
642 -- STALE_PERCENT: The "STALE_PERCENT" value determines the percentage of rows
643 -- in a table that have to change before the statistics on that table are
644 -- deemed stale and should be regathered. The default value is 10%.
645 --
646 -- AUTOSTATS_TARGET
647 -- This preference is applicable only for auto stats collection. The value
648 -- of this parameter controls the objects considered for stats collection.
649 -- It takes the following values
650 -- 'ALL' -- statistics collected for all objects in system
651 -- 'ORACLE' -- statistics collected for all oracle owned objects
652 -- 'AUTO' -- oracle decide which objects to collect stats
653 --
654 -- CONCURRENT
655 -- This preference determines whether the statistics of tables or
656 -- (sub)partitions of tables to be gathered concurrently when user issues
657 -- gather_*_stats procedures. DBMS_STATS has the ability to collect
658 -- statistics for a single object (table, (sub)partition) in parallel
659 -- based on the value of degree parameter. However the parallelism is
660 -- limited to one object. CONCURRENT preference extends the scope of
661 -- "parallelization" to multiple database objects by enabling users to
662 -- concurrently gather statistics for multiple tables in a schema/database
666 --
663 -- and multiple (sub)partitions within a table. Note that this is primarily
664 -- intented for multi cpu systems and it may not be suitable for small
665 -- databases on single cpu machines.
667 -- To gather statistics concurrently,
668 -- 1. The user must have DBA role or have the following privileges in
669 -- addition to privileges that are required for gathering statistics.
670 -- CREATE JOB, MANAGE SCHEDULER, MANAGE ANY QUEUE
671 -- 2. Resource Manager should be enabled.
672 -- 3. job_queue_processes parameter should be at least 4.
673 --
674 -- The preference takes the following values.
675 --
676 -- MANUAL: Concurrency is enabled only for manual statistics gathering.
677 -- AUTOMATIC: Concurrency is enabled only for the auto statistics gathering.
678 -- ALL: Concurrency is enabled for all statistics gathering calls.
679 -- OFF: Concurrency is disabled (default).
680 --
681 -- TABLE_CACHED_BLOCKS
682 -- The average number of blocks cached in the buffer cache for any table
683 -- we can assume when gathering the index clustering factor.
684 --
685 --
686 -- OPTIONS
687 -- The preference determines the 'options' parameter used in
688 -- gather_table_stats
689 -- The preference takes two values:
690 -- 'GATHER' (default) - Gather statistics for all objects in the table
691 -- 'GATHER AUTO' - Gather statistics for objects having missing or empty
692 -- statistics
693 -- We recommend setting 'GATHER AUTO' on tables that undergo bulk loads.
694 -- Statistics gathering on load will automatically gather statistics during
695 -- bulk load. Gather_table_stats on these tables with 'gather auto' options
696 -- will skip regathering the already fresh statistics.
697 --
698 -- STAT_CATEGORY
699 -- The preferece determines category of statistics that will be exported or
700 -- imported using export_*_stats/import_*_stats/datapump. It accepts multiple
701 -- values separated by comma. The values we support now are 'OBJECT_STATS'
702 -- (i.e., table statistics, column statistics and index statistics) and
703 -- 'SYNOPSES'. However synopses can only be exported/imported along with
704 -- OBJECT_STATS. Therefore only valid combinations are
705 -- OBJECT_STATS (default value)
706 -- OBJECT_STATS, SYNOPSES
707 -- Note that the preference is not used in delete_*_stats procedures.
708 -- WAIT_TIME_TO_UPDATE_STATS
709 -- This preference specifies the wait time, in minutes, before
710 -- timing out for locks and pins required for updating statistics.
711 -- It accepts values in the range, [0, 65535]. The default value is
712 -- 15 minutes. The special value 0 can be used to get the locks and
713 -- pins in no-wait mode.
714 --
715 -- Notes:
716 -- Both arguments are of type varchar2 and values are enclosed in quotes,
717 -- even when they represent numbers
718 --
719 -- Examples:
720 -- dbms_stats.set_global_prefs('ESTIMATE_PERCENT','9');
721 -- dbms_stats.set_global_prefs('DEGREE','99');
722 --
723 -- Exceptions:
724 -- ORA-20000: Insufficient privileges
725 -- ORA-20001: Invalid or Illegal input values
726 --
727
728 function get_prefs(
729 pname in varchar2,
730 ownname in varchar2 default null,
731 tabname in varchar2 default null)
732 return varchar2;
733 --
734 --
735 -- Get default value of the specified preference.
736 -- If the ownname and tabname are provided and a preference has been entered
737 -- for the table then it returns the preference as specified for the table.
738 -- In all other cases it returns the global preference, in case it has been
739 -- specified, otherwise the default value is returned.
740 --
741 -- Input arguments:
742 -- pname - preference name
743 -- The default value for following preferences can be retrieved.
744 -- CASCADE
745 -- DEGREE
746 -- ESTIMATE_PERCENT
747 -- METHOD_OPT
748 -- NO_INVALIDATE
749 -- GRANULARITY
750 -- PUBLISH
751 -- INCREMENTAL
752 -- INCREMENTAL_LEVEL
753 -- INCREMENTAL_STALENESS
754 -- GLOBAL_TEMP_TABLE_STATS
755 -- STALE_PERCENT
756 -- AUTOSTATS_TARGET
757 -- CONCURRENT
758 -- TABLE_CACHED_BLOCKS
759 -- OPTIONS
760 -- STAT_CATEGORY
761 -- WAIT_TIME_TO_UPDATE_STATS
762 --
763 -- ownname - owner name
764 -- tabname - table name
765 --
766 --
767 -- Exceptions:
768 -- ORA-20001: Invalid input values
769 --
770
771 procedure set_table_prefs(
772 ownname varchar2,
773 tabname varchar2,
774 pname varchar2,
775 pvalue varchar2);
776 --
777 -- This procedure is used to set the statistics preferences of the
778 -- specified table in the specified schema.
779 --
780 -- To run this procedure, you need to connect as owner of the table
781 -- or be granted ANALYZE ANY system privilege.
782 --
783 -- Input arguments:
784 -- ownname - owner name
785 -- tabname - table name
786 -- pname - preference name
787 -- The default value for following preferences can be set.
788 -- CASCADE
789 -- DEGREE
790 -- ESTIMATE_PERCENT
791 -- METHOD_OPT
795 -- INCREMENTAL
792 -- NO_INVALIDATE
793 -- GRANULARITY
794 -- PUBLISH
796 -- INCREMENTAL_LEVEL
797 -- INCREMENTAL_STALENESS
798 -- GLOBAL_TEMP_TABLE_STATS
799 -- STALE_PERCENT
800 -- TABLE_CACHED_BLOCKS
801 -- OPTIONS
802 -- STAT_CATEGORY
803 --
804 -- pvalue - preference value.
805 -- if null is specified, it will set the oracle default value.
806 --
807 -- Notes:
808 -- All arguments are of type varchar2 and values are enclosed in quotes,
809 -- even when they represent numbers
810 --
811 -- Examples:
812 -- dbms_stats.set_table_prefs('SH', 'SALES', 'CASCADE',
813 -- 'DBMS_STATS.AUTO_CASCADE');
814 -- dbms_stats.set_table_prefs('SH', 'SALES', 'ESTIMATE_PERCENT','9');
815 -- dbms_stats.set_table_prefs('SH', 'SALES', 'DEGREE','99');
816 --
817 -- Exceptions:
818 -- ORA-20000: Insufficient privileges
819 -- ORA-20001: Invalid or Illegal input values
820 --
821
822 procedure delete_table_prefs(
823 ownname varchar2,
824 tabname varchar2,
825 pname varchar2);
826 --
827 -- This procedure is used to delete the statistics preferences of the
828 -- specified table in the specified schema.
829 --
830 -- To run this procedure, you need to connect as owner of the table
831 -- or be granted ANALYZE ANY system privilege.
832 --
833 -- Input arguments:
834 -- ownname - owner name
835 -- tabname - table name
836 -- pname - preference name
837 -- The default value for following preferences can be deleted.
838 -- CASCADE
839 -- DEGREE
840 -- ESTIMATE_PERCENT
841 -- METHOD_OPT
842 -- NO_INVALIDATE
843 -- GRANULARITY
844 -- PUBLISH
845 -- INCREMENTAL
846 -- INCREMENTAL_LEVEL
847 -- INCREMENTAL_STALENESS
848 -- GLOBAL_TEMP_TABLE_STATS
849 -- STALE_PERCENT
850 -- TABLE_CACHED_BLOCKS
851 -- OPTIONS
852 -- STAT_CATEGORY
853 --
854 -- Notes:
855 -- All arguments are of type varchar2 and values are enclosed in quotes.
856 --
857 -- Examples:
858 -- dbms_stats.delete_table_prefs('SH', 'SALES', 'CASCADE');
859 -- dbms_stats.delete_table_prefs('SH', 'SALES', 'DEGREE');
860 --
861 -- Exceptions:
862 -- ORA-20000: Insufficient privileges
863 -- ORA-20001: Invalid or Illegal input values
864 --
865
866
867 procedure export_table_prefs(
868 ownname varchar2,
869 tabname varchar2,
870 stattab varchar2,
871 statid varchar2 default null,
872 statown varchar2 default null);
873 --
874 -- This procedure is used to export the statistics preferences of the
875 -- specified table in the specified schema into the specified statistics
876 -- table.
877 --
878 -- To run this procedure, you need to connect as owner of the table
879 -- or be granted ANALYZE ANY system privilege.
880 --
881 -- Input arguments:
882 -- ownname - owner name
883 -- tabname - table name
884 -- stattab - statistics table name where to export the statistics
885 -- statid - (optional) identifier to associate with these statistics
886 -- within stattab.
887 -- statown - The schema containing stattab (if different then ownname)
888 --
889 -- Notes:
890 -- All arguments are of type varchar2 and values are enclosed in quotes.
891 --
892 -- Examples:
893 -- dbms_stats.export_table_prefs('SH', 'SALES', 'MY_STAT_TAB');
894 --
895 -- Exceptions:
896 -- ORA-20000: Object does not exist or insufficient privileges
897 --
898
899
900 procedure import_table_prefs(
901 ownname varchar2,
902 tabname varchar2,
903 stattab varchar2,
904 statid varchar2 default null,
905 statown varchar2 default null);
906 --
907 -- This procedure is used to set the statistics preferences of the
908 -- specified table in the specified schema.
909 --
910 -- To run this procedure, you need to connect as owner of the table
911 -- or be granted ANALYZE ANY system privilege.
912 --
913 -- Input arguments:
914 -- ownname - owner name
915 -- tabname - table name
916 -- stattab - The user stat table identifier describing from where
917 -- to retrieve the statistics.
918 -- statid - (optional) identifier to associate with these statistics
919 -- within stattab.
920 -- statown - The schema containing stattab (if different then ownname)
921 --
922 -- Notes:
923 -- All arguments are of type varchar2 and values are enclosed in quotes.
924 --
925 -- Examples:
926 -- dbms_stats.import_table_prefs('SH', 'SALES', 'MY_STAT_TAB');
927 --
928 -- Exceptions:
929 -- ORA-20000: Insufficient privileges
930 -- ORA-20000: Schema "<schema>" does not exist
931 --
932
933
934 procedure set_schema_prefs(
935 ownname varchar2,
936 pname varchar2,
937 pvalue varchar2);
938 --
939 -- This procedure is used to set the statistics preferences of all
940 -- the tables owned by the specified owner name.
941 --
942 -- To run this procedure, you need to connect as owner, have the SYSDBA
946 -- ownname - owner name
943 -- privilege, OR have the ANALYZE ANY system privilege
944 --
945 -- Input arguments:
947 -- pname - preference name
948 -- The default value for following preferences can be set.
949 -- CASCADE
950 -- DEGREE
951 -- ESTIMATE_PERCENT
952 -- METHOD_OPT
953 -- NO_INVALIDATE
954 -- GRANULARITY
955 -- PUBLISH
956 -- INCREMENTAL
957 -- INCREMENTAL_LEVEL
958 -- INCREMENTAL_STALENESS
959 -- GLOBAL_TEMP_TABLE_STATS
960 -- STALE_PERCENT
961 -- TABLE_CACHED_BLOCKS
962 -- OPTIONS
963 -- STAT_CATEGORY
964 --
965 -- pvalue - preference value.
966 -- if null is specified, it will set the oracle default value.
967 -- Notes:
968 -- All arguments are of type varchar2 and values are enclosed in quotes,
969 -- even when they represent numbers
970 --
971 -- Examples:
972 -- dbms_stats.set_schema_prefs('SH', 'CASCADE',
973 -- 'DBMS_STATS.AUTO_CASCADE');
974 -- dbms_stats.set_schema_prefs('SH' 'ESTIMATE_PERCENT','9');
975 -- dbms_stats.set_schema_prefs('SH', 'DEGREE','99');
976 --
977 -- Exceptions:
978 -- ORA-20000: Insufficient privileges
979 -- ORA-20000: Schema "<schema>" does not exist
980 -- ORA-20001: Invalid or Illegal input values
981 --
982
983
984 procedure delete_schema_prefs(
985 ownname varchar2,
986 pname varchar2);
987 --
988 -- This procedure is used to delete the statistics preferences of all
989 -- the tables owned by the specified owner name.
990 --
991 -- To run this procedure, you need to connect as owner, have the SYSDBA
992 -- privilege, OR have the ANALYZE ANY system privilege
993 --
994 -- Input arguments:
995 -- ownname - owner name
996 -- pname - preference name
997 -- The default value for following preferences can be deleted.
998 -- CASCADE
999 -- DEGREE
1000 -- ESTIMATE_PERCENT
1001 -- METHOD_OPT
1002 -- NO_INVALIDATE
1003 -- GRANULARITY
1004 -- PUBLISH
1005 -- INCREMENTAL
1006 -- INCREMENTAL_LEVEL
1007 -- INCREMENTAL_STALENESS
1008 -- GLOBAL_TEMP_TABLE_STATS
1009 -- STALE_PERCENT
1010 -- TABLE_CACHED_BLOCKS
1011 -- OPTIONS
1012 -- STAT_CATEGORY
1013 --
1014 -- Notes:
1015 -- All arguments are of type varchar2 and values are enclosed in quotes.
1016 --
1017 -- Examples:
1018 -- dbms_stats.delete_schema_prefs('SH', 'CASCADE');
1019 -- dbms_stats.delete_schema_prefs('SH', 'ESTIMATE_PERCENT');
1020 -- dbms_stats.delete_schema_prefs('SH', 'DEGREE');
1021 --
1022 -- Exceptions:
1023 -- ORA-20000: Insufficient privileges
1024 -- ORA-20000: Schema "<schema>" does not exist
1025 -- ORA-20001: Invalid or Illegal input values
1026 --
1027
1028
1029 procedure export_schema_prefs(
1030 ownname varchar2,
1031 stattab varchar2,
1032 statid varchar2 default null,
1033 statown varchar2 default null);
1034 --
1035 -- This procedure is used to export the statistics preferences of all
1036 -- the tables owner by the specified owner name.
1037 --
1038 -- To run this procedure, you need to connect as owner, have the SYSDBA
1039 -- privilege, OR have the ANALYZE ANY system privilege
1040 --
1041 -- Input arguments:
1042 -- ownname - owner name
1043 -- stattab - statistics table name where to export the statistics
1044 -- statid - (optional) identifier to associate with these statistics
1045 -- within stattab.
1046 -- statown - The schema containing stattab (if different then ownname)
1047 --
1048 -- Notes:
1049 -- All arguments are of type varchar2 and values are enclosed in quotes.
1050 --
1051 -- Examples:
1052 -- dbms_stats.export_schema_prefs('SH', 'MY_STAT_TAB');
1053 --
1054 -- Exceptions:
1055 -- ORA-20000: Insufficient privileges
1056 -- ORA-20000: Schema "<schema>" does not exist
1057 --
1058
1059
1060 procedure import_schema_prefs(
1061 ownname varchar2,
1062 stattab varchar2,
1063 statid varchar2 default null,
1064 statown varchar2 default null);
1065 --
1066 -- This procedure is used to import the statistics preferences of all
1067 -- the tables owner by the specified owner name.
1068 --
1069 -- To run this procedure, you need to connect as owner, have the SYSDBA
1070 -- privilege, OR have the ANALYZE ANY system privilege
1071 --
1072 -- Input arguments:
1073 -- ownname - owner name
1074 -- stattab - The user stat table identifier describing from where
1075 -- to retrieve the statistics.
1076 -- statid - (optional) identifier to associate with these statistics
1077 -- within stattab.
1078 -- statown - The schema containing stattab (if different from ownname)
1079 --
1080 -- Notes:
1081 -- All arguments are of type varchar2 and values are enclosed in quotes.
1082 --
1083 -- Examples:
1084 -- dbms_stats.import_schema_prefs('SH', 'MY_STAT_TAB');
1085 --
1086 -- Exceptions:
1087 -- ORA-20000: Insufficient privileges
1091
1088 -- ORA-20000: Schema "<schema>" does not exist
1089 --
1090
1092 procedure set_database_prefs(
1093 pname varchar2,
1094 pvalue varchar2,
1095 add_sys boolean default false);
1096 --
1097 -- This procedure is used to set the statistics preferences of all
1098 -- the tables, excluding the tables owned by Oracle. These tables
1099 -- can by included by passing TRUE for the add_sys parameter.
1100 --
1101 -- To run this procedure, you need to have the SYSDBA role OR both
1102 -- ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges.
1103 --
1104 -- Input arguments:
1105 -- pname - preference name
1106 -- The default value for following preferences can be set.
1107 -- CASCADE
1108 -- DEGREE
1109 -- ESTIMATE_PERCENT
1110 -- METHOD_OPT
1111 -- NO_INVALIDATE
1112 -- GRANULARITY
1113 -- PUBLISH
1114 -- INCREMENTAL
1115 -- INCREMENTAL_LEVEL
1116 -- INCREMENTAL_STALENESS
1117 -- GLOBAL_TEMP_TABLE_STATS
1118 -- STALE_PERCENT
1119 -- TABLE_CACHED_BLOCKS
1120 -- OPTIONS
1121 -- STAT_CATEGORY
1122 --
1123 -- pvalue - preference value.
1124 -- if null is specified, it will set the oracle default value.
1125 -- add_sys - value TRUE will include the Oracle-owned tables
1126 --
1127 -- Notes:
1128 -- All arguments are of type varchar2 and values are enclosed in quotes,
1129 -- even when they represent numbers.
1130 --
1131 -- Examples:
1132 -- dbms_stats.set_database_prefs('CASCADE', 'DBMS_STATS.AUTO_CASCADE');
1133 -- dbms_stats.set_database_prefs('ESTIMATE_PERCENT','9');
1134 -- dbms_stats.set_database_prefs('DEGREE','99');
1135 --
1136 -- Exceptions:
1137 -- ORA-20000: Insufficient privileges
1138 -- ORA-20001: Invalid or Illegal input values
1139 --
1140
1141
1142 procedure delete_database_prefs(
1143 pname varchar2,
1144 add_sys boolean default false);
1145 --
1146 -- This procedure is used to delete the statistics preferences of
1147 -- all the tables, excluding the tables owned by Oracle. These
1148 -- tables can by included by passing TRUE for the add_sys parameter.
1149 --
1150 -- To run this procedure, you need to have the SYSDBA role OR both
1151 -- ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges.
1152 --
1153 -- Input arguments:
1154 -- pname - preference name
1155 -- The default value for following preferences can be deleted.
1156 -- CASCADE
1157 -- DEGREE
1158 -- ESTIMATE_PERCENT
1159 -- METHOD_OPT
1160 -- NO_INVALIDATE
1161 -- GRANULARITY
1162 -- PUBLISH
1163 -- INCREMENTAL
1164 -- INCREMENTAL_LEVEL
1165 -- INCREMENTAL_STALENESS
1166 -- GLOBAL_TEMP_TABLE_STATS
1167 -- STALE_PERCENT
1168 -- TABLE_CACHED_BLOCKS
1169 -- OPTIONS
1170 -- STAT_CATEGORY
1171 --
1172 -- add_sys - value TRUE will include the Oracle-owned tables
1173 --
1174 -- Notes:
1175 -- All arguments are of type varchar2 and values are enclosed in quotes.
1176 --
1177 -- Examples:
1178 -- dbms_stats.delete_database_prefs('CASCADE', false);
1179 -- dbms_stats.delete_database_prefs('ESTIMATE_PERCENT',true);
1180 --
1181 -- Exceptions:
1182 -- ORA-20000: Insufficient privileges
1183 -- ORA-20001: Invalid or Illegal input values
1184 --
1185
1186
1187 procedure export_database_prefs(
1188 stattab varchar2,
1189 statid varchar2 default null,
1190 statown varchar2 default null,
1191 add_sys boolean default false);
1192 --
1193 -- This procedure is used to export the statistics preferences of
1194 -- all the tables, excluding the tables owned by Oracle. These
1195 -- tables can by included by passing TRUE for the add_sys parameter.
1196 --
1197 -- To run this procedure, you need to have the SYSDBA role OR both
1198 -- ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges.
1199 --
1200 -- Input arguments:
1201 -- stattab - statistics table name where to export the statistics
1202 -- statid - (optional) identifier to associate with these statistics
1203 -- within stattab.
1204 -- statown - The schema containing stattab. If null, then it defaults
1205 -- to the current user
1206 -- add_sys - value TRUE will include the Oracle-owned tables
1207 --
1208 -- Examples:
1209 -- dbms_stats.export_database_prefs('MY_STAT_TAB', statown=>'SH');
1210 --
1211 -- Exceptions:
1212 -- ORA-20000: Insufficient privileges
1213 --
1214
1215
1216 procedure import_database_prefs(
1217 stattab varchar2,
1218 statid varchar2 default null,
1219 statown varchar2 default null,
1220 add_sys boolean default false);
1221 --
1222 -- This procedure is used to import the statistics preferences of
1223 -- all the tables, excluding the tables owned by Oracle. These
1224 -- tables can by included by passing TRUE for the add_sys parameter.
1225 --
1226 -- To run this procedure, you need to have the SYSDBA role OR both
1227 -- ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges.
1228 --
1229 -- Input arguments:
1230 -- stattab - The user stat table identifier describing from where
1231 -- to retrieve the statistics.
1235 -- add_sys - value TRUE will include the Oracle-owned tables
1232 -- statid - (optional) identifier to associate with these statistics
1233 -- within stattab.
1234 -- statown - The schema containing stattab
1236 -- Examples:
1237 -- dbms_stats.import_database_prefs('MY_STAT_TAB', statown=>'SH');
1238 --
1239 -- Exceptions:
1240 -- ORA-20000: Insufficient privileges
1241 --
1242
1243 function to_no_invalidate_type(no_invalidate varchar2) return boolean;
1244 procedure init_package;
1245
1246 procedure publish_pending_stats(
1247 ownname varchar2 default null,
1248 tabname varchar2,
1249 no_invalidate boolean default
1250 to_no_invalidate_type(get_param('NO_INVALIDATE')),
1251 force boolean default FALSE);
1252 --
1253 -- This procedure is used to publish the statistics gathered and stored
1254 -- as pending.
1255 -- If the parameter TABNAME is null then publish applies to all tables
1256 -- of the specified schema.
1257 -- The default owner/schema is the user who runs the procedure.
1258 --
1259 -- To run this procedure, you need to have the privilge to collect stats
1260 -- for the tables that will be touched by this procedure.
1261 --
1262 -- Input arguments:
1263 -- ownname - owner name
1264 -- tabname - table name
1265 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
1266 -- The procedure invalidates the dependent cursors immediately
1267 -- if set to FALSE.
1268 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
1269 -- invalidate dependend cursors. This is the default. The default
1270 -- can be changed using set_param procedure.
1271 -- force - to override the lock (TRUE will override the lock).
1272 --
1273 -- Notes:
1274 -- All arguments are of type varchar2 and values are enclosed in quotes.
1275 --
1276 -- Examples:
1277 -- dbms_stats.publish_pending_stats('SH', null);
1278 --
1279 -- Exceptions:
1280 -- ORA-20000: Insufficient privileges
1281 --
1282
1283 procedure export_pending_stats(
1284 ownname varchar2 default null,
1285 tabname varchar2,
1286 stattab varchar2,
1287 statid varchar2 default null,
1288 statown varchar2 default null);
1289 --
1290 -- This procedure is used to export the statistics gathered and stored
1291 -- as pending.
1292 --
1293 -- If the parameter TABNAME is null then export applies to all tables
1294 -- of the specified schema.
1295 -- The default owner/schema is the user who runs the procedure.
1296 --
1297 -- To run this procedure, you need to have the SYSDBA role OR both
1298 -- ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges.
1299 --
1300 -- Input arguments:
1301 -- ownname - owner name
1302 -- tabname - table name
1303 -- stattab - statistics table name where to export the statistics
1304 -- statid - (optional) identifier to associate with these statistics
1305 -- within stattab.
1306 -- statown - The schema containing stattab (if different from ownname)
1307 --
1308 -- Notes:
1309 -- All arguments are of type varchar2 and values are enclosed in quotes.
1310 --
1311 -- Examples:
1312 -- dbms_stats.export_pending_stats(null, null, 'MY_STAT_TAB');
1313 --
1314 -- Exceptions:
1315 -- ORA-20000: Insufficient privileges
1316 --
1317
1318 procedure delete_pending_stats(
1319 ownname varchar2 default null,
1320 tabname varchar2 default null);
1321 --
1322 -- This procedure is used to delete the pending statistics that have
1323 -- been gathered but not published yet, i.e, stored as pending.
1324 --
1325 -- If the parameter TABNAME is null then delete applies to all tables
1326 -- of the specified schema.
1327 -- The default owner/schema is the user who runs the procedure.
1328 --
1329 -- To run this procedure, you need to have the SYSDBA role OR both
1330 -- ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges.
1331 --
1332 -- Input arguments:
1333 -- ownname - owner name
1334 -- tabname - table name
1335 --
1336 -- Notes:
1337 -- All arguments are of type varchar2 and values are enclosed in quotes.
1338 --
1339 -- Examples:
1340 -- dbms_stats.delete_pending_stats('SH', 'SALES');
1341 --
1342 -- Exceptions:
1343 -- ORA-20000: Insufficient privileges
1344 --
1345
1346
1347 procedure publish_pending_system_stats;
1348 --
1349 -- This procedure is used to publish the system statistics which has been
1350 -- gathered and stored as pending.
1351 --
1352 -- To run this procedure, you need to have the gather_system_statistics role.
1353 --
1354 -- Input argument:
1355 -- None.
1356 --
1357 -- Examples:
1358 -- dbms_stats.publish_pending_system_stats;
1359 --
1360 -- Exceptions:
1361 -- ORA-20000: Insufficient privileges
1362 --
1363
1364 procedure export_pending_system_stats(
1365 stattab varchar2,
1366 statid varchar2 default null,
1367 statown varchar2 default null);
1368 -- Ths procedure is used to export the system statistics that has been
1369 -- gathered and stored as pending to the specified table.
1370 --
1371 -- To run this procedure, you need to have the gather_system_statistics role.
1372 --
1373 -- Input arguments:
1374 -- stattab - statistics table name where to export the statistics. Stattab
1375 -- cannot be null.
1376 -- statid - (optional) identifier to associate with these statistics
1377 -- within stattab.
1378 -- statown - The schema containing stattab. If null, then it defaults to
1379 -- the current user
1383 --
1380 --
1381 -- Notes:
1382 -- All arguments are of type varchar2 and values are enclosed in quotes.
1384 -- Examples:
1385 -- dbms_stats.export_pending_system_stats('MY_STAT_TAB', null, null);
1386 --
1387 -- Exceptions:
1388 -- ORA-20000: Insufficient privileges
1389 --
1390
1391 procedure delete_pending_system_stats;
1392 -- This procedure is used to delete the pendnig system statistics that
1393 -- has been gathered but not published yet, i.e. stored as pending.
1394 --
1395 -- To run this procedure, you need to have the gather_system_statistics role.
1396 --
1397 -- Input Arguments:
1398 -- None.
1399 --
1400 -- Examples:
1401 -- dbms_stats.delete_pending_system_stats;
1402 --
1403 -- Exceptions:
1404 -- ORA-20000: Insufficient privileges
1405 --
1406
1407
1408 procedure resume_gather_stats;
1409 --
1410 -- This procedure is used to resume statistics gathering at the point
1411 -- where it was interrupted. Statistics gathering can be interrupted
1412 -- as a result of a user action or a system event.
1413 --
1414 -- To run this procedure, you need to have the SYSDBA role OR both
1415 -- ANALYZE ANY DICTIONARY and ANALYZE ANY system privileges.
1416 --
1417 -- Input arguments:
1418 -- None.
1419 --
1420 -- Examples:
1421 -- dbms_stats.resume_gather_stats();
1422 --
1423 -- Exceptions:
1424 -- ORA-20000: Insufficient privileges
1425 --
1426
1427 procedure set_column_stats(
1428 ownname varchar2, tabname varchar2, colname varchar2,
1429 partname varchar2 default null,
1430 stattab varchar2 default null, statid varchar2 default null,
1431 distcnt number default null, density number default null,
1432 nullcnt number default null, srec StatRec default null,
1433 avgclen number default null, flags number default null,
1434 statown varchar2 default null,
1435 no_invalidate boolean default
1436 to_no_invalidate_type(get_param('NO_INVALIDATE')),
1437 force boolean default FALSE);
1438
1439 procedure set_column_stats(
1440 ownname varchar2, tabname varchar2, colname varchar2,
1441 partname varchar2 default null,
1442 stattab varchar2 default null, statid varchar2 default null,
1443 ext_stats raw,
1444 stattypown varchar2 default null,
1445 stattypname varchar2 default null,
1446 statown varchar2 default null,
1447 no_invalidate boolean default
1448 to_no_invalidate_type(get_param('NO_INVALIDATE')),
1449 force boolean default FALSE);
1450 --
1451 -- Set column-related information
1452 --
1453 -- Input arguments:
1454 -- ownname - The name of the schema
1455 -- tabname - The name of the table to which this column belongs
1456 -- colname - The name of the column or extension
1457 -- partname - The name of the table partition in which to store
1458 -- the statistics. If the table is partitioned and partname
1459 -- is null, the statistics will be stored at the global table
1460 -- level.
1461 -- stattab - The user stat table identifier describing where
1462 -- to store the statistics. If stattab is null, the statistics
1463 -- will be stored directly in the dictionary.
1464 -- statid - The (optional) identifier to associate with these statistics
1465 -- within stattab (Only pertinent if stattab is not NULL).
1466 -- distcnt - The number of distinct values
1467 -- density - The column density. If this value is null and distcnt is
1468 -- not null, density will be derived from distcnt.
1469 -- nullcnt - The number of nulls
1470 -- srec - StatRec structure filled in by a call to prepare_column_values
1471 -- or get_column_stats.
1472 -- avgclen - The average length for the column (in bytes)
1473 -- flags - For internal Oracle use (should be left as null)
1474 -- statown - The schema containing stattab (if different then ownname)
1475 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
1476 -- The procedure invalidates the dependent cursors immediately
1477 -- if set to FALSE.
1478 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
1479 -- invalidate dependend cursors. This is the default. The default
1480 -- can be changed using set_param procedure.
1481 -- force - set the values even if statistics of the object is locked
1482 --
1483 -- Input arguments for user-defined statistics:
1484 -- ext_stats - external (user-defined) statistics
1485 -- stattypown - owner of statistics type associated with column
1486 -- stattypname - name of statistics type associated with column
1487 --
1488 -- Exceptions:
1489 -- ORA-20000: Object does not exist or insufficient privileges
1490 -- ORA-20001: Invalid or inconsistent input values
1491 -- ORA-20002: Bad user statistics table, may need to upgrade it
1492 -- ORA-20005: object statistics are locked
1493 --
1494
1495
1496 procedure set_index_stats(
1497 ownname varchar2, indname varchar2,
1498 partname varchar2 default null,
1499 stattab varchar2 default null, statid varchar2 default null,
1500 numrows number default null, numlblks number default null,
1501 numdist number default null, avglblk number default null,
1502 avgdblk number default null, clstfct number default null,
1503 indlevel number default null, flags number default null,
1504 statown varchar2 default null,
1505 no_invalidate boolean default
1506 to_no_invalidate_type(get_param('NO_INVALIDATE')),
1507 guessq number default null,
1511
1508 cachedblk number default null,
1509 cachehit number default null,
1510 force boolean default FALSE);
1512 procedure set_index_stats(
1513 ownname varchar2, indname varchar2,
1514 partname varchar2 default null,
1515 stattab varchar2 default null, statid varchar2 default null,
1516 ext_stats raw,
1517 stattypown varchar2 default null,
1518 stattypname varchar2 default null,
1519 statown varchar2 default null,
1520 no_invalidate boolean default
1521 to_no_invalidate_type(get_param('NO_INVALIDATE')),
1522 force boolean default FALSE);
1523 --
1524 -- Set index-related information
1525 -- Input arguments:
1526 -- ownname - The name of the schema
1527 -- indname - The name of the index
1528 -- partname - The name of the index partition in which to store
1529 -- the statistics. If the index is partitioned and partname
1530 -- is null, the statistics will be stored at the global index
1531 -- level.
1532 -- stattab - The user stat table identifier describing where
1533 -- to store the statistics. If stattab is null, the statistics
1534 -- will be stored directly in the dictionary.
1535 -- statid - The (optional) identifier to associate with these statistics
1536 -- within stattab (Only pertinent if stattab is not NULL).
1537 -- numrows - The number of rows in the index (partition)
1538 -- numlblks - The number of leaf blocks in the index (partition)
1539 -- numdist - The number of distinct keys in the index (partition)
1540 -- avglblk - Average integral number of leaf blocks in which each
1541 -- distinct key appears for this index (partition). If not provided,
1542 -- this value will be derived from numlblks and numdist.
1543 -- avgdblk - Average integral number of data blocks in the table
1544 -- pointed to by a distinct key for this index (partition).
1545 -- If not provided, this value will be derived from clstfct
1546 -- and numdist.
1547 -- clstfct - See clustering_factor column of the all_indexes view
1548 -- for a description.
1549 -- indlevel - The height of the index (partition)
1550 -- flags - For internal Oracle use (should be left as null)
1551 -- statown - The schema containing stattab (if different then ownname)
1552 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
1553 -- The procedure invalidates the dependent cursors immediately
1554 -- if set to FALSE.
1555 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
1556 -- invalidate dependend cursors. This is the default. The default
1557 -- can be changed using set_param procedure.
1558 -- guessq - IOT guess quality. See pct_direct_access column of the
1559 -- all_indexes view for a description.
1560 -- force - set the values even if statistics of the object is locked
1561 --
1562 -- Input arguments for user-defined statistics:
1563 -- ext_stats - external (user-defined) statistics
1564 -- stattypown - owner of statistics type associated with index
1565 -- stattypname - name of statistics type associated with index
1566 --
1567 -- Exceptions:
1568 -- ORA-20000: Object does not exist or insufficient privileges
1569 -- ORA-20001: Invalid input value
1570 -- ORA-20002: Bad user statistics table, may need to upgrade it
1571 -- ORA-20005: object statistics are locked
1572 --
1573
1574 procedure set_table_stats(
1575 ownname varchar2,
1576 tabname varchar2,
1577 partname varchar2 default null,
1578 stattab varchar2 default null,
1579 statid varchar2 default null,
1580 numrows number default null,
1581 numblks number default null,
1582 avgrlen number default null,
1583 flags number default null,
1584 statown varchar2 default null,
1585 no_invalidate boolean default
1586 to_no_invalidate_type(get_param('NO_INVALIDATE')),
1587 cachedblk number default null,
1588 cachehit number default null,
1589 force boolean default FALSE);
1590 --
1591 -- Set table-related information
1592 --
1593 -- Input arguments:
1594 -- ownname - The name of the schema
1595 -- tabname - The name of the table
1596 -- partname - The name of the table partition in which to store
1597 -- the statistics. If the table is partitioned and partname
1598 -- is null, the statistics will be stored at the global table
1599 -- level.
1600 -- stattab - The user stat table identifier describing where
1601 -- to store the statistics. If stattab is null, the statistics
1602 -- will be stored directly in the dictionary.
1603 -- statid - The (optional) identifier to associate with these statistics
1604 -- within stattab (Only pertinent if stattab is not NULL).
1605 -- numrows - Number of rows in the table (partition)
1606 -- numblks - Number of blocks the table (partition) occupies
1607 -- avgrlen - Average row length for the table (partition)
1608 -- flags - For internal Oracle use (should be left as null)
1609 -- statown - The schema containing stattab (if different then ownname)
1610 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
1611 -- The procedure invalidates the dependent cursors immediately
1612 -- if set to FALSE.
1613 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
1614 -- invalidate dependend cursors. This is the default. The default
1615 -- can be changed using set_param procedure.
1616 -- force - set the values even if statistics of the object is locked
1617 --
1618 -- Exceptions:
1622 -- ORA-20005: object statistics are locked
1619 -- ORA-20000: Object does not exist or insufficient privileges
1620 -- ORA-20001: Invalid input value
1621 -- ORA-20002: Bad user statistics table, may need to upgrade it
1623 --
1624
1625
1626 procedure convert_raw_value(
1627 rawval raw, resval out NOCOPY varchar2);
1628 pragma restrict_references(convert_raw_value, WNDS, RNDS, WNPS, RNPS);
1629 procedure convert_raw_value(
1630 rawval raw, resval out NOCOPY date);
1631 pragma restrict_references(convert_raw_value, WNDS, RNDS, WNPS, RNPS);
1632 procedure convert_raw_value(
1633 rawval raw, resval out NOCOPY number);
1634 pragma restrict_references(convert_raw_value, WNDS, RNDS, WNPS, RNPS);
1635 procedure convert_raw_value(
1636 rawval raw, resval out NOCOPY binary_float);
1637 pragma restrict_references(convert_raw_value, WNDS, RNDS, WNPS, RNPS);
1638 procedure convert_raw_value(
1639 rawval raw, resval out NOCOPY binary_double);
1640 pragma restrict_references(convert_raw_value, WNDS, RNDS, WNPS, RNPS);
1641 procedure convert_raw_value_nvarchar(
1642 rawval raw, resval out NOCOPY nvarchar2);
1643 pragma restrict_references(convert_raw_value_nvarchar,
1644 WNDS, RNDS, WNPS, RNPS);
1645 procedure convert_raw_value_rowid(
1646 rawval raw, resval out NOCOPY rowid);
1647 pragma restrict_references(convert_raw_value_rowid, WNDS, RNDS, WNPS, RNPS);
1648 --
1649 -- Convert the internal representation of a minimum or maximum value
1650 -- into a datatype-specific value. The minval and maxval fields
1651 -- of the StatRec structure as filled in by get_column_stats or
1652 -- prepare_column_values are appropriate values for input.
1653 --
1654 -- Input argument
1655 -- rawval - The raw representation of a column minimum or maximum
1656 --
1657 -- Datatype specific output arguments:
1658 -- resval - The converted, type-specific value
1659 --
1660 -- Exceptions:
1661 -- None
1662 --
1663
1664
1665 procedure get_column_stats(
1666 ownname varchar2, tabname varchar2, colname varchar2,
1667 partname varchar2 default null,
1668 stattab varchar2 default null, statid varchar2 default null,
1669 distcnt out NOCOPY number, density out NOCOPY number,
1670 nullcnt out NOCOPY number, srec out NOCOPY StatRec,
1671 avgclen out NOCOPY number,
1672 statown varchar2 default null);
1673
1674 procedure get_column_stats(
1675 ownname varchar2, tabname varchar2, colname varchar2,
1676 partname varchar2 default null,
1677 stattab varchar2 default null, statid varchar2 default null,
1678 ext_stats out NOCOPY raw,
1679 stattypown out varchar2, stattypname out varchar2,
1680 statown varchar2 default null);
1681 --
1682 -- Gets all column-related information
1683 --
1684 -- Input arguments:
1685 -- ownname - The name of the schema
1686 -- tabname - The name of the table to which this column belongs
1687 -- colname - The name of the column or extension
1688 -- partname - The name of the table partition from which to get
1689 -- the statistics. If the table is partitioned and partname
1690 -- is null, the statistics will be retrieved from the global table
1691 -- level.
1692 -- stattab - The user stat table identifier describing from where
1693 -- to retrieve the statistics. If stattab is null, the statistics
1694 -- will be retrieved directly from the dictionary.
1695 -- statid - The (optional) identifier to associate with these statistics
1696 -- within stattab (Only pertinent if stattab is not NULL).
1697 -- statown - The schema containing stattab (if different then ownname)
1698 --
1699 -- Output arguments:
1700 -- distcnt - The number of distinct values
1701 -- density - The column density
1702 -- nullcnt - The number of nulls
1703 -- srec - structure holding internal representation of column minimum,
1704 -- maximum, and histogram values
1705 -- avgclen - The average length of the column (in bytes)
1706 --
1707 -- Output arguments for user-defined column statistics:
1708 -- ext_stats - external (user-defined) statistics
1709 -- stattypown - owner of statistics type associated with column
1710 -- stattypname - name of statistics type associated with column
1711 --
1712 -- Exceptions:
1713 -- ORA-20000: Object does not exist or insufficient privileges or
1714 -- no statistics have been stored for requested object
1715 -- ORA-20002: Bad user statistics table, may need to upgrade it
1716 --
1717
1718
1719 procedure get_index_stats(
1720 ownname varchar2, indname varchar2,
1721 partname varchar2 default null,
1722 stattab varchar2 default null, statid varchar2 default null,
1723 numrows out NOCOPY number, numlblks out NOCOPY number,
1724 numdist out NOCOPY number, avglblk out NOCOPY number,
1725 avgdblk out NOCOPY number, clstfct out NOCOPY number,
1726 indlevel out NOCOPY number,
1727 statown varchar2 default null,
1728 guessq out NOCOPY number,
1729 cachedblk out NOCOPY number,
1730 cachehit out NOCOPY number);
1731
1732 procedure get_index_stats(
1733 ownname varchar2, indname varchar2,
1734 partname varchar2 default null,
1735 stattab varchar2 default null, statid varchar2 default null,
1736 numrows out NOCOPY number, numlblks out NOCOPY number,
1737 numdist out NOCOPY number, avglblk out NOCOPY number,
1738 avgdblk out NOCOPY number, clstfct out NOCOPY number,
1742
1739 indlevel out NOCOPY number,
1740 statown varchar2 default null,
1741 guessq out NOCOPY number);
1743 procedure get_index_stats(
1744 ownname varchar2, indname varchar2,
1745 partname varchar2 default null,
1746 stattab varchar2 default null, statid varchar2 default null,
1747 numrows out NOCOPY number, numlblks out NOCOPY number,
1748 numdist out NOCOPY number, avglblk out NOCOPY number,
1749 avgdblk out NOCOPY number, clstfct out NOCOPY number,
1750 indlevel out NOCOPY number,
1751 statown varchar2 default null);
1752
1753 procedure get_index_stats(
1754 ownname varchar2, indname varchar2,
1755 partname varchar2 default null,
1756 stattab varchar2 default null, statid varchar2 default null,
1757 ext_stats out NOCOPY raw,
1758 stattypown out varchar2, stattypname out varchar2,
1759 statown varchar2 default null);
1760 --
1761 -- Gets all index-related information
1762 --
1763 -- Input arguments:
1764 -- ownname - The name of the schema
1765 -- indname - The name of the index
1766 -- partname - The name of the index partition for which to get
1767 -- the statistics. If the index is partitioned and partname
1768 -- is null, the statistics will be retrieved for the global index
1769 -- level.
1770 -- stattab - The user stat table identifier describing from where
1771 -- to retrieve the statistics. If stattab is null, the statistics
1772 -- will be retrieved directly from the dictionary.
1773 -- statid - The (optional) identifier to associate with these statistics
1774 -- within stattab (Only pertinent if stattab is not NULL).
1775 -- statown - The schema containing stattab (if different then ownname)
1776 --
1777 -- Output arguments:
1778 -- numrows - The number of rows in the index (partition)
1779 -- numlblks - The number of leaf blocks in the index (partition)
1780 -- numdist - The number of distinct keys in the index (partition)
1781 -- avglblk - Average integral number of leaf blocks in which each
1782 -- distinct key appears for this index (partition).
1783 -- avgdblk - Average integral number of data blocks in the table
1784 -- pointed to by a distinct key for this index (partition).
1785 -- clstfct - The clustering factor for the index (partition).
1786 -- indlevel - The height of the index (partition).
1787 -- guessq - IOT guess quality of the index (partition).
1788 --
1789 -- Output arguments for user defined statistics:
1790 -- ext_stats - external (user-defined) statistics
1791 -- stattypown - owner of statistics type associated with index
1792 -- stattypname - name of statistics type associated with index
1793 --
1794 -- Exceptions:
1795 -- ORA-20000: Object does not exist or insufficient privileges or
1796 -- no statistics have been stored for requested object
1797 -- ORA-20002: Bad user statistics table, may need to upgrade it
1798 --
1799
1800
1801 procedure get_table_stats(
1802 ownname varchar2, tabname varchar2,
1803 partname varchar2 default null,
1804 stattab varchar2 default null, statid varchar2 default null,
1805 numrows out NOCOPY number, numblks out NOCOPY number,
1806 avgrlen out NOCOPY number,
1807 statown varchar2 default null);
1808
1809 procedure get_table_stats(
1810 ownname varchar2,
1811 tabname varchar2,
1812 partname varchar2 default null,
1813 stattab varchar2 default null,
1814 statid varchar2 default null,
1815 numrows out NOCOPY number,
1816 numblks out NOCOPY number,
1817 avgrlen out NOCOPY number,
1818 statown varchar2 default null,
1819 cachedblk out NOCOPY number,
1820 cachehit out NOCOPY number);
1821 --
1822 -- Gets all table-related information
1823 --
1824 -- Input arguments:
1825 -- ownname - The name of the schema
1826 -- tabname - The name of the table to which this column belongs
1827 -- partname - The name of the table partition from which to get
1828 -- the statistics. If the table is partitioned and partname
1829 -- is null, the statistics will be retrieved from the global table
1830 -- level.
1831 -- stattab - The user stat table identifier describing from where
1832 -- to retrieve the statistics. If stattab is null, the statistics
1833 -- will be retrieved directly from the dictionary.
1834 -- statid - The (optional) identifier to associate with these statistics
1835 -- within stattab (Only pertinent if stattab is not NULL).
1836 -- statown - The schema containing stattab (if different then ownname)
1837 --
1838 -- Output arguments:
1839 -- numrows - Number of rows in the table (partition)
1840 -- numblks - Number of blocks the table (partition) occupies
1841 -- avgrlen - Average row length for the table (partition)
1842 --
1843 -- Exceptions:
1844 -- ORA-20000: Object does not exist or insufficient privileges or
1845 -- no statistics have been stored for requested object
1846 -- ORA-20002: Bad user statistics table, may need to upgrade it
1847 --
1848
1849
1850
1851 procedure delete_column_stats(
1852 ownname varchar2, tabname varchar2, colname varchar2,
1853 partname varchar2 default null,
1854 stattab varchar2 default null, statid varchar2 default null,
1855 cascade_parts boolean default true,
1856 statown varchar2 default null,
1857 no_invalidate boolean default
1858 to_no_invalidate_type(get_param('NO_INVALIDATE')),
1862 -- Deletes column-related statistics
1859 force boolean default FALSE,
1860 col_stat_type varchar2 default 'ALL');
1861 --
1863 --
1864 -- Input arguments:
1865 -- ownname - The name of the schema
1866 -- tabname - The name of the table to which this column belongs
1867 -- colname - The name of the column or extension
1868 -- partname - The name of the table partition for which to delete
1869 -- the statistics. If the table is partitioned and partname
1870 -- is null, global column statistics will be deleted.
1871 -- stattab - The user stat table identifier describing from where
1872 -- to delete the statistics. If stattab is null, the statistics
1873 -- will be deleted directly from the dictionary.
1874 -- statid - The (optional) identifier to associate with these statistics
1875 -- within stattab (Only pertinent if stattab is not NULL).
1876 -- cascade_parts - If the table is partitioned and partname is null,
1877 -- setting this to true will cause the deletion of statistics for
1878 -- this column for all underlying partitions as well.
1879 -- statown - The schema containing stattab (if different then ownname)
1880 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
1881 -- The procedure invalidates the dependent cursors immediately
1882 -- if set to FALSE.
1883 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
1884 -- invalidate dependend cursors. This is the default. The default
1885 -- can be changed using set_param procedure.
1886 -- force - delete statistics even if it is locked
1887 -- col_stat_type - Type of column statitistics to be deleted.
1888 -- This argument takes the following values:
1889 -- 'HISTOGRAM' - delete column histogram only
1890 -- 'ALL' - delete base column stats and histogram
1891 --
1892 -- Exceptions:
1893 -- ORA-20000: Object does not exist or insufficient privileges
1894 -- ORA-20002: Bad user statistics table, may need to upgrade it
1895 -- ORA-20005: object statistics are locked
1896 --
1897
1898
1899 procedure delete_index_stats(
1900 ownname varchar2, indname varchar2,
1901 partname varchar2 default null,
1902 stattab varchar2 default null, statid varchar2 default null,
1903 cascade_parts boolean default true,
1904 statown varchar2 default null,
1905 no_invalidate boolean default
1906 to_no_invalidate_type(get_param('NO_INVALIDATE')),
1907 stattype varchar2 default 'ALL',
1908 force boolean default FALSE);
1909 --
1910 -- Deletes index-related statistics
1911 --
1912 -- Input arguments:
1913 -- ownname - The name of the schema
1914 -- indname - The name of the index
1915 -- partname - The name of the index partition for which to delete
1916 -- the statistics. If the index is partitioned and partname
1917 -- is null, index statistics will be deleted at the global level.
1918 -- stattab - The user stat table identifier describing from where
1919 -- to delete the statistics. If stattab is null, the statistics
1920 -- will be deleted directly from the dictionary.
1921 -- statid - The (optional) identifier to associate with these statistics
1922 -- within stattab (Only pertinent if stattab is not NULL).
1923 -- cascade_parts - If the index is partitioned and partname is null,
1924 -- setting this to true will cause the deletion of statistics for
1925 -- this index for all underlying partitions as well.
1926 -- statown - The schema containing stattab (if different then ownname)
1927 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
1928 -- The procedure invalidates the dependent cursors immediately
1929 -- if set to FALSE.
1930 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
1931 -- invalidate dependend cursors. This is the default. The default
1932 -- can be changed using set_param procedure.
1933 -- force - delete the statistics even if it is locked
1934 --
1935 -- Exceptions:
1936 -- ORA-20000: Object does not exist or insufficient privileges
1937 -- ORA-20002: Bad user statistics table, may need to upgrade it
1938 -- ORA-20005: object statistics are locked
1939 --
1940
1941
1942 procedure delete_table_stats(
1943 ownname varchar2, tabname varchar2,
1944 partname varchar2 default null,
1945 stattab varchar2 default null, statid varchar2 default null,
1946 cascade_parts boolean default true,
1947 cascade_columns boolean default true,
1948 cascade_indexes boolean default true,
1949 statown varchar2 default null,
1950 no_invalidate boolean default
1951 to_no_invalidate_type(get_param('NO_INVALIDATE')),
1952 stattype varchar2 default 'ALL',
1953 force boolean default FALSE,
1954 stat_category varchar2 default DEFAULT_DEL_STAT_CATEGORY);
1955 --
1956 -- Deletes table-related statistics
1957 --
1958 -- Input arguments:
1959 -- ownname - The name of the schema
1960 -- tabname - The name of the table to which this column belongs
1961 -- partname - The name of the table partition from which to get
1962 -- the statistics. If the table is partitioned and partname
1963 -- is null, the statistics will be retrieved from the global table
1964 -- level.
1965 -- stattab - The user stat table identifier describing from where
1966 -- to retrieve the statistics. If stattab is null, the statistics
1967 -- will be retrieved directly from the dictionary.
1968 -- statid - The (optional) identifier to associate with these statistics
1969 -- within stattab (Only pertinent if stattab is not NULL).
1970 -- cascade_parts - If the table is partitioned and partname is null,
1974 -- called for all underlying columns (passing the cascade_parts
1971 -- setting this to true will cause the deletion of statistics for
1972 -- this table for all underlying partitions as well.
1973 -- cascade_columns - Indicates that delete_column_stats should be
1975 -- parameter).
1976 -- cascade_indexes - Indicates that delete_index_stats should be
1977 -- called for all underlying indexes (passing the cascade_parts
1978 -- parameter).
1979 -- statown - The schema containing stattab (if different then ownname)
1980 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
1981 -- The procedure invalidates the dependent cursors immediately
1982 -- if set to FALSE.
1983 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
1984 -- invalidate dependend cursors. This is the default. The default
1985 -- can be changed using set_param procedure.
1986 -- force - delete the statistics even if it is locked
1987 -- stat_category - what statistics to delete. It accepts multiple values
1988 -- separated by comma. The values we support now are 'OBJECT_STATS'
1989 -- (i.e., table statistics, column statistics and index statistics) and
1990 -- 'SYNOPSES'. The default is 'OBJECT_STATS, SYNOPSES'
1991 -- Exceptions:
1992 -- ORA-20000: Object does not exist or insufficient privileges
1993 -- ORA-20002: Bad user statistics table, may need to upgrade it
1994 -- ORA-20005: object statistics are locked
1995 --
1996
1997
1998 procedure delete_schema_stats(
1999 ownname varchar2,
2000 stattab varchar2 default null, statid varchar2 default null,
2001 statown varchar2 default null,
2002 no_invalidate boolean default
2003 to_no_invalidate_type(get_param('NO_INVALIDATE')),
2004 stattype varchar2 default 'ALL',
2005 force boolean default FALSE,
2006 stat_category varchar2 default DEFAULT_DEL_STAT_CATEGORY);
2007 --
2008 -- Deletes statistics for a schema
2009 --
2010 -- Input arguments:
2011 -- ownname - The name of the schema
2012 -- stattab - The user stat table identifier describing from where
2013 -- to delete the statistics. If stattab is null, the statistics
2014 -- will be deleted directly in the dictionary.
2015 -- statid - The (optional) identifier to associate with these statistics
2016 -- within stattab (Only pertinent if stattab is not NULL).
2017 -- statown - The schema containing stattab (if different then ownname)
2018 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
2019 -- The procedure invalidates the dependent cursors immediately
2020 -- if set to FALSE.
2021 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
2022 -- invalidate dependend cursors. This is the default. The default
2023 -- can be changed using set_param procedure.
2024 -- stattype - The type of statistics to be deleted
2025 -- ALL - both data and cache statistics will be deleted
2026 -- CACHE - only cache statistics will be deleted
2027 -- force - Ignores the statistics lock on objects and delete
2028 -- the statistics if set to TRUE.
2029 -- stat_category - what statistics to delete. It accepts multiple values
2030 -- separated by comma. The values we support now are 'OBJECT_STATS'
2031 -- (i.e., table statistics, column statistics and index statistics) and
2032 -- 'SYNOPSES'. The default is 'OBJECT_STATS, SYNOPSES'
2033 --
2034 --
2035 -- Exceptions:
2036 -- ORA-20000: Object does not exist or insufficient privileges
2037 -- ORA-20002: Bad user statistics table, may need to upgrade it
2038 --
2039
2040
2041 procedure delete_database_stats(
2042 stattab varchar2 default null, statid varchar2 default null,
2043 statown varchar2 default null,
2044 no_invalidate boolean default
2045 to_no_invalidate_type(get_param('NO_INVALIDATE')),
2046 stattype varchar2 default 'ALL',
2047 force boolean default FALSE,
2048 stat_category varchar2 default DEFAULT_DEL_STAT_CATEGORY);
2049 --
2050 -- Deletes statistics for an entire database
2051 --
2052 -- Input arguments:
2053 -- stattab - The user stat table identifier describing from where
2054 -- to delete the statistics. If stattab is null, the statistics
2055 -- will be deleted directly in the dictionary.
2056 -- statid - The (optional) identifier to associate with these statistics
2057 -- within stattab (Only pertinent if stattab is not NULL).
2058 -- statown - The schema containing stattab.
2059 -- If stattab is not null and statown is null, it is assumed that
2060 -- every schema in the database contains a user statistics table
2061 -- with the name stattab.
2062 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
2063 -- The procedure invalidates the dependent cursors immediately
2064 -- if set to FALSE.
2065 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
2066 -- invalidate dependend cursors. This is the default. The default
2067 -- can be changed using set_param procedure.
2068 -- stattype - The type of statistics to be deleted
2069 -- ALL - both data and cache statistics will be deleted
2070 -- CACHE - only cache statistics will be deleted
2071 -- force - Ignores the statistics lock on objects and delete
2072 -- the statistics if set to TRUE.
2073 -- stat_category - what statistics to delete. It accepts multiple values
2074 -- separated by comma. The values we support now are 'OBJECT_STATS'
2075 -- (i.e., table statistics, column statistics and index statistics) and
2076 -- 'SYNOPSES'. The default is 'OBJECT_STATS, SYNOPSES'
2077 -- Exceptions:
2081
2078 -- ORA-20000: Object does not exist or insufficient privileges
2079 -- ORA-20002: Bad user statistics table, may need to upgrade it
2080 --
2082
2083
2084
2085
2086
2087 --
2088 -- This set of procedures enable the transferrance of statistics
2089 -- from the dictionary to a user stat table (export_*) and from a user
2090 -- stat table to the dictionary (import_*).
2091 --
2092 -- The procedures are:
2093 --
2094 -- create_stat_table
2095 -- drop_stat_table
2096 -- upgrade_stat_table
2097 --
2098 -- export_column_stats
2099 -- export_index_stats
2100 -- export_table_stats
2101 -- export_schema_stats
2102 -- export_database_stats
2103 -- export_system_stats
2104 -- export_fixed_objects_stats
2105 -- export_dictionary_stats
2106 --
2107 -- import_column_stats
2108 -- import_index_stats
2109 -- import_table_stats
2110 -- import_schema_stats
2111 -- import_database_stats
2112 -- import_system_stats
2113 -- import_fixed_objects_stats
2114 -- import_dictionary_stats
2115 --
2116 -- Notes:
2117 -- We do not support export/import of stats across databases of
2118 -- different character sets.
2119 --
2120
2121
2122 procedure create_stat_table(
2123 ownname varchar2, stattab varchar2,
2124 tblspace varchar2 default null,
2125 global_temporary boolean default false);
2126 --
2127 -- Creates a table with name 'stattab' in 'ownname's
2128 -- schema which is capable of holding statistics. The columns
2129 -- and types that compose this table are not relevant as it
2130 -- should be accessed solely through the procedures in this
2131 -- package.
2132 --
2133 -- Input arguments:
2134 -- ownname - The name of the schema
2135 -- stattab - The name of the table to create. This value should
2136 -- be passed as the 'stattab' argument to other procedures
2137 -- when the user does not wish to modify the dictionary statistics
2138 -- directly.
2139 -- tblspace - The tablespace in which to create the stat tables.
2140 -- If none is specified, they will be created in the user's
2141 -- default tablespace.
2142 -- table_options - Whether or not the table should be created as a global
2143 -- temporary table.
2144 --
2145 -- Exceptions:
2146 -- ORA-20000: Insufficient privileges
2147 -- ORA-20001: Tablespace does not exist
2148 -- ORA-20002: Table already exists
2149 --
2150
2151
2152 procedure drop_stat_table(
2153 ownname varchar2, stattab varchar2);
2154 --
2155 -- Drops a user stat table
2156 --
2157 -- Input arguments:
2158 -- ownname - The name of the schema
2159 -- stattab - The user stat table identifier
2160 --
2161 -- Exceptions:
2162 -- ORA-20000: Insufficient privileges
2163 -- ORA-20001: Table is not a statistics table
2164 -- ORA-20002: Table does not exist
2165 --
2166
2167
2168 procedure upgrade_stat_table(
2169 ownname varchar2, stattab varchar2);
2170 --
2171 -- Upgrade a user stat table from an older version
2172 --
2173 -- Input arguments:
2174 -- ownname - The name of the schema
2175 -- stattab - The user stat table identifier
2176 --
2177 -- Exceptions:
2178 -- ORA-20000: Unable to upgrade table
2179 --
2180
2181 procedure remap_stat_table(
2182 ownname varchar2, stattab varchar2,
2183 src_own varchar2, src_tab varchar2,
2184 tgt_own varchar2, tgt_tab varchar2);
2185 --
2186 -- The procedure remaps the names of objects in the user stat table. It allows
2187 -- to import the statistics to objects with same definition but with different
2188 -- names.
2189 --
2190 -- Input arguments:
2191 -- ownname - The owner of the user stat table (NULL means current schema).
2192 -- stattab - The user stat table identifier.
2193 -- src_own - Owner of the table to be renamed. This argument can not be null.
2194 -- src_tab - Name of the table to be renamed.
2195 -- If null, all tables owned by src_own.
2196 -- tgt_own - New name of the owner of the table. The owner name is updated
2197 -- for the dependend objects like columns and indexes as well. Note
2198 -- that a index of src_tab not owned by src_own is not renamed.
2199 -- This argument can not be null.
2200 -- tgt_tab - New name of the table. This argument is valid only if src_tab
2201 -- is not null.
2202 -- Examples:
2203 -- The following statement remap all objects of sh to shsave in user stat
2204 -- table sh.ustat
2205 -- dbms_stats.remap_stat_table('sh', 'ustat', 'sh', null, 'shsave', null);
2206 --
2207 -- The following statement can be used to import statistics into objects of
2208 -- shsave once the above remap procedure is completed.
2209 -- dbms_stats.import_schema_stats('shsave', 'ustat', statown => 'sh');
2210 --
2211 -- The following statement remaps sh.customers ->shsave.customers_sav.
2212 -- dbms_stats.remap_stat_table('sh', 'ustat', 'sh', 'customers',
2213 -- 'shsave', 'customers_sav');
2214 --
2215 -- Exceptions:
2216 -- ORA-20000: Insufficient privileges
2217 -- ORA-20001: Invalid input
2218 --
2219 procedure export_column_stats(
2220 ownname varchar2, tabname varchar2, colname varchar2,
2221 partname varchar2 default null,
2222 stattab varchar2, statid varchar2 default null,
2223 statown varchar2 default null);
2224 --
2225 -- Retrieves statistics for a particular column and stores them in the user
2226 -- stat table identified by stattab
2227 --
2231 -- colname - The name of the column or extension
2228 -- Input arguments:
2229 -- ownname - The name of the schema
2230 -- tabname - The name of the table to which this column belongs
2232 -- partname - The name of the table partition. If the table is
2233 -- partitioned and partname is null, global and partition column
2234 -- statistics will be exported.
2235 -- stattab - The user stat table identifier describing where
2236 -- to store the statistics.
2237 -- statid - The (optional) identifier to associate with these statistics
2238 -- within stattab.
2239 -- statown - The schema containing stattab (if different then ownname)
2240 --
2241 -- Exceptions:
2242 -- ORA-20000: Object does not exist or insufficient privileges
2243 -- ORA-20002: Bad user statistics table, may need to upgrade it
2244 --
2245
2246
2247 procedure export_index_stats(
2248 ownname varchar2, indname varchar2,
2249 partname varchar2 default null,
2250 stattab varchar2, statid varchar2 default null,
2251 statown varchar2 default null);
2252 --
2253 -- Retrieves statistics for a particular index and stores them
2254 -- in the user stat table identified by stattab
2255 --
2256 -- Input arguments:
2257 -- ownname - The name of the schema
2258 -- indname - The name of the index
2259 -- partname - The name of the index partition. If the index is
2260 -- partitioned and partname is null, global and partition index
2261 -- statistics will be exported.
2262 -- stattab - The user stat table identifier describing where
2263 -- to store the statistics.
2264 -- statid - The (optional) identifier to associate with these statistics
2265 -- within stattab.
2266 -- statown - The schema containing stattab (if different then ownname)
2267 --
2268 -- Exceptions:
2269 -- ORA-20000: Object does not exist or insufficient privileges
2270 -- ORA-20002: Bad user statistics table, may need to upgrade it
2271 --
2272
2273
2274 procedure export_table_stats(
2275 ownname varchar2, tabname varchar2,
2276 partname varchar2 default null,
2277 stattab varchar2, statid varchar2 default null,
2278 cascade boolean default true,
2279 statown varchar2 default null,
2280 stat_category varchar2 default DEFAULT_STAT_CATEGORY
2281 );
2282 --
2283 -- Retrieves statistics for a particular table and stores them
2284 -- in the user stat table.
2285 -- Cascade will result in all index and column stats associated
2286 -- with the specified table being exported as well.
2287 --
2288 -- Input arguments:
2289 -- ownname - The name of the schema
2290 -- tabname - The name of the table
2291 -- partname - The name of the table partition. If the table is
2292 -- partitioned and partname is null, global and partition table
2293 -- statistics will be exported.
2294 -- stattab - The user stat table identifier describing where
2295 -- to store the statistics.
2296 -- statid - The (optional) identifier to associate with these statistics
2297 -- within stattab.
2298 -- cascade - If true, column and index statistics for this table
2299 -- will also be exported.
2300 -- statown - The schema containing stattab (if different then ownname)
2301 -- stat_category - what statistics to export. It accepts multiple values
2302 -- separated by comma. The values we support now are 'OBJECT_STATS'
2303 -- (i.e., table statistics, column statistics and index statistics) and
2304 -- 'SYNOPSES'. However SYNOPSES can only be exported along with
2305 -- OBJECT_STATS. Therefore only valid combinations are
2306 -- OBJECT_STATS
2307 -- OBJECT_STATS, SYNOPSES
2308 -- The default value is OBJECT_STATS that can be changed usig statistics
2309 -- preference.
2310 -- Exceptions:
2311 -- ORA-20000: Object does not exist or insufficient privileges
2312 -- ORA-20002: Bad user statistics table, may need to upgrade it
2313 --
2314
2315
2316 procedure export_schema_stats(
2317 ownname varchar2,
2318 stattab varchar2, statid varchar2 default null,
2319 statown varchar2 default null,
2320 stat_category varchar2 default DEFAULT_STAT_CATEGORY);
2321 --
2322 -- Retrieves statistics for all objects in the schema identified
2323 -- by ownname and stores them in the user stat table identified
2324 -- by stattab
2325 --
2326 -- Input arguments:
2327 -- ownname - The name of the schema
2328 -- stattab - The user stat table identifier describing where
2329 -- to store the statistics.
2330 -- statid - The (optional) identifier to associate with these statistics
2331 -- within stattab.
2332 -- statown - The schema containing stattab (if different then ownname)
2333 -- stat_category - what statistics to export. It accepts multiple values
2334 -- separated by comma. The values we support now are 'OBJECT_STATS'
2335 -- (i.e., table statistics, column statistics and index statistics) and
2336 -- 'SYNOPSES'. However SYNOPSES can only be exported along with
2337 -- OBJECT_STATS. Therefore only valid combinations are
2338 -- OBJECT_STATS
2339 -- OBJECT_STATS, SYNOPSES
2340 -- The default value is OBJECT_STATS that can be changed usig statistics
2341 -- preference.
2342 --
2343 -- Exceptions:
2344 -- ORA-20000: Object does not exist or insufficient privileges
2345 -- ORA-20002: Bad user statistics table, may need to upgrade it
2346 --
2347
2348
2349 procedure export_database_stats(
2350 stattab varchar2, statid varchar2 default null,
2351 statown varchar2 default null,
2352 stat_category varchar2 default DEFAULT_STAT_CATEGORY);
2356 -- by statown.stattab
2353 --
2354 -- Retrieves statistics for all objects in the database
2355 -- and stores them in the user stat tables identified
2357 --
2358 -- Input arguments:
2359 -- stattab - The user stat table identifier describing where
2360 -- to store the statistics.
2361 -- statid - The (optional) identifier to associate with these statistics
2362 -- within stattab.
2363 -- statown - The schema containing stattab.
2364 -- If statown is null, it is assumed that every schema in the database
2365 -- contains a user statistics table with the name stattab.
2366 -- stat_category - what statistics to export. It accepts multiple values
2367 -- separated by comma. The values we support now are 'OBJECT_STATS'
2368 -- (i.e., table statistics, column statistics and index statistics) and
2369 -- 'SYNOPSES'. However SYNOPSES can only be exported along with
2370 -- OBJECT_STATS. Therefore only valid combinations are
2371 -- OBJECT_STATS
2372 -- OBJECT_STATS, SYNOPSES
2373 -- The default value is OBJECT_STATS that can be changed usig statistics
2374 -- preference.
2375 --
2376 -- Exceptions:
2377 -- ORA-20000: Object does not exist or insufficient privileges
2378 -- ORA-20002: Bad user statistics table, may need to upgrade it
2379 --
2380
2381
2382 procedure import_column_stats(
2383 ownname varchar2, tabname varchar2, colname varchar2,
2384 partname varchar2 default null,
2385 stattab varchar2, statid varchar2 default null,
2386 statown varchar2 default null,
2387 no_invalidate boolean default
2388 to_no_invalidate_type(get_param('NO_INVALIDATE')),
2389 force boolean default FALSE);
2390 --
2391 -- Retrieves statistics for a particular column from the user stat table
2392 -- identified by stattab and stores them in the dictionary
2393 --
2394 -- Input arguments:
2395 -- ownname - The name of the schema
2396 -- tabname - The name of the table to which this column belongs
2397 -- colname - The name of the column or extension
2398 -- partname - The name of the table partition. If the table is
2399 -- partitioned and partname is null, global and partition column
2400 -- statistics will be imported.
2401 -- stattab - The user stat table identifier describing from where
2402 -- to retrieve the statistics.
2403 -- statid - The (optional) identifier to associate with these statistics
2404 -- within stattab.
2405 -- statown - The schema containing stattab (if different then ownname)
2406 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
2407 -- The procedure invalidates the dependent cursors immediately
2408 -- if set to FALSE.
2409 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
2410 -- invalidate dependend cursors. This is the default. The default
2411 -- can be changed using set_param procedure.
2412 -- force - import statistics even if it is locked
2413 --
2414 -- Exceptions:
2415 -- ORA-20000: Object does not exist or insufficient privileges
2416 -- ORA-20001: Invalid or inconsistent values in the user stat table
2417 -- ORA-20002: Bad user statistics table, may need to upgrade it
2418 -- ORA-20005: object statistics are locked
2419 --
2420
2421
2422 procedure import_index_stats(
2423 ownname varchar2, indname varchar2,
2424 partname varchar2 default null,
2425 stattab varchar2, statid varchar2 default null,
2426 statown varchar2 default null,
2427 no_invalidate boolean default
2428 to_no_invalidate_type(get_param('NO_INVALIDATE')),
2429 force boolean default FALSE);
2430 --
2431 -- Retrieves statistics for a particular index from the user
2432 -- stat table identified by stattab and stores them in the
2433 -- dictionary
2434 --
2435 -- Input arguments:
2436 -- ownname - The name of the schema
2437 -- indname - The name of the index
2438 -- partname - The name of the index partition. If the index is
2439 -- partitioned and partname is null, global and partition index
2440 -- statistics will be imported.
2441 -- stattab - The user stat table identifier describing from where
2442 -- to retrieve the statistics.
2443 -- statid - The (optional) identifier to associate with these statistics
2444 -- within stattab.
2445 -- statown - The schema containing stattab (if different then ownname)
2446 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
2447 -- The procedure invalidates the dependent cursors immediately
2448 -- if set to FALSE.
2449 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
2450 -- invalidate dependend cursors. This is the default. The default
2451 -- can be changed using set_param procedure.
2452 -- force - import the statistics even if it is locked
2453 --
2454 -- Exceptions:
2455 -- ORA-20000: Object does not exist or insufficient privileges
2456 -- ORA-20001: Invalid or inconsistent values in the user stat table
2457 -- ORA-20002: Bad user statistics table, may need to upgrade it
2458 -- ORA-20005: object statistics are locked
2459 --
2460
2461
2462 procedure import_table_stats(
2463 ownname varchar2, tabname varchar2,
2464 partname varchar2 default null,
2465 stattab varchar2, statid varchar2 default null,
2466 cascade boolean default true,
2467 statown varchar2 default null,
2468 no_invalidate boolean default
2469 to_no_invalidate_type(get_param('NO_INVALIDATE')),
2470 force boolean default FALSE,
2471 stat_category varchar2 default DEFAULT_STAT_CATEGORY);
2472 --
2476 -- with the specified table being imported as well.
2473 -- Retrieves statistics for a particular table from the user
2474 -- stat table identified by stattab and stores them in the dictionary.
2475 -- Cascade will result in all index and column stats associated
2477 -- The statistics will be imported as pending in case PUBLISH preference
2478 -- is set to FALSE.
2479 --
2480 -- Input arguments:
2481 -- ownname - The name of the schema
2482 -- tabname - The name of the table
2483 -- partname - The name of the table partition. If the table is
2484 -- partitioned and partname is null, global and partition table
2485 -- statistics will be imported.
2486 -- stattab - The user stat table identifier describing from where
2487 -- to retrieve the statistics.
2488 -- statid - The (optional) identifier to associate with these statistics
2489 -- within stattab.
2490 -- cascade - If true, column and index statistics for this table
2491 -- will also be imported.
2492 -- statown - The schema containing stattab (if different then ownname)
2493 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
2494 -- The procedure invalidates the dependent cursors immediately
2495 -- if set to FALSE.
2496 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
2497 -- invalidate dependend cursors. This is the default. The default
2498 -- can be changed using set_param procedure.
2499 -- force - import even if statistics of the object is locked
2500 -- stat_category - what statistics to import. It accepts multiple values
2501 -- separated by comma. The values we support now are 'OBJECT_STATS'
2502 -- (i.e., table statistics, column statistics and index statistics) and
2503 -- 'SYNOPSES'. However SYNOPSES can only be imported along with
2504 -- OBJECT_STATS. Therefore only valid combinations are
2505 -- OBJECT_STATS
2506 -- OBJECT_STATS, SYNOPSES
2507 -- The default value is OBJECT_STATS that can be changed usig statistics
2508 -- preference.
2509 -- Exceptions:
2510 -- ORA-20000: Object does not exist or insufficient privileges
2511 -- ORA-20001: Invalid or inconsistent values in the user stat table
2512 -- ORA-20002: Bad user statistics table, may need to upgrade it
2513 -- ORA-20005: object statistics are locked
2514 --
2515
2516
2517 procedure import_schema_stats(
2518 ownname varchar2,
2519 stattab varchar2, statid varchar2 default null,
2520 statown varchar2 default null,
2521 no_invalidate boolean default
2522 to_no_invalidate_type(get_param('NO_INVALIDATE')),
2523 force boolean default FALSE,
2524 stat_category varchar2 default DEFAULT_STAT_CATEGORY);
2525 --
2526 -- Retrieves statistics for all objects in the schema identified
2527 -- by ownname from the user stat table and stores them in the
2528 -- dictionary
2529 -- The statistics will be imported as pending in case PUBLISH preference
2530 -- is set to FALSE.
2531 --
2532 -- Input arguments:
2533 -- ownname - The name of the schema
2534 -- stattab - The user stat table identifier describing from where
2535 -- to retrieve the statistics.
2536 -- statid - The (optional) identifier to associate with these statistics
2537 -- within stattab.
2538 -- statown - The schema containing stattab (if different then ownname)
2539 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
2540 -- The procedure invalidates the dependent cursors immediately
2541 -- if set to FALSE.
2542 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
2543 -- invalidate dependend cursors. This is the default. The default
2544 -- can be changed using set_param procedure.
2545 -- force - Override statistics lock.
2546 -- TRUE- Ignores the statistics lock on objects and import
2547 -- the statistics.
2548 -- FALSE-The statistics of an object will be imported only if it
2549 -- is not locked.
2550 -- ie if both DATA and CACHE statistics is locked, it will not
2551 -- import anything. If CACHE statistics of an object is locked,
2552 -- only DATA statistics will be imported and vice versa.
2553 -- stat_category - what statistics to import. It accepts multiple values
2554 -- separated by comma. The values we support now are 'OBJECT_STATS'
2555 -- (i.e., table statistics, column statistics and index statistics) and
2556 -- 'SYNOPSES'. However SYNOPSES can only be imported along with
2557 -- OBJECT_STATS. Therefore only valid combinations are
2558 -- OBJECT_STATS
2559 -- OBJECT_STATS, SYNOPSES
2560 -- The default value is OBJECT_STATS that can be changed usig statistics
2561 -- preference.
2562 --
2563 -- Exceptions:
2564 -- ORA-20000: Object does not exist or insufficient privileges.
2565 -- if ORA-20000 shows "no statistics are imported", several
2566 -- possible reasons are: (1) no statistics exist for the specified
2567 -- ownname or statid in the stattab; (2) statistics are locked;
2568 -- (3) objects in the stattab no longer exist in the current
2569 -- database
2570 -- ORA-20001: Invalid or inconsistent values in the user stat table
2571 -- ORA-20002: Bad user statistics table, may need to upgrade it
2572 --
2573
2574
2575 procedure import_database_stats(
2576 stattab varchar2, statid varchar2 default null,
2577 statown varchar2 default null,
2578 no_invalidate boolean default
2579 to_no_invalidate_type(get_param('NO_INVALIDATE')),
2580 force boolean default FALSE,
2581 stat_category varchar2 default DEFAULT_STAT_CATEGORY
2582 );
2586 -- dictionary
2583 --
2584 -- Retrieves statistics for all objects in the database
2585 -- from the user stat table(s) and stores them in the
2587 -- The statistics will be imported as pending in case PUBLISH preference
2588 -- is set to FALSE.
2589 --
2590 -- Input arguments:
2591 -- stattab - The user stat table identifier describing from where
2592 -- to retrieve the statistics.
2593 -- statid - The (optional) identifier to associate with these statistics
2594 -- within stattab.
2595 -- statown - The schema containing stattab.
2596 -- If statown is null, it is assumed that every schema in the database
2597 -- contains a user statistics table with the name stattab.
2598 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
2599 -- The procedure invalidates the dependent cursors immediately
2600 -- if set to FALSE.
2601 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
2602 -- invalidate dependend cursors. This is the default. The default
2603 -- can be changed using set_param procedure.
2604 -- force - Override statistics lock.
2605 -- TRUE- Ignores the statistics lock on objects and import
2606 -- the statistics.
2607 -- FALSE-The statistics of an object will be imported only if it
2608 -- is not locked.
2609 -- ie if both DATA and CACHE statistics is locked, it will not
2610 -- import anything. If CACHE statistics of an object is locked,
2611 -- only DATA statistics will be imported and vice versa.
2612 -- stat_category - what statistics to import. It accepts multiple values
2613 -- separated by comma. The values we support now are 'OBJECT_STATS'
2614 -- (i.e., table statistics, column statistics and index statistics) and
2615 -- 'SYNOPSES'. However SYNOPSES can only be imported along with
2616 -- OBJECT_STATS. Therefore only valid combinations are
2617 -- OBJECT_STATS
2618 -- OBJECT_STATS, SYNOPSES
2619 -- The default value is OBJECT_STATS that can be changed usig statistics
2620 -- preference.
2621 --
2622 -- Exceptions:
2623 -- ORA-20000: Object does not exist or insufficient privileges
2624 -- if ORA-20000 shows "no statistics are imported", several
2625 -- possible reasons are: (1) user specified statid does not
2626 -- exist; (2) statistics are locked; (3) objects in the
2627 -- stattab no longer exist in the current database
2628 -- ORA-20001: Invalid or inconsistent values in the user stat table
2629 -- ORA-20002: Bad user statistics table, may need to upgrade it
2630 --
2631
2632
2633
2634
2635
2636
2637
2638
2639 --
2640 -- This set of procedures enable the gathering of certain
2641 -- classes of optimizer statistics with possible performance
2642 -- improvements over the analyze command.
2643 --
2644 -- The procedures are:
2645 --
2646 -- gather_index_stats
2647 -- gather_table_stats
2648 -- gather_schema_stats
2649 -- gather_database_stats
2650 -- gather_system_stats
2651 -- gather_fixed_objects_stats
2652 -- gather_dictionary_stats
2653 --
2654 -- We also provide the following procedure for generating some
2655 -- statistics for derived objects when we have sufficient statistics
2656 -- on related objects
2657 --
2658 -- generate_stats
2659 --
2660
2661 procedure gather_index_stats
2662 (ownname varchar2, indname varchar2, partname varchar2 default null,
2663 estimate_percent number default DEFAULT_ESTIMATE_PERCENT,
2664 stattab varchar2 default null, statid varchar2 default null,
2665 statown varchar2 default null,
2666 degree number default DEFAULT_DEGREE_VALUE,
2667 granularity varchar2 default DEFAULT_GRANULARITY,
2668 no_invalidate boolean default
2669 to_no_invalidate_type(get_param('NO_INVALIDATE')),
2670 stattype varchar2 default 'DATA',
2671 force boolean default FALSE);
2672 --
2673 -- This procedure gathers index statistics.
2674 -- It attempts to parallelize as much of the work as possible.
2675 -- are some restrictions as described in the individual parameters.
2676 -- This operation will not parallelize with certain types of indexes,
2677 -- including cluster indexes, domain indexes and bitmap join indexes.
2678 -- The "granularity" and "no_invalidate" arguments are also not pertinent to
2679 -- these types of indexes.
2680 --
2681 -- ownname - schema of index to analyze
2682 -- indname - name of index
2683 -- partname - name of partition
2684 -- estimate_percent - Percentage of rows to estimate (NULL means compute).
2685 -- The valid range is [0.000001,100]. Use the constant
2686 -- DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the
2687 -- appropriate sample size for good statistics. This is the default.
2688 -- The default value can be changed using set_param procedure.
2689 -- degree - degree of parallelism (NULL means use of table default value
2690 -- which was specified by DEGREE clause in CREATE/ALTER INDEX statement)
2691 -- Use the constant DBMS_STATS.DEFAULT_DEGREE for the default value
2692 -- based on the initialization parameters.
2693 -- default for degree is NULL.
2694 -- The default value can be changed using set_param procedure.
2695 -- granularity - the granularity of statistics to collect (only pertinent
2696 -- if the table is partitioned)
2697 -- 'AUTO' - the procedure determines what level of statistics to collect
2698 -- 'GLOBAL AND PARTITION' - gather global- and partition-level statistics
2699 -- 'SUBPARTITION' - gather subpartition-level statistics
2700 -- 'PARTITION' - gather partition-level statistics
2704 -- The default value can be changed using set_param procedure.
2701 -- 'GLOBAL' - gather global statistics
2702 -- 'ALL' - gather all (subpartition, partition, and global) statistics
2703 -- default for granularity is AUTO.
2705 -- stattab - The user stat table identifier describing where to save
2706 -- the current statistics.
2707 -- statid - The (optional) identifier to associate with these statistics
2708 -- within stattab.
2709 -- statown - The schema containing stattab (if different then ownname)
2710 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
2711 -- The procedure invalidates the dependent cursors immediately
2712 -- if set to FALSE.
2713 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
2714 -- invalidate dependend cursors. This is the default. The default
2715 -- can be changed using set_param procedure.
2716 -- force - gather statistics of index even if it is locked.
2717 -- options - further specification of which objects to gather statistics for
2718 -- 'GATHER' - gather statistics on all objects in the schema
2719 -- 'GATHER AUTO' - gather all necessary statistics automatically. Oracle
2720 -- implicitly determines which objects need new statistics.
2721 --
2722 -- Exceptions:
2723 -- ORA-20000: Index does not exist or insufficient privileges
2724 -- ORA-20001: Bad input value
2725 -- ORA-20002: Bad user statistics table, may need to upgrade it
2726 -- ORA-20005: object statistics are locked
2727 --
2728
2729 procedure gather_table_stats
2730 (ownname varchar2, tabname varchar2, partname varchar2 default null,
2731 estimate_percent number default DEFAULT_ESTIMATE_PERCENT,
2732 block_sample boolean default FALSE,
2733 method_opt varchar2 default DEFAULT_METHOD_OPT,
2734 degree number default DEFAULT_DEGREE_VALUE,
2735 granularity varchar2 default DEFAULT_GRANULARITY,
2736 cascade boolean default DEFAULT_CASCADE,
2737 stattab varchar2 default null, statid varchar2 default null,
2738 statown varchar2 default null,
2739 no_invalidate boolean default
2740 to_no_invalidate_type(get_param('NO_INVALIDATE')),
2741 stattype varchar2 default 'DATA',
2742 force boolean default FALSE,
2743 -- the context is intended for internal use only.
2744 context dbms_stats.CContext default null,
2745 options varchar2 default DEFAULT_OPTIONS
2746 );
2747
2748 --
2749 -- This procedure gathers table and column (and index) statistics.
2750 -- It attempts to parallelize as much of the work as possible, but there
2751 -- are some restrictions as described in the individual parameters.
2752 -- This operation will not parallelize if the user does not have select
2753 -- privilege on the table being analyzed.
2754 --
2755 -- Input arguments:
2756 -- ownname - schema of table to analyze
2757 -- tabname - name of table
2758 -- partname - name of partition
2759 -- estimate_percent - Percentage of rows to estimate (NULL means compute).
2760 -- The valid range is [0.000001,100]. Use the constant
2761 -- DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the
2762 -- appropriate sample size for good statistics. This is the default.
2763 -- The default value can be changed using set_param procedure.
2764 -- block_sample - whether or not to use random block sampling instead of
2765 -- random row sampling. Random block sampling is more efficient, but
2766 -- if the data is not randomly distributed on disk then the sample values
2767 -- may be somewhat correlated. Only pertinent when doing an estimate
2768 -- statistics.
2769 -- method_opt - method options of the following format
2770 --
2771 -- method_opt := FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
2772 -- FOR COLUMNS [size_clause]
2773 -- column|attribute [size_clause]
2774 -- [,column|attribute [size_clause] ... ]
2775 --
2776 -- size_clause := SIZE [integer | auto | skewonly | repeat],
2777 -- where integer is between 1 and 2048
2778 --
2779 -- column := column name | extension name | extension
2780 --
2781 -- default is FOR ALL COLUMNS SIZE AUTO.
2782 -- The default value can be changed using set_param procedure.
2783 -- Optimizer related table statistics are always gathered.
2784 --
2785 -- If an extension is provided, the procedure create the extension if it
2786 -- does not exist already. Please refer to create_extended_stats for
2787 -- description of extension.
2788 --
2789 -- degree - degree of parallelism (NULL means use of table default value
2790 -- which was specified by DEGREE clause in CREATE/ALTER TABLE statement)
2791 -- Use the constant DBMS_STATS.DEFAULT_DEGREE for the default value
2792 -- based on the initialization parameters.
2793 -- default for degree is NULL.
2794 -- The default value can be changed using set_param procedure.
2795 -- granularity - the granularity of statistics to collect (only pertinent
2796 -- if the table is partitioned)
2797 -- 'AUTO' - the procedure determines what level of statistics to collect
2798 -- 'GLOBAL AND PARTITION' - gather global- and partition-level statistics
2799 -- 'APPROX_GLOBAL AND PARTITION' - This option is similar to
2800 -- 'GLOBAL AND PARTITION'. But the global statistics are aggregated
2801 -- from partition level statistics. It will aggregate all statistics
2802 -- except number of distinct values for columns and number of distinct
2803 -- keys of indexes. The existing histograms of the columns at the
2807 -- partitions with statistics, so to get accurate global statistics,
2804 -- table level are also aggregated. The global statistics are gathered
2805 -- (i.e., going back to GLOBAL AND PARTITION behaviour)
2806 -- if partname argument is null. The aggregation will use only
2808 -- user has to make sure to have statistics for all partitions.
2809 --
2810 --
2811 -- This option is useful when you collect statistics for a new
2812 -- partition added into a range partitioned table (for example, a table
2813 -- partitioned by month). The new data in the partition makes the
2814 -- global statistics stale (especially the min/max values of the
2815 -- partitioning column). This stale global statistics may cause
2816 -- suboptimal plans. In this scenario, users can collect statistics
2817 -- for the newly added partition with 'APPROX_GLOBAL AND PARTITION'
2818 -- option so that the global statistics will reflect the newly added
2819 -- range. This option will take less time than 'GLOBAL AND PARTITION'
2820 -- option since the global statistics are aggregated from underlying
2821 -- partition level statistics. Note that, if you are using
2822 -- APPROX_GLOBAL AND PARTITION, you still need to collect global
2823 -- statistics (with granularity = 'GLOBAL' option) when there is
2824 -- substantial amount of change at the table level. For example you
2825 -- added 10% more data to the table. This is needed to get the correct
2826 -- number of distinct values/keys statistic at table level.
2827 -- 'SUBPARTITION' - gather subpartition-level statistics
2828 -- 'PARTITION' - gather partition-level statistics
2829 -- 'GLOBAL' - gather global statistics
2830 -- 'ALL' - gather all (subpartition, partition, and global) statistics
2831 -- default for granularity is AUTO.
2832 -- The default value can be changed using set_param procedure.
2833 -- cascade - gather statistics on the indexes for this table.
2834 -- Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine
2835 -- whether index stats to be collected or not. This is the default.
2836 -- The default value can be changed using set_param procedure.
2837 -- Using this option is equivalent to running the gather_index_stats
2838 -- procedure on each of the table's indexes.
2839 -- stattab - The user stat table identifier describing where to save
2840 -- the current statistics.
2841 -- statid - The (optional) identifier to associate with these statistics
2842 -- within stattab.
2843 -- statown - The schema containing stattab (if different then ownname)
2844 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
2845 -- The procedure invalidates the dependent cursors immediately
2846 -- if set to FALSE.
2847 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
2848 -- invalidate dependend cursors. This is the default. The default
2849 -- can be changed using set_param procedure.
2850 -- When the 'cascade' argument is specified, not pertinent with certain
2851 -- types of indexes described in the gather_index_stats section.
2852 -- force - gather statistics of table even if it is locked.
2853 -- context - internal use only.
2854 --
2855 -- Exceptions:
2856 -- ORA-20000: Table does not exist or insufficient privileges
2857 -- ORA-20001: Bad input value
2858 -- ORA-20002: Bad user statistics table, may need to upgrade it
2859 -- ORA-20005: object statistics are locked
2860 --
2861
2862 function report_gather_table_stats
2863 (ownname varchar2, tabname varchar2, partname varchar2 default null,
2864 estimate_percent number default DEFAULT_ESTIMATE_PERCENT,
2865 block_sample boolean default FALSE,
2866 method_opt varchar2 default DEFAULT_METHOD_OPT,
2867 degree number default DEFAULT_DEGREE_VALUE,
2868 granularity varchar2 default DEFAULT_GRANULARITY,
2869 cascade boolean default DEFAULT_CASCADE,
2870 stattab varchar2 default null, statid varchar2 default null,
2871 statown varchar2 default null,
2872 no_invalidate boolean default
2873 to_no_invalidate_type(get_param('NO_INVALIDATE')),
2874 stattype varchar2 default 'DATA',
2875 force boolean default FALSE,
2876 options varchar2 default DEFAULT_OPTIONS,
2877 detail_level varchar2 default 'TYPICAL',
2878 format varchar2 default 'TEXT')
2879 return clob;
2880
2881 --
2882 -- This procedure runs gather_table_stats in reporting mode. That is,
2883 -- stats are not actually collected, but all the objects that will be
2884 -- affected when gather_table_stats is invoked are reported.
2885 -- The detail level for the report is defined by the detail_level
2886 -- input parameter. Please see the comments for report_single_stats_operation
2887 -- on possible values for detail_level and format.
2888 -- For all other input parameters, please see the comments on
2889 -- gather_table_stats.
2890
2891
2892 procedure gather_schema_stats
2893 (ownname varchar2,
2894 estimate_percent number default DEFAULT_ESTIMATE_PERCENT,
2895 block_sample boolean default FALSE,
2896 method_opt varchar2 default DEFAULT_METHOD_OPT,
2897 degree number default DEFAULT_DEGREE_VALUE,
2898 granularity varchar2 default DEFAULT_GRANULARITY,
2899 cascade boolean default DEFAULT_CASCADE,
2900 stattab varchar2 default null, statid varchar2 default null,
2901 options varchar2 default 'GATHER', objlist out NOCOPY ObjectTab,
2902 statown varchar2 default null,
2903 no_invalidate boolean default
2904 to_no_invalidate_type(get_param('NO_INVALIDATE')),
2908 force boolean default FALSE,
2905 gather_temp boolean default FALSE,
2906 gather_fixed boolean default FALSE,
2907 stattype varchar2 default 'DATA',
2909 obj_filter_list ObjectTab default null);
2910 procedure gather_schema_stats
2911 (ownname varchar2,
2912 estimate_percent number default DEFAULT_ESTIMATE_PERCENT,
2913 block_sample boolean default FALSE,
2914 method_opt varchar2 default DEFAULT_METHOD_OPT,
2915 degree number default DEFAULT_DEGREE_VALUE,
2916 granularity varchar2 default DEFAULT_GRANULARITY,
2917 cascade boolean default DEFAULT_CASCADE,
2918 stattab varchar2 default null, statid varchar2 default null,
2919 options varchar2 default 'GATHER', statown varchar2 default null,
2920 no_invalidate boolean default
2921 to_no_invalidate_type(get_param('NO_INVALIDATE')),
2922 gather_temp boolean default FALSE,
2923 gather_fixed boolean default FALSE,
2924 stattype varchar2 default 'DATA',
2925 force boolean default FALSE,
2926 obj_filter_list ObjectTab default null);
2927 --
2928 -- Input arguments:
2929 -- ownname - schema to analyze (NULL means current schema)
2930 -- estimate_percent - Percentage of rows to estimate (NULL means compute).
2931 -- The valid range is [0.000001,100]. Use the constant
2932 -- DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the
2933 -- appropriate sample size for good statistics. This is the default.
2934 -- The default value can be changed using set_param procedure.
2935 -- block_sample - whether or not to use random block sampling instead of
2936 -- random row sampling. Random block sampling is more efficient, but
2937 -- if the data is not randomly distributed on disk then the sample values
2938 -- may be somewhat correlated. Only pertinent when doing an estimate
2939 -- statistics.
2940 -- method_opt - method options of the following format
2941 --
2942 -- method_opt := FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
2943 --
2944 -- size_clause := SIZE [integer | auto | skewonly | repeat],
2945 -- where integer is between 1 and 2048
2946 --
2947 -- default is FOR ALL COLUMNS SIZE AUTO.
2948 -- The default value can be changed using set_param procedure.
2949 -- This value will be passed to all of the individual tables.
2950 -- degree - degree of parallelism (NULL means use table default value which
2951 -- is specified by DEGREE clause in CREATE/ALTER TABLE statement)
2952 -- Use the constant DBMS_STATS.DEFAULT_DEGREE for the default value
2953 -- based on the initialization parameters.
2954 -- default for degree is NULL.
2955 -- The default value can be changed using set_param procedure.
2956 -- granularity - the granularity of statistics to collect (only pertinent
2957 -- if the table is partitioned)
2958 -- 'AUTO' - the procedure determines what level of statistics to collect
2959 -- 'GLOBAL AND PARTITION' - gather global- and partition-level statistics
2960 -- 'SUBPARTITION' - gather subpartition-level statistics
2961 -- 'PARTITION' - gather partition-level statistics
2962 -- 'GLOBAL' - gather global statistics
2963 -- 'ALL' - gather all (subpartition, partition, and global) statistics
2964 -- default for granularity is AUTO.
2965 -- The default value can be changed using set_param procedure.
2966 -- cascade - gather statistics on the indexes as well.
2967 -- Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine
2968 -- whether index stats to be collected or not. This is the default.
2969 -- The default value can be changed using set_param procedure.
2970 -- Using this option is equivalent to running the gather_index_stats
2971 -- procedure on each of the indexes in the schema in addition to
2972 -- gathering table and column statistics.
2973 -- stattab - The user stat table identifier describing where to save
2974 -- the current statistics.
2975 -- statid - The (optional) identifier to associate with these statistics
2976 -- within stattab.
2977 -- options - further specification of which objects to gather statistics for
2978 -- 'GATHER' - gather statistics on all objects in the schema
2979 -- 'GATHER AUTO' - gather all necessary statistics automatically. Oracle
2980 -- implicitly determines which objects need new statistics, and
2981 -- determines how to gather those statistics. When 'GATHER AUTO' is
2982 -- specified, the only additional valid parameters are no_invalidate,
2983 -- ownname, stattab,
2984 -- statid, objlist and statown; all other parameter settings will be
2985 -- ignored. Also, return a list of objects processed.
2986 -- 'GATHER STALE' - gather statistics on stale objects as determined
2987 -- by looking at the *_tab_modifications views. Also, return
2988 -- a list of objects found to be stale.
2989 -- 'GATHER EMPTY' - gather statistics on objects which currently
2990 -- have no statistics. also, return a list of objects found
2991 -- to have no statistics.
2992 -- 'LIST AUTO' - return list of objects to be processed with 'GATHER AUTO'
2993 -- 'LIST STALE' - return list of stale objects as determined
2994 -- by looking at the *_tab_modifications views
2995 -- 'LIST EMPTY' - return list of objects which currently
2996 -- have no statistics
2997 -- objlist - list of objects found to be stale or empty
2998 -- statown - The schema containing stattab (if different then ownname)
2999 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
3003 -- invalidate dependend cursors. This is the default. The default
3000 -- The procedure invalidates the dependent cursors immediately
3001 -- if set to FALSE.
3002 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
3004 -- can be changed using set_param procedure.
3005 -- When 'cascade' option is specified, not pertinent with certain types
3006 -- of indexes described in the gather_index_stats section.
3007 -- gather_temp - gather stats on global temporary tables also. The
3008 -- temporary table must be created with "on commit preserve rows" clause,
3009 -- and the statistics being collected are based on the data in the session
3010 -- which this procedure is run but shared across all the sessions.
3011 -- gather_fixed - Gather statistics on fixed tables also.
3012 -- Statistics for fixed tables can be collected only by user SYS.
3013 -- Also the ownname should be SYS or NULL.
3014 -- Specified values for the following arguments will be ignored while
3015 -- gathering statistics for fixed tables.
3016 -- estimate_percent, block_sample, stattab, statid, statown
3017 -- It will not invalidate the dependent cursors on fixed table
3018 -- on which stats is collected.
3019 -- This option is meant for internal use only.
3020 -- force - gather statistics of objects even if they are locked.
3021 -- obj_filter_list - a list of object filters. When provided,
3022 -- gather_schema_stats will only gather statistics on the objects which
3023 -- satisfy at least one object filter in the list as needed. Please refer
3024 -- to obj_filter_list in gather_database_stats.
3025 -- Exceptions:
3026 -- ORA-20000: Schema does not exist or insufficient privileges
3027 -- ORA-20001: Bad input value
3028 -- ORA-20002: Bad user statistics table, may need to upgrade it
3029 --
3030
3031 function report_gather_schema_stats
3032 (ownname varchar2,
3033 estimate_percent number default DEFAULT_ESTIMATE_PERCENT,
3034 block_sample boolean default FALSE,
3035 method_opt varchar2 default DEFAULT_METHOD_OPT,
3036 degree number default DEFAULT_DEGREE_VALUE,
3037 granularity varchar2 default DEFAULT_GRANULARITY,
3038 cascade boolean default DEFAULT_CASCADE,
3039 stattab varchar2 default null, statid varchar2 default null,
3040 options varchar2 default 'GATHER', statown varchar2 default null,
3041 no_invalidate boolean default
3042 to_no_invalidate_type(get_param('NO_INVALIDATE')),
3043 gather_temp boolean default FALSE,
3044 gather_fixed boolean default FALSE,
3045 stattype varchar2 default 'DATA',
3046 force boolean default FALSE,
3047 obj_filter_list ObjectTab default null,
3048 detail_level varchar2 default 'TYPICAL',
3049 format varchar2 default 'TEXT')
3050 return clob;
3051
3052 --
3053 -- This procedure runs gather_schema_stats in reporting mode. That is,
3054 -- stats are not actually collected, but all the objects that will be
3055 -- affected when gather_schema_stats is invoked are reported.
3056 -- The detail level for the report is defined by the detail_level
3057 -- input parameter. Please see the comments for report_single_stats_operation
3058 -- on possible values for detail_level and format.
3059 -- For all other input parameters, please see the comments on
3060 -- gather_schema_stats.
3061
3062 procedure gather_database_stats
3063 (estimate_percent number default DEFAULT_ESTIMATE_PERCENT,
3064 block_sample boolean default FALSE,
3065 method_opt varchar2 default DEFAULT_METHOD_OPT,
3066 degree number default DEFAULT_DEGREE_VALUE,
3067 granularity varchar2 default DEFAULT_GRANULARITY,
3068 cascade boolean default DEFAULT_CASCADE,
3069 stattab varchar2 default null, statid varchar2 default null,
3070 options varchar2 default 'GATHER', objlist out NOCOPY ObjectTab,
3071 statown varchar2 default null,
3072 gather_sys boolean default TRUE,
3073 no_invalidate boolean default
3074 to_no_invalidate_type(get_param('NO_INVALIDATE')),
3075 gather_temp boolean default FALSE,
3076 gather_fixed boolean default FALSE,
3077 stattype varchar2 default 'DATA',
3078 obj_filter_list ObjectTab default null);
3079
3080 procedure gather_database_stats
3081 (estimate_percent number default DEFAULT_ESTIMATE_PERCENT,
3082 block_sample boolean default FALSE,
3083 method_opt varchar2 default DEFAULT_METHOD_OPT,
3084 degree number default DEFAULT_DEGREE_VALUE,
3085 granularity varchar2 default DEFAULT_GRANULARITY,
3086 cascade boolean default DEFAULT_CASCADE,
3087 stattab varchar2 default null, statid varchar2 default null,
3088 options varchar2 default 'GATHER', statown varchar2 default null,
3089 gather_sys boolean default TRUE,
3090 no_invalidate boolean default
3091 to_no_invalidate_type(get_param('NO_INVALIDATE')),
3092 gather_temp boolean default FALSE,
3093 gather_fixed boolean default FALSE,
3094 stattype varchar2 default 'DATA',
3095 obj_filter_list ObjectTab default null);
3096 --
3097 -- Input arguments:
3098 -- estimate_percent - Percentage of rows to estimate (NULL means compute).
3099 -- The valid range is [0.000001,100]. Use the constant
3100 -- DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the
3101 -- appropriate sample size for good statistics. This is the default.
3102 -- The default value can be changed using set_param procedure.
3103 -- block_sample - whether or not to use random block sampling instead of
3104 -- random row sampling. Random block sampling is more efficient, but
3105 -- if the data is not randomly distributed on disk then the sample values
3109 --
3106 -- may be somewhat correlated. Only pertinent when doing an estimate
3107 -- statistics.
3108 -- method_opt - method options of the following format
3110 -- method_opt := FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
3111 --
3112 -- size_clause := SIZE [integer | auto | skewonly | repeat],
3113 -- where integer is between 1 and 2048
3114 --
3115 -- default is FOR ALL COLUMNS SIZE AUTO.
3116 -- The default value can be changed using set_param procedure.
3117 -- This value will be passed to all of the individual tables.
3118 -- degree - degree of parallelism (NULL means use table default value which
3119 -- is specified by DEGREE clause in CREATE/ALTER TABLE statement)
3120 -- Use the constant DBMS_STATS.DEFAULT_DEGREE for the default value
3121 -- based on the initialization parameters.
3122 -- default for degree is NULL.
3123 -- The default value can be changed using set_param procedure.
3124 -- granularity - the granularity of statistics to collect (only pertinent
3125 -- if the table is partitioned)
3126 -- 'AUTO' - the procedure determines what level of statistics to collect
3127 -- 'GLOBAL AND PARTITION' - gather global- and partition-level statistics
3128 -- 'SUBPARTITION' - gather subpartition-level statistics
3129 -- 'PARTITION' - gather partition-level statistics
3130 -- 'GLOBAL' - gather global statistics
3131 -- 'ALL' - gather all (subpartition, partition, and global) statistics
3132 -- default for granularity is AUTO.
3133 -- The default value can be changed using set_param procedure.
3134 -- cascade - gather statistics on the indexes as well.
3135 -- Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine
3136 -- whether index stats to be collected or not. This is the default.
3137 -- The default value can be changed using set_param procedure.
3138 -- Using this option is equivalent to running the gather_index_stats
3139 -- procedure on each of the indexes in the database in addition to
3140 -- gathering table and column statistics.
3141 -- stattab - The user stat table identifier describing where to save
3142 -- the current statistics.
3143 -- statid - The (optional) identifier to associate with these statistics
3144 -- within stattab.
3145 -- options - further specification of which objects to gather statistics for
3146 -- 'GATHER' - gather statistics on all objects in the schema
3147 -- 'GATHER AUTO' - gather all necessary statistics automatically. Oracle
3148 -- implicitly determines which objects need new statistics, and
3149 -- determines how to gather those statistics. When 'GATHER AUTO' is
3150 -- specified, the only additional valid parameters are no_invalidate,
3151 -- stattab, statid, objlist and statown; all other parameter settings
3152 -- will be ignored. Also, return a list of objects processed.
3153 -- 'GATHER STALE' - gather statistics on stale objects as determined
3154 -- by looking at the *_tab_modifications views. Also, return
3155 -- a list of objects found to be stale.
3156 -- 'GATHER EMPTY' - gather statistics on objects which currently
3157 -- have no statistics. also, return a list of objects found
3158 -- to have no statistics.
3159 -- 'LIST AUTO' - return list of objects to be processed with 'GATHER AUTO'
3160 -- 'LIST STALE' - return list of stale objects as determined
3161 -- by looking at the *_tab_modifications views
3162 -- 'LIST EMPTY' - return list of objects which currently
3163 -- have no statistics
3164 -- objlist - list of objects found to be stale or empty
3165 -- statown - The schema containing stattab. If null, it will assume
3166 -- there is a table named stattab in each relevant schema in the
3167 -- database if stattab is specified for saving current statistics.
3168 -- gather_sys - Gather statistics on the objects owned by the 'SYS' user
3169 -- as well.
3170 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
3171 -- The procedure invalidates the dependent cursors immediately
3172 -- if set to FALSE.
3173 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
3174 -- invalidate dependend cursors. This is the default. The default
3175 -- can be changed using set_param procedure.
3176 -- When 'cascade' option is specified, not pertinent with certain types
3177 -- of indexes described in the gather_index_stats section.
3178 -- gather_temp - gather stats on global temporary tables also. The
3179 -- temporary table must be created with "on commit preserve rows" clause,
3180 -- and the statistics being collected are based on the data in the session
3181 -- which this procedure is run but shared across all the sessions.
3182 -- gather_fixed - Gather stats on fixed tables also.
3183 -- Statistics for fixed tables can be collected only by user SYS.
3184 -- Specified values for the following arguments will be ignored while
3185 -- gathering statistics for fixed tables.
3186 -- gathering statistics for fixed tables.
3187 -- estimate_percent, block_sample, stattab, statid, statown
3188 -- It will not invalidate the dependent cursors on fixed table
3189 -- on which stats is collected.
3190 -- This option is meant for internal use only.
3191 -- obj_filter_list - a list of object filters. When provided,
3192 -- gather_database_stats will only gather statistics on the objects which
3193 -- satisfy at least one of the object filters as needed.
3194 --
3195 -- In one single object filter, we can specify the constraints on the
3196 -- object attributes. The attribute values specified in the object filter
3197 -- are case-insensitive unless double-quoted. Wildcard is allowed in the
3201 -- is true. The following example specifies that any table with a "SALES"
3198 -- attribute values. Suppose non-null values s1, s2, ... are specified for
3199 -- attributes a1, a2, ... in one object filter. An object o is said to
3200 -- satisfy this object filter if (o.a1 like s1) and (o.a2 like s2) and ...
3202 -- prefix in the SH schema and any table in the SYS schema, if stale, will
3203 -- be gathered. Note that the statistics for the partitions of the tables
3204 -- also will be gathered if they are stale.
3205 -- Example:
3206 -- declare
3207 -- filter_lst dbms_stats.objecttab := dbms_stats.objecttab();
3208 -- begin
3209 -- filter_lst.extend(2);
3210 -- filter_lst(1).ownname := 'sh';
3211 -- filter_lst(1).objname := 'sales%';
3212 -- filter_lst(2).ownname := 'sys';
3213 -- dbms_stats.gather_schema_stats(null, obj_filter_list => filter_lst,
3214 -- options => 'gather_stale');
3215 -- end;
3216 -- Exceptions:
3217 -- ORA-20000: Insufficient privileges
3218 -- ORA-20001: Bad input value
3219 -- ORA-20002: Bad user statistics table, may need to upgrade it
3220 --
3221
3222 function report_gather_database_stats
3223 (estimate_percent number default DEFAULT_ESTIMATE_PERCENT,
3224 block_sample boolean default FALSE,
3225 method_opt varchar2 default DEFAULT_METHOD_OPT,
3226 degree number default DEFAULT_DEGREE_VALUE,
3227 granularity varchar2 default DEFAULT_GRANULARITY,
3228 cascade boolean default DEFAULT_CASCADE,
3229 stattab varchar2 default null, statid varchar2 default null,
3230 options varchar2 default 'GATHER', statown varchar2 default null,
3231 gather_sys boolean default TRUE,
3232 no_invalidate boolean default
3233 to_no_invalidate_type(get_param('NO_INVALIDATE')),
3234 gather_temp boolean default FALSE,
3235 gather_fixed boolean default FALSE,
3236 stattype varchar2 default 'DATA',
3237 obj_filter_list ObjectTab default null,
3238 detail_level varchar2 default 'TYPICAL',
3239 format varchar2 default 'TEXT')
3240 return clob;
3241
3242 --
3243 -- This procedure runs gather_database_stats in reporting mode. That is,
3244 -- stats are not actually collected, but all the objects that will be
3245 -- affected when gather_database_stats is invoked are reported.
3246 -- The detail level for the report is defined by the detail_level
3247 -- input parameter. Please see the comments for report_single_stats_operation
3248 -- on possible values for detail_level and format.
3249 -- For all other input parameters, please see the comments on
3250 -- gather_database_stats.
3251
3252 procedure generate_stats
3253 (ownname varchar2, objname varchar2,
3254 organized number default 7,
3255 force boolean default FALSE);
3256 --
3257 -- This procedure generates object statistics from previously collected
3258 -- statistics of related objects. For fully populated
3259 -- schemas, the gather procedures should be used instead when more
3260 -- accurate statistics are desired.
3261 -- The currently supported objects are b-tree and bitmap indexes.
3262 --
3263 -- ownname - schema of object
3264 -- objname - name of object
3265 -- organized - the amount of ordering associated between the index and
3266 -- its undelrying table. A heavily organized index would have consecutive
3267 -- index keys referring to consecutive rows on disk for the table
3268 -- (the same block). A heavily disorganized index would have consecutive
3269 -- keys referencing different table blocks on disk. This parameter is
3270 -- only used for b-tree indexes.
3271 -- The number can be in the range of 0-10, with 0 representing a completely
3272 -- organized index and 10 a completely disorganized one.
3273 -- force - generate statistics even if it is locked
3274 -- Exceptions:
3275 -- ORA-20000: Unsupported object type of object does not exist
3276 -- ORA-20001: Invalid option or invalid statistics
3277 -- ORA-20005: object statistics are locked
3278 --
3279
3280
3281
3282
3283 --
3284 -- This procedure enables the flushing of in-memory monitoring
3285 -- information to the dictionary. Corresponding entries in the
3286 -- *_tab_modifications views are updated immediately, without waiting
3287 -- for Oracle to flush it periodically. Useful for the users who need
3288 -- up-to-date information in those views.
3289 -- The gather_*_stats procedures internally flush the monitoring information
3290 -- accordingly, and it is NOT necessary to run this procedure before
3291 -- gathering the statistics.
3292 --
3293 --
3294 -- The procedure is:
3295 --
3296 -- flush_database_monitoring_info
3297 --
3298 -- The modification monitoring mechanism is now controlled by the
3299 -- STATISTICS_LEVEL initialization parameter, and the following
3300 -- procedures no longer have any effect:
3301 --
3302 -- alter_schema_tab_monitoring
3303 -- alter_database_tab_monitoring
3304 --
3305
3306 procedure flush_database_monitoring_info;
3307 --
3308 -- Flush in-memory monitoring information for all the tables to the dictionary.
3309 --
3310 -- Exceptions:
3311 -- ORA-20000: Insufficient privileges
3312 --
3313
3314 procedure alter_schema_tab_monitoring
3315 (ownname varchar2 default NULL, monitoring boolean default TRUE);
3316 procedure alter_database_tab_monitoring
3317 (monitoring boolean default TRUE, sysobjs boolean default FALSE);
3318
3319
3320
3321 procedure gather_system_stats (
3322 gathering_mode varchar2 default 'NOWORKLOAD',
3326 statown varchar2 default null);
3323 interval integer default 60,
3324 stattab varchar2 default null,
3325 statid varchar2 default null,
3327 --
3328 -- This procedure gathers system statistics.
3329 --
3330 -- Input arguments:
3331 -- gathering_mode - Values: NOWORKLOAD, INTERVAL, START, STOP, EXADATA
3332 -- NOWORKLOAD:
3333 -- In this mode system statistics will be gathered based on system
3334 -- characteristics only, without regard to the workload.
3335 -- INTERVAL:
3336 -- In this mode the user can specify a time interval parameter, in
3337 -- minutes.
3338 -- After <interval> minutes has passed the system statistics will be
3339 -- updated either in dictionary, or stattab if specified. The system
3340 -- statistics are based on system activity during specified interval.
3341 -- START | STOP:
3342 -- The procedure is first called with START then followed by another
3343 -- call using STOP. It is assumed that between these two calls some
3344 -- SQL workload has been running allowing the procedure to capture
3345 -- workload-specific statistics.
3346 -- START will initiate gathering statistics. STOP will calculate
3347 -- statistics for elapsed period of time (since START) and refresh
3348 -- dictionary or stattab. Interval in these modes is ignored.
3349 -- EXADATA:
3350 -- In this mode the gathered system statistics takes into account the
3351 -- unique capabilities provided by using Exadata such as large IO size
3352 -- and high IO throughput. The multi-block read count and IO throughput
3353 -- statistics are set along with the CPU speed.
3354 --
3355 -- interval - Specifies period of time in minutes for gathering statistics
3356 -- in INTERVAL mode.
3357 -- stattab - The user stat table identifier describing where to save
3358 -- the current statistics.
3359 -- statid - The (optional) identifier to associate with these statistics
3360 -- within stattab.
3361 -- statown - The schema containing stattab (if different then ownname)
3362 --
3363 -- Exceptions:
3364 -- ORA-20000: Object does not exist or insufficient privileges
3365 -- ORA-20001: Bad input value
3366 -- ORA-20002: Bad user statistics table, may need to upgrade it
3367 -- ORA-20003: Unable to gather system statistics
3368 -- ORA-20004: Error in "INTERVAL" mode :
3369 -- system parameter job_queue_processes must be > 0
3370 --
3371
3372 procedure get_system_stats (
3373 status out varchar2,
3374 dstart out date,
3375 dstop out date,
3376 pname varchar2,
3377 pvalue out number,
3378 stattab varchar2 default null,
3379 statid varchar2 default null,
3380 statown varchar2 default null);
3381
3382 --
3383 -- Input arguments:
3384 -- stattab - The user stat table identifier describing from where to get
3385 -- the current statistics info. If stattab is null, the statistics info
3386 -- will be obtained directly from the dictionary.
3387 -- statid - The (optional) identifier to associate with these statistics
3388 -- within stattab.
3389 -- statown - The schema containing stattab (if different then ownname)
3390 -- pname - parameter name to get
3391 --
3392 -- Output arguments:
3393 -- status - returns one of the following: COMPLETED, AUTOGATHERING,
3394 -- MANUALGATHERING, BADSTATS
3395 -- dstart - date when system stats gathering has been started
3396 -- dstop - date when gathering was finished if status = COMPLETE,
3397 -- will be finished if status = AUTOGATHERING,
3398 -- had to be finished if status = BADSTATS,
3399 -- dstarted if status = MANUALGATHERING,
3400 -- the following parameters defined only if status = COMPLETE
3401 -- pvalue - parameter value to get
3402 --
3403 -- Exceptions:
3404 -- ORA-20000: Object does not exist or insufficient privileges
3405 -- ORA-20002: Bad user statistics table, may need to upgrade it
3406 -- ORA-20003: Unable to get system statistics
3407 -- ORA-20004: Parameter doesn't exist
3408 --
3409
3410 procedure set_system_stats (
3411 pname varchar2,
3412 pvalue number,
3413 stattab varchar2 default null,
3414 statid varchar2 default null,
3415 statown varchar2 default null);
3416
3417 --
3418 -- Input arguments:
3419 -- pname - parameter name to set
3420 -- pvalue - parameter value to set
3421 -- stattab - The user stat table identifier describing from where to get
3422 -- the current statistics info. If stattab is null, the statistics info
3423 -- will be obtained directly from the dictionary.
3424 -- statid - The (optional) identifier to associate with these statistics
3425 -- within stattab.
3426 -- statown - The schema containing stattab (if different then ownname)
3427 --
3428 -- Exceptions:
3429 -- ORA-20000: Object does not exist or insufficient privileges
3430 -- ORA-20001: Invalid input value
3431 -- ORA-20002: Bad user statistics table, may need to upgrade it
3432 -- ORA-20003: Unable to set system statistics
3433 -- ORA-20004: Parameter doesn't exist
3434 --
3435
3436
3437 procedure delete_system_stats (
3438 stattab varchar2 default nulL,
3439 statid varchar2 default nulL,
3440 statown varchar2 default null);
3441
3442 --
3443 -- Deletes system statistics
3444 --
3445 -- Input arguments:
3446 -- stattab - The user stat table identifier describing from where
3450 -- within stattab (Only pertinent if stattab is not NULL).
3447 -- to delete the statistics. If stattab is null, the statistics
3448 -- will be deleted directly from the dictionary.
3449 -- statid - The (optional) identifier to associate with these statistics
3451 -- statown - The schema containing stattab (if different then ownname)
3452 --
3453 -- Exceptions:
3454 -- ORA-20000: Object does not exist or insufficient privileges
3455 -- ORA-20002: Bad user statistics table, may need to upgrade it
3456 --
3457
3458 procedure import_system_stats (
3459 stattab varchar2,
3460 statid varchar2 default null,
3461 statown varchar2 default null);
3462
3463 --
3464 -- Retrieves system statistics from the user
3465 -- stat table identified by stattab and stores it in the
3466 -- dictionary
3467 --
3468 -- Input arguments:
3469 -- stattab - The user stat table identifier describing from where
3470 -- to retrieve the statistics.
3471 -- statid - The (optional) identifier to associate with these statistics
3472 -- within stattab.
3473 -- statown - The schema containing stattab (if different then ownname)
3474 --
3475 -- Exceptions:
3476 -- ORA-20000: Object does not exist or insufficient privileges
3477 -- if ORA-20000 shows "no statistics are imported", several
3478 -- possible reasons are: (1) user specified statid does not
3479 -- exist; (2) statistics are locked; (3) objects in the
3480 -- stattab no longer exist in the current database
3481 -- ORA-20001: Invalid or inconsistent values in the user stat table
3482 -- ORA-20002: Bad user statistics table, may need to upgrade it
3483 -- ORA-20003: Unable to import system statistics
3484 --
3485
3486
3487 procedure export_system_stats (
3488 stattab varchar2,
3489 statid varchar2 default null,
3490 statown varchar2 default null);
3491
3492 --
3493 -- Retrieves system statistics and stores it
3494 -- in the user stat table identified by stattab
3495 --
3496 -- Input arguments:
3497 -- stattab - The user stat table identifier describing where
3498 -- to store the statistics.
3499 -- statid - The (optional) identifier to associate with these statistics
3500 -- within stattab.
3501 -- statown - The schema containing stattab (if different then ownname)
3502 --
3503 -- Exceptions:
3504 -- ORA-20000: Object does not exist or insufficient privileges
3505 -- ORA-20002: Bad user statistics table, may need to upgrade it
3506 -- ORA-20003: Unable to export system statistics
3507 --
3508
3509
3510 procedure gather_fixed_objects_stats
3511 (stattab varchar2 default null, statid varchar2 default null,
3512 statown varchar2 default null,
3513 no_invalidate boolean default
3514 to_no_invalidate_type(get_param('NO_INVALIDATE')));
3515 --
3516 -- Gather statistics for fixed tables.
3517 -- To run this procedure, you must have the SYSDBA or ANALYZE ANY DICTIONARY
3518 -- system privilege.
3519 --
3520 -- Input arguments:
3521 -- stattab - The user stat table identifier describing where to save
3522 -- the current statistics.
3523 -- statid - The (optional) identifier to associate with these statistics
3524 -- within stattab.
3525 -- statown - The schema containing stattab (if different then ownname)
3526 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
3527 -- The procedure invalidates the dependent cursors immediately
3528 -- if set to FALSE.
3529 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
3530 -- invalidate dependend cursors. This is the default. The default
3531 -- can be changed using set_param procedure.
3532 -- Exceptions:
3533 -- ORA-20000: insufficient privileges
3534 -- ORA-20001: Bad input value
3535 -- ORA-20002: Bad user statistics table, may need to upgrade it
3536 --
3537
3538 function report_gather_fixed_obj_stats
3539 (stattab varchar2 default null, statid varchar2 default null,
3540 statown varchar2 default null,
3541 no_invalidate boolean default
3542 to_no_invalidate_type(get_param('NO_INVALIDATE')),
3543 detail_level varchar2 default 'TYPICAL',
3544 format varchar2 default 'TEXT')
3545 return clob;
3546
3547 --
3548 -- This procedure runs gather_fixed_objects_stats in reporting mode. That is,
3549 -- stats are not actually collected, but all the objects that will be
3550 -- affected when gather_fixed_objects_stats is invoked are reported.
3551 -- The detail level for the report is defined by the detail_level
3552 -- input parameter. Please see the comments for report_single_stats_operation
3553 -- on possible values for detail_level and format.
3554 -- For all other input parameters, please see the comments on
3555 -- gather_fixed_objects_stats.
3556
3557 procedure delete_fixed_objects_stats(
3558 stattab varchar2 default null, statid varchar2 default null,
3559 statown varchar2 default null,
3560 no_invalidate boolean default
3561 to_no_invalidate_type(get_param('NO_INVALIDATE')),
3562 force boolean default FALSE);
3563 --
3564 -- Deletes statistics for fixed tables
3565 -- To run this procedure, you must have the SYSDBA or ANALYZE ANY DICTIONARY
3566 -- system privilege.
3567 --
3568 -- Input arguments:
3569 -- stattab - The user stat table identifier describing from where
3570 -- to delete the statistics. If stattab is null, the statistics
3571 -- will be deleted directly in the dictionary.
3572 -- statid - The (optional) identifier to associate with these statistics
3576 -- The procedure invalidates the dependent cursors immediately
3573 -- within stattab (Only pertinent if stattab is not NULL).
3574 -- statown - The schema containing stattab (if different then ownname)
3575 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
3577 -- if set to FALSE.
3578 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
3579 -- invalidate dependend cursors. This is the default. The default
3580 -- can be changed using set_param procedure.
3581 -- force - Ignores the statistics lock on objects and delete
3582 -- the statistics if set to TRUE.
3583 --
3584 -- Exceptions:
3585 -- ORA-20000: insufficient privileges
3586 -- ORA-20002: Bad user statistics table, may need to upgrade it
3587 --
3588
3589
3590 procedure export_fixed_objects_stats(
3591 stattab varchar2, statid varchar2 default null,
3592 statown varchar2 default null);
3593 --
3594 -- Retrieves statistics for fixed tables and stores them in the user
3595 -- stat table identified by stattab
3596 -- To run this procedure, you must have the SYSDBA or ANALYZE ANY DICTIONARY
3597 -- system privilege.
3598 --
3599 -- Input arguments:
3600 -- stattab - The user stat table identifier describing where
3601 -- to store the statistics.
3602 -- statid - The (optional) identifier to associate with these statistics
3603 -- within stattab.
3604 -- statown - The schema containing stattab (if different then ownname)
3605 --
3606 -- Exceptions:
3607 -- ORA-20000: insufficient privileges
3608 -- ORA-20002: Bad user statistics table, may need to upgrade it
3609 --
3610
3611
3612 procedure import_fixed_objects_stats(
3613 stattab varchar2, statid varchar2 default null,
3614 statown varchar2 default null,
3615 no_invalidate boolean default
3616 to_no_invalidate_type(get_param('NO_INVALIDATE')),
3617 force boolean default FALSE);
3618 --
3619 -- Retrieves statistics for fixed tables from the user stat table and
3620 -- stores them in the dictionary
3621 -- To run this procedure, you must have the SYSDBA or ANALYZE ANY DICTIONARY
3622 -- system privilege.
3623 -- The statistics will be imported as pending in case PUBLISH preference
3624 -- is set to FALSE.
3625 --
3626 -- Input arguments:
3627 -- stattab - The user stat table identifier describing from where
3628 -- to retrieve the statistics.
3629 -- statid - The (optional) identifier to associate with these statistics
3630 -- within stattab.
3631 -- statown - The schema containing stattab (if different then ownname)
3632 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
3633 -- The procedure invalidates the dependent cursors immediately
3634 -- if set to FALSE.
3635 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
3636 -- invalidate dependend cursors. This is the default. The default
3637 -- can be changed using set_param procedure.
3638 -- force - Override statistics lock.
3639 -- TRUE- Ignores the statistics lock on objects and import
3640 -- the statistics.
3641 -- FALSE-The statistics of an object will be imported only if it
3642 -- is not locked.
3643 --
3644 -- Exceptions:
3645 -- ORA-20000: insufficient privileges
3646 -- if ORA-20000 shows "no statistics are imported", several
3647 -- possible reasons are: (1) user specified statid does not
3648 -- exist; (2) statistics are locked; (3) objects in the
3649 -- stattab no longer exist in the current database
3650 -- ORA-20001: Invalid or inconsistent values in the user stat table
3651 -- ORA-20002: Bad user statistics table, may need to upgrade it
3652 --
3653
3654 procedure gather_dictionary_stats
3655 (comp_id varchar2 default null,
3656 estimate_percent number default DEFAULT_ESTIMATE_PERCENT,
3657 block_sample boolean default FALSE,
3658 method_opt varchar2 default DEFAULT_METHOD_OPT,
3659 degree number default DEFAULT_DEGREE_VALUE,
3660 granularity varchar2 default DEFAULT_GRANULARITY,
3661 cascade boolean default DEFAULT_CASCADE,
3662 stattab varchar2 default null, statid varchar2 default null,
3663 options varchar2 default 'GATHER AUTO', objlist out ObjectTab,
3664 statown varchar2 default null,
3665 no_invalidate boolean default
3666 to_no_invalidate_type(get_param('NO_INVALIDATE')),
3667 stattype varchar2 default 'DATA',
3668 obj_filter_list ObjectTab default null);
3669 procedure gather_dictionary_stats
3670 (comp_id varchar2 default null,
3671 estimate_percent number default DEFAULT_ESTIMATE_PERCENT,
3672 block_sample boolean default FALSE,
3673 method_opt varchar2 default DEFAULT_METHOD_OPT,
3674 degree number default DEFAULT_DEGREE_VALUE,
3675 granularity varchar2 default DEFAULT_GRANULARITY,
3676 cascade boolean default DEFAULT_CASCADE,
3677 stattab varchar2 default null, statid varchar2 default null,
3678 options varchar2 default 'GATHER AUTO', statown varchar2 default null,
3679 no_invalidate boolean default
3680 to_no_invalidate_type(get_param('NO_INVALIDATE')),
3681 stattype varchar2 default 'DATA',
3682 obj_filter_list ObjectTab default null);
3683
3684 --
3685 -- Gather statistics for dictionary schemas 'SYS', 'SYSTEM' and schemas of
3686 -- RDBMS components.
3687 -- To run this procedure, you must have the SYSDBA OR
3688 -- both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.
3689 --
3690 -- Input arguments:
3694 -- The procedure always gather stats on 'SYS' and 'SYSTEM' schemas
3691 -- comp_id - component id of the schema to analyze (NULL means schemas
3692 -- of all RDBMS components).
3693 -- Please refer to comp_id column of dba_registry view.
3695 -- regardless of this argument.
3696 -- estimate_percent - Percentage of rows to estimate (NULL means compute).
3697 -- The valid range is [0.000001,100]. Use the constant
3698 -- DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the
3699 -- appropriate sample size for good statistics. This is the default.
3700 -- The default value can be changed using set_param procedure.
3701 -- block_sample - whether or not to use random block sampling instead of
3702 -- random row sampling. Random block sampling is more efficient, but
3703 -- if the data is not randomly distributed on disk then the sample values
3704 -- may be somewhat correlated. Only pertinent when doing an estimate
3705 -- statistics.
3706 -- method_opt - method options of the following format
3707 --
3708 -- method_opt := FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
3709 --
3710 -- size_clause := SIZE [integer | auto | skewonly | repeat],
3711 -- where integer is between 1 and 2048
3712 --
3713 -- default is FOR ALL COLUMNS SIZE AUTO.
3714 -- The default value can be changed using set_param procedure.
3715 -- This value will be passed to all of the individual tables.
3716 -- degree - degree of parallelism (NULL means use table default value which
3717 -- is specified by DEGREE clause in CREATE/ALTER TABLE statement)
3718 -- Use the constant DBMS_STATS.DEFAULT_DEGREE for the default value
3719 -- based on the initialization parameters.
3720 -- default for degree is NULL.
3721 -- The default value can be changed using set_param procedure.
3722 -- granularity - the granularity of statistics to collect (only pertinent
3723 -- if the table is partitioned)
3724 -- 'AUTO' - the procedure determines what level of statistics to collect
3725 -- 'GLOBAL AND PARTITION' - gather global- and partition-level statistics
3726 -- 'SUBPARTITION' - gather subpartition-level statistics
3727 -- 'PARTITION' - gather partition-level statistics
3728 -- 'GLOBAL' - gather global statistics
3729 -- 'ALL' - gather all (subpartition, partition, and global) statistics
3730 -- default for granularity is AUTO.
3731 -- The default value can be changed using set_param procedure.
3732 -- cascade - gather statistics on the indexes as well.
3733 -- Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine
3734 -- whether index stats to be collected or not. This is the default.
3735 -- The default value can be changed using set_param procedure.
3736 -- Using this option is equivalent to running the gather_index_stats
3737 -- procedure on each of the indexes in the schema in addition to
3738 -- gathering table and column statistics.
3739 -- stattab - The user stat table identifier describing where to save
3740 -- the current statistics.
3741 -- statid - The (optional) identifier to associate with these statistics
3742 -- within stattab.
3743 -- options - further specification of which objects to gather statistics for
3744 -- 'GATHER' - gather statistics on all objects in the schema
3745 -- 'GATHER AUTO' - gather all necessary statistics automatically. Oracle
3746 -- implicitly determines which objects need new statistics, and
3747 -- determines how to gather those statistics. When 'GATHER AUTO' is
3748 -- specified, the only additional valid parameters are no_invalidate,
3749 -- comp_id, stattab,
3750 -- statid and statown; all other parameter settings will be
3751 -- ignored. Also, return a list of objects processed.
3752 -- 'GATHER STALE' - gather statistics on stale objects as determined
3753 -- by looking at the *_tab_modifications views. Also, return
3754 -- a list of objects found to be stale.
3755 -- 'GATHER EMPTY' - gather statistics on objects which currently
3756 -- have no statistics. also, return a list of objects found
3757 -- to have no statistics.
3758 -- 'LIST AUTO' - return list of objects to be processed with 'GATHER AUTO'
3759 -- 'LIST STALE' - return list of stale objects as determined
3760 -- by looking at the *_tab_modifications views
3761 -- 'LIST EMPTY' - return list of objects which currently
3762 -- have no statistics
3763 -- objlist - list of objects found to be stale or empty
3764 -- statown - The schema containing stattab (if different from current schema)
3765 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
3766 -- The procedure invalidates the dependent cursors immediately
3767 -- if set to FALSE.
3768 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
3769 -- invalidate dependend cursors. This is the default. The default
3770 -- can be changed using set_param procedure.
3771 -- When 'cascade' option is specified, not pertinent with certain types
3772 -- of indexes described in the gather_index_stats section.
3773 -- obj_filter_list - a list of object filters. When provided,
3774 -- gather_dictionary_stats will only gather statistics on the objects which
3775 -- satisfy at least one of the object filters as needed. Please refer to
3776 -- obj_filter_list in gather_database_stats
3777 -- Exceptions:
3778 -- ORA-20000: Schema does not exist or insufficient privileges
3779 -- ORA-20001: Bad input value
3780 -- ORA-20002: Bad user statistics table, may need to upgrade it
3781 --
3785 estimate_percent number default DEFAULT_ESTIMATE_PERCENT,
3782
3783 function report_gather_dictionary_stats
3784 (comp_id varchar2 default null,
3786 block_sample boolean default FALSE,
3787 method_opt varchar2 default DEFAULT_METHOD_OPT,
3788 degree number default DEFAULT_DEGREE_VALUE,
3789 granularity varchar2 default DEFAULT_GRANULARITY,
3790 cascade boolean default DEFAULT_CASCADE,
3791 stattab varchar2 default null, statid varchar2 default null,
3792 options varchar2 default 'GATHER AUTO', statown varchar2 default null,
3793 no_invalidate boolean default
3794 to_no_invalidate_type(get_param('NO_INVALIDATE')),
3795 stattype varchar2 default 'DATA',
3796 obj_filter_list ObjectTab default null,
3797 detail_level varchar2 default 'TYPICAL',
3798 format varchar2 default 'TEXT')
3799 return clob;
3800
3801 --
3802 -- This procedure runs gather_dictionary_stats in reporting mode. That is,
3803 -- stats are not actually collected, but all the objects that will be
3804 -- affected when gather_dictionary_stats is invoked are reported.
3805 -- The detail level for the report is defined by the detail_level
3806 -- input parameter. Please see the comments for report_single_stats_operation
3807 -- on possible values for detail_level and format.
3808 -- For all other input parameters, please see the comments on
3809 -- gather_dictionary_stats.
3810
3811 procedure delete_dictionary_stats(
3812 stattab varchar2 default null, statid varchar2 default null,
3813 statown varchar2 default null,
3814 no_invalidate boolean default
3815 to_no_invalidate_type(get_param('NO_INVALIDATE')),
3816 stattype varchar2 default 'ALL',
3817 force boolean default FALSE,
3818 stat_category varchar2 default DEFAULT_DEL_STAT_CATEGORY);
3819 --
3820 -- Deletes statistics for all dictionary schemas ('SYS', 'SYSTEM' and
3821 -- RDBMS component schemas)
3822 --
3823 -- To run this procedure, you must have the SYSDBA OR
3824 -- both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.
3825 --
3826 -- Input arguments:
3827 -- stattab - The user stat table identifier describing from where
3828 -- to delete the statistics. If stattab is null, the statistics
3829 -- will be deleted directly in the dictionary.
3830 -- statid - The (optional) identifier to associate with these statistics
3831 -- within stattab (Only pertinent if stattab is not NULL).
3832 -- statown - The schema containing stattab (if different from current schema)
3833 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
3834 -- The procedure invalidates the dependent cursors immediately
3835 -- if set to FALSE.
3836 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
3837 -- invalidate dependend cursors. This is the default. The default
3838 -- can be changed using set_param procedure.
3839 -- stattype - The type of statistics to be deleted
3840 -- ALL - both data and cache statistics will be deleted
3841 -- CACHE - only cache statistics will be deleted
3842 -- force - Ignores the statistics lock on objects and delete
3843 -- the statistics if set to TRUE.
3844 -- stat_category - what statistics to delete. It accepts multiple values
3845 -- separated by comma. The values we support now are 'OBJECT_STATS'
3846 -- (i.e., table statistics, column statistics and index statistics) and
3847 -- 'SYNOPSES'. The default is 'OBJECT_STATS, SYNOPSES'
3848 --
3849 -- Exceptions:
3850 -- ORA-20000: Object does not exist or insufficient privileges
3851 -- ORA-20002: Bad user statistics table, may need to upgrade it
3852 --
3853
3854 procedure export_dictionary_stats(
3855 stattab varchar2, statid varchar2 default null,
3856 statown varchar2 default null,
3857 stat_category varchar2 default DEFAULT_STAT_CATEGORY);
3858 --
3859 -- Retrieves statistics for all dictionary schemas ('SYS', 'SYSTEM' and
3860 -- RDBMS component schemas) and stores them in the user stat table
3861 -- identified by stattab
3862 --
3863 -- To run this procedure, you must have the SYSDBA OR
3864 -- both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.
3865 --
3866 -- Input arguments:
3867 -- stattab - The user stat table identifier describing where
3868 -- to store the statistics.
3869 -- statid - The (optional) identifier to associate with these statistics
3870 -- within stattab.
3871 -- statown - The schema containing stattab (if different from current schema)
3872 -- stat_category - what statistics to export. It accepts multiple values
3873 -- separated by comma. The values we support now are 'OBJECT_STATS'
3874 -- (i.e., table statistics, column statistics and index statistics) and
3875 -- 'SYNOPSES'. However SYNOPSES can only be exported along with
3876 -- OBJECT_STATS. Therefore only valid combinations are
3877 -- OBJECT_STATS
3878 -- OBJECT_STATS, SYNOPSES
3879 -- The default value is OBJECT_STATS that can be changed usig statistics
3880 -- preference.
3881 --
3882 -- Exceptions:
3883 -- ORA-20000: Object does not exist or insufficient privileges
3884 -- ORA-20002: Bad user statistics table, may need to upgrade it
3885 --
3886 procedure import_dictionary_stats(
3887 stattab varchar2, statid varchar2 default null,
3888 statown varchar2 default null,
3889 no_invalidate boolean default
3890 to_no_invalidate_type(get_param('NO_INVALIDATE')),
3891 force boolean default FALSE,
3892 stat_category varchar2 default DEFAULT_STAT_CATEGORY);
3896 -- the dictionary
3893 --
3894 -- Retrieves statistics for all dictionary schemas ('SYS', 'SYSTEM' and
3895 -- RDBMS component schemas) from the user stat table and stores them in
3897 -- The statistics will be imported as pending in case PUBLISH preference
3898 -- is set to FALSE.
3899 --
3900 -- To run this procedure, you must have the SYSDBA OR
3901 -- both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.
3902 --
3903 -- Input arguments:
3904 -- stattab - The user stat table identifier describing from where
3905 -- to retrieve the statistics.
3906 -- statid - The (optional) identifier to associate with these statistics
3907 -- within stattab.
3908 -- statown - The schema containing stattab (if different from current schema)
3909 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
3910 -- The procedure invalidates the dependent cursors immediately
3911 -- if set to FALSE.
3912 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
3913 -- invalidate dependend cursors. This is the default. The default
3914 -- can be changed using set_param procedure.
3915 -- force - Override statistics lock.
3916 -- TRUE- Ignores the statistics lock on objects and import
3917 -- the statistics.
3918 -- FALSE-The statistics of an object will be imported only if it
3919 -- is not locked.
3920 -- ie if both DATA and CACHE statistics is locked, it will not
3921 -- import anything. If CACHE statistics of an object is locked,
3922 -- only DATA statistics will be imported and vice versa.
3923 -- stat_category - what statistics to import. It accepts multiple values
3924 -- separated by comma. The values we support now are 'OBJECT_STATS'
3925 -- (i.e., table statistics, column statistics and index statistics) and
3926 -- 'SYNOPSES'. However SYNOPSES can only be imported along with
3927 -- OBJECT_STATS. Therefore only valid combinations are
3928 -- OBJECT_STATS
3929 -- OBJECT_STATS, SYNOPSES
3930 -- The default value is OBJECT_STATS that can be changed usig statistics
3931 -- preference.
3932 --
3933 -- Exceptions:
3934 -- ORA-20000: Object does not exist or insufficient privileges
3935 -- if ORA-20000 shows "no statistics are imported", several
3936 -- possible reasons are: (1) user specified statid does not
3937 -- exist; (2) statistics are locked; (3) objects in the
3938 -- stattab no longer exist in the current database
3939 -- ORA-20001: Invalid or inconsistent values in the user stat table
3940 -- ORA-20002: Bad user statistics table, may need to upgrade it
3941 --
3942
3943 procedure lock_table_stats(
3944 ownname varchar2,
3945 tabname varchar2,
3946 stattype varchar2 default 'ALL');
3947 --
3948 -- This procedure enables the user to lock the statistics on the table
3949 --
3950 -- Input arguments:
3951 -- ownname - schema of table to lock
3952 -- tabname - name of the table
3953 -- stattype - type of statistics to be locked
3954 -- 'CACHE' - lock only caching statistics
3955 -- 'DATA' - lock only data statistics
3956 -- 'ALL' - lock both data and caching statistics. This is the default
3957
3958
3959 procedure lock_partition_stats(
3960 ownname varchar2,
3961 tabname varchar2,
3962 partname varchar2);
3963
3964 --
3965 -- This procedure enables the user to lock statistics for a partition
3966 --
3967 -- Input arguments:
3968 -- ownname - schema of the table to lock
3969 -- tabname - name of the table
3970 -- partname - name of the partition
3971 --
3972
3973
3974 procedure lock_schema_stats(
3975 ownname varchar2,
3976 stattype varchar2 default 'ALL');
3977
3978 --
3979 -- This procedure enables the user to lock the statistics of all
3980 -- tables of a schema
3981 --
3982 -- Input arguments:
3983 -- ownname - schema of tables to lock
3984 -- stattype - type of statistics to be locked
3985 -- 'CACHE' - lock only caching statistics
3986 -- 'DATA' - lock only data statistics
3987 -- 'ALL' - lock both data and caching statistics. This is the default
3988
3989
3990 procedure unlock_table_stats(
3991 ownname varchar2,
3992 tabname varchar2,
3993 stattype varchar2 default 'ALL');
3994 --
3995 -- This procedure enables the user to unlock the statistics on the table
3996 --
3997 -- Input arguments:
3998 -- ownname - schema of table to unlock
3999 -- tabname - name of the table
4000 -- stattype - type of statistics to be unlocked
4001 -- 'CACHE' - unlock only caching statistics
4002 -- 'DATA' - unlock only data statistics
4003 -- 'ALL' - unlock both data and caching statistics. This is the default
4004
4005
4006 procedure unlock_partition_stats(
4007 ownname varchar2,
4008 tabname varchar2,
4009 partname varchar2);
4010
4011 --
4012 -- This procedure enables the user to unlock statistics for a partition
4013 --
4014 -- Input arguments:
4015 -- ownname - schema of table to unlock
4016 -- tabname - name of the table
4017 -- partname - name of the partition
4018 --
4019
4020
4021 procedure unlock_schema_stats(
4022 ownname varchar2,
4023 stattype varchar2 default 'ALL');
4024 --
4025 -- This procedure enables the user to unlock the statistics of all
4026 -- tables of a schema
4027 --
4031 -- 'CACHE' - unlock only caching statistics
4028 -- Input arguments:
4029 -- ownname - schema of tables to unlock
4030 -- stattype - type of statistics to be unlocked
4032 -- 'DATA' - unlock only data statistics
4033 -- 'ALL' - unlock both data and caching statistics. This is the default
4034
4035 procedure restore_table_stats(
4036 ownname varchar2,
4037 tabname varchar2,
4038 as_of_timestamp timestamp with time zone,
4039 restore_cluster_index boolean default FALSE,
4040 force boolean default FALSE,
4041 no_invalidate boolean default
4042 to_no_invalidate_type(get_param('NO_INVALIDATE')));
4043 --
4044 -- This procedure enables the user to restore statistics of a table as of
4045 -- a specified timestamp (as_of_timestamp). The procedure will restore
4046 -- statistics of associated indexes and columns as well. If the table
4047 -- statistics were locked at the specified timestamp the procedure will
4048 -- lock the statistics.
4049 -- Note:
4050 -- The procedure may not restore statistics correctly if analyze interface
4051 -- is used for computing/deleting statistics.
4052 -- Old statistics versions are not saved when SYSAUX tablespace is
4053 -- offline, this affects restore functionality.
4054 -- The procedure may not restore statistics if the table defn is
4055 -- changed (eg: column added/deleted, partition exchanged etc).
4056 -- Also it will not restore stats if the object is created after
4057 -- the specified timestamp.
4058 -- The procedure will not restore user defined statistics.
4059 -- Input arguments:
4060 -- ownname - schema of table for which statistics to be restored
4061 -- tabname - table name
4062 -- as_of_timestamp - statistics as of this timestamp will be restored.
4063 -- restore_cluster_index - If the table is part of a cluster,
4064 -- restore statistics of the cluster index if set to TRUE.
4065 -- force - restore statistics even if the table statistics are locked.
4066 -- if the table statistics were not locked at the specified
4067 -- timestamp, it will unlock the statistics
4068 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
4069 -- The procedure invalidates the dependent cursors immediately
4070 -- if set to FALSE.
4071 -- The procedure invalidates the dependent cursors immediately
4072 -- if set to FALSE.
4073 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
4074 -- invalidate dependend cursors. This is the default. The default
4075 -- can be changed using set_param procedure.
4076 --
4077 -- Exceptions:
4078 -- ORA-20000: Object does not exist or insufficient privileges
4079 -- ORA-20001: Invalid or inconsistent values
4080 -- ORA-20006: Unable to restore statistics , statistics history not available
4081
4082 procedure restore_schema_stats(
4083 ownname varchar2,
4084 as_of_timestamp timestamp with time zone,
4085 force boolean default FALSE,
4086 no_invalidate boolean default
4087 to_no_invalidate_type(get_param('NO_INVALIDATE')));
4088 --
4089 -- This procedure enables the user to restore statistics of all tables of
4090 -- a schema as of a specified timestamp (as_of_timestamp).
4091
4092 -- Input arguments:
4093 -- ownname - schema of tables for which statistics to be restored
4094 -- as_of_timestamp - statistics as of this timestamp will be restored.
4095 -- force - restore statistics of tables even if their statistics are locked.
4096 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
4097 -- The procedure invalidates the dependent cursors immediately
4098 -- if set to FALSE.
4099 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
4100 -- invalidate dependend cursors. This is the default. The default
4101 -- can be changed using set_param procedure.
4102 --
4103 -- Exceptions:
4104 -- ORA-20000: Object does not exist or insufficient privileges
4105 -- ORA-20001: Invalid or inconsistent values
4106 -- ORA-20006: Unable to restore statistics , statistics history not available
4107
4108 procedure restore_database_stats(
4109 as_of_timestamp timestamp with time zone,
4110 force boolean default FALSE,
4111 no_invalidate boolean default
4112 to_no_invalidate_type(get_param('NO_INVALIDATE')));
4113 --
4114 -- This procedure enables the user to restore statistics of all tables of
4115 -- the database as of a specified timestamp (as_of_timestamp).
4116
4117 -- Input arguments:
4118 -- as_of_timestamp - statistics as of this timestamp will be restored.
4119 -- force - restore statistics of tables even if their statistics are locked.
4120 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
4121 -- The procedure invalidates the dependent cursors immediately
4122 -- if set to FALSE.
4123 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
4124 -- invalidate dependend cursors. This is the default. The default
4125 -- can be changed using set_param procedure.
4126 --
4127 -- Exceptions:
4128 -- ORA-20000: Insufficient privileges
4129 -- ORA-20001: Invalid or inconsistent values
4130 -- ORA-20006: Unable to restore statistics , statistics history not available
4131
4132 procedure restore_fixed_objects_stats(
4133 as_of_timestamp timestamp with time zone,
4134 force boolean default FALSE,
4135 no_invalidate boolean default
4136 to_no_invalidate_type(get_param('NO_INVALIDATE')));
4137 --
4141 -- system privilege.
4138 -- This procedure enables the user to restore statistics of all fixed tables
4139 -- as of a specified timestamp (as_of_timestamp).
4140 -- To run this procedure, you must have the SYSDBA or ANALYZE ANY DICTIONARY
4142 --
4143 -- Input arguments:
4144 -- as_of_timestamp - statistics as of this timestamp will be restored.
4145 -- force - restore statistics of tables even if their statistics are locked.
4146 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
4147 -- The procedure invalidates the dependent cursors immediately
4148 -- if set to FALSE.
4149 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
4150 -- invalidate dependend cursors. This is the default. The default
4151 -- can be changed using set_param procedure.
4152 --
4153 -- Exceptions:
4154 -- ORA-20000: Insufficient privileges
4155 -- ORA-20001: Invalid or inconsistent values
4156 -- ORA-20006: Unable to restore statistics , statistics history not available
4157
4158 procedure restore_dictionary_stats(
4159 as_of_timestamp timestamp with time zone,
4160 force boolean default FALSE,
4161 no_invalidate boolean default
4162 to_no_invalidate_type(get_param('NO_INVALIDATE')));
4163 --
4164 -- This procedure enables the user to restore statistics of all dictionary
4165 -- tables (tables of 'SYS', 'SYSTEM' and RDBMS component schemas)
4166 -- as of a specified timestamp (as_of_timestamp).
4167 --
4168 -- To run this procedure, you must have the SYSDBA OR
4169 -- both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.
4170 --
4171 -- Input arguments:
4172 -- as_of_timestamp - statistics as of this timestamp will be restored.
4173 -- force - restore statistics of tables even if their statistics are locked.
4174 -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
4175 -- The procedure invalidates the dependent cursors immediately
4176 -- if set to FALSE.
4177 -- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
4178 -- invalidate dependend cursors. This is the default. The default
4179 -- can be changed using set_param procedure.
4180 --
4181 -- Exceptions:
4182 -- ORA-20000: Insufficient privileges
4183 -- ORA-20001: Invalid or inconsistent values
4184 -- ORA-20006: Unable to restore statistics , statistics history not available
4185
4186 procedure restore_system_stats(
4187 as_of_timestamp timestamp with time zone);
4188 --
4189 -- This procedure enables the user to restore system statistics
4190 -- as of a specified timestamp (as_of_timestamp).
4191 --
4192 -- Input arguments:
4193 -- as_of_timestamp - statistics as of this timestamp will be restored.
4194 --
4195 -- Exceptions:
4196 -- ORA-20000: Insufficient privileges
4197 -- ORA-20001: Invalid or inconsistent values
4198 -- ORA-20006: Unable to restore statistics , statistics history not available
4199
4200 procedure purge_stats(
4201 before_timestamp timestamp with time zone);
4202 --
4203 -- This procedure enables the user to purge old versions of statistics
4204 -- saved in dictionary
4205 --
4206 -- To run this procedure, you must have the SYSDBA OR
4207 -- both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.
4208 --
4209 -- Input arguments:
4210 -- before_timestamp - versions of statistics saved before this timestamp
4211 -- will be purged. if null, it uses the purging policy
4212 -- used by automatic purge. The automatic purge deletes all
4213 -- history older than
4214 -- min(current time - stats history retention,
4215 -- time of recent analyze in the system - 1).
4216 -- stats history retention value can be changed using
4217 -- alter_stats_history_retention procedure.
4218 -- The default is 31 days.
4219 --
4220 -- When before_timestamp is specified as DBMS_STATS.PURGE_ALL, all stats
4221 -- history tables are truncated. Please note that interrupting
4222 -- (e.g., hitting Ctrl-C) purge_stats while it is running with PURGE_ALL
4223 -- option may lead to inconsistencies. Hence, please avoid interrupting
4224 -- purge_stats manually.
4225 --
4226 --
4227 -- Exceptions:
4228 -- ORA-20000: Insufficient privileges
4229 -- ORA-20001: Invalid or inconsistent values
4230
4231 procedure alter_stats_history_retention(
4232 retention in number);
4233 --
4234 -- This procedure enables the user to change stats history retention
4235 -- value. Stats history retention is used by both the automatic
4236 -- purge and purge_stats procedure.
4237 --
4238 --
4239 -- To run this procedure, you must have the SYSDBA OR
4240 -- both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.
4241 --
4242 -- Input arguments:
4243 -- retention - The retention time in days. The stats history will be
4244 -- ratained for at least these many number of days.
4245 -- The valid range is [1,365000]. Also the following
4246 -- values can be used for special purposes.
4247 -- 0 - old stats are never saved. The automatic purge will
4248 -- delete all stats history
4249 -- -1 - stats history is never purged by automatic purge.
4250 -- null - change stats history retention to default value
4251
4252 --
4253
4254 function get_stats_history_retention return number;
4255
4256 -- This function returns the current retention value.
4257
4258 function get_stats_history_availability
4262 -- is available.
4259 return timestamp with time zone;
4260
4261 -- This function returns oldest timestamp where stats history
4263 -- Users can not restore stats to timestamp older than this one.
4264
4265
4266 procedure copy_table_stats(
4267 ownname varchar2,
4268 tabname varchar2,
4269 srcpartname varchar2,
4270 dstpartname varchar2,
4271 scale_factor number DEFAULT 1,
4272 flags number DEFAULT null,
4273 force boolean DEFAULT FALSE);
4274
4275 --
4276 -- This procedure copies the statistics of the source [sub] partition to the
4277 -- destination [sub] partition. It also copies statistics of all dependent
4278 -- objects such as columns and local indexes. If the statistics for source
4279 -- are not available, then nothing is copied. It can optionally scale the
4280 -- statistics (such as the number of blks, or number of rows) based on the
4281 -- given scale_factor.
4282 --
4283 -- Usage notes:
4284 --
4285 -- a) To invoke this procedure you must be owner of the table, or you need
4286 -- the ANALYZE ANY privilege. For objects owned by SYS, you need to be
4287 -- either the owner of the table, or you need the ANALYZE ANY DICTIONARY
4288 -- privilege or the SYSDBA privilege.
4289 --
4290 -- b) This procedure updates the minimum and maximum values of destination
4291 -- partition for the first partitioning column as follows:
4292 --
4293 -- - If the partitioning type is HASH the minimum and maximum values of the
4294 -- destination partition are same as that of the source partition.
4295 --
4296 -- - If the partitioning type is LIST then
4297 -- if the destination partition is a NOT DEFAULT partition then
4298 --
4299 -- The minimum value of the destination partition is set to the
4300 -- minimum value of the value list that describes the destination
4301 -- partition.
4302 -- The maximum value of the destination partition is set to the
4303 -- maximum value of the value list that describes the destination
4304 -- partition.
4305 --
4306 -- Alternatively, if the destination partition is a DEFAULT partition, then
4307 -- The minimum value of the destination partition is set to the minimum
4308 -- value of the source partition
4309 -- The maximum value of the destination partition is set to the maximum
4310 -- value of the source partition
4311 --
4312 -- - If the partitioning type is RANGE then
4313
4314 -- The minimum value of the destination partition is set to the high bound
4315 -- of previous partition unless the destination partition is the first
4316 -- partition. For the first partition, the minimum value is set to the
4317 -- high bound of the destination partition.
4318 -- The maximum value of the destination partition is set to the high bound
4319 -- of the destination partition unless the high bound of the destination
4320 -- partition is MAXVALUE, in which case the maximum value of the
4321 -- destination partition is set to the high bound of the previous
4322 -- partition.
4323 --
4324 -- c) Additional modification to minimum and maximum values if the
4325 -- partitioning type is RANGE:
4326 --
4327 -- - If the source partition column's minimum value is equal to its maximum
4328 -- value, and both are equal to the source partition's lower bound, and
4329 -- it has a single distinct value, then the destination partition column's
4330 -- minimum and maximum values are both set to the destination partition's
4331 -- lower bound. This is done for all partitioning columns.
4332 --
4333 -- - If the above condition does not apply, second and subsequent
4334 -- partitioning columns are updated as follows:
4335 -- The destination partition column's maximum value is set to the greater
4336 -- of the destination partition upper bound and the source partition
4337 -- column's maximum value, with the following exception: if the
4338 -- destination partition is D and its preceding partition is D-1 and the
4339 -- key column to be adjusted is Cn, the maximum value for Cn is set to
4340 -- the upper bound of D (ignoring the maximum value of the source
4341 -- partition column) provided that the upper bounds of the previous key
4342 -- column Cn-1 are the same in partitions D and D-1.
4343 --
4344 -- d) If the minimum and maximum values are different for a column after
4345 -- modifications, and if the number of distinct values is less than 1,
4346 -- the number of disctinct values is updated as 2.
4347 --
4348 -- e) This procedure does not copy statistics of the underlying subpartitions
4349 -- if the source/destination is a partition of a composite partitioned table.
4350
4351 -- Parameters:
4352 -- ownname - Schema of the table of source and destination
4353 -- [sub] partitions
4354 -- tabname - Table name of source and destination [sub] partitions
4355 -- srcpartname - Source [sub] partition
4356 -- dtspartname - Destination [sub] partition
4357 -- scale_factor - Scale factor to scale nblks, nrows etc. in dstpartname
4358 -- flags - For internal Oracle use (should be left as NULL)
4359 -- force - When value of this argument is TRUE copy statistics even
4360 -- if the destination [sub]partition is locked
4361
4362 -- Exceptions:
4363 -- ORA-20000: Invalid partition name
4364 -- ORA-20001: Bad input value
4365 --
4366
4367
4368 function diff_table_stats_in_stattab(
4369 ownname varchar2,
4370 tabname varchar2,
4371 stattab1 varchar2,
4372 stattab2 varchar2 default null,
4373 pctthreshold number default 10,
4374 statid1 varchar2 default null,
4378 return DiffRepTab pipelined;
4375 statid2 varchar2 default null,
4376 stattab1own varchar2 default null,
4377 stattab2own varchar2 default null)
4379
4380 -- Input arguments:
4381 -- ownname - owner of the table. Specify null for current schema.
4382 -- tabname - table for which statistics are to be compared.
4383 -- stattab1 - user stats table 1.
4384 -- stattab2 - user stats table 2. If null, statistics in stattab1
4385 -- is compared with current statistics in dictionary.
4386 -- This is the default.
4387 -- Specify same table as stattab1 to compare two sets
4388 -- within the stats table (Please see statid below).
4389 -- pctthreshold - The function report difference in statistics
4390 -- only if it exceeds this limit. The default value is 10.
4391 -- statid1 - (optional) identifies statistics set within stattab1.
4392 -- statid2 - (optional) identifies statistics set within stattab2.
4393 -- stattab1own - The schema containing stattab1 (if different than ownname)
4394 -- stattab2own - The schema containing stattab2 (if different than ownname)
4395 --
4396
4397 function diff_table_stats_in_history(
4398 ownname varchar2,
4399 tabname varchar2,
4400 time1 timestamp with time zone,
4401 time2 timestamp with time zone default null,
4402 pctthreshold number default 10)
4403 return DiffRepTab pipelined;
4404
4405 -- Input arguments:
4406 -- ownname - owner of the table. Specify null for current schema.
4407 -- tabname - table for which statistics are to be compared.
4408 -- time1 - first time stamp
4409 -- time2 - second time stamp
4410 --
4411 -- pctthreshold - The function report difference in statistics
4412 -- only if it exceeds this limit. The default value is 10.
4413 --
4414 -- NOTE:
4415 -- If the second timestamp is null, the function compares the current
4416 -- statistics in dictionary with the statistics as of the other timestamp.
4417
4418 function diff_table_stats_in_pending(
4419 ownname varchar2,
4420 tabname varchar2,
4421 time_stamp timestamp with time zone default null,
4422 pctthreshold number default 10)
4423 return DiffRepTab pipelined;
4424
4425 -- Input arguments:
4426 -- ownname - owner of the table. Specify null for the current schema.
4427 -- tabname - table for which statistics are to be compared.
4428 -- time_stamp - time stamp to get statistics from the history
4429 --
4430 -- pctthreshold - The function report difference in statistics
4431 -- only if it exceeds this limit. The default value is 10.
4432 --
4433 -- NOTE:
4434 -- If the time_stamp parameter is null, the function compares the current
4435 -- statistics in the dictionary with the pending statistics. This is the
4436 -- default
4437
4438
4439
4440 function create_extended_stats(
4441 ownname varchar2,
4442 tabname varchar2,
4443 extension varchar2)
4444 return varchar2;
4445
4446 -- This function creates a column stats entry in the system for a user
4447 -- specified column group or an expression in a table. Statistics for this
4448 -- extension will be gathered when user or auto statistics gathering job
4449 -- gathers statistics for the table. We call statistics for such an extension,
4450 -- "extended statistics". This function returns the name of this newly created
4451 -- entry for the extension. If the extension already exists then this function
4452 -- throws an error.
4453
4454 --
4455 -- Parameters:
4456 -- ownname -- owner name of a table
4457 -- tabname -- table name
4458 -- extension -- can be either a column group or an expression. Suppose
4459 -- the specified table has two column c1, c2. An example
4460 -- column group can be '(c1, c2)', an example expression
4461 -- can be '(c1 + c2)'.
4462 --
4463 -- Notes:
4464 --
4465 -- 1. An extension cannot contain a virtual column.
4466 --
4467 -- 2. You can not create extensions on tables owned by SYS.
4468 --
4469 -- 3. You can not create extensions on cluster tables, index organized
4470 -- tables, temporary tables, external tables.
4471 --
4472 -- 4. Total number of extensions in a table cannot be greater than
4473 -- maximum of (20, 10 % of number of non-virtual columns in the table).
4474 --
4475 -- 5. Number of columns in a column group must be in the range [2, 32].
4476 --
4477 -- 6. A column can not appear more than once in a column group.
4478 --
4479 -- 7. Column group can not contain expressions.
4480 --
4481 -- 8. An expression must contain at least one column.
4482 --
4483 -- 9. An expression can not contain subquery.
4484 --
4485 -- 10. COMPATIBLE parameter needs to be 11.0.0.0.0 or greater.
4486 --
4487 -- Exceptions:
4488 --
4489 -- ORA-20000: Insufficient privileges / creating extension is not supported
4490 --
4491 -- ORA-20001: Error when processing extension
4492 --
4493 -- ORA-20007: Extension already exist
4494 --
4495 -- ORA-20008: Reached the upper limit on number of extensions
4496 --
4497
4498 function create_extended_stats(
4499 ownname varchar2,
4500 tabname varchar2)
4501 return clob;
4502
4503 -- This function is very similar to the function above but creates statistics
4507 --
4504 -- extension based on the column group usage recorded by seed_col_usage
4505 -- procedure. This function returns a report of extensions created.
4506 --
4508 -- Parameters:
4509 -- ownname -- owner name of a table. If null, all schemas in the
4510 -- database.
4511 -- tabname -- table name. If null, creates statistics extensions
4512 -- for all tables of ownname.
4513
4514 function show_extended_stats_name(
4515 ownname varchar2,
4516 tabname varchar2,
4517 extension varchar2)
4518 return varchar2;
4519
4520 -- This function returns the name of the statistics entry that is created for
4521 -- the user specified extension. It raises error if no extension is created
4522 -- yet
4523 --
4524 --
4525 -- Parameters:
4526 -- ownname -- owner name of a table
4527 -- tabname -- table name
4528 -- extension -- can be either a column group or an expression
4529 -- (see description in create_extended_stats)
4530 -- Exceptions:
4531 -- ORA-20000: Insufficient privileges or extension does not exist.
4532 -- ORA-20001: Error when processing extension
4533
4534 procedure drop_extended_stats(
4535 ownname varchar2,
4536 tabname varchar2,
4537 extension varchar2);
4538
4539 -- This function drops the statistics entry that is created for the user
4540 -- specified extension. This cancels the effects of created_extended_stats.
4541 -- If no extension is created for the extension, this function
4542 -- throws an error.
4543 --
4544 --
4545 -- Parameters:
4546 -- ownname -- owner name of a table
4547 -- tabname -- table name
4548 -- extension -- can be either a column group or an expression
4549 -- (see description in create_extended_stats)
4550 -- Exceptions:
4551 -- ORA-20000: Insufficient privileges or extension does not exist.
4552 -- ORA-20001: Error when processing extension
4553 --
4554 -- The following procedure is for internal use only.
4555 --
4556 -- gather_database_stats_job_proc
4557 -- cleanup_stats_job_proc
4558 --
4559
4560
4561 procedure merge_col_usage(
4562 dblink varchar2);
4563
4564 --
4565 -- This procedure merges column usage information from a source database,
4566 -- specified via a dblink, into the local database.
4567 -- If the column usage information already exists for a given table and
4568 -- column then it will combine both the local and the remote information
4569 -- otherwise it will insert the remote information as new.
4570 -- This procedure is allowed to execute as SYS only, otherwise you will
4571 -- get an error message 'Insufficient privileges'.
4572 -- In addition the user specified during the creation of the dblink is
4573 -- expected to have privileges to select from tables in the SYS schema.
4574 --
4575 -- Parameters:
4576 -- dblink - dblink name
4577 --
4578 -- Exceptions:
4579 -- ORA-20000: Insufficient privileges
4580 -- ORA-20001: Parameter dblink cannot be null
4581 -- ORA-20002: Unable to create a temp table
4582 --
4583
4584 procedure transfer_stats(
4585 ownname IN VARCHAR2,
4586 tabname IN VARCHAR2,
4587 dblink IN VARCHAR2,
4588 options IN NUMBER DEFAULT null);
4589 --
4590 -- This procedure transfers statistics for specified table(s) from a remote
4591 -- database specified by dblink to the local database. It also transfers other
4592 -- statistics related structures like synopses, DML monitoring information etc.
4593 -- This procedure is allowed to execute as SYS only, otherwise you will
4594 -- get an error message 'Insufficient privileges'.
4595 -- In addition the user specified during the creation of the dblink is
4596 -- expected to have privileges to select from tables in the SYS schema.
4597 --
4598 --
4599 -- Parameters:
4600 -- dblink - dblink name
4601 -- ownname - owner name. If null transfers stats for all objects
4602 -- in all schemas in the database.
4603 -- tabname - table name. If null, transfers stats for all objects
4604 -- of ownname.
4605 -- options - different options. Specify ADD_GLOBAL_PREFS to
4606 -- transfer global preferences as well.
4607 --
4608 --
4609 -- Exceptions:
4610 -- ORA-20000: Insufficient privileges
4611 -- ORA-20001: Invalid input
4612 --
4613
4614 procedure seed_col_usage(
4615 sqlset_name IN VARCHAR2,
4616 owner_name IN VARCHAR2,
4617 time_limit IN POSITIVE DEFAULT NULL);
4618
4619 --
4620 -- This procedure seeds column usage information from a statements in
4621 -- the specified sql tuning set.
4622 -- The procedure will iterate over the SQL statements in the SQL tuning
4623 -- set and compile them in order to seed column usage information for
4624 -- the columns that appear in these statements. This procedure records
4625 -- group of columns as well. Extensions for the recorded group of columns
4626 -- can be created using create_extended_stats procedure.
4627 --
4628 -- Parameters:
4629 -- sqlset_name - sqlset name
4630 -- owner_name - owner name
4631 -- time_limit - time limit (in seconds). Default value is 1800s.
4632 --
4633 -- If sqlset_name and owner_name is null, it records the column (group) usage
4634 -- information for the statements executed in the system in next time_limit
4635 -- seconds.
4636 --
4637 -- Exceptions:
4641 procedure reset_col_usage(
4638 -- ORA-20000: Insufficient privileges
4639 --
4640
4642 ownname varchar2,
4643 tabname varchar2);
4644
4645 -- This procedure deletes the recorded column (group) usage information from
4646 -- dictionary. Column (group) usage information is used by gather procedures to
4647 -- automatically determine the columns that require histograms. Also this
4648 -- information is used by create_extended_stats to create extensions for the
4649 -- group of columns seen in the workload. So resetting column usage will affect
4650 -- these functionalities. This procedure should be used only in very rare cases
4651 -- where you need to start from scratch and need to seed column usage all over
4652 -- again.
4653 --
4654 -- Parameters:
4655 -- ownname - owner name. If null it resets column usage information
4656 -- for tables in all schemas in the database.
4657 -- tabname - table name. If null, resets column usage information
4658 -- for all tables of ownname.
4659 -- If ownname and tabname is null, it will stop seeding column usage, if
4660 -- currently seeding column usage using seed_col_usage.
4661
4662 function report_col_usage(
4663 ownname varchar2,
4664 tabname varchar2) return clob;
4665
4666 -- This procedure reports the recorded column (group) usage information.
4667 --
4668 -- Parameters:
4669 -- ownname - owner name. If null it reports column usage
4670 -- information for tables in all schemas in the database.
4671 -- tabname - table name. If null, it reports column usage
4672 -- information for all tables of ownname.
4673 -- Examples:
4674 -- The following example shows column usage information for customers table
4675 -- in SH schema.
4676 --
4677 --SQL> set long 100000
4678 --SQL> set lines 120
4679 --SQL> set pages 0
4680 --SQL>
4681 --SQL> -- Display column usage
4682 --SQL> select dbms_stats.report_col_usage('sh', 'customers') from dual;
4683 --LEGEND:
4684 --.......
4685 --
4686 --EQ : Used in single table EQuality predicate
4687 --RANGE : Used in single table RANGE predicate
4688 --LIKE : Used in single table LIKE predicate
4689 --NULL : Used in single table is (not) NULL predicate
4690 --EQ_JOIN : Used in EQuality JOIN predicate
4691 --NONEQ_JOIN : Used in NON EQuality JOIN predicate
4692 --FILTER : Used in single table FILTER predicate
4693 --JOIN : Used in JOIN predicate
4694 --GROUP_BY : Used in GROUP BY expression
4695 --.............................................................................
4696 --
4697 --#############################################################################
4698 --
4699 --COLUMN USAGE REPORT FOR SH.CUSTOMERS
4700 --....................................
4701 --
4702 --1. COUNTRY_ID : EQ
4703 --2. CUST_CITY : EQ
4704 --3. CUST_STATE_PROVINCE : EQ
4705 --4. (CUST_CITY, CUST_STATE_PROVINCE,
4706 -- COUNTRY_ID) : FILTER
4707 --5. (CUST_STATE_PROVINCE, COUNTRY_ID) : GROUP_BY
4708 --#############################################################################
4709
4710 -------------------------------------------------------------------------
4711 -- procedure set_processing_rate
4712 -------------------------------------------------------------------------
4713 --
4714 -- Sets the value of rate of processing for a given operation.
4715 --
4716 -- Input arguments:
4717 -- opName - Name of the operation
4718 -- Valid values: ALL, CPU, CPU_ACCESS, CPU_AGGR,
4719 -- CPU_BYTES_PER_SEC, CPU_FILTER, CPU_GBY, CPU_HASH_JOIN,
4720 -- CPU_JOIN, CPU_NL_JOIN, CPU_RANDOM_ACCESS,
4721 -- CPU_SEQUENTIAL_ACCESS, CPU_SM_JOIN, CPU_SORT, IO,
4722 -- IO_ACCESS, IO_BYTES_PER_SEC, IO_RANDOM_ACCESS,
4723 -- IO_SEQUENTIAL_ACCESS, HASH, AGGR, MEMCMP, MEMCPY
4724 -- procRate - Processing Rate
4725 --
4726 -- Output arguments: None
4727 --
4728 -- Privileges: The user issuing should have the role optimizer_processing_rate
4729 -- or dba
4730 --
4731 -- Exceptions:
4732 -- ORA-20000: Insufficient privileges
4733 -- ORA-20001: Invalid or inconsistent input values
4734 --
4735 procedure set_processing_rate (
4736 opName IN varchar2,
4737 procRate IN number);
4738
4739 -------------------------------------------------------------------------
4740 -- procedure delete_processing_rate
4741 -------------------------------------------------------------------------
4742 --
4743 -- deletes the processing rate of a given stat source. If the source is not
4744 -- specified, then it deletes the statistics of all the sources
4745 --
4746 -- Input arguments:
4747 -- stat_source - the source of processing rates can be 'MANUAL' or
4748 -- 'CALIBRATION'
4749 --
4750 -- Output arguments: None
4751 --
4752 -- Privileges: The user issuing should have the role optimizer_processing_rate
4753 -- or dba
4754 --
4755 -- Exceptions:
4756 -- ORA-20000: Insufficient privileges
4757 -- ORA-20001: Invalid or inconsistent input values
4758 --
4759 procedure delete_processing_rate (
4760 stat_source IN varchar2 DEFAULT NULL);
4761
4762 --------------------------- gather_processing_rate --------------------------
4763 -- NAME:
4764 -- gather_processing_rate - Gather Processing Rates
4765 --
4769 -- PARAMETERS:
4766 -- DESCRIPTION: This procedure starts the job of gathering the processing
4767 -- rates which end after 'timeLimit' seconds
4768 --
4770 -- gathering_mode - Mode can be 'START' and 'END'
4771 -- duration - time duration (number of minutes) for which the
4772 -- processing must be gathered. Default value is 60
4773 -- minutes.
4774 --
4775 -- Exceptions:
4776 -- ORA-20000: Insufficient privileges
4777 -- ORA-20001: Invalid or inconsistent input values
4778 --
4779 procedure gather_processing_rate(gathering_mode IN VARCHAR2 DEFAULT 'START',
4780 duration IN NUMBER DEFAULT NULL);
4781
4782 NULL_NUMTAB dbms_utility.number_array;
4783
4784 --------------------------- report_stats_operations ----------------------------------
4785 -- NAME:
4786 -- report_stats_operations
4787 --
4788 -- DESCRIPTION: This procedure reports statistics operations at different levels
4789 -- of detail with optional filters on the start time of operations.
4790 --
4791 -- PARAMETERS:
4792 -- detail_level (IN) detail level for the content of the report
4793 -- BASIC : The report includes:
4794 -- * operation id
4795 -- * operation name
4796 -- * operation target object
4797 -- * start time
4798 -- * end time
4799 -- * completion status (i.e., succeeded, failed, etc.)
4800 --
4801 -- TYPICAL (default): In addition to the information provided at level BASIC,
4802 -- at this level of detail, the report includes the following fields:
4803 -- * total number of target objects
4804 -- * total number of successfully completed objects
4805 -- * total number of failed objects
4806 -- * total number of timedout objects
4807 -- (applies to only auto stats gathering)
4808 -- ALL: In addition to the information provided at level TYPICAL,
4809 -- at this level of detail, the report includes the following details.
4810 -- * job name (if the operation was run in a job)
4811 -- * session id
4812 -- * parameter values
4813 -- * additional error details if the operation has failed.
4814 --
4815 -- format (IN) Report format. Suported formats are TEXT (default), HTML, XML.
4816 --
4817 -- latestN (IN) Include in the report only latest N operations
4818 -- (if null (default), no limit on the number of operations included
4819 -- in the report).
4820 -- since (IN) only include only stat operations that started since this date.
4821 -- when null (default), no lower limit on start time is applied.
4822 -- until (IN) only include stat operations that started before this date
4823 -- when null (default), no upper limit on start time is applied.
4824 -- auto_only (IN) TRUE: report only auto stats gathering operations
4825 -- FALSE: report all operations (default)
4826 --
4827 -- container_ids (IN) When provided, the report will include operations only from the input
4828 -- set of pluggable databases in a consolidated database environment.
4829 -- By default it is null. In a pluggable-database, only operations from
4830 -- the local database are reported, and in the root database,
4831 -- operations from all pluggable databases are reported.
4832 --
4833 --
4834 -- NOTES:
4835 -- Note that the type for container_ids input parameter is dbms_utility.number_array
4836 -- which is an associative PL/SQL array collection. Although associative array type
4837 -- allows for more flexible hash table-like organization of entries, this function treats
4838 -- container_ids as a regular table collection with the first id located at index 1 and
4839 -- the last id located at index "container_ids.count" without any empty array slot left
4840 -- between any two ids. An example for 3 container ids is provided below.
4841 --
4842 -- declare
4843 -- conid_tab dbms_utility.number_array;
4844 -- report clob;
4845 -- begin
4846 -- conid_tab(1) := 124;
4847 -- conid_tab(2) := 63;
4848 -- conid_tab(3) := 98;
4849 --
4850 -- report := dbms_stats.report_stats_operations(container_ids => conid_tab);
4851 -- end;
4852 ---------------------------------------------------------------------------------------
4853 function report_stats_operations(detail_level varchar2 default 'TYPICAL',
4854 format varchar2 default 'TEXT',
4855 latestN number default null,
4856 since timestamp with time zone default null,
4857 until timestamp with time zone default null,
4858 auto_only boolean default false,
4859 container_ids dbms_utility.number_array
4860 default dbms_stats.NULL_NUMTAB)
4861 return clob;
4862
4863 --------------------------- report_single_stats_operation -----------------------------
4864 -- NAME:
4865 -- report_single_stats_operation
4866 --
4870 -- opid (IN) internal id of the operation that will be reported. This
4867 -- DESCRIPTION: This procedure generates a report for the provided operation.
4868 --
4869 -- PARAMETERS:
4871 -- information may be obtained from the output of
4872 -- report_stats_operations or through querying
4873 -- DBA_OPTSTAT_OPERATIONS view.
4874 --
4875 -- detail_level (IN) detail level for the content of the report
4876 -- BASIC : The report includes:
4877 -- * operation id
4878 -- * operation name
4879 -- * operation target object
4880 -- * start time
4881 -- * end time
4882 -- * completion status (i.e., succeeded, failed, etc.)
4883 --
4884 -- TYPICAL (default): In addition to the information provided at level BASIC,
4885 -- at this level of detail, the report includes individual target objects
4886 -- for which statistics are gathered in this operation. More
4887 -- specifically:
4888 -- -- operation related details --
4889 -- * total number of target objects
4890 -- * total number of successfully completed objects
4891 -- * total number of failed objects
4892 -- * total number of timedout objects
4893 -- (applies to only auto stats gathering)
4894 --
4895 -- -- target object related details --
4896 -- * owner and name of each target object
4897 -- * target object type (e.g., table, index, etc.)
4898 -- * start time
4899 -- * end time
4900 -- * completion status
4901 --
4902 -- ALL: In addition to the information provided at level TYPICAL,
4903 -- at this level of detail, the report includes further information
4904 -- on each target object as follows:
4905 -- -- operation related details --
4906 -- * job name (if the operation was run in a job)
4907 -- * session id
4908 -- * parameter values
4909 -- * additional error details if the operation has failed.
4910 --
4911 -- -- target object related details --
4912 -- * job name
4913 -- * batching details
4914 -- * estimated cost
4915 -- * rank in the target list
4916 -- * columns for which histograms were collected
4917 -- * list of collected extended stats (if any)
4918 -- * reason for including the object in the target list
4919 -- (applies to only auto stats gathering)
4920 -- * additional error details if the task has failed.
4921 --
4922 -- format (IN) Report format. Suported formats are TEXT (default), HTML, XML.
4923 -- container_id (IN) container (pluggable database) id in a consolidated
4924 -- database environment. By default it is null. In a pluggable-database,
4925 -- only operations from the local database are reported, and in the root
4926 -- database, operations from all pluggable databases are reported.
4927 --
4928 -- NOTES:
4929 -- Note that some of the fields like estimated cost, batching details, job_name, etc.
4930 -- for individual tasks are only populated when the corresponding operation was run
4931 -- with concurrency enabled.
4932 --
4933 -------------------------------------------------------------------------------------
4934 function report_single_stats_operation(opid number,
4935 detail_level varchar2 default 'TYPICAL',
4936 format varchar2 default 'TEXT',
4937 container_id number default null) return clob;
4938
4939
4940 function report_gather_auto_stats (
4941 detail_level varchar2 default 'TYPICAL',
4942 format varchar2 default 'TEXT')
4943 return clob;
4944 --
4945 -- This procedure runs auto stats gathering job in reporting mode. That is,
4946 -- stats are not actually collected, but all the objects that will be
4947 -- affected when auto stats gathering is invoked are reported.
4948 -- The detail level for the report is defined by the detail_level
4949 -- input parameter. Please see the comments for report_single_stats_operation
4950 -- on possible values for detail_level and format.
4951
4952 procedure gather_database_stats_job_proc;
4953
4954 procedure cleanup_stats_job_proc(
4955 ctx number, job_owner varchar2, job_name varchar2,
4956 sesid number, sesser number);
4957
4958 -- internal use only
4959 procedure gen_selmap(
4960 owner varchar2,
4961 tabname varchar2,
4962 pname varchar2,
4963 spname varchar2,
4964 flag binary_integer,
4965 colinfo ColDictTab,
4966 selmap IN OUT NOCOPY SelTab,
4967 clist IN OUT NOCOPY CTab);
4968
4969 -- internal use only
4970 procedure postprocess_stats(
4971 owner varchar2,
4972 tabname varchar2,
4973 pname varchar2,
4974 spname varchar2,
4975 flag number,
4976 rawstats RawCTab,
4977 selmap SelTab,
4978 clist CTab
4979 );
4980
4981 -- internal use only
4982 function conv_raw (
4983 rawval raw,
4984 type number)
4985 return varchar2;
4986
4987 -- For internal use of datapump only
4988 procedure export_stats_for_dp (
4989 objlist_tab_own varchar2,
4990 objlist_tab varchar2,
4991 dblink in varchar2 default null,
4992 options in number default null,
4993 export_stats_since timestamp with time zone default null
4994 );
4995
4996 -- For internal use of datapump only
4997 procedure import_stats_for_dp (
4998 objlist_tab_own varchar2,
4999 objlist_tab varchar2,
5000 options in number default null
5001 );
5002
5003 -- For internal use
5004 function get_compatible return number;
5005
5006 -- For internal use
5007 function get_stat_tab_version return number;
5008
5009 -- For internal use
5010 function varray_to_clob(va DS_VARRAY_4_CLOB) return clob;
5011
5012 -- For internal use
5013 function clob_to_varray(cl clob) return DS_VARRAY_4_CLOB;
5014
5015 -- For internal use
5016 --
5017 -- Quickly estimate number of rows in the table for auto sampling
5018 --
5019 function get_row_count_estimate(
5020 ownname varchar2, -- table owner to select from
5021 tabname varchar2, -- table name to select from
5022 partname varchar2, -- table partition name
5023 nblks integer,
5024 degree integer
5025 ) return number;
5026
5027 end;
[Home] [Help]