Jerry Held

1
(No Transcript)
2
The Self-Managing DatabaseGuided Application
and SQL Tuning
Session id 40713

• Mohamed ZiauddinConsulting Member of Technical
  Staff
• Oracle Corporation

3
Agenda

• SQL Tuning Challenges
• Automatic SQL Tuning Overview
• Usage Scenarios
• High load SQL tuning
• Custom SQL workload tuning
• User Interface
• Enterprise Manager
• DBMS_SQLTUNE PL/SQL package
• Conclusion

4
Manual SQL Tuning Challenges
How can I selecthigh-load SQL?
How can I tune high-load SQL?
High-LoadSQL
DBA
DBA
SQL Workload
5
Manual 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

6
An Example Tuning Scenario
Problem Incorrect Optimizer Mode Selection

• 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 that it is a 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 first rows hint from the
  vendor
• Install the patch in next maintenance cycle

7
Oracle 10g SQL Tuning Solution
Application SQL Management
Storage Management
System Resource Management
Space Management
Backup Recovery Management
Database Management
Database Control
Intelligent Infrastructure
8
Oracle 10g Automates the SQL Tuning Process
I can do it for you !
ADDM
DBA
High-LoadSQL
SQL Workload
SQL Tuning Advisor
9
Agenda

• SQL Tuning Challenges
• Automatic SQL Tuning Overview
• Usage Scenarios
• High load SQL tuning
• Custom SQL workload tuning
• User Interface
• Enterprise Manager
• DBMS_SQLTUNE PL/SQL package
• Conclusion

10
Automatic SQL Tuning Overview
SQL Tuning Recommendations
Automatic Tuning Optimizer
SQL Tuning Advisor
Gather Missing or Stale Statistics
Statistics Analysis
SQL Profiling
Create a SQL Profile
DBA
Add Missing Indexes
Access Path Analysis
SQL Structure Analysis
Modify SQL Constructs
11
Automatic Tuning Optimizer (ATO)

• It is the query optimizer running in tuning mode
• Uses same plan generation process but performs
  additional steps that require lot more time
• It performs verification steps
• To validate statistics and its own estimates
• Uses dynamic sampling and partial executions to
  validate
• It performs exploratory steps
• To investigate the use of new indexes that could
  provide significant speed-up
• To analyze SQL constructs that led to expensive
  plan operators

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

• Motivation
• Statistics are key input to the query optimizer
• Their availability and accuracy is very important
• In Oracle10g, the Automatic statistics collection
  maintains statistics up to date
• But it may not be enabled or properly configured!
• The ATO verifies statistics that it needs/uses
• Generates auxiliary information to compensate for
  missing or stale statistics
• Generates recommendations to gather statistics
  where appropriate

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

• Motivation
• Empower the query optimizer to find a better plan
• The query optimizer has time constraints
• Makes compromises while finding right plan
• The ATO is allowed a lot more time
• Uses the time to gather customized information
  about the SQL statement, known as SQL Profile
• Builds a SQL Profile and recommends it
• Once implemented, SQL Profile is used by the
  query optimizer to generate a well-tuned plan

16
SQL Profiling Flow
SQL Profiling
submit
create
Optimizer (Tuning Mode)
SQL Profile
SQL TuningAdvisor
use
After
output
submit
Optimizer (Normal Mode)
Well-Tuned Plan
DatabaseUsers
17
SQL Profile

• It contains auxiliary information collected by
  the ATO for a SQL statement
• Customized optimizer settings
• Based on past execution history (e.g., first_rows
  vs. all_rows)
• Compensation for missing or stale statistics
• Compensation for errors in optimizer estimates
• Estimation errors occur due to data skews and
  correlations, complex filters and joins
• It doesnt require any change to the SQL text
• Ideal for Packaged Apps
• It is persistent
• Works across shutdowns and upgrades

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

• Motivation
• Adding an index may significantly improve the
  performance of a SQL statement
• Problem A critical access path is missing
• Index not created or mistakenly dropped
• ATO explores the use of new indexes
• Recommends an index if it provides a major
  performance boost
• Also recommends to run SQL Access Advisor to get
  comprehensive index analysis based on a workload
• SQL Access Advisor also uses this analysis mode

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

• Motivation
• Help application developers identify poorly
  written SQL statements
• Suggest restructuring of SQL for efficiency
• Problem categories
• Semantic changes of SQL operators (e.g., use
  UNION ALL instead of UNION)
• Subject to user acceptance of new result
• Syntactic changes to predicates on indexed
  columns (e.g., remove type mismatch in column
  bind)
• Design issues (e.g., add missing join predicate
  to eliminate a large Cartesian join)

22
Agenda

• SQL Tuning Challenges
• Automatic SQL Tuning Overview
• Usage Scenarios
• High load SQL tuning
• Custom SQL workload tuning
• User Interface
• Enterprise Manager
• DBMS_SQLTUNE PL/SQL package
• Conclusion

23
SQL Tuning Usage Scenarios
Automatic Selection
AWR
ADDM
High-load SQL
SQL Sources
Manual Selection
AWR
SQL Tuning Advisor
Cursor Cache
SQL Tuning Set(STS)
Filter / Rank
User-defined
24
SQL Tuning Set (STS)

• Motivation
• Enable user to tune a custom set of SQL
  statements
• It is a new object in Oracle10g for capturing SQL
  workload
• It stores SQL statements along with..
• Execution context parsing user, bind values,
  etc.
• Execution statistics buffer gets, CPU time,
  elapse time, number of executions, etc.
• It is created from a SQL source
• Sources AWR, cursor cache, user-defined SQL
  workload, another STS

25
SQL Tuning Set Benefits

• Allows selective, on-demand, custom SQL workload
  tuning
• It simplifies tuning of a large number of SQL
  statements
• It is persistent
• Provides a common infrastructure for dealing with
  SQL workloads
• Can be used as a source for different tuning tasks

26
Agenda

• SQL Tuning Challenges
• Automatic SQL Tuning Overview
• Usage Scenarios
• High load SQL tuning
• Custom SQL workload tuning
• User Interface
• Enterprise Manager
• DBMS_SQLTUNE PL/SQL package
• Conclusion

27
Enterprise Manager Interface

• 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

28
SQL Source ADDM Finding
29
SQL Source Top SQL
30
SQL Source SQL Tuning Set
31
SQL Tuning Options
32
Enterprise Manager Interface

• 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

33
SQL Tuning Recommendations Overview
34
SQL Tuning Recommendations Details
35
Enterprise Manager Interface

• 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

36
Implement Recommendations
37
Implement Recommendations
38
Agenda

• SQL Tuning Challenges
• Automatic SQL Tuning Overview
• Usage Scenarios
• High load SQL tuning
• Custom SQL workload tuning
• User Interface
• Enterprise Manager
• DBMS_SQLTUNE PL/SQL package
• Conclusion

39
DBMS_SQLTUNE PL/SQL Package

• Contains API for SQL Tuning

Tuning Task Management
STS Management

• Create STS
• Populate STS
• Query STS Contents
• Drop STS

• Create Tuning Task
• Execute Tuning Task
• Display Advisor Recommendations
• Drop Tuning Task

SQL Profile Management

• Accept SQL Profile
• Drop SQL Profile
• Alter SQL Profile Attribute

40
Conclusion
Problem Incorrect Optimizer Mode Selection

• 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 that it is a 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 first rows hint from the
  vendor
• Install the patch in next maintenance cycle

• Automatic Tuning
• Run SQL Tuning Advisor
• Implement SQL profile

41
Recommended Sessions/Demos
Technical Sessions
Campground Demos

• Automatic SGA Memory Management (Tuesday, 5 PM,
  Room 103)
• The Invisible Oracle Deploying Oracle DB in
  Embedded Environments (Wednesday, 430 PM, Room
  103
• Automatic Health Monitoring (Thursday, 1100 AM,
  Room 103

• Proactive Performance Management
• Automatic Memory Management
• Proactive Space Management
• Invisible Installation Deployment
• Automatic Storage Management
• Easy Upgrade

42
Reminder Please complete the OracleWorld
online session survey. This was Session
40173Thank you.
43
A
44
(No Transcript)