Get documents about "Oracle Database 10g and 11g: What to Expect From the Optimizer
Shared by: uc51Vln
-
Stats
- views:
- 47
- posted:
- 11/23/2011
- language:
- English
- pages:
- 38
Document Sample
<Insert Picture Here>
Oracle Database 10g and 11g:
What to Expect From the Optimizer
Maria Colgan Principal Product Manager
Agenda
• Changes in behaviour <Insert Picture Here>
• SQL Plan Mangement
• Pre-upgrade checklist
• Post-upgrade checklist
• Correcting regressed SQL Statements
<Insert Picture Here>
Change in Behavior
Init.ora Parameters
Parameter Function In 10g In 11g
Optimizer_mode Cost-based Optimizer used All_rows All_rows
for all SQL Statements
Optimizer_Dynamic_Sampling If no statistics on an object 2 2
automatically gathered at
parse
Optimizer_Secure_view_merging Additional security checks True True
before merging a view
Optimizer_use_invisible_indexes Allows Optimizer to use an N/A False
invisible index as access
method
Optimizer_use_pending_statistics Allows Optimizer to use an N/A False
pending statistics
Optimizer_capture_SQL_plan_baselines Automatically captures N/A False
execution plans into SPM
Optimizer_use_SQL_plan_baselines Optimizer uses any existing N/A True
SQL Plan Baseline
New DBMS_STATS Subprograms
Subprogram Function In 10gR2 In 11g
Gather_System_Stats Gathers stats on CPU and IO Yes Yes
speed of H/W
Gather_Dictionary_Stats Gathers stats on dictionary objects Yes Yes
Gather_Fixed_Object_Stats Gather stats on V$views Yes Yes
Publish_Pending_stats Pending stats allows stats to be N/A Yes
gather but not published immediate
Restore_Table_Stats Revert stats back to what they were 10.2.0.4 Yes
before
Diff_Table_Stats Compare stats for a table from two 10.2.0.4 Yes
different sources
Create_Extended_stats Gathers stats for a user specified N/A Yes
column group or an expression
Set_Table_Perfs Sets stats preferences of a table N/A Yes
Automatic Statistics Gathering Job
• Introduced in 10g
• Gathers statistics on objects where
• Statistics are missing
• Statistics are stale
• In 10g its an Oracle Scheduler job
• Runs during maintenance window
• In 11g its an Autotask
• Runs during maintenance window
• Use DBMS_AUTO_TASK_ADMIN package to control job
New Features
• New Optimizations
• Group-by placement
• Enhanced join predicate push down
• Null-aware antijoin
• Adaptive Cursor Sharing (enhanced bind peeking)
• Extended Statistics
• Multi-column statistics ( for correlation)
• Statistics on expressions
• Pending Statistics
Each of the new features could potentially change a plan
How can you maintain performance -> stability during upgrade?
SQL Plan Management
<Insert Picture Here>
SQL Plan Management
Guaranteed plan stability and
controlled plan evolution
SQL Plan Management
Prior to 11g
• Unpredictable changes can happen to an execution plan
• Avoiding plan changes the only method to avoid performance
regression
• Lock Statistics to prevent them from changing
• Freezing an execution plan with a Stored Outline
• No mechanism for plans to evolve
Solution
• Optimizer automatically manages „execution plans‟
• Only known and verified plans are used
• Plan changes are verified
• Only comparable or better plans are used going forward
SQL Plan Management is controlled plan performance
With SQL Plan Management
• SQL statement is parsed for the first time and a plan is generated
• Check the log to see if this is a repeatable SQL statement
• Add SQL statement signature to the log and execute it
• Plan performance is still “verified by execution”
GB
Parse Execute Plan Acceptable
HJ
HJ
Statement log
With SQL Plan Management
• SQL statement is parsed again and a plan is generated
• Check log to see if this is a repeatable SQL statement
• Create a Plan history and use current plan as SQL plan baseline
• Plan performance is “verified by execution”
GB
Parse Execute Plan Acceptable
HJ
HJ
Statement log
Plan history
Plan baseline
GB
HJ
HJ
With SQL Plan Management
• Something changes in the environment
• SQL statement is parsed again and a new plan is generated
• New plan is not the same as the baseline – new plan is not executed
but marked for verification
GB
Parse
NL
NL
Statement log
Plan history
GB Plan baseline
NL
GB
NL
HJ
HJ
With SQL Plan Management
• Something changes in the environment
• SQL statement is parsed again and a new plan is generated
• New plan is not the same as the baseline – new plan is not executed
but marked for verification
• Execute known plan baseline - plan performance is “verify by history”
GB
Parse Execute
HJ Plan Acceptable
HJ
Statement log
Plan history
GB Plan baseline
NL
GB
NL
HJ
HJ
Verifying the new plan
• Non-baseline plans will not be used until verified
• DBA can verify plan at any time
Statement log
Optimizer
Plan history
checks if new
GB Plan baseline Invoke or schedule plan is as good
NL
GB DBA verification as or better
NL than old plan
HJ
HJ
Statement log
Plan
history
Plans which don’t GB
Plan baseline
perform as good as NL
GB GB
the original plan NL NL
NL
HJ
stay in the plan Plans which perform as good as or
NL
history and are HJ better than original plan are added to
marked the plan baseline
unaccepted
SQL Plan Management – the details
• Controlled by two init.ora parameter
• optimizer_capture_sql_plan_baselines
• Controls auto-capture of SQL plan baselines for repeatable stmts
• Set to FALSE by default in 11gR1
• optimizer_use_sql_plan_baselines
• Controls the use of existing SQL plan baselines by the optimizer
• Set to TRUE by default in 11gR1
• Monitoring SPM
• Dictionary view DBA_SQL_PLAN_BASELINE
• Via the SQL Plan Control in EM DBControl
• Managing SPM
• PL/SQL package DBMS_SPM or via SQL Plan Control in EM DBControl
• Requires the „administer sql management object‟ privilege
SPM Plan Capture – Bulk
• From SQL Tuning Set (STS)
• Captures plan details for a (critical) set of SQL Statement in STS
• Load these plans into SPM as baseline plans
• From Stored Outlines
• Migrate previously created Stored Outlines to SQL plan baselines
• From Cursor Cache
• Load plans from the cursor cache into SPM as baseline plans
• Filters can be specified (SQL_ID, Module name, schema)
• From staging table
• SQL plan baselines can be captured on another system
• Exported via a table (similar to statistics) and imported locally
• Plan are “unpacked” from the table and loaded into SPM
<Insert Picture Here>
Pre-upgrade Steps
Pre-Upgrade Step
• Testing on the new Database Release
• Use hardware identical to product
• Use a copy of the „live‟ data from product
• Ensure all important queries and reports are tested
• Capture all necessary performance information during tests
• Ensure comparable test results are available for your current
Oracle release
• Capture current 10g execution plans
• Using SQL Performance Analyzer
• Using Stored Outlines
• Using SQL Tuning Sets
• Using exported SQL plan baselines
Testing on the new database release
Removing old Optimizer hints
• If there are hints for every aspect of the execution plan
the plan won‟t change between releases (Stored Outline)
• Partial hints that worked in one release may not work in
another
• Test all SQL stmts without hints on the new release
using the parameter _optimizer_ignore_hints=TRUE
• Chance are the SQL stmts will perform better without any hints
Capturing Plans using SPA
Before Oracle Database 11g
2 change
O_F_E=10
Plan History 5
4
GB GB
HJ HJ
HJ HJ
Regressing
statements No plan
regressions
After
3 change
O_F_E=11
optimizer_features_enable
GB GB GB
HJ HJ HJ
SQL 1 HJ HJ HJ Well
tuned
Performance plans
Analyzer Oracle Database 10g
Capturing Plans using Stored outlines
Oracle Database 11g
Plan History OH Schema
GB 5. Migrate Stored Outlines
GB
HJ into SPM GB
HJ
HJ HJ
HJ
HJ
No plan
regressions
Oracle Database 11g
4. Upgrade
to 11g
1. Begin CREATE_STORED_OUTLINES=true
with OH Schema
2. Run all SQL in the GB
Application and auto HJ
create a Stored Outline HJ
for each one
3. After Store
Outlines are CREATE_STORED_OUTLINES=false
captured Oracle Database 9 or 10g
Capturing Plans using SQL Tuning Set
Database Upgrade
Oracle Database 11g
Plan History
3. Bulk load plans into
GB GB
HJ HJ
SPM
HJ HJ
No plan
regressions
Oracle Database 11g
2. Upgrade
DBA to 11g
GB
Well tuned
HJ
plan
HJ 1. Create STS for
critical statements Oracle Database 10g
Capturing Plans Using an 11g test environment
Production Database 11g
Plan History 5. Unpack baselines
GB into SPM
HJ GB
HJ HJ
HJ
No plan
regressions
4. Import staging
table
DBA
3. Export staging
Plan History
table
GB GB
HJ HJ
HJ 1. Create HJ 2. Create staging
Well tuned
baselines from table & pack Baseline
plan tuned stmts baselines into it plans
staging table
Development / Test Database 11g
<Insert Picture Here>
Post-Upgrade Steps
Post-upgrade Steps
• Load SPM with 10g plans from
• A STS create in Oracle Database 10gR2
• Stored Outlines
• SQL Tuning Set
• A staging table
• The Cursor Cache
• Manage Optimizer Statistics
Post-upgrade Steps
• Load SPM with 10g plans from
• A STS create in Oracle Database 10gR2
• Stored Outlines
• SQL Tuning Set
• A staging table
• The Cursor Cache
• Manage Optimizer Statistics
SQL Plan Management - general
upgrade strategy
1. Set OFE to Oracle Database 11g
O_F_E=10
previous release
Plan History
2. Run all SQL in the 3. Auto Capture 10g
GB GB
Application and auto plans GB
HJ HJ
load SQL Plan Baselines HJ
HJ HJ
with 10g plan NL
No plan
5. 11g plan queue regressions
4. After
O_F_E=11 for verification
plans are
loaded
change optimizer_features_enable
OFE to 11
• Seeding the SQL Plan Baselines with 10g plans No plan change on upgrade
• After all SQL Plan Baselines are populated switch Optimizer_Features_Enable to 11g
• new 11g plans will only be used after they have been verified
What to do with statistics after
upgrade
• Use last known 10g stats until system is stable
• Switch on incremental statistics for partitioned tables
• DBMS_STATS.SET_GLOBAL_PREFS('INCREMENTAL','TRUE');
• Temporarily switch on pending statistics
• DBMS_STATS.SET_GLOBAL_PREFS(‘PENDING’,’TRUE’);
• Gather 11g statistics
• DBMS_STATS.GATHER_TABLE_STATS(‘sh’,’SALES’);
• Test your critical SQL statement with the pending stats
• Alter session set optimizer_use_pending_statistics=TRUE;
• When proven publish the 11g statistics
• DBMS_STATS.PUBLISH_PENDING_STATS();
<Insert Picture Here>
Correcting Regressed
SQL Statements
Correcting Regressed SQL
• Compare 10g & 11g execution to see what has changed
• If the pre-upgrade instance is still available, execute transaction in both
instances (10g and 11g) and compare the execution plans, buffer gets,
CPU time, and total elapse times
• If pre-upgrade instance is no longer available, use the export of the AWR/
PERFSTAT schema to find the SQL statement and its execution plan
(use script sprepsql.sql)
• Determine root cause of suboptimal plans and take corrective action
• Re-gathering statistics with different parameter settings
• Use SQL Tuning Advisor
• Create of a SQL Profile
• Research know issues and log an SR
• Temporary work around creating SQL plan baseline from
• STS
• Stored Outline
• From hinted statement
Capture a hinted plan as SQL plan Baseline
1. In a SQL*Plus session run the non-hinted SQL statement to
begin the SQL plan baseline capture
SQL> SELECT PROD_NAME, SUM(AMOUNT_SOLD)
2 from Sales s, Products p
3 where s.prod_id=p.prod_id
4 and PROD_CATEGORY = :ctgy
5 group by PROD_NAME;
2. Find SQL_ID for the statement in V$SQL
SQL> select SQL_ID, SQL_FULLTEXT from v$SQL where SQL_TEXT like
'%SELECT PROD_NAME, SUM(%';
SQL_ID SQL_FULLTEXT
------------- -------------------------------------------
74hnd835n81yv select SQL_ID, SQL_FULLTEXT from v$SQL
chj6q8z7ykbyy SELECT PROD_NAME, SUM(AMOUNT_SOLD)
3. Create a SQL plan baseline for the statement
SQL> variable cnt number;
SQL> execute :cnt :=dbms_spm.load_plans_from_cursor_cache(
sql_id=>'chj6q8z7ykbyy');
Capture a hinted plan as SQL plan Baseline
4. The plan that was captured is incorrect and will need to be
disabled. The SQL_HANDLE & PLAN_NAME are required.
These can found by looking in DBA_SQL_PLAN_BASELINE
SQL> select sql_handle, sql_text, plan_name, enabled from
dba_sql_plan_baselines;
SQL_HANDLE SQL_TEXT PLAN_NAME ENABLED
----------------------------------- -------------------------------- ------------------------------------------- -----------
SYS_SQL_bf5c9b08f72bde3e SELECT PROD_NAME, SUM( SQL_PLAN_byr4v13vkrrjy42949306 YES
5. Using DBMS_SPM.ALTER_SQL_PLAN_BASELINE disable the
bad plan
SQL> variable cnt number;
SQL> exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
SQL_HANDLE => 'SYS_SQL_bf5c9b08f72bde3e',
PLAN_NAME => 'SQL_PLAN_byr4v13vkrrjy42949306',
ATTRIBUTE_NAME => 'enabled',
ATTRIBUTE_VALUE => 'NO');
SQL> select sql_handle, sql_text, plan_name, enabled from
dba_sql_plan_baselines;
SQL_HANDLE SQL_TEXT PLAN_NAME ENABLED
----------------------------------- -------------------------------- ------------------------------------------- -----------
SYS_SQL_bf5c9b08f72bde3e SELECT PROD_NAME, SUM( SQL_PLAN_byr4v13vkrrjy42949306 NO
Capture a hinted plan as SQL plan Baseline
6. Modify the SQL statement using hints & execute it
SQL> SELECT /*+ INDEX(p) */ PROD_NAME, SUM(AMOUNT_SOLD)
from Sales s, Products p
where s.prod_id=p.prod_id
and PROD_CATEGORY = :ctgy
group by PROD_NAME;
7. Find new SQL_ID and PLAN_HASH_VALUE for the hinted SQL stmt
SQL> select SQL_ID, SQL_FULLTEXT from v$SQL where SQL_TEXT like '%SELECT /*+
INDEX(p) */ PROD_NAME, SUM(%';
SQL_ID PLAN_HASH_VALUE SQL_FULLTEXT
------------- --------------- ----------------------------
9t5v8swp79svs 3262214722 select SQL_ID, SQL_FULLTEXT from v$SQL
djkqjd0kvgmb5 3074207202 SELECT /*+ INDEX(p) */ PROD_NAME,
8. Create new accepted plan for original SQL stmt by associating the
modified plan to the original statement's SQL HANDLE
exec :cnt:=dbms_spm.load_plans_from_cursor_cache(
sql_id => 'djkqjd0kvgmb5',
plan_hash_value => 3074207202,
sql_handle => 'SYS_SQL_bf5c9b08f72bde3e„);
Capture a hinted plan as SQL plan Baseline
4. Finally lets look at DBA_SQL_PLAN_BASELINE again and
confirm we have two plans for this stmt, one enabled and one
disables
SQL> select sql_handle, sql_text, plan_name, enabled from
dba_sql_plan_baselines;
SQL_HANDLE SQL_TEXT PLAN_NAME ENABLED
----------------------------------- -------------------------------- ------------------------------------------- -----------
SYS_SQL_bf5c9b08f72bde3e SELECT PROD_NAME, SUM( SQL_PLAN_byr4v13vkrrjy42949306 NO
SYS_SQL_bf5c9b08f72bde3e SELECT PROD_NAME, SUM( SQL_PLAN_byo8x12xastyr84223231 YES
Correcting Regressed SQL Statement
Load plans Load plans Load plans Load plans Load a
from a SQL from the from Stored from a hinted
Tuning Set Cursor Outlines staging execution
Cache table plan
Stage table
Cursor
Cache
For More Information
search.oracle.com
Upgrading Optimizer
Or
http://www.oracle.com/technology/products/bi/db/11g/pdf/twp_upgrading_10g_to_11g_what_to_expect_from_optimizer.pdf
Or
http://www.optimizermagic.blogspot.com
Q&A
If you have more questions later, feel free to ask
The preceding is intended to outline our general
product direction. It is intended for information
purposes only, and may not be incorporated into any
contract. It is not a commitment to deliver any
material, code, or functionality, and should not be
relied upon in making purchasing decisions.
The development, release, and timing of any
features or functionality described for Oracle‟s
products remains at the sole discretion of Oracle.
