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
Received on Fri Oct 22 2004 - 17:45:01 CDT
