Procedure signature:
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2, --Owner/schema name
tabname VARCHAR2, --Table Name
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
stattype VARCHAR2 DEFAULT 'DATA',
force BOOLEAN DEFAULT FALSE);
Oracle database version:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE 11.2.0.1.0 Production"
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
1. View table, index and column statistics.
select table_name,
num_rows,
blocks,
empty_blocks,
avg_space,
chain_cnt,
avg_row_len,
degree,
sample_size,
to_char(last_analyzed,'yyyy/mm/dd hh24:mi:ss') last_analyzed
from user_tables where table_name='TT';
Query user_tab_partitions for partition statistics, user_tab_subpartitions for subpartitions.
After table creation, there's no statistics at the table level. Oracle uses dynamic sampling to calculate the statistics.
select index_name,
table_name,
blevel,
leaf_blocks,
distinct_keys,
avg_leaf_blocks_per_key,
avg_data_blocks_per_key
cluster_factor,
status,
num_rows,
sample_size,
to_char(last_analyzed,'yyyy/mm/dd hh24:mi:ss') last_analyzed,
degree
from user_indexes
where table_name='TT';
Statistics exists after index is built.
select
table_name,
column_name,
density,
num_nulls,
num_buckets,
to_char(last_analyzed,'yyyy/mm/dd hh24:mi:ss') last_analyzed,
sample_size,
avg_col_len,
histogram
from user_tab_col_statistics
where table_name='TT';
No column statistics
2. Gather statistics of table and its indexes
SQL> exec dbms_stats.gather_table_stats(user, 'TT', cascade=>true);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.36
3. Gather statistics on partitioned table
exec dbms_stats.gather_table_stats(user, 'TT', granularity=>'ALL',cascade=>true);
4. Gather histogram statistics
SELECT dbms_stats.get_param('METHOD_OPT') opt FROM DUAL;
SQL> SELECT dbms_stats.get_param('METHOD_OPT') opt FROM DUAL;
OPT
------------------------------
FOR ALL COLUMNS SIZE AUTO
Determines how histogram statstics is collected by default."SIZE AUTO": Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
exec dbms_stats.gather_table_stats(user, 'TT', method_opt=>'FOR ALL COLUMNS SIZE 16',cascade=>true); --Histogram bucket size 16
5. Gather statistics in parallel
SQL> exec dbms_stats.gather_table_stats(user, 'TT', cascade=>true, degree=>2);
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.33
6. Delete statiscs
exec dbms_stats.delete_table_stats(user, 'TT');
This procedure by default deletes the statistics of paritions,indexes, columns.
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/638844/viewspace-775894/ ,如需转载,请注明出处,否则将追究法律责任。