The most common reason I find people having problems with star transformation explain plans is that they don't set the proper INIT.ORA parameters. In short, if you don't have the proper initialization parameters set, you cannot obtain star transformation explain plans, even if you specify the STAR_TRANSFORMATION hint! Please reread that last sentence again, possibly even twice, because 20% of the problem sites I visit have the simple problem of either not setting the right values or not setting them high enough.
For Oracle 8i, the following parameters must be set:
ALWAYS_ANTI_JOIN = HASH
ALWAYS_SEMI_JOIN = HASH
BITMAP_MERGE_AREA_SIZE = 16MB or larger
COMPATIBLE = 8.1.7
CREATE_BITMAP_AREA_SIZE = 16MB or larger
HASH_AREA_SIZE = 16MB or larger
HASH_JOIN_ENABLED = TRUE
OPTIMIZER_FEATURES_ENABLE = 8.1.7
SORT_AREA_SIZE = 16MB or larger
STAR_TRANSFORMATION = TRUE or TEMP_DISABLE
For Oracle 9i, the list is even shorter?just set the following parameters:
BITMAP_MERGE_AREA_SIZE = 16MB or larger
COMPATIBLE = 9.0.1 or 9.2.0
CREATE_BITMAP_AREA_SIZE = 16MB or larger
HASH_AREA_SIZE = 16MB or larger
HASH_JOIN_ENABLED = TRUE
OPTIMIZER_FEATURES_ENABLE = 9.0.1 or 9.2.0
SORT_AREA_SIZE = 16MB or larger
STAR_TRANSFORMATION = TRUE or TEMP_DISABLE
The STAR_TRANSFORMATION parameter is paramount here. Without it being set, there is absolutely no way to get a star transformation explain plan?not even by using the STAR_TRANNSFORMATION hint. This is the single most critical factor in getting the star transformation to work. Yet, 10% of the problem sites I visit have this simple problem. The default is FALSE, so please set this parameter.
One question that always comes up is what is TEMP_DISABLE and when or why should it be used? In Oracle 8.0, the STAR_TRANSFORMATION parameter was simply set to either TRUE or FALSE. However, beginning with Oracle 8i, the value of TEMP_DISABLE entered the mix, and in fact meant the same as TRUE in Oracle 8.0 (i.e., merely enabled), whereas TRUE now means both enabled and that Oracle can use temporary tables to store intermediate results. Specifically in the case where a dimension table may need to be accessed twice in the explain plan, the query optimizer may decide to create a temporary table for a subset of a dimension table instead of accessing that dimension table twice (e.g., when the dimension table is large and the selected subset seems to be small). Note that prior to Oracle 8.1.7.3, there were serious bugs with this optimization approach that could yield incorrect results or generate ORA-00600 errors.
The second most important initialization parameters to set are COMPATIBLE and OPTIMIZER_FEATURES_ENABLE. COMPATIBLE is often a problem because many people forget to change this parameter in their INIT.ORA file as they apply patches and/or install new versions. Likewise, the OPTIMIZER_FEATURES_ENABLE is just as important as it directly affects the behavior of the query optimizer (i.e., which optimizer features are in effect usable). Table 5-2 details the various optimizer features that are enabled by setting the different version settings:
Features | 8.0.6 | 8.0.7 | 8.1.6 | 8.1.7 | 9.0.1 | 9.2.0 |
---|---|---|---|---|---|---|
Index fast full scan | ||||||
Consideration of bitmap access paths for tables with only b-tree indexes | ||||||
Complex view merging | ||||||
Push-join predicate | ||||||
Ordered nested loop costing | ||||||
Improved outer join cardinality calculation | ||||||
Improved verification of NULLs inclusion in b-tree indexes | ||||||
Random distribution method for left of nested loops | ||||||
Type-dependent selectivity estimates | ||||||
Setting of optimizer mode for user-recursive SQL | ||||||
Improved average row length calculation | ||||||
Partition pruning based on sub-query predicates | ||||||
Common sub-expression elimination | ||||||
Use statistics of a column embedded in some selected functions such as TO_CHAR to compute selectivity | ||||||
Improved partition statistics aggregation | ||||||
Peeking at user-defined bind variables | ||||||
Index joins | ||||||
Sub-query un-nesting |
The initialization parameters BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE affect explain plan bitmap merge operations and bitmap index creation, respectively. Remember that star transformation depends heavily on bitmap indexes, so these initialization parameters are quite important. Also, remember that these settings apply per Oracle process, so if you're using parallel DML or parallel query, factor the size times the number of processes against your total overall memory consumption calculations.
The initialization parameters HASH_AREA_SIZE and HASH_JOIN_ENABLED affect explain plan hash join operations and whether hash joins are enabled, respectively. Remember that star transformation depends heavily on hash joins, so these initialization parameters are quite important. Also, remember that these settings apply per Oracle process, so if you're using parallel DML or parallel query, factor the size times the number of processes against your total overall memory consumption calculations.
Finally, increasing the initialization parameter SORT_AREA_SIZE improves the efficiency of large sorts as they can be performed in memory rather than on-disk. The default is a scant 64K, which is far too small for a data warehouse. Also, remember that these settings apply per Oracle process, so if you're using parallel DML or parallel query, factor the size times the number of processes against your total overall memory consumption calculations.