Friday, November 30, 2018

A Brief Look Inside Oracle's Autonomous Data Warehouse Cloud

This post is part of a series that discusses some common issues in data warehouses.
There is lots of documentation for Autonomous Data Warehouse Cloud (ADWC), in which I found this bold claim:
  •  'Additionally, Autonomous Data Warehouse does not require any tuning. Autonomous Data Warehouse is designed as a ''load and go” service: you start the service, define tables, load data, and then run queries. When you use Autonomous Data Warehouse, no tuning is necessary. You do not need to consider any details about parallelism, partitioning, indexing, or compression. The service automatically configures the database for high-performance queries.' - Getting Started with Autonomous Data Warehouse 
I also found these references were helpful to get me going:
The documentation describes how to import data with the DBMS_CLOUD package or with data pump. You have to upload files onto cloud storage and import from there.  For data pump import, the various object types in the exclude parameter recommended in the documentation are a consequence of the 'load and go' approach.
impdp admin/password@ADWC1_high \       
     directory=data_pump_dir \       
     credential=def_cred_name \       
     dumpfile= https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/adwc/adwc_user/export%u.dmp \
     parallel=16 \
     partition_options=merge \ 
     transform=segment_attributes:n \
     transform=dwcs_cvt_iots:y \
     transform=constraint_use_default_index:y \
     exclude=index, cluster, indextype, materialized_view, materialized_view_log, materialized_zonemap, db_link
Clearly, Oracle expects you to periodically incrementally bulk load data and then work with essentially static data.

What is Autonomous Data Warehouse Cloud? 

It is built on Exadata, so it is an engineered system.  This is what I found I was running on:
  • 12.2.0.1.0 Single Instance RAC
  • SGA: 3.3Gb, PGA: 5Gb, RAM: 708Gb
  • Intel(R) Xeon(R) CPU E7-8867 v4 @ 2.40GHz 84 CPUs/Core/Threads
  • 12 storage servers
There are two pre-installed locked sample schemas
  • The sales history schema SH, that I have been using so far, is a bit different to the one you would build with the scripts on Github. 
  • SSB – Star Schema Benchmark.

How is the Oracle delivered Sales History demo schema in ADWC built? 

There are some significant differences with the delivered Sales History schema:
  • Primary key constraints are DISABLED, NOVALIDATE, RELY.  There is corresponding no unique index because the constraint was created DISABLED. 
    • Thus, there is nothing to prevent you loading duplicate data!
  • Foreign key constraints are present, but also DISABLED, NOVALIDATE, RELY.
    • Foreign keys are also not indexed, but as they are not enforced there is no risk of TM locking.
    • Foreign key join elimination can still happen because QUERY_REWRITE_INTEGRITY=TRUSTED
It is clearly not intended that referential integrity is going to be enforced by the database in ADWC. That is not an unreasonable decision in a data warehouse because you expect the data to be validated in the source system.  However, while it is one thing not to enforce referential integrity, I think that not enforcing uniqueness is courageous!  Although, part of the cost of doing so is maintaining a unique index.
I am surprised that the SALES table is not partitioned.  Partition pruning is a very effective way of eliminating data from a query, but perhaps Oracle is relying on the Bloom filtering and the storage indexes to instead.
PCTFREE is set to 0, so that the data is packed into data blocks without leaving room for updates.  This makes good sense.  In a data warehouse, we don't expect to update the data after it has been loaded, or cater also for OLTP behaviour.  This fits with the 'load and go' statement.
The tables have been Hybrid Columnar Compressed (HCC). This also makes sense, because the data is bulk loaded in direct-path mode, and again we don't expect to be updating the data after it has been loaded.  However, Oracle has used QUERY HIGH HCC.  As early as 2016, Roger MacNichol pointed out in Compression in a well-balanced system that “since HCC was released …, DBAs have almost always used Query High” but as CPU speeds have increased “the time has come for a reassessment of role HCC Query Low”.  I have also written about just such a case.

Building My Own Sales History Demo Schema 

I also built my own sales history demo per the standard public scripts. The difference between the two is quite telling. I found that:
  • I can build indexes, not just primary keys and foreign keys (though I understand that has only recently become possible).
  • I can specify tablespaces and storage 
  • I can do some alter session commands but not others due to PDB security profiles.
    • I can't ALTER SESSION SET star_transformation_enabled = TRUE;
    • However, I can set ALTER SESSION SET optimizer_ignore_hints =FALSE, and then I get star transformation by using the STAR_TRANSFORMATION hint. 
  • I can also build B-tree or bitmap secondary indexes. 
 For example, you might choose to index attribute columns on your dimensions.
CREATE UNIQUE INDEX "COUNTRIES_ISO" ON "COUNTRIES" ("COUNTRY_ISO_CODE");
And ADWC will use it.
Plan hash value: 2437708077

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 |        |       |   427 (100)|       |          |       |     45 |00:00:00.11 |    1651 |       |       |          |
|   1 |  SORT GROUP BY                    |               |      1 |    102 |  8874 |   427   (6)| 00:00:01 |       |       |     45 |00:00:00.11 |    1651 |  6144 |  6144 | 6144  (0)|
|*  2 |   HASH JOIN                       |               |      1 |   4210 |   357K|   426   (6)| 00:00:01 |       |       |  64818 |00:00:00.16 |    1651 |  5443K|  5443K| 5260K (0)|
|*  3 |    TABLE ACCESS STORAGE FULL      | PRODUCTS      |      1 |     26 |   208 |     2   (0)| 00:00:01 |       |       |     26 |00:00:00.01 |       7 |  1025K|  1025K|          |
|*  4 |    HASH JOIN                      |               |      1 |  11886 |   916K|   424   (6)| 00:00:01 |       |       |    141K|00:00:00.08 |    1644 |  4266K|  4266K| 4262K (0)|
|   5 |     JOIN FILTER CREATE            | :BF0001       |      1 |    364 |  4368 |     2   (0)| 00:00:01 |       |       |    364 |00:00:00.01 |      16 |       |       |          |
|   6 |      PART JOIN FILTER CREATE      | :BF0000       |      1 |    364 |  4368 |     2   (0)| 00:00:01 |       |       |    364 |00:00:00.01 |      16 |       |       |          |
|*  7 |       TABLE ACCESS STORAGE FULL   | TIMES         |      1 |    364 |  4368 |     2   (0)| 00:00:01 |       |       |    364 |00:00:00.01 |      16 |  1025K|  1025K|          |
|*  8 |     HASH JOIN                     |               |      1 |  48360 |  3164K|   422   (6)| 00:00:01 |       |       |    143K|00:00:00.07 |    1628 |  3376K|  3178K| 4759K (0)|
|   9 |      JOIN FILTER CREATE           | :BF0002       |      1 |   2921 |   128K|    21   (5)| 00:00:01 |       |       |  18520 |00:00:00.02 |     755 |       |       |          |
|  10 |       NESTED LOOPS                |               |      1 |   2921 |   128K|    21   (5)| 00:00:01 |       |       |  18520 |00:00:00.01 |     755 |       |       |          |
|  11 |        TABLE ACCESS BY INDEX ROWID| COUNTRIES     |      1 |      1 |    18 |     1   (0)| 00:00:01 |       |       |      1 |00:00:00.01 |       2 |       |       |          |
|* 12 |         INDEX UNIQUE SCAN         | COUNTRIES_ISO |      1 |      1 |       |     0   (0)|       |          |       |      1 |00:00:00.01 |       1 |  1025K|  1025K|          |
|* 13 |        VIEW                       | VW_GBF_25     |      1 |   2921 | 78867 |    20   (5)| 00:00:01 |       |       |  18520 |00:00:00.01 |     753 |       |       |          |
|  14 |         TABLE ACCESS STORAGE FULL | CUSTOMERS     |      1 |  55500 |  1138K|    20   (5)| 00:00:01 |       |       |  55500 |00:00:00.01 |     753 |  1025K|  1025K|          |
|  15 |      JOIN FILTER USE              | :BF0001       |      1 |    918K|    19M|   396   (5)| 00:00:01 |       |       |    158K|00:00:00.04 |     873 |       |       |          |
|  16 |       JOIN FILTER USE             | :BF0002       |      1 |    918K|    19M|   396   (5)| 00:00:01 |       |       |    158K|00:00:00.04 |     873 |       |       |          |
|  17 |        PARTITION RANGE JOIN-FILTER|               |      1 |    918K|    19M|   396   (5)| 00:00:01 |:BF0000|:BF0000|    158K|00:00:00.04 |     873 |       |       |          |
|* 18 |         TABLE ACCESS STORAGE FULL | SALES         |      5 |    918K|    19M|   396   (5)| 00:00:01 |:BF0000|:BF0000|    158K|00:00:00.04 |     873 |  1025K|  1025K|          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Whether I should do any of this is another matter!

Delivered non-default parameters

There are a number of database initialisation parameters set to non-default values that are noteworthy:
  • QUERY_REWRITE_INTEGRITY=TRUSTED: This ties in with making the foreign key constraints RELY, so that foreign key join elimination will still occur.
  • RESULT_CACHE_MODE=FORCE enables the result cache by default for all queries.  Whether this is going to be a significant benefit depends upon your application.
select  /*+ gather_plan_statistics opt_param('result_cache_mode','MANUAL')*/ 
 c.country_name
, u.cust_state_province
,  COUNT(*) num_sales
,  SUM(s.amount_sold) total_amount_sold
from  sales s
,   customers u
,   products p
,  times t
, countries c
WHERE  s.time_id = t.time_id
AND   s.prod_id = p.prod_id
AND   u.cust_id = s.cust_id
AND u.country_id = c.country_id
AND c.country_iso_code = '&&iso_country_code'
AND p.prod_category_id = 205
and  t.fiscal_year = 1999
GROUP BY c.country_name
, u.cust_state_province
ORDER BY 1,2
/
Without the result cache, I get the standard full scan/Bloom filter execution plan.
Plan hash value: 2719715383

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |           |      1 |        |       |    38 (100)|          |     45 |00:00:00.16 |     657 |       |       |          |
|   1 |  SORT GROUP BY                   |           |      1 |    102 |  8262 |    38  (48)| 00:00:01 |     45 |00:00:00.16 |     657 |  6144 |  6144 | 6144  (0)|
|*  2 |   HASH JOIN                      |           |      1 |   3478 |   275K|    37  (46)| 00:00:01 |  64818 |00:00:00.03 |     657 |  5443K|  5443K| 5259K (0)|
|*  3 |    TABLE ACCESS STORAGE FULL     | PRODUCTS  |      1 |     26 |   208 |     2   (0)| 00:00:01 |     26 |00:00:00.01 |       3 |  1025K|  1025K|          |
|*  4 |    HASH JOIN                     |           |      1 |   9819 |   699K|    35  (49)| 00:00:01 |    141K|00:00:00.18 |     654 |  4266K|  4266K| 4289K (0)|
|   5 |     JOIN FILTER CREATE           | :BF0000   |      1 |    364 |  4368 |     2   (0)| 00:00:01 |    364 |00:00:00.01 |       3 |       |       |          |
|*  6 |      TABLE ACCESS STORAGE FULL   | TIMES     |      1 |    364 |  4368 |     2   (0)| 00:00:01 |    364 |00:00:00.01 |       3 |  1025K|  1025K|          |
|*  7 |     HASH JOIN                    |           |      1 |  39950 |  2379K|    33  (52)| 00:00:01 |    141K|00:00:00.18 |     651 |  3411K|  3411K| 4776K (0)|
|   8 |      JOIN FILTER CREATE          | :BF0001   |      1 |   2413 | 94107 |     9  (12)| 00:00:01 |  18520 |00:00:00.01 |     215 |       |       |          |
|*  9 |       HASH JOIN                  |           |      1 |   2413 | 94107 |     9  (12)| 00:00:01 |  18520 |00:00:00.01 |     215 |  2642K|  2642K|  681K (0)|
|  10 |        JOIN FILTER CREATE        | :BF0002   |      1 |      1 |    18 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|* 11 |         TABLE ACCESS STORAGE FULL| COUNTRIES |      1 |      1 |    18 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |  1025K|  1025K|          |
|  12 |        JOIN FILTER USE           | :BF0002   |      1 |  55500 |  1138K|     7  (15)| 00:00:01 |  18520 |00:00:00.01 |     212 |       |       |          |
|* 13 |         TABLE ACCESS STORAGE FULL| CUSTOMERS |      1 |  55500 |  1138K|     7  (15)| 00:00:01 |  18520 |00:00:00.01 |     212 |  1025K|  1025K|          |
|  14 |      JOIN FILTER USE             | :BF0000   |      1 |    918K|    19M|    19  (58)| 00:00:01 |    156K|00:00:00.07 |     436 |       |       |          |
|  15 |       JOIN FILTER USE            | :BF0001   |      1 |    918K|    19M|    19  (58)| 00:00:01 |    156K|00:00:00.07 |     436 |       |       |          |
|* 16 |        TABLE ACCESS STORAGE FULL | SALES     |      1 |    918K|    19M|    19  (58)| 00:00:01 |    156K|00:00:00.07 |     436 |  1025K|  1025K|          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Normally result cache is enabled.  Here the query was not executed because the result was in the cache.
Plan hash value: 2719715383

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                            |      1 |        |       |    38 (100)|          |     45 |00:00:00.01 |
|   1 |  RESULT CACHE                     | 8992dgrw00p4p9zu2vmq8p3nwg |      1 |        |       |            |          |     45 |00:00:00.01 |
|   2 |   SORT GROUP BY                   |                            |      0 |    102 |  8262 |    38  (48)| 00:00:01 |      0 |00:00:00.01 |
|   3 |    HASH JOIN                      |                            |      0 |   3478 |   275K|    37  (46)| 00:00:01 |      0 |00:00:00.01 |
|   4 |     TABLE ACCESS STORAGE FULL     | PRODUCTS                   |      0 |     26 |   208 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |
|   5 |     HASH JOIN                     |                            |      0 |   9819 |   699K|    35  (49)| 00:00:01 |      0 |00:00:00.01 |
|   6 |      JOIN FILTER CREATE           | :BF0000                    |      0 |    364 |  4368 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |
|   7 |       TABLE ACCESS STORAGE FULL   | TIMES                      |      0 |    364 |  4368 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |
|   8 |      HASH JOIN                    |                            |      0 |  39950 |  2379K|    33  (52)| 00:00:01 |      0 |00:00:00.01 |
|   9 |       JOIN FILTER CREATE          | :BF0001                    |      0 |   2413 | 94107 |     9  (12)| 00:00:01 |      0 |00:00:00.01 |
|  10 |        HASH JOIN                  |                            |      0 |   2413 | 94107 |     9  (12)| 00:00:01 |      0 |00:00:00.01 |
|  11 |         JOIN FILTER CREATE        | :BF0002                    |      0 |      1 |    18 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |
|  12 |          TABLE ACCESS STORAGE FULL| COUNTRIES                  |      0 |      1 |    18 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |
|  13 |         JOIN FILTER USE           | :BF0002                    |      0 |  55500 |  1138K|     7  (15)| 00:00:01 |      0 |00:00:00.01 |
|  14 |          TABLE ACCESS STORAGE FULL| CUSTOMERS                  |      0 |  55500 |  1138K|     7  (15)| 00:00:01 |      0 |00:00:00.01 |
|  15 |       JOIN FILTER USE             | :BF0000                    |      0 |    918K|    19M|    19  (58)| 00:00:01 |      0 |00:00:00.01 |
|  16 |        JOIN FILTER USE            | :BF0001                    |      0 |    918K|    19M|    19  (58)| 00:00:01 |      0 |00:00:00.01 |
|  17 |         TABLE ACCESS STORAGE FULL | SALES                      |      0 |    918K|    19M|    19  (58)| 00:00:01 |      0 |00:00:00.01 |
------------------------------------------------------------------------------------------------------------------------------------------------

Parameter
Value
Comment
_default_pct_free
1
Sets the default value for PCT_FREE to 1% (defaults to 10%) in order to pack data in blocks.
_optimizer_gather_stats_on_load_all
TRUE
Statistics gathered on-line during direct-path insert into a table that is not empty.  Otherwise, this only happens on initial load or during a create-table-as-select command.
_optimizer_gather_stats_on_load_hist
TRUE
Histograms also gathered on-line during direct-path load.
optimizer_ignore_hints
TRUE
Ignore hints embedded in SQL.
optimizer_ignore_parallel_hints
TRUE
Ignore embedded parallel hints.
result_cache_max_size
100M
Maximum size of result cache.
result_cache_max_result
1
Maximum percentage of result cache that one result can use (defaults to 5%).
Inmemory_size
1G
Size of In-memory Column Store, so this feature is enabled.
_cell_offload_vector_groupby
FALSE
In-Memory Aggregation optimisation is disabled (see related white paper).

Other useful references:

Summary

Everything that I have discussed in this series applies to ADWC just as it does to other data warehouses.
  • Your data warehouse should certainly use a strict star schema data model such that you can define foreign and either primary or unique keys in the database.  
  • You should only have equality joins between dimension and fact tables.
  • Wherever possible these should be single column joins and therefore single column foreign and primary keys.  You can consider multi-column keys from Oracle 12.2, however, there are bugs.
  • Whether the database enforces the foreign key constraints is a matter of choice, but you need to define them in order to achieve join elimination.
ADWC is built on Engineered systems
  • So full scan/Bloom filter is going to be able to take advantage of Engineer system optimisations.
    • Bloom Filters are pushed to storage server during smart scan
    • A Bloom Filter on one table can be used against the storage index on another table to skip I/O.
    • Hybrid Columnar Compression further assists smart scans.
  • I would not like to work without enforced primary keys, I think that is courageous!
  • If the database is not going to enforce referential integrity, then foreign key should be made RELY.  As QUERY_REWRITE_INTEGRITY=TRUSTED you will still get foreign key join elimination.
  • You are expected to load data by periodically incrementally bulk loading it.
    • Optimiser statistics are maintained automatically in direct path insert even after the initial insert.  The regular statistics maintenance window is disabled.  If you are going to further transform of data after the initial load, then you will need to think about whether and when to collect statistics.
  • RESULT_CACHE_MODE=FORCE, so the result cache is used by default across the board, with a maximum result size to keep things reasonable.
    • This raises the question whether this should be considered for non-autonomous engineered system data warehouses?
    • However, used incorrectly, the result cache can cause contention problems.

Will Anything run on ADWC?

At the end of the day, ADWC is an Oracle database on an engineered system, although it has been set up and configured in a very particular way.  It is possible to alter ADWC settings to get traditional Star Transformation behaviour and do all the things that you usually do to implement a data warehouse.  However, as I have demonstrated earlier in this series, you probably wouldn't want to do that.
If your legacy data warehouse doesn't follow the principles of good practice, including but not limited to those set out above, then you probably should not be trying to force it into ADWC.  Other platforms, where you have more discretion, are available.

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.