【Oracle Database】收集统计信息--DBMS_STAT

默认参数
SQL>  select dbms_stats.get_param('ESTIMATE_PERCENT') from dual; 

DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
------------------------------------------------------------------------------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE

SQL> select dbms_stats.get_param('METHOD_OPT') from dual;

DBMS_STATS.GET_PARAM('METHOD_OPT')
------------------------------------------------------------------------------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO

SQL>  select dbms_stats.get_param('DEGREE') from dual; 

DBMS_STATS.GET_PARAM('DEGREE')
------------------------------------------------------------------------------------------------------------------------------------------------------
NULL

SQL> select dbms_stats.get_param('GRANULARITY') from dual; 

DBMS_STATS.GET_PARAM('GRANULARITY')
------------------------------------------------------------------------------------------------------------------------------------------------------
AUTO

SQL> select dbms_stats.get_param('CASCADE') from dual; 

DBMS_STATS.GET_PARAM('CASCADE')
------------------------------------------------------------------------------------------------------------------------------------------------------
DBMS_STATS.AUTO_CASCADE

SQL> select dbms_stats.get_param('NO_INVALIDATE') from dual; 

DBMS_STATS.GET_PARAM('NO_INVALIDATE')
------------------------------------------------------------------------------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE

收集表的统计信息
SQL> exec dbms_stats.gather_table_stats(ownname => 'SOE',tabname => 'CUSTOMERS',estimate_percent => 100,degree=> 4,cascade=> true);

收集索引的统计信息
SQL> exec dbms_stats.gather_index_stats(ownname => 'SOE',indname => 'CUSTOMERS_PK',estimate_percent => 100,degree => 4);

收集用户的统计信息
SQL> exec dbms_stats.gather_schema_stats(ownname=> 'SOE',estimate_percent=> 100,degree=> 4,cascade=> true);

收集数据库的统计信息
SQL> exec dbms_stats.gather_database_stats(estimate_percent=>100,degree=>4,cascade=>true);

收集数据字典的统计信息
SQL> exec dbms_stats.gather_dictionary_stats();

收集系统的统计信息
SQL> exec dbms_stats.gather_system_stats();

删除表的统计信息
SQL> exec dbms_stats.delete_table_stats ('SOE','CUSTOMERS');

删除索引的统计信息
SQL> exec dbms_stats.delete_index_stats('SOE','CUSTOMERS_PK');

删除用户的统计信息
SQL> exec dbms_stats.delete_schema_stats ('SOE');

删除数据库的统计信息
SQL> exec dbms_stats.delete_database_stats;