oracle自带的统计信息是怎么一回事？

Question

oracle数据库自带的统计信息如何使用？谢谢

Answer 1

数据库哪方面的统计?使用了空间统计?权限?
还是SQL执行时间,使用资源的 统计?

Answer 2

对整个数据库做统计信息，使得sql能够执行正确的执行计划，你们没做过吗？

Answer 3

其实LZ想说的是，CBO中sql的执行计划与oracle对象统计信息的收集有关，那oracle是怎么收集这些统计信息的，为什么有时候统计信息会不完整或者过时。其实这个问题我也一直很困扰，期待lx的回复。

Answer 4

偶是菜鸟,没有,偶做开发的,至今才学到语句级别优化,,,数据库只知道一点,例如调整HASH_AREA_SIZE之类的优化,关注..大家多讨论讨论,我也多学点

Answer 5

统计信息的作用你已经提到了，至于为什么会过时，很显然，你没有定时更新统计信息嘛。
更新统计信息是通过dbms_stats包来实现的，可以是表级、索引级、用户级、数据库级等，
比如你可以定时执行这条语句：exec dbms_stats.gather_database_stats();来更新当前数据库的全部信息。
()内参数可就多了，比如(options => 'gather stale');就是只更新过期的信息，数据库比较大时自然需要这个。
可以根据你本身数据库大小、数据变动程度来每晚或者每周甚至每半年来执行一次更新，当然要挑数据库负载低的时候，比如定时到凌晨。

Answer 6

感谢LS的回复，是不是可以这么说，在CBO中，如果执行查询之前已经通过dbms_stats包获得了对象的统计信息，那么执行效率会更高。还有，通过dbms_stats包获得的统计信息生命周期是多久呢？

Answer 7

在实际SQL的执行过程中，如表关联统计等等，会产生很多的关联路径和规则，至于ORACLE选择那一条是最好的，此时依赖于对表和索引的统计信息，然后由优化器计算出来，主要依赖于计算出来的逻辑读。统计信息时为辅助CBO（基于成本的优化器）提取正确的统计信息后，该优化器会根据统计信息，计算出SQL之际的执行路径那一条是为最优路径（这个并非），然后去执行SQL，它的前提是要求统计信息是较为准确的。
统计信息都在包：DBMS_STATS包内部，ORACLE 10G后提供了ANALYZE对表和索引对象有一些更为详细的统计，对于DBMS_STATS包常用的有：
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER, '表名称大写');--对表信息进行收集
  dbms_stats.gather_index_stats(USER, '索引名称大写');--对索引信息进行搜集
  dbms_stats.gather_schema_stats(USER);--对用户信息进行搜集
  dbms_stats.gather_database_stats();--对数据库进行搜集
END;
当对用户或者数据库进行收集的时候，它会遍历下面的每个索引和表，对于大量修改和删除操作的表，先进行磁盘压缩再收集是比较好的，通过视图：USER_TABLES 和 USER_INDEXES 相关的字段，可以看到收集的信息包含了数据的行数、占用的物理块、索引层数等等信息；在什么时候有必要去统计呢？因为统计耗时比较大，所以运行时尤其是白天是不要去统计的，这个可以自己决定，但是常规查询表所表的数量发生非常大的变化时，一定要统计，比如举个简单例子：你在一个表上创建了索引（普通索引），开始只有10条数据，此时统计了表和索引后，发现表占用的块只有1块，而索引也有一块。如果你查询数时查询了非索引字段，此时如果走索引还需要回表查询，CBO很可能会认为走全表扫描比走索引更快，因为全表扫描的磁盘读读是1，而走索引是2（这个并非绝对，看实际情况）；当有一天你的数据膨胀到1000万的时候，ORACLE得到的统计信息还是那些，它这个时候还是认为走全表扫描快，因为它根据你的统计信息计算出来的确是这样的，此时你的表查询就慢下来了。在ORACLE 7i以前，ORACLE采用RBO（基于规则的优化器），它会尝试各种路径去计算消耗，使用的是试探法，找到最优路径，一般都会走索引，一般会有驱动表，而且会根据实际情况，可以看出SQL的执行效率是很好的，但是试探法所消耗的性能是很大的，尤其是对于复杂的SQL；其实ORACLE 7已经引入CBO（基于成本的优化器），只不过在ORACLE 8i开始成熟起来，最大的特征就是不去试探，但是要依赖于统计信息进行计算，这样直接计算出最优路径出来，至ORACLE 8i后你如果不特殊指定，以及存在对象的统计信息，ORACLE默认是用CBO去完成执行计划。但是不要过于相信CBO，因为它做得还是比较傻的，第一要依赖于统计信息，有些时候即使有统计信息也未必是正确的，所以我们在适当的时候需要提示hint它怎么去执行达到稳定的执行效率（ORACLE的提示（hint）大概有几十个吧，常用的有七八个左右，可以在适当情况下使用），举个简单例子，如一个表已经被分页为20条数据，但是由于要获取扩展信息，需要和另一个大表关联（如几百万吧），此时如果你直接和他关联，但是这个分页的范围是动态参数传入进去的（以预编译方式），也就是对于ORACLE来说它不知道你第一个视图计算出来的结果只有20条了，此时它很可能让这两个表去做HASH JOIN，当然可能对于不算太大的表你感觉不出来，但是这个对系统开销的确很大，因为它需要对关联键值在临时表空间创建HASH表格，所以此时我们有建议的告诉ORACLE，我要用小表（只有20条）数据，通过它嵌套循环驱动大表，通过小表全盘扫描，让大表对应字段索引得到数据。关于CBO方面的弊病还很多（如一些高级的索引它不太认识），但是始终还是有解决办法，解决了就是你的经验，呵呵，其实原理上大致就这样，至于你如何灵活应用就是根据实际情况和你自己的发挥了。

Answer 8

一般来说基于CBO（基于成本的优化的单词缩写）的优化，核心是成本计算。
成本计算，就来源于SQL语句中所涉及的各对象的统计数据，如数据行数
平均行长度，占用的数据块数......表的统计特征。
然后，ORACLE通过这些统计数据估算各种执行SQL路径的成本，最终得到
一个最优化的SQL执行计划。

Answer 9

1） ORACLE有一个自带的定时任务，会收集统计信息。2） 假如没有统计信息，ORACLE会动态采样收集统计信息，这个收集的动作可能会耗点时间

Answer 10

 楼上第二条我是知道的，第一条我不太理解，是说oracle本身就会自动定时收集信息吗？意思是说我们根本就不需要考虑信息收集这回事是吗？

Answer 11

我看到 Oracle 10g 里面有创建 job 的，还可以设置调度时间。只要我们创建一个 job 让它执行一个 PL/SQL 块就可以了。下面这个是工具帮我生成的创建 Job 的 SQL 语句，在 10g 上可以跑，它创建了一个 Job 收集 TRAINING 和 JAZZ 两个 Schema 里面的对象统计信息，定时在每月26号晚上8点钟跑。另外直接调用 SQL 去手工启动 job。-- SQL for start job ---call sys.dbms_scheduler.RUN_JOB('TRAINING.GATHER_PFM_STAT');-------------- SQL for Job creation ----BEGIN
sys.dbms_scheduler.create_job
(
job_name => '"TRAINING"."GATHER_PFM_STAT"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
    dbms_output.put_line(to_char(sysdate,''yyyy-mm-dd HH24:MI:SS'') || '' : '' || ''Gathering JAZZ ...'');
    dbms_stats.gather_schema_stats(''JAZZ'');
    dbms_output.put_line(to_char(sysdate,''yyyy-mm-dd HH24:MI:SS'') || '' : '' || ''Gathering TRAINING ...'');
    dbms_stats.gather_schema_stats(''TRAINING''); 
end;',
repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=26;BYHOUR=20;BYMINUTE=0;BYSECOND=0',
start_date => systimestamp at time zone 'Asia/Shanghai',
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => FALSE
);
sys.dbms_scheduler.set_attribute(name=> '"TRAINING"."GATHER_PFM_STAT"', attribute => 'logging_level', value =>DBMS_SCHEDULER.LOGGING_FULL);
sys.dbms_scheduler.set_attribute(name=> '"TRAINING"."GATHER_PFM_STAT"', attribute => 'restartable', value =>TRUE);
sys.dbms_scheduler.enable( '"TRAINING"."GATHER_PFM_STAT"');
END;

Answer 12

你不需要使用这些统计信息
发出一个SQL语句的时候 ORACLE会自动根据这些统计信息选择成本最小的方式来执行当然 ORACLE也不是完全智能的 CBO也会有选择不恰当的时候 所以这个时候还是需要你进行人工的一些干预 比如通过查看执行计划 使用一些HINTS

Answer 13

10g就有自动收集统计信息了
select JOB_NAME,LAST_START_DATE from dba_scheduler_jobs;
但是这种自动收集不符合大部分系统的要求，所以还是需要用dbms_stats包手动收集的