X

Everything you want and need to know about Oracle SPARC systems performance

In-Memory Aggregation: SPARC T7-2 Beats 4-Chip x86 E7 v2

Brian Whitney
Principal Software Engineer

Oracle's SPARC T7-2 server demonstrates better performance both in throughput and number of users compared to a four-chip x86 E7 v2 sever. The workload consists of a realistic set of business intelligence (BI) queries in a multi-user environment against a 500 million row fact table using Oracle Database 12c Enterprise Edition utilizing the In-Memory option.

  • The SPARC M7 chip delivers 2.3 times more query throughput per hour compared to an x86 E7 v2 chip.

  • The two-chip SPARC T7-2 server delivered 13% more query throughput per hour compared to a four-chip x86 E7 v2 server.

  • The two-chip SPARC T7-2 server supported over 10% more users than a four-chip x86 E7 v2 server.

  • Both the SPARC server and x86 server ran with just under 5 second average response time.

 

Performance Landscape

The results below were run as part of this benchmark. All results use 500,000,000 fact table rows and had average cpu utilization of 100%.

 
In-Memory Aggregation
500 Million Row Fact Table
System Users Queries
per Hour
Queries per Hour
per Chip
Average
Response Time
SPARC T7-2
2 x SPARC M7 (32core)
190 127,540 63,770 4.99 (sec)
x86 E7 v2
4 x E7-8895 v2 (4x 15core)
170 112,470 28,118 4.92 (sec)
 

The number of cores are listed per chip.

Configuration Summary

SPARC Configuration:

SPARC T7-2
2 x 4.13 GHz SPARC M7 processors
1 TB memory (32 x 32 GB)
Oracle Solaris 11.3
Oracle Database 12c Enterprise /Edition (12.1.0.2.0)
 

x86 Configuration:

Sun Server X4-4
4 x Intel Xeon Processor E7-8895 v2 processors
1 TB memory (64 x 16 GB)
Oracle Linux Server 6.5 (kernel 2.6.32-431.el6.x86_64)
Oracle Database 12c Enterprise /Edition (12.1.0.2.0)
 

Benchmark Description

The benchmark is designed to highlight the efficacy of the Oracle Database 12c In-Memory Aggregation facility (join and aggregation optimizations) together with the fast scan and filtering capability of Oracle's in-memory column store facility.

The benchmark runs analytic queries such as those seen in typical customer business intelligence (BI) applications. These are done in the context of a star schema database.  The key metrics are query throughput, number of users and average response times.

The implementation of the workload used to achieve the results is based on a schema consisting of 9 dimension tables together with a 500 million row fact table.

The query workload consists of randomly generated star-style queries simulating a collection of ad-hoc business intelligence users. Up to 300 concurrent users have been run, with each user running approximately 500 queries. The implementation includes a relatively small materialized view, which contains some precomputed data. The creation of the materialized view takes only a few minutes.
 

Key Points and Best Practices

The reported results were obtained by using the following settings on both systems except where otherwise noted:

  1. starting with a completely cold shared pool
  2. without making use of the result cache
  3. without using dynamic sampling or adaptive query optimization
  4. running all queries in parallel, where
    parallel_max_servers = 1600 (on the SPARC T7-2) or
    parallel_max_servers = 240 (on the Sun Server X4-4)
    each query hinted with PARALLEL(4)
    parallel_degree_policy = limited
  5. having appropriate queries rewritten to the materialized view, MV3, defined as
    SELECT
    /*+ append vector_transform */
    d1.calendar_year_name, d1.calendar_quarter_name, d2.all_products_name,
    d2.department_name, d2.category_name, d2.type_name, d3.all_customers_name,
    d3.region_name, d3.country_name, d3.state_province_name, d4.all_channels_name,
    d4.class_name, d4.channel_name, d5.all_ages_name, d5.age_name, d6.all_sizes_name,
    d6.household_size_name, d7.all_years_name, d7.years_customer_name, d8.all_incomes_name,
    d8.income_name, d9.all_status_name, d9.marital_status_name,
    SUM(f.sales) AS sales,
    SUM(f.units) AS units,
    SUM(f.measure_3) AS measure_3,
    SUM(f.measure_4) AS measure_4,
    SUM(f.measure_5) AS measure_5,
    SUM(f.measure_6) AS measure_6,
    SUM(f.measure_7) AS measure_7,
    SUM(f.measure_8) AS measure_8,
    SUM(f.measure_9) AS measure_9,
    SUM(f.measure_10) AS measure_10
    FROM time_dim d1, product_dim d2, customer_dim_500M_10 d3, channel_dim d4, age_dim d5,
    household_size_dim d6, years_customer_dim d7, income_dim d8, marital_status_dim d9,
    units_fact_500M_10 f
    WHERE d1.day_id = f.day_id AND
    d2.item_id = f.item_id AND
    d3.customer_id = f.customer_id AND
    d4.channel_id = f.channel_id AND
    d5.age_id = f.age_id AND
    d6.household_size_id = f.household_size_id AND
    d7.years_customer_id = f.years_customer_id AND
    d8.income_id = f.income_id AND
    d9.marital_status_id = f.marital_status_id
    GROUP BY d1.calendar_year_name, d1.calendar_quarter_name, d2.all_products_name,
    d2.department_name, d2.category_name, d2.type_name, d3.all_customers_name,
    d3.region_name, d3.country_name, d3.state_province_name, d4.all_channels_name,
    d4.class_name, d4.channel_name, d5.all_ages_name, d5.age_name, d6.all_sizes_name,
    d6.household_size_name, d7.all_years_name, d7.years

 

See Also

 

Disclosure Statement

Copyright 2015, Oracle and/or its affiliates.  All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. Results as of October 25, 2015.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services