From oracle-l-bounce@freelists.org Wed May 19 21:41:28 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i4K2f2x32206 for ; Wed, 19 May 2004 21:41:12 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i4K2eq632178 for ; Wed, 19 May 2004 21:41:02 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C8C3272C26B; Wed, 19 May 2004 21:29:35 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 23744-94; Wed, 19 May 2004 21:29:35 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0EC7472C4FB; Wed, 19 May 2004 21:29:35 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 19 May 2004 21:28:14 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1F47E72C468 for ; Wed, 19 May 2004 21:28:14 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 23744-64 for ; Wed, 19 May 2004 21:28:13 -0500 (EST) Received: from CWYSMTP01.acxiom.com (cwysmtp01.acxiom.com [198.160.100.127]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0B34772C25C for ; Wed, 19 May 2004 21:28:13 -0500 (EST) Received: from relay2.Corp.Acxiom.net (unverified [139.61.199.172]) by CWYSMTP01.acxiom.com (Content Technologies SMTPRS 4.3.12) with ESMTP id for ; Wed, 19 May 2004 21:44:17 -0500 Received: from dngmsc01.Corp.Acxiom.net (unverified [10.76.40.237]) by relay2.Corp.Acxiom.net (Content Technologies SMTPRS 4.3.12) with ESMTP id for ; Wed, 19 May 2004 21:44:16 -0500 Received: by dngmsc01.dg.acxiom.com with Internet Mail Service (5.5.2657.72) id ; Wed, 19 May 2004 21:44:16 -0500 Message-ID: From: Wolfson Larry - lwolfs To: oracle-l@freelists.org Subject: TOO Many 9.2 PARMS for PS? Date: Wed, 19 May 2004 21:41:45 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-type: text/plain X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 961 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: lawrence.wolfson@acxiom.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org A PeopleSoft client going from 8.1.7.4 too 9.2.0.4 ( 5.8 Generic_108528-24 sun4u sparc SUNW,Ultra-Enterprise) went into Metalink and decided we should add quite a few parameters too her initora file. She convinced her boss they need this too avoid returning wrong results from the database. My own PS staff said they were using just the hash_join_enable and _COMPLEX_VIEW_MERGING=FALSE on our 9.2.0.4 AIX system. They said they had some doc from PS. I asked client for PS recommendations on 9i initora parameters and she sent me item A) below. For 9.2.0.4 it only refers to: _COMPLEX_VIEW_MERGING=FALSE and O7_DICTIONARY_ACCESSIBILITY=TRUE From the HOTSOS PS Special Interest Group I was involved with they said for Oracle release 9.2 they were using _COMPLEX_VIEW_MERGING = FALSE and hash_join_enable = false # This was a hidden parameter but isn't in 9.2 For 9.2 we have hash_join_enable = TRUE. It should be worth testing as FALSE. For 9.2 we dropped event = 10235 Result of TAR _db_file_noncontig_mblock_read_count = 1 No history on this I know of _ignore_desc_in_index = TRUE Result of TAR In 8.1.7.4 we suggesting using _kgl_latch_count = 23 because of latch contention from Steve Adams _row_cache_cursors = 100 and were going to leave them in for 9.2 The row_cache_cursors came from the Oracle guy who wrote it. Some PS DBA or other consultant had set _use_column_stats_for_function = FALSE Client asked us to add _UNNEST_SUBQUERY = FALSE as she had evidence that this helped some of her code. The document she sent only suggests it be used through 9.2.0.3 However, one of the PS SIG DBAs said they were using it. In addition setting these events and hidden parameters was requested event = 10078 event = 10181 event = 38007 event = 38022 event = 38040 and _COST_EQUALITY_SEMI_JOIN = FALSE _UNNEST_SUBQUERY = FALSE _PRED_MOVE_AROUND = FALSE _PUSH_JOIN_PREDICATE = FALSE _PUSH_JOIN_UNION_VIEW = FALSE None of the people in the PS SIG nor my company (a PS AIX user, Oracle 9.2.0.4) is using these events or parameters Item B) Oracle response to my TAR about setting events and hidden parameters We tend to limit the changes we make that affect the CBO. Otherwise it's more difficult to ascertain which ones are helping and which ones are not. Client is understandably concerned about code not returning wrong results. I would have thought with all the testing PS does and all the PS users out there we would have been alerted to the necessity of acquiring these other parameters. OS did say that many of these events etc. were Application software specific (Ariba, Baan, Oracle Apps, SAP, Siebel, etc). You might ask if any can be excluded based on being for other than PS. At one point I mentioned I thought this was like getting medicine from a doctor for things you might get. Not the kind of doctor most people would want to go to. Now she's requested other DBAs add same parameters to other non PS databases we're supporting. We asked if we could get install instructions and were told the owner(?) couldn't find them on vendor's website. I've been trying get her own DBA (who's very good) to deal with this but he's swamped with other issues. I'm concerned some things could go South and we'll be spending a lot of time trying to figure out which/who is culprit. Suggestions? Thanks Larry !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!!!!!! A) Larry, This will hopefully answer this e-mail and another one when you ask for information from PeopleSoft. This is what I found: 1. Required INIT.ORA Parameters Its come to our attention that the Oracle 9.0.1.x.x CBO optimizer may produce inefficient plans. Oracle recommended that our customers set the following init.ora parameter for Oracle 9i versions 9.0.1.x.x: optimizer_features_enable=8.1.7 These optimizer issues have been addressed in Oracle 9.2.0.2.x Setting the optimizer_features_enable=8.1.7 parameter is not needed with 9.2.0.2 and beyond. The remaining reason for the necessity of the optimizer_features_enable=8.1.7 init.ora parameter was for an Upgrade issue discovered internally while testing the upgrade to PT 8.4x . The upgrade compare step was completing but the results of the step were incorrect. The reason for this was the underlying select criteria in some of the upgrade compare steps were not updating the correct number of rows. Although setting optimizer_features_enable=8.1.7 did address the Upgrade issue, there was a negative performance impact on the kinds of SQL utilized in the Tools Tree feature. Using PeopleSoft Trees would be very slow. The aforementioned specific Upgrade issue has been fixed in Oracle 9.2.0.4. For those customers still on Oracle 9.2.0.2 and 9.2.0.3, they should be adding the following parameter to their init.ora: _cost_equality_semi_join=false in lieu of setting optimizer_features_enable=8.1.7 in their init.ora in order to address the specific Upgrade issue. The following bug was reported by a customer using PT8.4x, Bug# 2948326 Query gives incorrect results when using MIN. MAX Function under CBO The complete description of the bug can be found on Oracle's metalink site. Reference Bug# 2948326 While the bugs are not yet resolved in Oracle 9i (9.0.1.3, 9.0.1.4 or 9.2.0.2, or 9.2.0.3) Oracle does recommend a workaround, by using the following init.ora parameter correct results will be produced: _unnest_subquery = false A related bug Bug# 2700474 and the base bug for Bug# 2948326 is also fixed by setting the parameter noted below. While the bugs are not yet resolved in Oracle 9i (9.0.1.3, 9.0.1.4 or 9.2.0.2, or 9.2.0.3), Oracle does recommend a workaround that does fix the problem we have encountered. Oracle indicates no side effects to implementing this workaround. Set the following init.ora parm as indicated below: _complex_view_merging=FALSE Bounce (shutdown and restart) the SID for the parm to take effect. According to Oracle the base bug# 2700474 has been fixed in Oracle 9.2.0.4.0, however the following bug(s) still require using the _complex_view_merging=FALSE parameter in the init.ora "ORA-3113 and ORA-7445 on certain SQL querys" Bug# 2415893. The complete description of the bug can be found on Oracle's metalink site. Reference Bug# 2415893 This bug is not yet resolved on Oracle 9i (9.0.1.3, 9.0.1.4 or 9.2.0.2, or 9.2.0.3), Oracle recommends the following workaround that does fix the problem: Set the following init.ora parm as indicated below: _complex_view_merging=FALSE Bounce (shutdown and restart) the SID for the parm to take effect. The following issue was found while testing CRM on PT8.4x The O7_DICTIONARY_ACCESSIBILITY initialization parameter controls restrictions on system privileges when you migrate from Oracle7 to Oracle8i and higher releases. If the parameter is set to TRUE, access to objects in the SYS schema is allowed (Oracle7 behavior). If this parameter is set to FALSE, system privileges that allow access to objects in "any schema" do not allow access to objects in SYS schema The default for O7_DICTIONARY_ACCESSIBILITY is FALSE. This is different from Oracle versions prior to 9i. Beginning with PeopleTools 8.4 Oracle triggers were utilized in the CRM applications. The triggers work fine on Oracle8i but were failing on Oracle9i. For these triggers to work on Oracle9i, we need to revert Oracle database catalog access behaviour to Oracle 8.1.7 or earlier. This is accomplished by setting the following init.ora parameter: O7_DICTIONARY_ACCESSIBILITY=TRUE !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!!!!!!!!!!!! B) ORACLE RESPONSE:"Larry, The official policy is that you don't set any event unless we specifically tell you. No need for management's comments. They is stated on all internal documents produced by development concerning events. Sometimes events give the same behavior as the related patch, but more often the event just flips some code paths off and restrict the optimization choices. I'd rather fix the usage of the code path than take it away. Mostly the newer optimizations available in the later releases give faster execution times and usually for customers using the latest features that are more difficult to develop applications for. In short, the optimization techniques that have been provided in later releases have been provided for a specific reason. I'd rather get it working than to flip the "off switch". So, I hope you guys have plans to test as much as you can. If so, and you see some queries that will specifically use optimization techniques that are affected by some of the associated bugs, then by all means use the event or even better, apply that patch. But, by then you have already run the queries and probably know whether or not you are affected by the behavior. Most Oracle bugs only affect a specific group of Oracle customers. For example, just because you are using IN-LISTS doesn't mean that you will get affected by every single INLIST bug on your release/patchset. My recommendation would be to apply the 9.2.0.5 patchset. Some very important fixes are included as well as some important diagnostics improvements. Then test as much as you can. If you start to see WRONG RESULTS you can look at the Execution Plan and make an educated guess as to which event/parameter applies. Then test it/them. If none, work, then file an ITar and we'll either have you try some more events/parameters or file a new bug with the already completed testcase that you have. Or if you find one that DOES work, but the bug has no available fix then file an ITAR." ********************************************************************** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------