Initialization Parameters
| 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:
For Oracle 9i, the list is even shorter ”just set the following parameters:
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: Table 5-2. Oracle Version Support for Data Warehousing Features
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. |
EAN: 2147483647
Pages: 79