Re: Huge optimization costs with 9.2

Execution plans would be helpful.
If optimizer_index_* parameters are unset, CBO tends to prefer full table access more, which doesn't seem to be your case (but exectution plans are needed in order to be sure in that).

As Mladen asked about histograms -> do you use bind variables in your queries? In 8i CBO can't peek bind variable values during hard parse, but in 9i it can, this feature in combination with histograms might cause execution plan change...

Did you do the analyzing in 9i exactly the same way and with same tools than in 8i?

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, October 01, 2003 7:09 PM

> 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.
> >
> > Joan
> >
> > Kirtikumar Deshpande wrote:
> > >
> > > Were tables/indexes anlayzed after the upgrade?
> > >
> > > - Kirti
> > >
> > > --- Jeff Landers <jlanders_at_convergys.com> wrote:
> > > > Hello All
> > > >
> > > > Version & OS:
> > > > Upgraded from 9.0.1.4 (Sun Solaris 2.8) to 9.2.0.3.
> > > >
> > > > Problem:
> > > > We've captured the sql text and optimization plans for critical sql
> > > > prior to upgrading to 9.2. After the upgrade we have noticed
> > > > that the cost associated with every sql statement is now HUGE
> > > > compared to its 9.0.1.4 counterpart. Per the statistics being
captured
> > > > via traces,
> > > > these statement are noticeably slower per execution.
> > > >
> > > > Anyone experiencing/experienced the same problem with 9.2?
> > > >
> > > > Thank you in advance.
> > > >
> > > >
> > >
> > > __________________________________
> > > Do you Yahoo!?
> > > Yahoo! SiteBuilder - Free, easy-to-use web site design software
> > > http://sitebuilder.yahoo.com
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Kirtikumar Deshpande
> > > INET: kirtikumar_deshpande_at_yahoo.com
> > >
> > > 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: 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.net
>
>
>
>
> Note:
> This message is for the named person's use only. It may contain
confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks.
> Any views expressed in this message are those of the individual sender,
except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
> INET: mladen_at_wangtrading.com
>
> 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: 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.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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: 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).