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