dbms_stats(轉)

zhouwf0726發表於2019-06-18

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;

Misc Stuff

Collecting statistics does not invalidate cursors (at least on some versions). So, a alter system flush shared pool should be issued after collecting the stats so that a hard parse is forced when a cursor is opened anew.

[@more@]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-861204/，如需轉載，請註明出處，否則將追究法律責任。

dbms_stats(zt)
2019-01-30
[20210506]oracle19c dbms_stats的預設引數.txt
2021-05-06
Oracle
[轉]旋轉矩陣：點旋轉和座標系旋轉
2024-12-10
矩陣
HString 轉 string 轉 HTuple
2020-09-24
大轉轉FE招聘啦
2019-02-14
玩轉SSH埠轉發
2018-09-12
轉轉：2019年度轉轉二手交易服務白皮書
2020-03-20
Excel轉PDF怎麼轉？Excel轉PDF方法有哪些
2020-03-11
Excel
語音轉文字工具，語音轉文字怎樣轉？
2019-06-12
轉
2019-01-29
什麼是SSH埠轉發（本地轉發、遠端轉發、動態轉發）？
2019-03-20
向上轉型和向下轉型
2018-11-08
轉轉OLAP自助分析實踐
2023-12-28
行轉列與列轉行
2019-07-21
SQL 行轉列，列轉行
2019-05-19
SQL
Mysql - 行轉列、列轉行
2018-04-23
MySql
(轉)OC專案轉Swift指南
2020-12-28
Swift
Sting 轉List＜String＞轉List＜Integer＞
2020-12-25
heic格式轉換jpg工具——轉易俠heic轉換器
2021-12-07
ppt轉pdf怎麼轉？試試這個轉換技巧！
2020-12-10
iOS引用轉換：Foundation與Core Foundation物件互相轉換（__CFString轉NSString，void *轉id等等）
2019-03-01
iOS物件
PDF轉PPT怎麼轉？好用的PDF轉換方法有哪些？
2020-03-05
heic格式轉換jpg免費怎麼轉？轉易俠可以
2021-12-09
如何將圖片轉word？圖文轉換選轉易俠
2022-01-12
JAVA字串轉日期或日期轉字串
2019-01-15
Java字串
Oracle實驗(02)：轉換 & 轉譯
2019-02-25
Oracle
JS json字串轉物件、物件轉字串
2019-01-29
JSON字串物件
axmath 轉換latex 再轉 word公式
2024-11-30
公式
c# svg轉Bitmap Bitmap轉ImageSource
2024-08-08
C#SVG
pdf轉word如何線上轉換？
2021-12-08
heic格式批量轉jpg怎麼轉？
2021-10-14
影像縮放、旋轉、翻轉、平移
2023-01-07
PDF批次轉換器，批次轉word為pdf，批次轉ppt為pdf
2024-10-23
轉 linqToDatatable
2019-01-11
QT
python（轉）
2019-04-16
Python
轉跳
2024-04-30
[轉帖]
2024-05-16
RMAN(轉)
2019-04-22