Corporate PPT Template

1
(No Transcript)
2
Tamás KerepesSenior Principal Trainer Oracle
Hungary
3
Oracle Database 10g Solution Self-Managing
Database
DatabaseControl
Applicationand SQLmanagement
Alert
Monitor
Automaticmanagement
Fix
Advise
Commoninfrastructure
4
Rule-Based Optimizer Obsolescence

• RBO is no longer supported
• No bug fixes will be provided.
• Almost no regression testing of RBO for Oracle
  Database 10g
• CHOOSE and RULE values are no longer supported.
• ALL_ROWS is the default value for OPTIMIZER_MODE.

5
Automatic SQL Tuning Overview

• Automatic SQL Tuning automates the entire SQL
  tuning process and replaces manual SQL tuning.
• Optimizer modes
• Normal mode
• Tuning mode or Automatic Tuning Optimizer (ATO)
• SQL Tuning Advisor is used to access the tuning
  mode.
• The tuning mode should be used only for high-load
  SQL statements.

6
Application Tuning Challenges
How can Itune myhigh-loadSQL?
SQL workload
DBA
High-loadSQL
SQL Tuning Advisor
7
SQL Tuning Advisor Overview
Comprehensive SQL Tuning
Automatic Tuning Optimizer
Statistics Check optimizationmode
Detect stale or missing statistics
Plan Tuning optimization mode
Plan tuning (SQL Profile)
Access Analysis optimization mode
Add missing index Run Access Advisor
SQL Analysis optimization mode
SQL Tuning Advisor
Restructure SQL
8
Stale or Missing Object Statistics

• Object statistics are key input to the optimizer.
• ATO verifies object statistics for each query
  object.
• ATO uses dynamic sampling.
• ATO generates
• Auxiliary object statistics to compensate for
  missing or stale object statistics
• Generates recommendations to gather object
  statistics where appropriate

9
SQL Statement Profiling

• Statement statistics are key input to the
  optimizer.
• ATO verifies statement statistics like
• Predicate selectivity
• Optimizer settings (FIRST_ROWS versus ALL_ROWS)
• ATO uses
• Dynamic sampling
• Partial execution of the statement
• Past execution history statistics of the
  statement
• Generates a profile if statistics were generated

10
Plan Tuning Flow and SQL Profile Creation
create
submit
SQL Profile
SQL TuningAdvisor
use
No application code change
output
Optimizer (Normal mode)
Databaseusers
Well-tunedplan
11
SQL Tuning Loop
Workload
Generateprofiles
SQL Tuning Advisor
High load
DBA
ADDM
12
SQL Tuning Advisor Usage Model
Automatic selection
AWR
ADDM
High-load SQL
SQLTuning Advisor
Sources
Manual Selection
AWR
Cursor cache
Filter
DBA
Custom
13
Database Control and SQL Tuning Advisor
14
SQL Tuning Advisor Options and Recommendations
15
Database Home Page
16
Database Performance Page
17
Concurrency Wait Class Drill Down
18
Top SQL by Waits Drill Down
19
(No Transcript)