Re: Why does Optimizer relys on COST to produce the execution plan?
- From: The Human Fly <sjaffarhussain@xxxxxxxxx>
- To: Li-Shan Cheng <exriscer@xxxxxxxxx>
- Date: Mon, 2 Jan 2006 11:32:08 +0300
Li,
I know that we can't measure query performance on cost value. I have
come across many times where higher cost value response was more
faster than the lower cost value query. I also know what does reflects
optimizer to calculate the cost.
My question was that instaed of simply replying on cost value, oracle
also can look for other details like logical reads, and other stuff.
Regards,
Jaffar
On 1/2/06, Li-Shan Cheng <exriscer@xxxxxxxxx> wrote:
> Cost depends on tons of factors, parameter setting, object statistics,
> segment storage settings etc etc etc.
>
> I am pretty sure J. Lewis can give you much more hints. Or you can get a
> copy of his book which is entirely about CBO behaviour.
>
> regards
>
>
>
> --
> LSC
>
>
>
>
>
>
> On 1/2/06, The Human Fly <sjaffarhussain@xxxxxxxxx > 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.
> > I have not enabled the system level statistics. I question is that,
> > there is nothing related with cost value, then, why does oracle heavly
> > rely on this value to produce the 'best execution plan'?
> > I dont know whether the behaviour might change once we get the system
> > level stats.
> > 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?
> >
> > Thanks and once again a very happy new year.
> >
> >
> > --
> > 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."
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
--
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."
--
//www.freelists.org/webpage/oracle-l
Other related posts:
