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

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

SQL>selectnum_rows,blocksfromuser_tables;

NUM_ROWS     BLOCKS

---------- ----------

50315        103

SQL> turncate tabledaodao_temp;

SP2-0734: unknown command beginning "turncate t..."- restofline ignored.

SQL> truncatetabledaodao_temp;

Tabletruncated.

SQL> selectnum_rows,blocksfromuser_tables;

NUM_ROWS     BLOCKS

---------- ----------

50315        103

SQL> executedbms_stats.gather_table_stats(user,'DAODAO_TEMP');

PL/SQL proceduresuccessfully completed.

SQL> selectnum_rows,blocksfromuser_tables;

NUM_ROWS     BLOCKS

---------- ----------

0          0

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.