| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> SOLVED: BIZARRE Query Optimization
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
> -----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. Received on Wed Nov 15 2000 - 13:50:26 CST
 |
 |