oracle 11g新特性 之SQL Tuning.ppt

oracle 11g新特性 之SQL_TuningAutomatic SQL Tuning
Objectives
pleting this lesson, you should be able to:
Set up and modify Automatic SQL Tuning
Use the PL/SQL interface to perform fine tuning
View and interpret reports generated by Automatic SQL Tuning
SQL Tuning in Oracle Database 10g
SQL Tuning Advisor
High load
ADDM
Workload
DBA
1
2
3
4
Run SQL Tuning Advisor
Accept profiles
GenerateSQL profiles
Automatic
Automatic SQL Tuning in Oracle Database 11g
AWR
Auto matic
SQL Tuning
Workload
Reports
1
2
3
4
DBA
Top SQL
Summary of Automation in Oracle Database 11g
Task runs automatically (AUTOTASK framework)
Workload automatically chosen (no SQL Tuning Set)
SQL profiles automatically tested
SQL profiles can be configured for automatic implementation
SQL statements automatically retuned if they regress
Reporting available over any time period
Selecting Potential SQL Statements for Tuning
AWR
Avrg execution
Hourly
Pull the top queries from the past week into four buckets:
Top for the past week
Top for any day in the past week
Top in any single hour
Top by average single bine four buckets into one (assigning weights).
Cap at 150 queries per bucket.
Candidate list
Daily
Weekly
Maintenance Window Timeline
Maintenancewindow
PickcandidateSQL
TuneS1
TestP1
AcceptP1
TuneS2
…
…
Automatic SQL Tuning task
One hour maximum (by default)
Automatic Tuning Process
Existingprofile?
Replace profile
Y
N
3Xbenefit?
3Xbenefit?
Y
Accept profile
Y
Ignore new profile
N
N
New SQL profile
GATHER_STATS_JOB
Indexes
Not considered forauto implementation
Stalestats
RestructureSQL
Considered forauto implementation
Controlling the Automatic SQL Tuning Task
Autotask configuration:
On/off switch
Maintenance windows running tuning task
CPU resource consumption of tuning task
Task parameters:
SQL profile implementation automatic/manual switch
Global time limit for tuning task
Per-SQL time limit for tuning task
Test-execute mode disabled to save time
M