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 <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED] 10/03/2003 08:54 AM
|
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]>
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: [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).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Richard Foote
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).
