| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: for security patches - going to 9.2.0.4
Paula,
I had also bad experience when we upgraded one of our applications from 8.1.7 to 9.2.0.1 There were some queries that executed significantly slower then on 8.1.7. What we have done was:
1. We gathered system statistics with dbms_system.gather_system_stats. This helps CBO to know on what kind of HW configuration it is running on and also turns on the new cpu costing model. The result were better execution plans. Please read Metalink Note: 153761.1 about system stats. I had also replied to several questions regarding system stats on Metalink and I don't know for a case when gathering system stats caused performance degradation.
2. We have installed the latest patch 9.2.0.4 which among other bugs fixes also some optimizer bugs - some of them are still there ;) . This also solved other performance problems and things were again under control.
3. With manual setting system statistics (see the procedures in dbms_stats package to do that) one can achieve the same execution plans as on 8.1.7 version. If the mreadtim is 1.2 times higher than the sreadtim with a multiblockreadcount (mbrc) set to 8, then the I/O cost part will be equivalent to the Oracle8i I/O cost. I haven't tested this yet (there was no need to do that and besides I currently don't have access to proper environment to do that) but it sounds like setting the OPTIMIZER_INDEX_COST_ADJ in 8i to lower values then default.
4. There are several settings related to CBO's behavior that have changed the default value from false to true in 9iR2. Those are:
_b_tree_bitmap_plans = true
_complex_view_merging = true
_index_join_enabled = true
_new_initial_join_orders = true
_ordered_nested_loop = true
_push_join_predicate = true
_push_join_union_view = true
_table_scan_cost_plus_one = true
_unnest_subquery = true
We had several times problems with bitmap conversions for b-tree indexes, so we changed _b_tree_bitmap_plans back to false as it was in 8i. The changed plans without this feature were executing faster.
Regards, Joze
-----Original Message-----
From: Paula_Stankus@doh.state.fl.us [mailto:Paula_Stankus@doh.state.fl.us]
Sent: Friday, December 12, 2003 10:59 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: for security patches - going to 9.2.0.4
I was just wondering if setting compatible back to 8.1.7 for the CBO reasons was still necessary?
-----Original Message-----
From: ml-errors@fatcity.com [mailto:ml-errors@fatcity.com]On Behalf Of Paul Drake
Sent: Friday, December 12, 2003 4:49 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: for security patches - going to 9.2.0.4
Paula,
since you use the export utility - patch to 9.2.0.4.
a full export will throw an error in 9.2.0.3, on W2K and on RH 8.0.
regarding security, I haven't yet applied the 8.1.7.4.13 patchset (its not yet available) or the SSL-related issues in Oracle Security Alert #62. but the patchset 9.2.0.4.0 is still a pre-requisite for this, as the one-off patchset for 9.2.0.3.0 is not yet available.
As far as setting compatible back to 8.1.7, the key thing is not whether you have locally managed tablespaces, but a locally managed _SYSTEM_ tablespace. just wanted to make that clear.
Paul
Paula_Stankus@doh.state.fl.us wrote:
That is the case. I do have tablespaces that are locally managed - I did the migrate with compatible set to 8.1.7. I did startup the database. However, my system tablespace is dictionary managed - all others are not.
The migration worked. I have large databases and have been using Oracle's migration utility from 7 - 8 and 8 - 9. Yes, it is quirky and tricky but better than export/import on large databases.
-----Original Message-----
From: ml-errors@fatcity.com [mailto:ml-errors@fatcity.com]On Behalf Of Paul Drake
Sent: Thursday, December 11, 2003 7:04 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: for security patches - going to 9.2.0.4
Paula,
I hope that you are just confused.
AFAIK, if you have created a database with a locally managed system tablespace, that you cannot set compatible to anything lower than 9.0.1.
Ok, you can set it, but oracle will complain during instance startup and you won't have a database instance to attach to. But this might be a myth of mine, its awhile since I last read the upgrade/migration guide.
I can see setting the init.ora parameter
optimizer_features_enable = 8.1.7
if the 9.2 CBO acts quite differently from its older brother did, back in 8.1.7.
But compatible? I seriously doubt it.
If you migrated your db from 8.1.7 to 9.2 and the system tablespace is still dictionary managed - that is a completely different matter.
I've been lucky enough that most dbs were small enough to just use exp/imp and move data into a clean, newly created db.
Paul
Paula_Stankus@doh.state.fl.us wrote:
Guys,
I saved all of your writing including Todd Boxx, Richard Foote, Wolfgang... about issues with 9.2.0.4. We are currently on 9.2.0.3 and I understand (although have not hit it yet) that in this version we could get locks when building indexes. Also, that basically you need to set your compatible parameter to 8.1.7. On some databases we have compatible set to 9.2.0.0.
Question:
-any bugs/problems going to 9.2.0.4 and...
-should we really change compatible from 9.X to 8.1.7?????
We are currently migrating a large database to 9.X and I want to know if I should use the latest patchset 9.2.0.4 - for security and performance reasons?
Also, we have some dev/test databases where once they go into production performance could be an issue - should we change the compatible param. to 8.1.7 proactively????
Thanks,
Paula
--
Please! ! see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: INET: Paula_Stankus@doh.state.fl.us
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@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).
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joze Senegacnik INET: JozeS_at_hermes-plus.si 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).Received on Mon Dec 15 2003 - 05:09:25 CST
 |
 |