Agenda

1
(No Transcript)
2
Agenda

• Sneak Peek at Oracle Database 11g
• Management Enhancements
• Availability Enhancements
• Performance Enhancements
• 11g New Features Summary

3
Oracle Database 11g Change Assurance Capabilities
Detect TunePerformanceChanges
PerformOnline Application Upgrades
Capture ReplayWorkload
ManageExecutionPlanEvolution
Set Up TestEnvironments
PackageIncidentsforSupport
OnlineHot Patching
4
Snapshot Standbysfor Test Environments

• Convert Physical Standby to Snapshot Standby and
  open for writes by testing applications
• ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
• Discard testing writes and catch-up to primary by
  applying logs
• ALTER DATABASE CONVERT TO PHYSICAL STANDBY
• Preserves zero data loss
• But no real time query or fast failover
• Similar to storage snapshots, but
• Provides DR at the same time
• Single copy of storage

Physical Standby Apply Logs
Open Database
Back out Changes
Snapshot Standby Perform Testing
Continuous Redo Shipping
5
Database Replay

• 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

6
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
7
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
8
Capturing the SQL Workload
9
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 10.2 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

10
SQL Replay Performance Analysis
11
Viewing SQL Replay Analysis Results
12
(No Transcript)
13
(No Transcript)
14
Automatic SQL Tuning Configuration
15
(No Transcript)
16
Automatic SQL Tuning Result Summary
17
Automatic SQL Tuning Results Recommendations
18
Automatic SQL Tuning Result Details
19
Automatic SQL Tuning Results Drilldown
20
New Change Assurance Capabilities in Oracle
Database 11g
Detect TunePerformanceChanges
PerformOnline Application Upgrades
Capture ReplayWorkload
ManageExecutionPlanEvolution
Set Up TestEnvironments
PackageIncidentsforSupport
OnlineHot Patching
21
Online Application Upgrade

• Large, mission critical applications are often
  unavailable for tens of hours while a patch or an
  upgrade is installed
• Oracle Database 11g introduces revolutionary new
  capabilities that allow online application
  upgrade with uninterrupted availability of the
  application
• The pre-upgrade application and the post-upgrade
  application can be used at the same time
• End-user sessions therefore enjoy hot rollover

22
Online Application Upgrade The challenge

• The installation of the upgrade into the
  production database must not perturb live users
  of thepre-upgrade application
• The changes must be made in privacy
• Transactions done by the users of the pre-upgrade
  application must by reflected in the post-upgrade
  application
• For hot rollover, we also need the reverse of
  this
• Transactions done by the users of the
  post-upgrade application must by reflected in the
  pre-upgrade application

23
Online Application Upgrade How did we do it?

• Evolutionary capability improvements
• Some table DDLs that used to fail if another
  session had outstanding DML now always succeed
• Others, that cannot succeed while theres
  outstanding DML, are now governed by a timeout
  parameter
• Online index creation and rebuild now never wait
• The dependency model is now fine-grainede.g.
  adding a new column to a table, or a new
  subprogram to a package spec, no longer
  invalidates the dependants

24
Online Application Upgrade Revolutionary new
capabilities

• 11.1 brings the edition, the editioning view, and
  the crossedition trigger
• 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 editions
  columns, or (in hot-rollover) vice-versa

25
New Change Assurance Capabilities in Oracle
Database 11g
Detect TunePerformanceChanges
PerformOnline Application Upgrades
Capture ReplayWorkload
ManageExecutionPlanEvolution
Set Up TestEnvironments
PackageIncidentsforSupport
OnlineHot Patching
26
SQL Plan Managementcontrolled plan evolution

• 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 in
  maintenance window
• Only comparable or better plans are used going
  forward
• Can pre-seed critical SQL with baselines from SQL
  Replay

27
Automatic Diagnostic WorkflowReduce Time to
Problem Resolution
Automatic Diagnostic Repository
Critical Error
2
3
1

• Alert DBA
• Targeted assessments
• Assisted SR Filing

Auto Bug Matching (post 11gR1)

• Incident Packaging Service
• First-failure Capture

No
Duplicate Bug?
Yes
4
5

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

• EM Support Workbench
• Package Incident information
• including CCR info
• Guided Repair

28
New Change Assurance Capabilities in Oracle
Database 11g
Detect TunePerformanceChanges
PerformOnline Application Upgrades
Capture ReplayWorkload
ManageExecutionPlanEvolution
Set Up TestEnvironments
PackageIncidentsforSupport
OnlineHot Patching
29
Incident Packaging Service
30
New Change Assurance Capabilities in Oracle
Database 11g
Detect TunePerformanceChanges
PerformOnline Application Upgrades
Capture ReplayWorkload
ManageExecutionPlanEvolution
Set Up TestEnvironments
PackageIncidentsforSupport
OnlineHot Patching
31
Feature-Based Patching
Recommended interim patch becomes available in
Metalink
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
32
EM-driven Proactive Patching
33
Database feature based Patching
34
Online Patching of One-off Patches

• 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).

35
Online Patch Upgrade using RAC
Emergency One-off Patches
Clients
Clients
Patch
A
1
2
Critical Patch Updates
Initial RAC Configuration
Clients on A, Patch B
Operating System Upgrades
Patch
B
B
3
4
Hardware Upgrades
Upgrade Complete
Clients on B, Patch A
36
Online Database Upgrades using SQL Apply
Upgrade
Redo
Clients
Logs Queue
Version X
Version X
X
X1
1
2
Initial SQL Apply Config
Upgrade node B to X1
Redo
Redo
Upgrade
X1
X1
X1
X
Switchover to B, upgrade A
Run in mixed mode to test
4
3
37
Manageability, High Availability and Performance
Highlights
38
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
• 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
39
Partition Management Made Easy

• Partition Advice from Access Advisor
• Provide advice on Partitioning methods
• 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
40
Flashback Data Archive

• Long term retention - years
• Automatically stores all changes to selected
  tables in Flashback Data Archive
• Archive cannot be modified
• Old data purged per retention policy
• View table contents as of any time using
  Flashback SQL
• Uses
• Change Tracking
• ILM
• Long term history
• Auditing
• Compliance

Select from orders AS OF Midnight
31-Dec-2004
ORDERS
Archive Tables
Changes
User Tablespaces
Flashback Data Archive
Oracle Database
Total Recall
41
Physical Standby with Real-Time Query
Real-time Queries
Concurrent Real-Time Query
Continuous Redo Shipment and Apply
Primary Database
Physical Standby Database

• Read-only queries on physical standby concurrent
  with redo apply
• Supports RAC on primary / standby
• Queries see transactionally consistent results
• Immediate appeal to the many users of physical
  standby
• DR with real time query is unique in the industry
  no idle resources
• Handles all data types, but not as flexible as
  logical standby

42
Rolling Database Upgrades Using Transient Logical
Standby

• Logical standby allows rolling upgrades but has
  data type restrictions
• Temporarily convert physical standby to logical
  to perform a rolling upgrade
• Data type restrictions are limited to short
  upgrade window
• No need for separate logical standby for upgrade

Physical
Logical
Upgrade
Physical
Also possible in 10.2 with more manual steps
43
PerformanceMajor New Performance Innovations

• Automatic native PL/SQL and Java compilation
• Now fully transparent, no C compiler required
• SecureFiles
• New generation LOBs just as fast as files
• Query Results caches
• Fast caching of query/function results for
  read-mostly data
• Table Compression
• Now for OLTP as well as DW applications
• And much more
• RAC optimizations, TimesTen enhancements, faster
  triggers, Auto NFS, faster upgrades, faster
  Streams, faster Data Guard,

44
Oracle SecureFilesUnified Storage

• SecureFiles is a new database feature designed to
  break the performance barrier keeping file data
  out of databases
• Similar to LOBs but much faster, and with more
  capabilities
• Transparent encryption, compression,
  deduplication, etc.
• Preserves the security, reliability, and
  scalability of database
• Superset of LOB interfaces allows easy migration
  from LOBs
• Enables consolidation of file data with
  associated relational data
• Single security model
• Single view of data
• Single management of data

45
Server Results Cachetaking the buffer cache to
the next level

• Automatically Caches results of queries, query
  blocks, or pl/sql function calls
• Cache is shared across statements and sessions on
  server
• Significant speed up for read-only / read-mostly
  data

46
OCI Consistent Client Cachelike cache fusion
between server and client

• Caches query results on client
• Improves performance for read-mostly tables
• Faster response time - eliminates network round
  trips
• Saves server CPU
• Consistent with server
• Cache proactively refreshed when result set
  changes
• Maximum age of consistency info can be set

47
Compression for OLTP

• Oracle Database 9i table compression works for DW
• Compression for bulk load operations
• Direct Path Load
• Create Table Select As
• Compression is now viable for OLTP
• Regular (non-bulk) INSERT, UPDATE, DELETE
• Add, Drop Column
• Compression benefits
• Reduced disk space consumption typically 2x to
  3x
• Reduced IO
• Enhanced cache efficiency
• Better read performance

48
Information Management Platform

• Complete
• Integrated
• Robust
• Scalable
• Secure
• Available on all platforms

49
Information Management

• Complete
• Integrated
• Robust
• Scalable
• Secure
• Available on all platforms

Oracle Content and Records DB11g Feature Refresh
XML DBBinary XML, Schema Evolution
Information RetrievalText Improvements,
Semantic Processing
Oracle Locator SpatialImproved maps, 3D
support, traffic updates
Oracle interMediaDICOM Medical Image support
RelationalSecure Files
50
Addtl Oracle Database 11g Highlights

• High Availability
• Logminer, Physical Standby, Streams support for
  XMLType, TDE
• Fast Start Failover with Async Propagation
• Faster Logical Standby Apply
• Flashback Transaction
• Data Repair Advisor
• Integration
• Faster Gateways
• Gateways to Legacy Databases
• Streams Synchronous Capture
• Streams Faster Apply
• Performance and Scalability
• RAC Scalability Enhancements
• Faster PL/SQL Triggers
• Security
• Tablespace and LOB Encryption
• Secure By Default
• Enterprise Manager support for TDE, OLS, VPD, EUS
• TDE works with LOBS, log miner

• Manageability and Diagnosability
• Unified Automatic Memory Management
• Scheduler supports remote jobs
• Automatic Health Monitoring
• Faster Database Upgrades
• ASM
• Variable extent sizes, Fast resync, Preferred
  reads
• RAC
• Highly optimized protocols
• ADDM for RAC
• Content Management Infrastructure
• Binary XML storage
• Enhanced XML indexing
• Combined Text and Relational Indexes
• DICOM Medical Imaging Support
• Storage Query of Semantic Content (RDF, OWL)
• Business Intelligence
• Next generation OLAP
• Supermodels for Data Mining

51
Using EM to Perform Fast Mirror Resync
52
Setting Up ASM Fast Mirror Resync
53
Monitor Memory Management
54
ADDM for RAC
55
Q

A
Q U E S T I O N S
A N S W E R S
56
(No Transcript)