【正文】 SAMPLES39。 AND = ORDER BY severity, first_detected DESC, inst_name。, 7 39。SYS39。 ACTUAL_POOL_SIZE SQL SHOW PARAMETER shared_pool_size NAME TYPE VALUE shared_pool_size string 41943040 ? We’ve seen the disparity as high as 27%. 26 Shared SQL Area Memory Usage ? Individual 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 don’t know. 27 Shared SQL Area Memory Usage ? On our Oracle 8i 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。DBRX_OWNER‘ 8 AND = 9 GROUP BY 。, 39。SAMPLE_LIBRARY_CACHE_STATS39。 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 Oracle 8i Without Hint Rows Execution Plan 0 SELECT STATEMENT MODE: CHOOSE 0 SORT (ORDER BY) 0 FILTER 7093 HASH JOIN 71 TABLE ACCESS MODE: ANALYZED (FULL) OF 39。 42 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 39。 (NONUNIQUE) 42 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 39。event39。SAMPLE_SQL_TEXTS39。, 39。shared pool39。 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。SYSTEM39。) 8 AND = 39。LOOKUP_REPORT_40000_FORMATS39。SAMPLES_PK39。ANALYSIS_COMMON_RESULTS_N139。) AND ( SELECT ( / 24) FROM samples s2 WHERE = () ) AND = AND = AND = AND = 39。, 39。DBSNMP39。ve done my share of Oracle installs over the years, and honestly this was one of the smoother ones. (Note: Solaris platform!) 14 Impressions: Compatibility ? Encountered two patibility issues: – EXTPROC needed reconfiguring (tighter security) and repiling (32 bit to 64 bit change). – Oracle 10g PLSQL piler 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 interoperability issues encountered. 15 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 ORA600s or other funnies. – Caveat: We are using few Oracle 9i and bare minimum Oracle 10g new features. 16 Impressions: Oracle 10g ? Bigger, bulkier, hungrier for system resources: – Bigger executable size, shared pool, SYSTEM tablespace… ? More overhead: – Daemon processes, hard parses, statistics collection… ? Overhead and bulkiness were tolerable for us. 17 Impressions: Oracle 10g ? Application 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 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. 18 Impressions: 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 manageable—a 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!) 19 Upgrade Issues in Greater Detail ? 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 S