Oracle统计信息的魔力

一、Oracle统计信息的概述

Oracle统计信息是数据库性能调优的关键组成部分，它为数据库优化器提供了关于表、索引、列和分区等对象的详细信息。这些统计信息可以帮助数据库优化器更智能地制定执行计划，从而提高查询性能。Oracle通过自动收集和更新统计信息，这些信息对于Oracle的优化器（CBO）来说非常重要，因为优化器需要根据统计信息来决定查询的最佳执行计划。

二、统计信息主要包括以下内容

表和索引的大小、行数、块数等。
数据的分布情况，例如数据的偏斜程度、不同值的数量、空值的数量等。
索引的分布和键值的信息。
这些统计信息可以帮助优化器更好地理解数据，从而选择更有效的查询路径。例如，如果一个表只有很少的数据，而一个全表扫描的成本很低，那么优化器可能会选择全表扫描而不是使用索引。相反，如果表的数据量很大，并且使用索引的成本更低，那么优化器可能会选择使用索引。

三、收集统计信息的方法

GATHER_STATS_JOB 是Oracle中的一个作业，主要用于自动收集数据库的统计信息。该作业是在数据库创建时自动创建的，由Scheduler来管理。该作业的主要任务是检测哪些对象没有统计信息或者统计信息比较陈旧，然后优先对这些对象进行分析。

GATHER_STATS_JOB 作业一般会在特定的时间段内运行，例如晚上10点到早上6点或者周末全天。这种安排可以尽量减少对数据库性能的影响。

如果您想要手动执行GATHER_STATS_JOB，可以使用以下SQL命令：
EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SCHEMA_NAME’, ‘TABLE_NAME’);

当参数STATISTICS_LEVEL设置为TYPICAL或者ALL，系统就会在夜间自动收集统计信息

查看系统自动收集统计信息的job：
SELECT * FROM dba_scheduler_jobs WHERE job_name = ‘GATHER_STATS_JOB’;

关闭自动收集统计信息
BEGIN
dbms_scheduler.disable(‘GATHER_STATS_JOB’);
END;

使用dbms_stats 程序包手动收集收集的是系统统计信息

DBMS_STATS 提供了一些过程（如 GATHER_TABLE_STATS、GATHER_INDEX_STATS 等）来收集或修改统计信息。这些过程可以针对表、索引、列或分区进行操作。
例如，GATHER_TABLE_STATS 过程用于收集表的统计信息，而 GATHER_INDEX_STATS 过程用于收集索引的统计信息。

– 查看是否开启统计信息功能

select client_name,status from dba_autotask_client where client_name = 'auto optimizer stats collection';

四、 案例

例：创建一个job用来收集apps用户下MRP_SCHD_DATES_SN表的统计信息，并且每天10:00中自动收集
代码：

BEGIN  
  DBMS_SCHEDULER.create_job (  
    job_name        => 'gather_mrp_schd_dates_sn_stats',  
    job_type         => 'PLSQL_BLOCK',  
    job_action       => 'BEGIN dbms_stats.gather_table_stats(ownname => ''APPS'', tabname => ''MRP_SCHD_DATES_SN'', estimate_percent => 10, cascade => true, degree => 8); END;',  
    start_date       => SYSTIMESTAMP,  
    repeat_interval  => 'FREQ=DAILY; BYHOUR=10; BYMINUTE=0',  
    enabled          => TRUE);  
END;  
/

参数解释：
ownname：指定表所属的模式或用户。在这个例子中，表MRP_SCHD_DATES_SN属于APPS模式。
tabname：指定要收集统计信息的表的名称。在这个例子中，要收集表MRP_SCHD_DATES_SN的统计信息。
estimate_percent：指定用于收集统计信息的样本数据的百分比。在这个例子中，使用10%的样本数据进行统计信息收集。
cascade：指定是否级联收集相关对象的统计信息。如果设置为TRUE，则会收集与表MRP_SCHD_DATES_SN相关的索引、分区等对象的统计信息。在这个例子中，设置为TRUE。
degree：指定并行处理的度数。这个参数用于指定在收集统计信息时使用的并行度。在这个例子中，设置为8，表示使用8个并行进程进行统计信息收集。
repeat_interval 设置您希望的调度频率，例如每天的10:00

验证：

– 查看数据库的所有统计信息

SELECT OWNER,TABLE_NAME,OBJECT_TYPE,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN,SAMPLE_SIZE,GLOBAL_STATS,
USER_STATS,LAST_ANALYZED  FROM DBA_TAB_STATISTICS;

– 查看APPS用户下关于MRP_SCHD_DATES_SN表的统计信息

SELECT OWNER,TABLE_NAME,OBJECT_TYPE,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN,SAMPLE_SIZE,GLOBAL_STATS,
USER_STATS,LAST_ANALYZED  FROM DBA_TAB_STATISTICS WHERE TABLE_NAME = 'MRP_SCHD_DATES_SN' AND OWNER = 'APPS';

– 查看APPS下的统计信息

SELECT OWNER,TABLE_NAME,OBJECT_TYPE,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN,SAMPLE_SIZE,GLOBAL_STATS,
USER_STATS,LAST_ANALYZED  FROM DBA_TAB_STATISTICS WHERE  OWNER = 'APPS';

– 查看定时执行的JOB信息

SELECT OWNER,JOB_NAME,JOB_STYLE,JOB_CREATOR, STATE,JOB_TYPE,JOB_ACTION,START_DATE,REPEAT_INTERVAL, LAST_START_DATE,LAST_RUN_DURATION, NEXT_RUN_DATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_MRP_SCHD_DATES_SN_STATS';

– 查看作业GATHER_MRP_SCHD_DATES_SN_STATS的日志

SELECT * FROM DBA_SCHEDULER_JOB_LOG WHERE JOB_NAME = 'GATHER_MRP_SCHD_DATES_SN_STATS';