问题背景

有项目反馈，收集统计信息后，sql执行还是很慢，清理缓存执行计划就可以了。

统计信息失效测试

这里我们来做个测试，初始化数据后查看缓存执行计划。

DROP TABLE IF EXISTS EMPLOYEES CASCADE;  
 
CREATE TABLE EMPLOYEES (
    EMPLOYEEID INT PRIMARY KEY,
    EMPNAME VARCHAR(50), 
    PROVINCEID INT
); 

BEGIN
    FOR I IN 1..10000 
    LOOP
        INSERT INTO EMPLOYEES VALUES(i,'员工'||i,ceil(dbms_random.value(1,1000))); 
     END LOOP;
    COMMIT;
END; 

CREATE INDEX IDX_PROVINCEID ON EMPLOYEES(PROVINCEID);
 
   DBMS_STATS.GATHER_TABLE_STATS(
   		ownname => 'SYSDBA', 
   		tabname => 'EMPLOYEES',
   		partname => null, 
   		estimate_percent => 100,
   		block_sample => false,
   		method_opt => 'FOR ALL COLUMNS SIZE AUTO',
   		degree => 8,
   		granularity => 'ALL',
   		cascade => true,
   		stattab => null, 
   		statid => null,
   		statown => null,
   		no_invalidate => false,
   		force => true --强制收集统计信息
    ); 

执行以下sql。

SELECT * FROM EMPLOYEES WHERE EMPLOYEES.PROVINCEID=10;

查看缓存执行计划，然后导出。

SELECT SQLSTR,* FROM V$CACHEPLN WHERE SQLSTR LIKE '%EMPLOYEES%';
alter session set events 'immediate trace name plndump level 139960643449576, dump_file ''/home/dmdba/139960643449576.log''';

这时，插入1.5w数据模拟数据变化后统计信息失效。

BEGIN
    FOR I IN 10001..15000 
    LOOP
        INSERT INTO EMPLOYEES VALUES(i,'员工'||i,10); 
     END LOOP;
    COMMIT;
END; 
 

   DBMS_STATS.GATHER_TABLE_STATS(
   		ownname => 'SYSDBA', 
   		tabname => 'EMPLOYEES',
   		partname => null, 
   		estimate_percent => 100,
   		block_sample => false,
   		method_opt => 'FOR ALL COLUMNS SIZE AUTO',
   		degree => 8,
   		granularity => 'ALL',
   		cascade => false,
   		stattab => null, 
   		statid => null,
   		statown => null,
   		no_invalidate => true,
   		force => false --强制收集统计信息
    );

收集统计信息后，查看缓存的执行计划，cache_item 没变。 再次导出，计划也没变。

说明之前统计信息后，缓存执行计划并未失效。

查阅文档，NO_INVALIDATE 为 false 时游标失效，会移除原有的相关的执行计划。

因为上面统计信息时no_invalidate=>true，所以收集统计信息后不生效。

接下来使用false重新收集下。

   DBMS_STATS.GATHER_TABLE_STATS(
   		ownname => 'SYSDBA', 
   		tabname => 'EMPLOYEES',
   		partname => null, 
   		estimate_percent => 100,
   		block_sample => false,
   		method_opt => 'FOR ALL COLUMNS SIZE AUTO',
   		degree => 8,
   		granularity => 'ALL',
   		cascade => true,
   		stattab => null, 
   		statid => null,
   		statown => null,
   		no_invalidate => false,
   		force => false --强制收集统计信息
    );

再次查看内存，语句和对应的执行计划已清空。

小结

当统计信息过期时，需要更新，但是更新后要想直接更新计划缓存，需要指定 no_invalidate => false。

也就是如下语句。

   DBMS_STATS.GATHER_TABLE_STATS(
   		ownname => 'SYSDBA', 
   		tabname => 'EMPLOYEES',
   		partname => null, 
   		estimate_percent => 100,
   		block_sample => false,
   		method_opt => 'FOR ALL COLUMNS SIZE AUTO',
   		degree => 8,
   		granularity => 'ALL',
   		cascade => true,
   		stattab => null, 
   		statid => null,
   		statown => null,
   		no_invalidate => false,
   		force => false --强制收集统计信息
    );