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 


Other related posts: