Purpose

Gathering statistics is essential for the CBO to do his work.

Procedures/Functions

alter_database_tab_monitoring

procedure alter_database_tab_monitoring (
 monitoring                         in                  boolean default,
 sysobjs                            in                  boolean default
);

alter_schema_tab_monitoring

procedure alter_schema_tab_monitoring (
 ownname                            in                 varchar2 default,
 monitoring                         in                  boolean default
);

alter_stats_history_retention

procedure alter_stats_history_retention (
 retention                          in                   number        
);

convert_raw_value

procedure convert_raw_value (
 rawval                             in                      raw        ,
 resval                            out                 varchar2        
);

procedure convert_raw_value (
 rawval                             in                      raw        ,
 resval                            out                     date        
);

procedure convert_raw_value (
 rawval                             in                      raw        ,
 resval                            out                   number        
);

procedure convert_raw_value (
 rawval                             in                      raw        ,
 resval                            out             binary_float        
);

procedure convert_raw_value (
 rawval                             in                      raw        ,
 resval                            out            binary_double        
);

convert_raw_value_nvarchar

procedure convert_raw_value_nvarchar (
 rawval                             in                      raw        ,
 resval                            out                nvarchar2        
);

convert_raw_value_rowid

procedure convert_raw_value_rowid (
 rawval                             in                      raw        ,
 resval                            out                    rowid        
);

copy_table_stats

procedure copy_table_stats (
 ownname                            in                 varchar2        ,
 tabname                            in                 varchar2        ,
 srcpartname                        in                 varchar2        ,
 dstpartname                        in                 varchar2        ,
 flags                              in                   number default
);

create_stat_table

procedure create_stat_table (
 ownname                            in                 varchar2        ,
 stattab                            in                 varchar2        ,
 tblspace                           in                 varchar2 default
);

delete_column_stats

procedure delete_column_stats (
 ownname                            in                 varchar2        ,
 tabname                            in                 varchar2        ,
 colname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 cascade_parts                      in                  boolean default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 force                              in                  boolean default
);

delete_database_stats

procedure delete_database_stats (
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 stattype                           in                 varchar2 default,
 force                              in                  boolean default
);

delete_dictionary_stats

procedure delete_dictionary_stats (
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 stattype                           in                 varchar2 default,
 force                              in                  boolean default
);

delete_fixed_objects_stats

procedure delete_fixed_objects_stats (
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 force                              in                  boolean default
);

delete_index_stats

procedure delete_index_stats (
 ownname                            in                 varchar2        ,
 indname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 cascade_parts                      in                  boolean default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 stattype                           in                 varchar2 default,
 force                              in                  boolean default
);

delete_schema_stats

procedure delete_schema_stats (
 ownname                            in                 varchar2        ,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 stattype                           in                 varchar2 default,
 force                              in                  boolean default
);

delete_system_stats

procedure delete_system_stats (
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default
);

delete_table_stats

procedure delete_table_stats (
 ownname                            in                 varchar2        ,
 tabname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 cascade_parts                      in                  boolean default,
 cascade_columns                    in                  boolean default,
 cascade_indexes                    in                  boolean default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 stattype                           in                 varchar2 default,
 force                              in                  boolean default
);

drop_stat_table

procedure drop_stat_table (
 ownname                            in                 varchar2        ,
 stattab                            in                 varchar2        
);

export_column_stats

procedure export_column_stats (
 ownname                            in                 varchar2        ,
 tabname                            in                 varchar2        ,
 colname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2        ,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default
);

export_database_stats

procedure export_database_stats (
 stattab                            in                 varchar2        ,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default
);

export_dictionary_stats

procedure export_dictionary_stats (
 stattab                            in                 varchar2        ,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default
);

export_fixed_objects_stats

procedure export_fixed_objects_stats (
 stattab                            in                 varchar2        ,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default
);

export_index_stats

procedure export_index_stats (
 ownname                            in                 varchar2        ,
 indname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2        ,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default
);

export_schema_stats

procedure export_schema_stats (
 ownname                            in                 varchar2        ,
 stattab                            in                 varchar2        ,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default
);

export_system_stats

procedure export_system_stats (
 stattab                            in                 varchar2        ,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default
);

export_table_stats

procedure export_table_stats (
 ownname                            in                 varchar2        ,
 tabname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2        ,
 statid                             in                 varchar2 default,
 cascade                            in                  boolean default,
 statown                            in                 varchar2 default
);

flush_database_monitoring_info

procedure flush_database_monitoring_info (
);

gather_database_stats

procedure gather_database_stats (
 estimate_percent                   in                   number default,
 block_sample                       in                  boolean default,
 method_opt                         in                 varchar2 default,
 degree                             in                   number default,
 granularity                        in                 varchar2 default,
 cascade                            in                  boolean default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 options                            in                 varchar2 default,
 objlist                           out               dbms_stats        ,
 statown                            in                 varchar2 default,
 gather_sys                         in                  boolean default,
 no_invalidate                      in                  boolean default,
 gather_temp                        in                  boolean default,
 gather_fixed                       in                  boolean default,
 stattype                           in                 varchar2 default
);

procedure gather_database_stats (
 estimate_percent                   in                   number default,
 block_sample                       in                  boolean default,
 method_opt                         in                 varchar2 default,
 degree                             in                   number default,
 granularity                        in                 varchar2 default,
 cascade                            in                  boolean default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 options                            in                 varchar2 default,
 statown                            in                 varchar2 default,
 gather_sys                         in                  boolean default,
 no_invalidate                      in                  boolean default,
 gather_temp                        in                  boolean default,
 gather_fixed                       in                  boolean default,
 stattype                           in                 varchar2 default
);

gather_database_stats_job_proc

procedure gather_database_stats_job_proc (
);

gather_dictionary_stats

procedure gather_dictionary_stats (
 comp_id                            in                 varchar2 default,
 estimate_percent                   in                   number default,
 block_sample                       in                  boolean default,
 method_opt                         in                 varchar2 default,
 degree                             in                   number default,
 granularity                        in                 varchar2 default,
 cascade                            in                  boolean default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 options                            in                 varchar2 default,
 objlist                           out               dbms_stats        ,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 stattype                           in                 varchar2 default
);

procedure gather_dictionary_stats (
 comp_id                            in                 varchar2 default,
 estimate_percent                   in                   number default,
 block_sample                       in                  boolean default,
 method_opt                         in                 varchar2 default,
 degree                             in                   number default,
 granularity                        in                 varchar2 default,
 cascade                            in                  boolean default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 options                            in                 varchar2 default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 stattype                           in                 varchar2 default
);

gather_fixed_objects_stats

procedure gather_fixed_objects_stats (
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default
);

gather_index_stats

procedure gather_index_stats (
 ownname                            in                 varchar2        ,
 indname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 estimate_percent                   in                   number default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default,
 degree                             in                   number default,
 granularity                        in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 stattype                           in                 varchar2 default
);

gather_schema_stats

procedure gather_schema_stats (
 ownname                            in                 varchar2        ,
 estimate_percent                   in                   number default,
 block_sample                       in                  boolean default,
 method_opt                         in                 varchar2 default,
 degree                             in                   number default,
 granularity                        in                 varchar2 default,
 cascade                            in                  boolean default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 options                            in                 varchar2 default,
 objlist                           out               dbms_stats        ,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 gather_temp                        in                  boolean default,
 gather_fixed                       in                  boolean default,
 stattype                           in                 varchar2 default
);

procedure gather_schema_stats (
 ownname                            in                 varchar2        ,
 estimate_percent                   in                   number default,
 block_sample                       in                  boolean default,
 method_opt                         in                 varchar2 default,
 degree                             in                   number default,
 granularity                        in                 varchar2 default,
 cascade                            in                  boolean default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 options                            in                 varchar2 default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 gather_temp                        in                  boolean default,
 gather_fixed                       in                  boolean default,
 stattype                           in                 varchar2 default
);

gather_system_stats

procedure gather_system_stats (
 gathering_mode                     in                 varchar2 default,
 interval                           in               number(38) default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default
);

gather_table_stats

procedure gather_table_stats (
 ownname                            in                 varchar2        ,
 tabname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 estimate_percent                   in                   number default,
 block_sample                       in                  boolean default,
 method_opt                         in                 varchar2 default,
 degree                             in                   number default,
 granularity                        in                 varchar2 default,
 cascade                            in                  boolean default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 stattype                           in                 varchar2 default
);

generate_stats

procedure generate_stats (
 ownname                            in                 varchar2        ,
 objname                            in                 varchar2        ,
 organized                          in                   number default
);

get_column_stats

procedure get_column_stats (
 ownname                            in                 varchar2        ,
 tabname                            in                 varchar2        ,
 colname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 distcnt                           out                   number        ,
 density                           out                   number        ,
 nullcnt                           out                   number        ,
 srec                              out                   record        ,
 avgclen                           out                   number        ,
 statown                            in                 varchar2 default
);

procedure get_column_stats (
 ownname                            in                 varchar2        ,
 tabname                            in                 varchar2        ,
 colname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 ext_stats                         out                      raw        ,
 stattypown                        out                 varchar2        ,
 stattypname                       out                 varchar2        ,
 statown                            in                 varchar2 default
);

get_index_stats

procedure get_index_stats (
 ownname                            in                 varchar2        ,
 indname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 numrows                           out                   number        ,
 numlblks                          out                   number        ,
 numdist                           out                   number        ,
 avglblk                           out                   number        ,
 avgdblk                           out                   number        ,
 clstfct                           out                   number        ,
 indlevel                          out                   number        ,
 statown                            in                 varchar2 default,
 guessq                            out                   number        ,
 cachedblk                         out                   number        ,
 cachehit                          out                   number        
);

procedure get_index_stats (
 ownname                            in                 varchar2        ,
 indname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 numrows                           out                   number        ,
 numlblks                          out                   number        ,
 numdist                           out                   number        ,
 avglblk                           out                   number        ,
 avgdblk                           out                   number        ,
 clstfct                           out                   number        ,
 indlevel                          out                   number        ,
 statown                            in                 varchar2 default,
 guessq                            out                   number        
);

procedure get_index_stats (
 ownname                            in                 varchar2        ,
 indname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 numrows                           out                   number        ,
 numlblks                          out                   number        ,
 numdist                           out                   number        ,
 avglblk                           out                   number        ,
 avgdblk                           out                   number        ,
 clstfct                           out                   number        ,
 indlevel                          out                   number        ,
 statown                            in                 varchar2 default
);

procedure get_index_stats (
 ownname                            in                 varchar2        ,
 indname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 ext_stats                         out                      raw        ,
 stattypown                        out                 varchar2        ,
 stattypname                       out                 varchar2        ,
 statown                            in                 varchar2 default
);

get_param

function get_param returns varchar2
 (
 pname                              in                 varchar2        
);

get_stats_history_availability

function get_stats_history_availability returns timestamp with time zone
 (
);

get_stats_history_retention

function get_stats_history_retention returns number
 (
);

get_system_stats

procedure get_system_stats (
 status                            out                 varchar2        ,
 dstart                            out                     date        ,
 dstop                             out                     date        ,
 pname                              in                 varchar2        ,
 pvalue                            out                   number        ,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default
);

get_table_stats

procedure get_table_stats (
 ownname                            in                 varchar2        ,
 tabname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 numrows                           out                   number        ,
 numblks                           out                   number        ,
 avgrlen                           out                   number        ,
 statown                            in                 varchar2 default
);

procedure get_table_stats (
 ownname                            in                 varchar2        ,
 tabname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 numrows                           out                   number        ,
 numblks                           out                   number        ,
 avgrlen                           out                   number        ,
 statown                            in                 varchar2 default,
 cachedblk                         out                   number        ,
 cachehit                          out                   number        
);

import_column_stats

procedure import_column_stats (
 ownname                            in                 varchar2        ,
 tabname                            in                 varchar2        ,
 colname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2        ,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 force                              in                  boolean default
);

import_database_stats

procedure import_database_stats (
 stattab                            in                 varchar2        ,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 force                              in                  boolean default
);

import_dictionary_stats

procedure import_dictionary_stats (
 stattab                            in                 varchar2        ,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 force                              in                  boolean default
);

import_fixed_objects_stats

procedure import_fixed_objects_stats (
 stattab                            in                 varchar2        ,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 force                              in                  boolean default
);

import_index_stats

procedure import_index_stats (
 ownname                            in                 varchar2        ,
 indname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2        ,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 force                              in                  boolean default
);

import_schema_stats

procedure import_schema_stats (
 ownname                            in                 varchar2        ,
 stattab                            in                 varchar2        ,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 force                              in                  boolean default
);

import_system_stats

procedure import_system_stats (
 stattab                            in                 varchar2        ,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default
);

import_table_stats

procedure import_table_stats (
 ownname                            in                 varchar2        ,
 tabname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2        ,
 statid                             in                 varchar2 default,
 cascade                            in                  boolean default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 force                              in                  boolean default
);

init_package

procedure init_package (
);

lock_partition_stats

procedure lock_partition_stats (
 ownname                            in                 varchar2        ,
 tabname                            in                 varchar2        ,
 partname                           in                 varchar2        
);

lock_schema_stats

procedure lock_schema_stats (
 ownname                            in                 varchar2        ,
 stattype                           in                 varchar2 default
);

lock_table_stats

procedure lock_table_stats (
 ownname                            in                 varchar2        ,
 tabname                            in                 varchar2        ,
 stattype                           in                 varchar2 default
);

prepare_column_values

procedure prepare_column_values (
 srec                           in out                   record        ,
 charvals                           in               dbms_stats        
);

procedure prepare_column_values (
 srec                           in out                   record        ,
 datevals                           in               dbms_stats        
);

procedure prepare_column_values (
 srec                           in out                   record        ,
 numvals                            in               dbms_stats        
);

procedure prepare_column_values (
 srec                           in out                   record        ,
 fltvals                            in               dbms_stats        
);

procedure prepare_column_values (
 srec                           in out                   record        ,
 dblvals                            in               dbms_stats        
);

procedure prepare_column_values (
 srec                           in out                   record        ,
 rawvals                            in               dbms_stats        
);

prepare_column_values_nvarchar

procedure prepare_column_values_nvarchar (
 srec                           in out                   record        ,
 nvmin                              in                nvarchar2        ,
 nvmax                              in                nvarchar2        
);

prepare_column_values_rowid

procedure prepare_column_values_rowid (
 srec                           in out                   record        ,
 rwmin                              in                    rowid        ,
 rwmax                              in                    rowid        
);

purge_stats

procedure purge_stats (
 before_timestamp                 with                timestamp    time
);

restore_database_stats

procedure restore_database_stats (
 as_of_timestamp                  with                timestamp    time,
 force                              in                  boolean default
);

restore_dictionary_stats

procedure restore_dictionary_stats (
 as_of_timestamp                  with                timestamp    time,
 force                              in                  boolean default
);

restore_fixed_objects_stats

procedure restore_fixed_objects_stats (
 as_of_timestamp                  with                timestamp    time,
 force                              in                  boolean default
);

restore_schema_stats

procedure restore_schema_stats (
 ownname                            in                 varchar2        ,
 as_of_timestamp                  with                timestamp    time,
 force                              in                  boolean default
);

restore_system_stats

procedure restore_system_stats (
 as_of_timestamp                  with                timestamp    time
);

restore_table_stats

procedure restore_table_stats (
 ownname                            in                 varchar2        ,
 tabname                            in                 varchar2        ,
 as_of_timestamp                  with                timestamp    time,
 restore_cluster_index              in                  boolean default,
 force                              in                  boolean default
);

set_column_stats

procedure set_column_stats (
 ownname                            in                 varchar2        ,
 tabname                            in                 varchar2        ,
 colname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 distcnt                            in                   number default,
 density                            in                   number default,
 nullcnt                            in                   number default,
 srec                               in                   record default,
 avgclen                            in                   number default,
 flags                              in                   number default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 force                              in                  boolean default
);

procedure set_column_stats (
 ownname                            in                 varchar2        ,
 tabname                            in                 varchar2        ,
 colname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 ext_stats                          in                      raw        ,
 stattypown                         in                 varchar2 default,
 stattypname                        in                 varchar2 default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 force                              in                  boolean default
);

set_index_stats

procedure set_index_stats (
 ownname                            in                 varchar2        ,
 indname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 numrows                            in                   number default,
 numlblks                           in                   number default,
 numdist                            in                   number default,
 avglblk                            in                   number default,
 avgdblk                            in                   number default,
 clstfct                            in                   number default,
 indlevel                           in                   number default,
 flags                              in                   number default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 guessq                             in                   number default,
 cachedblk                          in                   number default,
 cachehit                           in                   number default,
 force                              in                  boolean default
);

procedure set_index_stats (
 ownname                            in                 varchar2        ,
 indname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 ext_stats                          in                      raw        ,
 stattypown                         in                 varchar2 default,
 stattypname                        in                 varchar2 default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 force                              in                  boolean default
);

set_param

procedure set_param (
 pname                              in                 varchar2        ,
 pval                               in                 varchar2        
);

set_system_stats

procedure set_system_stats (
 pname                              in                 varchar2        ,
 pvalue                             in                   number        ,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 statown                            in                 varchar2 default
);

set_table_stats

procedure set_table_stats (
 ownname                            in                 varchar2        ,
 tabname                            in                 varchar2        ,
 partname                           in                 varchar2 default,
 stattab                            in                 varchar2 default,
 statid                             in                 varchar2 default,
 numrows                            in                   number default,
 numblks                            in                   number default,
 avgrlen                            in                   number default,
 flags                              in                   number default,
 statown                            in                 varchar2 default,
 no_invalidate                      in                  boolean default,
 cachedblk                          in                   number default,
 cachehit                           in                   number default,
 force                              in                  boolean default
);

to_cascade_type

function to_cascade_type returns boolean
 (
 cascade                            in                 varchar2        
);

to_degree_type

function to_degree_type returns number
 (
 degree                             in                 varchar2        
);

to_estimate_percent_type

function to_estimate_percent_type returns number
 (
 estimate_percent                   in                 varchar2        
);

to_no_invalidate_type

function to_no_invalidate_type returns boolean
 (
 no_invalidate                      in                 varchar2        
);

unlock_partition_stats

procedure unlock_partition_stats (
 ownname                            in                 varchar2        ,
 tabname                            in                 varchar2        ,
 partname                           in                 varchar2        
);

unlock_schema_stats

procedure unlock_schema_stats (
 ownname                            in                 varchar2        ,
 stattype                           in                 varchar2 default
);

unlock_table_stats

procedure unlock_table_stats (
 ownname                            in                 varchar2        ,
 tabname                            in                 varchar2        ,
 stattype                           in                 varchar2 default
);

upgrade_stat_table

procedure upgrade_stat_table (
 ownname                            in                 varchar2        ,
 stattab                            in                 varchar2        
);

Misc

dbms_stats uses sys.col_usage$ to decide if it is worth building a histogram on a column.

Examples

The following script finds out, how many rows in a table are stored. The tablenames of the tables are stored in info_tablerows.

create table t1 (a number);

begin
  for i in 100 .. 110 loop
    insert into t1 values (i);
  end loop;
end;
/

create table t2 (a number);

begin
  for i in 1000 .. 1100 loop
    insert into t2 values (i);
  end loop;
end;
/

create table t3 (a number);
begin
  for i in 2000 .. 2200 loop
    insert into t3 values (i);
  end loop;
end;
/

commit;


create table info_tablerows (
  tablename varchar2(30) primary key
);

insert into info_tablerows values ('t1');
insert into info_tablerows values ('t2');
insert into info_tablerows values ('t3');

commit;

declare
  cursor c is select tablename from info_tablerows;
begin
  for r in c loop
    dbms_output.put_line(r.tablename);
    dbms_stats.gather_table_stats(user,r.tablename);
  end loop;
end;
/

create view v_num_rows as 
  select num_rows, table_name from 
  user_tables u, info_tablerows i where
  u.table_name = upper(i.tablename);

select * from v_num_rows;

drop table t1;
drop table t2;
drop table t3;
drop table info_tablerows;
drop view v_num_rows;

[@more@]

ITPub博客

dbms_stats