Oracle Collecting Statstics

最新推荐文章于 2023-09-20 13:35:22 发布

chncaesar

最新推荐文章于 2023-09-20 13:35:22 发布

阅读量720

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle调优文章标签： Collecting statistic dbms_stats

本文链接：https://blog.youkuaiyun.com/chncaesar/article/details/17319647

Oracle调优专栏收录该内容

15 篇文章

订阅专栏

本文详细介绍了如何使用Oracle数据库的DBMS_STATS包来管理并分析表、索引和列的统计信息，包括如何获取统计信息、更新统计信息、指定采样百分比、收集特定列的直方图统计信息、并行收集统计信息等操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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.

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';

Statistics exists after index is built.

select
  table_name,
  column_name,
  density,
  num_nulls,
  num_distinct,
  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';

SELECT column_name, endpoint_number, endpoint_value, endpoint_actual_value 
FROM   USER_TAB_HISTOGRAMS
WHERE  TABLE_NAME = 'xxx'
AND column_name='yyy';

Gather statistics of table and its indexes

exec dbms_stats.gather_table_stats(user, 'TT', cascade=>true);

Specifying sample percentage

exec dbms_stats.gather_table_stats(user, 'TT1',estimate_percent=>33, cascade=>true);

Gather statistics on partitioned table

exec dbms_stats.gather_table_stats(user, 'TT', granularity=>'ALL',cascade=>true);

Gather histogram statistics

SELECT dbms_stats.get_param('METHOD_OPT') opt FROM DUAL;

OPT
------------------------------
FOR ALL COLUMNS SIZE AUTO

This shows the default setting of how histogram statstics is collected.

"SIZE AUTO": Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

User can specify the bucket size of histogram.

exec dbms_stats.gather_table_stats(user, 'TT', method_opt=>'FOR ALL COLUMNS SIZE 16',cascade=>true);  --Histogram bucket size 16
exec dbms_stats.gather_table_stats(user, 'EMP', method_opt=>'FOR COLUMNS DEPTNO SIZE 240',cascade=>true);
exec dbms_stats.gather_table_stats(user, 'EMP', estimate_percent=>33,method_opt=>'FOR COLUMNS DEPTNO SIZE 254',granularity=>'ALL',cascade=>true);

Gather statistics in parallel

exec dbms_stats.gather_table_stats(user, 'TT', cascade=>true, degree=>2);

Delete statiscs

exec dbms_stats.delete_table_stats(user, 'TT');

This procedure deletes the statistics of paritions,indexes, columns.