1、An Oracle 10g Upgrade Case Study: Looking at System Performance Before and After the UpgradeRoger SchragDatabase Specialists, Inc.NoCOUG Spring Conference 2005Todays SessionThe view from 30,000 feet:Our Oracle environment, upgrade strategyImpressions: upgrade process and compatibilityImpressions: Or

2、acle 10g in generalIn greater detail:Sizing the shared pool and SGAOptimizer statistics collection and accuracyQuery optimizationSQL Tuning AdvisorOverhead Todays SessionGoal: Help you plan for your own Oracle 10g upgrade.We will: Look at one companys experience upgrading to 10gDiscuss real-life exp

3、eriencesProvide data so you can draw your own conclusionsWe will not:Walk through the actual upgrade stepsMake any judgments about Oracle 10g Always RememberEach Oracle system is unique and will have its own challenges. Never take somebody elses word on anything when it comes to Oracle technology. I

4、n this session we are only relaying one companys experiences. The only way for you to know how your specific system will fare on Oracle 10g is to try itin a test environmentand see. White PaperContains additional topics and examples we wont have time to discuss todayContains additional “supporting e

5、vidence” for conclusions reached in todays session that we wont have time to discuss or that wont fit legibly on a PowerPoint slideTKPROF reports, execution plans, AWR reportsDownload: /presentations The View From 30,000 FeetOur Oracle environmentOur upgrade strategyImpressions: upgrade process and

6、compatibilityImpressions: Oracle 10g in general Our Oracle EnvironmentPlatform details:Oracle 8.1.7 Standard Edition 32 bitSun Solaris 8 64 bitOne production and one dev databaseProduction database 15 Gb in size Our Oracle EnvironmentApplication: Customer database monitoring toolBackend daemons proc

7、ess inbound agent files from our customers database servers in the fieldWeb-based user interface for report generation, system configurationAlmost all code is PL/SQL (roughly 50,000 lines)Leverages Oracle 8i featureseg GTTs, table()About 50 SQL statements have hints Our Oracle Environment Oracle 8i

8、production database was very stableFigured out workarounds to 8i bugs long agoApplication enhancements are tested in dev before production deploymentInstance restarted 3-4 times per yearDesigned and developed from the start by small group of experienced Oracle DBAs, developersWell-architected for ef

9、ficiency, performance, scalability (in our opinion) Our Reasons to Upgrade to 10gOracle 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 compelling reason.) Our Upgrade StrategyRestore production hot backup onto de

10、dicated 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 LMTsCompact all application segments (purges left holes)Change character set“Fresh” data dictionary, database components Worked

11、out a strategy to keep the down time tolerableOur Upgrade StrategyOur Oracle 8i and 10g test databases started out with the same datahandy for testing and comparison.Two critical points to remember when comparing these two test databases:Application segments in Oracle 10g test database occupied fewe

12、r blocks.Our Oracle 10g test database was 64 bit while our Oracle 8i test database was 32 bit.Impressions: Upgrade ProcessOracle 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 po

13、inted out necessary parameter changes.Ive done my share of Oracle installs over the years, and honestly this was one of the smoother ones. (Note: Solaris platform!) Impressions: Compatibility Encountered two compatibility issues:EXTPROC needed reconfiguring (tighter security) and recompiling (32 bit

14、 to 64 bit change).Oracle 10g PLSQL compiler 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 interope

15、rability issues encountered. 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 ORA-600s or other funnies.Caveat: We are using few Oracle 9i and b

16、are minimum Oracle 10g new features. Impressions: Oracle 10gBigger, bulkier, hungrier for system resources:Bigger executable size, shared pool, SYSTEM tablespaceMore overhead:Daemon processes, hard parses, statistics collection Overhead and bulkiness were tolerable for us.Impressions: Oracle 10g App

17、lication 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

18、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. Impressions: Oracle 10gDiscouraged by SQL Tuning Advisor. (But did not test exhaustively due to frustration.)The bottom line for

19、 us:Install and upgrade went better than we expected.Increased overhead and heft are manageablea fair exchange for increased functionality and sophistication.We expect to get more out of our system than was possible with Oracle 8i, once we leverage newer features. (But will proceed in this direction

20、 very cautiously!) Upgrade Issues in Greater DetailSizing the shared pool and SGAOptimizer statistics collection and accuracyQuery optimizationSQL Tuning AdvisorOverhead Sizing the Shared Pool and SGAWe like SGA to be only as large as necessary.Oracle 8i settings:shared_pool_size = 40 MbTotal SGA si

21、ze was 84 MbOracle 8i performance characteristics:50,000 lines of PL/SQL code15-20 executions per secondUnder 660 hard parses per dayBuffer cache hit ratio 97%Library cache hit ratio 100% Sizing the Shared Pool and SGAOracle 10g settings:shared_pool_size = 144 MbTotal SGA size is 194 MbWhy?Minimum s

22、hared_pool_size setting for 64 bit platforms is 144 Mb according to Metalink document 263809.1Recommended by Upgrade Information Tool as well Sizing the Shared Pool and SGAJust to satisfy a curiosityshared_pool_size = 48 Mb on Oracle 10g:Instance would not startshared_pool_size = 64 Mb on Oracle 10g

23、:Instance started, but frequent ORA-4031 errorsshared_pool_size = 96 Mb on Oracle 10g:Everything seemed to work properlyWe run Oracle 10g in production with:shared_pool_size = 144 MbReasons for Larger Shared PoolThree reasons why the shared_pool_size setting needs to be increased when upgrading to O

24、racle 10g:Allocation for overheadShared SQL area memory usageSQL statements generated by Oracle Allocation for OverheadA portion of the shared pool is used to hold internal memory structures (overhead).Oracle 8i and 9i make the shared pool larger than shared_pool_size specifies in order to allow spa

25、ce for this overhead.Oracle 10g does not make the shared pool larger than shared_pool_size specifies.Thus Oracle 10g gives you less usable space in the shared pool for the same shared_pool_size setting.See Metalink document 270935.1.Allocation for OverheadOn our Oracle 8i database the shared pool wa

26、s about 3 Mb (8%) larger than specified by shared_pool_size: SQL SELECT SUM (bytes) / 1024 / 1024 actual_pool_size 2 FROM v$sgastat 3 WHERE pool = shared pool; ACTUAL_POOL_SIZE - 43.1291847 SQL SHOW PARAMETER shared_pool_size NAME TYPE VALUE - - - shared_pool_size string 41943040Weve seen the dispar

27、ity as high as 27%. Shared SQL Area Memory UsageIndividual SQL statements appear to occupy more memory in the shared SQL area in Oracle 10g than in Oracle 8i.In our environment the difference was almost 2x.The move from 32 bit Oracle software to 64 bit accounts for much of this growth.How much, we d

28、ont know. Shared SQL Area Memory UsageOn our Oracle 8i database: SQL SELECT A.username, COUNT(*), SUM (B.sharable_mem) sharable_mem, 2 SUM (B.persistent_mem) persistent_mem, 3 SUM (B.runtime_mem) runtime_mem, 4 SUM (B.sharable_mem + B.persistent_mem + B.runtime_mem) 5 total_mem 6 FROM dba_users A, v

29、$sql B 7 WHERE A.username = DBRX_OWNER 8 AND B.parsing_user_id = A.user_id 9 GROUP BY A.username; USERNAME COUNT(*) SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM TOTAL_MEM - - - - - - DBRX_OWNER 362 6,275,020 256,176 1,996,324 8,527,520 Shared SQL Area Memory UsageOn our Oracle 10g database: SQL SELECT A.

30、username, COUNT(*), SUM (B.sharable_mem) sharable_mem, 2 SUM (B.persistent_mem) persistent_mem, 3 SUM (B.runtime_mem) runtime_mem, 4 SUM (B.sharable_mem + B.persistent_mem + B.runtime_mem) 5 total_mem 6 FROM dba_users A, v$sql B 7 WHERE A.username = DBRX_OWNER 8 AND B.parsing_user_id = A.user_id 9 G

31、ROUP BY A.username; USERNAME COUNT(*) SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM TOTAL_MEM - - - - - - DBRX_OWNER 360 12,941,006 487,048 3,361,160 16,789,214 SQL Generated by OracleThe shared SQL area on any Oracle instance will contain statements issued by Oracle itself and not by the application.Ofte

32、n called “internal SQL” or “recursive SQL”.Automatic and self-management infrastructure in Oracle 10g (database and EM Database Control) generates a lot of internal SQL.The shared pool will need to be larger in order to accommodate the extra statements.SQL Generated by OracleInternal SQL took up an

33、order of magnitude more space in the shared SQL area of our Oracle 10g test database than our Oracle 8i test database.Internal SQL took up more space in Oracle 10g than our application code.Caveat:The Oracle 8i test database was Standard Edition with minimal options installed. The Oracle 10g test da

34、tabase was Enterprise Edition with “default” options installed. SQL Generated by OracleOn our Oracle 8i database: SQL SELECT A.username, COUNT(*), SUM (B.sharable_mem) sharable_mem, 2 SUM (B.persistent_mem) persistent_mem, 3 SUM (B.runtime_mem) runtime_mem, 4 SUM (B.sharable_mem + B.persistent_mem +

35、 B.runtime_mem) 5 total_mem 6 FROM dba_users A, v$sql B 7 WHERE A.username IN (DBSNMP, SYS, SYSTEM, SYSMAN) 8 AND B.parsing_user_id = A.user_id 9 GROUP BY A.username; USERNAME COUNT(*) SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM TOTAL_MEM - - - - - - SYS 192 2,331,619 125,356 569,688 3,026,663 SYSTEM 30

36、 810,325 19,644 163,480 993,449 - - - - sum 3,141,944 145,000 733,168 4,020,112 SQL Generated by OracleOn our Oracle 10g database: SQL SELECT A.username, COUNT(*), SUM (B.sharable_mem) sharable_mem, 2 SUM (B.persistent_mem) persistent_mem, 3 SUM (B.runtime_mem) runtime_mem, 4 SUM (B.sharable_mem + B

37、.persistent_mem + B.runtime_mem) 5 total_mem 6 FROM dba_users A, v$sql B 7 WHERE A.username IN (DBSNMP, SYS, SYSTEM, SYSMAN) 8 AND B.parsing_user_id = A.user_id 9 GROUP BY A.username; USERNAME COUNT(*) SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM TOTAL_MEM - - - - - - DBSNMP 99 4,161,758 137,504 1,701,03

38、2 6,000,294 SYS 695 24,402,627 1,024,744 8,103,496 33,530,867 SYSMAN 670 16,644,400 806,904 4,403,720 21,855,024 SYSTEM 14 533,442 18,152 290,280 841,874 - - - - sum 45,742,227 1,987,304 14,498,528 62,228,059 Optimizer StatisticsCollected optimizer statistics weekly in Oracle 8i: ANALYZE TABLE table

39、_name ESTIMATE STATISTICS SAMPLE 5 PERCENT;Oracle 10g uses gather_stats_job:Automatic job runs nightly 10 pm to 6 am.Uses dbms_stats.Only collects statistics where missing or stale.Sample size and histograms “automatic.”This is all set up automatically out of the box. Optimizer Statistics: CostAutom

40、atic statistics collection in Oracle 10g is more resource intensive than ANALYZE was in Oracle 8i:Resources Used to CollectOptimizer StatisticsOracle8i (ANALYZE)Oracle 10g(automatic)CPU seconds1,1012,595Elapsed seconds2,0445,244Logical reads597,71773,082,675Physical reads545,8442,926,625Histogram Cr

41、eationHistograms are one reason statistics collection in Oracle 10g is so much more expensive:Our setup on Oracle 8i created no histograms.Oracle 10g created lots of histograms: SQL SELECT histogram, COUNT(*) 2 FROM user_tab_columns 3 GROUP BY histogram; HISTOGRAM COUNT(*) - - FREQUENCY 267 HEIGHT B

42、ALANCED 74 NONE 1202 - sum 1543 Histogram CreationIf a column has ever been used in a WHERE clause, Oracle 10g will consider creating a histogram for it (note col_usage$):FREQUENCY histograms for low cardinality columnsHEIGHT BALANCED histograms for columns with gaps or skewed data distributionMany

43、of the histograms wont be useful:On unindexed columns that only appear in WHERE clauses alongside a selective, indexed columnOn columns that rarely appear in WHERE clausesSample SizeSample size is another reason statistics collection in Oracle 10g was so much more expensive.Oracle 8i sample sizes we

44、re consistent:Sample sizes on tables over 1 Mb were 4.5 to 5.4%.Sample sizes on smaller tables were 100%.Oracle 10g sample sizes were all over the map:Sample size on 80 Mb table: 100%Sample size on 1,088 Mb table: 0.4%Sample size on 760 Mb table: 100% Sample SizeOn our Oracle 10g database: SQL SELEC

45、T A.table_name, A.num_rows, B.bytes / 1024 / 1024 mb, 2 100 * (A.sample_size / A.num_rows) sample_pct 3 FROM user_tables A, user_segments B 4 WHERE A.table_name IN 5 (SAMPLE_DATA_FILES, SAMPLE_JOBS, 6 COMMON_SQL_PLAN_PARTS, SAMPLE_SQL_TEXTS, 7 SAMPLE_LIBRARY_CACHE_STATS) 8 AND B.segment_type = TABLE

46、 9 AND B.segment_name = A.table_name 10 ORDER BY sample_pct; TABLE_NAME NUM_ROWS MB SAMPLE_PCT - - - - SAMPLE_DATA_FILES 14,938,632 1,088.00 0.4 SAMPLE_JOBS 1,360,429 54.00 4.1 COMMON_SQL_PLAN_PARTS 174,851 9.00 6.9 SAMPLE_LIBRARY_CACHE_STATS 1,414,830 80.00 100.0 SAMPLE_SQL_TEXTS 6,346,638 760.00 1

47、00.0 Sample SizeHow Oracle 10g came to sample every row in a 760 Mb table:First, Oracle sampled all 35 columns of the table on 0.0892929621% of the rows.Next, Oracle sampled 8 of the columns on 0.8929296209% of the rows.Next, Oracle sampled 3 of the columns on 8.9292962091% of the rows. Finally, Ora

48、cle performed a COUNT (DISTINCT) on one of the columns without a SAMPLE clause.Optimizer Statistics: AccuracyOracle 10g optimizer statistics did not appear to be particularly more accurate than those collected by ANALYZE in Oracle 8i.In particular Oracle 10gs estimate of distinct column values was s

49、ometimes less accurate than Oracle 8is.Could have been caused by excessively small sample size on some tables (just a guess) Optimizer Statistics: AccuracyHow accurate do optimizer statistics need to be?If every business process on your system gives satisfactory response time, then the statistics ar

50、e accurate enough.But if a business process runs too slowly, can you blame the optimizer statistics? We will see some queries that got unsatisfactory execution plans in our Oracle 10g test environment.Is it the statistics? We dont know. Query OptimizationQueries in our application follow an OLTP wor

51、kload model.All run quickly (except for quarterly purge).Quick, but some are complex.We believe weve written practical, logical SQL.Oracle 8i ran most of our SQL efficiently:We added hints to SQL only when response time concerns arose.About 50 statements throughout the application have hints.Query O

52、ptimizationDid not expect things to run faster in Oracle 10g.Queries already had efficient execution plans in 8i.We expect the gains to come when we leverage Oracle 9i and 10g new features.Concern: What if some queries run slower in Oracle 10g?In a business process with 100 SQL statements, it only t

53、akes one bad execution plan to slow the whole process down. The Executive SummaryMost SQL in our application consumed roughly the same CPU time and number of logical reads in Oracle 10g as in Oracle 8i.Some statements ran a little faster, and a few ran a little slower.Most workload operations yielde

54、d similar response times in both versions of Oracle.Only a very few SQL statements were slow enough on Oracle 10g to cause concern. Query Optimizer ChallengeCould Oracle 10g find efficient execution plans for the queries that required hints in Oracle 8i?Is adding hints to queries a thing of the past

55、?Well not yet:Oracle 10g ran the troublesome queries faster without hints than Oracle 8i without hints.However, both versions of Oracle ran the queries faster with hints than Oracle 10g did without hints. Query Optimization in DetailSQL that ran similarly in Oracle 8i and 10gSQL that ran faster in O

56、racle 10gSQL that ran faster in Oracle 8iSQL That Ran SimilarlyLoader Daemon comparisonPerformance Summary report comparisonSee the white paper for TKPROF report excerptsLoader Daemon ComparisonLoader Daemon parses, validates, and loads files from our monitoring agents into the database for analysis

57、 and reporting.PL/SQL package roughly 7,800 lines long.7 SQL statements in the package have hints.Starting out with the same data in the Oracle 8i and 10g test databases, we traced the Loader Daemon on each database while loading the same agent file into each.Loader Daemon ComparisonResources Used b

58、y Loader Daemonto Load One Agent FileOracle 8iOracle 10gUser SQL statements traced110127Internal SQL statements traced99Unique SQL statements traced109110Total OCI calls1,8001,792CPU seconds3.133.12Logical reads13,76712,920Physical reads613Loader Daemon ComparisonBusiness process gave roughly same r

59、esponse time and load profile on Oracle 8i and 10g.Fewer logical reads on Oracle 10g:Import made 10g segments more compact.More user SQL statements traced on Oracle 10g:Oracle 10g database had smaller PL/SQL cursor cache due to behavior change implemented in re open_cursors. (See Metalink document 2

60、74496.1.)Cache misses lead to extra (soft) parse calls.TKPROF reported these extra parse calls as extra traced statements.Performance Report ComparisonPerformance Summary report provides a summary of performance statistics for one monitored Oracle database over a specified period of time (like a Sta