【正文】 GA ? Oracle 10g settings: – shared_pool_size = 144 Mb – Total SGA size is 194 Mb ? Why? – Minimum shared_pool_size setting for 64 bit platforms is 144 Mb according to Metalink document – Remended by Upgrade Information Tool as well 22 Sizing the Shared Pool and SGA ? Just to satisfy a curiosity… ? shared_pool_size = 48 Mb on Oracle 10g: – Instance would not start ? shared_pool_size = 64 Mb on Oracle 10g: – Instance started, but frequent ORA4031 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 Mb 23 Reasons for Larger Shared Pool ? Three reasons 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 24 Allocation for Overhead ? A 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 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_size setting. ? See Metalink document . 25 Allocation 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 = 39。DBRX_OWNER‘ 8 AND = 9 GROUP BY 。, 39。TABLE‘ 9 AND = 10 ORDER BY sample_pct。 7092 HASH JOIN 4 TABLE ACCESS MODE: ANALYZED (FULL) OF 39。 (UNIQUE) 60 Oracle 10g Without Hint Rows Row Source Operation 0 SORT ORDER BY (cr=4212 pr=0 pw=0 time=3573213 us) 0 HASH JOIN (cr=4212 pr=0 pw=0 time=3573077 us) 71 TABLE ACCESS FULL LOOKUP_REPORT_40000_FORMATS (cr=3 pr=0 pw=0 time=489 us) 0 HASH JOIN (cr=4209 pr=0 pw=0 time=3562021 us) 4 TABLE ACCESS FULL ANALYSIS_TESTS (cr=18 pr=0 pw=0 time=853 us) 243 HASH JOIN (cr=4191 pr=0 pw=0 time=3554047 us) 126110 INDEX FAST FULL SCAN ANALYSIS_COMMON_RESULTS_N1 (cr=341 pr=0 pw=0 time=126363 us)(object id 49302) 243 HASH JOIN (cr=3850 pr=0 pw=0 time=2830427 us) 343 TABLE ACCESS BY INDEX ROWID SAMPLES (cr=391 pr=0 pw=0 time=19666 us) 359 NESTED LOOPS (cr=292 pr=0 pw=0 time=578919 us) 15 NESTED LOOPS (cr=58 pr=0 pw=0 time=1791 us) 41 TABLE ACCESS FULL CUSTOMER_INSTANCES (cr=15 pr=0 pw=0 time=759 us) 15 INDEX UNIQUE SCAN CUSTOMER_USER_INST_PRIVS_PK (cr=43 pr=0 pw=0 time=1588 us)(object id 49663) 343 INLIST ITERATOR (cr=234 pr=0 pw=0 time=40802 us) 343 INDEX RANGE SCAN SAMPLES_UK2 (cr=234 pr=0 pw=0 time=40979 us)(object id 49504) 14 TABLE ACCESS BY INDEX ROWID SAMPLES (cr=147 pr=0 pw=0 time=33644 us) 14 INDEX UNIQUE SCAN SAMPLES_PK (cr=133 pr=0 pw=0 time=33165 us)(object id 49501) 832469 INDEX FAST FULL SCAN ANALYSIS_RESULTS_PK (cr=3459 pr=0 pw=0 time=1665167 us)(object id 49571) 61 SQL That Ran Slower in 10g ? SQL noticeably slower in very few cases on 10g. ? A report ran unacceptably slower after the upgrade: – CPU time doubled. – Logical reads increased by order of magnitude. ? Slowdown attributed to one query (which runs many times): SELECT FROM mon_stat_names A, sample_sysstats B WHERE = :p_statname AND = AND = :p_sample_id。 (UNIQUE) 126110 INDEX MODE: ANALYZED (FAST FULL SCAN) OF 39。full_stat39。COMMON_SQL_PLAN_PARTS39。 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 32 SQL Generated by Oracle ? On our Oracle 10g database: SQL SELECT , COUNT(*), SUM () sharable_mem, 2 SUM () persistent_mem, 3 SUM () runtime_mem, 4 SUM ( + + ) 5 total_mem 6 FROM dba_users A, v$sql B 7 WHERE IN (39。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?