当前位置：首页 > news >正文

oracle统计信息

news 2025/8/30 3:34:48

1. 查看表的统计信息

1.建表
SQL> create table test as select * from dba_objects;2.查看表的统计信息
select owner, table_name, num_rows, blocks, avg_row_lenfrom dba_tableswhere owner = 'SCOTT'and table_name = 'TEST';
OWNER			       TABLE_NAME			NUM_ROWS     BLOCKS AVG_ROW_LEN
------------------------------ ------------------------------ ---------- ---------- -----------
SCOTT			       TEST3. 收集统计信息
BEGINDBMS_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_lenfrom dba_tableswhere owner = 'SCOTT'and table_name = 'TEST';OWNER			       TABLE_NAME			NUM_ROWS     BLOCKS AVG_ROW_LEN
------------------------------ ------------------------------ ---------- ---------- -----------
SCOTT			       TEST				   86262       1260	     985. 查看test表的直方图
select sta.column_name,sta.num_distinct,sta.num_nulls,sta.num_buckets,sta.HISTOGRAMfrom dba_tab_col_statistics stawhere 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_bucketsfrom dba_tab_col_statistics sta, dba_tables tabwhere sta.owner = tab.ownerand sta.table_name = tab.table_nameand 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:当前哪些列收集统计信息，还对那些列收集）

2.查看索引的统计信息

1.创建索引（会自动收集统计信息）
SQL> create index idx_id on test(object_id);2.查看索引统计信息
select idx.blevel, idx.leaf_blocks, idx.clustering_factor, idx.statusfrom dba_indexes idxwhere idx.index_name = 'IDX_ID';BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR STATUS
---------- ----------- ----------------- --------1	   191		    1304 VALID3. 单独对索引收集统计信息
begindbms_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_analyzedfrom dba_tab_statistics swhere 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.timestampfrom all_tab_modifications almwhere alm.table_owner = 'SCOTT'and alm.table_name = 'TEST';
PS：当表中有10%的数据发生改变，就会引起统计信息过期3.检查表统计信息过期sql
select owner, table_name, object_type, stale_stats, last_analyzedfrom dba_tab_statisticswhere (owner, table_name) in(select object_owner, object_namefrom plan_tablewhere object_type like '%TABLE%'unionselect idx.table_owner, idx.table_namefrom dba_indexes idxwhere (idx.owner, idx.table_name) in(select plt.object_owner, plt.object_namefrom plan_table pltwhere plt.object_type = '%INDEX%'))4.统计过期原因
select *from all_tab_modificationswhere (table_owner, table_name) in(select object_owner, object_namefrom plan_tablewhere object_type like '%TABLE%'unionselect table_owner, table_namefrom dba_indexeswhere (owner, index_name) in(select object_owner, object_namefrom plan_tablewhere object_type like '%INDEX%'));