【正文】 62 Sample Stats Query ? On our Oracle 8i database: call count cpu elapsed disk query current rows Parse 1 0 0 0 0 Execute 1 0 0 0 0 Fetch 2 0 6 0 1 total 4 0 6 0 1 Rows Execution Plan 0 SELECT STATEMENT MODE: CHOOSE 1 NESTED LOOPS 2 INDEX MODE: ANALYZED (RANGE SCAN) OF 39。CUSTOMER_USER_INST_PRIVS_PK39。ANALYSIS_TESTS39。, 39。 TABLE_NAME NUM_ROWS MB SAMPLE_PCT SAMPLE_DATA_FILES 14,938,632 1, SAMPLE_JOBS 1,360,429 COMMON_SQL_PLAN_PARTS 174,851 SAMPLE_LIBRARY_CACHE_STATS 1,414,830 SAMPLE_SQL_TEXTS 6,346,638 39 Sample Size ? How Oracle 10g came to sample every row in a 760 Mb table: – First, Oracle sampled all 35 columns of the table on % of the rows. – Next, Oracle sampled 8 of the columns on % of the rows. – Next, Oracle sampled 3 of the columns on % of the rows. – Finally, Oracle performed a COUNT (DISTINCT) on one of the columns without a SAMPLE clause. 40 Optimizer Statistics: Accuracy ? Oracle 10g optimizer statistics did not appear to be particularly more accurate than those collected by ANALYZE in Oracle 8i. ? In particular Oracle 10g’s estimate of distinct column values was sometimes less accurate than Oracle 8i’s. – Could have been caused by excessively small sample size on some tables (…just a guess) 41 Optimizer Statistics: Accuracy ? How accurate do optimizer statistics need to be? – If every business process on your system gives satisfactory response time, then the statistics are accurate enough. – But if a business process runs too slowly, can you blame the optimizer statistics? ? We will see some queries that got unsatisfactory execution plans in our Oracle 10g test environment. – Is it the statistics? We don’t know. 42 Query Optimization ? Queries in our application follow an OLTP workload model. – All run quickly (except for quarterly purge). – Quick, but some are plex. ? We believe we’ve written practical, logical SQL. ? Oracle 8i ran most of our SQL efficiently: – We added hints to SQL only when response time concerns arose. – About 50 statements throughout the application have hints. 43 Query Optimization ? Did not expect things to run faster in Oracle 10g. – Queries already had efficient execution plans in 8i. – We expect the gains to e when we leverage Oracle 9i and 10g new features. ? Concern: What if some queries run slower in Oracle 10g? – In a business process with 100 SQL statements, it only takes one bad execution plan to slow the whole process down. 44 The Executive Summary ? Most SQL in our application consumed roughly the same CPU time and number of logical reads in Oracle 10g as in Oracle 8i. ? Some statements ran a little faster, and a few ran a little slower. ? Most workload operations yielded similar response times in both versions of Oracle. ? Only a very few SQL statements were slow enough on Oracle 10g to cause concern. 45 Query Optimizer Challenge ? Could Oracle 10g find efficient execution plans for the queries that required hints in Oracle 8i? – Is adding hints to queries a thing of the past? ? Well… not yet: – Oracle 10g ran the troublesome queries faster without hints than Oracle 8i without hints. – However, both versions of Oracle ran the queries faster with hints than Oracle 10g did without hints. 46 Query Optimization in Detail ? SQL that ran similarly in Oracle 8i and 10g ? SQL that ran faster in Oracle 10g ? SQL that ran faster in Oracle 8i 47 SQL That Ran Similarly ? Loader Daemon parison ? Performance Summary report parison ? See the white paper for TKPROF report excerpts 48 Loader Daemon Comparison ? Loader Daemon parses, validates, and loads files from our monitoring agents into the database for analysis and reporting. ? PL/SQL package roughly 7,800 lines long. ? 7 SQL statements in the package have hints. ? Starting out with the same data in the Oracle 8i and 10g test databases, we traced the Loader Daemon on each database while loading the same agent file into each. 49 Loader Daemon Comparison Resources Used by Loader Daemon to Load One Agent File Oracle 8i Oracle 10g User SQL statements traced 110 127 Internal SQL statements traced 9 9 Unique SQL statements traced 109 110 Total OCI calls 1,800 1,792 CPU seconds Logical reads 13,767 12,920 Physical reads 6 13 50 Loader Daemon Comparison ? Business process gave roughly same response time and load profile on Oracle 8i and 10g. ? Fewer logical reads on Oracle 10g: – Import made 10g segments more pact. ? More user SQL statements traced on Oracle 10g: – Oracle 10g database had smaller PL/SQL cursor cache due to behavior change implemented in re open_cursors. (See Metalink document .) – Cache misses lead to extra (soft) parse calls. – TKPROF reported these extra parse calls as extra traced statements. 51 Performance Report Comparison ? Performance Summary report provides a summary of performance statistics for one monitored Oracle database over a specified period of time (like a Statspack report). ? PL/SQL package roughly 3,200 lines long. ? 4 SQL statements in the package have hints. ? Starting out with the same data in the Oracle 8i and 10g test databases, we traced sessions that called the report with the same parameters on each database. 52 Performance Report Comparison