Effective PeopleSoft Performance Monitoring

This advice note describes how to configure PeopleSoft systems on Oracle so that performance metrics are collected that are useful performance monitoring and subsequent performance tuning.

Contents

• Oracle RDBMS Instrumentation
• On-Line Component/Page Information
• Application Engine Step Information
• Cobol/nVision Instrumentation
• nVision Layout Instrumentation
• 2nd Database Connection Instrumentation
• PeopleTools Performance Metrics
• Cobol and Application Engine Batch Timings
• PeopleSoft Performance Monitor

Summary of Recommendations

• Set EnableAEMonitoring=1 in all Application Server and process scheduler domains in order to enable PeopleSoft instrumentation on-line, in the integration broker, and in Application Engine programs.
• Implement the PSFTAPI package and trigger described above to set MODULE and ACTION at the start of all processes.
• Implement Fine-Grained Audit policy and handler to instrument nVision processes if required.
• Reduce AWR snapshot frequency from the default of 1 hour to every 15 minutes.
• Enable Application Engine batch timings to at least database and preferably also file by setting AETrace=1152 in all Application Server and Process Scheduler domain configuration files.
• Enable Cobol statement timings report should be written to log file by setting TraceSQL=128 in all Process Scheduler domain configuration files.
• Performance Monitor is complex to set up and the delivered analytics are limited.  Nonetheless, the information that can be obtained from the data collected can be worth the effort of configuration to address on-line configuration and performance issues.

Oracle RDBMS Instrumentation

Oracle provides the dbms_application_info package to set certain attributes on the current database session.  These attributes are visible in some of the dynamic performance version, and are picked up by Active Session History (ASH) and can also be seen in AWR reports, Enterprise Manager screens, SQL trace files, and other performance utilities such as EDB360.  The package was first documented in Oracle 7.3.3

"Application developers can use the DBMS_APPLICATION_INFO package to record the name of the executing module or transaction in the database for use later when tracking the performance of various modules… System administrators can also use this information to track resource usage by module. When an application registers with the database, its name and actions are recorded in the V$SESSION and V$SQLAREA views. Your applications should set the name of the module and name of the action automatically each time a user enters that module. The module name could be the name of a … code segment in an … application. The action name should usually be the name or description of the current transaction within a module."

• Oracle 7 Tuning, release 7.3.3 ©Oracle 1997, Chapter 23 Registering Applications

• See also One of my Favourite Database Things: DBMS_APPLICATION_INFO
If module and action are consistently set to meaningful values as the application executes it is then possible to determine from where SQL statements originated and how different parts of the application are performing.
ASH is separately licenced as part the Diagnostics Pack, that is only available on Enterprise Edition of the database.  However, most PeopleSoft customers running on Oracle do so on Enterprise Edition and are licenced for the diagnostics pack.
PeopleSoft has set client_info since PeopleTools 7.53 so the PeopleSoft operator ID can be associated with the database session, mainly to allow auditing to be done with database triggers.  However, this attribute is not persisted to the ASH data.  However, client_id is also set to the operator ID, and this is collected by ASH.
PeopleTools has set module and action since PeopleTools 8.50, though the exact values used have changed sometimes with the PeopleTools version.
A new application server/process scheduler domain parameter EnableAEMonitoring was introduced in PeopleTools 8.54 to control this behaviour and it is not enabled by default.  If monitoring is not enabled module defaults to the program name and action remains blank.
Recommendation: Set EnableAEMonitoring=1 in all application server and process scheduler domains in order to enable PeopleSoft instrumentation online, in the integration broker, and in Application Engine programs.
See also:

• PeopleTools 8.50 uses DBMS_APPLICATION_INFO to Identify Database Sessions
• PeopleTools 8.52 Application Engine sets MODULE and ACTION 
• Undocumented (until PeopleTools 8.55) Application Engine Parameter: EnableAEMonitoring  

On-Line Component/Page Information

In the online application, module and action are set to the component and page name respectively.  In a search dialogue, Action is set to 'xyzzy'.
In the Integration Broker module and action are set to service name and queue name.

Application Engine Step Information

In Application Engine, module is set to a string that includes the name of the Application Engine main program the was called and the sessionid_num for the current process instance number recorded on the process scheduler request table PSPRCSQUE.  For example: PSAE.PSPMCSOSUM.1448
Later on, it may be necessary to use regular expressions in SQL to process this string before profiling the ASH data.
Action is set to string concatenated from the Application Engine program, section, step name, and step type.  For example: PSPMCSOSUM.GETCNT.CNT.P
The program name may be different from that shown in module if one AE program calls another.
Note: Application Engine still doesn't reset ACTION on DO SELECT steps

• See PeopleTools 8.52 Application Engine sets MODULE and ACTION).

Cobol/nVision Instrumentation

Cobol, nVision, and SQR do not set module and action.  Instead, they can be set at the start of every program initiated by Process Scheduler using a trigger on PSPRCSRQST.  The first thing a process does when it is initiated is to set the run status on its scheduler request record to 7, indicating that it is processing.  This is visible in the Process Monitor component.  A trigger on this transition can set module and action for the session to the program and process instance number on that row of data.
This technique was used prior to PeopleSoft adding instrumentation to PeopleTools.  It can still be applied to all processes, including Application Engine because any PeopleSoft instrumentation will simply overwrite the value set by the trigger.

• See null

Recommendation: Implement the PSFTAPI package and trigger described above in order to set module and action at the start of all processes.
The same technique was also used prior to the introduction of ASH to enable Oracle SQL Trace if a particular run control was specified, and is still occasionally useful.

• See Enabling Oracle Database Trace on PeopleSoft processes with a Trigger (improved)

nVision Layout Instrumentation

One of the challenges of tuning and monitoring nVision is to be able to identify each report being run.   nVision reports always run as the same process name, either NVSRUN for a single report, RPTBOOK for a report book of many reports, or DRILLDWN for a nVision drill-down query.  Knowing the process instance is useful because then we can look up the operator and run control ID
However, it would also be useful to know the report ID being run.  When each individual nVision report starts it queries the runtime parameters from the PS_NVS_REPORT PeopleTools table.  There is no update, so it is not possible to capture this with a DML trigger.  Instead, it is possible to define a fine-grained audit policy on the query and set module within a PL/SQL handler package that is invoked by the fine-grained audit.

• See nVision Performance Tuning: 5. Additional Instrumentation of nVision

Recommendation: Implement Fine-Grained Audit policy and handler to instrument nVision processes if required

2nd Database Connection Information

PeopleSoft programs use a second database connection to increment sequence numbers to minimise the row-level locking on such tables.  It is like an AUTONOMOUS_TRANSACTION in PL/SQL.  There is no PeopleSoft instrumentation on this session.  It is possible to use an AFTER LOGON trigger to set client_info, module and action.

• See also Sequence Number Allocation in PeopleSoft

Oracle Automatic Workload Repository (AWR) Snapshots

PeopleSoft generates a lot of non-shareable SQL.

• Dynamically generated SQL, often in PeopleCode, concatenates strings of SQL with bind variables, thus the bind variables become literals in the final SQL statement.  Statements with different literal values are considered to be different statements with different SQL_IDs.
• Similarly, dynamic Cobol statements result in literal values in the SQL statement.
• %BIND() variables in Application Engine will also become literal values in the SQL Statement unless the ReUseStatement attribute is set on the AE step, however, this cannot be set on statements with dynamic fragments code are introduced with %BIND(…,NOQUOTES).
• Application Engine programs that use temporary records can use different non-shared instances of the record in different executions, and this also results in different statements with different SQL_IDs.
• See also Performance Benefits of ReUse Statement Flag in Application Engine

Consequently, the library caching is not particularly efficient in PeopleSoft, and dynamic SQL statements are often quickly aged out of the library cache.  AWR snapshots can only capture the SQL that is in the library cache at the time of the snapshot.  If the SQL statement, or at least a statement with the same force matching signature or plan hash value, cannot be found in the AWR it cannot be identified or analysed.  Therefore, it is advantageous to increase the snapshot frequency on PeopleSoft systems.
Recommendation: Reduce AWR snapshot frequency from the default of 1 hour to every 15 minutes.  This change results in only a modest increase in overhead in processing and space on AWR, but it is worth the additional information that is captured.
This advice also applies to Statspack that may be used if you are not licenced for the Diagnostics Pack.

PeopleTools Performance Metrics

Batch Timings

PeopleSoft Application Engine and Cobol programs can emit batch timings reports on successful completion.

Application Engine

Application Engine batch timings are controlled by the AETrace flag in the Process Scheduler domain configuration file and for on-line AE programs in the Application Server domain configuration files.

• AETrace=128: batch timings report is written to the AE Trace file to
• AETrace=1024: batch timings are written to PS_BAT_TIMINGS% tables in the database

The overhead of batch timings is negligible while the program is running because it is accounted in memory and only written to a file or the database when the process completes successfully.
Recommendation: Enable Application Engine batch timings to at least database and preferably also file by setting AETrace=1152 in all Application Server and Process Scheduler domain configuration files.
The trace setting in the process scheduler configuration can be overridden by setting process-specific command line parameter overrides in the process definition.  This is often done to set other trace settings, it is also common to see these unintentionally left in place longer than necessary.  If trace is enabled in this way, the batch timings flags should also be set.
See PeopleBooks -> Development Tools -> Application Engine -> Enabling Application Engine Tracing

Cobol

PeopleSoft Cobol programs can only write batching timings reports to file and not to the database.  This is controlled by a different parameter.

• TraceSQL = 128: Enable Cobol statement timings report 

Recommendation: Enable Cobol statement timings report should be written to log file by setting TraceSQL=128 in all Process Scheduler domain configuration files.
This trace setting can also be overridden by setting process-specific command line parameter overrides in the process definition.  If trace is enabled in this manner, then it should always also set the batch timings flags.

PeopleSoft Performance Monitor

This provides information about the performance of the PIA including response times for the online transactions.  Metrics are stored in a separate monitoring PeopleSoft system to minimize the effect of measurement intrusion.  It optionally samples the state of each web server, application server and process scheduler collecting operating system and Tuxedo metrics.  It also has a PIA session trace capability.
The sampled data includes the number of busy application server processes and length of inbound Tuxedo service queues.  This data can be used to validate the sizing of the application servers.
Recommendation: Performance Monitor is complex to set up and the delivered analytics are limited.  Nonetheless, the information that can be obtained from the data collected can be worth the effort of configuration to address online configuration and performance issues.

Related post:

• Practical Application Performance Tuning: An nVision Case Study (Video)

Regular Expression Functions are Considered To Be Non-Deterministic from Oracle 12.2

You cannot put non-deterministic functions into virtual columns or function-based index.  Regular expressions have always been sensitive to National Language Support (NLS) settings because what is a character is different in different languages.  However, from Oracle 12.2. regular expressions are considered to be non-deterministic because Oracle has fixed bug 20804063.

Background 

A developer came to me with the following query, complaining it was slow.

SELECT …
FROM   …
AND    LOWER(REGEXP_REPLACE(ASCIISTR(my_column), '[^a-zA-Z0-9]')) ='2732018'
…

Obviously, functions on columns in predicates prevent the use of indexes, so I suggested creating and indexing a virtual column (slightly more elegant and almost the same as a function-based index but without needing the function in the code to match the index).
This works fine in Oracle 12.1, but they came back saying it didn't in 12.2.  Note the error messages in bold below.

DROP TABLE t PURGE
/
CREATE TABLE t AS 
SELECT TO_CHAR(sysdate-level,'YYYY/DDD') a
FROM   dual
CONNECT BY LEVEL < 1000
/

CREATE INDEX t1 on t (REGEXP_REPLACE(a,'[^a-zA-Z0-9]'))
/
ORA-01743: only pure functions can be indexed

DROP INDEX t1
/
ALTER TABLE t 
ADD b AS (REGEXP_REPLACE(a,'[^a-zA-Z0-9]')) VIRTUAL
/
ORA-54002: only pure functions can be specified in a virtual column expression

I eventually came across this question and answer by Connor McDonald on AskTom: Adding constraint with REGEXP_REPLACE fails that explains that Oracle has fixed bug 20804063 in 12.2.  This fix affects virtual columns, function-based indexes (because they also create virtual columns), and constraints.
There are a number of workarounds, but none are completely satisfactory.

Workaround 1: Deterministic Function 

As Connor suggests, you can work around this unwanted behaviour by creating your own PL/SQL function and telling the database it is deterministic. This is not simply lying to the database.  Not all regular expressions are actually NLS sensitive, it depends on what you are doing.  However, it does appear that the validation is applied regardless. If the function is NLS sensitive that might have unwanted consequences, including incorrect query results. You would have to decide whether you can live with the risk depending on what is actually in the column and function in question.

CREATE OR REPLACE FUNCTION my_regexp_replace(p1 VARCHAR2, p2 VARCHAR2) 
RETURN varchar2 
DETERMINISTIC IS
BEGIN
RETURN REGEXP_REPLACE(p1, p2);
END;
/
show error

CREATE INDEX t1 ON t (my_regexp_replace(a,'[^a-zA-Z0-9]'))
/
DROP INDEX t1
/
ALTER TABLE t 
ADD b AS (my_REGEXP_REPLACE(a,'[^a-zA-Z0-9]')) VIRTUAL
/
CREATE INDEX t1 ON t (b)
/

Workaround 2: Refresh on Commit Materialized View 

Another possible workaround would be a materialized view that refreshes on commit, with the expression creating an additional column that is then indexed.

CREATE TABLE t AS 
SELECT rownum n
,      TO_CHAR(sysdate-level,'YYYY/DDD') a
FROM  dual
CONNECT BY LEVEL < 1000
/
ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (n)
/
CREATE MATERIALIZED VIEW LOG ON t
/

CREATE MATERIALIZED VIEW T1_MV 
REFRESH ON COMMIT 
FAST 
WITH PRIMARY KEY 
ENABLE QUERY REWRITE AS 
SELECT t.*
,REGEXP_REPLACE(a,'[^a-zA-Z0-9]') b
FROM t
/

CREATE INDEX t1_mv_b ON t1_mv(b);

INSERT INTO t
SELECT ROWNUM+1000 n
,      TO_CHAR(sysdate-level-1000,'YYYY/DDD') a
FROM  dual
CONNECT BY LEVEL < 1000
/
commit
/

set autotrace on
SELECT * FROM t
WHERE REGEXP_REPLACE(a,'[^a-zA-Z0-9]') like '201720%';

And Oracle does indeed rewrite the query to use the materialised view and then uses the index on the materialized view.

Plan hash value: 3543552962

----------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |         |     1 |    21 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS BY INDEX ROWID BATCHED| T1_MV   |     1 |    21 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                             | T1_MV_B |     2 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

However, this solution has the same weakness of being dependent on NLS settings – it is just that there is no validation to stop you! There is the further threat that the refresh on commit could become a performance problem if there is intense DDL on the underlying table.

Workaround 3: Use a Non-NLS Dependent Function 

Ideally, it would be better to use a non-NLS dependent function instead of a REGEXP% function. However, this may be easier said than done.  Regular expressions solve problems that are hard to do with basic string handling functions and may require a PL/SQL function anyway to hold procedural code.  Although in this relatively simple example it is possible to remove the unwanted characters with the translate and replace functions. There is no NLS restriction here.

SELECT a
,      REGEXP_REPLACE(a,'[^0-9]')
,      REPLACE(TRANSLATE(a,'/',' '),' ','')
FROM   t
WHERE rownum <= 10
/

A        REGEXP_REPLACE(A,'[^0-9]')       REPLACE(TRANSLATE(A,'/',''),'','
-------- -------------------------------- --------------------------------
2019/031 2019031                          2019031
2019/030 2019030                          2019030
2019/029 2019029                          2019029
2019/028 2019028                          2019028
2019/027 2019027                          2019027
2019/026 2019026                          2019026
2019/025 2019025                          2019025
2019/024 2019024                          2019024
2019/023 2019023                          2019023
2019/022 2019022                          2019022
…

And you can put this into a function-based index or virtual column thus

DROP INDEX t1 
/ 
ALTER TABLE t ADD b AS (REPLACE(TRANSLATE(a,'/',' '),' ','')) VIRTUAL 
/ 
CREATE INDEX t1 ON t (b) 
/

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:

• Oracle Cloud Infrastructure Documentation
• Connecting to an Autonomous Data Warehouse 
• Autonomous Data Warehouse
• Loading Data from Files in the Cloud
• Importing Data Using Oracle Data Pump 

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:

• Frank Pachot ADWC – System and session settings (DWCS lockdown profile)
• Dani Schnider: Gathering Statistics in the Autonomous Data Warehouse Cloud
• Frank Pachot Result Cache: when *not* to use it

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.

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.