【正文】 1 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。, 39。) 8 AND = 9 GROUP BY 。SYS39。SYSMAN39。 HISTOGRAM COUNT(*) FREQUENCY 267 HEIGHT BALANCED 74 NONE 1202 sum 1543 36 Histogram Creation ? If a column has ever been used in a WHERE clause, Oracle 10g will consider creating a histogram for it (note col_usage$): – FREQUENCY histograms for low cardinality columns – HEIGHT BALANCED histograms for columns with gaps or skewed data distribution ? Many of the histograms won’t be useful: – On unindexed columns that only appear in WHERE clauses alongside a selective, indexed column – On columns that rarely appear in WHERE clauses 37 Sample Size ? Sample size is another reason statistics collection in Oracle 10g was so much more expensive. ? Oracle 8i sample sizes were consistent: – Sample sizes on tables over 1 Mb were to %. – Sample sizes on smaller tables were 100%. ? Oracle 10g sample sizes were all over the map: – Sample size on 80 Mb table: 100% – Sample size on 1,088 Mb table: % – Sample size on 760 Mb table: 100% 38 Sample Size ? On our Oracle 10g database: SQL SELECT , , / 1024 / 1024 mb, 2 100 * ( / ) sample_pct 3 FROM user_tables A, user_segments B 4 WHERE IN 5 (39。, 6 39。, 7 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