Re: Why does Optimizer relys on COST to produce the execution plan?

Breitling,

Thanks for your time and advice.

To give you a little brief, this query involves two tables, and one of table has 87 distinct values and other table has around 1 million records. The big table column which is involving in the query (driven column in the composite query) has the following statistics:

Num Distinct : 74066
Density : .000013501
num_buckets : 1 ( I didn't collect histograms, since its has a lot of distinct values).
sample size : 20%

Table Num Rows : 968892

The query is like this:

select * from testv1
where casm_pac_nb = 0500013289 and stkm_stK_cd = 7010

testv1 is a view involving the following sql.

select column,column2 from small_table,big_table where big_tabl_index_column = small_table_unique_column.

Can you tell me what is the best value on the big table column for histogram size?

Regards,
Jaffar
On 1/2/06, Wolfgang Breitling wrote:
> The optimizer needs some criterion to decide which plan to choose for
> a SQL. The RBO uses a sequence of rules and chooses the first rule in
> the sequence that applies (with some tie-breaking rules). The CBO
> uses an estimate of the resources needed to process each plan. This
> resource estimate is the "cost". In the absence of system statistics
> that cost is the estimate of logical IO that it will take to get the
> result set. With system statistics, the cpu cost of the individual
> plan steps weighs in as well.
> The "cost", i.e. estimate of LIO required, is based on the underlying
> object statistics, the predicates and some built-in assumptions/heuristics.
> It is my firm conviction that the optimizer chooses the best plan
> possible IF it gets the estimates right.
> When you force a particular access path through hints you distort the
> meaning of the cost. Of course, the hinted plan will have a higher
> cost or else the CBO would have chosen it in the first place.
> The question to ask is not what other measure the CBO should use to
> base its decision on, but what led it to miscalculate the LIO "costs"
> of the plans. There are several possibilities:
> a) The cost of an FTS was artificially low due to a high
> db_file_multiblock_read_count.
> b) The cost of an FTS was undervalued because of the missing cpu
> component. As you observed (and as Jonathan showed in his book), the
> cpu cost of an FTS can be substantial.
> c) The cost of the index access was overvalued because of an
> incorrect rowcount estimate. Maybe a histogram on the predicate
> column might help
> ...
> Find - and fix - the cause for the optimizer's wrong estimate and the
> optimizer will choose the correct plan without a hint.
>
> At 11:32 PM 1/1/2006, The Human Fly wrote:
> >Hello list,
> >
> >Wishing you a very happy and prosperous new year.
> >
> >Yesterday, I was happend to tune a query and got surprised the way
> >Optimizer behaves. I think oracle should reconsidered about the
> >Optimizer behaviour, which relys on cost value, as of 9207, to produce
> >the explain plan. Initially when I run the query, oracle was doing
> >FTS of two tables and response time was 0.08, there is a composite
> >index and the column which I am using in the query is the leading
> >column in the composite index. I thought Optimizer would choose INDEX
> >SKIP SCAN, when I force to using the composite index, the query
> >response time also was 0.08. But, the cost between the two explain
> >plans are double. The query which was does FTS, cost was 1040 and the
> >query which was using INDEX hint cost 3564. But, the big difference
> >was logical reads. Query with FTS doing 10 thousand logical reads and
> >query with INDEX hint was doing 3thousand logical reads. The
> >difference is 7 thousand logical reads. I have also compared the CPU
> >used by these query and the difference was around 70% between these
> >two queries.
>
> Why an index skip scan if the leading column is in the predicates?
> Unless, of course, it's not an equal predicate.
>
> [snip]
>
> >By the way, how do we count the value coming from v$sysstat for CPU
> >used by this session'? How do I calculate this value? Is this CPU
> >cycles or what?
>
> That value is centiseconds.
>
>
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> http://www.centrexcc.com
>
>

--
Best Regards,
Syed Jaffar Hussain
OCP 8i & 9i DBA,
Banque Saudi Fransi,
Saudi Arabia
http://jaffardba.blogspot.com/
----------------------------------------------------------------------------------
"Winners don't do different things. They do things differently."
--
http://www.freelists.org/webpage/oracle-l