In this post, i have compiled a list of the common custom parameters set for the Oracle database in PeopleSoft applications, some of which can have a dramatic performance impact on the system. Some of the parameters mentioned are also applicable for general Non-PeopleSoft database applications. Please note that not all of them may apply in your current setup/version - i have included MOS Notes for reference and further reading/exploration.
alter system set "_unnest_subquery"=FALSE scope=spfile sid='*';
alter system set "fast_start_parallel_rollback"=false scope=spfile sid='*';
alter system set "_gby_hash_aggregation_enabled"=FALSE scope=spfile sid='*';
alter system set "_ignore_desc_in_index"=TRUE scope=spfile sid='*';
alter system set "_smu_debug_mode"=4 scope=spfile sid='*';
alter system set "optimizer_adaptive_features"=false scope=spfile sid='*';
alter system set "_optimizer_skip_scan_enabled"=false scope=spfile sid='*';
alter system set "nls_length_semantics"=char scope=spfile sid='*';
alter system set "cursor_sharing"=exact scope=spfile sid='*';
alter system reset "db_file_multiblock_read_count" scope=spfile sid='*';
alter system reset "_optimizer_cost_based_transformation" scope=spfile sid='*';
alter system reset "_complex_view_merging" scope=spfile sid='*';
MOS Notes/Documents:
alter system set "_unnest_subquery"=FALSE scope=spfile sid='*';
alter system set "fast_start_parallel_rollback"=false scope=spfile sid='*';
alter system set "_gby_hash_aggregation_enabled"=FALSE scope=spfile sid='*';
alter system set "_ignore_desc_in_index"=TRUE scope=spfile sid='*';
alter system set "_smu_debug_mode"=4 scope=spfile sid='*';
alter system set "optimizer_adaptive_features"=false scope=spfile sid='*';
alter system set "_optimizer_skip_scan_enabled"=false scope=spfile sid='*';
alter system set "nls_length_semantics"=char scope=spfile sid='*';
alter system set "cursor_sharing"=exact scope=spfile sid='*';
alter system reset "db_file_multiblock_read_count" scope=spfile sid='*';
alter system reset "_optimizer_cost_based_transformation" scope=spfile sid='*';
alter system reset "_complex_view_merging" scope=spfile sid='*';
MOS Notes/Documents:
- E-ORA Advice for the PeopleSoft Oracle DBA (Doc ID 1445965.1)
From 10gR2, HASH UNIQUE Operation Returns Results in UNSORTED ORDER by Default
(Doc ID 341838.1) - Required Interim Patches for the Oracle Database with PeopleSoft (Doc ID 1100831.1)
- Operating System, RDBMS & Additional Component Patches Required for Installation
PeopleTools - Master List (Doc ID 756571.1) - ANNOUNCEMENT: Deprecating the cursor_sharing = 'SIMILAR' setting. (Doc ID 1169017.1)
- E-ORA: How to convert an Oracle Non-Unicode Database to a Unicode Database (Doc ID 1437384.1)
- Init.ora Parameter "FAST_START_PARALLEL_ROLLBACK" Reference Note (Doc ID 68932.1)
- Removing Descending Indices for PeopleSoft Databases (Doc ID 1909646.1)
- Master Note: Troubleshooting guide for Automatic Undo Management (Doc ID 1579081.1)