Upgrade from Oracle Database 10g to 11g:What to Expect From the Optimizer

Agenda

• Changes in behaviour
• SQL Plan Mangement
• Pre-upgrade checklist
• Post-upgrade checklist
• Correcting regressed SQL Statements

Change in Behavior

Init.ora Parameters

Parameter
Function
In 10g
In 11g


Optimizer_mode
Cost-based Optimizer used for all   SQL Statements
All_rows
All_rows


Optimizer_Dynamic_Sampling
If no statistics on an object   automatically gathered at parse
2
2


Optimizer_Secure_view_merging
Additional security checks before   merging a view
True
True


Optimizer_use_invisible_indexes
Allows Optimizer to use an   invisible index as access method
N/A
False


Optimizer_use_pending_statistics
Allows Optimizer to use an pending   statistics
N/A
False


Optimizer_capture_SQL_plan_baselines
Automatically captures execution   plans into SPM
N/A
False


Optimizer_use_SQL_plan_baselines
Optimizer uses any existing SQL   Plan Baseline
N/A
True

New DBMS_STATS Subprograms

Subprogram
Function
In 10gR2
In 11g


Gather_System_Stats
Gathers stats on CPU and IO speed   of H/W
Yes
Yes


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   gather but not published immediate
N/A
Yes


Restore_Table_Stats
Revert stats back to what they were before
10.2.0.4
Yes


Diff_Table_Stats
Compare stats for a table from two different sources
10.2.0.4
Yes


Create_Extended_stats
Gathers stats for a user specified   column group or an expression
N/A
Yes


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?

© 2010, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.

相关文章 | Related posts:

1. Siebel CRM with Oracle® Cost-Based Optimizer (CBO)
2. Oracle Database 11g: Server Manageability Student Guide
3. Oracle Database 10g Upgrade Companion
4. Upgrade to Oracle Database 11g:Single Instance to RAC & ASM
5. Oracle Database 11g: Scheduler Student Guide
6. Data Warehouse with Oracle Database 11g
7. Using Automatic Statistics Collection In A Streams Environment
8. EVENT: 10060 dump predicates in optimizer (kko)
9. Oracle BI Enterprise Edition 11g Installation,Upgrade,Security
10. Oracle Database 11gR2 Upgrade Companion