【正文】 etail ? Sizing the shared pool and SGA ? Optimizer statistics collection and accuracy ? Query optimization ? SQL Tuning Advisor ? Overhead 20 Sizing 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 ? Oracle 8i performance characteristics: – 50,000 lines of PL/SQL code – 1520 executions per second – Under 660 hard parses per day – Buffer cache hit ratio 97% – Library cache hit ratio ~100% 21 Sizing the Shared Pool and SGA ? 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。, 39。DBSNMP39。 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 sum 45,742,227 1,987,304 14,498,528 62,228,059 33 Optimizer Statistics ? Collected optimizer statistics weekly in Oracle 8i: ANALYZE TABLE table_name ESTIMATE STATISTICS SAMPLE 5 PERCENT。, 39。, 39。) AND ( SELECT ( / 24) FROM samples s2 WHERE = () ) AND = AND = AND = AND = 39。CUSTOMER_INSTANCES39。ANALYSIS_COMMON_RESULTS_N139。 (UNIQUE) 1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 39。SAMPLES_PK39。ANALYSIS_RESULTS_PK39。LOOKUP_REPORT_40000_FORMATS39。 AND = AND IN (39。) 8 AND = 39。, 39。SYSTEM39。, 39。 USERNAME COUNT(*) SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM TOTAL_MEM DBRX_OWNER 362 6,275,020 256,176 1,996,324 8,527,520 28 Shared SQL Area Memory Usage ? 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 = 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。shared pool39。SYS39。, 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_SQL_TEXTS39。read only39。event39。 465504