【正文】 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。full_stat39。y39。admin39。SAMPLE_LIBRARY_CACHE_STATS39。COMMON_SQL_PLAN_PARTS39。SAMPLE_DATA_FILES39。) 8 AND = 9 GROUP BY 。, 39。 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。SYSTEM39。DBSNMP39。DBRX_OWNER‘ 8 AND = 9 GROUP BY 。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? – Oracle 8i desupport. (What difference does it make?) – Gain Oracle 10g experience. (For us, a more pelling reason.) 11 Our Upgrade Strategy ? Restore production hot backup onto dedicated test server. ? Export Oracle 8i test database and import into empty Oracle 10g test database. ? Why export/import instead of upgrading in place? – Switch all tablespaces to LMTs – Compact all application segments (purges left holes) – Change character set – ―Fresh‖ data dictionary, database ponents – Worked out a strategy to keep the down time tolerable 12 Our Upgrade Strategy ? Our Oracle 8i and 10g test databases started out with the same data—handy for testing and parison. ? Two critical points to remember when paring these two test databases: – Application segments in Oracle 10g test database occupied fewer blocks. – Our Oracle 10g test database was 64 bit while our Oracle 8i test database was 32 bit. 13 Impressions: Upgrade Process ? Oracle 10g version and patch set installed very smoothly. ? Oracle 10g import utility read our Oracle 8i export file with no issues. ? Oracle 10g Upgrade Information Tool accurately pointed out necessary parameter changes. ? I39。1 An Oracle 10g Upgrade Case Study: Looking at System Performance Before and After the Upgrade Roger Schrag Database Specialists, Inc. NoCOUG Spring Conference 2021 2 Today39。s session that we won39。 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。 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. 3