收集统计信息

DBMS_STATS包

    我们经常使用dbms_stats包来对数据库进行统计信息收集,包括表，用户模式，数据库级别的统计信息收集，当然在生产环境中不要轻易收集全库的统计信息常用的有gather_databases_stats,gather_table_stats,gather_schema_stats等过程

1.收集数据库用户模式的统计信息

SQL> exec  dbms_stats.gather_schema_stats('SCOTT',cascade=>TRUE);

PL/SQL procedure successfully completed.

2.收集表的统计信息

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T',cascade=>TRUE);

PL/SQL procedure successfully completed.

当指定cascade=>true 时，会导致表上的所有索引的统计信息都被收集，如果不指定cascade=>true,这时Oracle对于表的上索引统计信息不一定会收集，可以在表级别，用户模式级别，或者数据库级别改变cascade参数的默认值

3.收集索引的统计信息

SQL> exec dbms_stats.gather_index_stats('scott','idx_t');

PL/SQL procedure successfully completed.

4.可以使用以下过程来收集用户模式下的所有索引统计信息(数据量大的生产环境慎用)
SQL>set serveroutput on 
SQL>begin
for ind in 
(select object_name
from user_objects
where object_type='INDEX')
loop
dbms_output.put_line(
'Gathering Index Statistics for '|| ind.object_name ||'...');
dbms_stats.gather_index_stats(user,ind.object_name,estimate_percent=>100);
dbms_output.put_line('Gathering Index Statistics for '|| ind.object_name||' is Coplete!');
end loop;
end;
/

Gathering Index Statistics for IDX_EMP_HIRE.....
Gathering Index Statistics for IDX_EMP_HIRE is Coplete!
Gathering Index Statistics for IDX_ID.....
Gathering Index Statistics for IDX_ID is Coplete!
Gathering Index Statistics for IDX_ID2.....
Gathering Index Statistics for IDX_ID2 is Coplete!
Gathering Index Statistics for PK_DEPT.....
Gathering Index Statistics for PK_DEPT is Coplete!
Gathering Index Statistics for PK_EMP.....
Gathering Index Statistics for PK_EMP is Coplete!
Gathering Index Statistics for IDX_T.....
Gathering Index Statistics for IDX_T is Coplete!
Gathering Index Statistics for PK1_OBJECT_ID.....
Gathering Index Statistics for PK1_OBJECT_ID is Coplete!
Gathering Index Statistics for IDX_PART_ID.....
Gathering Index Statistics for IDX_PART_ID is Coplete!
Gathering Index Statistics for IDX_PART_NBR.....
Gathering Index Statistics for IDX_PART_NBR is Coplete!
Gathering Index Statistics for IDX_I.....
Gathering Index Statistics for IDX_I is Coplete!
Gathering Index Statistics for TEST_INDEX_B.....
Gathering Index Statistics for TEST_INDEX_B is Coplete!

PL/SQL procedure successfully completed.