Oracle-L: Re: Huge optimization costs with 9.2

From: <Jared.Still_at_radisys.com>
Date: Fri, 03 Oct 2003 11:34:25 -0800
Message-ID: <F001.005D1FC7.20031003113425@fatcity.com>

Here's a script to show differences in default parameter values between releases.

Kind of interesting results.

Jared

parmdiff2.sql
show init parms that differ between instances
a parameter is displayed only if it appears
in both database, and is set to default
--
the purpose of this is to determine if the default
value for a parameter has changed between database
releases. Undocumented parameters are included.
create_xviews.sql needs to be run first
for this to work unless you logon as SYS

col cinstance1 noprint new_value uinstance1 col cinstance2 noprint new_value uinstance2

prompt
prompt Parmdif2f - Compare init.ora parms for 2 Oracle Instances

prompt             Parameters are displayed only if they appear in
prompt             both instances and are set to default values
prompt
prompt             This helps explain database behavior changes
prompt             between releases

prompt

prompt Instance 1:
set feed off term off
select '&1' cinstance1 from dual;

set term on
prompt
prompt Instance 2:
set term off
select '&2' cinstance2 from dual;

set term on feed on

set line 110

break on name skip 1

col name format a40
col value format a40
col database format a8 head 'DB'

spool parmdiff2.txt

basic parm query
--select a.ksppinm name, b.ksppstvl value
--from x$ksppi a, x$ksppsv b
--where a.indx = b.indx

select p.name, p.value, p.database
from (
(

        select a.ksppinm name, b.ksppstvl value, '&&uinstance1' database
        from x$ksppi@&&uinstance1 a, x$ksppsv@&&uinstance1 b
        where a.indx = b.indx
        minus
        select a.ksppinm name, b.ksppstvl value, '&&uinstance1' database
        from x$ksppi@&&uinstance2 a, x$ksppsv@&&uinstance2 b
        where a.indx = b.indx

)
union all
(

        select a.ksppinm name, b.ksppstvl value, '&&uinstance2' database
        from x$ksppi@&&uinstance2 a, x$ksppsv@&&uinstance2 b
        where a.indx = b.indx
        minus
        select a.ksppinm name, b.ksppstvl value, '&&uinstance2' database
        from x$ksppi@&&uinstance1 a, x$ksppsv@&&uinstance1 b
        where a.indx = b.indx

)
) p,
-- this query used to limit output to parameters

that both instances have in common, and are
set to default values ( select name from ( select a.ksppinm name, b.ksppstvl value, '&&uinstance1' database from x$ksppi@&&uinstance1 a, x$ksppsv@&&uinstance1 b where a.indx = b.indx
- get only defaults that have changed
- if ksppstdf is TRUE, then it is a default value and b.ksppstdf = 'TRUE' union select a.ksppinm name, b.ksppstvl value, '&&uinstance2' database from x$ksppi@&&uinstance2 a, x$ksppsv@&&uinstance2 b where a.indx = b.indx and b.ksppstdf = 'TRUE' ) p3 group by name having count(*) > 1 ) p2 where p.name = p2.name order by name, value, database /

spool off

undef 1 2

Richard Foote <richard.foote_at_bigpond.com> Sent by: ml-errors_at_fatcity.com
10/03/2003 08:54 AM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Re: Huge optimization costs with 9.2

We has problems with another undocumented parameter that changed when we migrated to 9i in August last year. _B_TREE_BITMAP_PLANS change from false to true and caused a number of issues with sub-optimal execution plans.

Another possible trap for the unwary ...

Cheers

Richard
----- Original Message -----

To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, October 02, 2003 2:54 AM

> 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: breitliw_at_centrexcc.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: Richard Foote
INET: richard.foote_at_bigpond.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:
INET: Jared.Still_at_radisys.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