RE: Is important to GATHER_SYSTEM_STATS???
- From: ryan_gaffuri@xxxxxxxxxxx
- To: SMILEYJ@xxxxxxxx, "'jreyes@xxxxxxxxxxxxxxxx'" <jreyes@xxxxxxxxxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Fri, 22 Oct 2004 22:49:32 +0000
is anyone noticing an improving in the percentage of queries plans the CBO gets right with this? With decent optimizer_index_cost_adj, optimizer_index_Caching, and db_file_multiblock_read_Count, in verion 9.2 oracle appears to have a 99.5% accuracy in plan generation even if you take into consideration the use of bind variables and oracles inability to use histograms with them. . not much to improve on if you use system stats.... We implemented it here and we notice no improvement whatsoever. > In a recent case, I was able to smooth the transition from nested loops to > hash joins by setting system stats to appropriate values. Prior to this, > the CBO would switch from NL to hash joins much too early, resulting in a > big discontinuity in the response time curve for certain queries as the > number of rows processed increased. > > Be sure to gather the stats during a load that represents the target state -- http://www.freelists.org/webpage/oracle-l