【正文】 Resources Used by Performance Summary Report Oracle 8i Oracle 10g User SQL statements traced 98 98 Internal SQL statements traced 10 10 Unique SQL statements traced 98 97 Total OCI calls 654 531 CPU seconds Logical reads 4,641 3,661 Physical reads 1 0 53 Performance Report Comparison ? Business process gave roughly same response time and load profile on Oracle 8i and 10g. ? Fewer logical reads on Oracle 10g again. ? Fewer total OCI calls in Oracle 10g: – Same number of parse and execute calls. – Oracle 8i had twice as many fetch calls as 10g. – It appears as if Oracle 8i did extra fetch calls to make sure it had retrieved all rows from a cursor, while perhaps Oracle 10g asked for more rows up front. 54 SQL That Ran Faster in 10g ? We did not expect noticeable response time improvements on Oracle 10g because everything already ran ―fast enough‖ on 8i. ? We removed the hints from queries that had been slow in Oracle 8i to see if Oracle 10g could find the right execution plan. ? In several cases Oracle 10g did better than 8i did without hints, but 10g’s execution plan was still far inferior to that chosen when the hints were in place. 55 Recent Event Notifications ? Query appears in several reports. ? Retrieves a list of recent event notifications for all databases to which the specified user has access. ? Joins 7 tables and includes a subquery. ? To get the query to run efficiently in Oracle 8i we had added a hint to specify join order and which join algorithm to use for each table. ? Not a trivial query, nor the most plex. 56 Recent Event Notifications SELECT /*+ ORDERED INDEX (privs) USE_NL (i s ar acr) USE_HASH (t l) */ severity, , NVL (, ) inst_name, , brief_description, FROM customer_user_instance_privs privs, customer_instances i, samples s, analysis_results ar, analysis_mon_results acr, analysis_tests t, lookup_report_40000_formats l WHERE = :cp_user_id AND IN (39。, 6 39。SYSMAN39。) 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。s session that we won39。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 1