Highlights of Oracle Database 11g: Top Picks of Manageability & - - PowerPoint PPT Presentation

<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

S t

• r

a g e B a c k u p M e m

• r

y A p p s / S Q L S c h e m a R A C R e c

• v

e r y R e p l i c a t i

• n

Auto-Tuning Tuning Advisory Instrumentation

L

• w

I m p a c t I n t e g r a t e d A d a p t i v e

Sustain Optimal Performance with Self-Managing Database

S t

• r

a g e B a c k u p M e m

• r

y A p p s / S Q L S c h e m a R A C R e c

• v

e r y R e p l i c a t i

• n

Auto-Tuning Tuning Advisory Instrumentation

L

• w

I m p a c t I n t e g r a t e d A d a p t i v e

Sustain Optimal Performance with Self-Managing Database

S t

• r

a g e B a c k u p M e m

• r

y

• A

p p s / S Q L S c h e m a

• R

A C R e c

• v

e r y R e p l i c a t i

• n

Auto-Tuning Tuning Advisory Instrumentation

L

• w

I m p a c t I n t e g r a t e d A d a p t i v e

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 Node 4

Database Level Analysis

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

Packaged Apps Custom Apps

Automatic SQL Tuning

Auto Capture High-Load SQL SQL Profiles Nightly Well-tuned SQL Automatic implement Manually implement SQL Analysis Report

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

• 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

SQL

<Insert Picture Here>

DEMO Real-time SQL Monitoring

• Oracle Database 11gR1
• Oracle Grid Control 11g (Alpha)

0% 25% 50% 75% 100%

Time Steps

9i 10g 11g

Manageability Comparison of Oracle Database 9i, 10g, and 11g

Summary Summary Oracle 9i vs. 10g 44% less time 47% fewer steps Oracle 9i vs. 10g 44% less time 47% fewer steps Oracle 10g vs. 11g 26% less time 31% fewer steps Oracle 10g vs. 11g 26% less time 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

Make Change Set Up Test Environments 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 Test Environments Test Diagnose & Resolve Problems

Preserve Order Amid Change

Identify Patches & Workarounds Diagnose & Manage Problems Provision for Production

Data Masking

What

• The act of anonymizing customer,

financial, or company confidential data to create new, legible data which retains the data's properties, such as its width, type, and format. Why

• To protect confidential data in test

environments when the data is used by developers or offshore vendors

• When customer data is shared with

3rd parties without revealing personally identifiable information

45,000 093-44-3823 FIORANO 80,000 989-22-2403 D’SOUZA 60,000 323-22-2943 BENSON 40,000 203-33-3234 AGUILAR

SALARY SSN LAST_NAME

45,000 111-49-3849 Glpokopa 80,000 111-97-2749 Kadsfkie 60,000 111-34-1345 Nareopi 40,000 111—23-1111 Jalfdkjd

SALARY SSN LAST_NAME

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

Data Masking

Production Staging Mask Test Test Clone Clone

Lifecycle of Change Management

Make Change Set Up Test Environments Test Diagnose & Resolve Problems Identify Patches & Workarounds Diagnose & Manage Problems Provision for Production

Real Application Testing

Real Application Testing with Database Replay

… … Client Client

…

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

20 40 60 80

Time Taken (Days)

Install & Setup Understand Application Usage Identify Key Transactions Generate Workload Run Test DB Replay LoadRunner

2 20 5 4 80 24 20 DB Replay: ½ month LoadRunner: 7 ½ months Total Testing Time 5

Comparison of LoadRunner & DB Replay Testing e-Business Suite

<Insert Picture Here>

DEMO Database Replay

Database Replay Comparison

Test Effort Test Coverage Workload

Database Time Machine Technology that can only be built by Oracle

1-5% of workflow Artificial simulated workload 3rd Party Load Testing Tools

• Simple app: weeks
• Complex app:months
• Simple app: days
• Complex app: days

Real application workload Oracle Database Replay 100% of workflow

… … Client Client

…

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 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 1 3 2

Database Upgrade: 10g to 11g After Using SQL Plan Management

1 2

SQL Plan Baselines Accepted

No Regressions!

Lifecycle of Change Management

Make Change Set Up Test Environments 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 1

Create reference RAC System Scale-out RAC

4

Automatic Diagnostic Workflow

Automatic Diagnostic Repository

Duplicate Bug? Yes EM Support Workbench: Apply Patch or Workaround Repair Advisors No EM Support Workbench: Auto SR Creation Package Incident & Configuration Information Repair Advisors Auto Incident Creation First-Failure Capture

Critical Error

Alert DBA Targeted Health Checks 1 2

Reduce Time to Problem Resolution

3 4 OCM Connection

EM Support Workbench

Complete Diagnostic Package

Reliable Deployment with Patch Automation

• Proactive patch advisories from

Oracle Metalink

• Acquisition of the patch based on

installation’s configurations and feature usage

• 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 DBA Download Patch Relevant Patch Found?

Change Without Risk with Oracle Database 11g Solutions

• Performance Management
• Challenge: Sustain Optimal Performance amid Workload

Changes

• Solution:

Self-Managing Database

• Change Management
• Challenge: Preserve Order amid Infrastructure Changes
• Solution:

Real Application Testing & Provisioning Automation

Recommended Campground Demos

Moscone West Exhibit Hall Change Management & Data Masking for DBAs Moscone West Exhibit Hall Self-Managing Database: Automatic Application & SQL Tuning Moscone West Exhibit Hall Self-Managing Database: Oracle Database 11g SQL Plan Management Moscone West Exhibit Hall Self-Managing Database: Automatic Fault Diagnostics Moscone West Exhibit Hall Self-Managing Database: Automatic Performance Diagnostics Moscone West Exhibit Hall Oracle Real Application Testing: SQL Performance Analyzer Moscone West Exhibit Hall Oracle Real Application Testing: Database Replay

Location Demo

Recommended Sessions (Monday Sessions)

Location Time Date Session Title

Moscone West: 2022 - L2 12:30 p.m. Monday, Nov. 12 ROI of Oracle Database Management Packs: A Presentation by Forrester Research, Inc. Moscone West: 2001 – L2 4:45 p.m. Monday, Nov. 12 Detecting Performance Problems Accurately and Quickly: Essential Diagnostics Techniques for Oracle DB 11g Moscone South: 104 3:15 p.m. Monday, Nov. 12 Highlights of Oracle Database 11g: Top Picks of New Manageability and Real Application Testing Features Moscone South: 309 4:45 p.m. Tuesday,

• Nov. 13

Oracle 11g Real Application Testing: Change Without Risk with Database Replay Moscone West: 3009 – L3 12:15 p.m. Tuesday,

• Nov. 13

Storage Secrets for DBAs: What Your Storage Administrator Doesn’t Want You to Know Moscone South: 309 12:15 p.m. Tuesday,

• Nov. 13

Oracle 11g Real Application Testing: Avoid Performance Regressions with SQL Performance Analyzer Moscone West: 2001 – L2 4:45 p.m. Tuesday,

• Nov. 13

Using Active Session History for Performance Tuning: Advanced Topics in Performance Diagnostics

Please note date and location change for this session

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