Oracle Database In-Memory and Hash Keys

Hash keys are often used as primary keys in Data Vault environments. When loading dimensional data marts from a Data Vault schema, it seems to be obvious to use them as dimension keys, too. At least in combination with Oracle Database In-Memory, this is not a good idea.

In a data warehouse project of one of my customers, we had some strange performance issues with Oracle Database In-Memory. The response times of queries on star schemas were not as good as expected, in some cases even slower than without the In-Memory option. We also realized that In-Memory Aggregation (aka Vector Transformation) was never used, even for very big fact tables. When I forced the optimizer to use a Vector Transformation with a vector_transform hint, the costs and the response time increased dramatically. First, I thought this has to do with the Exadata platform: Because the Smart Scans on Exadata are already fast enough, the optimizer decides not to use a Vector Transformation. But actually, there was another reason for the performance problems: The datatypes (or actually the size) of the join columns were responsible for the poor performance.

The data warehouse was implemented with a Data Vault 2.0 modeling approach. In Data Vault 2.0, it is common to use hash keys for the primary key columns of the Hub and Link tables. The star schemas were built on top of the Hubs, Links and Satellites of the Data Vault schema. So, it was obvious to use the same keys as primary key columns for the dimensions and foreign key columns for the fact tables. In the mentioned project, SHA-1 hash values were used (see blog post How to Build Hash Keys in Oracle), and they were stored in CHAR(40) columns. For the star schemas, all key columns on the dimension and fact tables were created as CHAR(40) columns, too.

To simulate this situation, I prepared a demo script for the Trivadis Performance Days in September. I neither needed an Exadata platform nor a huge data warehouse with Data Vault to show the behavior of Oracle Database In-Memory. Instead, I explained it with the star schema tables of Oracle’s “Sales History” (SH) demo schema. Because not all of you attended the Performance Days (you missed something!), here an explanation of this demo use case.

Preparation Steps

Instead of loading the star schema from a Data Vault schema, I just create a copy of the dimension and fact tables of the SH schema. For the three dimension tables CUSTOMERS, PRODUCTS and TIMES, I create an additional column which contains the hash value of the original primary key (I know, that’s not the idea of a hash key in Data Vault, its just to simulate the situation). For the fact table SALES, a hash value is calculated for each of the dimension keys.

 

CREATE TABLE customers_hash INMEMORY

AS

SELECT CAST(STANDARD_HASH(cust_id) AS CHAR(40)) AS cust_hash

     , c.* FROM customers c;

 

CREATE TABLE products_hash INMEMORY

AS

SELECT CAST(STANDARD_HASH(prod_id) AS CHAR(40)) AS prod_hash

     , p.* FROM products p;

 

CREATE TABLE times_hash INMEMORY

AS

SELECT CAST(STANDARD_HASH(time_id) AS CHAR(40)) AS time_hash

     , t.* FROM times t;

 

CREATE TABLE sales_hash INMEMORY

AS

SELECT CAST(STANDARD_HASH(prod_id) AS CHAR(40)) AS prod_hash

     , CAST(STANDARD_HASH(cust_id) AS CHAR(40)) AS cust_hash

     , CAST(STANDARD_HASH(time_id) AS CHAR(40)) AS time_hash

     , prod_id

     , cust_id

     , time_id

     , quantity_sold

     , amount_sold

  FROM sales;

 

Before running some queries on the copied star schema, I change some configuration settings. The first  two parameters are set to avoid “surprises” with the adaptive features of the optimizer in Oracle 12.2. The undocumented parameter “_optimizer_vector_min_fact_rows” is used to reduce the threshold for a Vector Transformation. By default, Vector Transformations are only considered by the optimizer if a fact table has more than 10 million rows. The SALES table has less than 1 million rows. Finally, the shared pool is flushed to initialize the demo case.


ALTER SESSION SET optimizer_adaptive_plans = FALSE;

ALTER SESSION SET optimizer_adaptive_statistics = FALSE;

ALTER SESSION SET “_optimizer_vector_min_fact_rows” = 100000;

ALTER SYTEM FLUSH SHARD_POOL;

 

 

Join on Numeric Keys

The following SQL statement is a typical query on a star schema that joins the fact table with three dimension tables and applies a filter on each of the dimensions. In this case, the numeric surrogate keys are used as join columns.

 

SELECT /* JOIN NUMERIC KEYS */

       c.cust_credit_limit

     , p.prod_name

     , SUM(s.amount_sold)

  FROM sales_hash s

  JOIN products_hash p  ON (p.prod_id = s.prod_id)

  JOIN customers_hash c ON (c.cust_id = s.cust_id)

  JOIN times_hash t ON (t.time_id = s.time_id)

 WHERE p.prod_category = ‘Photo’

   AND c.cust_credit_limit < 50000

   AND t.calendar_year in (2000, 2001)

GROUP BY c.cust_credit_limit, p.prod_name

 

The estimated costs of the first execution are quite high (5860). This is because the tables are not yet populated in the In-Memory column store (IMCS). Note that the optimizer decides to use a Vector Transformation for this query.

                                                                                                                                                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------                                                  
| Id  | Operation                                | Name                      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------                                                  
|   0 | SELECT STATEMENT                         |                           |      1 |        |  5860 (100)|     80 |00:00:00.53 |   21350 |  21292 |                                                  
|   1 |  TEMP TABLE TRANSFORMATION               |                           |      1 |        |            |     80 |00:00:00.53 |   21350 |  21292 |                                                  
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6618_65F13C |      1 |        |            |      0 |00:00:00.01 |      12 |      3 |                                                  
|   3 |    HASH GROUP BY                         |                           |      1 |     14 |     4  (25)|     10 |00:00:00.01 |      11 |      3 |                                                  
|   4 |     KEY VECTOR CREATE BUFFERED           | :KV0000                   |      1 |     14 |     3   (0)|     10 |00:00:00.01 |      11 |      3 |                                                  
|*  5 |      TABLE ACCESS INMEMORY FULL          | PRODUCTS_HASH             |      1 |     14 |     3   (0)|     10 |00:00:00.01 |      11 |      3 |                                                  
|   6 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6619_65F13C |      1 |        |            |      0 |00:00:00.04 |    1860 |   1840 |                                                  
|   7 |    HASH GROUP BY                         |                           |      1 |      8 |   513   (1)|      8 |00:00:00.04 |    1860 |   1840 |                                                  
|   8 |     KEY VECTOR CREATE BUFFERED           | :KV0001                   |      1 |      8 |   511   (1)|      8 |00:00:00.04 |    1860 |   1840 |                                                  
|*  9 |      TABLE ACCESS INMEMORY FULL          | CUSTOMERS_HASH            |      1 |  55500 |   511   (1)|  55500 |00:00:00.03 |    1860 |   1840 |                                                  
|  10 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D661A_65F13C |      1 |        |            |      0 |00:00:00.01 |      75 |     63 |                                                  
|  11 |    HASH GROUP BY                         |                           |      1 |      1 |    22   (5)|      1 |00:00:00.01 |      75 |     63 |                                                  
|  12 |     KEY VECTOR CREATE BUFFERED           | :KV0002                   |      1 |      1 |    21   (0)|      1 |00:00:00.01 |      75 |     63 |                                                  
|* 13 |      TABLE ACCESS INMEMORY FULL          | TIMES_HASH                |      1 |    730 |    21   (0)|    731 |00:00:00.01 |      75 |     63 |                                                  
|  14 |   HASH GROUP BY                          |                           |      1 |     56 |  5321   (1)|     80 |00:00:00.49 |   19402 |  19386 |                                                  
|* 15 |    HASH JOIN                             |                           |      1 |     56 |  5320   (1)|     80 |00:00:00.49 |   19402 |  19386 |                                                  
|* 16 |     HASH JOIN                            |                           |      1 |     56 |  5318   (1)|     80 |00:00:00.48 |   19402 |  19386 |                                                  
|  17 |      MERGE JOIN CARTESIAN                |                           |      1 |      8 |     4   (0)|      8 |00:00:00.01 |       0 |      0 |                                                  
|  18 |       TABLE ACCESS FULL                  | SYS_TEMP_0FD9D661A_65F13C |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       0 |      0 |                                                  
|  19 |       BUFFER SORT                        |                           |      1 |      8 |     2   (0)|      8 |00:00:00.01 |       0 |      0 |                                                  
|  20 |        TABLE ACCESS FULL                 | SYS_TEMP_0FD9D6619_65F13C |      1 |      8 |     2   (0)|      8 |00:00:00.01 |       0 |      0 |                                                  
|  21 |      VIEW                                | VW_VT_197303A2            |      1 |     56 |  5314   (1)|     80 |00:00:00.48 |   19402 |  19386 |                                                  
|  22 |       VECTOR GROUP BY                    |                           |      1 |     56 |  5314   (1)|     80 |00:00:00.48 |   19402 |  19386 |                                                  
|  23 |        HASH GROUP BY                     |                           |        |     56 |  5314   (1)|        |            |         |        |                                                  
|  24 |         KEY VECTOR USE                   | :KV0001                   |      1 |  89332 |  5314   (1)|  62832 |00:00:00.47 |   19402 |  19386 |                                                  
|  25 |          KEY VECTOR USE                  | :KV0002                   |      1 |  89332 |  5314   (1)|  62832 |00:00:00.45 |   19402 |  19386 |                                                  
|  26 |           KEY VECTOR USE                 | :KV0000                   |      1 |    178K|  5314   (1)|  62832 |00:00:00.44 |   19402 |  19386 |                                                  
|* 27 |            TABLE ACCESS INMEMORY FULL    | SALES_HASH                |      1 |    918K|  5314   (1)|  62832 |00:00:00.43 |   19402 |  19386 |                                                  
|  28 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D6618_65F13C |      1 |     14 |     2   (0)|     10 |00:00:00.01 |       0 |      0 |                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------                                                  

                                                                                                                                                                                                       
Predicate Information (identified by operation id):                                                                                                                                                    
---------------------------------------------------                                                                                                                                                    
                                                                                                                                                                                                       
   5 - inmemory("P"."PROD_CATEGORY"='Photo')                                                                                                                                                            
      filter("P"."PROD_CATEGORY"='Photo')                                                                                                                                                              
  9 - inmemory("C"."CUST_CREDIT_LIMIT"<50000)                                                                                                                                                          
      filter("C"."CUST_CREDIT_LIMIT"<50000)                                                                                                                                                            
 13 - inmemory(("T"."CALENDAR_YEAR"=2000 OR "T"."CALENDAR_YEAR"=2001))                                                                                                                                
       filter(("T"."CALENDAR_YEAR"=2000 OR "T"."CALENDAR_YEAR"=2001))                                                                                                                                  
  15 - access("ITEM_14"=INTERNAL_FUNCTION("C0") AND "ITEM_15"="C2")                                                                                                                                    
  16 - access("ITEM_10"=INTERNAL_FUNCTION("C0") AND "ITEM_11"="C2" AND "ITEM_12"=INTERNAL_FUNCTION("C0") AND "ITEM_13"="C2")                                                                            
 27 - inmemory((SYS_OP_KEY_VECTOR_FILTER("S"."PROD_ID",:KV0000) AND SYS_OP_KEY_VECTOR_FILTER("S"."TIME_ID",:KV0002) AND                                                                                
             SYS_OP_KEY_VECTOR_FILTER("S"."CUST_ID",:KV0001)))                                                                                                                                        
       filter((SYS_OP_KEY_VECTOR_FILTER("S"."PROD_ID",:KV0000) AND SYS_OP_KEY_VECTOR_FILTER("S"."TIME_ID",:KV0002) AND                                                                                  
             SYS_OP_KEY_VECTOR_FILTER("S"."CUST_ID",:KV0001)))                                                                                                                                        
                                                                                                                                                                                                       
Note                                                                                                                                                                                                    
-----                                                                                                                                                                                                   
   - vector transformation used for this statement                                                                                                                                                      
                                                                                                                                                                                                        

I execute the statement a second and a third time. The execution plan is still the same, but the costs are now lower (755) because the data of all tables is available in the IMCS.

                                                                                                                                                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------                                                           
| Id  | Operation                                | Name                      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |                                                           
---------------------------------------------------------------------------------------------------------------------------------------------                                                           
|   0 | SELECT STATEMENT                         |                           |      1 |        |   755 (100)|     80 |00:00:00.10 |    1960 |                                                           
|   1 |  TEMP TABLE TRANSFORMATION               |                           |      1 |        |            |     80 |00:00:00.10 |    1960 |                                                           
 
...                                                                                                                                                                                                        
 
Note                                                                                                                                                                                                    
-----                                                                                                                                                                                                   
   - statistics feedback used for this statement                                                                                                                                                        
   - vector transformation used for this statement                                                                                                                                                      
                                                                                                                                                                                                        

The following query on the shared pool shows the costs of the first and the following executions:

 

SELECT sql_id, child_number, executions, optimizer_cost, elapsed_time

     , SUBSTR(sql_text, 8, 23)

  FROM v$sql

 WHERE sql_text LIKE ‘%/* JOIN %’ AND sql_text NOT LIKE ‘%v$sql%’

ORDER BY last_load_time;

 

SQL_ID        CHILD_NUMBER EXECUTIONS OPTIMIZER_COST ELAPSED_TIME SUBSTR(SQL_TEXT,8,23)  

————- ———— ———- ————– ———— ———————–

9jbp7kkprg88d            0          1           5860       616555 /* JOIN NUMERIC KEYS */

9jbp7kkprg88d            1          2            755       226056 /* JOIN NUMERIC KEYS */

 

 

Join on Hash Keys

Now, I repeat the same demo case again, but this time with the hash keys as join columns. Before running the following query, I recreated the tables again, and flushed the shared pool to avoid any side effects from the previous demo case. The following SQL statement is almost identical to the previous one, except for the join columns.

 

SELECT /* JOIN HASH KEYS */

       c.cust_credit_limit

     , p.prod_name

     , SUM(s.amount_sold)

  FROM sales_hash s

  JOIN products_hash p  ON (p.prod_hash = s.prod_hash)

  JOIN customers_hash c ON (c.cust_hash = s.cust_hash)

  JOIN times_hash t ON (t.time_hash = s.time_hash)

 WHERE p.prod_category = ‘Photo’

   AND c.cust_credit_limit < 50000

   AND t.calendar_year in (2000, 2001)

GROUP BY c.cust_credit_limit, p.prod_name

 

Even the execution plan looks almost the same, except for the costs that are a bit higher for the first execution (7164 instead of 5860). 

                                                                                                                                                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------                                                  
| Id  | Operation                                | Name                      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------                                                  
|   0 | SELECT STATEMENT                         |                           |      1 |        |  7164 (100)|     80 |00:00:00.51 |   21470 |  21292 |                                                  
|   1 |  TEMP TABLE TRANSFORMATION               |                           |      1 |        |            |     80 |00:00:00.51 |   21470 |  21292 |                                                  
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D664B_65F13C |      1 |        |            |      0 |00:00:00.01 |      12 |      3 |                                                  
|   3 |    HASH GROUP BY                         |                           |      1 |     14 |   104  (98)|     10 |00:00:00.01 |      11 |      3 |                                                  
|   4 |     KEY VECTOR CREATE BUFFERED           | :KV0000                   |      1 |     14 |   103  (98)|     10 |00:00:00.01 |      11 |      3 |                                                  
|*  5 |      TABLE ACCESS INMEMORY FULL          | PRODUCTS_HASH             |      1 |     14 |     3   (0)|     10 |00:00:00.01 |      11 |      3 |                                                  
|   6 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D664C_65F13C |      1 |        |            |      0 |00:00:00.09 |    1860 |   1840 |                                                  
|   7 |    HASH GROUP BY                         |                           |      1 |      8 |   726  (30)|      8 |00:00:00.09 |    1860 |   1840 |                                                  
|   8 |     KEY VECTOR CREATE BUFFERED           | :KV0001                   |      1 |      8 |   724  (30)|      8 |00:00:00.09 |    1860 |   1840 |                                                  
|*  9 |      TABLE ACCESS INMEMORY FULL          | CUSTOMERS_HASH            |      1 |  55500 |   511   (1)|  55500 |00:00:00.03 |    1860 |   1840 |                                                  
|  10 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D664D_65F13C |      1 |        |            |      0 |00:00:00.01 |     195 |     63 |                                                  
|  11 |    HASH GROUP BY                         |                           |      1 |      1 |   123  (83)|      1 |00:00:00.01 |     195 |     63 |                                                  
|  12 |     KEY VECTOR CREATE BUFFERED           | :KV0002                   |      1 |      1 |   122  (83)|      1 |00:00:00.01 |      75 |     63 |                                                  
|* 13 |      TABLE ACCESS INMEMORY FULL          | TIMES_HASH                |      1 |    730 |    21   (0)|    731 |00:00:00.01 |      75 |     63 |                                                  
|  14 |   HASH GROUP BY                          |                           |      1 |     56 |  6211  (15)|     80 |00:00:00.40 |   19402 |  19386 |                                                  
|* 15 |    HASH JOIN                             |                           |      1 |     56 |  6210  (15)|     80 |00:00:00.40 |   19402 |  19386 |                                                  
|  16 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D664B_65F13C |      1 |     14 |     2   (0)|     10 |00:00:00.01 |       0 |      0 |                                                  
|* 17 |     HASH JOIN                            |                           |      1 |     56 |  6208  (15)|     80 |00:00:00.40 |   19402 |  19386 |                                                  
|  18 |      MERGE JOIN CARTESIAN                |                           |      1 |      8 |     4   (0)|      8 |00:00:00.01 |       0 |      0 |                                                  
|  19 |       TABLE ACCESS FULL                  | SYS_TEMP_0FD9D664D_65F13C |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       0 |      0 |                                                  
|  20 |       BUFFER SORT                        |                           |      1 |      8 |     2   (0)|      8 |00:00:00.01 |       0 |      0 |                                                  
|  21 |        TABLE ACCESS FULL                 | SYS_TEMP_0FD9D664C_65F13C |      1 |      8 |     2   (0)|      8 |00:00:00.01 |       0 |      0 |                                                  
|  22 |      VIEW                                | VW_VT_197303A2            |      1 |     56 |  6204  (15)|     80 |00:00:00.40 |   19402 |  19386 |                                                  
|  23 |       VECTOR GROUP BY                    |                           |      1 |     56 |  6204  (15)|     80 |00:00:00.40 |   19402 |  19386 |                                                  
|  24 |        HASH GROUP BY                     |                           |        |     56 |  6204  (15)|        |            |         |        |                                                  
|  25 |         KEY VECTOR USE                   | :KV0001                   |      1 |  89332 |  6180  (15)|  62832 |00:00:00.39 |   19402 |  19386 |                                                  
|  26 |          KEY VECTOR USE                  | :KV0002                   |      1 |  89332 |  5891  (10)|    422K|00:00:00.32 |   19402 |  19386 |                                                  
|  27 |           KEY VECTOR USE                 | :KV0000                   |      1 |    178K|  5602   (6)|    422K|00:00:00.26 |   19402 |  19386 |                                                  
|* 28 |            TABLE ACCESS INMEMORY FULL    | SALES_HASH                |      1 |    918K|  5314   (1)|    422K|00:00:00.20 |   19402 |  19386 |                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------                                                  
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   5 - inmemory("P"."PROD_CATEGORY"='Photo')                                                                                                                                                            
       filter("P"."PROD_CATEGORY"='Photo')                                                                                                                                                              
   9 - inmemory("C"."CUST_CREDIT_LIMIT"<50000)                                                                                                                                                          
       filter("C"."CUST_CREDIT_LIMIT"<50000)                                                                                                                                                            
  13 - inmemory(("T"."CALENDAR_YEAR"=2000 OR "T"."CALENDAR_YEAR"=2001))                                                                                                                                 
       filter(("T"."CALENDAR_YEAR"=2000 OR "T"."CALENDAR_YEAR"=2001))                                                                                                                                   
  15 - access("ITEM_14"=INTERNAL_FUNCTION("C0") AND "ITEM_15"="C2")                                                                                                                                     
  17 - access("ITEM_10"=INTERNAL_FUNCTION("C0") AND "ITEM_11"="C2" AND "ITEM_12"=INTERNAL_FUNCTION("C0") AND "ITEM_13"="C2")                                                                            
  28 - inmemory((SYS_OP_KEY_VECTOR_FILTER("S"."PROD_HASH",:KV0000) AND SYS_OP_KEY_VECTOR_FILTER("S"."TIME_HASH",:KV0002) AND                                                                            
              SYS_OP_KEY_VECTOR_FILTER("S"."CUST_HASH",:KV0001)))                                                                                                                                       
       filter((SYS_OP_KEY_VECTOR_FILTER("S"."PROD_HASH",:KV0000) AND SYS_OP_KEY_VECTOR_FILTER("S"."TIME_HASH",:KV0002) AND                                                                              
              SYS_OP_KEY_VECTOR_FILTER("S"."CUST_HASH",:KV0001)))                                                                                                                                       
                                                                                                                                                                                                        
Note                                                                                                                                                                                                    
-----                                                                                                                                                                                                   
   - vector transformation used for this statement                                                                                                                                                      
                                                                                                                                                                                                        

Again, I execute the statement a second and a third time to see the impact on the costs. But in this case, we see a surprise: No Vector Transformation is used anymore. Instead, the optimizer decides to use an In-Memory join with bloom filters (:BFxxxx in the execution plan). The costs are similar to those in the second/third execution of the previous demo case. But the execution plan changed.

                                                                                                                                                                                                        
-----------------------------------------------------------------------------------------------------------------------------                                                                           
| Id  | Operation                           | Name           | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |                                                                           
-----------------------------------------------------------------------------------------------------------------------------                                                                           
|   0 | SELECT STATEMENT                    |                |      1 |        |   769 (100)|     80 |00:00:00.21 |    1967 |                                                                           
|   1 |  HASH GROUP BY                      |                |      1 |     16 |   769   (5)|     80 |00:00:00.21 |    1967 |                                                                           
|*  2 |   HASH JOIN                         |                |      1 |     16 |   768   (5)|  46895 |00:00:00.20 |    1967 |                                                                           
|   3 |    JOIN FILTER CREATE               | :BF0000        |      1 |     16 |   257  (15)|  46895 |00:00:00.12 |     104 |                                                                           
|*  4 |     HASH JOIN                       |                |      1 |     16 |   257  (15)|  46895 |00:00:00.12 |     104 |                                                                           
|   5 |      JOIN FILTER CREATE             | :BF0001        |      1 |     16 |   236  (16)|  75064 |00:00:00.10 |      26 |                                                                           
|*  6 |       HASH JOIN                     |                |      1 |     16 |   236  (16)|  75064 |00:00:00.09 |      26 |                                                                           
|   7 |        JOIN FILTER CREATE           | :BF0002        |      1 |     14 |     3   (0)|     10 |00:00:00.01 |      11 |                                                                           
|*  8 |         TABLE ACCESS INMEMORY FULL  | PRODUCTS_HASH  |      1 |     14 |     3   (0)|     10 |00:00:00.01 |      11 |                                                                           
|   9 |        VIEW                         | VW_GBC_17      |      1 |     80 |   233  (16)|  75064 |00:00:00.08 |      12 |                                                                           
|  10 |         HASH GROUP BY               |                |      1 |     80 |   233  (16)|  75064 |00:00:00.08 |      12 |                                                                           
|  11 |          JOIN FILTER USE            | :BF0002        |      1 |    918K|   208   (6)|  95509 |00:00:00.01 |      12 |                                                                           
|* 12 |           TABLE ACCESS INMEMORY FULL| SALES_HASH     |      1 |    918K|   208   (6)|  95509 |00:00:00.01 |      12 |                                                                           
|  13 |      JOIN FILTER USE                | :BF0001        |      1 |    730 |    21   (0)|    494 |00:00:00.01 |      75 |                                                                           
|* 14 |       TABLE ACCESS INMEMORY FULL    | TIMES_HASH     |      1 |    730 |    21   (0)|    494 |00:00:00.01 |      75 |                                                                           
|  15 |    JOIN FILTER USE                  | :BF0000        |      1 |  55500 |   511   (1)|   7615 |00:00:00.03 |    1860 |                                                                           
|* 16 |     TABLE ACCESS INMEMORY FULL      | CUSTOMERS_HASH |      1 |  55500 |   511   (1)|   7615 |00:00:00.02 |    1860 |                                                                           
-----------------------------------------------------------------------------------------------------------------------------                                                                           
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   2 - access("C"."CUST_HASH"="ITEM_2")                                                                                                                                                                 
   4 - access("T"."TIME_HASH"="ITEM_3")                                                                                                                                                                 
   6 - access("P"."PROD_HASH"="ITEM_1")                                                                                                                                                                 
   8 - inmemory("P"."PROD_CATEGORY"='Photo')                                                                                                                                                            
       filter("P"."PROD_CATEGORY"='Photo')                                                                                                                                                              
  12 - inmemory(SYS_OP_BLOOM_FILTER(:BF0002,"S"."PROD_HASH"))                                                                                                                                           
       filter(SYS_OP_BLOOM_FILTER(:BF0002,"S"."PROD_HASH"))                                                                                                                                             
  14 - inmemory((INTERNAL_FUNCTION("T"."CALENDAR_YEAR") AND SYS_OP_BLOOM_FILTER(:BF0001,"T"."TIME_HASH")))                                                                                              
       filter((INTERNAL_FUNCTION("T"."CALENDAR_YEAR") AND SYS_OP_BLOOM_FILTER(:BF0001,"T"."TIME_HASH")))                                                                                                
  16 - inmemory(("C"."CUST_CREDIT_LIMIT"<50000 AND SYS_OP_BLOOM_FILTER(:BF0000,"C"."CUST_HASH")))                                                                                                       
       filter(("C"."CUST_CREDIT_LIMIT"<50000 AND SYS_OP_BLOOM_FILTER(:BF0000,"C"."CUST_HASH")))                                                                                                         
 
Note                                                                                                                                                                                                    
-----                                                                                                                                                                                                   
   - statistics feedback used for this statement
                                                                                                                                                                                                        

The next step is to enforce the Vector Transformation with a hint. That’s what I also tried in the real project environment.

 

SELECT /*+ vector_transform */ /* JOIN HASH KEYS */

       c.cust_credit_limit

     , p.prod_name

     , SUM(s.amount_sold)

  FROM sales_hash s

  JOIN products_hash p  ON (p.prod_hash = s.prod_hash)

  JOIN customers_hash c ON (c.cust_hash = s.cust_hash)

  JOIN times_hash t ON (t.time_hash = s.time_hash)

 WHERE p.prod_category = ‘Photo’

   AND c.cust_credit_limit < 50000

   AND t.calendar_year in (2000, 2001)

GROUP BY c.cust_credit_limit, p.prod_name

 

Although this works, the costs are higher than with bloom filters. This is the reasons why the optimizer decided before not to use a Vector Transformation.

                                                                                                                                                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------                                                           
| Id  | Operation                                | Name                      | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |                                                           
---------------------------------------------------------------------------------------------------------------------------------------------                                                           
|   0 | SELECT STATEMENT                         |                           |      1 |        |  1547 (100)|     80 |00:00:00.03 |      49 |                                                           
|   1 |  TEMP TABLE TRANSFORMATION               |                           |      1 |        |            |     80 |00:00:00.03 |      49 |                                                           
 
...                                                                                                                                
                                                                                                                                                                                                        
Note                                                                                                                                                                                                    
-----                                                                                                                                                                                                   
   - vector transformation used for this statement                                                                                                                                                      
                                                                                                                                                                                                        

The query on the shared pool shows that the costs for the Vector Transformation decreased from the first (7164) to the subsequent execution (1547), but are higher than the costs without Vector Transformation (769).

 

SELECT sql_id, child_number, executions, optimizer_cost, elapsed_time

     , SUBSTR(sql_text, 8, 23)

  FROM v$sql

 WHERE sql_text LIKE ‘%/* JOIN %’ AND sql_text NOT LIKE ‘%v$sql%’

ORDER BY last_load_time;

 

SQL_ID        CHILD_NUMBER EXECUTIONS OPTIMIZER_COST ELAPSED_TIME SUBSTR(SQL_TEXT,8,23)  

————- ———— ———- ————– ———— ———————–

94qxq84qu28yx            0          1           7164       781576 /* JOIN HASH KEYS */   

94qxq84qu28yx            1          2            769       390925 /* JOIN HASH KEYS */   

g64vm12jmmtn3            0          1           1547       104970 /*+ vector_transform */

 

 

Conclusion

It seems that Vector Transformations are much more expensive when the join columns are larger than a NUMBER or a DATE column. This is the case when hash keys or any natural keys (e.g. long codes or descriptions) are used as primary keys of the dimension tables.

The solution in the customer project was to replace the hash keys in the star schema with numeric sequence numbers. Of course, this needs some additional lookups when loading the data mart tables, but after this change, Vector Transformation was used as expected, and the performance with Oracle Database In-Memory was much better than before.

Does this mean that hash keys should not be used at all? I know that some people would like to hear that, other people definitely not. From my point of view, it depends (yes, I am a consultant):

  • If you do not use the Oracle Database In-Memory option, there is no big difference in performance between numeric keys and hash keys. So, query performance is not a reason to use one or the other type of keys.
  • If you use In-Memory for the Data Marts, you should use numeric dimension keys for the star schema tables. In the Core or Data Vault layer, either hash key or numeric keys can be used.
  • If you use In-Memory for the Data Vault tables, you should use numeric keys even in the Data Vault layer. A typical case for this is when you use virtual data marts, i.e. views that select data directly from the Data Vault tables.
For me, the experience in the mentioned customer project showed once more that design patterns are not only related to recommendations in books, but depend on the technology that is used for the concrete implementation.
 


6 thoughts on “Oracle Database In-Memory and Hash Keys

  1. Dear Dani,
    thanks for your example. I started to think: many people use hash keys. My first thought was: OK – lets somehow group data naturally. That led to a second one to reduce join cost, namely one of the three good friends of DBIM. DBIMs best friends are – to my experience – the optimizer, parallel execution and partitioning. Independently of the example on 100k records: if the data is (sub) partitioned by hash, then you might again improve the situation.
    If I had one wish: please add a graph showing the elapsed time for the test-cases. Would help a lot to gain the big picture and later on understanding why this is the case.
    Kind regards
    Thomas

    Liked by 2 people

    • Hi Thomas
      Thanks for your reply. Currently, I’m just writing a white paper about partitioning Data Vault tables, and the partitioning strategy you mentioned is one of the options I will describe. Coming (hopefully) soon on this blog 🙂
      A graph wtih elapsed time is not very interesting for this simple demo case, because all examples are (too) fast The elapsed times of the real project I cannot publish. But perhaps I can add a graph about the costs. Thanks for the hint.
      Cheers, Dani

      Liked by 1 person

  2. Sequences are better for star schemas, I usually don’t talk about using Hashes for Information Marts or dimensional models… That said, Hashes should always be stored in Binary format NOT character format (for both Oracle and SQLServer)…

    I can see the issues here, but obviously I’m curious as to a Very Large Table, partitioned and split across MPP nodes. Partitioning over large data sets that are distributed changes the way all queries work. Especially if the data is distributed across multiple machines. MPP responds differently (in my experience), so I’d be curious to see your partitioned results over multiple machines.

    Thank you for your insightful work, this is good to see.

    Cheers,
    Dan Linstedt
    http://danLinstedt.com – CDVP2 and more about hashes here

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s