BIZARRE Query Optimization

From: Steven Monaghan <MonaghaS_at_mscdirect.com>
Date: Wed, 15 Nov 2000 10:24:21 -0500
Message-Id: <10681.122116@fatcity.com>

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 Received on Wed Nov 15 2000 - 09:24:21 CST