Transcript Slide 1
Session S317113: What do I really need to know when upgrading
Thomas Kyte http://asktom.oracle.com/
1
So … What Does Oracle Database 11g Mean To Me?
2
Small Change – but
think
about it…
3
Small Change – but
think
about it…
ops$tkyte%ORA11GR2> create table t 2 as 3 select substr(object_name, 1, 1 ) str, all_objects.* 4 from all_objects 5 order by dbms_random.random; Table created.
ops$tkyte%ORA11GR2> create index t_idx on t(str,object_name); Index created.
ops$tkyte%ORA11GR2> begin 2 dbms_stats.gather_table_stats
3 ( user, 'T', 4 method_opt => 'for all indexed columns size 254', 5 estimate_percent=>100 ); 6 end; 7 / PL/SQL procedure successfully completed.
4
Small Change – but
think
about it…
ops$tkyte%ORA11GR2> select count(subobject_name) from t t1 where str = 'T'; … ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 19 | 296 (0)| 00:00:04 | | 1 | SORT AGGREGATE | | 1 | 19 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 292 | 5548 | 296 (0)| 00:00:04 | |* 3 | INDEX RANGE SCAN | T_IDX | 292 | | 4 (0)| 00:00:01 | ------------------------------------------------------------------------------------- 5
Small Change – but
think
about it…
ops$tkyte%ORA11GR2> insert into t 2 select 'T', all_objects.* 3 from all_objects 4 where rownum <= 1; 1 row created.
ops$tkyte%ORA11GR2> begin 2 dbms_stats.gather_table_stats
3 ( user, 'T', 4 method_opt => 'for all indexed columns size 254', 5 estimate_percent=>100 ); 6 end; 7 / PL/SQL procedure successfully completed.
6
Small Change – but
think
about it…
ops$tkyte%ORA11GR2> select count(subobject_name) from t t2 where str = 'T'; … -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 19 | 297 (1)| 00:00:04 | | 1 | SORT AGGREGATE | | 1 | 19 | | | |* 2 | TABLE ACCESS FULL| T | 293 | 5567 | 297 (1)| 00:00:04 | -------------------------------------------------------------------------- 7
““ The Law of unintended consequences holds that almost all human actions have at least one unintended consequence. Unintended consequences are a common phenomenon, due to the complexity of the world and human over-confidence.
”
8
What do you have from the past…
• Online Parameter Changes • Online Major Memory Changes • Online Schema Evolution • Online Index Creates • Quiesce • Rolling Upgrades • Online Disk reconfiguration (ASM) • Online Cross Platform Tablespace Transport • Full Database Transports • And more….
9
What do you need to know?
Test To Scale SQL Plan Management The ability to forget and let it go Never Stopping Planning Ahead
10
First – what do we need to do?
© 2010 Oracle Corporation
11 11
Database Upgrade Process: Steps
1.
2.
3.
4.
5.
6.
7.
8.
9.
Analyze & gather information about environment Determine the upgrade path and choose upgrade method
Planning Ahead
Establish performance baseline/metrics before upgrade
Forget and let it go ASH and AWR
Test upgraded database with applications and reports Ensure adequate performance by comparing metrics gathered before and after upgrade Remediate regressions, e.g, tune queries, update database parameters, call Support, etc.
Never Stopping
Go Live!
SQL Plan Management
12
SQL
Plan Management
© 2010 Oracle Corporation
13 13
SQL Plan Management Phase 1 - Capture
• Run applications to create a baseline –
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE
Repeated plans will be added to the SQL Plan Baseline during this phase
Parse GB HJ HJ Plan History Plan Baseline GB HJ HJ
SQL MANAGEMENT BASE
Residing in SYSAUX TS.
Will occupy max. 10% of SYSAUX.
Weekly job will delete plans not used in 53 weeks [default].
14
SQL Plan Management Phase 2 - Selection
• • New Plans are generated (because something changed) But are not trusted –
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE
New plan will be added to the Plan History but it won't be used
unless and until
it has been verified
Hard Parse GB NL NL GB NL NL Plan History GB Plan Baseline GB GB HJ HJ HJ HJ HJ HJ GB NL NL GB NL NL 15
SQL Plan Management Phase 3 – Evolution
• Plans are verified – by testing the performance of the new plan in the background – Automagically or Manually Equal or better plans can be added to the SQL Plan Baseline
DBA Plan History Plan Baseline GB NL GB NL HJ HJ GB NL NL GB
Inefficient plan will be kept in the Plan History
NL NL Automatic Job 16
Upgrade Scenario
• • • Your 9i application is already in 11g for whatever reason You’d like to have ‘query plan stability’ – Coupled with the opportunity to use better plans – do not want to be frozen The steps would be….
17
SQL Plan Management – Parameterize
Repeatable plans will be added to the Plan Baseline upon 2nd execution
Plan History Plan Baseline GB NL GB HJ GB NL NL HJ NL GB NL NL
STS
Now: Different plans created with OFE=11 will be added to the Plan History for later verification
18
Upgrade Scenario
• • • • Your application is in 9i You’d like to have ‘query plan stability’ – Coupled with the opportunity to use better plans – do not want to be frozen You will be changing platforms during the upgrade (not doing a direct upgrade of the database) The steps would be….
19
SQL Plan Management – Outlines
STS
Capture query outlines on the production system Exp/imp outlines to New system
SS
exp imp expdp impdp DB-Link ...
3 DBMS_SPM.MIGRATE_STORED_OUTLINE
Plan History Plan Baseline GB NL GB HJ GB NL NL HJ NL 20
Upgrade Scenario
• • • • Same Scenario but your application is in 10g You’d like to have ‘query plan stability’ – Coupled with the opportunity to use better plans – do not want to be frozen You will be changing platforms during the upgrade (not doing a direct upgrade of the database) The steps would be….
21
SQL Plan Management – Tuning Pack
STS Staging Table STS
exp imp expdp impdp DB-Link ...
3
10.2 plans will become the SQL Plan Baseline
Plan History Plan Baseline GB NL GB HJ GB NL NL HJ NL GB NL NL
22
Upgrade Scenario
• • • • You would like to deploy from development to production..
You would like to deploy at a customer site…
And you want to start with a stable set of plans
–
Using better plans only after they have been verified
The steps would be….
23
SQL Plan Management - New Application
@Vendor
DBMS_SPM.CREATE_STGTAB_BASELINE
Staging
@Customer
Staging Table exp imp expdp impdp Table 3 DBMS_SPM.UNPACK_STGTAB_BASELINE
DBMS_SPM.PACK_STGTAB_BASELINE
Plan Baseline GB NL GB HJ GB NL NL HJ NL Plan Baseline GB NL GB HJ GB NL NL HJ NL 24
Test to Scale
© 2010 Oracle Corporation
25 25
Database Replay Overview
• • Replay actual production database workload in test environment Identify, analyze and fix potential instabilities before making changes to production • Capture Workload in Production – Capture full production workload with real load, timing & concurrency characteristics (9
i,
10
g,
11
g
) – Move the captured workload to test system (11
g
) • Replay Workload in Test – Make the desired changes in test system – Replay workload with full production characteristics – Honor commit ordering • Analyze & Report – Errors – Data divergence – Performance divergence
Analysis & Reporting 26
Supported Changes
Changes Unsupported (there are other tools for that) Changes Supported
•
Database Upgrades, Patches
•
Schema, Parameters
•
RAC nodes, Interconnect
•
OS Platforms, OS Upgrades
•
CPU, Memory
•
Storage
•
Etc. Client Client … Client Middle Tier Storage Recording of External Client Requests 27
Step 1: Workload Capture
• All external client requests captured in binary files • System background and internal activity excluded • Minimal overhead –Avoids function call when possible –Buffered I/O • Independent of client protocol • Can capture on 9
i,
10
g,
and 11
g
11g and replay on • Capture load for interesting time period, e.g., peak workload, month-end processing, etc.
Client Storage Production System Client … Client Middle Tier File System File 1 File 2 … File n 28
Step 2: Process Workload Files
• Setup test system –Application data should be same as production system as of capture start time –Use RMAN, Snapshot Standby, imp/exp, Data Pump, etc. to create test system –Make change: upgrade db and/or OS, change storage, migrate platforms, etc.
• Processing transforms captured data into replayable format • Once processed, workload can be replayed many times • For RAC copy all capture files to single location for processing or use shared file system
File 1 File 2 … File n Capture Files Test System File 1 File 2 … File n Metadata Replay Files 29
Step 3: Replay Workload
• • • • Replays workload preserving timing, concurrency and dependencies of the capture system Replay Client is a special program that consumes processed workload and sends requests to the replay system Clients interpret captured calls into sequence of OCI calls and submit to database For high concurrency workloads, it may be necessary to start multiple clients
Test System
Replay Clients
File 1 File 2 … File n Metadata
Replay Files
30
Analysis & Reporting
• Error Divergence :
For each call error divergence is reported
–New: Error encountered during replay not seen during capture –Not Found: Error encountered during capture not seen during replay –Mutated: Different error produced in replay than during capture • Data Divergence –
Replay
: Number of rows returned by each call are compared and divergences reported –
User
: Application level validation scripts • Performance Reporting –Capture and Replay Report: Provides high-level performance information –ADDM Report: Provides in-depth performance analysis –AWR, ASH Report: Facilitates comparative or skew analysis
31
Client Middle Tier
SQL Performance Analyzer: Overview
Production Test … Re-execute SQL … … Capture SQL Transport SQL Oracle DB Storage
•
If adequate spare cycles available, optionally execute SQL here Make Changes / Tuning Regressions
*
No middle & application tier setup required 32
SQL Performance Analyzer: Workflow
Production Test Steps (1) Capture SQL (STS) Make Change (2) (3) (4) (5) Transport STS Execute SQL Pre-change Execute SQL Post-change Compare Perf.
(6) Reiterate (7) Production Change / Tuning Deployment Tuned System No Done?
Yes 33
SQL Performance Analyzer: Key Differentiators From:
Manual SQL capture, High overhead Non-production SQL context Partial SQL workload Months of manual analysis Manual regression tuning High risk, High cost
To:
Automated SQL capture, Negligible overhead Production SQL context Complete SQL workload Automated analysis in minutes Automatic regression tuning Low risk, Low cost
34
Real Application Testing: Tools of the Trade What is it?
How it works?
When to use?
SQL Performance Analyzer
• Predicts SQL performance deviations before end-users can be impacted, helps assess impact of change on
SQL response time
• Executes each SQL, stored in SQL Tuning Set, in
isolation
using
production context
and then compares before and after execution plans and run time statistics •
Unit testing
of SQL with the goal to identify the set of SQL statements with improved/regressed performance
Database Replay
• Replays
real
database workload on test system, helps assess impact of change on
workload throughput
• Captures workloads and replays it with production characteristics including
concurrency , synchronization & dependencies
•
Comprehensive
testing of all sub systems of the database server using real production workload
SQL Concurrency SQL Dependency Speed up/down 35
More information…
• Hands on Lab: S318966 – Database and Application Testing HOL – Wed: 4.45-5.45 pm – Marriott Golden Gate • SPA / Database Replay Demo grounds – Moscone West: 038/039
36
The Ability to forget And let it go
© 2010 Oracle Corporation
37 37
Flashback for Rapid Recovery from Human Error
Flashback Query Flashback Tables Flashback Database Flashback Data Archive and Transaction 38
Restore Points
• Restore point – specifies a jump label –Named Restore Point • Similar to a bookmark • "Can be" - but no guarantee • Will be recorded to the control file
SQL> CREATE RESTORE POINT rpt; SQL> FLASHBACK DATABASE TO RESTORE POINT rpt;
–Guaranteed Restore Point • Similar to storage snapshots • Overrides the FLASHBACK_RETENTION_TARGET •Attention : A guarantee restore point can stop the whole database
SQL> CREATE RESTORE POINT grpt GUARANTEE FLASHBACK DATABASE; SQL> FLASHBACK DATABASE TO RESTORE POINT grpt; 39
Never Stopping
© 2010 Oracle Corporation
40 40
Rolling Database Upgrades
Clients A
Redo
B 1 Version X Version X Initial SQL Apply Config Upgrade
Logs Queue
A B 2 X X+1 Upgrade node B to X+1 Upgrade A
Redo
B A
Redo
B 4 X+1 X+1 Switchover to B, upgrade A 3 X X+1 Run in mixed mode to test Patch Set Upgrades Major Release Upgrades Cluster Software & Hardware Upgrades 41
Online Application Upgrade
Edition-based redefinition
• Code changes are installed in the privacy of a new
edition
• Data changes are made safely by writing only to new columns or new tables not seen by the old edition • An
editioning view
exposes a different projection of a table into each edition to allow each to see just its own columns • A
crossedition
trigger propagates data changes made by the old edition into the new edition’s columns, or (in hot-rollover) vice-versa
42
Editions & object visibility
Object_4 Object_3 Object_2 Object_1
Pre-upgrade edition 43
Editions & object visibility
Object_4 Object_3 Object_2 Object_1
Pre-upgrade edition
Object_4 Object_3 Object_2
is child of
Object_1
Post-upgrade edition
(inherited) (inherited) (inherited) (inherited)
44
Editions & object visibility
Object_4 Object_3 Object_2 Object_1
Pre-upgrade edition
Object_4* Object_3* Object_2
is child of
Object_1
Post-upgrade edition
(actual) (actual) (inherited) (inherited)
45
Planning Ahead
Upgrade Planner
© 2010 Oracle Corporation
46 46
The following 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.
47
MOS-EM: Unified View, Integrated Information Between My Oracle Support and Enterprise Manager
Knowledge Management
My Oracle Support
Problem/SR Management Configuration Management
Oracle Customer
Knowledge Management Problem/SR Management Configuration Management
Performance Management Problem Diagnosis Enterprise Manager Provisioning & Patching
Operating Systems Databases Middleware Applications © 2010 Oracle Corporation
48 48
My Oracle Support – Upgrade Planner
What Are We Announcing?
• New Feature in My Oracle Support – Assist customers in planning the an Upgrade of Oracle technology • Benefits – Reduced time to create, manage and execute Upgrade plan • Streamlined process to request merge patches – Greater Reliability due to Software Currency • • Latest SW, Patch, and Certification information Accuracy of recommendations based on config data (not manual) – Lower Risk • Automated analysis for missing patches and conflict checking © 2010 Oracle Corporation
49 49
My Oracle Support:
Upgrade Planner
Challenges
• • • Upgrade from 10.2.0.4 to 11.2
Where do I get the software What are the valid SW upgrade paths ?
• What is the certification/EOL status of the SW ?
• What recommended patches do I apply post-upgrade?
• How do I know if my 10.2.0.4 fixes will be on my 11.2 upgrade?
• Are there patch conflicts?
• • • • • • • •
Capabilities
• Upgrade path SW recommendations w/ Certification /EOL checks Recommended Patches Replacement/Merge Patch Conflict Analysis for Patches Research and Add Patches Review Patch Feedback Links to latest Support Best Practices and knowledge
Value
• • Reduced Time in research, analysis and management of Upgrade Plan • Reduced Risk due to increased accuracy (automated analysis) Improved quality of plan due to latest Oracle Advice, Best Practices © 2010 Oracle Corporation
50 50
‘End-to-End’
Upgrade Lifecycle
Phase Preparation Upgrade Sub-Phase
Upgrade Plan Upgrade Testing Rehearsal Production Upgrade
Post-Upgrade
Monitor & Maintain
My Oracle Support Upgrade Plan*
Real Application Testing Provisioning
Enterprise Manager – Grid Control
Monitoring
My Oracle Support
EM Grid Control
Integrated solution can be leveraged throughout full lifecycle *Will be integrated in upcoming release
51 51
How to get there
Upgrade is easier!
• The upgrade to Oracle Database 11
g
is much easier than any upgrades to earlier Oracle releases • Size of Upgrade guides: –
8.1.7
- 512 pages –
9.0.1
- 484 pages – 111 steps for an RDBMS with 9 components –
9.2.0
- 344 pages –
10.1.0
- 170 pages –
10.2.0
- 140 pages –
11.1.0
–
11.2.0
- 186 pages -178 pages © 2010 Oracle Corporation
53
53
Documentation
– Note:785351.1
Upgrade Companion 11
g
Release 2
54
What are my upgrade paths?
Predictable performance post-upgrade
9.2.0.8
10.1.0.5
10.2.0.2
11.1.0.6
11.2
SQL Plan Management Automated SQL tuning Real Application Testing 55
56