收集表的统计信息(表大小写没关系,USER是一个SQL函数):
HR@ prod> begin
2 dbms_stats.gather_table_stats
3 ( ownname => user ,
4 tabname => 'EMPLOYEES' ) ;
5 end ;
6 /
PL/SQL procedure successfully completed.
HR@ prod> begin
2 dbms_stats.gather_table_stats
3 ( ownname => user ,
4 tabname => 'employees' ) ;
5 end ;
6 /
收集整个方案的统计信息:
HR@ prod> begin
2 dbms_stats.gather_schema_stats
3 ( ownname => user ) ;
4 end ;
5 /
PL/SQL procedure successfully completed.
收集整个方案中统计信息过时的表的统计信息:
HR@ prod> begin
2 dbms_stats.gather_schema_stats
3 ( ownname => user ,
4 options => 'gather stale' ) ;
5 end ;
6 /
PL/SQL procedure successfully completed.
为所有索引列创建直方图:
HR@ prod> begin
2 dbms_stats.gather_schema_stats
3 ( ownname => 'hr' ,
4 method_opt => 'for all indexed columns size auto' ) ;
5 end ;
6 /
PL/SQL procedure successfully completed.
修改默认收集方式,只为数据倾斜分布的索引列创建直方图:
HR@ prod> conn / as sysdba
Connected.
SYS@ prod> begin
2 dbms_stats.set_database_prefs
3 ( pname => 'method_opt' ,
4 pvalue => 'for all indexed columns size skewonly' ) ;
5 end ;
6 /
PL/SQL procedure successfully completed.
删除统计信息
HR@ prod> begin
2 dbms_stats.delete_schema_stats
3 ( ownname => user ) ;
4 end ;
5 /
PL/SQL procedure successfully completed.
HR@ prod> select NUM_ROWS from user_tables where table_name = 'EMPLOYEES10' ;
NUM_ROWS
----------