The NextGeneration SelfManaging Database: A Sneak Preview

1
(No Transcript)
2
The Next-Generation Self-Managing Database A
Sneak Preview

• Leng Leng Tan
• Vice President, Database Manageability
  Diagnosability
• Oracle

3
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
Oracles products remain at the sole discretion
of Oracle.
4
Self-Managing DatabaseOracle Database 10g
Manage Performance Resource

• Proven technology
• Reduce Total Cost of Ownership

5
Self-Managing Database The Next Generation
Manage Performance Resource
Manage Change
Manage Fault
Manage Fault
6
Self-Managing Database The Next Generation
ltInsert Picture Heregt
Manage Performance Resource
Manage Change
Manage Fault
Manage Fault
7
Change is the only Constant

• Enterprise production systems are complex
• Actual workloads are difficult to simulate

• Realistic testing before production is
  impossible

• Change is the most common cause of instability

• Reluctant to make changes
• Unable to adopt new competitive technologies

8
Database Replay

• Recreate actual production database workload in
  test environment
• Identify, analyze and fix potential instabilities
  before making changes to production
• Capture Workload in Production
• Capture production workload with actual load
  concurrency
• Move the captured workload to test system
• Replay Workload in Test
• Make the desired changes in test system
• Replay workload with production load
  concurrency
• Analyze Report
• Errors
• Data divergence
• Performance divergence
• Use ADDM, AWR for further performance analysis

9
Pre-Change Production System

Changes Unsupported
Changes Supported
Captured Workload

• Database Upgrades, Patches
• Schema, Parameters
• RAC nodes, Interconnect
• OS Platforms, OS Upgrades
• CPU, Memory
• Storage
• Etc.

Process
Process

Capture Workload

Backup
10
Post-Change Test System
Pre-Change Production System




Processed Captured Workload
Process
Process
Process
Process


Capture Workload

Backup
Can use Snapshot Standby as test system
11
SQL Replay

• Focus on impact of change on SQL query workload
• Capture SQL in Production
• Automatically capture SQL workload over a
  specified period
• Capture SQL text, plans, bind variables,
  execution statistics
• Can capture 10gR2 SQL workload
• Move captured SQL workload to test system
• Replay SQL in Test
• Replay SQL in pre and post-change configurations
• Compare and analyze performance
• For regressed SQL, use SQL Tuning Advisor (10g)
  to improve performance with SQL Profiles
• Changes supported
• Major minor database releases, patches,
  parameters, schema, optimizer statistics, tuning
  recommendations

12
SQL Replay Performance Analysis
13
SQL Plan Management

• Business Requirement
• Data is changing over time
• Statistics and execution plans become suboptimal
• Statistics have to be updated
• Possibly unpredictable changes of execution plans
• Today you have freeze critical plans or
  statistics

• Solution
• Optimizer automatically manages SQL Plan
  Baselines
• Only known and verified plans are used
• Plan changes are automatically verified
• Only comparable or better plans are used going
  forward
• Can pre-seed critical SQL with baselines from SQL
  Replay

SQL Plan Management is controlled plan evolution
14
Self-Managing Database The Next Generation
ltInsert Picture Heregt
Manage Performance Resource
Manage Change
Manage Fault
Manage Fault
15
Automatic Diagnostic
Automatic Diagnostic Repository
Critical Error
2
1

• Alert DBA
• Targeted Health Checks

• Auto Incident Creation
• First-Failure Capture

No
Yes
3
4

• EM Support Workbench
• Package Incident Configuration Information
• Repair Advisors

• EM Support Workbench
• Apply Patch or Workaround
• Repair Advisors

Reduce Time to Problem Resolution
16
Support Workbench
17
Ease Diagnosis withIncident Packaging Service

• Package all Automatic Diagnostic Repository
  information related to an Incident
• Pull in information from other related Incidents
• Request user to capture test cases and more
  diagnostic data, e.g. redo logs
• Package all relevant configuration information
  across all relevant products, e.g. OS, AS,
  eBusiness suites
• Provide user-sensitivity indicator

18
Incident Packaging Service
19
Feature-Based Patching
Database targets/groups
1
2
Proactively push metadata (version, feature
relevance) for evaluation
5
Features versions collected
Apply patch
3
Customer Relevant Patch Found?
Grid/DB Control
Yes

• Automatic correlation against feature usages
  eliminates unnecessary downtime
• Both ad-hoc searches and subscription supported

4
Download patch
20
Self-Managing Database The Next Generation
ltInsert Picture Heregt
Manage Performance Resource
Manage Change
Manage Fault
Manage Fault
21
Self-Managing Database The Next Generation
Manage Change
Manage Fault
Manage Fault
22
Automatic SQL Tuning
PackagedApps
Customizable Apps

• Automatically chooses high-load SQL based on
  response time, throughput and frequency criteria
• Automatically tunes SQL by creating SQL Profiles
• Considers alternate plans from previously known
  ones
• Automatically test executes the tuned plans to
  verify improvement
• Automatically implements greatly (3X) improved
  plans (optional)
• Automatically reports missing access structures,
  stale stats, poorly structured SQL
• Automatically runs during maintenance window or
  manually triggered

Automatic SQL Tuning
Nightly

Index, Stats, Structure Analysis
SQL Profiling
SQL Profiles
Test Execute
Implement
Automatic
Advisor Report
Manual
Well-tuned SQL
23
Partition Management Made Easy

• Partition Advice from Access Advisor
• Provide advice on Partitioning methods
• Range, Range Key
• Interval (new), Interval Key
• Hash, Hash key
• Always equi-partition
• Partitioning advice for tables,indexes, MVs
• Consider entire query DML workload to improve
  query performance
• Integrated, non-conflicting advice with Indexes,
  MVs
• Automatic Interval Partition Creation
• On first insert
• With constant width intervals for dates and
  numbers
• Existing RANGE partitioned tables can be
  converted to INTERVAL
• RANGE and INTERVAL can coexist in a single table

PackagedApps
Customizable Apps
SQL Workload
Access Advisor
Partition Analysis
Partition Advice
Index, MV Advice
Well-Designed Schema
24
Automatic Memory Tuning

• Automatically adapts to workload changes
• Unifies shared (SGA) and process (PGA) memory
  management
• Single dynamic parameter for all database memory
• 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 Memory
O/S Memory
25
ADDM for RAC
Database-Level ADDM

• Performance expert in a box
• Now RAC specialist too!
• Identifies the most Globally Significant
  performance issues for the entire RAC database
• Database-wide and instance-level analysis
• Database-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
• Allows drill down to instances
• Runs proactively every hour (default)
• Directives to filter results

Self-Diagnostic Engine
Instance-Level ADDMs
Node 1
Node 2
Node 3
26
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)

Actual
Metric Units
time
800
1200
27
Manage by Exception with Adaptive Metric
Thresholds
Adaptive threshold values follow baseline values
AWR Baseline
28
Recommended Campground Demos
29
Recommended Sessions
30
Q

A
Q U E S T I O N S
A N S W E R S