Perhaps using an index isn't the best approach? When you say the queries
run remarkably well how do you define this? If you are running the query
in TOAD, for example, you might be returning just the first page of results
where CBO has chosen an all rows approach - slower to start but quicker
overall.
I'm not saying this is your problem but it's something to be careful of -
I've been bitten several times in the past when trying to tune something
quickly... As recently as last week. It looked great until I realised it
was only the beginning of the result.
Regards,
Mark.
Sai Selvaganesan
[EMAIL PROTECTED]To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
obal.netcc:
Sent by: Subject: stats and query plans
[EMAIL PROTECTED]
.com
11/08/2003 05:54
Please respond to
ORACLE-L
hi
a very basic question
i have faced this issue a few times in the last couple of weeks. we have a
few queries that seem to run remarkable well in rule based optimisation
than in choose based optimisation. the queries, with all the object stats
collected upto date, never returns a result while when i deleted all stats
it returns a result in no time.The plans are diffrent and w/o stats indexes
are being used.
when this happens in the same system what are the things i should check. is
it not recommended that oracle CBO is the best way to go in performance
tuning and query optimisations.then how come rule based turns out to be
efficient.surely i havent generated 10053 trace and gone through it but it
doesnt make sense why CBO falters. Logically speaking when rule based path
gives a result,CBO should choose that explain plan.
can someone throw some light on this
thanks
sai
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for delivery of
the message to such person), you may not copy or deliver this message to anyone.
In such a case, you should destroy this message and kindly notify the sender by reply
e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999.
Please advise immediately if you or your employer does not consent to Internet e-mail
for messages of this kind.
Opinions, conclusions and other information in this message that do not relate to the
official business of Transurban Infrastructure Developments Limited and CityLink
Melbourne Limited shall be understood as neither given nor endorsed by them.
--
Please see the official ORACLE-L FAQ: http
