【正文】 1 An Oracle 10g Upgrade Case Study: Looking at System Performance Before and After the Upgrade Roger Schrag Database Specialists, Inc. NoCOUG Spring Conference 2021 2 Today39。s Session ? The view from 30,000 feet: – Our Oracle environment, upgrade strategy – Impressions: upgrade process and patibility – Impressions: Oracle 10g in general ? In greater detail: – Sizing the shared pool and SGA – Optimizer statistics collection and accuracy – Query optimization – SQL Tuning Advisor – Overhead 3 Today’s Session Goal: Help you plan for your own Oracle 10g upgrade. ? We will: – Look at one pany’s experience upgrading to 10g – Discuss reallife experiences – Provide data so you can draw your own conclusions ? We will not: – Walk through the actual upgrade steps – Make any judgments about Oracle 10g 4 Always Remember ? Each Oracle system is unique and will have its own challenges. ? Never take somebody else’s word on anything when it es to Oracle technology. ? In this session we are only relaying one pany’s experiences. ? The only way for you to know how your specific system will fare on Oracle 10g is to try it—in a test environment—and see. 5 White Paper ? Contains additional topics and examples we won39。t have time to discuss today ? Contains additional ―supporting evidence‖ for conclusions reached in today39。s session that we won39。t have time to discuss or that won’t fit legibly on a PowerPoint slide – TKPROF reports, execution plans, AWR reports ? Download: 6 The View From 30,000 Feet ? Our Oracle environment ? Our upgrade strategy ? Impressions: upgrade process and patibility ? Impressions: Oracle 10g in general 7 Our Oracle Environment ? Platform details: – Oracle Standard Edition 32 bit – Sun Solaris 8 64 bit – One production and one dev database – Production database 15 Gb in size 8 Our Oracle Environment ? Application: Customer database monitoring tool – Backend daemons process inbound agent files from our customers’ database servers in the field – Webbased user interface for report generation, system configuration – Almost all code is PL/SQL (roughly 50,000 lines) – Leverages Oracle 8i features—eg GTTs, table() – About 50 SQL statements have hints 9 Our Oracle Environment ? Oracle 8i production database was very stable – Figured out workarounds to 8i bugs long ago – Application enhancements are tested in dev before production deployment – Instance restarted 34 times per year – Designed and developed from the start by small group of experienced Oracle DBAs, developers – Wellarchitected for efficiency, performance, scalability (in our opinion) 10 Our Reasons to Upgrade to 10g ? Oracle 8i met all of our needs. ? So why upgrade? – Oracle 8i desupport. (What difference does it make?) – Gain Oracle 10g experience. (For us, a more pelling reason.) 11 Our Upgrade Strategy ? Restore production hot backup onto dedicated test server. ? Export Oracle 8i test database and import into empty Oracle 10g test database. ? Why export/import instead of upgrading in place? – Switch all tablespaces to LMTs – Compact all application segments (purges left holes) – Change character set – ―Fresh‖ data dictionary, database ponents – Worked out a strategy to keep the down time tolerable 12 Our Upgrade Strategy ? Our Oracle 8i and 10g test databases started out with the same data—handy for testing and parison. ? Two critical points to remember when paring these two test databases: – Application segments in Oracle 10g test database occupied fewer blocks. – Our Oracle 10g test database was 64 bit while our Oracle 8i test database was 32 bit. 13 Impressions: Upgrade Process ? Oracle 10g version and patch set installed very smoothly. ? Oracle 10g import utility read our Oracle 8i export file with no issues. ? Oracle 10g Upgrade Information Tool accurately pointed out necessary parameter changes. ? I39。ve done my share of Oracle installs over the years, and honestly this was one of the smoother ones. (Note: Solaris platform!) 14 Impressions: Compatibility ? Encountered two patibility issues: – EXTPROC needed reconfiguring (tighter security) and repiling (32 bit to 64 bit change). – Oracle 10g PLSQL piler did not like our Oracle 8i wrapped PL/SQL code. (Cause is probably an Oracle 8i export bug.) Rewrapping with Oracle 10g wrapper utility resolved this. – All other application code functioned correctly. ? Retained Oracle 8i modplsql client initially. – No interoperability issues encountered. 15 Impressions: Oracle 10g ? Worked well out of the box: – Enterprise Manager Database Control and iSQLPlus were terribly slow, but they worked. ? Our system appears as stable on Oracle 10g as it was on Oracle 8i: – No ORA600s or other funnies. – Caveat: We are using few Oracle 9i and bare minimum Oracle 10g new features. 16 Impressions: Oracle 10g ? Bigger, bulkier, hungrier for system resources: – Bigger executable size, shared pool, SYSTEM tablespace… ? More overhead: – Daemon processes, hard parses, statistics collection… ? Overhead and bulkiness were tolerable for us. 17 Impressions: Oracle 10g ? Application performance was about the same: – Most SQL consumed similar resources. – Due to our hints, OLTP nature, we had not expected Oracle 10g to run noticeably faster. – Very few queries ran slow enough in Oracle 10g to be a problem. – Oracle 10g did better than 8i when hints were removed, but not as well as either version with the hints in place. – If we had started out on Oracle 10g, do we think we could have done without manual query optimization (hints)? We do not believe so. 18 Impressions: Oracle 10g ? Discouraged by SQL Tuning Advisor. (But did not test exhaustively due t