Agenda

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. Client Client Client App Server App Server App Server Process 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. Client Client Client … App Server App Server App Server Replay Driver Replay Driver Process Process 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. Automatic SQL Tuning Configuration

12. Automatic SQL Tuning Results Recommendations

13. New Change Assurance Capabilities in Oracle Database 11g Detect &TunePerformanceChanges PerformOnline Application Upgrades Capture &ReplayWorkload ManageExecutionPlanEvolution Set Up TestEnvironments PackageIncidentsforSupport OnlineHot Patching

14. 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

15. 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

16. 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 there’s outstanding DML, are now governed by a timeout parameter • Online index creation and rebuild now never wait • The dependency model is now fine-grained:e.g. adding a new column to a table, or a new subprogram to a package spec, no longer invalidates the dependants

17. 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 edition’s columns, or (in hot-rollover) vice-versa

18. New Change Assurance Capabilities in Oracle Database 11g Detect &TunePerformanceChanges PerformOnline Application Upgrades Capture &ReplayWorkload ManageExecutionPlanEvolution Set Up TestEnvironments PackageIncidentsforSupport OnlineHot Patching

19. 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

20. 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: • Package Incident information including CCR info • Guided Repair EM Support Workbench: • Apply Patch or Workaround • Guided Repair

21. New Change Assurance Capabilities in Oracle Database 11g Detect &TunePerformanceChanges PerformOnline Application Upgrades Capture &ReplayWorkload ManageExecutionPlanEvolution Set Up TestEnvironments PackageIncidentsforSupport OnlineHot Patching

22. Incident Packaging Service

23. New Change Assurance Capabilities in Oracle Database 11g Detect &TunePerformanceChanges PerformOnline Application Upgrades Capture &ReplayWorkload ManageExecutionPlanEvolution Set Up TestEnvironments PackageIncidentsforSupport OnlineHot Patching

24. Feature-Based Patching Recommended interim patch becomes available in Metalink Database targets/groups • Automatic correlation against feature usages eliminates unnecessary downtime • Both ad-hoc searches and subscription supported 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 4 Download patch

25. EM-driven Proactive Patching

26. Database feature based Patching

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

28. 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 4 3 Hardware Upgrades Upgrade Complete Clients on B, Patch A

29. A A A B B B A B Patch Set Upgrades Major Release Upgrades Cluster Software & Hardware Upgrades Online Database Upgrades using SQL Apply Upgrade Redo Clients Logs Queue Version X Version X X X+1 1 2 Initial SQL Apply Config Upgrade node B to X+1 Redo Redo Upgrade X+1 X+1 X X+1 Switchover to B, upgrade A Run in mixed mode to test 4 3

30. High-Load SQL AWR 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 Nightly Automatic SQL Tuning SQL Profiling Index, Stats, Structure Analysis SQL Profiles Test Execute Implement Automatic Advisor Report Manual Well-tuned SQL

31. 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 Index, MV Analysis Partition Management Made Easy PackagedApps Customizable Apps SQL Workload Access Advisor Partition Analysis Partition Advice Index, MV Advice Well-Designed Schema

32. Flashback Data Archive Select * from orders AS OF ‘Midnight 31-Dec-2004’ • 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 ORDERS Archive Tables Changes User Tablespaces Flashback Data Archive Oracle Database Total Recall

33. Physical Standby with Real-Time Query Real-time Queries Concurrent Real-Time Query • 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 Continuous Redo Shipment and Apply Primary Database Physical Standby Database

34. 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, …

35. 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

36. Q2: Use it transparently Q1: build query cache GBY GBY GBY GBY GBY GBY join join join join join join join join join join join join join T2 T6 T1 T5 T3 T4 T4 T2 T5 T1 T1 T2 T3 T6 T3 cache 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

37. Consistent Caching Application Server Database 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

38. 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

39. 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 Windows VSS Writer for Point In Time Copies 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 Enterprise Manager is DW aware PIVOT/UNPIVOT operators Addt’l Oracle Database 11g Highlights

40. Q & A Q U E S T I O N S A N S W E R S