1 / 89

Oracle10g SQL Optimization

Oracle10g SQL Optimization. Dinesh Das Server Technologies Oracle Corporation. Agenda. Optimizer Enhancements More comprehensive cost model: includes CPU and I/O cost, dynamic sampling Query Transformations Automatic Statistics Gathering Automatic SQL Tuning Plan Table enhancements

zuri
Download Presentation

Oracle10g SQL Optimization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Oracle10g SQL Optimization Dinesh Das Server Technologies Oracle Corporation

  2. Agenda • Optimizer Enhancements • More comprehensive cost model: includes CPU and I/O cost, dynamic sampling • Query Transformations • Automatic Statistics Gathering • Automatic SQL Tuning • Plan Table enhancements • Numerous other improvements • Benefits for e-Business Suite

  3. Query Transformations

  4. Agenda • Why Query Transformation? • Transformations in 10g • Examples of selected transformations • Summary

  5. Why Query Transformation? • Goal: Enhance query performance • Semantically equivalent forms • Algebraic properties -- not always expressible in SQL

  6. Transformations • May span more than one query block • Sophisticated techniques • Automatic, heuristic-based, cost-based

  7. Transformations • Outer-join to Join • Common Sub-expression Elimination • Materialized View Rewrite • Subquery Unnesting • Simple and Complex View Merging • Predicate Move-Around • Join Predicate Push-down into View • Star Transformation • OR Expansion • Removal of Subsumed Subquery through Window Function • Distinct Push-down

  8. Subquery Unnesting • Non-declarative: multiple, redundant re-evaluation of subquery • Unnesting: subquery merged into the outer query or converted into an inline view • Unnesting: new access paths, join orders, join methods (anti-/semi-join) • A wide variety of unnesting • Any (IN), All (NOT IN), [NOT] EXISTS, correlated, uncorrelated, aggregated, group by • Some automatic; what used be heuristic-based are cost-based in 10g

  9. Unnesting NOT EXISTS SELECT c_acctbal FROM customer WHERE NOT EXISTS (SELECT * FROM orders WHERE o_custkey = c_custkey); SELECT c_acctbal FROM customer, orders WHERE c_custkey A= o_custkey;

  10. Unnesting aggregated subquery SELECT p_partkey, p_partname FROM lineitem, parts WHERE p_partkey = l_partkey AND p_container = ‘MED BOX’ AND l_quantity < (SELECT AVG(l_quantity) FROM lineitem WHERE l_partkey = p_partkey); SELECT p_partkey, p_partname FROM lineitem, parts, (SELECT AVG(l_quantity) AS avgqnt, l_partkey FROM lineitem GROUP BY l_partkey) V WHERE p_partkey = l_partkey AND p_container = ‘MED BOX’ AND l_quantity < V.avgqnt AND l_partkey = V.l_partkey;

  11. View Merging • Views describe business logic • View merging allows efficient access paths and join orders • Simple view (Select-Project-Join) View -- merged automatically • Complex view: aggregation / group by, distinct, or outer-join • Complex view merging used to be heuristic-based; cost-based in 10g

  12. SPJ View Merging SELECT t1.x, V.z FROM t1, t2, (SELECT t3.z, t4.m FROM t3, t4 WHERE t3.k = t4.k AND t4.q = 5) V WHERE t2.p = t1.p AND t2.m = V.m; SELECT t1.x, t3.z FROM t1, t2, t3, t4 WHERE t2.p = t1.p AND t2.m = t4.m AND t3.k = t4.k AND t4.q = 5;

  13. Complex View Merging SELECT ps_suppkey FROM partsupp, parts, SELECT SUM(l_quantity) AS V_sum, l_partkey AS V_lpk, l_suppkey AS V_lsk FROM lineitem WHERE l_linestatus = ‘M’ GROUP BY l_partkey, l_suppkey) V WHERE ps_partkey = p_partkey AND p_name LIKE ‘forest%’ AND V.V_lpk = ps_partkey AND V.V_lsk = ps_suppkey AND ps_availqty > V.V_sum; SELECT ps_suppkey FROM partsupp, parts, lineitem WHERE l_linestatus = ‘M’ AND ps_partkey = p_partkey AND p_name LIKE ‘forest%’ AND l_partkey = ps_partkey AND l_suppkey = ps_suppkey GROUP BY l_partkey, l_suppkey, parts.rowid, partsupp.rowid, ps_suppkey, ps_availqty HAVING ps_availqty > SUM(l_quantity);

  14. Predicate Move-Around • Generates filter predicates based on transitivity or on functional dependencies • Filter predicates move through SPJ, GROUP BY, DISTINCT views and views with OLAP constructs • Copies of filter predicates can move up, down, and across query blocks • Significantly reduces the data set of views • Automatically performed

  15. Predicate Move-Around Example SELECT V1.k1, V2.q, max1 FROM (SELECT t1.k AS k1, MAX(t1.a) AS max1 FROM t1, t2 WHERE t1.k1 = 6 AND t1.z = t2.z GROUP BY t1.k) V1, (SELECT t1.k AS k2, T3.q AS q FROM t1, t3 WHERE t1.y = t3.y AND t3.z > 4) V2 WHERE V1.k1 = V2.k2 AND max1 > 50; SELECT V1.x, V2.q, max1 FROM (SELECT t1.k AS k1, MAX(t1.a) AS max1 FROM t1, t2 WHERE t1.x = 6 AND t1.z = t2.z AND t1.a > 50 GROUP BY t1.k) V1, (SELECT t1.k AS k2, t3.q AS q FROM t1, t3 WHERE t1.y = t3.y AND t3.z > 4 AND t1.k2 = 6) V2 WHERE V1.k1 = V2.k2;

  16. Join Predicate Pushdown (JPPD) • Many types of view are unmergeable; e.g., views containing UNION ALL/UNION; anti-/semi-joined views; some outer-joined views • As an alternative, join predicates can be pushed inside unmerged views • A pushed-down join predicate acts as a correlating condition inside the view and opens up new access paths e.g., index-based nested-loop join • Decision to do JPPD is cost-based

  17. Join Predicate Pushdown Example SELECT t1.c, t2.x FROM t1, t2 (SELECT t4.x, t3.y FROM t4, t3 WHERE t3.p = t4.q AND t4.k > 4) V WHERE t1.c = t2.d AND t1.x = V.x (+) AND t2.d = V.y (+); SELECT t1.c, t2.x FROM t1, t2 (SELECT t4.x, t3.y FROM t4, t3 WHERE t3.p = t4.q AND t4.k > 4 AND t1.x = t4.x AND t2.d = t3.y) V WHERE t1.c = t2.d;

  18. Summary • Wide variety of transformations • Transparent to DBAs and users • Sophisticated techniques for semantic analyses and search space exploration • Intelligent choice of automatic, heuristic-based or cost-based transformation

  19. Automatic Statistics Gathering

  20. Manageability Architecture Application & SQL Management Storage Management System Resource Management Space Management Backup & Recovery Management ADDM Database Control (Enterprise Manager) Manageability Infrastructure

  21. Agenda • Why Gather Statistics? • Why Automatic Gathering? • Modification/Column Usage Monitoring • Statistics Gathering Job • Automatic Statistics Gathering in Index Creation/Rebuild • Locking and Unlocking Statistics • Saving and Restoring Old Statistics

  22. Why Gather Statistics? • Statistics are important for optimizer to build good execution plans • Optimizer costs plans using statistics as inputs • Important to keep statistics up-to-date

  23. Why Automatic Gathering? • Difficult to maintain accurate statistics for many objects • Time consuming: • Some objects may not have to be analyzed often • Too large sample size • Inaccurate: • Too small sample size • Difficult to determine the columns requiring histograms 10g automates the statistics gathering process!

  24. Modification Monitoring • Keep track of approximate amount of modifications since last analyze(unit: number of rows) • Inserts • Deletes • Updates • Truncate

  25. Column Usage Monitoring • Keep track of columns that have been used in different predicate types • Equality • Range • Equi-join • Non Equi-join • LIKE • IS [NOT] NULL

  26. Statistics Gathering Job • Predefined DB Scheduler Job • Run in predefined Maintenance Window • Scheduler terminates the job if it does not finish before the window closes • Restartable

  27. Statistics Gathering Job • Gather statistics for: • Objects with missing or stale statistics • Dictionary objects as well as user’s

  28. Statistics Gathering Job • Automatically determines: • Objects with Stale Statistics • More than 10% of rows modified or the table was truncated since last analyze • Sample size: Iteratively increase sample size until desired accuracy is attained • Histograms • Auto-Cursor Invalidation • Invalidate dependent cursors over time • Prevent compilation spikes

  29. Auto-Histogram Gathering • Decisions made based on predicate types and type of skews • Frequency skews: Both equality and range predicatese.g. 1, 1, 1, 1, 2, 3, 4, 4, 4, 4, 4, 4, 5, 5, 6,… • Range skews: Only range predicatese.g. 1, 2, 3, 4, 5, 100, 5000, 10000, 10001,… • Column usage monitoring

  30. Statistics Gathering Job • Processing order: • Objects with missing stats first, then from highest to lowest staleness • Smaller ones before larger ones within each staleness group • Progressively de-prioritize potentially very expensive-to-analyze objects • If scheduler terminates the job before it finishes… • Dump list of unprocessed objects in the trace file • Print a brief note in the alert log • Unprocessed objects will be picked up again

  31. Statistics Gathering Job Staleness • Processing order: High Low

  32. Statistics Gathering Job • De-prioritization: Window - 2 Window - 3 Window - 1 1 4 5 2 3 6 3 3 5 4 6 7 5 7 6 7

  33. Index Creation & Rebuild • Automatically gather index statistics during CREATE INDEX • Negligible overhead

  34. Locking and Unlocking statistics • Object statistics can be manually locked and unlocked • Auto stats collection skips locked objects • Typical usage: • Gather stats and lock -> representative statistics for staging tables • Restore stats and lock -> temporarily fixes the plan

  35. Saving and Restoring Statistics • Previous version of statistics are saved whenever statistics are updated in dictionary • Restore optimizer statistics as of a timestamp in past • Used for getting back old execution plans • Restore works at table, schema and database levels • Automatic purging of old statistics versions

  36. Summary • Statistics are key to choosing optimal plan • 10g automates the decision of when to analyze, what to analyze and what types of statistics to collect • Statistics versioning allows easy restoration in case of plan regressions

  37. Automatic SQL Tuning

  38. Agenda • Introduction • SQL Tuning • SQL Tuning Advisor • Automatic SQL Tuning • Usage scenarios • User interface • SQL Access Advisor • Usage scenarios • User interface • Summary

  39. Introduction • Automatic SQL Tuning solution consists of • SQL Tuning Advisor • SQL Access Advisor • Provides comprehensive, automatic, and cost-effective solution for application tuning • Reduces SQL tuning time by up to 80% • Reduces management cost

  40. SQL Tuning: Why? • Few SQL statements can be responsible for a large share of the system database timehigh-load SQL • Find better plan for these SQL statements • The Optimizer missed the optimal plan • Limited time to optimize • Limited knowledge about underlying data • Limited knowledge about execution environment • Key access structure(s) missing • SQL statement poorly written

  41. SQL Tuning: Challenges • Requires expertise in several domains • SQL optimization: adjust the execution plan • Access design: provide fast data access • SQL design: use appropriate SQL constructs • Time consuming • Each SQL statement is unique • Potentially large number of statements to tune • Never ending task • SQL workload always evolving • Plan regressions due to changes in system

  42. SQL Tuning: Manual Process How can I selecthigh-load SQL? How can I tune high-load SQL? High-LoadSQL DBA DBA SQL Workload

  43. Oracle 10g Automates the SQL Tuning Process I can do it for you ! ADDM DBA High-LoadSQL SQL Workload SQL Tuning Advisor

  44. Automatic SQL Tuning: Architecture SQL Tuning Recommendations Automatic Tuning Optimizer Gather Missing or Stale Statistics Create a SQL Profile SQL Profiling SQL Tuning Advisor Access Path Analysis DBA Add Missing Indexes SQL Structure Analysis Modify SQL Structure

  45. Automatic Tuning Optimizer • The Oracle query optimizer running in tuning mode • Given a lot more time than in the regular mode • Uses the extra time to: • Find a better plan within the regular search space • “Profile” the SQL statement • Explore plans beyond the regular search space using what-if analyses • Investigate the use of new access paths (indexes) • Investigate restructuring of SQL that may improve the plan (different from transparent query transformations)

  46. SQL Profiling SQL Tuning Recommendations Automatic Tuning Optimizer Gather Missing or Stale Statistics Create a SQL Profile SQL Profiling SQL Tuning Advisor Access Path Analysis DBA Add Missing Indexes SQL Structure Analysis Modify SQL Structure

  47. SQL Profiling: Overview • Motivation • Empower the query optimizer to find a better plan • The query optimizer • Has time constraint so it makes compromises while finding a best plan • The Automatic Tuning Optimizer • Uses time to gather customized information about the SQL statement and build a SQL Profile • SQL Profile • Once stored, it is used by the regular query optimizer to produce a well-tuned plan

  48. Plan 1 Plan 1 Plan 3 Plan 3 Plan 3 Plan 1 Plan 2 Plan 2 Plan 2 ? SQL Profile ? ? ? ? Search Space Search Space SQL Profiling: Concept No SQL Profile Collect SQL Profile Use SQL Profile use ? ? add ? ? ? ? ? Search Space • Validates estimates using dynamic sampling and partial execution • Validates only relevant estimates

  49. SQL Profile Object • Contains customized information collected for a SQL statement • Appropriate optimizer settings • Based on execution statistics (e.g., first_rows vs. all_rows) • Compensation for missing or stale statistics • Compensation for errors in optimizer estimates • Remove estimation errors due to data skews, correlations, complex filters and joins • Persistent • Create once, use often • It’s use doesn’t require any change to the SQL (ideal for Packaged Apps)

  50. SQL Profiling: Usage Model SQL Profiling (collect phase) submit create Optimizer (Tuning Mode) SQL Profile SQL Tuning Advisor use After … (use phase) output submit Optimizer (Normal Mode) Well-Tuned Plan DatabaseUsers

More Related