Oracle: DBMS

Parameter NO_INVALIDATE

The parameter no_invalidate controls whether cursors are invalided (i. e. purged from the library cache) when dbms_stats is used to modify statistics.

false invalidates them, true does not invalide them (this is the default), null lets Oracle choose if it will or won't invalidate them.

When set to null, it seems that the current (19c) implementation does not invalidate these statistics for 5 hours (see Dani Schnider's Blog: Avoid dbms_stats.auto_invalidate in ETL jobs).

This period can apparently be changed with the _optimizer_invalidation_period init parameter

Functions and procedures

Oracle's documentation on dbms_stats says Most procedures in this package commit the current transaction, perform the operation, and then commit again.

`alter_database_tab_monitoring`
`alter_schema_tab_monitoring`
`alter_stats_history_retention`
`cancel_advisor_task`
`cleanup_stats_job_proc`
`clob_to_varray`
`column_need_hist`
`configure_advisor_filter`
`configure_advisor_obj_filter`
`configure_advisor_opr_filter`
`configure_advisor_rule_filter`
`convert_raw_to_<datatype>`	`<datatype>` is one of `bin_double`, `bin_float`, `date`, `number`, `nvarchar`, `rowid`, `varchar2`.
`convert_raw_value`	Converts values that are stored as `raw` to a human readable text.
`convert_raw_value_nvarchar`
`convert_raw_value_rowid`
`conv_raw`	A function similar to the procedure `convert_raw_value`: it converts a `raw` value to a human readable text.
`copy_table_stats`
`create_advisor_task`
`create_extended_stats`
`create_stat_table`, `drop_stat_table`	Create or drop a user statitics table. See importing and exporting statistics.
`delete_column_stats`
`delete_database_prefs`
`delete_database_stats`
`delete_dictionary_stats`
`delete_fixed_objects_stats`
`delete_index_stats`
`delete_pending_stats`
`delete_pending_system_stats`
`delete_processing_rate`
`delete_schema_prefs`
`delete_schema_stats`
`delete_system_stats`
`delete_table_prefs`
`delete_table_stats`
`diff_table_stats_in_history`
`diff_table_stats_in_pending`
`diff_table_stats_in_stattab`
`drop_advisor_task`
`drop_extended_stats`
`execute_advisor_task`
`export_column_stats`
`export_database_prefs`
`export_database_stats`
`export_dictionary_stats`
`export_fixed_objects_stats`
`export_index_stats`	See importing and exporting statistics.
`export_pending_stats`
`export_pending_system_stats`
`export_schema_prefs`
`export_schema_stats`
`export_stats_for_dp`
`export_system_stats`
`export_table_prefs`
`export_table_stats`	See importing and exporting statistics.
`flush_database_monitoring_info`	Flush in-memmory monitoring information to the data dictionary.
`gather_database_stats`
`gather_database_stats_job_proc`
`gather_dictionary_stats`
`gather_fixed_objects_stats`	But 29275347 seems to indicate that `x$ktfbue` is skipped when executing `dbms_stats.gather_fixed_objects_stats`.
`gather_index_stats`
`gather_processing_rate`
`gather_schema_stats`
`gather_st_job_continuous_proc`
`gather_system_stats`
`gather_table_stats`
`gather_table_stats_func`
`generate_index_stats`
`generate_stats`	Generate statistics for objects that are derived from objects that have a certain amount of statistics. Deprecated in favor of `gather_index_stats`
`gen_selmap`
`get_advisor_opr_filter`
`get_advisor_recs`
`get_column_stats`
`get_compatible`
`get_index_stats`
`get_param`	Deprecated in favor of `get_prefs`
`get_prefs`
`get_row_count_estimate`
`get_stats_history_availability`
`get_stats_history_retention`
`get_stat_tab_version`
`get_system_stats`
`get_table_stats`
`implement_advisor_task`
`import_column_stats`
`import_database_prefs`
`import_database_stats`
`import_dictionary_stats`
`import_fixed_objects_stats`
`import_index_stats`
`import_schema_prefs`
`import_schema_stats`
`import_stats_for_dp`
`import_system_stats`
`import_table_prefs`
`import_table_stats`
`init_package`
`interrupt_advisor_task`
`lock_partition_stats`
`lock_schema_stats`
`lock_table_stats`
`merge_col_usage`
`merge_stats_partition_exchange`
`postprocess_indstats`
`postprocess_stats`
`prepare_column_values`
`prepare_column_values_nvarchar`
`prepare_column_values_rowid`
`publish_pending_stats`
`publish_pending_system_stats`
`purge_stats`
`remap_stat_table`
`report_advisor_task`
`report_col_usage`
`report_gather_auto_stats`
`report_gather_database_stats`
`report_gather_dictionary_stats`
`report_gather_fixed_obj_stats`
`report_gather_schema_stats`
`report_gather_table_stats`
`report_single_stats_operation`
`report_stats_operations`
`reset_advisor_task`
`reset_col_usage`
`reset_global_pref_defaults`
`reset_param_defaults`	deprecated in favor of `reset_global_pref_defaults`
`restore_database_stats`
`restore_dictionary_stats`
`restore_fixed_objects_stats`
`restore_schema_stats`
`restore_system_stats`
`restore_table_stats`
`resume_advisor_task`
`resume_gather_stats`
`save_inmemory_stats`
`script_advisor_task`
`seed_col_usage`	Seeds column usage information from statements in the specified SQL tuning set or in the database
`set_advisor_task_parameter`
`set_column_stats`
`set_database_prefs`	Specify default values for some parameters of some procedures of `dbms_stats`.
`set_global_prefs`	Specify default values for some parameters of some procedures of `dbms_stats`.
`set_index_stats`
`set_index_stats`
`set_param`	Deprecated in favor of `set_global_prefs`
`set_processing_rate`
`set_schema_prefs`	Specify default values for some parameters of some procedures of `dbms_stats`.
`set_system_stats`
`set_table_prefs`	Specify default values for some parameters of some procedures of `dbms_stats`.
`set_table_stats`
`show_extended_stats_name`
`to_no_invalidate_type`
`transfer_stats`
`unlock_partition_stats`
`unlock_schema_stats`
`unlock_table_stats`
`upgrade_stat_table`
`varray_to_clob`

Common parameters statown, stattab and statid

The three parameters statown, stattab and statid, which are included in a number of procedures, allow to store the statistics in table outside of the data dictionary and allow to experiment with sets of statitistcs without affecting the optimizer.

If these parameters are used with gather_* procedures, Oracle will back up the current statistics in the specified table(s) before gathering new statistics.

`statown`
`stattab`	Name of the table to store the statistics
`statid`	Allows to maintain multiple sets of statistics in one table so that the number of statistic-tables can be reduced in a schema

These parameter names must not be confused with ownname and tabname of dbms_stats.gather_table_stats.

gather_dictionary_stats

Gathers statistics on the data dictionary.

gather_fixed_objects_stats

Collect statistics on fixed (x$) objects.

dbms_stats.gather_fixed_objects_stats is quite similar to dbms_stats.gather_table_stats except that the number of blocks is set to 0 because x$ tables reside in memory (SGA) rather than on disk.

Oracle: DBMS_STATS

Parameter NO_INVALIDATE

Functions and procedures

Common parameters statown, stattab and statid

gather_dictionary_stats

gather_fixed_objects_stats

See also