| René Nyffenegger's collection of things on the web | |
|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
dbms_stats | ||
Purpose
Gathering statistics is essential for the
CBO to do his work.
Procedures/Functionsalter_database_tab_monitoringprocedure alter_database_tab_monitoring ( monitoring in boolean default, sysobjs in boolean default ); alter_schema_tab_monitoringprocedure alter_schema_tab_monitoring ( ownname in varchar2 default, monitoring in boolean default ); alter_stats_history_retentionprocedure alter_stats_history_retention ( retention in number ); convert_raw_valueprocedure 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_nvarcharprocedure convert_raw_value_nvarchar ( rawval in raw , resval out nvarchar2 ); convert_raw_value_rowidprocedure convert_raw_value_rowid ( rawval in raw , resval out rowid ); copy_table_statsprocedure copy_table_stats ( ownname in varchar2 , tabname in varchar2 , srcpartname in varchar2 , dstpartname in varchar2 , flags in number default ); create_stat_tableprocedure create_stat_table ( ownname in varchar2 , stattab in varchar2 , tblspace in varchar2 default ); delete_column_statsprocedure 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_statsprocedure 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_statsprocedure 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_statsprocedure 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_statsprocedure 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_statsprocedure 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_statsprocedure delete_system_stats ( stattab in varchar2 default, statid in varchar2 default, statown in varchar2 default ); delete_table_statsprocedure 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_tableprocedure drop_stat_table ( ownname in varchar2 , stattab in varchar2 ); export_column_statsprocedure 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_statsprocedure export_database_stats ( stattab in varchar2 , statid in varchar2 default, statown in varchar2 default ); export_dictionary_statsprocedure export_dictionary_stats ( stattab in varchar2 , statid in varchar2 default, statown in varchar2 default ); export_fixed_objects_statsprocedure export_fixed_objects_stats ( stattab in varchar2 , statid in varchar2 default, statown in varchar2 default ); export_index_statsprocedure 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_statsprocedure export_schema_stats ( ownname in varchar2 , stattab in varchar2 , statid in varchar2 default, statown in varchar2 default ); export_system_statsprocedure export_system_stats ( stattab in varchar2 , statid in varchar2 default, statown in varchar2 default ); export_table_statsprocedure 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_infoprocedure flush_database_monitoring_info ( ); gather_database_statsprocedure 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_procprocedure gather_database_stats_job_proc ( ); gather_dictionary_statsprocedure 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_statsprocedure gather_fixed_objects_stats ( stattab in varchar2 default, statid in varchar2 default, statown in varchar2 default, no_invalidate in boolean default ); gather_index_statsprocedure 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_statsprocedure 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_statsprocedure 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_statsprocedure 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_statsprocedure generate_stats ( ownname in varchar2 , objname in varchar2 , organized in number default ); get_column_statsprocedure 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_statsprocedure 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_paramfunction get_param returns varchar2 ( pname in varchar2 ); get_stats_history_availabilityfunction get_stats_history_availability returns timestamp with time zone ( ); get_stats_history_retentionfunction get_stats_history_retention returns number ( ); get_system_statsprocedure 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_statsprocedure 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_statsprocedure 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_statsprocedure 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_statsprocedure 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_statsprocedure 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_statsprocedure 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_statsprocedure 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_statsprocedure import_system_stats ( stattab in varchar2 , statid in varchar2 default, statown in varchar2 default ); import_table_statsprocedure 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_packageprocedure init_package ( ); lock_partition_statsprocedure lock_partition_stats ( ownname in varchar2 , tabname in varchar2 , partname in varchar2 ); lock_schema_statsprocedure lock_schema_stats ( ownname in varchar2 , stattype in varchar2 default ); lock_table_statsprocedure lock_table_stats ( ownname in varchar2 , tabname in varchar2 , stattype in varchar2 default ); prepare_column_valuesprocedure 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_nvarcharprocedure prepare_column_values_nvarchar ( srec in out record , nvmin in nvarchar2 , nvmax in nvarchar2 ); prepare_column_values_rowidprocedure prepare_column_values_rowid ( srec in out record , rwmin in rowid , rwmax in rowid ); purge_statsprocedure purge_stats ( before_timestamp with timestamp time ); restore_database_statsprocedure restore_database_stats ( as_of_timestamp with timestamp time, force in boolean default ); restore_dictionary_statsprocedure restore_dictionary_stats ( as_of_timestamp with timestamp time, force in boolean default ); restore_fixed_objects_statsprocedure restore_fixed_objects_stats ( as_of_timestamp with timestamp time, force in boolean default ); restore_schema_statsprocedure restore_schema_stats ( ownname in varchar2 , as_of_timestamp with timestamp time, force in boolean default ); restore_system_statsprocedure restore_system_stats ( as_of_timestamp with timestamp time ); restore_table_statsprocedure 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_statsprocedure 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_statsprocedure 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_paramprocedure set_param ( pname in varchar2 , pval in varchar2 ); set_system_statsprocedure set_system_stats ( pname in varchar2 , pvalue in number , stattab in varchar2 default, statid in varchar2 default, statown in varchar2 default ); set_table_statsprocedure 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_typefunction to_cascade_type returns boolean ( cascade in varchar2 ); to_degree_typefunction to_degree_type returns number ( degree in varchar2 ); to_estimate_percent_typefunction to_estimate_percent_type returns number ( estimate_percent in varchar2 ); to_no_invalidate_typefunction to_no_invalidate_type returns boolean ( no_invalidate in varchar2 ); unlock_partition_statsprocedure unlock_partition_stats ( ownname in varchar2 , tabname in varchar2 , partname in varchar2 ); unlock_schema_statsprocedure unlock_schema_stats ( ownname in varchar2 , stattype in varchar2 default ); unlock_table_statsprocedure unlock_table_stats ( ownname in varchar2 , tabname in varchar2 , stattype in varchar2 default ); upgrade_stat_tableprocedure 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.
|