Oracle10g SQL Optimization

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 lt (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 lt 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 gt 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

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 gt 4) V2
• WHERE V1.k1 V2.k2 AND max1 gt 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 gt 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 gt 4 AND
  t1.k2 6) V2

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 gt 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 gt 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 users

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 -gt representative
  statistics for staging tables
• Restore stats and lock -gt 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
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
• Its use doesnt 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
51
Access Path Analysis
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
52
Access Path Analysis

• Motivation
• Adding a critical access path may significantly
  improve the performance of a SQL statement
• Explore the use of new access paths (indexes)
• Generate index candidates and evaluate them along
  with existing indexes
• If an index candidate provides significant
  performance boost, recommend to
• Add new index
• Run Access Advisor to get a comprehensive
  workload-based Access recommendations

53
SQL Structure Analysis
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
54
SQL Structure Analysis

• Motivation
• Help application developers identify poorly
  written SQL statements
• Cost-based analysis of plan node operators
• Find methods of reducing the plan cost through
  possible restructuring of SQL statement
• Caveat Subject to user acceptance of possibly
  new result
• Several restructuring methods explored
• Semantic changes of SQL operators (e.g., UNION
  ALL instead of UNION)
• SQL design issues (e.g., add missing join
  predicate to fix an accidental Cartesian product)

55
Usage Scenarios
Automatic Selection
Workload Repository
ADDM
High-load SQL
SQL Sources
Manual Selection
W. Repository
Automatic SQL Tuning
SQL Cache
SQL Tuning Set(STS)
Filter / Rank
User-defined
56
SQL Tuning Advisor User Interface

• GUI Enterprise Manager
• Launch SQL Tuning Advisor from a SQL Source page
• ADDM Finding page, or
• Top SQL page, or
• SQL Tuning Set (STS) page
• View SQL Tuning Recommendations
• Implement SQL Tuning Recommendations
• Command line dbms_sqltune package

57
SQL Source ADDM Finding
58
SQL Source Top SQL
59
SQL Tuning Options
60
SQL Tuning Results
61
Recommendations Details
62
Implement Recommendation
63
Automatic vs. Manual SQL Tuning

• Manual Tuning
• Get explain plan
• Examine query objects and their sizes
• Review and compare explain plan statistics with
  execution statistics (stored in VSQL view)
• Identify the problem, e.g., first rows issue
  because only recent data is ever displayed
  despite large history being queried
• Contact application vendor
• Produce test case for vendor
• Get a patch with appropriate code modifications
  from the vendor
• Install the patch in next maintenance cycle

• Automatic Tuning
• Run SQL Tuning Advisor
• Implement recommendations.

64
SQL Access Advisor
SQL Access Advisor
Workload
Indexes
Materialized Views
Materialized View Logs
65
SQL Access Advisor Features

• De-mystifies access structure design for optimal
  application performance
• Recommends indexes, materialized views, and
  materialized view logs to create and/or drop for
  faster performance
• Analyzes entire workload and not just independent
  SQL statements
• Takes into account impact of new access
  structures on DML operations
• Considers storage, creation and maintenance costs

66
SQL Access Advisor Features

• Simultaneously considers
• index solutions
• materialized view solutions
• combinations of both
• Optimizes materialized views for
• maximum query rewrite usage
• fast refresh
• Recommends materialized view logs for fast
  refresh
• Combines similar indexes into single index

67
Usage Scenarios
SQL Access Advisor
68
SQL Access Advisor User Interface

• GUI Enterprise Manager
• Launch SQL Access Advisor from Advisor Central
• Select workload source
• Set options
• Workload
• Recommendation
• Advanced
• Schedule job
• Review job and submit
• Monitor job
• View Recommendations
• Implement Recommendations
• Command line DBMS_ADVISOR package

69
Launch SQL Access Advisor
70
Select Workload Source
71
Set Workload Options
72
Set Recommendation Options
73
Advanced Options
74
Advanced Tuning Options

• SQL statements will be tuned according to the
  resources they use

75
Schedule Job
76
Review Submit
77
Monitor the Job
78
View Recommendations
79
Implement Recommendations
80
Automatic SQL Tuning Summary

• SQL Tuning Advisor and SQL Access Advisor
• together completely automate SQL tuning

Analysis Types Performed By
Statistics SQL Tuning Advisor
SQL Profile SQL Tuning Advisor
SQL Structure SQL Tuning Advisor
Access Path Indexes SQL Tuning/Access Advisor
Access Path Materialized Views SQL Access Advisor
Access Path Materialized View Logs SQL Access Advisor
81
Summary

• Oracle 10g eliminates one of the biggest DBA
  challenges SQL tuning
• Provides targeted, precise, and automatic tuning
• Makes possible comprehensive tuning of packaged
  applications
• Eliminates need for highly skilled performance
  experts
• Delivers immediate ROI by
• Reducing management cost
• Lowering system down-time
• Enhancing DBA productivity

82

• Plan Table Enhancements

83
Plan Table Enhancements

• New package dbms_xplan
• Formatted reports with varying levels of detail
• Notes section List used features (RBO, dynamic
  sampling, SQL profile, )
• Shows execution statistics at operation level
  (actual number of rows, elapsed time, I/Os, )
• Source plan may be in plan_table, vsql_plan,
  workload repository
• Plan hash value in plan_table
• Quick visual plan comparison
• SQL-based plan dif analysis

84

• SELECT / gather_plan_statistics / cust_city,
  AVG(quantity_sold)
• FROM sales, customers
• WHERE sales.cust_id customers.cust_id
• GROUP BY cust_city
• SELECT
• FROM TABLE(dbms_xplan.display_cursor(NULL, NULL,
  'RUNSTATS_LAST'))
• Plan hash value 3662534920
• --------------------------------------------------
  --------------------------------------
• Id Operation Name E-Rows
  A-Rows Buffers Reads Writes
• --------------------------------------------------
  -------------------------------------
• 1 HASH GROUP BY 300
  300 64 13 0
• 2 HASH JOIN 960
  960 64 13 0
• 3 TABLE ACCESS FULL CUSTOMERS 630
  630 16 13 0
• 4 PARTITION RANGE ALL 960
  960 48 0 0
• 5 TABLE ACCESS FULL SALES 960
  960 48 0 0
• --------------------------------------------------
  --------------------------------------

85

• E-Business Suite Benefits

86
10g Apps Certification

• Oracle Applications releases which will be
  certified with 10gR1 (10.1.0.3/10.1.0.4)
• 11.5.9 (with Apps interoperability patch)
• 11.5.10.
• Targeted for October 2004.
• RBO no longer supported.
• Custom code relying on the RBO must be migrated
  to the CBO. Refer to the Metalink note 222627.1
  for details on the migration steps.

87
Benefits for e-Business Suite

• Simplified init.ora configuration.
• Refer to Metalink note 216205.1 for Apps init.ora
  configuration.
• Optimizer enhancements
• Common reports improved by 50.
• Order Import improved by 15
• Territory Assignment Program improved by 10.
• Payroll improved by 5.

88
Benefits for e-Business Suite

• Optimizer enhancements
• Dynamic sampling has been extended to sample
  temporary tables. This improves performance for
  Apps modules such as order management, planning,
  and pricing which make extensive use of temporary
  tables.
• optimizer_features_enable parameter is
  session-settable
• SQL Execution / Tracing enhancements
• Row sources and row counts in trace.
• Program id / line id (vsql)

89
Apps Reports Improvements
Report Name 10g 9iR2 Improvement
Potential Revenue Summary 2 hrs 15 min 15 hrs 18 min 6.8X
Withholding Tax by Invoice Report 15 hrs 20 min 20 hrs 20 min 1.3X
Employee Assignments Report 12 min. 46 min. 4X
Purchase Summary By Category 37 min 1 hr 4 min 1.7X
Order/Invoice Detail Report 16 min 39 min 2.5X
Unbooked Orders Report 1 min 12 min 12X