<Insert Picture Here> Highlights of Oracle Database 11g: Top Picks of Manageability & Real Application Testing Features Leng Leng Tan Vice President, Server Technologies, Oracle Corporation
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.
Top Customer IT Concerns
Top DBA Activities • Performance Management • Challenge: Sustain Optimal Performance amid Workload Changes • Change Management • Challenge: Preserve Order amid Infrastructure Changes Source: IOUG Nov 2006 Database Survey
Top DBA Activities � Performance Management � Challenge: Sustain Optimal Performance amid Workload Changes • Change Management • Challenge: Preserve Order amid Infrastructure Changes
Sustain Optimal Performance with Self-Managing Database e v i t p a d A Auto-Tuning Tuning d e t a r g e t n I Advisory t c a p m I w o L Instrumentation n e p y L a C y g r m r u Q o A e o a k i S e R t v m r c a h / o o s a c e c c t B p i M S S e l p p R A e R
Sustain Optimal Performance with Self-Managing Database e v i t p a d A Auto-Tuning Tuning d e t a r g e t n I Advisory t c a p m I w o L Instrumentation n e p y L a C y g r m r u Q o A e o a k i S e R t v m r c a h / o o s a c e c c t B p i M S S e l p p R A e R
Sustain Optimal Performance with Self-Managing Database e v i t p a d A Auto-Tuning Tuning d e t a r g e t n I Advisory t c a p m I w o L Instrumentation n e p y L a C y g r m r u Q o A e o a k i S e R t v m r c a h / o o � s a c e c c t B p i M S S e l p p R A e R �
ADDM for RAC Database-Level ADDM • A Performance Expert, now a RAC Specialist too! • Identifies the most “Globally Self-Diagnostic Engine Significant” performance issues • Automatically runs every hour • Cluster-wide analysis of: • Global cache interconnect issues • Lock manager congestion issues Database Instance-Level ADDMs Level • Global resource contention, e.g. IO bandwidth, hot blocks Analysis • Globally high-load SQL • Skew in instance response times Node 1 Node 2 Node 3 Node 4
Automatic SQL Tuning • Automatically captures high- Packaged Custom load SQL Apps Apps • Automatically tunes SQL Auto Capture without changing application High-Load SQL by creating SQL Profiles Nightly Automatic SQL Tuning • Automatically implements (optional) greatly improved SQL SQL SQL plans Profiles Analysis • Automatically reports analysis • Automatically runs during Automatic implement maintenance window Report Manually implement Well-tuned SQL
Real-time SQL Monitoring • Shining new light on long-running SQLs • What? Who? Where? When? • Resource consumption • Real-time monitoring of active SQL while executing SQL • On by default with no overhead for: • SQLs longer than 5 secs • Parallel Query/DML/DDL • View statistics at multiple levels • System wide • Single Session • Single SQL • Provide insights into the internals of SQL executions • Ease tuning efforts
<Insert Picture Here> DEMO Real-time SQL Monitoring • Oracle Database 11gR1 • Oracle Grid Control 11g (Alpha)
Manageability Comparison of Oracle Database 9i, 10g, and 11g 100% 75% 9i 10g 50% 11g 25% 0% Time Steps Summary Summary Oracle 9i vs. 10g Oracle 10g vs. 11g Oracle 9i vs. 10g Oracle 10g vs. 11g � 44% less time � 26% less time � 44% less time � 26% less time � 47% fewer steps � 31% fewer steps � 47% fewer steps � 31% fewer steps
Top DBA Activities • Performance Management • Challenge: Sustain Optimal Performance amid Workload Changes � Change Management � Challenge: Preserve Order amid Infrastructure Changes
Lifecycle of Change Management Test Diagnose & Resolve Make Change Problems Set Up Test Environments Provision for Production Diagnose & Manage Identify Patches & Problems Workarounds Preserve Order Amid Change
Lifecycle of Change Management Test Diagnose & Resolve Make Change Problems � Set Up Test Environments Provision for Production Diagnose & Manage Identify Patches & Problems Workarounds Preserve Order Amid Change
Data Masking What LAST_NAME SSN SALARY • The act of anonymizing customer, AGUILAR 203-33-3234 40,000 financial, or company confidential data BENSON 323-22-2943 60,000 to create new, legible data which D’SOUZA 989-22-2403 80,000 retains the data's properties, such as its width, type, and format. FIORANO 093-44-3823 45,000 Why • To protect confidential data in test LAST_NAME SSN SALARY environments when the data is used by developers or offshore vendors Jalfdkjd 111—23-1111 40,000 • When customer data is shared with Nareopi 111-34-1345 60,000 3 rd parties without revealing Kadsfkie 111-97-2749 80,000 personally identifiable information Glpokopa 111-49-3849 45,000
Data Masking Mask Test Clone Clone Test Production Staging Major features • Automatic database referential integrity when masking primary keys • Implicit – database enforced • Explicit – application enforced • Data mask format library • View sample data before masking • Application masking templates • Define once; execute multiple times • New in Oracle Grid Control 10gR4
Lifecycle of Change Management Test Diagnose & Resolve Make Change Problems Set Up Test Environments Provision for Production Diagnose & Manage Identify Patches & Problems Workarounds Real Application Testing
Real Application Testing with Database Replay
Real Application Testing with Database Replay • Recreate actual production database workload in test environment • Capture workload in production including critical concurrency • Replay workload in test with production timing • Analyze & fix issues before production Client Client Client … Production Test Replay DB Workload Middle Tier Capture DB Workload … … Test migration Oracle DB to RAC Storage
Comparison of LoadRunner & DB Replay Testing e-Business Suite 80 80 Time Taken (Days) 60 40 24 20 20 5 20 LoadRunner 4 2 5 0 0 DB Replay 0 Install & Setup Understand Identify Key Generate Run Test Application Transactions Workload Usage Total Testing Time DB Replay: ½ month LoadRunner: 7 ½ months
<Insert Picture Here> DEMO Database Replay
Database Replay Comparison � Database Time Machine � Technology that can only be built by Oracle 3rd Party Oracle Load Testing Tools Database Replay Real application Artificial simulated Workload workload workload 100% of workflow 1-5% of workflow Test Coverage •Simple app: days •Simple app: weeks Test Effort •Complex app: days •Complex app:months
Real Application Testing with SQL Performance Analyzer (SPA) • Test impact of change on SQL query performance • Capture SQL workload in production including statistics & bind variables • Re-execute SQL queries in test environment • Tune regressed SQL and seed SQL plans for production Client Client Client … Production Test Re-execute SQL Queries Middle Tier Capture SQL Use SQL Tuning … … Advisor to tune Oracle DB regression Storage
SQL Plan Management Business Requirement • Unpredictable changes in execution plans can happen • New Statistics • Changes in the Environment, Software upgrades • Today you have to ‘freeze’ critical plans or statistics Solution • Allow SQL plan changes, but only for the better ! • Optimizer automatically manages ‘execution plans’ • Only known and verified plans are used • Plan changes are automatically verified • Only comparable or better plans are used going forward • Using SQL Performance Analyzer (SPA) to pre-seed with good plans for critical SQLs before making changes SQL Plan Management is Controlled Plan Evolution
Database Upgrade: 10g to 11g SPA Analysis 4 3 2 1
Database Upgrade: 10g to 11g After Using SQL Plan Management SQL Plan Baselines Accepted No Regressions! 2 1
Lifecycle of Change Management Test Diagnose & Resolve Make Change Problems Set Up Test Environments Provision for Production Diagnose & Manage Identify Patches & Problems Workarounds Provisioning Automation
Reliable Deployment with Provisioning Automation • Gold image based 2 1 provisioning New in 10gR3 Grid Control: • Greatly simplify RAC provisioning with automation Stage Gold Create reference RAC • Single click RAC scale-out Image System and scale-back 4 3 • Provision full RAC and Clusterware systems • Configures entire stack • Pre-requisite checks & automatic fix-ups Create Production Scale-out RAC RAC System
Recommend
More recommend
