【正文】 o 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 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。shared pool39。 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 。 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。DBRX_OWNER‘ 8 AND = 9 GROUP BY 。 USERNAME COUNT(*) SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM TOTAL_MEM DBRX_OWNER 360 12,941,006 487,048 3,361,160 16,789,214 29 SQL 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 selfmanagement infrastructure in Oracle 10g (database and EM Database Control) generates a lot of internal SQL. ? The shared pool will need to be larger in order to acmodate the extra statements. 30 SQL 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. ? 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. 31 SQL Generated by Oracle ? 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 IN (39。DBSNMP39。, 39。SYS39。, 39。SYSTEM39。, 39。SYSMAN39。) 8 AND = 9 GROUP BY 。 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