real-world performance training · bitmap indexes and the star transformation full scans and...

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

Real-World Performance TrainingStar Query Execution

Real-World Performance Team


Dimensional Queries

The Dimensional Model and Star Queries

Star Query Execution

Star Query Prescription

Edge Conditions and Extreme Performance

1

2

3

4


Get the rows we want, efficiently Discard the rows we don’t want, quickly

Star Query MechanicsWe either …


B*Tree Indexes with Nested Loop Joins Full Scans with Intelligent Filtering

Bitmap Indexes and the Star Transformation Full Scans and In-Memory Aggregation

Oracle’s Four Methods to Tackle the Star Query

In-Memory Aggregation

Exadata

Database In-Memory

Bloom Filters

Database In-Memory

Star Transformation


Performance Acceleration

Performance Foundation

Convergence of Techniques and Technology

Real World Performance

Partitioning

Statistics

Optimizer Environment

Application Algorithms

Data Types

Constraints

Schema Design

Exadata

Database In-Memory

Bloom Filters

Parallel Execution

Clustering


Star Transformation

Compression


Star Query Race Demo


• Star Schema Benchmark Data Model

• Four racers, B*Tree Indexes, Bitmap Indexes, Exadata, and In-Memory

• Same queries executed for each racer in order of increasing complexity

• Each query is different

Single User DemoRace Demo


Single User Race Demo

Bitmap Indexes with Star Transformation

Exadata with Smart Scan

Oracle 12.1.0.2 Database In-Memory

Queries running in serial

B*Tree Indexes with Nested Loop Joins

Serial Query

Quick-Config



1,000 to 100,000 rows extracted from Fact table

100,000+ rows extracted from Fact table

1-10 rows extracted from Fact table

10 to 1,000 rows extracted from Fact table

Serial Query (DoP=1)



Bitmap Indexes wins at low cardinality B*Tree Indexes

Bitmap Indexes

Exadata

In-Memory





Scan methods pull ahead as queries retrieve more rows from

Fact table

B*Tree Indexes

Bitmap Indexes

Exadata

In-Memory




As queries become more complex, index access methods run longer

Scan methods exhibit consistent elapsed times as number of Fact table rows

extracted increases

B*Tree Indexes

Bitmap Indexes

Exadata

In-Memory



In serial, Exadata can take advantage of more totalCPUs than In-Memory.

NOTE: extra CPUs are on the storage cells

Serial Query (Dop=1)

B*Tree Indexes

Bitmap Indexes

Exadata

In-Memory

Exadata wins






Queries running with DoP=4


Parallel Query

Quick-Config


Single User Race DemoParallel Query (DoP=4)

B*Tree Indexes

Bitmap Indexes

Exadata

In-Memory

Bitmap indexes still wins at low cardinality, but the

gap has narrowed


Single User Race DemoParallel Query (DoP=4)

As before, as queries become more complex, index access methods

run longer

Scan methods exhibit consistent elapsed times as number of Fact table

rows extracted increases

B*Tree Indexes

Bitmap Indexes

Exadata

In-Memory



The gap between Exadata and In-Memory is narrow – they are both technologies that solve the same

problem (in a different way)

Parallel Query

In-Memory wins

In parallel, scans & evaluation more efficient on fewer CPU cores on a

per-process basis

B*Tree Indexes

Bitmap Indexes

Exadata

In-Memory


• Index access methods efficient at low cardinality

• Index methods fall behind as queries retrieve more data from Fact table

• Scan methods demonstrate consistent response times

• In single-user testing, the gap between Exadata and In-Memory is narrow

Single User Race DemoLessons Learned


B*Tree Indexes with Nested Loop Joins Full Scans with Intelligent Filtering

Bitmap Indexes and the Star Transformation Full Scans and In-Memory Aggregation

Star Query Execution Plans


Exadata

Database In-Memory

Bloom Filters

Database In-Memory

Star Transformation


Are we extracting a few rows from the Fact table after filtering?

Are we extracting many rows from the Fact table after filtering?

Questions to Ask


• Access the fact table once

• Find the rows you’re interested in efficiently

Goal – Few Rows


B*Tree Indexes

• Many data warehouses are designed like traditional OLTP environments

• B*Tree Indexes on fact table

• Nested Loops joins

B*Tree Indexes and Nested Loop JoinsFew Rows


date_dim

part

1. Build Filters

Execution Method for Nested Loops with B*Tree Indexes

Operation Object Name Predicate information

SELECT STATEMENT

SORT GROUP BY

NESTED LOOPS

NESTED LOOPS

NESTED LOOPS

NESTED LOOPS

TABLE ACCCESS BY LOCAL INDEX ROWID PART P_CONTAINER = 'JUMBO PACK'

INDEX RANGE_SCAN PART_CONTAINER_N

PARTITION RANGE ALL

TABLE ACCESS BY LOCAL INDEX ROWID LINEORDER

INDEX RANGE_SCAN LO_PART_N LO_PARTKEY=P_PARTKEY

TABLE ACCESS BY INDEX ROWID DATE_DIM D_YEAR IN ( 1993, 1994, 1995 )

INDEX UNIQUE SCAN DATE_DIM_PK LO_ORDERDATE = D_DATEKEY

TABLE ACCESS BY INDEX ROWID SUPPLIER

INDEX UNIQUE SCAN SUPPLIER_PK LO_SUPPKEY = S_SUPPKEY

SELECT d_sellingseason, p_category, s_region,

sum(lo_extendedprice)

FROM lineorder

JOIN customer ON lo_custkey = c_custkey

JOIN date_dim ON lo_orderdate = d_datekey

JOIN part ON lo_partkey = p_partkey

JOIN supplier ON lo_suppkey = s_suppkey

WHERE d_year IN (1993, 1994, 1995)

AND p_container in ('JUMBO PACK')

GROUP BY d_sellingseason, p_category, s_region

ORDER BY d_sellingseason, p_category, s_region

part customer

supplier

lineorder



SELECT STATEMENT

SORT GROUP BY

NESTED LOOPS

NESTED LOOPS

NESTED LOOPS

NESTED LOOPS



PARTITION RANGE ALL LO_ORDERDATE = D_DATEKEY


INDEX RANGE_SCAN LO_PART_N





date_dim

part

2. Extract Rows from the Fact table




FROM lineorder





WHERE d_year IN (1993, 1994, 1995)




customer

supplier

lineorderlineorder



SELECT STATEMENT

SORT GROUP BY

NESTED LOOPS

NESTED LOOPS

NESTED LOOPS

NESTED LOOPS



PARTITION RANGE ALL







suppliersupplierdate_dim

part

3. Join to Dimensions to Project Additional Columns




FROM lineorder





WHERE d_year IN (1993, 1994, 1995)




customer

date_dim

lineorder

customer



SELECT STATEMENT

SORT GROUP BY

NESTED LOOPS

NESTED LOOPS

NESTED LOOPS

NESTED LOOPS



PARTITION RANGE ALL







date_dim

part

4. Aggregate/Sort Row and Return Results




FROM lineorder





WHERE d_year IN (1993, 1994, 1995)




customer

supplier

lineorder


Things to Think About

Nested Loops with B*Tree Indexes

Ran 3.4 hours

Most of the time was in accessing fact

table rows3.4 hours = not good


• Queries extract a small number of rows from Fact table

• Indexes small enough to fit in buffer cache

• High concurrency

• Optimizer costs the transformation

Bitmap Indexes and Star TransformationFew Rows

Bitmap Indexes


Star TransformationFew Rows



FROM lineorder





WHERE d_year IN (1993, 1994, 1995)




SELECT lo_orderdate, lo_partkey, lo_suppkey,

lo_extendedprice

FROM lineorder

WHERE lo_orderdate IN

(SELECT d_datekey

FROM date_dim

WHERE d_year IN ( 1993,1994,1995 ))

AND lo_partkey IN

(SELECT p_partkey

FROM part

WHERE p_container IN ('JUMBO PACK' ))


date_dim

part

Execution Method for Star Transformation1. Build Filters


SELECT STATEMENT

TEMP TABLE TRANSFORMATION

LOAD AS SELECT SYS_TEMP_0FD9FCA09_7D1FC714

TABLE ACCESS FULL DATE_DIM D_YEAR IN ( 1993, 1994, 1995 )

LOAD AS SELECT SYS_TEMP_0FD9FCA0A_7D1FC714

TABLE ACCESS FULL PART P_CONTAINER = 'JUMBO PACK'

SORT GROUP BY

HASH JOIN LO_PARTKEY = P_PARTKEY

TABLE ACCESS FULL SYS_TEMP_0FD9FCA0A_7D1FC714

HASH JOIN LO_ORDERDATE = D_DATEKEY

TABLE ACCESS FULL SYS_TEMP_0FD9FCA09_7D1FC714

HASH JOIN LO_SUPPKEY = S_SUPPKEY

TABLE ACCESS FULL SUPPLIER

VIEW VW_ST_F981A0CC

NESTED LOOPS

PARTITION RANGE SUBQUERY

BITMAP CONVERSION TO ROWIDS

BITMAP AND

BITMAP MERGE

BITMAP KEY ITERATION

BUFFER SORT


BITMAP INDEX RANGE SCAN LO_DATE_B LO_ORDERDATE = D_DATEKEY

BITMAP MERGE


BUFFER SORT


BITMAP INDEX RANGE SCAN LO_PART_B LO_PARTKEY = P_PARTKEY

TABLE ACCESS BY USER ROWID LINEORDER



FROM lineorder





WHERE d_year IN (1993, 1994, 1995)




part customer

date_dim supplier

lineorder



SELECT STATEMENT






SORT GROUP BY







VIEW VW_ST_F981A0CC

NESTED LOOPS



BITMAP AND

BITMAP MERGE


BUFFER SORT



BITMAP MERGE


BUFFER SORT




date_dim

part

Execution Method for Star Transformation2. Extract Rows from the Fact table



FROM lineorder





WHERE d_year IN (1993, 1994, 1995)




customer

supplier

lineorder

1993

1994

1995



part

Execution Method for Star Transformation3. Join Back to Dimensions to Project Additional Columns



FROM lineorder





WHERE d_year IN (1993, 1994, 1995)




part customer

date_dim

lineorder

customer


SELECT STATEMENT






SORT GROUP BY







VIEW VW_ST_F981A0CC

NESTED LOOPS



BITMAP AND

BITMAP MERGE


BUFFER SORT



BITMAP MERGE


BUFFER SORT





date_dim

part

Execution Method for Star Transformation4. Aggregate/Sort Rows and Return Results



FROM lineorder





WHERE d_year IN (1993, 1994, 1995)




customer

supplier

lineorder


SELECT STATEMENT






SORT GROUP BY







VIEW VW_ST_F981A0CC

NESTED LOOPS



BITMAP AND

BITMAP MERGE


BUFFER SORT



BITMAP MERGE


BUFFER SORT






SELECT STATEMENT






SORT GROUP BY







VIEW VW_ST_F981A0CC

NESTED LOOPS



BITMAP AND

BITMAP MERGE


BUFFER SORT



BITMAP MERGE


BUFFER SORT




Star TransformationThings to Think About

• Assume it takes 5ms to do a random IO

• If we need 5 rows from the fact table and they’re not in the buffer cache, how long would it take to extract the rows we want?

• What if we need to extract 1,000,000 rows?



Star Transformation with Bitmap Indexes

High-cardinality query ran in 3.9

minutes

Most of the time was with random I/O

accessing fact table rows

The number of rows required from fact

table after filtering is important with index

access methods


When Bitmap Indexes are Effective

Star Transformation with Bitmap Indexes

Combined filtering across all dimension

tables filters most fact table rows


• Access the fact table once

• Eliminate all the rows you’re not interested in as early as possible

• In other words, maximize row rejection

Goal – Many Rows


• Queries extract many rows from Fact table

• Fast table scans

• Intelligent filtering

• Parallel query

Exadata or Oracle Database In-Memory

Table Scans with Intelligent FilteringMany Rows


date_dim

part

1. Build Filters

Execution Method for Intelligent Full Scans


SELECT STATEMENT

SORT GROUP BY


TABLE ACCESS STORAGE FULL SUPPLIER

HASH JOIN

JOIN FILTER CREATE :BF0001

PART JOIN FILTER CREATE :BF0000 LO_ORDERDATE = D_DATEKEY

TABLE ACCESS STORAGE FULL DATE_DIM D_YEAR IN ( 1993, 1994, 1995 )

HASH JOIN

JOIN FILTER CREATE :BF0002 LO_PARTKEY = P_PARTKEY

TABLE ACCESS STORAGE FULL PART P_CONTAINER = 'JUMBO PACK'

JOIN FILTER USE :BF0001


PARTITION RANGE JOIN-FILTER

TABLE ACCESS STORAGE FULL LINEORDER :BF0000



FROM lineorder





WHERE d_year IN (1993, 1994, 1995)




part customer

date_dim supplier

lineorder



SELECT STATEMENT

SORT GROUP BY



HASH JOIN




HASH JOIN







date_dim

part

2. Extract Rows from the Fact table




FROM lineorder





WHERE d_year IN (1993, 1994, 1995)




customer

supplier

lineorder

1993

1994

1995



SELECT STATEMENT

SORT GROUP BY



HASH JOIN




HASH JOIN







date_dim

part

3. Join to Dimensions to Project




FROM lineorder





WHERE d_year IN (1993, 1994, 1995)




customer

supplier

lineorder

supplier

part

date_dim

customer



SELECT STATEMENT

SORT GROUP BY



HASH JOIN




HASH JOIN







date_dim

part

4. Aggregate/Sort Rows and Return Results




FROM lineorder





WHERE d_year IN (1993, 1994, 1995)




customer

supplier

lineorder



SELECT STATEMENT

SORT GROUP BY



HASH JOIN




HASH JOIN








Table Scans with Intelligent Filtering• The time to extract data from the fact

table is the same regardless of level of filtering – we’re doing full scans

• Execution plans very similar on Exadata and DBIM; TABLE ACCESS STORAGE FULL changes to TABLE ACCESS INMEMORY FULL

• On Exadata, filtering is offloaded to storage. With DBIM, filters are applied against the Column Store

• Exploiting parallel query often beneficial



Table Scans with Intelligent Filtering

Query ran in 5 seconds

Same high-cardinality query ran much faster with scans and intelligent filtering on Exadata

compared to index access methods

A Database In-Memory result would be similar

What if we could improve the

aggregation costs?


• Scan data & evaluate predicates fast –think Exadata and Database In-Memory• Hundreds of GB/second

• Millions->Billions of Rows/second

• Swap join optimization and right-deep trees

• Use Bloom Filters to evaluate (filter) efficiently

• Pipelined hash joins

• Parallel query

Exadata or Oracle Database In-Memory

Table Scans with Intelligent FilteringThings We Do for Performance


Scans and Access

Bloom Filters pushed down to storage

Table Scans with Intelligent Filtering

Hardware: CPUs, disks, flash, InfiniBand

Software: Smart Scan, HCC, Storage Indexes

In-Memory columnar layout

SIMD vector processing

Filtering & Evaluation Bloom Filters pushed down to column store

How We Do It

Exadata Database In Memory


Scan and Evaluation Techniques: Intelligent Filtering

Bloom Filters



• Efficient way to filter data

• Bloom Filters created from dimension tables and applied to fact table during scan

• Utilizes swap join optimization and yields right-deep plans

• Filtered data is pipelined to hash joins

Bloom Filters


Bloom Filter

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0


Build

Bloom Filter

0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0

10

This example uses 3 hash functions


Build

Bloom Filter

0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 1 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0

10 20


Build

Bloom Filter

0 0 0 0 0 1 0 1 0 0 1 0 0 1 0 1 0 0 1 0 1 0 0 0 0 1 0 0 0 0 0 0

10 20 30


Build

Bloom Filter

0 0 0 0 0 1 0 1 0 0 1 0 1 1 0 1 0 0 1 0 1 0 0 0 0 1 0 0 0 0 0 0

10 20 30 40


Bloom Filter passed Down

Bloom Filter

0 0 0 0 0 1 0 1 0 0 1 0 1 1 0 1 0 0 1 0 1 0 0 0 0 1 0 0 0 0 0 0


Test

Bloom Filter

0 0 0 0 0 1 0 1 0 0 1 0 1 1 0 1 0 0 1 0 1 0 0 0 0 1 0 0 0 0 0 0

10 Probable Match

10 20 30 40


Test

Bloom Filter

0 0 0 0 0 1 0 1 0 0 1 0 1 1 0 1 0 0 1 0 1 0 0 0 0 1 0 0 0 0 0 0

30 Probable Match

10 20 30 40


Test

Bloom Filter

0 0 0 0 0 1 0 1 0 0 1 0 1 1 0 1 0 0 1 0 1 0 0 0 0 1 0 0 0 0 0 0

60Definite No Match

10 20 30 40


Test

Bloom Filter

0 0 0 0 0 1 0 1 0 0 1 0 1 1 0 1 0 0 1 0 1 0 0 0 0 1 0 0 0 0 0 0

70Probable Match

In this case, the match is in fact a false positive

10 20 30 40


Identifying in Plans

Bloom Filter

Bloom Filter create

Bloom Filter use


Swap Join Input Optimization



FROM lineorder





WHERE d_year IN (1993, 1994, 1995)




SELECT lo_orderdate, lo_partkey, lo_suppkey,

lo_extendedprice

FROM lineorder

WHERE lo_orderdate IN

(SELECT d_datekey

FROM date_dim

WHERE d_year IN ( 1993,1994,1995 ))

AND lo_partkey IN

(SELECT p_partkey

FROM part

WHERE p_container IN ('JUMBO PACK' ))


Swap Join Input OptimizationLeft deep tree

PART

HASH JOIN

LINEORDER

HASH JOIN

DATEDIM

HASH JOIN

SUPPLIER

1 2

3

4

1

2

3


Swap Join Input OptimizationBecomes a right deep tree

PART

HASH JOIN

LINEORDER

HASH JOIN

DATEDIM

HASH JOIN

SUPPLIER

2

3

1

2

3

4

Why is this significant?

1


Right Deep Plan

Visit dimension tables and

create Bloom Filters

Scan fact table and apply filters

Pipeline rows from fact table through hash

joins


Scan and Evaluation Techniques: Intelligent Filtering

Bloom Filters



• Queries extract many rows from fact table

• Database size large

• Aggregation on low cardinality dimensions

• Optimizer costs the transformation

Vector TransformationIn-Memory Aggregation


Database In-Memory


• Goal is to do extra work up-front while processing dimension tables to save time downstream

• Scans and filtering takes place in the DBIM column store

• Aggregation is performed as part of the fact table access

• Build a cube as we scan the fact table to avoid potentially costly aggregation

In-Memory AggregationWhy Do It?


Database In-Memory


In-Memory AggregationVector Transformation



FROM lineorder





WHERE d_year IN (1993, 1994, 1995)




SELECT key_vector(lo_orderdate), key_vector(lo_partkey), key_vector(lo_suppkey),


FROM lineorder

WHERE key_vector(lo_orderdate) IN

(SELECT key_vector(d_datekey)

FROM date_dim

WHERE d_year IN ( 1993,1994,1995 ))

AND key_vector(lo_partkey) IN

(SELECT key_vector(p_partkey)

FROM part

WHERE p_container IN ( 'JUMBO PACK' ))

GROUP BY key_vector(lo_orderdate), key_vector(lo_partkey), key_vector(lo_suppkeu)


date_dim

part

Execution Method for In-Memory Aggregation1. Build Filters


SELECT STATEMENT


LOAD AS SELECT SYS_TEMP_0FD9FC928_7D1FC714

VECTOR GROUP BY

KEY VECTOR CREATE BUFFERED :KV0000

TABLE ACCESS INMEMORY FULL DATE_DIM D_YEAR IN ( 1993, 1994, 1995 )


VECTOR GROUP BY


TABLE ACCESS INMEMORY FULL PART P_CONTAINER = 'JUMBO PACK'

LOAD AS SELECT SYS_TEMP_0FD9FC92A_7D1FC714

VECTOR GROUP BY


TABLE ACCESS INMEMORY FULL SUPPLIER

SORT GROUP BY

HASH JOIN

TABLE ACCESS FULL SYS_TEMP_0FD9FC929_7D1FC714 LO_PARTKEY = P_PARTKEY

HASH JOIN

TABLE ACCESS FULL SYS_TEMP_0FD9FC92A_7D1FC714 LO_SUPPKEY = S_SUPPKEY

HASH JOIN

TABLE ACCESS FULL SYS_TEMP_0FD9FC928_7D1FC714 LO_ORDERDATE = D_DATEKEY

VIEW VW_VT_3DF18547

VECTOR GROUP BY

HASH GROUP BY

KEY VECTOR USE :KV0002 LO_SUPPKEY = S_SUPPKEY

KEY VECTOR USE :KV0000 LO_ORDERDATE = D_DATEKEY

KEY VECTOR USE :KV0001 LO_PARTKEY = P_PARTKEY


TABLE ACCESS INMEMORY FULL LINEORDER



FROM lineorder





WHERE d_year IN (1993, 1994, 1995)




part customer

date_dim supplier

lineorder

supplier



SELECT STATEMENT



VECTOR GROUP BY




VECTOR GROUP BY




VECTOR GROUP BY



SORT GROUP BY

HASH JOIN


HASH JOIN


HASH JOIN


VIEW VW_VT_3DF18547

VECTOR GROUP BY

HASH GROUP BY






date_dim

part

Execution Method for In-Memory Aggregation2. Extract Rows from Fact Table and Aggregate



FROM lineorder





WHERE d_year IN (1993, 1994, 1995)




customer

supplier

lineorder

1993

1994

1995



SELECT STATEMENT



VECTOR GROUP BY




VECTOR GROUP BY




VECTOR GROUP BY



SORT GROUP BY

HASH JOIN


HASH JOIN


HASH JOIN


VIEW VW_VT_3DF18547

VECTOR GROUP BY

HASH GROUP BY







part

Execution Method for In-Memory Aggregation3. Join Back to Temp Tables to Project Additional Columns



FROM lineorder





WHERE d_year IN (1993, 1994, 1995)




part customer

date_dim

lineorder



SELECT STATEMENT



VECTOR GROUP BY




VECTOR GROUP BY




VECTOR GROUP BY



SORT GROUP BY

HASH JOIN


HASH JOIN


HASH JOIN


VIEW VW_VT_3DF18547

VECTOR GROUP BY

HASH GROUP BY






date_dim

part

Execution Method for In-Memory Aggregation4. Sort Rows and Return results



FROM lineorder





WHERE d_year IN (1993, 1994, 1995)




customer

supplier

lineorder



SELECT STATEMENT



VECTOR GROUP BY




VECTOR GROUP BY




VECTOR GROUP BY



SORT GROUP BY

HASH JOIN


HASH JOIN


HASH JOIN


VIEW VW_VT_3DF18547

VECTOR GROUP BY

HASH GROUP BY






In-Memory AggregationConsiderations

• The vector transformation was designed for DBIM

• The optimizer chooses whether or not to use the vector transformation

• Things that influence the optimizer’s choice include the size of the key vector (i.e., distinct join keys) and number of distinct grouping keys

• The transformation tends to get chosen with low cardinality dimension join keys




Query ran in 2 seconds!

In this example, with In-Memory Aggregation, we aggregate as we scan and

improve performance All time spent in scan


Star Query Multi-User DemoPart I


• Star Schema Benchmark Data Model

• Four racers, B*Tree Indexes, Bitmap Indexes, Exadata, and In-Memory

• Each racer executes as many randomized queries as it can during the same interval

• Begin with four users and increase

Multi User DemoMultiuser Race Demo


Queries running in parallel with DoP=2

Multi-User Race Demo





Four concurrent users

Configuration

Quick config


Multi-User Race DemoFour Users (DoP=2)

B*Tree Indexes

Bitmap Indexes

Exadata

In-Memory

In-Memory completed almost twice the number

of queries as Exadata



In-Memory is nearly all CPU

Four Users (DoP=2)

Exadata and Indexes access methods constrained on I/O



We’ve got plenty of CPU capacity so let’s increase our

# of users to 8

Four Users (DoP=2)

Exadata storage cells @50-60% CPU utilization and ~40GB/second

of I/O with 4 users








Eight concurrent users

Configuration, 8 users

Quick config



In-Memory completed nearly double the

number of queries with twice the number of

users

How can this be?

Four User Test

Eight User Test

Four vs. Eight Users (DoP=2)

Exadata completed 15% more queries with twice

the number of users




Eight Users (DoP=2)




We still have CPU available for In-Memory testing, so

let’s run with 32 users

Eight Users (DoP=2)

Exadata storage cells @70% CPU utilization and reaching IO bandwidth limits at 8 users








32 concurrent users

Configuration, 32 users

Quick config



In-Memory throughput nearly linear with number of users

Eight User Test

32 User Test

32 Users (DoP=2)

Exadata completed just a 1 more query with 4x the

number of users




32 Users (DoP=2)



Multi-User Race Demo32 Users (DoP=2)

Exadata has reached its limits


• With few users, In-Memory marginally better than Exadata

• As we reach and exceed our ability to deliver IO on Exadata, throughput suffers

• Database In-Memory scales better with concurrency

• Single user, single query testing can be misleading

Multi-User DemoLessons Learned


Choosing Your Access Method & PlatformStar Query Technology Sweet Spots

ExadataIn-Memory

# Concurrent Queries

Fact

Tab

le R

ow

s af

ter

Filt

erin

g

Database Size

Fact

Tab

le R

ow

s af

ter

Filt

erin

g

Bitmap IndexesB*Tree Indexes

Exadata

In-Memory

Indexes


Reference Section


Summary: Pros, Cons, and Risk Assessment

TechniquePrimary Fact Table

Access MethodRequirements Pros Cons

B*Tree Indexes with NL Joins

• B*Tree index access• Nested Loops joins

• Indexes on fact table Decent performance if number of rows is very small and all data accessed is satisfied from memory

Algorithmically weak; can’t get fact table rows fast enough

Star transformation • Rowid from bitmap index• Bitmap merge• Star transformation

• star_transformation_enabled• query_rewrite_integrity• PK/FK constraints• NOT NULL constraints• Bitmap indexes on fact table

Excellent performance if number of rows is small and all data accessed is satisfied from memory

Poor performance if number of rows from fact table is high and requires random I/OExpensive maintenance of bitmap indexes

Full Scans with Intelligent Filtering

• Full scans• Swap join optimization & right-deep

tree• Bloom Filters and pipelined hash joins

• Exadata or DBIM• cell_offload_processing• PK/FK constraints• NOT NULL constraints

Can handle high and low cardinality queries to achieve consistent response times

Infrastructure cost, scalability as concurrency increases

In-Memory Aggregation • Full scans• Vector Transformation

• DBIM• PK/FK constraints• NOT NULL constraints

Excellent performance for both scan, filter, and aggregation


Star Query Techniques Platform Summary

TechniquePrimary Fact Table

Access MethodRequirements Pros Cons

Additional Performance Techniques

Bitmap indexes • Rowid from index• Bitmap merge• Star transformation

star_transformation_enabledquery_rewrite_integrityPK/FK constraintsNOT NULL constraints

Excellent performance if number of rows is small and all data accessed is satisfied from memory

Pathologically poor performance if number of rows from fact table is high and requires random I/O

Sorting to increaseclustering and cache hit ratesFixed width bitmaps

Exadata • Full scans• Swap join optimization &

right-deep tree• Bloom Filters• Pipelined hash joins

Exadatacell_offload_processingPK/FK constraintsNOT NULL constraints

Can handle high and low cardinality queries to achieveconsistent response times

Potential to saturatestorage cells with concurrent access

Partition pruningParallel querySorting & clustering to use storage indexesHCC

Database In-Memory

• Full scans• Swap join optimization &

right-deep tree• Bloom Filters• Pipelined hash joins• Vector transformation

inmemory_sizeTables loaded in column storePK/FK constraintsNOT NULL constraintsData types & precisions must match for Vector transformation

Can handle high and low cardinality queries to achieveconsistent response times

Memory requirement, CPU usage may be excessive if many rows/measures are required to be retrieved

Partition pruningParallel querySorting & clustering to perform IM Min-Max pruning


• Goal is to do extra work up-front while processing dimension tables to save time downstream

• Scans and filtering takes place in the DBIM column store

• Aggregation is performed as part of the fact table access

• Build a cube as we scan the fact table to avoid potentially costly aggregation

Oracle Database In-Memory

In-Memory AggregationWhy DO It?

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | 96

In-Memory Aggregation: ExampleSample Query

SELECT d_month, d_dayofweek, p_mfgr,

SUM(lo_quantity)

FROM lineorder



WHERE d_year = 1997


GROUP BY d_month, d_dayofweek, p_mfgr

ORDER BY d_month, d_dayofweek, p_mfgr


In-Memory Aggregation: ExampleStep 1a: Process DATE_DIM dimension

D_DATEKEY D_YEAR D_MONTH

D_DAYOFWEEK

1997-01-04 1997 January Saturday


1997-05-10 1997 May Saturday

1997-06-12 1997 June Thursday

1996-04-19 1996 April Friday

1993-03-21 1993 March Sunday

1997-02-04 1997 February Tuesday


1997-06-19 1997 June Thursday

1 0 2 3 0 0 4 1 3 …

19

97

-01

-04

19

97

-05

-10

19

97

-06

-12

19

96

-04

-19

19

93

-03

-21

19

97

-02

-04

19

94

-04

-09

19

97

-01

-01

19

97

-06

-19

Join Keys

Distinct Grouping Keys

DGK=1

DGK=1

DGK=2

DGK=3

DGK=3

DGK=4

D_MONTH D_DAYOFWEEK DGK

January Saturday 1

May Saturday 2

June Thursday 3

February Tuesday 4Temp Table


In-Memory Aggregation: ExampleStep 1b: Process PART dimension

P_PARTKEY P_MFGR P_CONTAINER

1 MFGR#1 JUMBO PACK

2 MFGR#2 CRATE

3 MFGR#3 PACK

4 MFGR#4 JUMBO PACK

5 MFGR#1 JUMBO PACK

6 MFGR#2 SLED

7 MFGR#2 JUMBO PACK

8 MFGR#1 JUMBO PACK

9 MFGR#3 JUMBO PACK

1 0 0 2 1 0 3 1 4 …

1Join Keys

Distinct Grouping Keys

DGK=1

DGK=1

DGK=2

DGK=3

DGK=4

P_MFGR DGK

MFGR#1 1

MFGR#2 3

MFGR#3 4

MFGR#4 2

Temp Table

2 3 4 5 6 7 8 9

DGK=1


1 2 3 4

1

2

3

4

99

In-Memory Aggregation: ExampleStep 2: Scan LINEORDER and Aggregate

LO_PARTKEY LO_ORDERDATE LO_QUANTITY

1 1997-06-12 10

3 1997-01-04 12

2 1993-04-06 16

1 1997-01-01 09

6 1997-02-04 01

8 1997-02-04 13

9 1997-06-19 44

4 1997-05-10 12

5 1997-01-04 13

1 0 0 2 1 0 3 1 4 …

1

Part KV

2 3 4 5 6 7 8 9

1 0 2 3 0 0 4 1 3 …

19

97

-01

-04

19

97

-05

-10

19

97

-06

-12

19

96

-04

-19

19

93

-03

-21

19

97

-02

-04

19

94

-04

-09

19

97

-01

-01

19

97

-06

-19

Date KV

LINEORDER10

1

2

In-Memory Accumulator




1 1997-06-12 10

3 1997-01-04 12

2 1993-04-06 16

1 1997-01-01 09

6 1997-02-04 01

8 1997-02-04 13

9 1997-06-19 44

4 1997-05-10 12

5 1997-01-04 13

1 0 0 2 1 0 3 1 4 …

1

Part KV

2 3 4 5 6 7 8 9

1 0 2 3 0 0 4 1 3 …

19

97

-01

-04

19

97

-05

-10

19

97

-06

-12

19

96

-04

-19

19

93

-03

-21

19

97

-02

-04

19

94

-04

-09

19

97

-01

-01

19

97

-06

-19

Date KV

1 2 3 4

1

2

3

4LINEORDER

10

Failed filter criteria

1





1 1997-06-12 10

3 1997-01-04 12

2 1993-04-06 16

1 1997-01-01 09

6 1997-02-04 01

8 1997-02-04 13

9 1997-06-19 44

4 1997-05-10 12

5 1997-01-04 13

1 0 0 2 1 0 3 1 4 …

1

Part KV

2 3 4 5 6 7 8 9

1 0 2 3 0 0 4 1 3 …

19

97

-01

-04

19

97

-05

-10

19

97

-06

-12

19

96

-04

-19

19

93

-03

-21

19

97

-02

-04

19

94

-04

-09

19

97

-01

-01

19

97

-06

-19

Date KV

1 2 3 4

1

2

3

4LINEORDER

10


1





1 1997-06-12 10

3 1997-01-04 12

2 1993-04-06 16

1 1997-01-01 09

6 1997-02-04 01

8 1997-02-04 13

9 1997-06-19 44

4 1997-05-10 12

5 1997-01-04 13

1 0 0 2 1 0 3 1 4 …

1

Part KV

2 3 4 5 6 7 8 9

1 0 2 3 0 0 4 1 3 …

19

97

-01

-04

19

97

-05

-10

19

97

-06

-12

19

96

-04

-19

19

93

-03

-21

19

97

-02

-04

19

94

-04

-09

19

97

-01

-01

19

97

-06

-19

Date KV

1 2 3 4

1

2

3

4LINEORDER

10

09

1

2





1 1997-06-12 10

3 1997-01-04 12

2 1993-04-06 16

1 1997-01-01 09

6 1997-02-04 01

8 1997-02-04 13

9 1997-06-19 44

4 1997-05-10 12

5 1997-01-04 13

1 0 0 2 1 0 3 1 4 …

1

Part KV

2 3 4 5 6 7 8 9

1 0 2 3 0 0 4 1 3 …

19

97

-01

-04

19

97

-05

-10

19

97

-06

-12

19

96

-04

-19

19

93

-03

-21

19

97

-02

-04

19

94

-04

-09

19

97

-01

-01

19

97

-06

-19

Date KV

1 2 3 4

1

2

3

4LINEORDER

10

09


1





1 1997-06-12 10

3 1997-01-04 12

2 1993-04-06 16

1 1997-01-01 09

6 1997-02-04 01

8 1997-02-04 13

9 1997-06-19 44

4 1997-05-10 12

5 1997-01-04 13

1 0 0 2 1 0 3 1 4 …

1

Part KV

2 3 4 5 6 7 8 9

1 0 2 3 0 0 4 1 3 …

19

97

-01

-04

19

97

-05

-10

19

97

-06

-12

19

96

-04

-19

19

93

-03

-21

19

97

-02

-04

19

94

-04

-09

19

97

-01

-01

19

97

-06

-19

Date KV

1 2 3 4

1

2

3

4LINEORDER

10

09

13

1

2





1 1997-06-12 10

3 1997-01-04 12

2 1993-04-06 16

1 1997-01-01 09

6 1997-02-04 01

8 1997-02-04 13

9 1997-06-19 44

4 1997-05-10 12

5 1997-01-04 13

1 0 0 2 1 0 3 1 4 …

1

Part KV

2 3 4 5 6 7 8 9

1 0 2 3 0 0 4 1 3 …

19

97

-01

-04

19

97

-05

-10

19

97

-06

-12

19

96

-04

-19

19

93

-03

-21

19

97

-02

-04

19

94

-04

-09

19

97

-01

-01

19

97

-06

-19

Date KV

1 2 3 4

1

2

3

4LINEORDER

10

09

13

44

1

2





1 1997-06-12 10

3 1997-01-04 12

2 1993-04-06 16

1 1997-01-01 09

6 1997-02-04 01

8 1997-02-04 13

9 1997-06-19 44

4 1997-05-10 12

5 1997-01-04 13

1 0 0 2 1 0 3 1 4 …

1

Part KV

2 3 4 5 6 7 8 9

1 0 2 3 0 0 4 1 3 …

19

97

-01

-04

19

97

-05

-10

19

97

-06

-12

19

96

-04

-19

19

93

-03

-21

19

97

-02

-04

19

94

-04

-09

19

97

-01

-01

19

97

-06

-19

Date KV

1 2 3 4

1

2

3

4LINEORDER

10

09

13

44

`

12

1

2





1 1997-06-12 10

3 1997-01-04 12

2 1993-04-06 16

1 1997-01-01 09

6 1997-02-04 01

8 1997-02-04 13

9 1997-06-19 44

4 1997-05-10 12

5 1997-01-04 13

1 0 0 2 1 0 3 1 4 …

1

Part KV

2 3 4 5 6 7 8 9

1 0 2 3 0 0 4 1 3 …

19

97

-01

-04

19

97

-05

-10

19

97

-06

-12

19

96

-04

-19

19

93

-03

-21

19

97

-02

-04

19

94

-04

-09

19

97

-01

-01

19

97

-06

-19

Date KV

1 2 3 4

1

2

3

4LINEORDER

10

09

13

44

12

`

22

1

2



In-Memory Aggregation: ExampleStep 3: Join back to project dimension columns

SELECT d_month, d_dayofweek, p_mfgr,

SUM(lo_quantity)

FROM lineorder



WHERE d_year = 1997


GROUP BY d_month, d_dayofweek, p_mfgr

ORDER BY d_month, d_dayofweek, p_mfgr

1 2 3 4

1

2

3

4

10

13

44

12

22


D_MONTH

D_DAYOFWEEK DGK

January Saturday 1

May Saturday 2

June Thursday 3

February Tuesday 4

P_MFGR DGK

MFGR#1 1

MFGR#2 3

MFGR#3 4

MFGR#4 2

Date Temp Table Part Temp Table

Month Day of Week Mfgr SUM(QTY)

February Tuesday MFGR#1 13

January Saturday MFGR#1 22

June Thursday MFGR#1 10

June Thursday MFGR#3 44

May Saturday MFGR#4 12

real-world performance training · bitmap indexes and the star transformation full scans and...

Documents