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
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
  – 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


Analytics                 ETL

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


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

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

Query Processing
Using HP Oracle Exadata Storage Server

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

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

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

  Installation and      Testing and
    configuration        Validation

   Acquisition of
    components        Installation and

  System sizing        Acquisition of
                                         Take delivery of HP Oracle
                       components        Database Machine

      Months              Weeks          < 1 Week after Delivery
HP Oracle Database Machine

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

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

                      Complete functionality, with in-database ETL,
                      analytics (OLAP, Data Mining) and more
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
  – 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
  – 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

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

                    ETL and Data Quality integrated with Oracle
                    Database, Oracle Business Intelligence and
                    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


Product               Channel
                                Automatic   OLAP Cube
    Analytics in Spreadsheets

                                    Dimensional calculations

            “Bubble-up” functions

navigation and                          Trend indicators
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
                    scalability, security and maintainability

                    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
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
  – 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
                                                         If yes
                                          Plan                    Execute SQL
       tracked                           known

              If no                           If no
     Execute SQL
                          Queue new                 Execute
                            plan for               known plan
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
• Accurate cardinalities for inter-related columns
  – Multiple predicates on the same table are estimated
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
• 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
• 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

                                                                                                                                 Release 2

Enhanced MV Refresh
Refresh time for conventional insert, aggregate MV

     Refresh time new vs. old [%]


                                                                                                      MV refresh 11.2
                                                                                                      MV refresh pre 11.2



                                          20000   50000           250000            500000   750000
                                                          number of inserted rows

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

 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

                                       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

                                                  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
           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;

                                                                     Release 2

Enhanced Analytical Functions

• New aggregation LISTAGG flattens result sets into a single
  – 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;

 ------    ------------------------------------------
   10      CLARK; KING; MILLER

                                                                                                      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,
Oracle8i          Hash and composite range-     Partition-wise joins         Merge operation
                  hash partitioning             “Dynamic” pruning

Oracle9i          List partitioning                                          Global index

Oracle9i R2       Composite range-list          Fast partition split

Oracle10g         Global hash indexes                                        Local Index maintenance

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

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

                                                          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

                                                                         executes in parallel
                  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
  – 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

                                                     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

                                                               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
                                                         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
                                                                  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
     – 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

• 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

Data Warehousing Platform

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

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

                     A complete data warehouse platform including
                     advanced security, high availability and
                     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
                                                                                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
                                                       11g plan queue                            No plan
                                                       for verification                        regressions
          After plans
          are loaded      O_F_E=11


   • 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
                                            all stored                   GB         GB
                                         outlines plans                  HJ         HJ
                                             are plan
        DBA offered to                                                    HJ         HJ
        migrate to SPM
                                                                                   No plan

                               stored    GB       GB       GB
                                         HJ        HJ      HJ
                               for all
                             important     HJ       HJ      HJ    Well
                             SQL stmts                           tuned

                 Oracle 9i
                                                                        Release 2
  Controlled Rollout
  Gradually introduce Auto Parallel Execution
• Control adoption of new parallel execution functionality
  • 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
     – 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
  – Broadest array of third-party technologies and solutions
• On the right hardware infrastructure
  – HP Oracle Database Machine