Re: SOLVED: BIZARRE Query Optimization

I once had a teacher who said 'When you're absolutely sure of all the facts and the equation still doesn't work, then one of the facts that you're absolutely sure of is wrong.'

:-)

Yosi

MonaghaS_at_mscdirect.com wrote:

> Sorry to bother the list with this one. It was my own stupidity!
>
> One of the columns in the where clause of the query was a varchar2
> containing numeric data. I declared the variable as a varchar2 but entered
> the data directly as a number. Oracle, when trying to do the equality
> check, opted to implicitly convert the data in the table to a number. This
> gave it no index to use and it went for a full table scan. Doing a to_char
> on the number in the where clause fixed it right away!
>
> Thanks to everyone who answered my plea for help!
> Steve
> -------------------------------------
> Steven Monaghan
> Oracle DBA
> MSC Industrial Direct Co., Inc.
> Melville, NY
> MonaghaS_at_mscdirect.com
> http://www.mscdirect.com
> -------------------------------------
>
> > -----Original Message-----
> > From: John Dailey [mailto:John.Dailey_at_concept-solutions.com]
> > Sent: Wednesday, November 15, 2000 2:16 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: BIZARRE Query Optimization
> >
> >
> > Steven, do you have a hint in your query? I ran in to
> > something similar to
> > this here recently... we are using RULE and the developer
> > used FIRST_ROWS...
> > query took 20 mins and tkprof output looked like a train
> > wreck. removed the
> > hint and reduced to 1 1/2 mins. Just a thought. Metalink
> > notes said if
> > using certain hints automatically forces CBO which could
> > explain odd access
> > you are experiencing....
> >
> > John Dailey, MCSE/MCP+I
> > Consultant
> > Concept Solutions, LLC
> >
> > Concept Solutions, LLC
> > *Your Business Intelligence Partner*
> > www.concept-solutions.com
> >
> >
> > -----Original Message-----
> > Monaghan
> > Sent: Wednesday, November 15, 2000 10:21 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > I have an identical query run using 3 scenarios.
> > 1 - using bind variables.
> > 2 - entering the values directly into the query.
> > 3 - running the query from a stored procedure using IN parameters.
> >
> > We are using 8.0.5.1.0 with optimizer set to CHOOSE but no statistics
> > (defaults to RULE) running on E450 SunOS 5.6. All tests run
> > from SQL*Plus.
> >
> > Oracle support says, in response to a TAR about this issue,
> > and I quote:
> > "The fact that the plans are being generated by the RBO and
> > are different
> > does not make sense as the RBO is not interested in whether
> > littorals or
> > bind variables are being used when the explain plan is being
> > generated.
> > Unfortunately, since you are running on 8.0.5.x which was
> > desupported back
> > in June, I cannot proceed any further in regards to
> > requesting assistance
> > from development in regards to why the plans are different."
> >
> > Can anyone offer me some insight or another place to look for
> > resolution? I
> > cannot analyze the tables and switch to CBO, because when I tried,
> > performance in general went down the tubes, so I am at a total loss!
> >
> >
> > Thanks,
> > Steve Monaghan
> >
> > PS: Here are the TKPROF outputs
> >
> > Scenario 1-
> > call count cpu elapsed disk query current
> > rows
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > Parse 1 0.00 0.00 0 0 0
> > 0
> > Execute 1 0.00 0.00 0 0 0
> > 0
> > Fetch 12 1.16 1.18 263 28325 0
> > 152
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > total 14 1.16 1.18 263 28325 0
> > 152
> >
> > Scenario 2-
> > call count cpu elapsed disk query current
> > rows
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > Parse 1 0.00 0.00 0 0 0
> > 0
> > Execute 2 0.00 0.00 0 0 0
> > 0
> > Fetch 12 8.71 8.85 15278 17946 1485
> > 152
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > total 15 8.71 8.85 15278 17946 1485
> > 152
> >
> > Scenario 3-
> > call count cpu elapsed disk query current
> > rows
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > Parse 1 0.00 0.00 0 0 0
> > 0
> > Execute 2 0.00 0.00 0 0 0
> > 0
> > Fetch 153 7.75 7.87 15040 17946 1489
> > 152
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > total 156 7.75 7.87 15040 17946 1489
> > 152
> >
> > -------------------------------------
> > Steven Monaghan
> > Oracle DBA
> > MSC Industrial Direct Co., Inc.
> > Melville, NY
> > MonaghaS_at_mscdirect.com
> > http://www.mscdirect.com
> > -------------------------------------
> >
> >
> > This e-mail is intended for the use of the addressee(s) only
> > and may contain
> > privileged, confidential, or proprietary information that is
> > exempt from
> > disclosure under law. If you are not the intended recipient,
> > please do not
> > read, copy, use or disclose the contents of this
> > communication to others.
> > Please notify the sender that you have received this e-mail
> > in error by
> > replying to the e-mail. Please then delete the e-mail and destroy any
> > copies of it. Thank you.
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Steven Monaghan
> > INET: MonaghaS_at_mscdirect.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: John Dailey
> > INET: John.Dailey_at_concept-solutions.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
> This e-mail is intended for the use of the addressee(s) only and may contain
> privileged, confidential, or proprietary information that is exempt from
> disclosure under law. If you are not the intended recipient, please do not
> read, copy, use or disclose the contents of this communication to others.
> Please notify the sender that you have received this e-mail in error by
> replying to the e-mail. Please then delete the e-mail and destroy any
> copies of it. Thank you.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Steven Monaghan
> INET: MonaghaS_at_mscdirect.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Wed Nov 15 2000 - 15:28:05 CST