【正文】 TABLE ACCESS MODE: ANALYZED (FULL) OF 39。 (NONUNIQUE) 42 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 39。SAMPLE_SYSSTATS_PK39。 42 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 39。 832469 INDEX MODE: ANALYZED (FAST FULL SCAN) OF 39。 57 Recent Event Notifications Resources Used by Recent Event Notifications Query Query With Hint Query Without Hint Oracle 8i Oracle 10g Oracle 8i Oracle 10g CPU seconds Logical reads 2,208 1,451 1,678,011 4,111 Physical reads 7 0 27,551 0 58 Recent Event Notifications ? Without the hint, Oracle 10g did a better job than Oracle 8i—but still not good enough: – Good: Oracle 10g figured out the right time to perform the subquery. – Bad: Oracle 10g chose a hash join to 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。y39。SAMPLE_LIBRARY_CACHE_STATS39。SAMPLE_DATA_FILES39。, 39。SYSTEM39。DBRX_OWNER‘ 8 AND = 9 GROUP BY 。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。 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。, 39。SYS39。 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。, 7 39。) AND = AND = 39。 AND = ORDER BY severity, first_detected DESC, inst_name。SAMPLES39。SAMPLES39。 (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 N