22 February 2007
Peoplesoft performance on Oracle 10g -our experience
This note highlights the actions we did in order to get acceptable performance for Peoplesoft 8.49 on Oracle 10g R2 (10.2.0.3, Solaris 64-bit). Database size is 700G, with the growth of 15-20G per month.
Hopefully, this note might help some other DBAs with Peoplesoft apps.
Actions taken to stabilize the performance:
Hopefully, this note might help some other DBAs with Peoplesoft apps.
Actions taken to stabilize the performance:
- Switched the bind variables off : Metalink Note:387394.1
- Switched the density recalculation off : Metalink Note:338113.1
- Switched the default analyzing off
- Re-analyzde with COMPUTE where possible, with "FOR ALL COLUMNS SIZE AUTOSKEW"
- Saved the stable database statistics and stopped re-analyzing
- We only re-analyze the statistics when absolutely needed
- Collected the system statistics over the averagely loaded day
- Bug 5762712 * Switched the new subquery selectivity off
- Used /*+ NO_UNNEST */ hints for sub-queries, especially for the effective dating sub-queries
- Sometimes switched the unnesting off on the session level, especially for the batch jobs
- Occasionally used the LEADING hints
- Cached 150 cursors per session
- Introduced KEEP and RECYLING caches
- Two parameter found to cause wrong cost for queries that contain sub-queries:
- alter system set "_optimizer_correct_sq_selectivity"=false scope=both;
- alter system set "_optimizer_squ_bottomup"=false scope=both;
- This was fixed by logging a bug with Oracle. Will add bug number in the future. Bug was about the wrong costing for inequality joins, logged for 10.2.0.3 in y2007.
- Update in 2008: Oracle confirmed the bug 4422100.8 and we applied the fix
- Enhancement request raised about the way Oracle handles the effective dating subqueries (1 index pass suggested over 3 index passes). ER accepted.
- Enhancement request raised about the wrong join selectivity for the unique equal predicates
- Introduce regular analyzing only where and when needed
- Cursor pinning for the relevant cursors
- Apply Peoplesoft recommended patch bundles
- At the end of 2007, we set unnest_subquery=false on the instance level
- Increased SGA to 6G and PGA to 700M
- Increased KGL latches to 17
- Increased number of hash buckets considerably
