1. 查看表的统计信息
1.建表
SQL> create table test as select * from dba_objects;
2.查看表的统计信息
select owner, table_name, num_rows, blocks, avg_row_len
from dba_tables
where owner = 'SCOTT'
and table_name = 'TEST';
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ ------------------------------ ---------- ---------- -----------
SCOTT TEST
3. 收集统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'SCOTT',
tabname => 'TEST',
estimate_percent => 100,
method_opt => 'for all columns size auto',
degree => 30,
cascade => TRUE);
END;
/
4.再次查看统计信息
select owner, table_name, num_rows, blocks, avg_row_len
from dba_tables
where owner = 'SCOTT'
and table_name = 'TEST';
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ ------------------------------ ---------- ---------- -----------
SCOTT TEST 86262 1260 98
5. 查看test表的直方图
select sta.column_name,
sta.num_distinct,
sta.num_nulls,
sta.num_buckets,
sta.HISTOGRAM
from dba_tab_col_statistics sta
where sta.owner = 'SCOTT'
and sta.table_name = 'TEST'
6.查看表和列的统计信息
select sta.column_name,
tab.num_rows,
sta.num_nulls,
sta.num_distinct cardinality,
round(sta.num_distinct / tab.num_rows * 100, 2) selectivity,
sta.HISTOGRAM,
sta.num_buckets
from dba_tab_col_statistics sta, dba_tables tab
where sta.owner = tab.owner
and sta.table_name = tab.table_name
and sta.owner = 'SCOTT'
and sta.table_name = 'TEST';
estimate_percent:采样率，0.0000001~100，一般设置为30
degree ：设置cpu负载
method_opt ：直方图收集策略（for all columns size 1:所有列不收集，for all columns size skewonly：对所有列自动判断是否收集，for all columns size auto:对出现在where条件中的列自动判断是否收集，for all columns size repeat:当前哪些列收集统计信息，还对那些列收集）文章来源：https://www.toymoban.com/news/detail-720651.html
2.查看索引的统计信息
1.创建索引（会自动收集统计信息）
SQL> create index idx_id on test(object_id);
2.查看索引统计信息
select idx.blevel, idx.leaf_blocks, idx.clustering_factor, idx.status
from dba_indexes idx
where idx.index_name = 'IDX_ID';
BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR STATUS
---------- ----------- ----------------- --------
1 191 1304 VALID
3. 单独对索引收集统计信息
begin
dbms_stats.gather_index_stats(
ownname => 'SCOTT',
indname => 'IDX_ID');
end;
/
3.查看统计信息状态
1.查看统计信息是否过期
select s.owner, s.table_name, s.object_type, s.stale_stats, s.last_analyzed
from dba_tab_statistics s
where s.owner = 'SCOTT'
AND S.table_name = 'EMP';
PS：stale_stats为yes表示统计信息过期
2.查看统计信息过期原因
select alm.table_owner,
alm.table_name,
alm.inserts,
alm.updates,
alm.deletes,
alm.timestamp
from all_tab_modifications alm
where alm.table_owner = 'SCOTT'
and alm.table_name = 'TEST';
PS：当表中有10%的数据发生改变，就会引起统计信息过期
3.检查表统计信息过期sql
select owner, table_name, object_type, stale_stats, last_analyzed
from dba_tab_statistics
where (owner, table_name) in
(select object_owner, object_name
from plan_table
where object_type like '%TABLE%'
union
select idx.table_owner, idx.table_name
from dba_indexes idx
where (idx.owner, idx.table_name) in
(select plt.object_owner, plt.object_name
from plan_table plt
where plt.object_type = '%INDEX%'))
4.统计过期原因
select *
from all_tab_modifications
where (table_owner, table_name) in
(select object_owner, object_name
from plan_table
where object_type like '%TABLE%'
union
select table_owner, table_name
from dba_indexes
where (owner, index_name) in
(select object_owner, object_name
from plan_table
where object_type like '%INDEX%'));
索引统计信息文章来源地址https://www.toymoban.com/news/detail-720651.html
select owner,index_name,num_rows,blevel,leaf_blocks,clustering_factor,status,last_analyzed
from dba_indexes
where owner = 'SYS'
and index_name = 'test';
--判断统计信息是否陈旧
select owner, table_name, object_type, stale_stats, num_rows, last_analyzed
from dba_tab_statistics
where table_name = 'TEST3'
and owner = 'SCOTT';
tips：stale_stats 若为 yes 则陈旧 ， no不陈旧
查看统计信息过期的原因（回收高水位、经常要rebuild的索引都可以用这个判断）：
当表中有超过10%的数据发生变化（insert、update、delete），就会引起统计信息过去
到了这里，关于oracle统计信息的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
