Improving Performance with 12C In-Memory Option

    By: Fong Zhuang, 25769 on Jul 17, 2017

    Before Oracle introduced dual format memory architecture, it was very challenging to find the best approach to accelerate the performance of OLAP and OLTP in the same database. We witnessed two types of DBMS in the market, columnar oriented 

    and row-major oriented. Columnar oriented DBMS is treated as a proven practice for traditional Data Warehouse, however the pure
    columnar format is not suited for the real time analytics demanding on high volume transactional data.  The traditional row-major
    DBMS is suited for OLTP workloads, however to retrieve high performance analytics, it requires analytic indexes, such as Oracle
    bitmap indexes to achieve this goal. Additionally, the overhead of index maintenance is expensive and bitmap indexes are not
    recommended for high transactional tables.  The new Oracle 12c In-Memory option brings the dual format memory structure into
    one single database.  The mixed OLTAP solution combining with the rapid advancement of hardware system can bring the data
    processing to a dramatic fast speed and a much higher scalability.

    Technical Discussions and Examples 

    Dual Format Architecture Introduction

    Figure 1 In memory SGA Arch.jpg

     

    The database In-Memory option was first introduced in Oracle 12.1.0.2 patch set.  In additional to the existing SGA components
    Buffer Cache, Shared Pool, Large Pool, Redo Buffer, and other Shared Memory Components, a new option is introduced to add
    a static pool “In-Memory area” to SGA. The same data can be stored in both Buffer Cache in row format and “In Memory Area” in
    columnar format as shown in figure 1.

    Before the In-Memory option was introduced, Oracle stores data in row format which is ideal for OLTP environment.  It allows quick
    access to all columns in a record since records are kept completely row by row in disk and in buffer cache.  The row based format is
    ideal for processing DMLs (Insert, Delete, and Update) and Select statement for OLTP. 

    A column format database stores data in columns rather than as rows.  It allows faster access of a few columns of data it needs to
    answer a query rather than scanning and discarding unwanted data in rows. The query performance is increased when the larger
    data sets are selected from. 

    With Dual Format Architecture [1],

    • Data are persistently stored on disk in row format only. 
    • In case data are requested for DML, they are loaded into the traditional row store (Buffer Cache). 
    • In case data are requested for read-only operations, they are populated into the In-Memory Column Store. 
    • In case a transaction (inserts, updates, and deletes) is committed, data will appear in both the row store and the in-memory
      column store right away.  Therefore, both stores are Transactionally Consistent.  The population includes a transformation
      from row to columnar format and it consumes CPU.

    Hint:  Traditionally, Oracle relies on different kinds of indexes for different purpose of data retrieval.  Especially in Oracle data warehouse,
    bitmap indexes are commonly used for optimized data access methods such as star transformations. With data storing in In-Memory
    column store, the indexes can be eliminated. The DML can be much faster by saving the time on index maintenance.

     

    In-Memory Area Structure & Enabling In-Memory Column Store 

    Figure 2 In memory Area IMB Pool and 64KB Pool.jpg

     

     

     

     

     

     

     

     

     

     

     

     

     

    Figure 2: In-Memory Area Structure [2]

    As Figure 2 shown, the IM column store is segregated into two pools, a 1MB pool and a 64KB pool.
    IMCU (In-Memory Compression Unit) is a logical unit of storage within the In-Memory column store (IM column store).
    It is roughly equivalent to an extent within a tablespace.  SMU (Snapshot Metadata Unit) holds the metadata about IMCU.
    Each IMCU maps to a SMU. The metadata keeps the records min and max values for the 1MB pools. Oracle can read a
    small 64KB metadata chunk to decide if it should spend time decompressing and reading 1MB chunk when a predicate is
    used in a query. 

    In-Memory column store is not enabled by default.  This option is controlled by the initialization parameter inmemory_size.
    In the following example, the original SGA size was about 35GB.  In order to set up an in memory area with 30GB in size,
    enlarge SGA_TARGET and SGA_MAX_SIZE to at least 65GB.  

     

    SQL1 Alter system.jpg

     

    Config 1.png


    Restart the database to take effect.

    SQL2 show sga.jpg

     

     

    The pool of information can be selected from V$INMEMORY_AREA dynamic performance view. 

    SQL 2.png

     

    Config2.jpg

    Case Study
    Several tests on the Oracle In-Memory option were conducted in both mix-workload OLTP EBS environment, and OLAP
    OBIEE data warehouse environment in Walgreens.  Promising results were observed and confirmed by the application teams.

    Case 1             OLTP - Oracle EBS in Exadata

    Hint:  Most of ERP system such as Oracle EBS has mix-workload of OLTP and periodic analytic reporting.  Some analytic
    indexes such as bitmap indexes are not idea for high transactional tables as it could slow down the DML.  Adding segments
    into In-Memory column store could run analytic reports on high transactional tables with exceptional good performance
    .

    Case 1.1  POC Environment Introduction

    • Application: EBS with OLTP relational database
    • Database host: Oracle Exadata X-3
    • RAC (2 nodes)
    • Host memory: 256GB/node
    • CPU cores:  16/node
    • Exadata is shared by multiple DBs
    • Plan to improve with INMEMORY_SIZE 30GB

    Case 1.2  Utilization of In-Memory Advisor

    What is In-Memory Advisor?

    • Use Active Session History (ASH), Automatic Workload Repository (AWR) and optionally SQL Tuning Sets (STS)
    • Determine which tables, partitions and sub-partitions to place In Memory.
    • Licensed as part of the Database Tuning pack.
    • Minimum DB version to install is 11.2.0.3. 
    • Implemented recommendations in Database 12.1.0.2.

    Where to get it and how to install it?

    • Check MOS note: 1965343.1 for the most recent package.  This package is updated very often. 
    • Download imadvisor.zip from Oracle, copy to DB server and then unzip it
    • Install in SQLPLUS with sysdba privilege
    • SQL> @instimadv.sql
    • Run the IM advisor recommendation script with appropriate period
    • SQL> @imadvisor_recommendations.sql
    • Review two outputs generated from the script

    Review the output     

    Image 3.jpg

     

     

     

     

     

    Observation:

    • From the Workload Database Usage report, it shows 67% of DB time was doing analytics processing during 960463 seconds of DB processing time.
    • This value is pretty high comparing to most of the OLTP systems.


    Image 4 In Memory Size.jpg

     

    Observation:

    • With 20GB In-Memory Size, which is 40% of total SGA, the Estimated Analytics Processing Time Reduction is
      estimated to be 51310 seconds (or 855 minutes, or 14.25 hours), the Estimated Analytics Processing Performance
      Improvement Factor is estimated to be 1.1X 
    • With 535.2GB In-Memory Size, which is 1070% of total SGA, the Estimated Analytics Processing Time Reduction is
      estimated to be 284826 seconds (or 4747 minutes, or 79 hours), the Estimated Analytics Processing Performance
      Improvement Factor is estimated to be 1.8X 
    • The current EBS system does not have 535.2GB of memory available, but at least with 20GB of In-Memory, we could
      potentially cut 14 hours on analytical SQLs.  

    Other Observations from the rest of the report:

    • With 20GB of In-Memory size, 10 top SQL statements with Analytics Processing Benefits are displayed.  All of them
      have 10X of performance improvement. 
    • Top 10 objects recommended place into IMCU.
    • Those 10 objects only take 12.4MB of total In-Memory space, so the Benefit/Cost ratio is very high.
    • All 503 SQLs potentially to benefit from IMCU.  The improvement factor varies from 1.0X to 10.0X.
    • 175 objects potentially to benefit from IMCU. The Benefit/Cost ratio varies from 329992:1 to 618:1.

     

    Case 1.3        Target the bottleneck For Wag Sales Tax Outbound Job

    The application team expected to use the 30GB memory to improve the performance of three long running jobs only.
    In the following example, one job was taking 14 hours & 39 min each time.

    Image 5 Wag Sales Tax Outbound Interface.jpg

     

    The IM advisor is executed again with the analysis period specified during the period when that particular job was running. 
    Part of the In-Memory html report is shown below:

    Image 6 Work Database Usage with Specific Period.jpg

     

     

    Image 7 Sample SQL statement.jpg

     

    Observation:

    • Only One SQL would benefit from IM Column Store.
    • Only 850 sec (14.2 min) out of 51,520 sec (14.3 hours) of Total Analytics Processing Time would get benefit.
    • The estimated time reduction was not impressive either, only 7 seconds from 850 seconds of analytics processing time.

    This estimation seems not impressively.

     

    Case 1.4  Populating Tables with the Modified Scripts

    Table Population Samples on Engineered Systems:

    SQL4 alter table.jpg

    Use the following SQLs to monitor the status of population:

    SQL5 Monitor.jpg

    Config3.jpg

     

    Case 1.5 New Result with Tables In Memory

    Image 8 New result with Tables in Memory.jpg

     

    Observations:

    • The Wag Sales Tax Outbound Interface job completed in 1 hour and 9 minutes after adding the tables to In-Memory.
    • Compare to 14 hours and 39 minutes of the previous timing, 12X performance improvement.
    • The real performance is much better than the estimation from In Memory Advisor report.
    • The SQL (ID 1223h53fh9ht9) which was identified in the In Memory Advisor report was NOT the only one benefit from In-Memory.
    • Several SQLs in custom written package XXGL_SALES_TAX_OUTBOUND_PKG got the benefits from the In-Memory option, although those SQLs were not displayed in the In-Memory Advisor estimation report.
    • The IM Advisor successfully identified optimal tables to add into In-Memory column store.

    Case 1.6  Identify the key time saving SQL from AWR Report

    Image 9 AWR report with SQL ordered by Elapsed Time.jpg

     

    Observations:

    • Two top SQLs with many executions were identified in the AWR report before tables were added to the In-Memory
      column store. The SQLs are highlighted in the above chart.
    • SQL (ID 1223h53fh9ht9) which was displayed in IM Advisor report only saved a total of 12.6 minutes in the whole
      Tax Out-Bound Job. The key time saving was from the SQL (ID d5v6120ykm39r) which was not displayed in the IM
      Advisor report.
    • It’s very interesting that both top SQLs cannot be found in the regular AWR reports after they are added into In-Memory.
      Both SQLs were executed either from the EBS Sales Tax Outbound job, or from running them many times manually,
      but they are not treated as top SQLs in AWR anymore. 
    • The best way to catch those SQLs is to enable the SQL monitoring or to set up SQL Tuning Set. Both methods are
      accessible in OEM.

    1.7 Top SQL Execution Plan Comparison from SQL Monitor Output 

    Image 10.1 Top SQL Execution Plan Comparison with IMCU.jpg

     

    Image 10.2 Top SQL Execution Plan Comparison without IMCU.jpg

     

    Observations:

    • The top graph is the SQL Monitor report with table In-Memory.
    • The bottom graph is the report with table NOT In-Memory hinted /* NO_INMEMORY */
    • The top one only took 10 seconds but the bottom one took over 2 minutes.
    • In-Memory Vector Processing takes place.
    • The Bloom Filter works extremely well with In-Memory Vector Processing.
    • The In-Memory processing speed is faster than the storage scan in Exadata Flash Cache.

    Case 1.8 Top SQLs performance evaluation from Wag Sales Tax Outbound Job

    SQL_ID

    SQL_Text

    Executions

    Avg Elapsed Time per Exec (s)

    Avg Elapsed Time per Exec w tab in IM

    Time Reduction/Exec(s)

    Performance Improvement Factor(Old Timing/New Timing)

    1223h53fh9ht9

    SELECT XGL.LOCATION_NUMBER STO...

    27,072

    0.04

    0.012

    0.028

    3.33X

    d5v6120ykm39r

    SELECT DISTINCT FFV.FLEX_VALUE FROM APPS.FND_FLEX_VALUE_CHILDREN_V FFV

    538

    94.02

    2.16

    91.86

    43.52X

     

    Case 1.9 All the Test Results From EBS Three Long Running Jobs

    EBS Job Name

    Disk Size

    In-Memory Size

    Processing Time w/o In-Memory

    Processing Time with In-Memory

    Time Reduced

    Performance Improvement Factor (Old Timing / New Timing)

    Outbound Interface Program

    11GB

    7GB

    9 hours

    5 hours

    4 hours

    1.8 X

    Warehouse Billing Journal Inbound Interface

    89GB

    15GB

    1 hour and 20 minutes

    1 hour 28 minutes

    N/A

    N/A

    Sales Tax Outbound Interface

    8GB

    1.8GB

    14 hours & 39 minutes

    1 hour 10 minutes

    13 hours

    12 X

     

    Case 1.10 Case 1 Summary

    • Target the performance bottleneck when you do not have lots of memory to create the In-Memory Area.
    • Choose the objects with high performance improvement factor.
    • In-Memory Advisor is efficient in identifying the best segments.
    • The real performance could be better than the In-Memory Advisor estimation.
    • No performance change on objects with high compression ratio.
    • Potential to drop indexes for saving index maintenance window.

    Expect extra RAM for extended performance improvement.

     

    Case 2 OLAP - Oracle OBI Data Warehouse in ODA

    Case 2.1 POC Environment Introduction

    • Application OBIEE with database in star schema
    • Database host: Oracle Database Appliance (ODA X3-2)
    • RAC (2 nodes)
    • CPU cores: 16/node
    • Host memory: 256GB/node
    • ODA is shared by multiple DBs
    • Plan to improve with INMEMORY_SIZE 50GB

     

    Case 2.2  Test Results in OLAP Oracle OBI Data Warehouse in ODA:

    Test Description 

    Disk Size

    In-Memory Size

    Tables NOT In Memory (HH:MM:SS.00)

    Tables In Memory (HH:MM:SS.00)

    Performance Improvement Factor (Old Timing / New Timing)

    Count Fact Table W_GL_BALANCE_F

    58GB

    16GB

    00:01:23.42

    00:00:00.61

    137 X

    Report Accesses Fact Table W_GL_BALANCE_F

    58.6GB

    16GB

    00:02:48.88

    00:00:09.12

    18.5 X

    Count Fact Table W_GL_OTHER_F

    128GB

    17GB

    00:04:42.39

    00:00:32.55

    8.7 X

    Report Accesses Fact Table W_GL_OTHER_F

    128.6GB

    17GB

    00:01:33.04

    00:01:29.80

    No Significant Change



    Case 2.3  Case 2 Summary

    • Tremendous performance improvement on counting records in tables.  Check results from counting Fact table W_GL_BALANCE_F & W_GL_OTHER_F.
    • Tremendous performance improvement on data aggregation, and the report access fact table W_GL_BALANCE_F was an example of that.
    • The report access Fact table W_GL_OTHER_F got no performance improvement because the compression ratio on W_GL_OTHER_F was too high.  Higher compression ratio on tables, degraded performance you would see.  Additionally, this is a report with many complicated joins, the more complicated joins you have, the degraded performance you could encounter.

     

    Case 3 OLAP - Oracle OBI Data Warehouse in Exadata

    Case 3.1 POC Environment Introduction

    • Application OBIEE with database in star schema
    • Database host: Exadata
    • RAC (2 nodes)
    • CPU cores:  16/node
    • Host memory: 256GB/node
    • Exadata is shared by multiple DBs
    • Planed INMEMORY_SIZE 20GB

     

    Case 3.2  Test Results in OLAP Oracle OBI Data Warehouse in Exadata:

    Test Description 

    Disk Size

    In-Memory Size

    Tables NOT In Memory

    (Fist Run)

    Tables In Memory (HH:MM:SS.00)

    Performance Improvement Factor (Old Timing / New Timing)

    Count Fact Table W_GL_BALANCE_F

    50.62GB

    13.65GB

    00:00:4.57

    00:00:01.08

    4.23 X

    Report Accesses Fact Table W_GL_BALANCE_F

    51.2GB

    14GB

    00:00:22.47

    00:00:07.34

    3.06 X

    Count Fact Table W_GL_OTHER_F

    128GB

    13.56GB

    00:00:04.22

    00:00:01.03

    4.09 X

    Report Accesses Fact Table W_GL_OTHER_F

    128.6GB

    13.60GB

    00:00:05.19

    00:00:6.80

    No Significant Change


    Case 3.3  Case 3 Summary

    • Tested same Walgreens customized SQLs which have been tested in the ODA environments. 
    • Three SQLs had about 3X to 4X of performance improvement.
    • The last report had no performance improvement with the same reason in ODA environment.
    • Exadata provides large Flash Cache and Smart Scan, so the performance enhancement room is relatively small comparing to ODA X3-2 with regular hard disk only.

    Advantages

    • In-Memory column store is a good option to improve performance on analytic reporting against OLTP mixed work-load databases, and OLAP.
    • Potential to get great performance improvement with a little price to pay.
    • In-Memory Advisor is a proven tool to efficiently analyze the workload for making suggestions on how to size In-Memory area, and to identify which segments would provide the best benefit to the system

    Limitations

    • Extra requirement on RAM and the In-Memory population is a CPU bound operation.
    • Accuracy of In-Memory Advisor
    • Possible performance degradation during the In-Memory population period when the database is restarted
    • Lack of In-Memory area space warning tool
    • Lack of In-Memory objects performance evaluation tool

    Future Performance Evaluation Plan

    The most current release 12c Release 2 provides many enhancements on the In-Memory Column Store.  The In-Memory FastStart can relieve the CPU overhead of population at the cost of additional disk space.  Additionally, there is an option of implementing In-Memory option in Active Data Guard only.  Expect the In-Memory Option to bring greater performance improvement down the road.

    Acknowledement

    Thanks to my colleague Karen Moe for suggesting me to submit my presentation to IOUG.

    Thanks to my colleague Raghav Kalakota for cloning the OBIEE environment for POC. 

    Thanks to Lead Architect Sergiy Smyrnov for reviewing my presentation draft and providing the valuable feedback to me. 

    Thanks to Manager Glenn Campbell, Director Jim Hope, and the leadership team for the continuous support.

    Thanks to EBS application team members Deba Mishra and Ankur Saxena for supporting the whole testing process and providing the detail results to me.

    Thanks to the Corporate System leadership team for the support of testing the Oracle new features.

    References

    [1] Oracle White Paper:  ORACLE DATABASE 12 C IN-MEMORY OPTION - The Top Tier of a Multi-tiered Database Architecture

    [2] Oracle blogs. https://blogs.oracle.com/In-Memory/entry/what_is_an_in_memory

    [3] Oracle White Paper: When to Use Oracle Database In-Memory

    [4] Oracle white paper: Oracle Database In-Memory Advisor 

    [5] Oracle white paper: Oracle Database In-Memory

    [6] IOUG presentation by Kai Yu: Optimize OLAP & Business Analytics Performance with Oracle 12c In-Memory Database Option

    Released: July 17, 2017, 2:06 pm | Updated: July 31, 2017, 10:33 am
    Keywords: Feature | SELECT Journal


    Independent Oracle Users Group
    330 N. Wabash Ave., Suite 2000, Chicago, IL 60611
    phone: 312-245-1579 | email: ioug@ioug.org

    Copyright © 1993-2017 by the Independent Oracle Users Group
    Terms of Use | Privacy Policy