【正文】 a table with 800,000 rows when nested loops was the right way to go. ? With the hint, Oracle 10g did better than Oracle 8i (with the hint) by performing the subquery as early as possible instead of as late as possible. 59 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。LOOKUP_REPORT_40000_FORMATS39。 7092 HASH JOIN 4 TABLE ACCESS MODE: ANALYZED (FULL) OF 39。ANALYSIS_TESTS39。 512382 HASH JOIN 512382 NESTED LOOPS 832470 HASH JOIN 465504 HASH JOIN 41 TABLE ACCESS MODE: ANALYZED (FULL) OF 39。CUSTOMER_INSTANCES39。 465504 TABLE ACCESS MODE: ANALYZED (FULL) OF 39。SAMPLES39。 832469 INDEX MODE: ANALYZED (FAST FULL SCAN) OF 39。ANALYSIS_RESULTS_PK39。 (UNIQUE) 512382 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 39。CUSTOMER_USER_INST_PRIVS_PK39。 (UNIQUE) 126110 INDEX MODE: ANALYZED (FAST FULL SCAN) OF 39。ANALYSIS_COMMON_RESULTS_N139。 (NONUNIQUE) 42 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 39。SAMPLES39。 42 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 39。SAMPLES_PK39。 (UNIQUE) 60 Oracle 10g Without Hint Rows Row Source Operation 0 SORT ORDER BY (cr=4212 pr=0 pw=0 time=3573213 us) 0 HASH JOIN (cr=4212 pr=0 pw=0 time=3573077 us) 71 TABLE ACCESS FULL LOOKUP_REPORT_40000_FORMATS (cr=3 pr=0 pw=0 time=489 us) 0 HASH JOIN (cr=4209 pr=0 pw=0 time=3562021 us) 4 TABLE ACCESS FULL ANALYSIS_TESTS (cr=18 pr=0 pw=0 time=853 us) 243 HASH JOIN (cr=4191 pr=0 pw=0 time=3554047 us) 126110 INDEX FAST FULL SCAN ANALYSIS_COMMON_RESULTS_N1 (cr=341 pr=0 pw=0 time=126363 us)(object id 49302) 243 HASH JOIN (cr=3850 pr=0 pw=0 time=2830427 us) 343 TABLE ACCESS BY INDEX ROWID SAMPLES (cr=391 pr=0 pw=0 time=19666 us) 359 NESTED LOOPS (cr=292 pr=0 pw=0 time=578919 us) 15 NESTED LOOPS (cr=58 pr=0 pw=0 time=1791 us) 41 TABLE ACCESS FULL CUSTOMER_INSTANCES (cr=15 pr=0 pw=0 time=759 us) 15 INDEX UNIQUE SCAN CUSTOMER_USER_INST_PRIVS_PK (cr=43 pr=0 pw=0 time=1588 us)(object id 49663) 343 INLIST ITERATOR (cr=234 pr=0 pw=0 time=40802 us) 343 INDEX RANGE SCAN SAMPLES_UK2 (cr=234 pr=0 pw=0 time=40979 us)(object id 49504) 14 TABLE ACCESS BY INDEX ROWID SAMPLES (cr=147 pr=0 pw=0 time=33644 us) 14 INDEX UNIQUE SCAN SAMPLES_PK (cr=133 pr=0 pw=0 time=33165 us)(object id 49501) 832469 INDEX FAST FULL SCAN ANALYSIS_RESULTS_PK (cr=3459 pr=0 pw=0 time=1665167 us)(object id 49571) 61 SQL That Ran Slower in 10g ? SQL noticeably slower in very few cases on 10g. ? A report ran unacceptably slower after the upgrade: – CPU time doubled. – Logical reads increased by order of magnitude. ? Slowdown attributed to one query (which runs many times): SELECT FROM mon_stat_names A, sample_sysstats B WHERE = :p_statname AND = AND = :p_sample_id。 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。COMMON_STAT_NAMES_PK39。 (UNIQUE) 1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 39。SAMPLE_SYSSTATS_PK39。 (UNIQUE) 63 Sample Stats Query ? On our Oracle 10g database: call count cpu elapsed disk query current rows Parse 1 0 0 0 0 Execute 1 0 0 0 0 Fetch 2 0 244 0 1 total 4 0 244 0 1 Rows Row Source Operation 1 NESTED LOOPS (cr=244 pr=0 pw=0 time=893 us) 234 INDEX RANGE SCAN SAMPLE_SYSSTATS_PK (cr=5 pr=0 pw=0 time=1152 us) 1 INDEX RANGE SCAN COMMON_STAT_NAMES_UK1 (cr=239 pr=0 pw=0 time=9472 us) 64 Sample Stats Query ? Who cares about a second query? – Suppose the query runs 50+ times each time a popular report is viewed? ? Adding an ORDERED hint to the query made Oracle 10g choose the correct execution plan. ? The same exact behavior occurred in both our test and production Oracle 10g environments. ? Both tables in the query are IOTs. – Oracle has determined this is ―a problem with the optimizer caching cost model.‖ 65 SQL Tuning Advisor ? Cool sounding Oracle 10g feature that studies a query and makes remendations: – You tell Advisor how long to study the query. – Advisor could remend rewrite. – Advisor could collect additional statistics that can be saved in data dictionary as a ―profile‖ to be used whenever the statement is parsed in the future. ? Opens the door to fixing bad queries without modifying the application code. 66 SQL Tuning Advisor ? We had already added hints to all queries that ran unacceptably slow. ? We’ve already discussed that taking those hints away in Oracle 10g led to inferior response times. ? So what if we took the hints away and let the SQL Tuning Advisor remend a solution for each troublesome query? 67 Recent Event Notif