BDN - Banner Developer Network

Attached is information from my Summit presentation in regards to tuning Banner 7 on Oracle 10g. I simply created an rtf from the Powerpoint slides. If you would like a copy of our stats script or have questions, please email me at sharden@uillinois.edu

Taming Banner 7 on Oracle 10g
Presented by: Scott Harden
University of Illinois

Introduction
• Purpose:
To share performance lessons learned after upgrading to Banner 7 and Oracle 10g leading to our best performance ever!

• Benefits:
• Learn Top 5 things that helped our performance
• Avoid the same problems we hit
• Tools to assist you in your tuning efforts
• Tuning tips particularly for large school volume

Topics of Discussion
• How large are we?
• Results of our tuning efforts
• Top 5 solutions
• Problems we hit
• Tools to help you tame the beast

How Large Are We?
How Large Are We?
• 70,000 students
• 40,000 employees
• 3 campuses
• Nearly 10,000 potential forms users
• Max. connections: 2635, Median: 1500
• Max. self service connections: 1147
• SUN F15K - 44 CPUs, 88 GB memory
• Database size: 330 GB
• Growth: ~250 MB/day
• FGBTRND over 140 million rows
• NHRDIST over 72 million rows
• SORLCUR over 4 million rows
• Live for roughly 3.5 years now

Results of Our Tuning Efforts
Results of Our Tuning Efforts
45% reduction in CPU load

62% reduction in logical reads/sec

50% increase in max transactions/sec

Top 5 Solutions
Top 5 Solutions
#1 Registration Class Search

SunGard Corrected Defect 1-BQAXW

Cursor was searching across multiple terms resulting in excessive load (60% in perf. testing)

Added cursor to search only one term when specified

Fixed in Banner 7.3

Top 5 Solutions
#2 New Banner 7.3 APIs

SunGard Corrected Defect 1-G7KLZ

Banner 7.2 APIs were using literals rather than bind variables causing shared pool fragmentation and high latch waits

Implemented Student 7.3 APIs that were backwards compatible with 7.2

Partially fixed in Banner 7.3

Top 5 Solutions
#3 Upgraded from Oracle 10.2.0.1 to 10.2.0.2

Oracle fixed many Cost-Based Optimizer Bugs

Many poor execution plans were observed under 10.2.0.1 causing us to use SQL profiles and RULE hints to overcome poor plans

Plans improved considerably under 10.2.0.2, SQL Profiles removed

Top 5 Solutions
#4 Turned off Oracle 10g Automatic Memory Mgt

During heavy load shared pool was resizing itself several thousand times a minute creating massive library latch waits

Oracle Bugs - 4472338/4466399 (unpublished)

Set SGA_TARGET= 0, SHARED_POOL_SIZE = 2147483648, DB_CACHE_SIZE = 8589934592

Top 5 Solutions
#5 Turned off Oracle 10g Automatic Statistics

Default stats gathering caused plans to change and random performance degradation from one day to the next

Default stats run nightly, use SIZE=AUTO and generate histograms indiscriminately

Implemented script to calc stats without histograms, SIZE=1, added histograms as needed

Problems We Hit
Problems We Hit – Week 1
• Problem: Query from Document Imaging against ALL_SYNONYMS responsible for 20% of database load

• Detected by: ADDM and Top Activity from Oracle 10g Enterprise Manager Grid Control

• Short term solution: Implement SQL Profile using Oracle 10g SQL Tuning Advisor

• Long term solution: Replaced 10g ALL_SYNONYMS view with 9i view. See Metalink Note:377037.1

Problems We Hit – Week 1
• Problem: SAAADMS form slow, issuing 19 million calls per hour to get effective term code from SGBSTDN and 14 million calls to SORLCUR, responsible for 28% of load

• Detected by: Reports of slow response, ADDM, AWR and Top Activity in Oracle EM

• Short Term Solution: Paper patch for defect CMS-DFCT103861

• Long Term Solution: Defect is fixed in Student 7.3

Other Problems We Hit
• Problem: Performance issues on NBQPOSN, TGRAGES, TSRCBIL, FAIVNDH, FAIINVL, FGIBDSR, PERJOBS, RORRULE, GLBDATA, RPEDISB due to bad plans/stats

• Short term solutions: Restore stats, RULE hint, SQL Profiles

• Long term solution: Implemented our own statistics gathering with histograms only where required

Other Problems We Hit
• Problem: Poor performance in FZRACTG, SFRFASC, and Registration

• Solutions:
Added index on FGBTRND on FGBTRND_DEFER_GRNT_IND;

Added index on SFRRGFE based upon our usage of columns;

dropped index SSBSECT_GSCH_INDEX

Other Problems We Hit
• Problem: Poor performance in FGITRND, FZIGITD, FZAGRNT, FGIGLAC due to parallel queries against partitioned FGBTRND; lots of PX waits skewing monitors

• Solution: Turned off parallel query by setting PARALLEL_MAX_SERVERS = 0

Other Problems We Hit
• Problem: Poor performance in Web for Admissions on two queries against SARHEAD due to conditional NULL logic in SQL statements

• Solution: Rewrote portions of 10 packages locally, SunGard issued Defect 1-17GNAT

SELECT SARHEAD_APPL_SEQNO, STVWAPP_CODE, STVWAPP_DESC, STVTERM_DESC, SARHEAD_ADD_DATE, SARHEAD_APPL_STATUS_IND, SARHEAD_APPL_COMP_IND, SARHEAD_WSCT_CODE_BOOKMARK, SARHEAD_APPL_PREFERENCE
FROM SARHEAD, STVTERM, STVWAPP
WHERE ( ( :B2 IS NOT NULL AND EXISTS (SELECT 1
FROM SABIDEN WHERE SABIDEN_PIDM = :B2 AND SARHEAD_AIDM = SABIDEN_AIDM) )
OR ( :B2 IS NULL AND SARHEAD_AIDM = :B1 ) )………

Allowed us to process over 13,000 admission decision queries in 30 minutes on Dec. 15, 2006 with almost no impact on the system!!

Other Problems We Hit
• Problem: Receiving ORA-6550 and “PLS-00306: wrong number or types of arguments” affecting Web For users due to Oracle Bug 4752541

• Short Term Solution: Used workaround of recompiling packages

• Long Term Solution: Implemented the Oracle patch for the bug

Tools to Help You Tame the Beast
Tools to Help You Tame the Beast
#1 Must Have!

Oracle 10g Enterprise Manager Grid Control w/Diagnostic & Tuning Packs

Make sure you have this up and running as soon as you put up Oracle 10g!

Tools to Help You Tame the Beast
Top Activity graph –
part of Oracle 10g Diagnostic Pack

Tools to Help You Tame the Beast
Advanced Database Diagnostic Monitor (ADDM) Report – part of Oracle 10g Diagnostic Pack

Tools to Help You Tame the Beast
Automatic Workload Repository (AWR) – part of Oracle 10g Diagnostic Pack

Tools to Help You Tame the Beast
Active Session History (ASH) Report –
Run reports by:
• Date/time range only
• SID
• SQL ID
• Wait Class
• Service
• Module
• Action
• Client

Tools to Help You Tame the Beast
RULE Hint

Majority of Banner still runs well with the RULE hint

Use only as a last resort!

Investigate statistics, particularly histograms, before implementing RULE hint

Tools to Help You Tame the Beast
SunGard Oracle 10g FAQ 1-S35GU

The FAQ of all other Oracle 10g related FAQs

Start here when configuring your first 10g environment

Summary
Summary
• NO FEAR! - Banner 7 and Oracle 10g can perform great together!

• Scrutinize Oracle’s automatic memory management and statistics gathering

• Use Oracle’s 10g tools to monitor performance and drill into problems

• Follow SunGard’s FAQs for Oracle 10g

Scott Harden
sharden@uillinois.edu