oracle自动收集统计信息

Oracle数据库提供了自动统计信息收集（Automatic Statistics Gathering）的功能，这可以通过定期自动任务（Scheduler Jobs）或者自动任务（Automatic Maintenance Tasks, AMT）来完成。

以下是启用Oracle自动统计信息收集的步骤：

1. 确保数据库的OPTIMIZER_MODE是设置为ALL_ROWS，FIRST_ROWS，或者FIRST_ROWS_N。

   
   
   
   ALTER SYSTEM SET OPTIMIZER_MODE=ALL_ROWS;

2. 启用自动任务。

   
   
   
   BEGIN
     DBMS_AUTO_TASK_ADMIN.ENABLE(
       client_name => 'auto stats gatherer',
       operation   => NULL,
       window_name => NULL);
   END;
   /

3. 设置统计信息收集的频率。

   你可以通过DBMS_SCHEDULER来创建一个定期执行的作业，例如每天自动收集统计信息。

   
   
   
   BEGIN
     DBMS_SCHEDULER.CREATE_JOB (
       job_name        => 'gather_stats_job',
       job_type        => 'PLSQL_BLOCK',
       job_action      => 'BEGIN -- statistics gathering code here END;',
       start_date      => SYSTIMESTAMP,
       repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0', -- 每天凌晨2点执行
       enabled         => TRUE,
       comments        => 'Job to automatically gather statistics');
   END;
   /

统计信息收集的代码示例：

DECLARE
  l_no_stats COUNT := 0;
BEGIN
  FOR rec IN (SELECT owner, table_name FROM all_tables) LOOP
    BEGIN
      DBMS_STATS.GATHER_TABLE_STATS(
        ownname          => rec.owner,
        tabname          => rec.table_name,
        cascade          => TRUE,
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt       => 'FOR ALL COLUMNS SIZE AUTO');
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        l_no_stats := l_no_stats + 1;
    END;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Number of tables with no stats: ' || TO_CHAR(l_no_stats));
END;
/

以上代码段创建了一个自动执行的作业，用于每天自动收集所有表的统计信息。这是通过循环遍历all_tables视图来实现的，并使用DBMS_STATS.GATHER_TABLE_STATS过程来收集每个表的统计信息。

请注意，实际部署时，你可能需要根据数据库的大小和工作负载调整统计信息收集的频率和设置。