1 / 34

SETA Midwest 2006

SETA Midwest 2006. Taming Banner 7 on Oracle 10g Scott Harden Data Management Area Mgr. University of Illinois Friday, December 1, 9:45 AM. Session Rules of Etiquette. Please turn off your cell phone/beeper If you must leave the session early, please do so as discretely as possible

mathewm
Download Presentation

SETA Midwest 2006

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SETA Midwest 2006 Taming Banner 7 on Oracle 10g Scott Harden Data Management Area Mgr. University of Illinois Friday, December 1, 9:45 AM

  2. Session Rules of Etiquette • Please turn off your cell phone/beeper • If you must leave the session early, please do so as discretely as possible • Please avoid side conversation during the presentation Thank you for your cooperation!

  3. 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

  4. 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

  5. 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

  6. How Large Are We? • SUN F15K - 44 CPUs, 88 GB memory • Database size: 307 GB • Growth: ~250 MB/day • FGBTRND over 133 million rows • NHRDIST over 68 million rows • SORLCUR over 3.9 million rows • Live for roughly 3 years now

  7. Results of Our Tuning Efforts 45% reduction in CPU load 62% reduction in logical reads/sec 50% increase in max transactions/sec

  8. Results of Our Tuning Efforts(Details) *numbers since adding 2 additional system boards (8 CPUs, 16 GB memory)

  9. 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

  10. Top 5 Solutions #2New 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

  11. Top 5 Solutions #3Upgraded 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

  12. Top 5 Solutions #4Turned 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

  13. Top 5 Solutions #5Turned 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

  14. 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

  15. 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

  16. 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

  17. Problems We Hit • Problem: Poor performance in FZRACTG, SFRFASC, and Registration • Solution: Added index on FGBTRND on FGBTRND_DEFER_GRNT_IND; Added index on SFRRGFE based upon our usage of columns; dropped index SSBSECT_GSCH_INDEX

  18. 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

  19. Problems We’re Hitting Now • 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

  20. Problems We’re Hitting Now SunGard – Please don’t do this! Forces a full table scan! 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 ) )………

  21. Problems We’re Hitting Now • Problem: Receiving ORA-6550 and “PLS-00306: wrong number or types of arguments” affecting Web For users due to Oracle Bug 4752541 • Solution: Currently using workaround of recompiling packages but are targeting implementing the Patch for the bug

  22. Tools to Help YouTame 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!

  23. Tools to Help YouTame the Beast Sample Top Activity graph – part of Oracle 10g Diagnostic Pack

  24. Tools to Help YouTame the Beast Advanced Database Diagnostic Monitor (ADDM) Report – part of Oracle 10g Diagnostic Pack (sample on next page)

  25. ADDM Report Sample FINDING 1: 46% impact (30464 seconds) ------------------------------------- SQL statements consuming significant database time were found. RECOMMENDATION 1: SQL Tuning, 21% benefit (14283 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "9zyggvkjs7pdt". RELEVANT OBJECT: SQL statement with SQL_ID 9zyggvkjs7pdt and PLAN_HASH 838732573 select table_owner, table_name from all_synonyms where owner = 'OTGMGR' and synonym_name = 'AE_USRPRO' order by decode(owner, 'PUBLIC', 2, 1) RATIONALE: SQL statement with SQL_ID "9zyggvkjs7pdt" was executed 8268 times and had an average elapsed time of 1.7 seconds.

  26. Tools to Help YouTame the Beast Automatic Workload Repository (AWR) – part of Oracle 10g Diagnostic Pack (section sample on next page)

  27. AWR Report Sample

  28. 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 (section sample on next page)

  29. ASH Report Sample

  30. Tools to Help YouTame the Beast RULE Hint Majority of Banner runs still well with the RULE hint Use only as a last resort! Investigate statistics, particularly histograms, before implementing RULE hint

  31. Tools to Help YouTame 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

  32. 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

  33. Questions?

  34. Presenter Information • Scott Harden • 50 Gerty Drive, Champaign, IL 61820 • (217) 244-0108 • sharden@uillinois.edu

More Related