| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> TOO Many 9.2 PARMS for PS?
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_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.
_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
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
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!!!!!!
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."
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed May 19 2004 - 21:41:28 CDT
 |
 |