Rule Based Optimization

[Terry Coccoli]

We have a Siebel implementation and for one query that was taking a long
time to run I asked a DBA to evaluate the explain plan. I noticed that
he chose to evaluate the Rule based optimization. I asked him why and
he said that the Siebel application defaults to Rule based optimization.

Not that I don't trust him, but I had the impression that Cost based was
the way to go.

Is it possible to set up a database schema so that it the optimizer
always uses Rule based rather than Cost ? And if anyone knows Siebel,
is this the actual default ?
Thanks.

[Jim Kennedy]

"Terry Coccoli" <re*****@ifneeded.com> wrote in message
news:Gi***********************@news.easynews.com.. .

We have a Siebel implementation and for one query that was taking a long
time to run I asked a DBA to evaluate the explain plan. I noticed that
he chose to evaluate the Rule based optimization. I asked him why and
he said that the Siebel application defaults to Rule based optimization.

Not that I don't trust him, but I had the impression that Cost based was
the way to go.

Is it possible to set up a database schema so that it the optimizer
always uses Rule based rather than Cost ? And if anyone knows Siebel,
is this the actual default ?
Thanks.

Siebel is rule based.
They are about 5 years behind in taking advantage of the database technology
that the system runs on. Yes, cost is the way to go but not with Siebel.
They won't support you if you use rule based. (which means you can't do a
whole bunch of things) One thing that you can do to get around this is that
if you have a specific query that is better under cost based you can use a
stored outline. (get Thomas Kyte's book)
Jim

[Terry Coccoli]

Jim Kennedy wrote:

"Terry Coccoli" <re*****@ifneeded.com> wrote in message
news:Gi***********************@news.easynews.com.. .

We have a Siebel implementation and for one query that was taking a long
time to run I asked a DBA to evaluate the explain plan. I noticed that
he chose to evaluate the Rule based optimization. I asked him why and
he said that the Siebel application defaults to Rule based optimization.

Not that I don't trust him, but I had the impression that Cost based was
the way to go.

Is it possible to set up a database schema so that it the optimizer
always uses Rule based rather than Cost ? And if anyone knows Siebel,
is this the actual default ?
Thanks.

Siebel is rule based.
They are about 5 years behind in taking advantage of the database technology
that the system runs on. Yes, cost is the way to go but not with Siebel.
They won't support you if you use rule based. (which means you can't do a
whole bunch of things) One thing that you can do to get around this is that
if you have a specific query that is better under cost based you can use a
stored outline. (get Thomas Kyte's book)
Jim

What happens if you throw in a hint like FIRST_ROWS? Would the
optimizer accept the hint ?

[Jim Kennedy]

"Terry Coccoli" <re*****@ifneeded.com> wrote in message
news:nu***********************@news.easynews.com.. .

Jim Kennedy wrote:

"Terry Coccoli" <re*****@ifneeded.com> wrote in message
news:Gi***********************@news.easynews.com.. .

We have a Siebel implementation and for one query that was taking a long
time to run I asked a DBA to evaluate the explain plan. I noticed that
he chose to evaluate the Rule based optimization. I asked him why and
he said that the Siebel application defaults to Rule based optimization.

Not that I don't trust him, but I had the impression that Cost based was
the way to go.

Is it possible to set up a database schema so that it the optimizer
always uses Rule based rather than Cost ? And if anyone knows Siebel,
is this the actual default ?
Thanks.

Siebel is rule based.
They are about 5 years behind in taking advantage of the database technology that the system runs on. Yes, cost is the way to go but not with Siebel. They won't support you if you use rule based. (which means you can't do a whole bunch of things) One thing that you can do to get around this is that if you have a specific query that is better under cost based you can use a stored outline. (get Thomas Kyte's book)
Jim

What happens if you throw in a hint like FIRST_ROWS? Would the
optimizer accept the hint ?

No, because it is rule based. Also you don't have access to edit the SQL
Siebel generates for the system. You can however use a logon trigger and
set up stored outlines. See Tom Kyte's book for excellent examples of how
to do this. (Expert 1 on 1 Oracle)

Jim

[Daniel Roy]

> What happens if you throw in a hint like FIRST_ROWS? Would the

optimizer accept the hint ?

If you throw any hint to a query (except "RULE"), cost-based
optimizing is triggered. In that case, since you probably don't have
any stats for your tables, Oracle will try to make up these stats by
itself, using criteria such as number of extents used by a table (to
estimate its size), ... Oracle usually does a decent job at making up
these stats (from what I've seen), but of course it would be much
better to have real up-to-date stats if you intend to use CBO for some
queries. If you decide to go that route (use hints and gather the
stats), make sure that OPTIMIZER_MODE (for the instance running
Siebel) is RULE (and not CHOOSE), or otherwise CBO will be used for
all the queries (including the ones from the Siebel application)
running against the database, and your Siebel TAM will slap your
fingers.

Daniel Roy
IBM
Siebel/Oracle Consultant

[Terry Coccoli]

Daniel Roy wrote:

What happens if you throw in a hint like FIRST_ROWS? Would the
optimizer accept the hint ?

If you throw any hint to a query (except "RULE"), cost-based
optimizing is triggered. In that case, since you probably don't have
any stats for your tables, Oracle will try to make up these stats by
itself, using criteria such as number of extents used by a table (to
estimate its size), ... Oracle usually does a decent job at making up
these stats (from what I've seen), but of course it would be much
better to have real up-to-date stats if you intend to use CBO for some
queries. If you decide to go that route (use hints and gather the
stats), make sure that OPTIMIZER_MODE (for the instance running
Siebel) is RULE (and not CHOOSE), or otherwise CBO will be used for
all the queries (including the ones from the Siebel application)
running against the database, and your Siebel TAM will slap your
fingers.

Daniel Roy
IBM
Siebel/Oracle Consultant

Thanks for the headsup, Roy. I think you've given me a couple of
questions that I need to discuss with the DBA.