Oracle Collecting Statstics

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部落格 ” ，連結：http://blog.itpub.net/638844/viewspace-775894/，如需轉載，請註明出處，否則將追究法律責任。