saving your database from alzheimer’s: analytic ...€¦ · saving your database from...

Saving Your Database from Alzheimer’s: Analytic Performance Improvements

From Oracle 12cR2 Database In-Memory (DBIM)

Jim Czuprynski

ViON Corporation

February 21, 2018

April 2-6, 2017 in Las Vegas, NV USA #C17LV

My Credentials

• 35+ years of database-centric IT experience

• Oracle DBA since 2001

• Oracle 9i, 10g, 11g, 12c OCP

• Oracle ACE Director

• 100+ articles on databasejournal.com and IOUG SELECT

• Co-author of 4 Oracle books

• Oracle-centric blog (Generally, It Depends)

• Regular speaker at Oracle OpenWorld, IOUG COLLABORATE, Hotsos Symposium, and Regional OUGs


Our Agenda

• What’s New in DBIM for Oracle 12cR2: A Brief Review• In-Memory Expressions and In-Memory Virtual Columns

• Improvements In-Memory Filtering and Aggregation

• In-Memory Join Groups

• ILM ADO Policies for In-Memory Tables

• Analytic Function Improvements• Analytic Views

• New APPROX_ Functions

• “Virtual” Materialized Views

• DBIM: What’s Coming in Oracle 18c

• Conclusions: DBIM and Analytics - A Powerful Partnership!

DBIM: Key New Features in 12cR2


In-Memory Expressions (IME)

IMEs are pre-computed, frequently evaluated expressions

• Created for virtual columns and automatic capture • Frequently evaluated expressions

• Expressions needed for filtering, joins, etc.

• Virtual columns (defined within a table)

• Auto-detected ”hot” (i.e. dynamic) expressions

• JSON functions also supported• Automatically retained in Expression Statistics Store (ESS)

• Since they need no disk storage, IMEs may replace the need for Materialized Views (MVs)• No need to refresh MV table when underlying base tables change


In-Memory Expressions: Advantages

Instead of calculating an

expression over and over again …

… the expression itselfcan be retained within a corresponding IMEU …

… and the 12.2 optimizer knows to retrieve it from the IMEU instead, thus

offering potential reduction in intense CPU

cycles


DBIM: Improved Filtering Operations

• Explicit and implicit predicates can be pushed to In-Memory Scans

• IMCU storage indexes leveraged for faster elimination• Requires that MEMCOMPRESS is set to any other level than NONE (so that IMCU

dictionary entries are created)

• Query optimizer is now fully aware of advantages of using In-Memory Filtering

• Complex joins can often be transformed into scan and filter operations

• A plethora of new IM session-level statistics help explain what’s happening during operations

• Bloom Filters (BFs) can now convert certain joins into scan and filteroperations

• BFs can also leverage In-Memory storage indexes and MIN/MAX pruning

• BFs are extremely effective for multi-set joins between multiple dimensions to a fact table

• Some LEFT DEEP TREE joins can be transformed to RIGHT DEEP TREE joins via SWAP JOIN INPUT optimization


Improved In-Memory Joins (IMJ)

What if there are no filter predicates on joined tables?

• Only choice in 12cR1: HASH JOIN in PGA memory

• Each IMCU as a local dictionary – so subscripts for same values may be different

• Data has to be decompressed from IMCS (expensive) and then hashed values are

probed in PGA

• In 12cR2, In-Memory Joins can leverage Join Groups

• Useful when query offers no filter predicates on joined tables

• Leverages new IMCU global dictionary to find matching data

• Since subscripts are identical for same values in all tables, no decompression is required!

• Join Groups are identified with CREATE INMEMORY JOIN GROUP command

• Viewable in V$IM_SEGDICT


Left Deep Tree vs. Right Deep Tree Joins

LEFT DEEP TREE Hash JoinsHASH JOIN

HASH JOIN

HASH JOIN

ITEMS STORE_SALES

HOUSEHOLD_DEMOGRAPHICS

DATE_DIM

A Bloom Filter is created for ITEMS :

STORE SALES …

… and it’s then applied against STORE_SALES …

… but since this is a LEFT DEEP TREE join, BFs can’t be

used for DATE_DIM and HOUSEHOLD_DEMOGRAPHICS

tables

RIGHT DEEP TREE Hash Joins

HASH JOIN

DATE_DIM HASH_JOIN

HOUSEHOLD_DEMOGRAPHICS

HASH_JOIN

ITEMS STORE_SALES

But for a RIGHT DEEP TREE join, a Bloom

Filter is created for allthree dimensions …

… and the BFs can be used at all

levels, essentially applied as a single

filter just once


Join Groups: A Common Dictionary for IMCUs

SALES_FACT

CommonDictionary

Column Value

ID

Austria 0

Canada 2

… …

Sweden 12

USA 13

Zimbabwe 14

IMCU2150

113

1311

IMCU13

1112131

144

IMCU103491463

IMCU5

123

112

121

14

GEO_DIM

IMCU789

1011121314

Holland

India

Netherlands

Poland

Russia

Sweden

USA

Zimbabwe

1.3

1350

0.9

24.1

183.2

4.1

326

1.6

IMCUgeo_id

0123456

name

Austria

Belgium

Canada

Denmark

Ethiopia

France

Germany

pop

1.5

0.7

15.2

2.8

13.1

42.8

52.1

With a common dictionary, each

table’s IMCUs have the same value

stored in every CU …

… for the samecolumn defined in the Join Group …

… instead of the actual column

value itself


Hash Joins With Join Groups

GEO_DIM

IMCU789

1011121314

Holland

India

Netherlands

Poland

Russia

Sweden

USA

Zimbabwe

1.3

1350

0.9

24.1

183.2

4.1

326

1.6

IMCUgeo_id

0123456

name

Austria

Belgium

Canada

Denmark

Ethiopia

France

Germany

pop

1.5

0.7

15.2

2.8

13.1

42.8

52.1

SALES_FACTIMCU

2150

113

1311

IMCU

13

1112131

144

IMCU

103491463

IMCU

5123

112

121

14

PGA

SELECT SF.*

FROM sales_fact SF, geo_dim GD

WHERE SF.geo_id = GD.geo_id

AND GD.pop >= 50;

1

2

3

4

5

Common Dictionary Array

0 0 0 0 0 0 1

0 1 0 0 1 0 1

Scan GEO_DIM and send common dictionary codes for matching GEO_IDs in compressed format to Hash Join

1 Using compressed common dictionary values, build an array of distinct values

2 Scan SALES_FACT and filter rows based on predicate

3 Send just matching SALES_FACT rows in compressed format to hash join

4 Complete hash join operation by looking up compressed values in array

5

? = ?


In-Memory Area: At Last, A True Storage Tier 0!

• Heat Maps can now used to decide which segments should remain in IMCS or be evicted based on memory pressure

• In 12cR2, Information Lifecycle Management (ILM) now supports Automatic Data Optimization (ADO) policies for managing objects within the In-Memory Area

• ADO will: • Moves or compresses data based on observed usage patterns

• Leverages heat maps to determine how often data has been accessed

• Tracks exactly how data has been utilized • DML versus query

• Random access versus table scan

• Usage patterns can be tracked at tablespace, segment, and even row level

• Heat Maps are simple to activate:

12

SQL> ALTER SYSTEM SET heat_map = ON;


DBIM ILM ADO Policies: An Example

2 days after partition creation:

15 days after partition creation:

Right after a new partition is created, heavy DML and online query activity is likely, so DBIM offers no benefit

After 180 days of no access to partition:

SQL> ALTER TABLE tpcds.catalog_sales

ILM ADD POLICY

SET INMEMORY MEMCOMPRESS FOR QUERY LOW

PRIORITY HIGH SEGMENT

AFTER 2 DAYS OF CREATION;

SQL> ALTER TABLE tpcds.store_sales

ILM ADD POLICY

MODIFY INMEMORY MEMCOMPRESS FOR QUERY HIGH

PRIORITY MEDIUM SEGMENT

AFTER 15 DAYS OF CREATION;

SQL> ALTER TABLE tpcds.web_sales

ILM ADD POLICY

NO INMEMORY AFTER 180 DAYS OF NO ACCESS;

DBIM Experiments: Phase 2.0• New Testing Environments

• Longest-Running Queries … Aren’t Any More!


Testing Environment: TPC-DS Schema

24 tables consisting of 17 dimensions and 7 fact tables• All fact tables

partitioned on date of activity

• Partitioning permits faster access through parallel execution and partition pruning

STORE_SALES Entity-Relationship Diagram

Swingbench 2.6.1leveraged for repeated testing• Initial results

obtained …


Tuning Goal: Make The Long-Running Queries Finish

• Database Environment:• Hardware: Oracle Public Cloud Exadata Express

• Oracle 12cR2 (12.2.0.1) database on Oracle Linux

• Only two (2) virtualized CPUs

• 30GB SGA, but In-Memory Area limited to only 5GB

• Schema: TPC-DS• 7 fact tables, 17 dimensions

• Fact tables partitioned for better access paths and parallelism

• Approximate size: 20GB data, 5GB indexes

• Queries: Between horrible and nasty!• 100+ standard queries answering complex business questions

• Queries written in generic SQL to permit easy transition between RDBMSs

• 10 queries never finish within their proscribed 2700-second timeout

• Changing query source code is not permitted


Creating Join Groups

Add Join Groups for TPCDS.STORE_SALES:1

SQL> CREATE INMEMORY JOIN GROUP tpcds.jg_ss_date (

tpcds.store_sales (ss_sold_date_sk)

,tpcds.date_dim (d_date_sk)

);

CREATE INMEMORY JOIN GROUP tpcds.jg_ss_cdemo (

tpcds.store_sales (ss_cdemo_sk)

,tpcds.customer_demographics (cd_demo_sk)

);

CREATE INMEMORY JOIN GROUP tpcds.jg_ss_hdemo (

tpcds.store_sales (ss_hdemo_sk)

,tpcds.household_demographics (hd_demo_sk)

);

Note: Only one Join Group per column permitted!2

SQL> CREATE INMEMORY JOIN GROUP tpcds.jg_cs_date (

tpcds.catalog_sales (cs_sold_date_sk)

,tpcds.date_dim (d_date_sk)

);

SQL> 2 3 4 ,tpcds.date_dim (d_date_sk)

*

ERROR at line 3:

ORA-00957: duplicate column name

Join Group common directory entries (after repopulation in IMCS)3

SELECT

joingroup_name

,table_name

,column_name

,gd_address

FROM dba_joingroups

ORDER BY 1,2;

Join Groups

(from DBA_JOINGROUPS)

Common

Join Group Directory

Name Table Name Column Name Address

------------ ------------------------ ------------------- ----------------

JG_SS_CDEMO CUSTOMER_DEMOGRAPHICS CD_DEMO_SK 0000000655005E60

JG_SS_CDEMO STORE_SALES SS_CDEMO_SK 0000000655005E60

JG_SS_DATE DATE_DIM D_DATE_SK 00000006550E5E60

JG_SS_DATE STORE_SALES SS_SOLD_DATE_SK 00000006550E5E60

JG_SS_HDEMO HOUSEHOLD_DEMOGRAPHICS HD_DEMO_SK 0000000655135E60

JG_SS_HDEMO STORE_SALES SS_HDEMO_SK 0000000655135E60


Detecting Join Group Usage

Query against TPCDS.STORE_SALES and other dimensions that can benefit from Join Groups:

1

SQL> SELECT /*query100*/

d_year

,d_qoy

,hd_income_band_sk

,cd_gender

,cd_marital_status

,SUM(ss_gross_revenue)

,SUM(ss_net_revenue)

,SUM(ss_extd_gpm)

FROM

tpcds.store_sales SS

,tpcds.customer_demographics CD

,tpcds.household_demographics HD

,tpcds.date_dim DD

WHERE SS.ss_cdemo_sk = CD.cd_demo_sk

AND SS.ss_hdemo_sk = HD.hd_demo_sk

AND SS.ss_sold_date_sk = DD.d_date_sk

GROUP BY

d_year, d_qoy

,hd_income_band_sk, cd_gender, cd_marital_status

ORDER BY

d_year, d_qoy

,hd_income_band_sk, cd_gender, cd_marital_status;

Did a Join Group get used? Proof via PL/SQL …2

SET SERVEROUTPUT ON

DECLARE

b_sqlid VARCHAR2(32);

vc_jgusage VARCHAR2(50);

BEGIN

SELECT prev_sql_id

INTO :b_sqlid

FROM v$session

WHERE SID = USERENV('SID');

vc_jgusage :=

DBMS_SQLTUNE.REPORT_SQL_MONITOR_XML(

sql_id => :b_sqlid).EXTRACT(q'#//operation[@name='HASH

JOIN']/rwsstats/stat[@id='9']#').GETCLOBVAL(2,2) join_group_usage;

DBMS_OUTPUT.PUT_LINE(‘Join Group Usage: ‘ || vc_jgusage);

END;

/

… or via SQL:3

SET LONGCHUNKSIZE 10000000 LONG 10000000

COL join_group_usage FORMAT A50

SELECT

DBMS_SQLTUNE.REPORT_SQL_MONITOR_XML(sql_id=>'9mtgnc8fxmg1f').EXTRACT(q'#//opera

tion[@name='HASH JOIN']/rwsstats/stat[@id='9']#').GETCLOBVAL(2,2)

join_group_usage

FROM DUAL;JOIN_GROUP_USAGE

---------------------

<stat id="9">1</stat>

<stat id="9">1</stat>


Demonstrating Join Group Value

PARALLEL(4), but INMEMORY_QUERY = DISABLE:1

Elapsed: 00:00:07.62Execution Plan

----------------------------------------------------------

Plan hash value: 4293234100

--------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

--------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 38190 | 3170K| | 18506 (1)| 00:00:01 |

| 1 | PX COORDINATOR | | | | | | |

| 2 | PX SEND QC (ORDER) | :TQ10002 | 38190 | 3170K| | 18506 (1)| 00:00:01 |

| 3 | SORT GROUP BY | | 38190 | 3170K| 530M| 18506 (1)| 00:00:01 |

| 4 | PX RECEIVE | | 38190 | 3170K| | 18506 (1)| 00:00:01 |

| 5 | PX SEND RANGE | :TQ10001 | 38190 | 3170K| | 18506 (1)| 00:00:01 |

| 6 | HASH GROUP BY | | 38190 | 3170K| 530M| 18506 (1)| 00:00:01 |

|* 7 | HASH JOIN | | 5773K| 467M| | 8012 (1)| 00:00:01 |

| 8 | PX RECEIVE | | 1920K| 18M| | 1234 (1)| 00:00:01 |

| 9 | PX SEND BROADCAST | :TQ10000 | 1920K| 18M| | 1234 (1)| 00:00:01 |

| 10 | PX BLOCK ITERATOR | | 1920K| 18M| | 1234 (1)| 00:00:01 |

| 11 | TABLE ACCESS FULL| CUSTOMER_DEMOGRAPHICS | 1920K| 18M| | 1234 (1)| 00:00:01 |

|* 12 | HASH JOIN | | 5760K| 412M| | 6772 (1)| 00:00:01 |

| 13 | TABLE ACCESS FULL | DATE_DIM | 73049 | 927K| | 114 (0)| 00:00:01 |

|* 14 | HASH JOIN | | 5760K| 340M| | 6653 (1)| 00:00:01 |

| 15 | TABLE ACCESS FULL | HOUSEHOLD_DEMOGRAPHICS | 7200 | 50400 | | 10 (0)| 00:00:01 |


| 17 | TABLE ACCESS FULL| STORE_SALES | 5760K| 302M| | 6639 (1)| 00:00:01 |

--------------------------------------------------------------------------------------------------------------

PARALLEL(4), but INMEMORY_QUERY = ENABLE:2

Elapsed: 00:00:03.43

Execution Plan

----------------------------------------------------------


-----------------------------------------------------------------------------------------------------------------------


-----------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 38190 | 3170K| | 10876 (2)| 00:00:01 |

| 1 | PX COORDINATOR | | | | | | |

| 2 | PX SEND QC (ORDER) | :TQ10002 | 38190 | 3170K| | 10876 (2)| 00:00:01 |

| 3 | SORT GROUP BY | | 38190 | 3170K| 530M| 10876 (2)| 00:00:01 |

| 4 | PX RECEIVE | | 38190 | 3170K| | 10876 (2)| 00:00:01 |

| 5 | PX SEND RANGE | :TQ10001 | 38190 | 3170K| | 10876 (2)| 00:00:01 |

| 6 | HASH GROUP BY | | 38190 | 3170K| 530M| 10876 (2)| 00:00:01 |

|* 7 | HASH JOIN | | 5773K| 467M| | 382 (22)| 00:00:01 |

| 8 | PX RECEIVE | | 1920K| 18M| | 51 (10)| 00:00:01 |

| 9 | PX SEND BROADCAST | :TQ10000 | 1920K| 18M| | 51 (10)| 00:00:01 |


| 11 | TABLE ACCESS INMEMORY FULL| CUSTOMER_DEMOGRAPHICS | 1920K| 18M| | 51 (10)| 00:00:01 |

|* 12 | HASH JOIN | | 5760K| 412M| | 324 (22)| 00:00:01 |

| 13 | TABLE ACCESS INMEMORY FULL | DATE_DIM | 73049 | 927K| | 5 (20)| 00:00:01 |

|* 14 | HASH JOIN | | 5760K| 340M| | 315 (22)| 00:00:01 |

| 15 | TABLE ACCESS INMEMORY FULL | HOUSEHOLD_DEMOGRAPHICS | 7200 | 50400 | | 2 (0)| 00:00:01 |


| 17 | TABLE ACCESS INMEMORY FULL| STORE_SALES | 5760K| 302M| | 309 (21)| 00:00:01 |

-----------------------------------------------------------------------------------------------------------------------

DBIM and Real-Time Analytics

• Improved APPROX Functions

• Attribute Dimensions, Hierarchies, and Analytic Views


Analytic Functions

• New APPROX_ functions for faster calculations of key analytics

• Materialized Views (MVs) and APPROX_ functions

• Analytic Views (AVs)

• Leveraging DBIM for even speedier access


APPROX_ Functions: Close Enough For What We’re Doing.

Operation Brute Force Mechanism

DB Release

New APPROX Methods

Count distinct values

COUNT(DISTINCT [column])

12.1.0.2 APPROX_COUNT_DISTINCT

12.2.0.1 APPROX_COUNT_DISTINCT_DETAILAPPROX_COUNT_DISTINCT_AGGTO_APPROX_COUNT_DISTINCT

Calculate percentiles

PERCENTILE_CONTPERCENTILE_DISC

12.2.0.1 APPROX_PERCENTILEAPPROX_PERCENTILE_DETAIL

APPROX_PERCENTILE_AGGTO_APPROX_PERCENTILE

Determine median values

MEDIAN 12.1.0.2 APPROX_MEDIAN


But I’ll Have to Rewrite All My Code! (Ummm … No.)

Parameter Controls

APPROX_FOR_AGGREGATION Automatically switches to approximatefunctionality for aggregation and analytic

functions

APPROX_FOR_COUNT_DISTINCT Automatically switches COUNT(DISTINCT [column]) to its APPROX_DISTINCT

counterpart

APPROX_FOR_PERCENTILE Automatically swaps PERCENTILE functions for their 12.2.0.1 APPROX_PERCENTILE versions


Hierarchies, Everywhere You Look

Implicit, obvious hierarchies are legion

• Days within week, month, quarter, year

• Locales within county/parish, state/province, country

… but explicit hierarchies are not uncommon! • Insurance: Insureds within policy, organization, group

• Manufacturing: Parts within assembly, model, make

Different CxOs need different hierarchies• CFO: Transactions within accounts & ledgers

• CIO: Components within servers, datacenters, regions

• SVP: Territories within districts & regions

• HCM: Persons within departments & divisions


Preparations

# Grant appropriate privileges to user accounts:-- Grant appropriate privileges to user accounts

GRANT CREATE ATTRIBUTE DIMENSION TO tpcds;

GRANT CREATE HIERARCHY TO tpcds;

GRANT CREATE ANALYTIC VIEW TO tpcds;

1

# Add and populate new columns in TPCDS.DATE_DIM:ALTER TABLE tpcds.date_dim

ADD (

d_moy_key CHAR(07)

,d_moy_abbr CHAR(03)

,d_moy_name VARCHAR2(09)

,d_qoy_key CHAR(06)

);

UPDATE tpcds.date_dim

SET

d_moy_key = TO_CHAR(d_date, 'YYYY-MM')

,d_moy_abbr = TO_CHAR(d_date, 'MON')

,d_moy_name = TO_CHAR(d_date, 'Month')

,d_qoy_key = TO_CHAR(d_date, 'YYYY') || 'Q' ||TO_CHAR(d_date,'Q');

COMMIT;

2


Building Attribute Dimensions

# Create an Attribute Dimension:CREATE OR REPLACE ATTRIBUTE DIMENSION

tpcds.avad_dates

DIMENSION TYPE TIME

USING tpcds.date_dim

ATTRIBUTES (

d_year

,d_qoy_key

,d_quarter_name

,d_moy_key

,d_moy_abbr

,d_moy_name

,d_date

,d_date_sk

,d_day_name

)

LEVEL day

LEVEL TYPE days

KEY d_date_sk

ALTERNATE KEY d_date

MEMBER NAME TO_CHAR(d_date,'yyyy-mm-dd')

MEMBER CAPTION d_day_name

MEMBER DESCRIPTION d_day_name

ORDER BY d_date

DETERMINES (d_moy_key)

. . .

Attribute Dimension

Dimension Attributes

Explicit Levels of Detail

# Attribute Dimension (cont’d):. . .

LEVEL month

LEVEL TYPE months

KEY d_moy_key

MEMBER NAME d_moy_key

MEMBER CAPTION d_moy_abbr

MEMBER DESCRIPTION d_moy_name

ORDER BY d_moy_key

DETERMINES (d_qoy_key)

LEVEL quarter

LEVEL TYPE quarters

KEY d_qoy_key

MEMBER NAME d_qoy_key

MEMBER CAPTION d_qoy_key

MEMBER DESCRIPTION d_qoy_key

ORDER BY d_qoy_key

DETERMINES (d_year)

LEVEL year

LEVEL TYPE years

KEY d_year

MEMBER NAME TO_CHAR(d_year)

MEMBER CAPTION TO_CHAR(d_year)

MEMBER DESCRIPTION TO_CHAR(d_year)

ORDER BY d_year

ALL

MEMBER NAME 'ALL DATES'

MEMBER CAPTION 'All Dates'

MEMBER DESCRIPTION 'All Timestamps‘;

Attribute dimension created.


Creating Explicit Hierarchies

# Create a Hierarchy:SQL> CREATE OR REPLACE HIERARCHY

tpcds.avhy_dates

USING tpcds.avad_dates (

day CHILD OF

month CHILD OF

quarter CHILD OF

year

);

Hierarchy created.

Attribute Dimension

HierarchyDescription

# Show the resulting Hierarchy:SELECT

level_name

,hier_order

,depth

,d_year

,d_qoy_key

,d_moy_key

,member_name

,member_unique_name

,member_caption

,member_description

FROM tpcds.avhy_dates

WHERE level_name <> 'DAY'

AND d_year = 2017

ORDER BY hier_order;

Hierarchy Sample Data for 2017

(from TPCDS.AVHY_DATES)

Member

Hier Hier Hier Qtr Mth Member Unique Member Member

Level Order Depth Year Key Key Name Name Caption Description

---------- ------- ------ ------ -------- -------- ---------- -------------------- ---------- ------------

YEAR 44723 1 2017 2017 [YEAR].&[2017] 2017 2017

QUARTER 44724 2 2017 2017Q1 2017Q1 [QUARTER].&[2017Q1] 2017Q1 2017Q1

MONTH 44725 3 2017 2017Q1 2017-01 2017-01 [MONTH].&[2017-01] JAN January

MONTH 44757 3 2017 2017Q1 2017-02 2017-02 [MONTH].&[2017-02] FEB February

MONTH 44786 3 2017 2017Q1 2017-03 2017-03 [MONTH].&[2017-03] MAR March


MONTH 44819 3 2017 2017Q2 2017-04 2017-04 [MONTH].&[2017-04] APR April

MONTH 44850 3 2017 2017Q2 2017-05 2017-05 [MONTH].&[2017-05] MAY May

MONTH 44882 3 2017 2017Q2 2017-06 2017-06 [MONTH].&[2017-06] JUN June


MONTH 44914 3 2017 2017Q3 2017-07 2017-07 [MONTH].&[2017-07] JUL July

MONTH 44946 3 2017 2017Q3 2017-08 2017-08 [MONTH].&[2017-08] AUG August

MONTH 44978 3 2017 2017Q3 2017-09 2017-09 [MONTH].&[2017-09] SEP September


MONTH 45010 3 2017 2017Q4 2017-10 2017-10 [MONTH].&[2017-10] OCT October

MONTH 45042 3 2017 2017Q4 2017-11 2017-11 [MONTH].&[2017-11] NOV November

MONTH 45073 3 2017 2017Q4 2017-12 2017-12 [MONTH].&[2017-12] DEC December

17 rows selected.


Creating Analytic Views: Unpacking the Basics

# Create a simple Analytic View:SQL> CREATE OR REPLACE ANALYTIC VIEW

tpcds.av_ss_basic

USING tpcds.store_sales

DIMENSION BY (

avad_dates

KEY ss_sold_date_sk

REFERENCES d_date_sk

HIERARCHIES (avhy_dates DEFAULT)

)

MEASURES (

dtl_qty FACT ss_quantity

);

Analytic view created.

Analytic View

Primary Source

Attribute Dimensions

Measures

Hierarchies


Analytic Views: More Complex MEASUREs

# Create a more complex Analytic View:CREATE OR REPLACE ANALYTIC VIEW tpcds.av_ss_complex

USING tpcds.store_sales

DIMENSION BY (

avad_dates

KEY ss_sold_date_sk

REFERENCES d_date_sk

HIERARCHIES (avhy_dates DEFAULT)

)

MEASURES (

ss_qty FACT ss_quantity

-- Aggregations within hierarchy levels:

,ytd_qty AS (

SUM(ss_qty)

OVER (HIERARCHY avhy_dates

BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER

WITHIN ANCESTOR AT LEVEL year)

)

,qtd_qty AS (

SUM(ss_qty)


BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER

WITHIN ANCESTOR AT LEVEL quarter)

)

. . .

Measures can also leverage windowing functions …

# Complex Analytic View (cont’d):. . .

,qty_prior_period AS (

LAG(ss_qty)


OFFSET 1)

)

-- SHARE OF Totals:

,qty_shr_year AS (

SHARE_OF(ss_qty HIERARCHY avhy_dates

LEVEL year)

)

,qty_shr_qtr AS (

SHARE_OF(ss_qty HIERARCHY avhy_dates

LEVEL quarter)

)

-- Qualified Data Regions:

,qty_pre_y2k AS (

QUALIFY (ss_qty, avhy_dates = year['1999'])

)

,qty_post_y2k AS (

QUALIFY (ss_qty, avhy_dates = quarter['2000Q1'])

)

)

DEFAULT MEASURE ss_qty;

Analytic view created.

… and qualified data regions are filtered

subsets of measures

… and SHARE_OFmeasures capture

shares of the wholewithin a hierarchy …


Querying an Analytic View: A Simple Example

# A simple AV query:

COL level_name FORMAT A10 HEADING "Hier|Level"

COL member_name FORMAT A10 HEADING "Member|Name"

COL dtl_qty FORMAT 999,999,999.99 HEADING "Store|Sales|Quantity"

TTITLE "Basic Analytic Query Example|(from TPCDS.AV_SS_BASIC)"

SELECT

avhy_dates.level_name

,avhy_dates.member_name

,dtl_qty

FROM tpcds.av_ss_basic

WHERE avhy_dates.level_name IN ('ALL','YEAR','QUARTER','MONTH')

ORDER BY avhy_dates.hier_order

;

TTITLE OFF

Basic Analytic Query Example

(from TPCDS.AV_SS_BASIC)

Store

Hier Member Sales

Level Name Quantity

---------- ---------- ---------------

ALL ALL DATES 288,272,713.00

YEAR 1998 57,064,038.00

QUARTER 1998Q1 13,983,442.00

MONTH 1998-01 4,684,654.00

MONTH 1998-02 4,398,090.00

MONTH 1998-03 4,900,698.00

QUARTER 1998Q2 14,263,620.00

MONTH 1998-04 4,679,562.00

MONTH 1998-05 4,871,646.00

MONTH 1998-06 4,712,412.00

QUARTER 1998Q3 14,430,832.00

MONTH 1998-07 4,844,202.00

MONTH 1998-08 4,915,358.00

MONTH 1998-09 4,671,272.00

QUARTER 1998Q4 14,386,144.00

MONTH 1998-10 4,857,150.00

MONTH 1998-11 4,681,908.00

MONTH 1998-12 4,847,086.00

. . .

YEAR 1999 57,368,014.00

QUARTER 1999Q1 14,159,312.00

MONTH 1999-01 4,896,268.00

MONTH 1999-02 4,455,816.00

MONTH 1999-03 4,807,228.00

QUARTER 1999Q2 14,307,102.00

MONTH 1999-04 4,730,778.00

MONTH 1999-05 4,857,914.00

MONTH 1999-06 4,718,410.00

QUARTER 1999Q3 14,426,508.00

MONTH 1999-07 4,909,008.00

MONTH 1999-08 4,817,114.00

MONTH 1999-09 4,700,386.00

QUARTER 1999Q4 14,475,092.00

MONTH 1999-10 4,860,494.00

MONTH 1999-11 4,707,378.00

MONTH 1999-12 4,907,220.00

. . .

. . .

YEAR 2000 57,511,340.00

QUARTER 2000Q1 14,326,914.00

MONTH 2000-01 4,819,758.00

MONTH 2000-02 4,592,016.00

MONTH 2000-03 4,915,140.00

QUARTER 2000Q2 14,322,272.00

MONTH 2000-04 4,727,808.00

MONTH 2000-05 4,903,956.00

MONTH 2000-06 4,690,508.00

QUARTER 2000Q3 14,402,968.00

MONTH 2000-07 4,879,852.00

MONTH 2000-08 4,850,890.00

MONTH 2000-09 4,672,226.00

QUARTER 2000Q4 14,459,186.00

MONTH 2000-10 4,897,608.00

MONTH 2000-11 4,686,910.00

MONTH 2000-12 4,874,668.00

. . .

. . .

YEAR 2001 57,118,131.00

QUARTER 2001Q1 14,125,274.00

MONTH 2001-01 4,925,032.00

MONTH 2001-02 4,350,420.00

MONTH 2001-03 4,849,822.00

QUARTER 2001Q2 14,214,084.00

MONTH 2001-04 4,715,698.00

MONTH 2001-05 4,830,712.00

MONTH 2001-06 4,667,674.00

QUARTER 2001Q3 14,369,151.00

MONTH 2001-07 4,851,668.00

MONTH 2001-08 4,849,892.00

MONTH 2001-09 4,667,591.00

QUARTER 2001Q4 14,409,622.00

MONTH 2001-10 4,826,386.00

MONTH 2001-11 4,704,742.00

MONTH 2001-12 4,878,494.00

. . .

. . .

YEAR 2002 57,478,306.00

QUARTER 2002Q1 14,218,054.00

MONTH 2002-01 4,879,048.00

MONTH 2002-02 4,435,116.00

MONTH 2002-03 4,903,890.00

QUARTER 2002Q2 14,289,346.00

MONTH 2002-04 4,679,752.00

MONTH 2002-05 4,912,014.00

MONTH 2002-06 4,697,580.00

QUARTER 2002Q3 14,529,304.00

MONTH 2002-07 4,911,188.00

MONTH 2002-08 4,892,030.00

MONTH 2002-09 4,726,086.00

QUARTER 2002Q4 14,441,602.00

MONTH 2002-10 4,881,696.00

MONTH 2002-11 4,670,818.00

MONTH 2002-12 4,889,088.00

YEAR 2003 1,732,884.00

QUARTER 2003Q1 1,732,884.00

MONTH 2003-01 1,732,884.00

89 rows selected.


A More Complex AV Query

COL level_name FORMAT A10 HEADING "Hier|Level"

COL member_name FORMAT A10 HEADING "Member|Name"

COL ss_qty FORMAT 999,999,999 HEADING "Store|Sales|Quantity"

COL ytd_qty FORMAT 999,999,999 HEADING "Y-T-D Quantity"

COL qtd_qty FORMAT 999,999,999 HEADING "Q-T-D Quantity"

COL qty_prior_period FORMAT 999,999,999 HEADING "Prior Year|Quantity"

COL qty_shr_year FORMAT 999.99 HEADING "Prior|Year|Share"

COL qty_shr_qtr FORMAT 999.99 HEADING "Prior|Qtr|Share"

COL qty_pre_y2k FORMAT 999,999,999 HEADING "Pre-Y2K|Quantity"

COL qty_post_y2k FORMAT 999,999,999 HEADING "Post-Y2K|Quantity"

TTITLE "Complex Analytic Query Example|(from TPCDS.AV_SS_COMPLEX)"

SELECT

avhy_dates.level_name

,avhy_dates.member_name

,ss_qty

,ytd_qty

,qtd_qty

,qty_prior_period

,qty_shr_year

,qty_shr_qtr

,qty_pre_y2k

,qty_post_y2k

FROM tpcds.av_ss_complex

WHERE avhy_dates.level_name IN ('ALL','YEAR','QUARTER','MONTH')

ORDER BY avhy_dates.hier_order;

Complex Analytic Query Example

(from TPCDS.AV_SS_COMPLEX)

Store Prior Prior Prior

Hier Member Sales Y-T-D Q-T-D Period Year Qtr Pre-Y2K Post-Y2K

Level Name Quantity Quantity Quantity Quantity Share Share Quantity Quantity

---------- ---------- ------------ ------------ ------------ ------------ ------- ------- ------------ ------------

ALL ALL DATES 288,272,713 57,368,014 14,326,914

YEAR 1998 57,064,038 57,064,038 1.00 57,368,014 14,326,914

QUARTER 1998Q1 13,983,442 13,983,442 13,983,442 .25 1.00 57,368,014 14,326,914

MONTH 1998-01 4,684,654 4,684,654 4,684,654 .08 .34 57,368,014 14,326,914

MONTH 1998-02 4,398,090 9,082,744 9,082,744 4,684,654 .08 .31 57,368,014 14,326,914

MONTH 1998-03 4,900,698 13,983,442 13,983,442 4,398,090 .09 .35 57,368,014 14,326,914

QUARTER 1998Q2 14,263,620 28,247,062 14,263,620 13,983,442 .25 1.00 57,368,014 14,326,914

MONTH 1998-04 4,679,562 18,663,004 4,679,562 4,900,698 .08 .33 57,368,014 14,326,914

MONTH 1998-05 4,871,646 23,534,650 9,551,208 4,679,562 .09 .34 57,368,014 14,326,914

MONTH 1998-06 4,712,412 28,247,062 14,263,620 4,871,646 .08 .33 57,368,014 14,326,914

QUARTER 1998Q3 14,430,832 42,677,894 14,430,832 14,263,620 .25 1.00 57,368,014 14,326,914

MONTH 1998-07 4,844,202 33,091,264 4,844,202 4,712,412 .08 .34 57,368,014 14,326,914

MONTH 1998-08 4,915,358 38,006,622 9,759,560 4,844,202 .09 .34 57,368,014 14,326,914

MONTH 1998-09 4,671,272 42,677,894 14,430,832 4,915,358 .08 .32 57,368,014 14,326,914

QUARTER 1998Q4 14,386,144 57,064,038 14,386,144 14,430,832 .25 1.00 57,368,014 14,326,914

MONTH 1998-10 4,857,150 47,535,044 4,857,150 4,671,272 .09 .34 57,368,014 14,326,914

MONTH 1998-11 4,681,908 52,216,952 9,539,058 4,857,150 .08 .33 57,368,014 14,326,914

MONTH 1998-12 4,847,086 57,064,038 14,386,144 4,681,908 .08 .34 57,368,014 14,326,914

YEAR 1999 57,368,014 57,368,014 57,064,038 1.00 57,368,014 14,326,914

QUARTER 1999Q1 14,159,312 14,159,312 14,159,312 14,386,144 .25 1.00 57,368,014 14,326,914

MONTH 1999-01 4,896,268 4,896,268 4,896,268 4,847,086 .09 .35 57,368,014 14,326,914

MONTH 1999-02 4,455,816 9,352,084 9,352,084 4,896,268 .08 .31 57,368,014 14,326,914

MONTH 1999-03 4,807,228 14,159,312 14,159,312 4,455,816 .08 .34 57,368,014 14,326,914

. . .

. . .

YEAR 2002 57,478,306 57,478,306 57,118,131 1.00 57,368,014 14,326,914

QUARTER 2002Q1 14,218,054 14,218,054 14,218,054 14,409,622 .25 1.00 57,368,014 14,326,914

MONTH 2002-01 4,879,048 4,879,048 4,879,048 4,878,494 .08 .34 57,368,014 14,326,914

MONTH 2002-02 4,435,116 9,314,164 9,314,164 4,879,048 .08 .31 57,368,014 14,326,914

MONTH 2002-03 4,903,890 14,218,054 14,218,054 4,435,116 .09 .34 57,368,014 14,326,914

QUARTER 2002Q2 14,289,346 28,507,400 14,289,346 14,218,054 .25 1.00 57,368,014 14,326,914

MONTH 2002-04 4,679,752 18,897,806 4,679,752 4,903,890 .08 .33 57,368,014 14,326,914

MONTH 2002-05 4,912,014 23,809,820 9,591,766 4,679,752 .09 .34 57,368,014 14,326,914

MONTH 2002-06 4,697,580 28,507,400 14,289,346 4,912,014 .08 .33 57,368,014 14,326,914

QUARTER 2002Q3 14,529,304 43,036,704 14,529,304 14,289,346 .25 1.00 57,368,014 14,326,914

MONTH 2002-07 4,911,188 33,418,588 4,911,188 4,697,580 .09 .34 57,368,014 14,326,914

MONTH 2002-08 4,892,030 38,310,618 9,803,218 4,911,188 .09 .34 57,368,014 14,326,914

MONTH 2002-09 4,726,086 43,036,704 14,529,304 4,892,030 .08 .33 57,368,014 14,326,914

QUARTER 2002Q4 14,441,602 57,478,306 14,441,602 14,529,304 .25 1.00 57,368,014 14,326,914

MONTH 2002-10 4,881,696 47,918,400 4,881,696 4,726,086 .08 .34 57,368,014 14,326,914

MONTH 2002-11 4,670,818 52,589,218 9,552,514 4,881,696 .08 .32 57,368,014 14,326,914

MONTH 2002-12 4,889,088 57,478,306 14,441,602 4,670,818 .09 .34 57,368,014 14,326,914

YEAR 2003 1,732,884 1,732,884 57,478,306 1.00 57,368,014 14,326,914

QUARTER 2003Q1 1,732,884 1,732,884 1,732,884 14,441,602 1.00 1.00 57,368,014 14,326,914

MONTH 2003-01 1,732,884 1,732,884 1,732,884 4,889,088 1.00 1.00 57,368,014 14,326,914

89 rows selected.


Under the Covers: Hidden Yet Elegant Complexity!Execution Plan

----------------------------------------------------------


--------------------------------------------------------------------------------------------------------------------------------------


--------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 53 | | 3 (34)| 00:00:01 |

| 1 | SORT ORDER BY | | 1 | 53 | | 3 (34)| 00:00:01 |

| 2 | VIEW | AV_SS_BASIC | 1 | 53 | | 2 (0)| 00:00:01 |

| 3 | TEMP TABLE TRANSFORMATION | | | | | | |

| 4 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D669E_438366 | | | | | |

| 5 | WINDOW SORT | | 604 | 76104 | | 57 (18)| 00:00:01 |

| 6 | VIEW | | 604 | 76104 | | 56 (17)| 00:00:01 |

| 7 | UNION-ALL | | | | | | |

| 8 | HASH GROUP BY | | 201 | 6432 | | 20 (20)| 00:00:01 |


| 10 | HASH GROUP BY | | 201 | 2412 | | 19 (16)| 00:00:01 |


| 12 | HASH GROUP BY | | 201 | 804 | | 17 (6)| 00:00:01 |

| 13 | TABLE ACCESS INMEMORY FULL | DATE_DIM | 201 | 804 | | 16 (0)| 00:00:01 |

| 14 | VIEW | | 1 | 126 | | 1 (0)| 00:00:01 |

|* 15 | COUNT STOPKEY | | | | | | |

| 16 | TABLE ACCESS INMEMORY FULL | DATE_DIM | 1 | | | 1 (0)| 00:00:01 |

| 17 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D669F_438366 | | | | | |

|* 18 | VIEW | | 604 | 36844 | | 5 (0)| 00:00:01 |

| 19 | TABLE ACCESS FULL | SYS_TEMP_0FD9D669E_438366 | 604 | 76104 | | 5 (0)| 00:00:01 |

|* 20 | HASH JOIN | | 1 | 139 | | 8161 (79)| 00:00:01 |

| 21 | VIEW | | 37 | 2072 | | 8156 (79)| 00:00:01 |

| 22 | HASH GROUP BY | | 37 | 3182 | | 8156 (79)| 00:00:01 |

| 23 | NESTED LOOPS | | 37 | 3182 | | 8155 (79)| 00:00:01 |

. . .

. . .

| 72 | HASH GROUP BY | | 1 | 77 | | 8047 (80)| 00:00:01 |

|* 73 | HASH JOIN | | 159M| 11G| 4216K| 2314 (29)| 00:00:01 |

| 74 | TABLE ACCESS FULL | SYS_TEMP_0FD9D669D_438366 | 73048 | 3352K| | 129 (1)| 00:00:01 |

| 75 | VIEW | VW_VT_CA4EAB06 | 218K| 6398K| | 1080 (18)| 00:00:01 |

| 76 | VECTOR GROUP BY | | 218K| 2772K| | 1080 (18)| 00:00:01 |

| 77 | HASH GROUP BY | | 218K| 2772K| | 1080 (18)| 00:00:01 |

| 78 | KEY VECTOR USE | :KV0000 | 5760K| 71M| | 1079 (18)| 00:00:01 |

| 79 | PARTITION RANGE ALL | | 5760K| 49M| | 1078 (18)| 00:00:01 |

|* 80 | TABLE ACCESS INMEMORY FULL | STORE_SALES | 5760K| 49M| | 1078 (18)| 00:00:01 |

|* 81 | VIEW | | 37 | 1591 | | 3 (0)| 00:00:01 |

| 82 | TABLE ACCESS FULL | SYS_TEMP_0FD9D669F_438366 | 604 | 33824 | | 3 (0)| 00:00:01 |

|* 83 | VIEW | | 604 | 50132 | | 5 (0)| 00:00:01 |

| 84 | TABLE ACCESS FULL | SYS_TEMP_0FD9D669E_438366 | 604 | 76104 | | 5 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

15 - filter(ROWNUM=1)

18 - filter("H"."AVAD_DATES#AVHY_DATES#LEVEL_NAME"='ALL' OR "H"."AVAD_DATES#AVHY_DATES#LEVEL_NAME"='MONTH' OR

"H"."AVAD_DATES#AVHY_DATES#LEVEL_NAME"='QUARTER' OR "H"."AVAD_DATES#AVHY_DATES#LEVEL_NAME"='YEAR')

. . .

83 - filter("TGT$AVAD_DATES#AVHY_DATES#FULL$"."AVAD_DATES#AVHY_DATES#LEVEL_NAME"='ALL' OR

"TGT$AVAD_DATES#AVHY_DATES#FULL$"."AVAD_DATES#AVHY_DATES#LEVEL_NAME"='MONTH' OR

"TGT$AVAD_DATES#AVHY_DATES#FULL$"."AVAD_DATES#AVHY_DATES#LEVEL_NAME"='QUARTER' OR

"TGT$AVAD_DATES#AVHY_DATES#FULL$"."AVAD_DATES#AVHY_DATES#LEVEL_NAME"='YEAR')

Note

-----

- vector transformation used for this statement

DBIM: Coming in Oracle 18c• In-Memory External Tables

• In-Memory …

• <others?>


ANY OF THE FOLLOWING FEATURES DESCRIBED –EVEN THOSE THAT MAY EVENTUALLY COME TO

FRUITION – ARE ENTIRELY THE OPINIONS OF THE PRESENTER. THEY MAY OR MAY NOT REPRESENT ANY ACTUAL FEATURES OF THE NEXT RELEASE OF

THE ORACLE DATABASE. ANY ACCIDENTAL ASSOCIATION OF THE EIGHTEENTH CARDINAL

NUMBER AND THE THIRD LETTER OF THE LATIN ALPHABET IS PURELY COINCIDENTAL. DUE TO ITS CONTENT THIS PRESENTATION SHOULD NOT BE

VIEWED BY ANYONE

Before We Proceed: A Disclaimer!


DBIM: What’s Coming in 18c?

• In-Memory Area Improvements• Automatic In-Memory Management

• Extreme capacity for NVRAM memory

• In-Memory Dynamic Scans

• In-Memory Optimized Arithmetic

• In-Memory External Tables • In-Memory Virtual Columns

• Partitioned External Tables

• Polymorphic Table Functions

39


In-Memory External Tables

• In-Memory Area Improvements• Automatic In-Memory Management

• Extreme capacity for NVRAM memory

• In-Memory Dynamic Scans

• In-Memory Optimized Arithmetic

40


In-Memory External Tables

• In-Memory External Tables• External tables are now accessible completely in memory

• Removes limitations of any storage tier

• <finish>

41


Analytic Views Enhancements

• Partitioned External Tables

42


Polymorphic Table Functions

• Polymorphic Table Functions• In-Memory . . .

43

Database In-Memory Features: Conclusions


Conclusion: DBIM & Analytic Functions - A Powerful Partnership!

Combining 12cR2 DBIM features with Analytic Functions offer some significant opportunities to improve application performance:

• In-Memory Aggregation leverages SIMD processing for faster processing of aggregation functionality

• In-Memory Expressions store the most frequently-used deterministic expressions for dramatically faster access

• In-Memory Join Groups enable faster join processing when a Bloom filter is ineffective or impossible to leverage

• ILM ADO Policies transform the IMCS into a true Tier 0 storage level

• New APPROX_ functions offer performance improvement possibilities (if used wisely!)

• Analytic Views make short work of multi-dimensional queries with impressive performance capabilities


If you have any questions or comments, feel free to:

➢E-mail me at [email protected]

➢Follow my blog (Generally, It Depends): http://jimczuprynski.wordpress.com

➢Follow me on Twitter (@JimTheWhyGuy)

➢Connect with me on LinkedIn (Jim Czuprynski)

Thank You For Your Kind Attention!

mailto:[email protected]

http://jimczuprynski.wordpress.com/

saving your database from alzheimer’s: analytic ...€¦ · saving your database from...

Documents