oracle database 11g: real application testing ......capture db workload real application testing...
TRANSCRIPT
Oracle Database 11g: Real Application Testing & Manageability Overview
Top 3 DBA Activities
• Performance Management• Challenge: Sustain Optimal Performance
• Change Management• Challenge: Preserve Order amid Change
• Ongoing Administration• Challenge: Manage More with Less
Source: IOUG Nov 2006 Database Survey
Sustain Optimal Performance with Self-Managing Database
Stor
age
Bac
kup
Mem
ory
App
s/SQ
L
Sche
ma
RA
C
Rec
over
y
Rep
licat
ion
Auto-TuningTuning
Advisory
InstrumentationLow Impact
Integrated
Adaptive
ADDM for RAC
• A Performance Expert, now a RAC Specialist too!
• Identifies the most “Globally Significant” performance issues
• Automatically runs every hour • Cluster-wide analysis of:
• Global cache interconnect issues• Lock manager congestion issues • Global resource contention, e.g.
IO bandwidth, hot blocks• Globally high-load SQL• Skew in instance response times
Self-Diagnostic Engine
Database-Level ADDM
Instance-Level ADDMs
Node 1 Node 2 Node 3
Automatic SQL Tuning
• Automatically captures high-load SQL
• Automatically tunes SQL without changing application by creating SQL Profiles
• Automatically implements (optional) greatly improved SQL plans
• Automatically reports analysis • Automatically runs during
maintenance window
PackagedApps
Custom Apps
Automatic SQL Tuning
Auto Capture High-Load SQL
SQL Profiles
Nightly
Well-tuned SQL
Automatic implement
Manually implement
SQL Analysis
Report
Index, MV Analysis
Ease Partition Management
Partition Advice from Access Advisor• Advise on Partitioning methods:
• Range, Range Key• Hash, Hash key• Interval (new), Interval Key
• Advise for tables,indexes, MVs• Consider entire query & DML
workload to improve query performance
Automatic Interval Partition Creation • On first insert• With constant width intervals for
dates and numbers• Existing RANGE partitioned tables
can be converted to INTERVALWell-Designed Schema
SQL Workload
PackagedApps
Customizable Apps
Access Advisor
Index, MV Advice
PartitionAnalysis
Partition Advice
Automatic Memory Tuning
• Unifies system (SGA) and process (PGA) memory management
• Single dynamic parameter for all database memory
• Automatically adapts to workload changes
• Maximizes memory utilization• Helps eliminate out- of-memory
errors• Gets and releases memory from
OS: Linux, Windows and Solaris, HPUX, AIX
• Built in safety-nets to work under runaway workloads
• Pace needy components• Not shrink below minimum
O/S MemoryO/S Memory
PGA
SGA
PGA
SGA
Streams Performance Advisor
• Auto-discovery of streams topology on multiple databases
• Automatic performance analysis across all databases
• Per-Stream Analysis: • Time-based analysis of each
component (waits, CPU, etc) using ASH
• Bottleneck components• Top wait events of bottleneck
• Per-Component Analysis:• Throughput and latency• Aborted or Enabled
• Integrate with ADDM• Stream errors are integrated with
Server-generated Alerts
ApplyDB 2 DB 3
DB 1
Capture
Queue
Propagation
Propagation
Apply
Queue
Comparative Performance Analysiswith AWR Baselines
• Performance baseline is key for tuning:
• guide set alert thresholds• monitor performance• compare advisor reports
• AWR Baseline contains a set of snapshots for “interesting or reference” time periods
• User-specifiable, schedulable, e.g.: • last Thanksgiving period• every Monday 10am-noon for 4
Mondays• Not automatically purged but can
specify expiration• Automatically captures 8-day
moving window baseline for week-to-week comparisons (default)
time
MetricUnits
Actual
AWR Baseline
8:00 12:00
Manage by Exception with Adaptive Metric Thresholds
Adaptive threshold values follow baseline values
AWR Baseline
Top 3 DBA Activities
• Performance Management• Challenge: Sustain Optimal Performance• Solution: Self-Managing DatabaseChange Management
Challenge: Preserve Order amid Change• Ongoing Administration
• Challenge: Manage More with Less
Lifecycle of Change Management
Make Change
Set Up TestEnvironments
Test
Diagnose & Resolve Problems
Preserve Order Amid Change
Identify Patches & Workarounds
Diagnose & Manage Problems
Provision for Production
Lifecycle of Change Management
Make Change
Set Up TestEnvironments
Test
Diagnose & Resolve Problems
Identify Patches & Workarounds
Diagnose & Manage Problems
Provision for Production
Real Application Testing
……
ClientClient
…Client
Capture DB Workload
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
Middle Tier
Storage
Oracle DB
Replay DB Workload
Production Test
Test migration to RAC
0
20
40
60
80
Tim
e T
aken
(D
ays)
Install & Setup UnderstandApplication
Usage
Identify KeyTransactions
GenerateWorkload
Run Test
DB Replay
LoadRunner2
205
4
80
24 20
DB Replay: ½ month
LoadRunner: 7 ½ months
Total Testing Time
500
Comparison of LoadRunner & DB ReplayTesting e-Business Suite
Database Replay Comparison
Test Effort
Test Coverage
Workload
Database Time MachineTechnology that can only be built by Oracle
1-5% of workflow
Artificial simulated workload
3rd PartyLoad Testing Tools
•Simple app: weeks•Complex app:months
•Simple app: days•Complex app: days
Real application workload
Oracle Database Replay
100% of workflow
……
ClientClient
…Client
Capture SQL
• 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
Middle Tier
Storage
Oracle DB
Re-execute SQL Queries
Production Test
Use SQL Tuning Advisor to tune regression
Real Application Testing with SQL Performance Analyzer (SPA)
SQL Performance Analyzer (SPA)Comparison
NoYes, integrated with SQL Tuning Advisor & SQL
Plan Management
Transfer resolutions to
production
No, not integrated with optimizer
YesTest with production context
No, require explicit polling & external
storage
YesCapture with low overhead
3rd PartySQL Testing Tools
Oracle SPA
Technology that can only be built by Oracle
Lifecycle of Change Management
Make Change
Set Up TestEnvironments
Test
Diagnose & Resolve Problems
Identify Patches & Workarounds
Diagnose & Manage Problems
Provision for Production
Provisioning Automation
Reliable Deployment with Provisioning Automation
• Gold image based provisioning
New in 10gR3 Grid Control:• Greatly simplify RAC
provisioning with automation• Single click RAC scale-out
and scale-back• Provision full RAC and
Clusterware systems • Configures entire stack• Pre-requisite checks &
automatic fix-ups
Stage Gold Image
Create Production RAC System
2
3
11
Create reference RAC System
Scale-out RAC
4
SQL Plan Management
• Allow SQL plan changes, but only for the better !• Optimizer selects a new plan only after verifying it performs
better• All new plans are automatically or manually verified by test
executing them• Better performance verified plans are stored in SQL Plan
Baseline• Using SQL Performance Analyzer (SPA) to pre-seed baseline
with good plans for critical SQLs before making changes
SQL Plan Management is Controlled Plan Evolution
Automatic Diagnostic WorkflowAutomatic Diagnostic Repository
Duplicate Bug?
Yes
EM Support Workbench:Apply Patch or WorkaroundRepair Advisors
No
EM Support Workbench:Package Incident &
Configuration InformationRepair Advisors
Auto Incident CreationFirst-Failure Capture
Critical Error
Alert DBATargeted Health Checks
1 2
Reduce Time to Problem Resolution
34
Reliable Deployment with Patch Automation
• Proactive patch advisories from Oracle Metalink
• Acquisition of the patch based on installation’s configurations and feature usage (new in 11g DB)
• Stage once in the software library for multiple deployments
• Best practice driven patching• Rolling patching support for
RAC/ASM/Clusterware (new in 10gR3 Grid Control)
1-off patch becomes available
Yes
Apply patch
Proactively search for patches relevant
to customer environment
Alert DBADownload Patch
Relevant Patch
Found?
Online Patching
• Patch a running Oracle instance with no downtime• Many one-off patches can be online patched
• Subset of RAC online upgradeable patches• Great for diagnostic patches
• Enable, disable and de-install one-off patches with no downtime• Integrated with OPatch and inventory
• Initially available on Linux and Solaris• Long term goal is online patching of Critical
Patch Updates (CPUs).
Top 3 DBA Activities
• Performance Management• Challenge: Sustain Optimal Performance• Solution: Self-Managing Database
• Change Management• Challenge: Preserve Order amid Change• Solution: Realistic Testing & Provisioning
AutomationOngoing Administration
Challenge: Manage More with Less
• Management by Exception– Automatic notifications on
violations – Proactive security and policy
evaluations– Standardized IT operations
management• Manage Many as One
– Dashboard views– Monitoring templates– Combine targets into Groups and
Systems– Apply tasks and policies to Groups
and Systems
Manage More with Less usingGrid Control
Configuration Management Database (CMDB)
Software Installations
SW and HW Configurations
Resource Relationships
Certified Configurations
Best Practices
• CMDB reports• CMDB Statistics• Policy standards• Audit Reports• Decision support
for incident, problem, change, and release management
• Discover– Automated Inventory and Change
Tracking– All h/w & s/w configurations– Relationships– Oracle inventory, including patch
history and updates• Analyze
– Monitor and audit change– Configuration comparisons against
reference, saved or live • Foundation for compliance and
change control– Map to ITIL and CoBIT frameworks– Policy Management
• Standardize IT operations mgt• Security configuration policies
• Support Service: Oracle Configuration Support Manager
• Faster problem resolution
Manage More with Less usingGrid Control
Oracle Database 11g Solutions to Top DBA Challenges
• Performance Management• Challenge: Sustain Optimal Performance• Solution: Self-Managing Database
• Change Management• Challenge: Preserve Order amid Change• Solution: Real Application Testing & Provisioning Automation
• Ongoing Administration• Challenge: Manage More with Less• Solution: Grid Control