Wednesday, November 28, 2018

Data Warehouse Design: Engineered Systems Considerations

This post is part of a series that discusses some common issues in data warehouses.

On an engineered system, a key feature is that Bloom filters are pushed to storage cells during smart scan,  Additionally, a Bloom filter computed from a join column of a one table can be used against another table.  Storage index can skip I/O against the large fact table based on a Bloom filter calculated from a small dimension table (see Tanel Poder's Blog: Combining Bloom Filter Offloading and Storage Indexes on Exadata)
This shifts the balance away from Star Transformation, so you are far less likely to want to add bitmap indexes.
I repeated the same test from my previous blog post (US by State for sales in 1999), on exactly the same data, on Oracle 12.1 on Exadata. The cost of the Star Transformation is lower at 575, because db_file_multi_block_read_count=128, and this makes sense on an Exadata because you can do 1Mb reads from disk.
Plan hash value: 472227253

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                             |      1 |        |       |   575 (100)|          |       |       |     45 |00:00:00.37 |   98059 |     98 |     98 |       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION           |                             |      1 |        |       |            |          |       |       |     45 |00:00:00.37 |   98059 |     98 |     98 |       |       |          |
|   2 |   LOAD AS SELECT                     |                             |      1 |        |       |            |          |       |       |      0 |00:00:00.03 |    1647 |      0 |     98 |  1040K|  1040K|          |
|   3 |    HASH JOIN                         |                             |      1 |   2921 |   111K|    23   (5)| 00:00:01 |       |       |  18520 |00:00:00.02 |    1546 |      0 |      0 |  2408K|  2408K|  281K (0)|
|   4 |     JOIN FILTER CREATE               | :BF0000                     |      1 |      1 |    18 |     2   (0)| 00:00:01 |       |       |      1 |00:00:00.01 |       9 |      0 |      0 |       |       |          |
|   5 |      TABLE ACCESS STORAGE FULL       | COUNTRIES                   |      1 |      1 |    18 |     2   (0)| 00:00:01 |       |       |      1 |00:00:00.01 |       9 |      0 |      0 |  1025K|  1025K|          |
|   6 |     JOIN FILTER USE                  | :BF0000                     |      1 |  55500 |  1138K|    21   (5)| 00:00:01 |       |       |  18520 |00:00:00.01 |    1537 |      0 |      0 |       |       |          |
|   7 |      TABLE ACCESS STORAGE FULL       | CUSTOMERS                   |      1 |  55500 |  1138K|    21   (5)| 00:00:01 |       |       |  18520 |00:00:00.01 |    1537 |      0 |      0 |  1025K|  1025K|          |
|   8 |   SORT GROUP BY                      |                             |      1 |   2344 |   100K|   551   (1)| 00:00:01 |       |       |     45 |00:00:00.35 |   96406 |     98 |      0 |  6144 |  6144 | 6144  (0)|
|   9 |    HASH JOIN                         |                             |      1 |   2344 |   100K|   550   (1)| 00:00:01 |       |       |  64818 |00:00:00.32 |   96406 |     98 |      0 |  5827K|  5180K| 7295K (0)|
|  10 |     VIEW                             | VW_ST_C525CEF3              |      1 |   2344 | 42192 |   548   (1)| 00:00:01 |       |       |  64818 |00:00:00.29 |   96300 |     98 |      0 |       |       |          |
|  11 |      NESTED LOOPS                    |                             |      1 |   2344 |   116K|   542   (1)| 00:00:01 |       |       |  64818 |00:00:00.28 |   96300 |     98 |      0 |       |       |          |
|  12 |       PARTITION RANGE SUBQUERY       |                             |      1 |   2344 | 67986 |   313   (1)| 00:00:01 |KEY(SQ)|KEY(SQ)|  64818 |00:00:00.21 |   96041 |     98 |      0 |       |       |          |
|  13 |        BITMAP CONVERSION TO ROWIDS   |                             |      5 |   2344 | 67986 |   313   (1)| 00:00:01 |       |       |  64818 |00:00:00.20 |   95974 |     98 |      0 |       |       |          |
|  14 |         BITMAP AND                   |                             |      5 |        |       |            |          |       |       |      5 |00:00:00.19 |   95974 |     98 |      0 |       |       |          |
|  15 |          BITMAP MERGE                |                             |      5 |        |       |            |          |       |       |      5 |00:00:00.01 |    1921 |      0 |      0 |  1024K|   512K|39936  (0)|
|  16 |           BITMAP KEY ITERATION       |                             |      5 |        |       |            |          |       |       |    365 |00:00:00.01 |    1921 |      0 |      0 |       |       |          |
|  17 |            BUFFER SORT               |                             |      5 |        |       |            |          |       |       |   1820 |00:00:00.01 |      65 |      0 |      0 | 73728 | 73728 |          |
|  18 |             TABLE ACCESS STORAGE FULL| TIMES                       |      1 |    364 |  4368 |     2   (0)| 00:00:01 |       |       |    364 |00:00:00.01 |      65 |      0 |      0 |  1025K|  1025K|          |
|  19 |            BITMAP INDEX RANGE SCAN   | SALES_TIME_BIX              |   1820 |        |       |            |          |KEY(SQ)|KEY(SQ)|    365 |00:00:00.01 |    1856 |      0 |      0 |  1025K|  1025K|          |
|  20 |          BITMAP MERGE                |                             |      5 |        |       |            |          |       |       |      5 |00:00:00.01 |     161 |      0 |      0 |  1024K|   512K|10240  (0)|
|  21 |           BITMAP KEY ITERATION       |                             |      5 |        |       |            |          |       |       |    132 |00:00:00.01 |     161 |      0 |      0 |       |       |          |
|  22 |            BUFFER SORT               |                             |      5 |        |       |            |          |       |       |    130 |00:00:00.01 |      10 |      0 |      0 | 73728 | 73728 |          |
|  23 |             TABLE ACCESS STORAGE FULL| PRODUCTS                    |      1 |     14 |   112 |     2   (0)| 00:00:01 |       |       |     26 |00:00:00.01 |      10 |      0 |      0 |  1025K|  1025K|          |
|  24 |            BITMAP INDEX RANGE SCAN   | SALES_PROD_BIX              |    130 |        |       |            |          |KEY(SQ)|KEY(SQ)|    132 |00:00:00.01 |     151 |      0 |      0 |  1025K|  1025K|          |
|  25 |          BITMAP MERGE                |                             |      5 |        |       |            |          |       |       |      5 |00:00:00.19 |   93892 |     98 |      0 |  1024K|   512K|  310K (0)|
|  26 |           BITMAP KEY ITERATION       |                             |      5 |        |       |            |          |       |       |   6504 |00:00:00.18 |   93892 |     98 |      0 |       |       |          |
|  27 |            BUFFER SORT               |                             |      5 |        |       |            |          |       |       |  92600 |00:00:00.03 |     106 |     98 |      0 |    28M|  2978K|  928K (0)|
|  28 |             TABLE ACCESS STORAGE FULL| SYS_TEMP_0FD9D66B4_75DFDD27 |      1 |   2921 | 14605 |     2   (0)| 00:00:01 |       |       |  18520 |00:00:00.01 |     106 |     98 |      0 |  1025K|  1025K|          |
|  29 |            BITMAP INDEX RANGE SCAN   | SALES_CUST_BIX              |  92600 |        |       |            |          |KEY(SQ)|KEY(SQ)|   6504 |00:00:00.13 |   93786 |      0 |      0 |  1025K|  1025K|          |
|  30 |       TABLE ACCESS BY USER ROWID     | SALES                       |  64818 |      1 |    22 |   235   (0)| 00:00:01 | ROWID | ROWID |  64818 |00:00:00.05 |     259 |      0 |      0 |       |       |          |
|  31 |     TABLE ACCESS STORAGE FULL        | SYS_TEMP_0FD9D66B4_75DFDD27 |      1 |   2921 | 75946 |     2   (0)| 00:00:01 |       |       |  18520 |00:00:00.01 |     106 |      0 |      0 |  1025K|  1025K|          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The cost of the full scan plan is just 66.  In fact, I could only get the star transformation with an explicit hint to force it (so, the cost-based star transformation is working as expected).
Plan hash value: 874291156

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |           |      1 |        |       |    66 (100)|          |       |       |     45 |00:00:00.14 |    2173 |       |       |          |
|   1 |  SORT GROUP BY                    |           |      1 |    103 |  8343 |    66  (25)| 00:00:01 |       |       |     45 |00:00:00.14 |    2173 |  6144 |  6144 | 6144  (0)|
|   2 |   HASH JOIN                       |           |      1 |   2411 |   190K|    65  (24)| 00:00:01 |       |       |  64818 |00:00:00.11 |    2173 |  4038K|  4038K| 1436K (0)|
|   3 |    PART JOIN FILTER CREATE        | :BF0000   |      1 |    364 |  4368 |     2   (0)| 00:00:01 |       |       |    364 |00:00:00.01 |      65 |       |       |          |
|   4 |     TABLE ACCESS STORAGE FULL     | TIMES     |      1 |    364 |  4368 |     2   (0)| 00:00:01 |       |       |    364 |00:00:00.01 |      65 |  1025K|  1025K|          |
|   5 |    HASH JOIN                      |           |      1 |   9672 |   651K|    63  (24)| 00:00:01 |       |       |  79240 |00:00:00.10 |    2108 |  5219K|  5219K| 1098K (0)|
|   6 |     JOIN FILTER CREATE            | :BF0001   |      1 |     14 |   112 |     2   (0)| 00:00:01 |       |       |     26 |00:00:00.01 |      10 |       |       |          |
|   7 |      TABLE ACCESS STORAGE FULL    | PRODUCTS  |      1 |     14 |   112 |     2   (0)| 00:00:01 |       |       |     26 |00:00:00.01 |      10 |  1025K|  1025K|          |
|   8 |     HASH JOIN                     |           |      1 |  48360 |  2880K|    60  (24)| 00:00:01 |       |       |  79240 |00:00:00.09 |    2098 |  3241K|  3241K| 1869K (0)|
|   9 |      JOIN FILTER CREATE           | :BF0002   |      1 |   2921 |   111K|    23   (5)| 00:00:01 |       |       |  18520 |00:00:00.02 |    1546 |       |       |          |
|  10 |       HASH JOIN                   |           |      1 |   2921 |   111K|    23   (5)| 00:00:01 |       |       |  18520 |00:00:00.01 |    1546 |  2507K|  2507K|  234K (0)|
|  11 |        JOIN FILTER CREATE         | :BF0003   |      1 |      1 |    18 |     2   (0)| 00:00:01 |       |       |      1 |00:00:00.01 |       9 |       |       |          |
|  12 |         TABLE ACCESS STORAGE FULL | COUNTRIES |      1 |      1 |    18 |     2   (0)| 00:00:01 |       |       |      1 |00:00:00.01 |       9 |  1025K|  1025K|          |
|  13 |        JOIN FILTER USE            | :BF0003   |      1 |  55500 |  1138K|    21   (5)| 00:00:01 |       |       |  18520 |00:00:00.01 |    1537 |       |       |          |
|  14 |         TABLE ACCESS STORAGE FULL | CUSTOMERS |      1 |  55500 |  1138K|    21   (5)| 00:00:01 |       |       |  18520 |00:00:00.01 |    1537 |  1025K|  1025K|          |
|  15 |      JOIN FILTER USE              | :BF0001   |      1 |    918K|    19M|    34  (30)| 00:00:01 |       |       |  87209 |00:00:00.06 |     552 |       |       |          |
|  16 |       JOIN FILTER USE             | :BF0002   |      1 |    918K|    19M|    34  (30)| 00:00:01 |       |       |  87209 |00:00:00.06 |     552 |       |       |          |
|  17 |        PARTITION RANGE JOIN-FILTER|           |      1 |    918K|    19M|    34  (30)| 00:00:01 |:BF0000|:BF0000|  87209 |00:00:00.06 |     552 |       |       |          |
|  18 |         TABLE ACCESS STORAGE FULL | SALES     |      5 |    918K|    19M|    34  (30)| 00:00:01 |:BF0000|:BF0000|  87209 |00:00:00.06 |     552 |  1025K|  1025K|          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The balance point between star transformation and full scan Bloom filter is exactly the same, but they all perform about 4x faster.
ISO Country Code
Country Name
Number of Sales in 1999
Number of Sales
Star Transformation
Full Scan-Bloom Filter
A-Time
Buffers
A-Time
Buffers
US
United States of America
2662
526212
.45
98056
.13
2173
DE
Germany
561
81978
.14
45538
.07
2173
JP
Japan
281
60183
.19
7118
.29
2068
GB
United Kingdom
391
58638
.41
4238
.25
2068
IT
Italy
257
42570
.12
43473
.07
2173
AU
Australia
228
33685
.04
8186
.06
2173
FR
France
161
33078
.07
23434
.07
2173
SG
Singapore
80
25253
.04
6984
.06
2173
CA
Canada
90
22858
.05
14156
.06
2173
ES
Spain
85
17136
.05
14319
.06
2173
DK
Denmark
89
16651
.03
5195
.06
2173
AR
Argentina
3
202
.02
5975
.06
2173
BR
Brazil
9
180
.03
7957
.05
2173
TR
Turkey
1
168
.02
4200
.05
2173
CN
China
4
19
.03
7342
.06
2173
PL
Poland
2
18
.03
7342
.06
2173
SA
Saudi Arabia
0
7
.02
4093
.05
2173

 A full-scan is far more likely to deliver acceptable performance on an Engineered system, so why would you go to the trouble of bitmap indexing to achieve star transformation?  You probably wouldn't.
Posted by at
Labels: ,

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )