yeah ... and setting _unnest_subquery=true also gave WRONG results when you used a aggregate function in a sub-query without a group by clause.
That was a bug ...
Raj
-----Original Message-----
From: Wolfgang Breitling [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 01, 2003 12:55 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Huge optimization costs with 9.2
One of the undocumented init.ora parameters that changed from 8 to 9 is
"_UNNEST_SUBQUERY" (from false to true). You could try if that is the
culprit. Of course, since it is an undocumented parameter, get the blessing
from Oracle support before using it in a production database.
At 10:09 AM 10/1/2003, you wrote:
>Joan, what is the difference in the plans? What specific feature
>made the difference? Are the values of
>optimizer_index_cost_adj and optimizer_index_caching same on both
>versions? How about histograms? What is with
>db_file_multiblock_read_count,sort_area_size and hash_area_size? Is
>everything same as in 8i? May be setting of those parameters can be
>tweaked to your benefit?
>
>On Wed, 2003-10-01 at 11:54, Joan Hsieh wrote:
> > Kirti,
> >
> > I had upgraded a database from 8.1.7.4 to 9.2.0.4. Before the upgrade,
> > performance is good. After upgrade, one query run time from 2 min to 12
> > hours. Of course, I re-analyzed all tables and indexes. The explain plan
> > changed from hash join to nested-loop. All the parameters are same. So I
> > have to put optimized_feature_enable=8.1.7 to make run normal as usual.
> > I hate to disable the new feature, but no choose.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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 message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************2
