工作量信息是否该收集

26856649

前些天看到工作量信息统计，经查发现收集前cost计算及plan都不大准确  11.1.0.6
语句如下：f.familyid 为主键，fm.familyid上有索引
select count(f.familycode),count(fm.membername)
  from family f
inner join familymemberinfor fm on fm.familyid = f.familyid
e_rows a_rows相差很多，且index scan变成了full table），收集后e_rows与a_rows一致了且join走了索引。


但今天谈论工作量信息时，一ocm认为不该收集工作量信息，认为收集了后会变慢。

那到底该不该收集呢？请指教。

Maclean Liu(刘相兵

你说的 工作量信息  是指什么？  是指 System Statistics ？　　还是指Workload Statistics？

26856649

Workload Statistics
declare
  v_fse number;
begin
  dbms_stats.gather_system_stats(gathering_mode => 'start');
  /*此处运行大查询*/
  dbms_stats.gather_system_stats(gathering_mode => 'stop');
end;

fsm

得看搜集的时间长短和搜集时段是否能代表系统的一般负载状况吧。

Maclean Liu(刘相兵

In Oracle 10g, Oracle uses noworkload statistics and the CPU cost model by default.   

10g 中 oracle默认收集 noworkload 统计信息

Workload statistics, introduced in Oracle 9i, gather single and multiblock read times, mbrc, CPU speed (cpuspeed), maximum system throughput, and average slave throughput. The sreadtim, mreadtim, and mbrc are computed by comparing the number of physical sequential and random reads between two points in time from the beginning to the end of a workload. These values are implemented through counters that change when the buffer cache completes synchronous read requests. Since the counters are in the buffer cache, they include not only I/O delays, but also waits related to latch contention and task switching. Workload statistics thus depend on the activity the system had during the workload window. If system is I/O bound—both latch contention and I/O throughput—it will be reflected in the statistics and will therefore promote a less I/O intensive plan after the statistics are used. Furthermore, workload statistics gathering does not generate additional overhead.


workload statistics 受到系统实际活动的影响和 收集的负载窗口， oracle可能受到workload statistics的影响 而更趋向于执行计划中避免昂贵的I/O操作。

一般我们不需要手动去收集 workload/noworkload 的 statistics， 除非你的环境中 i/O 和cpu 的配置异常高。