11g Grid and OLTP New Features

					Grid and OLTP




                • Real Application Clusters
                • Automatic Storage Management
                • Performance Enhancements
                   • Results Caches
                   • Optimizer Enhancements
                • TimesTen
Real Application Clusters
 RAC Optimized Cache Fusion
 protocols

• Improved performance for read-intensive workloads
   • improves any read from disk (not cache) whether short
     random reads or large table scans
• Throughput improved up to 70% for internal read-only
 benchmark
   RAC Other Enhancements


• Fine tune performance, scaling,
  failover, management
• Seamless integration with XA       Database
  and Microsoft Transaction Server
• Faster and more robust handling
  of instance crashes and hangs
  • Most of this also in 10.2.0.3
                                     Storage
• Runtime Connection Load
 Balancing works with OCI
 Session Pools
Database Control 11g
Tiled Instance Charts
           ADDM for RAC
                                                     •   Performance expert in a
                                                         box
                 Database-Level                      •   Identify the most
                 ADDM                                    “Globally Significant”
                                                         performance problems for
                 11g                                     the entire RAC cluster
                                                         database
                   Self-Diagnostic Engine
                                                     •   Database-wide analysis
                                                         of:
                                                           •   Global cache
                                                               interconnect issues
                                                           •   Global resource
                                                               contention, e.g. IO
Instance-Level                                                 bandwidth, hot blocks
ADDM
                                                           •   Globally high-load SQL
                                                           •   Skew in instance
                                                               response times
  Inst 1             Inst 2             Inst 3       •   Runs proactively every
                                                         hour when taking AWR
       AWR 1               AWR 2             AWR 3       snapshots (default)
                    <Insert Picture Here>


Automatic Storage
Management
       ASM Fast Mirror Resync

                                       • Fraction of time to establish
1
                                         redundancy
                                       • Only changed blocks are
                                         resync‟ed
    Failure time < DISK_REPAIR_TIME
                                       • Benefits:
                                          • Fast recovery from transient
                                            failures
2
                                          • Enables pro-active
                                            maintenance



       Disk again accessible:
Only need to resync modified extents
 ASM Preferred Mirror Read
• Allow local mirror read operations
• Eliminate network latencies in extended clusters
   Site A                                                  Site B




                          Extended Cluster
        P                                                 S

    ASM_PREFERRED_READ_                      ASM_PREFERRED_READ_
    FAILURE_GROUPS=DATA                      FAILURE_GROUPS=DATA
    .SITEA                                   .SITEB



   SELECT preferred_read FROM v$asm_disk;
   SELECT * FROM v$asm_disk_iostat;
 ASM Rolling Upgrades and Patches


• Maximizes database availability in a cluster
• How it works:
  • Place cluster in „Rolling Migration‟ mode
     • Bring down ASM on a cluster node
     • Upgrade or patch software
     • Re-start ASM
  • Stop „Rolling Migration‟ mode after upgrading all nodes
     ASM Scalability and Performance
     Enhancements

• Variable size extents        •   63 disk groups
  • grows automatically with   •   10,000 ASM disks
    file size                  •   4 petabyte per ASM disk
• Benefits                     •   40 exabyte of storage
  • Increase ASM file size     •   1 million files per disk group
                               •   Maximum file size:
  • Reduce memory
                                    • External redundancy: 140 PB
    utilization in SGA
                                    • Normal redundancy: 42 PB
• 100% automatic                    • High redundancy: 15 PB
  Multiple Allocation Unit Size support

• Allocation Unit (AU) selected at disk group creation time
and may be 1,2,4,8,16,32,64 MB
• Works with variable extent sizing
• Set Oracle MAX_IO_SIZE = AU size
    • default MAX_IO_SIZE is 1MB
    • MAX_IO_SIZE determines default AU size
• Striping
    • coarse stripe size = 1 AU
    • fine stripe size = 128KB



Higher performance for large sequential I/O (DW)
Better leverage of Hardware RAID read-ahead
 SYSASM Privilege

 • SYSASM role to manage ASM instances separates duty of DBAs
   and storage administrators

SQL> CONNECT / AS SYSASM

SQL> CREATE USER ossysasmusername IDENTIFIED by passwd;

SQL> GRANT SYSASM TO ossysasmusername;

SQL> CONNECT ossysasmusername / passwd AS SYSASM;

SQL> DROP USER ossysasmusername;

 • SYSDBA will be deprecated for ASM management:
    • Oracle Database 11g Release 1 behaves as in 10g
    • In future releases SYSDBA privileges restricted in ASM
      instances
     ASMCMD Extensions

User created directories
Templates
Disk group compatibility          md_backup
Disk group name
Disk names and failure groups               md_restore



      repair                    $ asmcmd help               cp


      Repair                                             Copy files
    Bad Blocks
                                    lsdsk

                                 List ASM disks
Results Caches
 Results Caches
 Agenda
• Server Results Cache
   • SQL Query Results Cache
   • PL/SQL Function Cache
• OCI Consistent Client Cache
                         <Insert Picture Here>


SQL Query Result Cache
  Data Warehouse Workload

• Analyze data across large data sets
   • reporting
   • forecasting – trend analysis
   • data mining
• Use parallel execution for good performance
• Result
   • very IO intensive workload – direct reads from disk
   • memory is less important
      • mostly execution memory
 Data Warehouse Query Example

 select p.prod_category
 ,      sum(s.amount_sold) revenue
 from products p
 ,    sales    s
 where s.prod_id = p.prod_id
 and   s.time_id
   between to_date('01-JAN-2006','dd-MON-yyyy')‫‏‬
   and     to_date('31-DEC-2006','dd-MON-yyyy')‫‏‬
 group by rollup (p.prod_category)‫‏‬




• accesses very many rows
• returns few rows
  Data Warehouse Configuration
  Sizing
• Critical success factors
   • IO throughput
      • number of physical disks
      • number of channels to disks
   • CPU power
• Everything else follows
   • Storage capacity (500GB – 1TB common)
      - use surplus for high availability and ILM
   • Memory capacity (4GB/CPU is “standard”)
      - use surplus for... RESULT CACHE
   SQL Query Result Cache
   Benefits
• Caches results of queries, query blocks, or pl/sql function calls
• Read consistency is enforced
   • DML/DDL against dependent database objects invalidates cache
• Bind variables parameterize cached result with variable values


                                           join
                    result is
                    query 1                                       query 2 uses cached
                    cached
                   executes                                        result transparently
                                                        join
                      cached
                     Group by
                       result                     Table 4         Group by
            join

      Table 1             join                                        join

                Table 2          Table 3                    Table 5          Table 5
 SQL Query Result Cache
 Enabling
• result_cache_mode initialization parameter
   • MANUAL, use hints to populate and use
   • FORCE, queries will use cache without hint
• result_cache_max_size initialization parameter
   • default is dependent on other memory settings
     (0.25% of memory_target or 0.5% of sga_target or 1% of
     shared_pool_size)
   • 0 disables result cache
   • never >75% of shared pool (built-in restriction)
• /*+ RESULT_CACHE */ hint in queries
 SQL Query Result Cache
 Example
• Use RESULT_CACHE hint

 select /*+ RESULT_CACHE */ p.prod_category
 ,      sum(s.amount_sold) revenue
 from products p
 ,    sales    s
 where s.prod_id = p.prod_id
 and   s.time_id
   between to_date('01-JAN-2006','dd-MON-yyyy')‫‏‬
   and     to_date('31-DEC-2006','dd-MON-yyyy')‫‏‬
 group by rollup (p.prod_category)‫‏‬
   SQL Query Result Cache
   Example
 • Execution plan fragment

------------------------------------------------------------------
| Id | Operation                    | Name                       |
------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                            |
|   1 | RESULT CACHE                | fz6cm4jbpcwh48wcyk60m7qypu |
|   2 |   SORT GROUP BY ROLLUP      |                            |
|* 3 |     HASH JOIN                |                            |
|   4 |     PARTITION RANGE ITERATOR|                            |
|* 5 |       TABLE ACCESS FULL      | SALES                      |
|   6 |     VIEW                    | index$_join$_001           |
|* 7 |       HASH JOIN              |                            |
|   8 |       INDEX FAST FULL SCAN | PRODUCTS_PK                 |
|   9 |       INDEX FAST FULL SCAN | PRODUCTS_PROD_CAT_IX        |
------------------------------------------------------------------
  SQL Query Result Cache
  Opportunity
• Depends... based on
   • query repetitiveness
   • query execution times
   • DML activity (cache invalidation frequency)
• Remember data warehouse workload
   • query may run 30 minutes
   • query may return 5 rows
   • query served from result cache would take split second
 SQL Query Result Cache
 Restrictions
• Result cache is disabled for queries containing
   • temporary or dictionary tables
   • non-deterministic PL/SQL functions
   • sequence CURRVAL and NEXTVAL
   • SQL functions current_date, sysdate, sys_guid, etc.
• Result cache for distributed queries
   • set result_cache_remote_expiration > 0
      • 0 means distributed queries are not cached
      • default is 0
   • DML/DDL on remote database will not expire cached results
  SQL Query Result Cache
  Caveats

• Result cache does not automatically release memory
   • Grows until maximum size is reached
   • DBMS_RESULT_CACHE.FLUSH purges memory
• Bind variables
   • Cached result is parameterized with variable values
   • Cached results can only be found for same variable values
• Cached result will not be built if
   • Query is built on a non-current version of data (read consistency
     enforcement)
   • Current session has outstanding transaction on table(s) in query

• Flashback queries can be cached
 SQL Query Result Cache
 Internal Benchmark
• Retail customer data (~50 GB)
• Concurrent users submitting queries randomly
   • executive dashboard application with 12 heavy analytical queries
• Cache results only at in-line view level
   • 12 queries run in random, different order
   • 4 queries benefiting from the cache
   • 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%
                        <Insert Picture Here>


PL/SQL Function Cache
 PL/SQL Function Cache
 The challenge
• Calculate a complex derived metric – like the ratio of
 the highest median income grouped by state to the
 lowest median income grouped by state over the
 whole population

• We need a PL/SQL function

• Like SQL Result Cache, the data changes fairly
 slowly (say every hour) but the query is repeated fairly
 often (say every second)
 PL/SQL Function Cache
 The challenge
 function f2 return t1%rowtype

 is
   ...
 begin
   select a, m into r1.a, r1.b from ...;

   select a, m into r2.a, r2.b from ...;

   r.a := r1.a + r2.a;
   r.b := r1.b + r2.b;
   return r;
 end f2;


• ~ 2,000 milliseconds for each new call
    PL/SQL Function Cache

    function f2 return t1%rowtype
       result_cache relies_on(t1, t2)
    is
       ...
    begin
       select a, m into r1.a, r1.b from ...;

      select a, m into r2.a, r2.b from ...;

      r.a := r1.a + r2.a;
      r.b := r1.b + r2.b;
      return r;
    end f2;


•       ~ 0 milliseconds for each new call
  SQL Query Result Cache &
  PL/SQL Function Cache

• Both are cross-session and RAC interoperable
   • set result_cache_max_size > 0 on all instances, or = 0 on all
     instances
   • memory pool is instance-specific

• Both build on the same infrastructure

   • same Result_Cache_Max_Size,… initialization parameters

   • same DBMS_Result_Cache management package

   • same v$Result_Cache_* performance views
                              <Insert Picture Here>


OCI Consistent Client Cache
 Application Caching Challenge


• Applications often implement custom caching
 of data
  • significant development effort
  • synchronization may not be possible or come at performance
    cost
  • not transparent to applications
  • takes development focus out of business logic into
    infrastructure
  OCI Consistent Client Cache
  like cache fusion between server and client



                                      Consistent
               Application Server
                                       Caching



                   Database



• Caches query results on client
• Targeted at repetitive queries against read-mostly, read-
  only data
OCI Consistent Client Cache
Benefits

• Frees application developers from building a shared per-process
  result cache shared by all sessions that is consistent.
• Extends server-side query caching to client side memory
   • leverages cheaper client-side memory
   • each application has it‟s working set cached locally
• Ensures better performance by eliminating round trips to the server
• Improves server scalability by saving server resources
• Transparently maintains cache consistency with server side
  changes
• Consistency mechanism works with RAC
 OCI Consistent Client Cache
 How does it work?

• Leverages a combination of unique Oracle
  technologies:
  •   Oracle's snapshot based Read Consistency
  •   Database Change Notification technology
• OCI Layer enhanced to lookup internal cache for all queries with
  /*+ result_cache */
• Consistency maintained by sending IN-BAND notifications on
  every roundtrip to server
  •   in a relatively busy client, cache keeps sliding forward by catching up
      with the DB
  •   in a relatively idle client, cache can trail behind DB no more than
      CACHE_LAG secs
• Query results cached in OCI client memory
  •   per-process cache shared across multiple sessions/threads
   OCI Consistent Client Cache
   Enabling
• Works with all OCI-based drivers
   • Including JDBC OCI, OCCI, ODP.Net, PHP, ODBC
• Activated with server or client parameter
   • Server
      •   CLIENT_RESULT_CACHE_SIZE (default 0, cache disabled)
      •   CLIENT_RESULT_CACHE_LAG (optional, 3000ms default)
   • Client (set in sqlnet.ora)
      •   OCI_RESULT_CACHE_MAX_SIZE (optional)
      •   OCI_RESULT_CACHE_MAX_RSET_SIZE (optional)
      •   OCI_RESULT_CACHE_MAX_RSET_ROWS(optional)

• Applications explicitly tag queries with SQL hint
   • select /*+ result_cache */ id, name from products;
• Enable statement caching in Drivers/layers such as JDBC, ODP.Net
  etc or OCI Statement Caching can be used
 OCI Consistent Client Cache
 Caveats
• Some restrictions
   • views
   • VPD
   • DBlinks


• Generally, not recommended for clients with
 extraneous channels
  • e.g. IPC between clients to notify of changes, since second
    process would expect to see update
OCI Consistent Client Cache
Usage Guidelines
• Look for candidate queries in AWR
  • frequent queries in Top SQL by CPU/Elapsed time
  • identify candidate queries on read-only/read-mostly tables
  • sprinkle the /*+ result_cache */ hint on such queries
  • validate by comparing performance with/without caching
• Monitor usage
  • client_result_cache_stats$

• Control the “lag”
  • client_result_cache_lag
 Results Caches
 Summary
• SQL Query Results Cache and PL/SQL Function
 Cache
  • Utilize server side memory for caching long running repetitive
    queries and functions
• OCI Consistent Client Cache
  • Utilize cheap client memory, gravitate data towards
    application, eliminate server round trip
• Client and Server Result Caches are autonomous,
 each be be enabled/disabled independently
      <Insert Picture Here>




Q&A
New Optimizer features in 11g
Oracle Optimizer


“The Optimizer works perfectly and is a
       black box to customers”




                              NOT
                              YET
 What are the problems

• Plans change unexpectedly especially during upgrades



• Cardinality estimate is wrong so plan goes wrong



• Gathering Optimizer Statistics takes too long



• Bind peeking doesn‟t work when there is a data skew
 What are the solutions in 11g

• Plans change unexpectedly especially during upgrades
   • Guaranteed plan stability and controlled plan evolution
   • Controlled statistics publication
• Cardinality estimate is wrong so plan goes wrong
   • Collect appropriate statistics
   • Eliminate wrong cardinality estimates
• Gathering Optimizer Statistics takes too long
  • Faster statistics gathering
  • Improved statistics quality
• Bind peeking doesn‟t work when there is a data skew
   • Enhanced plan sharing with binds
                                 <Insert Picture Here>



SQL Plan Management
 Guaranteed plan stability and
   controlled plan evolution
 SQL Plan Management

Business Problem
• Unpredictable changes in execution plans can happen
   • new Statistics
   • changes in the Environment
   • software upgrades
• Today you have to „freeze‟ critical plans or statistics

Solution
• Optimizer automatically manages „execution plans‟
   • Only known and verified (accepted) plans are used
• Plan changes are automatically verified
   • Only comparable or better plans are used going forward

SQL Plan Management is controlled plan evolution
Without SQL Plan Management
• SQL statement is parsed for the first time and a plan is generated
• Does plan gives good performance? Plan is “verified by execution”

                    GB

          Parse                 Execute           Plan Acceptable
                    HJ


                          HJ


• Something changes in the environment
   • Statistics are re-gathered, DB upgrade or parameter change
• Changes result in new plan
• New plan implemented regardless of resulting performance

                   GB

         Parse                 Execute         Plan NOT Acceptable
                   NL


                         NL
    With SQL Plan Management
• Repeatable SQL populates statement log, plan history and creates a
  baseline




                             GB

                     Parse             Execute          Plan Acceptable
                             HJ



                                  HJ




     Statement log
        Plan
       history
     Plan baseline

         GB

         HJ

              HJ
    With SQL Plan Management
• Something changes in the environment
• SQL statement is parsed again and a new plan is generated
• New plan is not the same as the baseline – new plan is not executed
but marked for verification
                                 GB

                         Parse
                                 NL


                                      NL




        Statement log
              Plan
             history
   GB
         Plan baseline
   NL

        NL     GB

               HJ

                    HJ
   With SQL Plan Management
• Something changes in the environment
• SQL statement is parsed again and a new plan is generated
• New plan is not the same as the baseline – new plan is not executed
  but marked for verification
• Execute the known plan baseline “performance guaranteed by history”
                                 GB

                         Parse             Execute
                                 HJ                    Plan Acceptable

                                      HJ




        Statement log
              Plan
             history
   GB    Plan baseline
   NL
               GB
        NL
               HJ

                    HJ
          Verifying the new plan
• Non-baseline plans will not be used until verified
• DBA can verify plan at any time
     Statement log
          Plan
                                                                                          Optimizer
         history                                                                        checks if new
     Plan baseline
                                                    Invoke or schedule                 plan is as good
GB

NL         GB                               DBA         verification                     as or better
                                                                                        than old plan
     N     HJ
     L

                HJ




                               Statement log
                                   Plan
 Plans which don’t    GB          history
perform as good as    NL       Plan baselines
  the original plan        N
                                GB        GB
                           L
   stay in the plan             HJ
                                          NL
   history and are                                   Plans which perform as good as or
                                               NL
       marked                        HJ             better than original plan are added to
     unaccepted                                               the plan baseline
       SQL Plan Baseline 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
• After all SQL Plan Baselines are populated switch Optimizer_Features_Enable to 11g
     • new 11g plans will only be used after they have been verified
Possible SQL Plan Manageability Scenarios
              Database Upgrade                          New Application Deployment,
              using Tuning Pack                           no Tuning Pack required
                         Oracle Database 11g                            Production Database
                Plan History                                   Plan History

  GB                GB                             GB              GB
   HJ               HJ                             HJ              HJ

    HJ               HJ                               HJ               HJ

  No plan                                         No plan
regressions                                     regressions


                                                                              DBA




                         DBA                                  Plan History


                                                 GB               GB

                                                 HJ               HJ
     Well GB
                                                   HJ              HJ
    tuned HJ
     plan  HJ
                                               Well tuned
                                                                                      Baseline
                                                 plan
                                                                                        plans
                                                                                    staging table

  Oracle Database 10g                            Development Database 11g
                            <Insert Picture Here>



Pending Statistics
  Separating statistics
gathering and publication
 Manage New Statistics

Business Problem
• Statistics are published as soon as we complete gathering
  => Possibly unpredictable changes of execution plans
• Today you have „freeze‟ critical plans or statistics

Solution
• Gather statistics but don't publish them
    •Marked as pending
• Verify that new statistics don‟t change plans adversely
    •Either on the same or a different system
• Publish verified statistics

Controlled and DBA-verified statistics management
                              <Insert Picture Here>



Extended Optimizer
     Statistics
Eliminate wrong cardinality
        estimates
     Extended Optimizer Statistics
Business problem - Correlated Columns
• Real data often shows correlations between various attributes
    •e.g. job title influences salary, car model influences make, seasons affect the
    amount of sold goods (e.g. snow shoes in winter)
• Optimizer has to estimate the correct cardinality
    •“Does an additional filter reduce the result set or not?”

Solution
• Extended Optimizer Statistics provides a mechanism to collect statistics
  on a group of columns
• Full integration into existing statistics framework
   • Automatically maintained with column statistics
   • Instantaneous and transparent benefit for any migrated application

       Improved Cardinality leads to Improved Plans
                           <Insert Picture Here>



Optimizer Statistics
 Improved Efficiency and
        Quality
     Improved Efficiency and Quality

Business problem
• “ .. Compute statistics gives accurate results but takes too long ..”
• “ .. Sampling is fast but not always accurate ..”
• “ .. AUTO SAMPLE SIZE does not always work with data skew ..”

Solution
• New groundbreaking implementation
   • Faster than sampling
   • Accuracy comparable to compute statistics
• Used by default with AUTO_SAMPLE_SIZE value
• No need to use manual sampling anymore

                 FASTER AND BETTER
     Speed of sampling with the accuracy of compute
 Optimizer Enhancements

• Plans change unexpectedly especially during upgrades
   • Guaranteed plan stability and controlled plan evolution
   • Controlled statistics publication
• Cardinality estimate is wrong so plan goes wrong
   • Collect appropriate statistics
   • Eliminate wrong cardinality estimates
• Gathering Optimizer Statistics takes too long
  • Faster statistics gathering
  • Improved statistics quality
     Oracle TimesTen 7.0

                            • Extends Oracle Database
                                  • dynamic database caching

                            • Strong compatibility / interoperability with
                              Oracle Database
Application   Application
                                  • eases adoption
                            • First major product release post-acquisition
                                   • same product development team
                                   • cross-fertilization with Oracle
                                     development team
                            • Maintains core value of TimesTen
                              technology
                                  • instant responsiveness and high
                                    throughput
Grid and OLTP




                • Real Application Clusters
                • Automatic Storage Management
                • Performance Enhancements
                   • Results Caches
                   • Optimizer Enhancements
                • TimesTen
      <Insert Picture Here>




Q&A

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:25
posted:8/8/2011
language:English
pages:66