【问题分类】性能优化

【关键字】统计信息

【问题描述】

SQL
--创建表结构
drop table t1;
create table t1 (id int,name varchar2(200));
create index t1_id_ind on t1(id);
create index t1_name_ind on t1(name);

--造数
begin
 for i in 1..100 loop
 insert into t1 values(i,'aaaa'||i);
 commit;
 end loop;
end;
/
 
--收集统计信息
begin DBMS_STATS.GATHER_TABLE_STATS('SYS', 'T1', '',1, FALSE, 'FOR ALL COLUMNS SIZE AUTO', 4, 'AUTO', TRUE); end;
/

--查看统计信息
SELECT OWNER ,TABLE_NAME ,LAST_ANALYZED ,NUM_ROWS,STALE_STATS FROM dba_tab_statistics WHERE table_name = 'T1' ;

--查看执行计划，此时是理想计划
explain SELECT * FROM T1 WHERE ID IN (1) ORDER BY NAME desc;

--设置失效比例
exec DBMS_STATS.SET_TABLE_PREFS('SYS', 'T1', 'STALE_PERCENT', '0.1');

--新增数据，超过失效比例
begin
 for i in 101..121 loop
 insert into t1 values(i,'aaaa'||i);
 commit;
 end loop;
end;
/

--查看统计信息，此时统计信息失效
SELECT OWNER ,TABLE_NAME ,LAST_ANALYZED ,NUM_ROWS,STALE_STATS FROM dba_tab_statistics WHERE table_name = 'T1' ;

--查看执行计划，此时计划不好的计划
explain SELECT * FROM T1 WHERE ID IN (1) ORDER BY NAME desc; --不能使用与上述步骤完全一致的SQL，计划保存在内存中，plan_hash值没有变化
explain SELECT * FROM T1 WHERE ID IN (1,2) ORDER BY NAME desc; --此时计划不好的计划
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.

【问题原因分析】此处的根因在于YashanDB为何要设计统计信息失效？

【解决/规避方法】

● 调整统计信息收集的JOB,业务空闲时间点统一收集一次统计信息；

● 调整statistics_level参数为basic；

● 将统计信息失效的全局参数STALE_PERCENT配置成0，即不失效；

● 锁定统计信息。

【影响范围】所有版本

【修复版本】所有版本