Docstoc

Data Warehousing with Oracle Database

Document Sample
Data Warehousing with Oracle Database Powered By Docstoc
					Data Warehousing with
Oracle Database 11g Release 2
  Data Warehousing Strategy



“Comprehensive, integrated, and
         complete”
Oracle Data Warehousing Strategy


• Best Database for Data Warehousing
  – 30 years of innovation
  – No other database can compare on the breadth and
    sophistication of Oracle‟s database features
• Within complete solutions
  – Complete database platform capabilities: ELT and Analytics
  – Complete BI and Performance Management solutions from
    Oracle
  – Broadest array of third-party technologies and solutions
• On the right hardware infrastructure
  – Oracle Database Machine
Distributed Data Marts and Servers
Expensive data warehouse architecture



               Data
               Marts




 Online
Analytics                 ETL


            Data Mining
Consolidated Data Warehouse
Single source of truth on low cost servers & storage



               Data
               Marts




 Online
Analytics                 ETL
                                     Oracle Database 11g
                                with integrated ETL, Analytics
            Data Mining                  & Data Mining
      HP Oracle Database Machine:
      The next step in DW Hardware Solutions

      Custom                Reference                   Optimized                 HP Oracle
                           Configurations               Warehouse                 Database
                                                                                   Machine
• Complete Flexibility   • Documented best-          • Scalable systems pre-   • Highest performance
                           practice configurations     installed and pre-
• Any OS, any platform     for data warehousing                                • Pre-installed and pre-
                                                       configured: ready to      configured
• Easy fit into a        • Introduced 2004             run out-of-the-box
  company‟s IT                                                                 • Sold by Oracle
  standards                                          • Introduced 2006
                                                                               • Introduced 2008




                                      Exadata Storage
HP Oracle Database Machine



  “Extreme performance”
HP Oracle Exadata Storage Server
Storage Optimized for Oracle Data Warehouses




   •   12 disk drives, up to 12 TB raw storage
   •   2 Infiniband connections
   •   2 Intel processors, 8 cores
   •   Oracle Enterprise Linux
   •   Automatic Storage Management
   •   Scale storage, processing and network capacity
Query Processing
Using Traditional Storage

               Oracle Database
 What Were           Grid                 Storage Array
 Yesterday‟s
   Sales?


                            Select                 Retrieve
                       sum(sales) where
                        salesdate= „01-             Entire
                         Jun-2009‟ …              Sales Table




                       SUM
Query Processing
Using HP Oracle Exadata Storage Server

               Oracle Database
 What Were           Grid                 Oracle Exadata
 Yesterday‟s                               Storage Grid
   Sales?


                            Select                   Retrieve Sales
                       sum(sales) where             for Jun 01 2009
                        salesdate= „01-
                         Jun-2009‟ …




                       SUM
HP Oracle Database Machine
Powered by Exadata Storage Servers

               • Integrated data warehouse solution
                  – Database Server Grid
                  – Exadata Storage Server Grid
                  – Software installed and configured
               • Extreme Performance
                  – 10-100X faster than conventional systems
                  – Enabled by Exadata Storage Servers
               • Scalability to Petabytes
                  – Add racks for more data and performance
               • Enterprise-Ready
                  – Complete data warehouse functionality
                  – Enterprise-level availability and security
                  – Enterprise-level software and hardware support
Deploy Faster and Reduce Risk

Build From Scratch   OWI Reference        HP Oracle
with Components      Configurations       Database Machine


    Testing and
     Validation



  Installation and      Testing and
    configuration        Validation

   Acquisition of
    components        Installation and
                       configuration

Pre-implementation
  System sizing        Acquisition of
                                         Take delivery of HP Oracle
                       components        Database Machine


      Months              Weeks          < 1 Week after Delivery
HP Oracle Database Machine
Benefits

                      Extreme performance for data warehousing,
     Extreme
                      powered by HP Oracle Exadata Storage Servers
    Performance




                      Enterprise ready for rapid customer success
   Pre-Installed
                      and high return on investment
 and Pre-Configured




                      Complete functionality, with in-database ETL,
     Complete
                      analytics (OLAP, Data Mining) and more
    Functionality
Complete Data Warehousing
  platform functionality

“Bring the analysis to the data,
 not the data to the analysis”
Oracle‟s Data Integration Strategy
Pervasive Data Integration


• Embed Data Integration with Oracle Database
  – Integrated, Optimized and Best for Oracle Database
  – Easiest Path to Get External Information into the Oracle Database

• Provide Comprehensive Data Integration
  –   Comprehensive Heterogeneous Technology Foundation
  –   Integrated Runtime, Data Management Tools and Administration
  –   Best of Breed: Significant Architectural Differentiators vs. Competitors
  –   “Hot Pluggable”: Broad Range of Sources & Packaged Applications

• Pre-Integrate Solutions for Oracle Portfolio
  – Make data integration pervasive with Lower Cost & Complexity
In-Database ETL
Oracle Warehouse Builder

• Benefit from your Oracle investments like no one
  else
  – Better performance through Oracle optimizations
  – Skills, people and technology
• Improved productivity
  – Use the latest Oracle features without rewriting your code
  – Advanced task automation features
• Shorten your project times while improving data
  quality
  – Discover problems up-front with data profiling
  – Improve data quality in a declarative way
Data Integration Platform

                    Leveraging an EL-T approach to data
  Best in-class
                    integration, Oracle outperforms best-of-breed
  Performance
                    tools



                    Declarative and metadata driven design delivers
 Industry Leading
                    a highly maintainable ETL infrastructure
   Productivity




                    ETL and Data Quality integrated with Oracle
      Fully
                    Database, Oracle Business Intelligence and
    Integrated
                    Performance Management Solutions
In-Database Analytics
Extreme Analytics
                       Oracle Analytics

                        • Analytics to the data
                        • Integrated
                        • Secure



Oracle OLAP                                Oracle Data Mining

          • Multi-dimensional                        • Advanced Predictive
            queries                                    Analytics
          • Summary Management                       • Advanced Algorithms
          • Embedded and                             • Embedded and
            Integrated                                 Integrated

     Analyze and Summarize                         Uncover and Predict
Oracle OLAP

• A summary management solution for SQL based business
  intelligence applications
  – An alternative to table-based materialized views, offering improved
    query performance and fast, incremental update

• A full featured multidimensional OLAP server
  – Excellent query performance for ad-hoc / unpredictable query
  – Enhances the analytic content of Business intelligence application
  – Fast, incremental updates of data sets

• Embedded in the Oracle database instance and storage.
  – Safe, secure and manageable.
  – Fully compatible with Grid Computing/Real Application Clusters.
 Cube Organized Materialized Views


          SQL Query                         Summaries

Region                 Date



                                 Query
                                Rewrite



Product               Channel
                                Automatic   OLAP Cube
                                 Refresh
    Analytics in Spreadsheets



                                    Dimensional calculations

            “Bubble-up” functions




 Hierarchical
navigation and                          Trend indicators
 aggregation
Oracle Data Mining


• Automatically sift through very large volumes
  of data to
  – Find hidden patterns
  – Discover new insights
  – Make predictions about the future
Typical Use Cases
• Retail                              • Healthcare                       • Manufacturing
   · Customer segmentation              · Patient procedure                · Root cause analysis of
   · Response modeling                    recommendation                    defects
   · Recommend next likely              · Patient outcome prediction       · Warranty analysis
     product                            · Fraud detection
   · Profile high value customers       · Doctor & nurse note analysis     · Reliability analysis
                                                                           · Yield analysis
• Banking                             • Life Sciences
   · Credit scoring                     · Drug discovery & interaction   • Automotive
   · Probability of default             · Common factors in                · Feature bundling for
   · Customer profitability               (un)healthy patients               customer segments
   · Customer targeting                 · Cancer cell classification       · Supplier quality analysis
• Insurance                             · Drug safety surveillance         · Problem diagnosis
   · Risk factor identification       • Telecommunications               • Chemical
   · Claims fraud                       · Customer churn                   · New compound discovery
   · Policy bundling                    · Identify cross-sell
   · Employee retention                   opportunities                    · Molecule clustering
                                        · Network intrusion detection      · Product yield analysis
• Higher Education
   · Alumni donations                 • Public Sector                    • Utilities
   · Student acquisition                · Taxation fraud & anomalies       · Predict power line /
   · Student retention                  · Crime analysis                     equipment failure
   · At-risk student identification     · Pattern recognition in           · Product bundling
                                          military surveillance            · Consumer fraud detection
In-Database Data Mining
General advantages

• Better Performance
• Data remains in the database
  – Better data security
  – Reduced Complexity
  – Shorter information latency
• Straightforward inclusion within interesting
  and arbitrarily complex queries

   SELECT Customers WHERE Income > 100K,
   AND Probability(Buy Product A) > .85;
In-Database Data Mining
Better Information for OBI EE Reports & Dashboards




       ODM‟s predictions & probabilities
       are available in the Database for
       reporting using Oracle BI EE and
       other tools
Data Analytics Platform

                    In-Database analytics deliver results faster by
   Better Time
                    eliminating data movement and leveraging
    To Insight
                    Oracle‟s scalability



                    Fully embedded analytics deliver enterprise
   Enterprise
                    scalability, security and maintainability
     Ready




                    No duplication of data and open standards
  Lower Total
                    hardware and software deliver a lower TCO
Cost of ownership
                    compared to proprietary solutions
 The relational foundation of
Oracle Database 11g Release 2

“More than a decade of software
         innovation”
Data Warehousing
Back to the future

                     • 1995: Oracle Version 7.3
                       – Parallel execution
                     • 1998: Oracle 8.0
                       – Partitioning
                     • 2001: Oracle 9.0
                       – Real Application Clusters
                     • 2003: Oracle 9.2
                       – Direct path compression
                     • 2005: Oracle 10g
                       – Automatic Storage Management
                     • 2007/2008: Oracle 11g
                       – Extreme IO performance with Exadata
                     • 2009: Oracle 11g Release 2
Large-Scale Data Warehouses
Feature Usage




   Source: Oracle ST Survey
 Data Warehouse Reference Architecture




Base data warehouse schema            Application-specific performance structures
Atomic-level data, 3nf design         Summary data / materialized views
Supports general end-user queries     Dimensional view of data
Data feeds to all dependent systems   Supports specific end-users, tools, and applications
  Data Warehouse Reference Architecture
• Access and performance layer needs smart software
  – Sophisticated database optimization techniques
     – Advanced Indexing
     – Star schema optimizations
     – Materialized views
     – Result Caches
  – Performance determined by algorithms and access paths
• Foundation layer needs power lifting
  – Brute-force query execution
     – Large amounts of hardware
     – Parallelize everything
  – Performance determined by hardware capabilities

          Benefits with Oracle Database 11g
Oracle Database 11g
Sample Enhancements

Smart software
• Enhanced and robust query execution
• Performance improvements
• New analytical capabilities
Power lifting
• More partitioning capabilities
• Enhanced pruning
• Automatic and in-memory parallel execution
Enhanced and Robust Query Execution


• Guaranteed plan stability with controlled plan
  evolution
  – SQL Plan Management
• Collect more statistics – better, faster
  – Multi-column statistics
  – Faster calculation of NDV
• Control and manage new statistics propagation
  – Separate statistic discovery and publication
• Enhanced bind peeking
  – Properly address data skew
SQL Plan Management
Controlled plan evolution

    SQL is issued


Generate execution plan




        Is this       If yes             Is this
         SQL
                                                         If yes
                                          Plan                    Execute SQL
       tracked                           known

              If no                           If no
     Execute SQL
                          Queue new                 Execute
                            plan for               known plan
                          verification
Multi Column Statistics


• Provides a mechanism to collect statistics on a group
  of columns from a single table
  – Extension for single column statistics
• Full integration into existing statistics framework
  – Automatically maintained with column statistics
  – Instantaneous and transparent benefit for any migrated
    application
• Accurate cardinalities for inter-related columns
  – Multiple predicates on the same table are estimated
    correctly
Faster Statistics Collection


• Faster and more efficient NDV calculation
  – Hash based algorithm enables „fast scanning‟
     – “Speed of sampling with the accuracy of compute”
  – Optimized resource consumption
• Incremental global statistics
  – Gather statistics for touched partition(s) ONLY
  – Table (global) statistics are built from new partial statistics
    and stored partition synopsis
Database Result Cache

• 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
• Full consistency and proper semantics
    – Cache refreshed when any underlying table updated
Database Result Cache
Sample Improvements

• Retail customer data set
  – Concurrent users submitting queries randomly
     – Executive dashboard with 12 heavy analytical queries
  – Cache results only at in-line view level
  – 12 queries run in random, different order – 4 queries cached
• Measure average, total response time for all users
              # Users   No cache   Cache      Improvement


              2            186 s      141 s          24%

              4            267 s      201 s          25%

              8            447 s      334 s          25%
                                                                        Release 2

Materialized View Improvements

• Enhancements for both on-demand and on-commit materialized
  views
• Enhanced materialized view log maintenance
  – Dramatically reduced setup cost with SCN-based MV log
       – Should be used by default with 11.2 onwards
  – Buffered log insertion
  – Delayed purging in the background
       – Enabled with new MV log syntax
• Enhanced MV refresh
  – Better algorithms for incremental refresh
       – Trusted usage of primary key - foreign key relationship
  – Enhanced DML tracking
  – Delayed purging in the background



                                                                   40
                                                                                                                                 Release 2

Enhanced MV Refresh
Refresh time for conventional insert, aggregate MV
                                    120



                                    100
     Refresh time new vs. old [%]




                                    80


                                                                                                      MV refresh 11.2
                                    60
                                                                                                      MV refresh pre 11.2


                                    40



                                    20



                                     0
                                          20000   50000           250000            500000   750000
                                                          number of inserted rows




• On average 30 – 40% better refresh performance with
  Oracle Database 11g Release 2

                                                                                                                            41
 Native Support for Pivot and Unpivot
                                       SALESREP   QU    REVENUE
                                     ----------   -- ----------
                                            100   Q1        230
                                            100   Q2        240
  SALESREP    Q1    Q2    Q3    Q4          100   Q3        260
---------- ----- ----- ----- -----          100   Q4        300
       100   230   240   260   300          101   Q1        200
       101   200   220   250   260          101   Q2        220
       102   260   280   265   310          101   Q3        250
                                            101   Q4        260
                                            102   Q1        260
                                            102   Q2        280
                                            102   Q3        265
                                            102   Q4        310
 Native Support for Pivot and Unpivot
                                       SALESREP   QU    REVENUE
                                     ----------   -- ----------
QUARTERLY_SALES                             100   Q1        230
                                            100   Q2        240
  SALESREP    Q1    Q2    Q3    Q4          100   Q3        260
---------- ----- ----- ----- -----          100   Q4        300
       100   230   240   260   300          101   Q1        200
       101   200   220   250   260          101   Q2        220
       102   260   280   265   310          101   Q3        250
                                            101   Q4        260
                                            102   Q1        260
                                            102   Q2        280
                                            102   Q3        265
                                            102   Q4        310

     select * from quarterly_sales
     unpivot include nulls
     (revenue for quarter in (q1,q2,q3,q4))‫‏‬
     order by salesrep, quarter ;
 Native Support for Pivot and Unpivot
                                     SALES_BY_QUARTER

                                       SALESREP   QU    REVENUE
                                     ----------   -- ----------
                                            100   Q1        230
                                            100   Q2        240
  SALESREP 'Q1' 'Q2' 'Q3' 'Q4'              100   Q3        160
---------- ----- ----- ----- -----          100   Q4         90
       100   230   240   260   300          100   Q3        100
       101   200   220   250   260          100   Q4        140
       102   260   280   265   310          100   Q4         70
                                            101   Q1        200
                                            101   Q2        220
                                            101   Q3        250
                                            101   Q4        260
                                            102   Q1        260

     select * from sales_by_quarter
     pivot (sum(revenue)‫‏‬
     for quarter in ('Q1','Q2','Q3','Q4'))‫‏‬
     order by salesrep ;
                                                  Release 2

Enhanced SQL Processing capabilities


• Recursive WITH clause
  – ANSI-compliant recursive functionality
  – Superior functionality over CONNECT BY
• New analytical window functions
  – Nth value for inter-row calculation
  – New aggregate function LISTAGG
• Optimized analytical processing
  –   Hash based DISTINCT aggregation
  –   Parallelization of window functions
  –   NULL-aware anti join
  –   GROUP BY aggregation improvements



                                             45
                                                  Release 2

Recursive WITH Clause


• ANSI-compliant recursive functionality
• Superior performance over CONNECT BY
      WITH rw (levl, empno, ename) AS
       ( SELECT 1 levl, empno, ename
         FROM emp
         WHERE‫‏‬ename‫‘‏=‏‬KING’
           UNION ALL
         SELECT levl + 1, e.empno, e.ename
         FROM emp e, rw r
         WHERE r.empno = e.mgr
       )
       SELECT * FROM rw
       ORDER BY levl, ename;



                                             46
                                                                     Release 2

Enhanced Analytical Functions

• New aggregation LISTAGG flattens result sets into a single
  record
  – Common requirement for 1:many correlations, e.g. hierarchical
    data, bill of materials
• Example:
 SELECT deptno,
        LISTAGG(ename, '; ') WITHIN GROUP (order by
 ename) FROM emp GROUP BY deptno;

 DEPTNO
 LISTAGG(ENAME,';')WITHINGROUP(ORDERBYENAME)
 ------    ------------------------------------------
 -
   10      CLARK; KING; MILLER
   20      ADAMS; FORD; JONES; SCOTT; SMITH
   30      ALLEN; BLAKE; JAMES; MARTIN; TURNER; WARD

                                                                50
                                                                                                      Release 2

Oracle Partitioning
Over a decade of development
                  Core functionality            Performance                  Manageability

Oracle8           Range partitioning            “Static” partition pruning   Basic maintenance
                  Global range indexes                                       operations: add, drop,
                                                                             exchange
Oracle8i          Hash and composite range-     Partition-wise joins         Merge operation
                  hash partitioning             “Dynamic” pruning

Oracle9i          List partitioning                                          Global index
                                                                             maintenance

Oracle9i R2       Composite range-list          Fast partition split
                  partitioning

Oracle10g         Global hash indexes                                        Local Index maintenance


Oracle10g R2      1M partitions per table       “Multi-dimensional”          Fast drop table
                                                pruning

Oracle Database   More composite choices                                     Interval Partitioning
11g               REF Partitioning                                           Partition Advisor
                  Virtual Column Partitioning
Oracle 11g R2     Enhanced REF and virtual      Multi-predicate pruning
                  partitioning                  Multi-branch execution
                                                                   Release 2

Oracle Partitioning
Multi-Predicate Pruning

• All predicates on partition key will used for pruning
  – Dynamic and static predicates will now be used combined
• Example:
  – Star transformation with pruning predicate on both the FACT
    table and a dimension                           Static pruning
    FROM‫‏‬sales‫‏‬s,‫‏‬times‫‏‬t‫…‏‬
    WHERE s.time_id = t.time_id ..
    AND t.fiscal_year in (2000,1999)
    AND s.time_id
      between TO_DATE('01-JAN-1999','DD-MON-1999')
      and TO_DATE('01-JAN-2000','DD-MON-YYYY')
                                                 Dynamic pruning


                                                            52
                                                          Release 2

Oracle Partitioning
Intelligent Multi-Branch Execution




• Intelligent UNION ALL expansion in the presence of
  partially unusable indexes
  – Transparent internal rewrite
  – Usable index partitions will be used
  – Full partition access for unusable index partitions
                                                               Release 2

Enhanced Parallel Execution


• Prior to Oracle Database 11g Release 2, Parallelism
  is almost completely manual
  – Decide if the workload warrants parallelism
  – Determine ideal DOP for each table with manual tuning
  – Generally reserved for well-defined workload (large SQL)
     – Some workload control mechanism with DBRM
• Oracle Database 11g Release 2 automatically
  decides if a statement
  • Executes in parallel or not and what DOP it will use
  • Can execute immediately or will be queued
  • Will take advantage of aggregated cluster memory or not
                                                                    Release 2

 Automatic Degree of Parallelism
 Auto DOP

• Optimizer derives the DOP from the statement based on
  resource requirements
  – Cost for all scans operations is considered
• Applies to all types of statements
  – Queries, DML, and DDL
• Explain plan has been enhanced to show DOP selected
• SQL Tune now uses auto DOP to recommend parallelism
• Safe enablement
  – Can be „partially‟ switched on for all parallel statements without a
    specific degree of parallelism
                                                                                       Release 2

    How Auto Degree of Parallelism works

   SQL           Statement is hard parsed
                                              If estimated time           Optimizer determines
statement        And optimizer determines
                                              greater than threshold      ideal DOP
                 the execution plan




                                            Actual DOP = MIN(PARALLEL_DEGREE_LIMIT, ideal DOP)
    If estimated time less
    than threshold


                                                                         Statement
                                                                         executes in parallel
                     Statement
                  executes serially
                                                             Release 2

Parallel Statement Queuing


• Oracle automatically decides if a statement can
  execute immediately or not
  – Queue the statement if there are not enough PX servers
    available
  – Prevents serializing when PX servers are not available
  – Prevents system thrashing
• Monitors RAC-wide availability of PX servers
  – Adaptive to dynamic environments
                                                                                 Release 2


     Parallel Statement Queuing

   SQL          Statement is parsed        If not enough parallel
statements      and Oracle automatically   servers available queue
                determines DOP             statements


                                             64        64
                                                       32      16
                                                               32     16
                                                                     128

                                                     FIFO Queue

                                                  When the required number
                                                  of parallel servers become
        If enough parallel                        available the first stmt on
        servers available                         the queue is dequeued
        execute immediately                       and executed


                              8
                                                                                128
                                                               Release 2

In-Memory Parallel Execution
Data warehousing Grid architecture for memory

• Real Application Clusters brings the Grid to the
  server tier (2001)
  – Scale-out using low cost commodity servers
• Exadata brings the Grid to the storage tier (2008)
  – Scale-out using commodity storage
• In-memory parallel execution in 11.2 brings the Grid
  for DW to the memory (2009)
  – Scale-out across the whole cluster‟s memory for a single
    operation
                                                         Release 2

Prior to Oracle Database 11gR2
Parallel Execution and the buffer cache
                 Cache Fusion


               Real Application
                  Clusters        After the first query is
                                  executed different parts of
                                  table T1 are found in both
                                  buffer caches
                                  From the second query
                                  onwards cache fusion is
                                  used to retrieve necessary
                                  data from T1
                                  Until a complete copy of T1
             Automatic Storage
                                  is found in both caches
               Management
                                                                  Release 2

In-Memory Parallel Execution


• Use the aggregated memory available on the cluster
  – Parallel execution can use the total memory of all RAC
    nodes to cache large objects
  – Each RAC node caches a deterministic subset of the tables
  – Future scans will reuse the data in cluster-wide cache
• Dramatic runtime improvements through in-memory
  parallel execution
  – Physical disk IO will be dramatically reduced or completely
    eliminated
     – Data transfer with the speed of memory
  – Not all data has to be cached to leverage this optimization
     – Missing data will be read from disk
                                                                                      Release 2

    In-Memory Parallel Execution

   SQL          Determine the size of the   Table is a good candidate   Fragments of Table are
statement       table being looked at       for In-Memory Parallel      read into each node‟s
                                            Execution                   buffer cache




                                 Table is
Table is extremely small         extremely Large




                                                                         Only parallel server on
                                                                         the same RAC node
              Read into the buffer                                       will access each
              cache on any node               Always use direct read     fragment
                                              from disk
                                              Release 2

Enhanced Parallel Execution
Benefits

• Less DBA management,
  – Parallelism is determined automatically
• Better scalability on RAC
  – Use aggregated buffer caches
• Better prevention of resources exhaustion
  – Parallel Statement queuing
                                                                      Release 2

And much more ….

Oracle Database 11g Release 1
• Real time SQL monitoring
• Advanced compression
• Active DataGuard
• Real Application Testing
Oracle Database 11g Release 2
• Segment creation on demand
• Online application upgrade with edition based redefinition
• Extended heterogeneous connectivity with Oracle Warehouse Builder
Oracle Exadata Release 2
• Hybrid columnar compression
• Storage indexes
• Enhanced offloading capabilities, e.g. data mining model scoring
Real-time SQL Monitoring
Real-time SQL Monitoring
Advanced Compression
Further cost reduction

           • Compress large application tables
             – Transaction processing, data warehousing
           • Compress all data types
             – Structured and unstructured data types
           • Improve query performance
             – Cascade storage savings throughout data center

                          Up To



                         4X
                          Compression
Data Warehousing Platform

                     Oracle runs the largest data warehouses in the
Proven Performance
                     world delivering value to thousands of
   and Scalability
                     customers



                     Any workload, any query requirement, any
   Best in-class
                     number of users in an ever changing
      Agility
                     environment



                     A complete data warehouse platform including
    Enterprise
                     advanced security, high availability and
      Ready
                     superior functionality
Benefit from Oracle Database 11g
            Release 2

 “Upgrading was never easier”
                                                 Release 2

Upgrading to Oracle Database 11g
Enabling fast and safe upgrades

• Guarantee plan stability throughout upgrade
  – Seed the system with known execution plans
     – From a SQL tuning set
     – From the cursor cache
     – Import them via a staging table
     – From Stored Outlines
• Controlled rollout of new functionality
  – Enhanced parallel execution
   SQL Plan Management
   General upgrade strategy

               Begin                      Oracle Database 11g
                with
                          O_F_E=10
                                                                                Plan History

Run all SQL in the Application                                                      GB            GB
  and auto load SQL Plan                                                  GB
                                                                                    HJ            HJ
  Baselines with 10g plan                                                 HJ
                                                                                     HJ            HJ
                                                                           NL
                                                       11g plan queue                            No plan
                                                       for verification                        regressions
          After plans
          are loaded      O_F_E=11
            change

                                     optimizer_features_enable



   • Seeding the SQL Plan Baselines with 10g plans
        – No plan change on upgrade
   • Switch optimizer_features_enable to 11g
        – New 11g plans will tracked
        – New plans only used validation
                                                                                          Release 2

SQL Plan Management
Upgrade scenario from earlier versions

                              Oracle Database 11gR2

                                                                  Plan History
       Stored outlines
                                         After Migration
        automatically
                                            all stored                   GB         GB
          detected
                                         outlines plans                  HJ         HJ
                                             are plan
        DBA offered to                                                    HJ         HJ
                                            baselines
        migrate to SPM
                                                                                   No plan
                                                                                 regressions




                               Create
                               stored    GB       GB       GB
                              outlines
                                         HJ        HJ      HJ
                               for all
                             important     HJ       HJ      HJ    Well
                             SQL stmts                           tuned
                                                                 plans


                 Oracle 9i
                                                                        Release 2
  Controlled Rollout
  Gradually introduce Auto Parallel Execution
• Control adoption of new parallel execution functionality
  with PARALLEL_DEGREE_POLICY
  • Manual
     –   As before, DBA must manually specify all aspects of PX
     –   No auto DOP
     –   No statement queuing
     –   No in-memory parallel execution
  • Limited
     – Restricted AUTO DOP for queries with tables decorated with default
       PARALLEL
     – No statement queueing
     – No in-memory parallel execution
  • Auto
     – All qualified statements subject to executing in parallel
     – Statements can be queued
     – In-memory parallel execution available
Oracle Data Warehousing Strategy


Oracle Database 11g Release 2 represents
• Best Database for Data Warehousing
  – 30 years of innovation
  – No other database can compare on the breadth and
    sophistication of Oracle‟s database features
• Within complete solutions
  – Complete database platform capabilities: ELT and Analytics
  – Complete BI and Performance Management solutions from
    Oracle
  – Broadest array of third-party technologies and solutions
• On the right hardware infrastructure
  – HP Oracle Database Machine

				
DOCUMENT INFO