| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Steven
Monaghan
Sent: Wednesday, November 15, 2000 10:21 AM
To: Multiple recipients of list ORACLE-L
Subject: BIZARRE Query Optimization
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
------- ------ -------- ---------- ---------- ---------- ----------
Scenario 2-
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Scenario 3-
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
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 Liststo: 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 Received on Wed Nov 15 2000 - 13:12:31 CST
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |