Can you help me understand this?

Hello,
I've been "playing" on a personal machine of mine trying to test various things to gain a better understanding of how oracle works. My question has to do with statistics and the CBO. Are there any guidelines to follow for how to select a good sampling level when generating statistics initially?

I am finding varied results with different levels of sampling. In some cases I get better results (more efficient and quicker sql processing) when I select a lower sampling level?

Does that make sense? Why is that possible? Is it true that generating statistics must be a highly trial and error process or is there a more logical formula/approach to follow for selecting the sampling level?

Also, can anyone explain "Optimizer Cost" to me.

I was running stats on 2 standard non-partitioned tables and then tracing the behavior of a query after generating the statistics.

originally, I started out with just "analyze table ... estimate statistics" ...accepting the default sampling level.

I then increased to "analyze table ... estimate statistics sample 10 percent"

and "analyze table ... estimate statistics sample 30 percent"

after the 2nd two, I ran the query and got better execution plans than after the first statistics gathering.

The 10 percent run and the 30 percent run both produced identical execution plans. However, after the 30 percent run, I noticed that the overall optimizer cost of the query had
increased from 1257 to 1405. If I am interpreting this correctly, I am getting better CBO behavior with a lower sampling rate of 10 percent???

I'm just trying to get a better understanding of CBO and statistics... I'd appreciate any tips. (and if you need specific examples (cardinalities of tables, indexes etc...), let me know and I will post it)
Thanks,
Gavin

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Aug 17 2000 - 19:25:06 CDT