对于经常需要truncate的表进行固定统计信息

 

大家做过统计的一些存储过程可能会知道，我们经常有这类表，要先truncate它，执行插入，再在执行相关sql，这就会导致有一个时间误差，如果在truncate和插入的中间进行了表的分析，这个统计信息是不准确的，也会影响执行计划：

 

SQL> select num_rows,blocks  from user_tables; 

  NUM_ROWS     BLOCKS
---------- ----------
     50315        103

SQL> turncate table daodao_temp;
SP2-0734: unknown command beginning "turncate t..." - rest of line ignored.
SQL> truncate table daodao_temp;

Table truncated.

SQL> select num_rows,blocks  from user_tables; 

  NUM_ROWS     BLOCKS
---------- ----------
     50315        103

SQL> execute dbms_stats.gather_table_stats(user,'DAODAO_TEMP');

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks  from user_tables; 

  NUM_ROWS     BLOCKS
---------- ----------
         0          0

 

 --这里是关键点，我们有个按天分析的job，如果这个时候分析了这个数据，会认为数据为0，但是之后就是录入数据到临时表

 

SQL> select num_rows,blocks  from user_tables;

  NUM_ROWS     BLOCKS
---------- ----------
         0          0

SQL> insert into daodao_temp select object_id,object_id from dba_objects;

50315 rows created.

SQL> commit;

Commit complete.

有数据进行入库：

好了，这个时候已经不会再执行统计信息的存储过程了（除非第二天的时候），这个时候如果有一个sql执行，就会导致执行计划可能错误了。

这种现象在月初尤其明显，道理类似的。

我们可以对这类临时表进行录入数据的锁定统计信息：

SQL> execute dbms_stats.gather_table_stats(user,'DAODAO_TEMP');

SQL> select num_rows,blocks  from user_tables;

  NUM_ROWS     BLOCKS
---------- ----------
     50315        103

SQL> execute DBMS_STATS.LOCK_TABLE_STATS(user,'DAODAO_TEMP');

PL/SQL procedure successfully completed.

SQL> TRUNCATE TABLE DAODAO_TEMP;

Table truncated.

SQL> execute dbms_stats.gather_table_stats(user,'DAODAO_TEMP');
BEGIN dbms_stats.gather_table_stats(user,'DAODAO_TEMP'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 13056
ORA-06512: at "SYS.DBMS_STATS", line 13076
ORA-06512: at line 1

这样搜集统计信息的时候就不会搜集了，相关数据字典可以查询这个:

SQL> select stattype_locked from user_tab_statistics where table_name ='DAODAO_TEMP';

STATT
-----
ALL

all表示锁定了 ，空表示没有锁定：

如果需要解锁，可以执行如下:

SQL> execute dbms_stats.unlock_table_stats(user,'DAODAO_TEMP');

PL/SQL procedure successfully completed.