【正文】 (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。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。shared pool39。DBRX_OWNER‘ 8 AND = 9 GROUP BY 。SYS39。SYSMAN39。, 39。, 39。 ? 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. 34 Optimizer Statistics: Cost ? Automatic statistics collection in Oracle 10g is more resource intensive than ANALYZE was in Oracle 8i: Resources Used to Collect Optimizer Statistics Oracle8i (ANALYZE) Oracle 10g (automatic) CPU seconds 1,101 2,595 Elapsed seconds 2,044 5,244 Logical reads 597,717 73,082,675 Physical reads 545,844 2,926,625 35 Histogram Creation ? Histograms 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。SAMPLE_JOBS39。SAMPLE_SQL_TEXTS39。TABLE‘ 9 AND = 10 ORDER BY sample_pct。read only39。ping39。event39。 7092 HASH JOIN 4 TABLE ACCESS MODE: ANALYZED (FULL) OF 39。 465504 TABLE ACCESS MODE: ANALYZED (FULL) OF 39。 (UNIQUE) 512382 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 39。 (NONUNIQUE) 42 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) 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。SAMPLE_SYSSTATS_PK39。COMMON_STAT_NAMES_PK39。 42 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 39。 (UNIQUE) 126110 INDEX MODE: ANALYZED (FAST FULL SCAN) OF 39。 832469 INDEX MODE: ANALYZED (FAST FULL SCAN) OF 39。 512382 HASH JOIN 512382 NESTED LOOPS 832470 HASH JOIN 465504 HASH JOIN 41 TABLE ACCESS MODE: ANALYZED (FULL) OF 39。 57 Recent Event Notifications Resources Used by Recent Event Notifications Query Query With Hint Query Without Hint Oracle 8i Oracle 10g Oracle 8i Oracle 10g CPU seconds Logical reads 2,208 1,451 1,678,011 4,111 Physical reads 7 0 27,551 0 58 Recent Event Notifications ? Without the hint, Oracle 10g did a better job than Oracle 8i—but still not good enough: – Good: Oracle 10g figured out the right time to perform the subquery. – Bad: Oracle 10g chose a hash join to a table with 800,000 rows when nested loops was the right way to go. ? With the hint, Oracle 10g did better than Oracle 8i (with the hint) by performing the subquery as early as possible instead of as late as possible. 59