Gather Stats

ORACLE 中dbms_stats的使用

dbms_stats能良好地估计统计数据（尤其是针对较大的分区表），并能获得更好的统计结果，最终制定出速度更快的SQL执行计划。

exec dbms_stats.gather_schema_stats(
ownname          => 'SCOTT',
options          => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt       => 'for all columns size repeat',
degree           => 15
)

       为了充分认识dbms_stats的好处，需要仔细体会每一条主要的预编译指令（directive）。下面让我们研究每一条指令，并体会如何用它为基于代价的SQL优化器收集最高质量的统计数据。
options参数

使用4个预设的方法之一，这个选项能控制Oracle统计的刷新方式：

gather——重新分析整个架构（Schema）。 
gather empty——只分析目前还没有统计的表。 
gather stale——只重新分析修改量超过10%的表（这些修改包括插入、更新和删除）。 
gather auto——重新分析当前没有统计的对象，以及统计数据过期（变脏）的对象。注意，使用gather auto类似于组合使用gather stale和gather empty。 
       注意，无论gather stale还是gather auto，都要求进行监视。如果你执行一个alter table xxx monitoring命令，Oracle会用dba_tab_modifications视图来跟踪发生变动的表。这样一来，你就确切地知道，自从上一次分析统计数据以来，发生了多少次插入、更新和删除操作。

estimate_percent选项

estimate_percent参数是一种比较新的设计，它允许Oracle的dbms_stats在收集统计数据时，自动估计要采样的一个segment的最佳百分比：
estimate_percent => dbms_stats.auto_sample_size

       要验证自动统计采样的准确性，你可检视dba_tables sample_size列。一个有趣的地方是，在使用自动采样时，Oracle会为一个样本尺寸选择5到20的百分比。记住，统计数据质量越好，CBO做出的决定越好。

method_opt选项
method_opt：for table --只统计表　
                      for all indexed columns --只统计有索引的表列 
                      for all indexes --只分析统计相关索引 
                      for all columns

dbms_stats的method_opt参数尤其适合在表和索引数据发生变化时刷新统计数据。method_opt参数也适合用于判断哪些列需要直方图（histograms）。

      某些情况下，索引内的各个值的分布会影响CBO是使用一个索引还是执行一次全表扫描的决策。例如，假如在where子句中指定的值的数量不对称，全表扫描就显得比索引访问更经济。

       如果你有一个高度倾斜的索引（某些值的行数不对称），就可创建Oracle直方图统计。但在现实世界中，出现这种情况的机率相当小。使用CBO时，最常见的错误之一就是在CBO统计中不必要地引入直方图。根据经验，只有在列值要求必须修改执行计划时，才应使用直方图。

为了智能地生成直方图，Oracle为dbms_stats准备了method_opt参数。在method_opt子句中，还有一些重要的新选项，包括skewonly，repeat和auto：
method_opt=>'for all columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size auto'

     skewonly选项会耗费大量处理时间，因为它要检查每个索引中的每个列的值的分布情况。

     假如dbms_stat发现一个索引的各个列分布得不均匀，就会为那个索引创建直方图，帮助基于代价的SQL优化器决定是进行索引访问，还是进行全表扫描访问。例如，在一个索引中，假定有一个列在50%的行中，如清单B所示，那么为了检索这些行，全表扫描的速度会快于索引扫描。

--*************************************************************
-- SKEWONLY option—Detailed analysis
--
-- Use this method for a first-time analysis for skewed indexes
-- This runs a long time because all indexes are examined
--*************************************************************
 
begin
  dbms_stats.gather_schema_stats(
     ownname          => 'SCOTT',
     estimate_percent => dbms_stats.auto_sample_size,
     method_opt       => 'for all columns size skewonly',
      degree           => 7
   );
end;

        重新分析统计数据时，使用repeat选项，重新分析任务所消耗的资源就会少一些。使用repeat选项（清单C）时，只会为现有的直方图重新分析索引，不再搜索其他直方图机会。定期重新分析统计数据时，你应该采取这种方式。

--**************************************************************
-- REPEAT OPTION - Only reanalyze histograms for indexes
-- that have histograms
--
-- Following the initial analysis, the weekly analysis
-- job will use the “repeat” option. The repeat option
-- tells dbms_stats that no indexes have changed, and
-- it will only reanalyze histograms for
-- indexes that have histograms.
--**************************************************************
begin
   dbms_stats.gather_schema_stats(
      ownname          => 'SCOTT',
      estimate_percent => dbms_stats.auto_sample_size,
      method_opt       => 'for all columns size repeat',
      degree           => 7
   );
end;

       使用alter table xxx monitoring;命令来实现Oracle表监视时，需要使用dbms_stats中的auto选项。如清单D所示，auto选项根据数据分布以及应用程序访问列的方式（例如通过监视而确定的一个列的工作量）来创建直方图。使用method_opt=>’auto’类似于在dbms_stats的option参数中使用gather auto。

begin
  dbms_stats.gather_schema_stats(
     ownname          => 'SCOTT',
      estimate_percent => dbms_stats.auto_sample_size,
      method_opt       => 'for all columns size auto',
      degree           => 7
   );
end;

并行统计收集degree参数
Oracle推荐设置DBMS_STATS的DEGREE参数为DBMS_STATS.AUTO_DEGREE，该参数允许Oracle根据对象的大小和并行性初始化参数的设置选择恰当的并行度。
聚簇索引，域索引，位图连接索引不能并行收集。

 

如何使用dbms_stats分析统计信息？
--创建统计信息历史保留表 

sql> exec dbms_stats.create_stat_table(ownname => 'scott',stattab => 'stat_table') ; 

--导出整个scheme的统计信息 

sql> exec dbms_stats.export_schema_stats(ownname => 'scott',stattab => 'stat_table') ; 

--分析scheme

Exec dbms_stats.gather_schema_stats( 
ownname => 'scott', 
options => 'GATHER AUTO', 
estimate_percent => dbms_stats.auto_sample_size, 
method_opt => 'for all indexed columns ', 
degree => 6 ) 

--分析表

sql> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'work_list',estimate_percent => 10,method_opt=> 'for all indexed columns') ; 

--分析索引

SQL> exec dbms_stats.gather_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',estimate_percent => '10',degree => '4') ;

--如果发现执行计划走错，删除表的统计信息

SQL>dbms_stats.delete_table_stats(ownname => 'scott',tabname => 'work_list') ;

--导入表的历史统计信息

sql> exec dbms_stats.import_table_stats(ownname => 'scott',tabname => 'work_list',stattab => 'stat_table') ; 

--如果进行分析后，大部分表的执行计划都走错，需要导回整个scheme的统计信息

sql> exec dbms_stats.import_schema_stats(ownname => 'scott',stattab => 'stat_table');

--导入索引的统计信息

SQL> exec dbms_stats.import_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',stattab => 'stat_table') 

--检查是否导入成功

SQL> select table_name,num_rows,a.blocks,a.last_analyzed from all_tables a where a.table_name='WORK_LIST'; 

分析数据库（包括所有的用户对象和系统对象）：gather_database_stats 
分析用户所有的对象（包括表、索引、簇）：gather_schema_stats
分析表：gather_table_stats
分析索引：gather_index_stats 
删除数据库统计信息：delete_database_stats
删除用户方案统计信息：delete_schema_stats
删除表统计信息：delete_table_stats
删除索引统计信息：delete_index_stats
删除列统计信息：delete_column_stats 
设置表统计信息：set_table_stats
设置索引统计信息：set_index_stats
设置列统计信息：set_column_stats 

从Oracle Database 10g开始，Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务，用于自动收集CBO的统计信息。

这个自动任务默认情况下在工作日晚上10：00-6：00和周末全天开启。调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息。
该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级，再开始进行统计信息。

可以通过以下查询这个JOB的运行情况：

select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'

其实同在10点运行的Job还有一个AUTO_SPACE_ADVISOR_JOB：

 

SQL> select JOB_NAME,LAST_START_DATE from dba_scheduler_jobs;

JOB_NAME                      LAST_START_DATE
------------------------------ ----------------------------------------
AUTO_SPACE_ADVISOR_JOB        04-DEC-07 10.00.00.692269 PM +08:00
GATHER_STATS_JOB              04-DEC-07 10.00.00.701152 PM +08:00
FGR$AUTOPURGE_JOB
PURGE_LOG                      05-DEC-07 03.00.00.169059 AM PRC

然而这个自动化功能已经影响了很多系统的正常运行，晚上10点对于大部分生产系统也并非空闲时段。
而自动分析可能导致极为严重的闩锁竞争，进而可能导致数据库Hang或者Crash。

所以建议最好关闭这个自动统计信息收集功能

方法之一:
exec dbms_scheduler.disPfile可以直接修改初始化参数文件然后重新启动数据库。

从Oracle Database 10g开始，Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务，用于自动收集CBO的统计信息。

这个自动任务默认情况下在工作日晚上10：00-6：00和周末全天开启。调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息。该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级，再开始进行统计信息。

可以通过以下查询这个JOB的运行情况：

select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'

其实同在10点运行的Job还有一个AUTO_SPACE_ADVISOR_JOB：

SQL> select JOB_NAME,LAST_START_DATE from dba_scheduler_jobs;

JOB_NAME LAST_START_DATE
------------------------------ ----------------------------------------
AUTO_SPACE_ADVISOR_JOB 04-DEC-07 10.00.00.692269 PM +08:00
GATHER_STATS_JOB 04-DEC-07 10.00.00.701152 PM +08:00
FGR$AUTOPURGE_JOB
PURGE_LOG 05-DEC-07 03.00.00.169059 AM PRC

然而这个自动化功能已经影响了很多系统的正常运行，晚上10点对于大部分生产系统也并非空闲时段。
而自动分析可能导致极为严重的闩锁竞争，进而可能导致数据库Hang或者Crash。

所以建议最好关闭这个自动统计信息收集功能：

exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

自动化永远而严重的隐患相伴随！



-----------------------------------------------------------------------------------------------------------------

Oracle 10g下cbo的自动统计分析功能很大程序的解决了因为9i下统计信息失效或者陈旧导致的sql语句选择了不合适的执行计划，该统计分析功能是通过dba_scheduler_jobs视图下的gather_stats_job来对系统的对象进行统计分析，gather_stats_job在星期一到星期五在晚上10点执行，双休从早上6点到星期一早上6点，该统计分析由于需要对数据库的所有对象进行分析，所以可能该job并没有完成，查看dba_scheduler_job_run_details可以对job执行是否完毕，可以看出在大型的生产库中很多情况该job都没有执行完毕就中止了。

SQL> select log_date,job_name,status from dba_scheduler_job_run_details where job_name='GATHER_STATS_JOB' order by log_id;

LOG_DATEJOB_NAME STATUS

-------------------------------------------------- -------------------- --------------------

07-4月 -12 06.01.40.434786 上午 +08:00GATHER_STATS_JOB STOPPED

09-4月 -12 10.27.13.012514 下午 +08:00GATHER_STATS_JOB STOPPED

10-4月 -12 10.33.26.498880 下午 +08:00GATHER_STATS_JOB STOPPED

11-4月 -12 10.08.14.335380 下午 +08:00GATHER_STATS_JOB STOPPED

12-4月 -12 10.24.57.878692 下午 +08:00GATHER_STATS_JOB STOPPED

13-4月 -12 10.09.23.672033 下午 +08:00GATHER_STATS_JOB STOPPED

14-4月 -12 06.01.43.666416 上午 +08:00GATHER_STATS_JOB STOPPED

那么gather_stats_job调用的是什么了，通过视图dba_scheduler_program可以查看

select program_action from dba_scheduler_programs where program_name='GATHER_STATS_PROG'

dbms_stats.gather_database_stats_job_proc

可以看出gather_stats_job调用dbms_stats.gather_database_stats_job_proc的procedure来检测统计信息缺失和失效的对象，然后确定优先级进行统计和分析信息。

（统计信息缺失可能都知道，但是失效如何解释，oracle中规定了修改的行达到了数据的10%，则就意味统计信息失效，关于这个修改的行则是oracle的另外一个视图sys.dba_tab_modification中的记录来对表等对象的统计信息是否失效来进行判断）

Cbo进行自动统计分析，不过如果查看生产系统中的表可以发现user_tables中的last_analyze_time不是近端时间的表大有所在，cbo进行自动统计分析需要表中的缺少统计信息，或者修改超过10%，如果没有达到，cbo下的自动分析并不会对该对象进行分析。

Cbo正是因为其根据统计信息而采取的最优的执行计划而减小了无谓的消耗，提高sql的性能，可以这么说cbo是完全依赖于统计信息，如果缺失cbo可以采取动态取样，oracle 10g下optimizer_dynamic_sampling默认是2，那么9i下的optimizer_dynamic_sampling是1。

（1） sql语句中必须有相应的至少一个没有采样信息的表

（2） 未分析的表没有索引

（3） 未分析的表占用的数据块要大于动态采样默认的数据块（32个）

9i下需要对表进行alter table tablename monitor 才会对表的修改进行监控，进而写入到dba_tab_monitoring视图中，而10g下默认都会对表进行监控。

这里说一下最近犯过的一个不知道算不算错误，生产库由于出现了大量的db file sequential read影响了性能，由于查看sql语句发现该逻辑读都很小，基本都是主键产生等待，根据awr的sga advisor增加了sga大小，然后重启数据库，居然发现了一个30多g的大表主键不停的fts，这可以说非常的不正常，隐式转换，统计信息失效但是由于设置的optimizer_dynamic_sampling觉得不是统计信息失效造成的，很有可能是隐式转换或者cbo的错误执行计划导致，由于没有metalink也无法去追踪具体的由来的，当时查看自动分析依然存在在晚上10点后会进行自动统计分析，第二天后业务恢复正常，选择了unique index。

不过当时自己其实完全可以利用dbms_stats.restore_table_stats(‘DESKTOP’,’ARTICLE’,sysdate-60/1440)恢复统计信息到一个小时前，不过案例不在，也是一个很痛苦的事情。

而统计信息保存的时间可以通过select dbms_stats.get_stats_hisory_retention from dual;

不过和undo_retention一样该参数并不是一个担保值

Select dbms_stats.get_stats_history_availability from dual;

统计信息的可用时间。

关闭cbo的自动统计分析dbms_scheduler.disable(‘GATHER_STATS_JOB’)，cbo下的自动分析需要消耗一定量的latch可能造成latch竞争，不过合理的统计信息却是执行计划合理的保证，所以这里也没有什么关于是否开启于关闭自动统计分析的建议，不过如果能真正了解cbo具体的行为，那么根据你自己的业务情况，相信肯定有个合适的选择。

able('SYS.GATHER_STATS_JOB');
恢复自动分析:
exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');

方法二：
alter system set "_optimizer_autostats_job"=false scope=spfile;
alter system set "_optimizer_autostats_job"=true scope=spfile;
Pfile可以直接修改初始化参数文件，重新启动数据库。

从Oracle Database 10g开始，Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务，用于自动收集CBO的统计信息。

这个自动任务默认情况下在工作日晚上10：00-6：00和周末全天开启。调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息。该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级，再开始进行统计信息。

可以通过以下查询这个JOB的运行情况：

select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'

其实同在10点运行的Job还有一个AUTO_SPACE_ADVISOR_JOB：

SQL> select JOB_NAME,LAST_START_DATE from dba_scheduler_jobs;

JOB_NAME LAST_START_DATE
------------------------------ ----------------------------------------
AUTO_SPACE_ADVISOR_JOB 04-DEC-07 10.00.00.692269 PM +08:00
GATHER_STATS_JOB 04-DEC-07 10.00.00.701152 PM +08:00
FGR$AUTOPURGE_JOB
PURGE_LOG 05-DEC-07 03.00.00.169059 AM PRC

然而这个自动化功能已经影响了很多系统的正常运行，晚上10点对于大部分生产系统也并非空闲时段。
而自动分析可能导致极为严重的闩锁竞争，进而可能导致数据库Hang或者Crash。

所以建议最好关闭这个自动统计信息收集功能：

exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

自动化永远而严重的隐患相伴随！

 

-----------------------------------------------------------------------------------------------------------------

Oracle 10g下cbo的自动统计分析功能很大程序的解决了因为9i下统计信息失效或者陈旧导致的sql语句选择了不合适的执行计划，该统计分析功能是通过dba_scheduler_jobs视图下的gather_stats_job来对系统的对象进行统计分析，gather_stats_job在星期一到星期五在晚上10点执行，双休从早上6点到星期一早上6点，该统计分析由于需要对数据库的所有对象进行分析，所以可能该job并没有完成，查看dba_scheduler_job_run_details可以对job执行是否完毕，可以看出在大型的生产库中很多情况该job都没有执行完毕就中止了。

SQL> select log_date,job_name,status from dba_scheduler_job_run_details where job_name='GATHER_STATS_JOB' order by log_id;

LOG_DATEJOB_NAME STATUS

-------------------------------------------------- -------------------- --------------------

07-4月 -12 06.01.40.434786 上午 +08:00GATHER_STATS_JOB STOPPED

09-4月 -12 10.27.13.012514 下午 +08:00GATHER_STATS_JOB STOPPED

10-4月 -12 10.33.26.498880 下午 +08:00GATHER_STATS_JOB STOPPED

11-4月 -12 10.08.14.335380 下午 +08:00GATHER_STATS_JOB STOPPED

12-4月 -12 10.24.57.878692 下午 +08:00GATHER_STATS_JOB STOPPED

13-4月 -12 10.09.23.672033 下午 +08:00GATHER_STATS_JOB STOPPED

14-4月 -12 06.01.43.666416 上午 +08:00GATHER_STATS_JOB STOPPED

那么gather_stats_job调用的是什么了，通过视图dba_scheduler_program可以查看

select program_action from dba_scheduler_programs where program_name='GATHER_STATS_PROG'

dbms_stats.gather_database_stats_job_prc

 select to_char(last_analyzed,'YYYY-MM-DD HH24:MI:SS'),owner,monitoring from dba_tables where table_name='YY';

 

对象信息收集机制

一种通过ANALYZE命令，一种通过DBMS_STATS报中的GATHER_*方法获得，这两种是手动方式。从10G开始，这个工作是GATHER_STATS_JOB自动完成。

手动：

SQL>ANALYZE TABLE TABLE1 VALIDATE STRUCTURE CASCADE;

SQL>ANALYZE INDEX INDEX1 COMPUTE STATISTICS;

 

查看自动信息：

ORACLE 10G中：

SQL>EXEC DBMS_SCHEDULER.ENABLE('SYS.GATHER_STATS_JOB');  --启动自动收集

SQL>EXEC DBMS_SCHEDULER.DISABLE('SYS.GATHER_STATS_JOB');  --关闭自动收集

SQL>SELECT JOB_NAME, STATE, COMMENTS FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME LIKE 'GATHER%';

如果有GATHER_STATS_JOB，那就是已经开启。

 

ORACLE 11G中：

ORACLE 11G中GATHER_STATS_JOB被BSLN_MAINTAIN_STATS_JOB替换了。

查询状态：

SQL> SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT;

如果auto optimizer stats collection，auto space advisor，sql tuning advisor都ENABLED了就说明开启了。

 

TABLE MONITORING机制：

ORACLE 10G对对象收集优化：只针对那些发生了重大变化的，原有统计信息已经陈旧的对象才重新收集统计数据。

监控原理：

ORACLE会监控对象上发生的更改，包括INSERT、UPDATE、DELETE、TRUNCATE的数据量。如果改变的数据量超过记录总数的10%，则重新收集统计信息。监控收到参数STATISTICS_LEVEL控制。可以用如下语句查看：

SQL>SELECT STATISTICS_NAME, SYSTEM_STATUS, ACTIVATION_LEVEL, STATISTICS_VIEW_NAME FROM V$STATISTICS_LEVEL;

其中STATISTICS_LEVEL参数值被设置成TYPICAL或者ALL，就会启动表监控。

监控信息从USER_TAB_MODIFICATIONS视图中查看。

SQL> SELECT TABLE_NAME, PARTITION_NAME, INSERTS, UPDATES, DELETES, TRUNCATED, DROP_SEGMENTS, TABLE_OWNER FROM DBA_TAB_MODIFICATIONS WHERE TABLE_OWNER='SCOTT';

收集到的这些信息不是立即更新到系统字典中去，会拖延一段。

也可以手动使用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO方法直接手动同步。

除了可以跟踪DML，还会跟踪每个数据列在查询中的使用情况，从COL_USAGE$中查看。

SQL>SELECT * FROM COL_USAGE$;

其中LIKE_PREDS表示在LIKE条件中的使用次数。这些会被ADDM作为SQL Access Advisor的依据。

 

动态收集对象信息

要想使用动态数据收集，需要把对象的统计信息设置成NULL。当ORACLE遇到一个统计信息是NULL的对象会动态收集其统计信息。就是通过抽取几个数据块作为样本来推算整个对象的统计信息。

初始化参数OPTIMIZER_DYNAMIC_SAMPLING，这个参数是采样提供数据块数量，缺省是2。

0：不适用动态统计信息收集。

1：使用32块进行统计信息收集。

2~9：分别是2、4、8、16、32、64、128、256倍的32块进行收集。

10：所有数据块。

还可以用HINT强行让优化器动态收集统计信息。

SQL>SELECT * from table1;

 

统计信息历史

统计信息的变化历史可以从DBA_TAB_STATS_HISTORY视图中看到。它会记录过去31天来的统计信息被修改的历史。

SQL>SELECT * FROM DBA_TAB_STATS_HISTORY;

*注：ANALYZE命令产生的数据不会保存历史信息。

查看保留策略：

SQL>SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;

修改保留策略：

SQL>EXECUTE EBMS_STATS.ALTER_STATS_HISTORY_RETENTION(45);

 

统计的信息历史也受到STATISTICS_LEVEL的约束。如果是BASIC，则历史数据不会被自动清理。需要用DBMS_STATS.PURGE_STATS来手动清理。而且BASIC的时候也不会自动收集任何统计数据了。

查看实际可用最早日期：

SQL>SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL;

 

生产中有可能使用恢复历史统计信息，使用

SQL>EXEC DBMS_STATS.RESTORE_TABLE_STATS(OWNERNAME=>’ZXXX’,TABNAME=>’TABLE111’,AS_OF_TIMESTAMP=>’12-MAY-12 10.00.00.000000 AM+12:00’);

来恢复，具体里面的参数需要参考：

SQL>SELECT * FROM DBA_TAB_STATS_HISTORY WHERE TABLE_NAME=’TABLE111’;

来获得

 

操作系统统计数据收集

CBO公式

Cost=IO+CPU÷1000+NetIO×1.5

IO代表物理IO操作量，

CPU代表逻辑IO操作量，

NetIO代表通过数据库连接的网络IO操作量。

 

Single_Block Read每次只会读取一个数据块，经过Buffer Cache，需要用OPTIMIZER_INDEX_CACHING来调整（默认为0）。Multi-Blocks Read会读取多个块，不经过Bufer Cache。

OPTIMZIER_INDEX_CACHING表示索引块被缓存在Buffer Cache中的比率，如果是0，就说明不缓存。如果是80，就表明80%的读取都是逻辑读，不需要物理读。

加入此因素，

COST=(总读取×(1-(OPTIMIZER_INDEX_CACHING÷100)))

×(OPTIMIZER_INDEX_COST_ADJ÷100)

OPTIMIZER_INDEX_COST_ADJ默认为100。

 

用下面这个SQL计算一下“连续读平均等待×100”与“分散读平均等待”的比值：

SQL>SELECT TRUNC(A.AVERAGE_WAIT*100/B.AVERAGE_WAIT) FROM

V$SYSTEM_EVENT A, V$SYSTEM_EVENT B

WHERE A.EVENT='db file sequential read' and

b.event='db file scattered read';

得到的结果，可以设置为OPTIMIZER_INDEX_COST_ADJ参数。

 

ORACLE把IO收集信息放在SYS.AUX_SATS$中，包含两类信息，SYSSTATS_INFO和SYSSTATS_MAIN。

SYSSTATS_INFO描述收集活动自身。

SYSSTATS_MAIN表述真正收集的数据。

操作系统统计数据分成两类：无负载模式NOWORKLOAD，和负载模式WORKLOAD。

无负载模式在数据库启动时就会收集。

负载模式需要手动收集。

两种界定根据自身业务需要，例如凌晨是无负载模式，工作时间是负载模式。

oracle 柱状图(histogram)

oracle中的柱状图是用于记录表中的数据分布质量情况的描述，当每次使用analyze或者dbms_stat包分析数据表及列后，该表的分布情况会呗保存在统计表

(user_tab_columns/user_histograms)里面，当多表连接时,CBO优化器会根据柱状图提供的信息评估多表连接时将产生的成本(cost)或技术(cardinality)，决定是否使用该列的索引，当然，导致CBO不能选择最优执行计划的因素有多种情况，而柱状图只是协助CBO优化器选择最优的执行计划，在一个数据分布不均匀的表列上建立柱状图将有力地保证优化器做出正确合理的选择。

其他因素后面在进行探讨。 (直方图的使用不受索引的限制，可以在表的任何列上构建直方图)

1. 搜集柱状图

SQL> conn scott/tiger
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
 
SQL> select table_name from user_tables;
 
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
 
SQL> exec dbms_stats.gather_table_stats(ownname=>'scott',tabname => 'dept',estimate_percent => null,method_opt => 'for all indexed columns',cascade => true);
 
PL/SQL procedure successfully completed
 
SQL> select column_name,density,num_buckets,histogram from user_tab_col_statistics where table_name='DEPT';
 
COLUMN_NAME                       DENSITY NUM_BUCKETS HISTOGRAM
------------------------------ ---------- ----------- ---------------
DEPTNO                              0.125           4 HEIGHT BALANCED
DNAME                                0.25           1 NONE
LOC                                  0.25           1 NONE

SQL> select * from dept;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

SQL> select * from user_tab_histograms where table_name='DEPT';
 
TABLE_NAME                     COLUMN_NAME                 ENDPOINT_NUMBER     ENDPOINT_VALUE   ENDPOINT_ACTUAL_VALUE
------------------------------ --------------------------------------------  ---------------   -----------------------------
DEPT                           DEPTNO                             1             10
DEPT                           DEPTNO                             2             20
DEPT                           DEPTNO                             3             30
DEPT                           DEPTNO                             4             40
DEPT                           DNAME                              0    3.388635500875
DEPT                           LOC                                0   3.443005050520
DEPT                           DNAME                              1   4.322850386777
DEPT                           LOC                                1   4.064055440899
  
8 rows selected

柱状图的搜集有三个参数，for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围[1,254]; REPEAT上次统计过的histograms;AUTO由oracle决定

N的大小;SKEWONLY

在上面柱状图搜集中，histogram字段有三个值，NONE，FREQUENCY或者HEIGHT BALANCED
  a. NONE：就是没有直方图
  b. FREQUENCY: 当该列的distinct值数量<=bucket数量时，为此类型。在user_tab_histograms表中记录有相关的值
  c. HEIGHT BALANCED：当该列的distinct值数量>bucket数量时，为此类型。
  d. density字段值的含义 Density的含义是“密度”。DENSITY值是会影响CBO判断执行计划的

2. 并不是所有柱状图信息都有存在的必要，产生直方图的成本是很高的，频繁分析一个表，若该表数据量非常大，做一次分析可能导致严重的性能问题，但是那些列的柱状图应该存在呢，建

议如下：

  a. 第一次收集统计信息时，设置method_opt=>FOR ALL COLUMNS SIZE 1，这意味删除所有列上的直方图。
  b. 在测试阶段或者在真实生产环境中，在调优SQL的过程中，DBA将会逐渐得知每个需要直方图信息的字段，在这些字段上人工收集统计信息，method_opt=>FOR COLUMNS SIZE AUTO

[COLUMN_NAME]
  c. 在每次数据分布有所变化的时候，更新统计信息，使用method_opt=>FOR ALL COLUMNS SIZE REPEAT，这样只会收集已经存在了直方图信息的字段。
重复2，3步骤，直到系统稳定。

3. 柱状图是如何影响执行计划的,下面通过示例来查看

SQL> show user
User is "colin"
 
SQL> drop table tmp_liuhc_1;
 
Table dropped

SQL> create table tmp_liuhc_1 as select * from dba_objects;
 
Table created
 
SQL> desc tmp_liuhc_1;
Name           Type          Nullable Default Comments
-------------- ------------- -------- ------- --------
OWNER          VARCHAR2(30)  Y                        
OBJECT_NAME    VARCHAR2(128) Y                        
SUBOBJECT_NAME VARCHAR2(30)  Y                        
OBJECT_ID      NUMBER        Y                        
DATA_OBJECT_ID NUMBER        Y                        
OBJECT_TYPE    VARCHAR2(19)  Y                        
CREATED        DATE          Y                        
LAST_DDL_TIME  DATE          Y                        
TIMESTAMP      VARCHAR2(19)  Y                        
STATUS         VARCHAR2(7)   Y                        
TEMPORARY      VARCHAR2(1)   Y                        
GENERATED      VARCHAR2(1)   Y                        
SECONDARY      VARCHAR2(1)   Y                        
 
SQL> select owner,count(*) from tmp_liuhc_1 group by owner;
 
OWNER                            COUNT(*)
------------------------------ ----------
MDSYS                                 885
TSMSYS                                  3
DMSYS                                 189
LINK                                    3
PUBLIC                              19987
OUTLN                                   8
CTXSYS                                339
OLAPSYS                               720
HR                                     34
SYSTEM                                454
EXFSYS                                281
SCOTT                                   6
DBSNMP                                 46
ORDSYS                               1669
ORDPLUGINS                             10
SYSMAN                               1321
OE                                    127
PM                                     26
SH                                    306
XDB                                   682
 
OWNER                            COUNT(*)
------------------------------ ----------
IX                                     53
BI                                      8
SYS                                 22912
WMSYS                                 242
SI_INFORMTN_SCHEMA                      8
COLIN                                   6
 
26 rows selected

SQL>  create index idx_tmp_liuhc on tmp_liuhc_1(owner);
 
Index created
 
SQL> select sysdate from dual;
 
SYSDATE
-----------
2011-10-30

删除柱状图信息，bucket为1时，相当于一个普通分析，没有柱状图信息，执行计划按绝大多数ID字段内容来选择走索引，删除之后刷新shared_pool
SQL> exec dbms_stats.gather_table_stats(ownname => 'colin',tabname => 'tmp_liuhc_1',estimate_percent => null ,method_opt => 'for all columns size 1' ,cascade => true);
 
PL/SQL procedure successfully completed
 
SQL> alter system flush shared_pool;
 
System altered
 
SQL>  select * from user_tab_col_statistics where column_name='OWNER';
 
TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT LOW_VALUE                                                        HIGH_VALUE                 

                                        DENSITY  NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE GLOBAL_STATS USER_STATS AVG_COL_LEN HISTOGRAM
------------------------------ ------------------------------ ------------ ---------------------------------------------------------------- --------------------------

-------------------------------------- ---------- ---------- ----------- ------------- ----------- ------------ ---------- ----------- ---------------
TMP_LIUHC_1                    OWNER                                    26 4249                                                             584442                     

                                     0.03846153          0           1 2011-10-30 9:       50325 YES          NO                   6 NONE
 
SQL> explain plan for select * from tmp_liuhc_1 where owner='COLIN';
 
Explained
 
SQL> select * From table(Dbms_Xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3774022813
--------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |  1936 |   175K|    57   (0
|   1 |  TABLE ACCESS BY INDEX ROWID| TMP_LIUHC_1   |  1936 |   175K|    57   (0
|*  2 |   INDEX RANGE SCAN          | IDX_TMP_LIUHC |  1936 |       |     5   (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='COLIN')
 
14 rows selected
 
SQL> explain plan for select * from tmp_liuhc_1 where owner='SYS';
 
Explained
 
SQL> select * From table(Dbms_Xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3774022813
--------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |  1936 |   175K|    57   (0
|   1 |  TABLE ACCESS BY INDEX ROWID| TMP_LIUHC_1   |  1936 |   175K|    57   (0
|*  2 |   INDEX RANGE SCAN          | IDX_TMP_LIUHC |  1936 |       |     5   (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='SYS')
 
14 rows selected
 
SQL> exec dbms_stats.gather_table_stats(ownname => 'colin',tabname => 'tmp_liuhc_1',estimate_percent => null ,method_opt => 'for all columns size auto' ,cascade =>

true);
 
PL/SQL procedure successfully completed

SQL> select * from user_tab_col_statistics where column_name='OWNER';
 
TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT LOW_VALUE                                                        HIGH_VALUE                 

                                        DENSITY  NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE GLOBAL_STATS USER_STATS AVG_COL_LEN HISTOGRAM
------------------------------ ------------------------------ ------------ ---------------------------------------------------------------- --------------------------

-------------------------------------- ---------- ---------- ----------- ------------- ----------- ------------ ---------- ----------- ---------------
TMP_LIUHC_1                    OWNER                                    26 4249                                                             584442                     

                                     9.93541977          0          26 2011-10-30 9:       50325 YES          NO                   6 FREQUENCY
 
SQL>
SQL> explain plan for select * from tmp_liuhc_1 where owner='COLIN';
 
Explained
 
SQL> select * From table(Dbms_Xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3774022813
--------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     6 |   558 |     2   (0
|   1 |  TABLE ACCESS BY INDEX ROWID| TMP_LIUHC_1   |     6 |   558 |     2   (0
|*  2 |   INDEX RANGE SCAN          | IDX_TMP_LIUHC |     6 |       |     1   (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='COLIN')
 
14 rows selected
 
SQL> explain plan for select * from tmp_liuhc_1 where owner='SYS';
 
Explained
 
SQL> select * From table(Dbms_Xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1961695573
--------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             | 22912 |  2080K|   160   (2)| 00:00:02
|*  1 |  TABLE ACCESS FULL| TMP_LIUHC_1 | 22912 |  2080K|   160   (2)| 00:00:02
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OWNER"='SYS')
 
13 rows selected

从以上可以看出，当删除柱状图时，查询SYS用户时，CBO按大多数ID字段的内容，选择走索引；当搜集柱状图后，CBO选择了正确的执行计划，走全表扫描，因为前面已经查询了，SYS用户下的表占用了决大部分。

 

附录：附带两张表的解释信息，此处的表DBA_TAB_COLUMNS和表user_tab_col_statistics是同样效果

DBA_TAB_HISTOGRAMS
该视图列出了所有表上列的柱状图信息。

 

字段名称              描述
---------------------------------------------------------
OWNER                 表的所有者
TABLE_NAME            表名
COLUMN_NAME           列名(字段名)
ENDPOINT_NUMBER       终点数值，也就是bucket的编号值
ENDPOINT_VALUE        根据该bucket的编号值，规格化的终点字段值，
                      如果字段类型是数字，则显示该字段值，
                      如果是其他类型的，则是被规格化的数值。

 

DBA_TAB_COLUMNS
该视图包含了所有表上列的描述信息。
(注意：虽然视图和簇的信息也包含在该视图中，但没有相关的柱状图信息)

 

字段名称              描述
----------------------------------------------------------
OWNER                 表的所有者
TABLE_NAME            表名
COLUMN_NAME           列名(字段名)
DATA_TYPE             字段的数据类型
DATA_LENGTH           该列定义的长度
DATA_PRECISION        NUMBER或FLOAT数值类型的精度
DATA_SCALE            定义的小数位数
NULLABLE              是否允许NULL值
COLUMN_ID             该列在表中的顺序的位置，表中第几个字段
DEFAULT_LENGTH        字段默认值的长度
DATA_DEFAULT          字段默认值
NUM_DISTINCT          该字段不同值的数量
LOW_VALUE             该字段的最小值，
                      该值是内部按16进制存储的该值的前32 bytes内容
HIGH_VALUE            该字段的最大值，
                      该值是内部按16进制存储的该值的前32 bytes内容
DENSITY               该字段的密度(不同值的比例值，
                      比如一个字段只有2个不同值，那么该字段密度为0.5)
NUM_NULLS             该字段NULL值的数量
NUM_BUCKETS           该字段柱状图中bucket的数量
LAST_ANALYZED         最近一次分析表的时间
SAMPLE_SIZE           采样数据的数量(根据分析时的百分比得出的数量，
                      例如100%分析，该采样值就是行的数量)