1、1An Oracle 10g Upgrade Case An Oracle 10g Upgrade Case Study:Looking at System Study:Looking at System Performance Before and After Performance Before and After the Upgradethe UpgradeRoger SchragRoger SchragDatabase Specialists,Database Specialists,Inc.Inc.NoCOUGNoCOUG Spring Spring Conference 2005C
2、onference 20052Todays SessionTodays Session The view from 30,000 feet:Our Oracle environment,upgrade strategy Impressions:upgrade process and compatibility Impressions:Oracle 10g in general In greater detail:Sizing the shared pool and SGA Optimizer statistics collection and accuracy Query optimizati
3、on SQL Tuning Advisor Overhead 3Todays SessionTodays SessionGoal:Help you plan for your own Oracle 10g upgrade.We will:Look at one companys experience upgrading to 10g Discuss real-life experiences Provide data so you can draw your own conclusions We will not:Walk through the actual upgrade steps Ma
4、ke any judgments about Oracle 10g 4Always RememberAlways Remember Each Oracle system is unique and will have its own challenges.Never take somebody elses word on anything when it comes to Oracle technology.In this session we are only relaying one companys experiences.The only way for you to know how
5、 your specific system will fare on Oracle 10g is to try itin a test environmentand see.5White PaperWhite Paper Contains additional topics and examples we wont have time to discuss today Contains additional“supporting evidence”for conclusions reached in todays session that we wont have time to discus
6、s or that wont fit legibly on a PowerPoint slide TKPROF reports,execution plans,AWR reports Download: 6The View From 30,000 FeetThe View From 30,000 Feet Our Oracle environment Our upgrade strategy Impressions:upgrade process and compatibility Impressions:Oracle 10g in general 7Our Oracle Environmen
7、tOur Oracle Environment Platform details:Oracle 8.1.7 Standard Edition 32 bit Sun Solaris 8 64 bit One production and one dev database Production database 15 Gb in size 8Our Oracle EnvironmentOur Oracle Environment Application:Customer database monitoring tool Backend daemons process inbound agent f
8、iles from our customers database servers in the field Web-based user interface for report generation,system configuration Almost all code is PL/SQL(roughly 50,000 lines)Leverages Oracle 8i featureseg GTTs,table()About 50 SQL statements have hints 9Our Oracle EnvironmentOur Oracle Environment Oracle
9、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 3-4 times per year Designed and developed from the start by small group of experienced Oracle DBAs,developers Well-architected
10、 for efficiency,performance,scalability(in our opinion)10Our Reasons to Upgrade to 10gOur 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 compelling reason.)11Our Upgrade StrategyOur
11、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 s
12、et“Fresh”data dictionary,database components Worked out a strategy to keep the down time tolerable12Our Upgrade StrategyOur Upgrade Strategy Our 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 te
13、st 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.13Impressions:Upgrade ProcessImpressions:Upgrade Process Oracle 10g version 10.1.0.2 and patch set 10.1.0.3 installed very smoothly
14、.Oracle 10g import utility read our Oracle 8i export file with no issues.Oracle 10g Upgrade Information Tool accurately pointed 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!)14Impressions:Com
15、patibilityImpressions:Compatibility Encountered two compatibility issues:EXTPROC needed reconfiguring(tighter security)and recompiling(32 bit 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
16、10g wrapper utility resolved this.All other application code functioned correctly.Retained Oracle 8i modplsql client initially.No interoperability issues encountered.15Impressions:Oracle 10gImpressions:Oracle 10g Worked well out of the box:Enterprise Manager Database Control and iSQLPlus were terrib
17、ly 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 bare minimum Oracle 10g new features.16Impressions:Oracle 10gImpressions:Oracle 10g Bigger,bulkier,hungrier for system resources:Bigger executa
18、ble size,shared pool,SYSTEM tablespace More overhead:Daemon processes,hard parses,statistics collection Overhead and bulkiness were tolerable for us.17Impressions:Oracle 10gImpressions:Oracle 10g Application performance was about the same:Most SQL consumed similar resources.Due to our hints,OLTP nat
19、ure,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 don
20、e without manual query optimization(hints)?We do not believe so.18Impressions:Oracle 10gImpressions:Oracle 10g Discouraged by SQL Tuning Advisor.(But did not test exhaustively due to frustration.)The bottom line for us:Install and upgrade went better than we expected.Increased overhead and heft are
21、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 very cautiously!)19Upgrade Issues in Greater Upgrade Issues in Greater DetailDetail Si
22、zing the shared pool and SGA Optimizer statistics collection and accuracy Query optimization SQL Tuning Advisor Overhead 20Sizing the Shared Pool and SGASizing the Shared Pool and SGA We like SGA to be only as large as necessary.Oracle 8i settings:shared_pool_size=40 Mb Total SGA size was 84 Mb Orac
23、le 8i performance characteristics:50,000 lines of PL/SQL code 15-20 executions per second Under 660 hard parses per day Buffer cache hit ratio 97%Library cache hit ratio 100%21Sizing the Shared Pool and SGASizing the Shared Pool and SGA Oracle 10g settings:shared_pool_size=144 Mb Total SGA size is 1
24、94 Mb Why?Minimum shared_pool_size setting for 64 bit platforms is 144 Mb according to Metalink document 263809.1 Recommended by Upgrade Information Tool as well 22Sizing the Shared Pool and SGASizing the Shared Pool and SGA Just to satisfy a curiosity shared_pool_size=48 Mb on Oracle 10g:Instance w
25、ould not start shared_pool_size=64 Mb on Oracle 10g:Instance started,but frequent ORA-4031 errors shared_pool_size=96 Mb on Oracle 10g:Everything seemed to work properly We run Oracle 10g in production with:shared_pool_size=144 Mb23Reasons for Larger Shared PoolReasons for Larger Shared Pool Three r
26、easons why the shared_pool_size setting needs to be increased when upgrading to Oracle 10g:Allocation for overhead Shared SQL area memory usage SQL statements generated by Oracle 24Allocation for OverheadAllocation for Overhead A portion of the shared pool is used to hold internal memory structures(
27、overhead).Oracle 8i and 9i make the shared pool larger than shared_pool_size specifies in order to allow space 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_siz
28、e setting.See Metalink document 270935.1.25Allocation for OverheadAllocation for Overhead On our Oracle 8i database the shared pool was 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
29、-43.1291847 SQL SHOW PARAMETER shared_pool_size NAME TYPE VALUE -shared_pool_size string 41943040 Weve seen the disparity as high as 27%.26Shared SQL Area Memory UsageShared SQL Area Memory Usage Individual SQL statements appear to occupy more memory in the shared SQL area in Oracle 10g than in Orac
30、le 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 dont know.27Shared SQL Area Memory UsageShared SQL Area Memory Usage On our Oracle 8i database:SQL SELECT A.username,COUNT(*),SUM(B.sharable_mem)sharable_
31、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 GROUP BY A.username;USERNAME COUNT(*)SHARABLE_MEM PERSISTENT_MEM RUNT
32、IME_MEM TOTAL_MEM -DBRX_OWNER 362 6,275,020 256,176 1,996,324 8,527,520 28Shared SQL Area Memory UsageShared SQL Area Memory Usage On 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.s
33、harable_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 GROUP 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 29SQL Gen
34、erated by OracleSQL Generated by Oracle The shared SQL area on any Oracle instance will contain statements issued by Oracle itself and not by the application.Often called“internal SQL”or“recursive SQL”.Automatic and self-management infrastructure in Oracle 10g(database and EM Database Control)genera
35、tes a lot of internal SQL.The shared pool will need to be larger in order to accommodate the extra statements.30SQL Generated by OracleSQL Generated by Oracle Internal SQL took up an order of magnitude more space in the shared SQL area of our Oracle 10g test database than our Oracle 8i test database
36、.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 database was Enterprise Edition with“default”options installed.31SQL Generated by OracleSQL Generated by Oracle On our O
37、racle 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$sql B 7 WHERE A.username IN(DBSNMP,SYS,SYSTEM,SYSMAN)8 AND B.par
38、sing_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 810,325 19,644 163,480 993,449 -sum 3,141,944 145,000 733,168 4,020,112 32SQL Generated by OracleSQL Generated by Oracle On our Oracl
39、e 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.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.parsin
40、g_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,032 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
41、-sum 45,742,227 1,987,304 14,498,528 62,228,059 33Optimizer StatisticsOptimizer Statistics Collected optimizer statistics weekly in Oracle 8i:ANALYZE TABLE table_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 c
42、ollects statistics where missing or stale.Sample size and histograms“automatic.”This is all set up automatically out of the box.34Optimizer Statistics:CostOptimizer Statistics:Cost Automatic statistics collection in Oracle 10g is more resource intensive than ANALYZE was in Oracle 8i:Resources Used t
43、o Collect Optimizer StatisticsOracle8i (ANALYZE)Oracle 10g(automatic)CPU seconds1,1012,595Elapsed seconds2,0445,244Logical reads597,71773,082,675Physical reads545,8442,926,62535Histogram CreationHistogram Creation Histograms are one reason statistics collection in Oracle 10g is so much more expensiv
44、e: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 BALANCED 74 NONE 1202 -sum 1543 36Histogram CreationHistogram Creation If a column has ever been used
45、 in a WHERE clause,Oracle 10g will consider creating a histogram for it(note col_usage$):FREQUENCY histograms for low cardinality columns HEIGHT BALANCED histograms for columns with gaps or skewed data distribution Many of the histograms wont be useful:On unindexed columns that only appear in WHERE
46、clauses alongside a selective,indexed column On columns that rarely appear in WHERE clauses37Sample SizeSample Size Sample size is another reason statistics collection in Oracle 10g was so much more expensive.Oracle 8i sample sizes were consistent:Sample sizes on tables over 1 Mb were 4.5 to 5.4%.Sa
47、mple 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%38Sample SizeSample Size On our Oracle 10g database:SQL SELECT A.table_name,A.num_rows,B.bytes/1024/1024 mb,2 100*(A.s
48、ample_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 9 AND B.segment_name=A.table_name 10 ORDER BY sample_pct;TABLE_NAME NUM_ROWS
49、 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 100.0 39Sample SizeSample Size How Oracle 10g came to sample every row in a 760 Mb table
50、: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,Oracle performed a COUNT(DISTINCT)on one of the columns without a SAMPLE clause.40Op
