Dear group,
We're on AIX5L-64 on a 4 CPU dualcore IBM p550, IBM SAN 4000,
Oracle9.2.0.8-64, PGA automatic tuning target is 512 MB.

Got a SQL statement that is perfect for a star transformation, one
fact table with 3 dimension-like tables with where-clauses on the
dimensions. The fact table has single column local bitmap indexes on
all dimension columns, there are no explicit constraints between the
fact table and the lookups. (Wish to make.)
Suddenly a user calls: my report that was back in minutes last week,
now is taking forever (not back after several hours).
After some experimentation, we have learned that, re. the response
time:
- setting the hash_area_size from 16 MB to bigger (100 MB, 1000 MB)
does not help
- setting star_transformation_enabled from true to temp_disable DOES
HELP enormously, back under a minute
- _b_tree_bitmap_plans true or false has no impact here.
(The query is not running with parallel execution.)

I generated a trace file for tkprof using
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';
However the total time elapsed in the tkprof report shows only three
seconds, while we are running now for at least several minutes.
The activity that runs very slowly, after creation of the sys temp
table, is like:
WAIT #3: nam='db file sequential read' ela= 7969 p1=64 p2=227352 p3=1
WAIT #3: nam='db file sequential read' ela= 10342 p1=64 p2=131478 p3=1
WAIT #3: nam='db file sequential read' ela= 5908 p1=64 p2=154214 p3=1
WAIT #3: nam='db file sequential read' ela= 9571 p1=64 p2=240847 p3=1
which happens to be pointing to the fact table:
  select segment_name
,      segment_type
,      block_id
from   dba_extents
where  file_id = 64
and    240847 between block_id and block_id + blocks
/
SEGMENT_NAME SEGMENT_TYPE       BLOCK_ID
WE_ROS_SKU_LOC_WK_DM TABLE PARTITION    240837

elapsed (ela) above, in centiseconds, is incredibly high for just the
single block being fetched (p3).
errpt -a shows no problems on the SAN.

I somehow do not feel convinced that the response times for correctly
accessed response times for these blocks are that high, since reading
the table with another plan reads like a dream.

We are curious to know whether you can give some suggestions where to
look for an explanation for this observation.
Thanks and regards,
Erik Ykema

This is the SQL (generated by MicroStrategy IntelligenceServer):
create table ZZTML0H3CNGMD000 nologging as
select  a11.WK_IDNT  WK_IDNT,
        a13.STYLE_KEY  STYLE_KEY,
        a13.COLR_KEY  COLR_KEY,
        a13.CLASS_KEY  CLASS_KEY,
        avg(((NVL(a11.F_ROS_SLS_CLRC_QTY, 0) + NVL(a11.F_ROS_SLS_PRMTN_QTY,
0)) + NVL(a11.F_ROS_SLS_RGLR_QTY, 0)))  as ROS_SLS_QTY
        ,  count(distinct a11.LOC_KEY) as loc_keys
from    WE_ROS_SKU_LOC_WK_DM    a11, -- fact table
        V_ORG_LOC_DM    a12, -- dimension
        V_DIM_PROD_SKU  a13, -- dimension
        PROD_SKU_SEASN_MTX_DM   a15 -- dimension
where   a11.LOC_KEY = a12.LOC_KEY and
        a11.SKU_KEY = a13.SKU_KEY and
        a12.CMPY_KEY = a13.CMPY_KEY and
        a11.SKU_KEY = a15.SKU_KEY
 and    (a12.CHAIN_KEY in (1)
 and a13.CLASS_KEY in (8, 16, 276, 20, 21, 23, 42, 46)
 and a15.SEASN_IDNT in (17)
 and a11.WK_IDNT in (200649, 200650, 200651, 200652, 200701, 200702,
200703, 200704, 200705, 200706, 200707, 200708, 200709, 200710,
200711, 200712, 200713)
 )
group by        a11.WK_IDNT,
        a13.STYLE_KEY,
        a13.COLR_KEY,
        a13.CLASS_KEY

and this is the plan with star_transformation=temp_disable,
_b_tree_bitmap_plans=false, has=16 and is back quickly (explain plan
using Oracle SqlDeveloper):

"Optimizer"   "Cost"        "Cardinality" "Bytes"       "Partition Start"     "Partition
Stop"      "Partition Id"        "ACCESS PREDICATES"   "FILTER PREDICATES"
"CREATE TABLE
STATEMENT" "CHOOSE"      "9382"        "7823"        "477203"      ""    ""    ""    ""    ""
"LOAD AS SELECT"      ""    ""    ""    ""    ""    ""    ""    ""    ""
"SORT(GROUP BY)"      ""    "9382"        "7823"        "477203"      ""    ""    ""    ""    ""
"HASH
JOIN"      ""    "9376"        "7823"        "477203"      ""    ""    ""    ""A11"."SKU_KEY"="A15"."SKU_KEY""     ""
"TABLE ACCESS(FULL)
RDW25_ADM_DM.PROD_SKU_SEASN_MTX_DM@:Q335110004"    "ANALYZED"    "9"   "18989"       "151912"      ""    ""    ""    ""    ""A15"."SEASN_IDNT"=17"
"HASH
JOIN"      ""    "9367"        "28849"       "1528997"     ""    ""    ""    ""A11"."LOC_KEY"="ORG_LOC_DM"."LOC_KEY"
AND "A11"."SKU_KEY"="MV_DIM_PROD_SKU"."SKU_KEY""   ""
"PARTITION
RANGE(INLIST)"     ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "6"   ""    ""
"TABLE ACCESS(BY LOCAL INDEX ROWID)
RDW25_ADM_DM.WE_ROS_SKU_LOC_WK_DM" "ANALYZED"    "9312"        "1204430"     "24088600"    "KEY(INLIST)" "KEY(INLIST)" "6"   ""    ""A11"."WK_IDNT"=200649
OR "A11"."WK_IDNT"=200650 OR "A11"."WK_IDNT"=200651 OR
"A11"."WK_IDNT"=200652 OR "A11"."WK_IDNT"=200701 OR
"A11"."WK_IDNT"=200702 OR "A11"."WK_IDNT"=200703 OR
"A11"."WK_IDNT"=200704 OR "A11"."WK_IDNT"=200705 OR
"A11"."WK_IDNT"=200706 OR "A11"."WK_IDNT"=200707 OR
"A11"."WK_IDNT"=200708 OR "A11"."WK_IDNT"=200709 OR
"A11"."WK_IDNT"=200710 OR "A11"."WK_IDNT"=200711 OR
"A11"."WK_IDNT"=200712 OR "A11"."WK_IDNT"=200713"
"BITMAP CONVERSION(TO ROWIDS)"        ""    ""    ""    ""    ""    ""    ""    ""    ""
"BITMAP AND"  ""    ""    ""    ""    ""    ""    ""    ""    ""
"BITMAP MERGE"        ""    ""    ""    ""    ""    ""    ""    ""    ""
"BITMAP KEY ITERATION"        ""    ""    ""    ""    ""    ""    ""    ""    ""
"BUFFER(SORT)"        ""    ""    ""    ""    ""    ""    ""    ""    ""
"TABLE ACCESS(FULL)
RDW25_ADM_DM.ORG_LOC_DM"   "ANALYZED"    "3"   "218" "2616"        ""    ""    ""    ""    ""ORG_LOC_DM"."CHAIN_KEY"=1
AND "ORG_LOC_DM"."DM_RECD_CURR_FLAG"='Y'"
"BITMAP INDEX(RANGE SCAN)
RDW25_ADM_DM.B_WE_ROS_SLW_LOC"     ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "6"   ""A11"."LOC_KEY"="ORG_LOC_DM"."LOC_KEY""      ""
"BITMAP OR"   ""    ""    ""    ""    ""    ""    ""    ""    ""
"BITMAP INDEX(SINGLE VALUE)
RDW25_ADM_DM.B_WE_ROS_SLW_WK"      ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "6"   ""A11"."WK_IDNT"=200649"  ""
"BITMAP INDEX(SINGLE VALUE)
RDW25_ADM_DM.B_WE_ROS_SLW_WK"      ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "6"   ""A11"."WK_IDNT"=200650"  ""
"BITMAP INDEX(SINGLE VALUE)
RDW25_ADM_DM.B_WE_ROS_SLW_WK"      ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "6"   ""A11"."WK_IDNT"=200651"  ""
"BITMAP INDEX(SINGLE VALUE)
RDW25_ADM_DM.B_WE_ROS_SLW_WK"      ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "6"   ""A11"."WK_IDNT"=200652"  ""
"BITMAP INDEX(SINGLE VALUE)
RDW25_ADM_DM.B_WE_ROS_SLW_WK"      ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "6"   ""A11"."WK_IDNT"=200701"  ""
"BITMAP INDEX(SINGLE VALUE)
RDW25_ADM_DM.B_WE_ROS_SLW_WK"      ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "6"   ""A11"."WK_IDNT"=200702"  ""
"BITMAP INDEX(SINGLE VALUE)
RDW25_ADM_DM.B_WE_ROS_SLW_WK"      ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "6"   ""A11"."WK_IDNT"=200703"  ""
"BITMAP INDEX(SINGLE VALUE)
RDW25_ADM_DM.B_WE_ROS_SLW_WK"      ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "6"   ""A11"."WK_IDNT"=200704"  ""
"BITMAP INDEX(SINGLE VALUE)
RDW25_ADM_DM.B_WE_ROS_SLW_WK"      ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "6"   ""A11"."WK_IDNT"=200705"  ""
"BITMAP INDEX(SINGLE VALUE)
RDW25_ADM_DM.B_WE_ROS_SLW_WK"      ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "6"   ""A11"."WK_IDNT"=200706"  ""
"BITMAP INDEX(SINGLE VALUE)
RDW25_ADM_DM.B_WE_ROS_SLW_WK"      ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "6"   ""A11"."WK_IDNT"=200707"  ""
"BITMAP INDEX(SINGLE VALUE)
RDW25_ADM_DM.B_WE_ROS_SLW_WK"      ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "6"   ""A11"."WK_IDNT"=200708"  ""
"BITMAP INDEX(SINGLE VALUE)
RDW25_ADM_DM.B_WE_ROS_SLW_WK"      ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "6"   ""A11"."WK_IDNT"=200709"  ""
"BITMAP INDEX(SINGLE VALUE)
RDW25_ADM_DM.B_WE_ROS_SLW_WK"      ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "6"   ""A11"."WK_IDNT"=200710"  ""
"BITMAP INDEX(SINGLE VALUE)
RDW25_ADM_DM.B_WE_ROS_SLW_WK"      ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "6"   ""A11"."WK_IDNT"=200711"  ""
"BITMAP INDEX(SINGLE VALUE)
RDW25_ADM_DM.B_WE_ROS_SLW_WK"      ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "6"   ""A11"."WK_IDNT"=200712"  ""
"BITMAP INDEX(SINGLE VALUE)
RDW25_ADM_DM.B_WE_ROS_SLW_WK"      ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "6"   ""A11"."WK_IDNT"=200713"  ""
"BITMAP MERGE"        ""    ""    ""    ""    ""    ""    ""    ""    ""
"BITMAP KEY ITERATION"        ""    ""    ""    ""    ""    ""    ""    ""    ""
"BUFFER(SORT)"        ""    ""    ""    ""    ""    ""    ""    ""    ""
"NESTED LOOPS"        ""    "53"  "10814"       "313606"      ""    ""    ""    ""    ""
"TABLE ACCESS(FULL)
RDW25_ADM_DM.MV_DIM_PROD_SKU"      "ANALYZED"    "52"  "10814"       "227094"      ""    ""    ""    ""    ""MV_DIM_PROD_SKU"."CLASS_KEY"=8
OR "MV_DIM_PROD_SKU"."CLASS_KEY"=16 OR
"MV_DIM_PROD_SKU"."CLASS_KEY"=20 OR "MV_DIM_PROD_SKU"."CLASS_KEY"=21
OR "MV_DIM_PROD_SKU"."CLASS_KEY"=23 OR
"MV_DIM_PROD_SKU"."CLASS_KEY"=42 OR "MV_DIM_PROD_SKU"."CLASS_KEY"=46
OR "MV_DIM_PROD_SKU"."CLASS_KEY"=276"
"INDEX(RANGE SCAN)
RDW25_ADM_DM.N_PROD_SKU_SSN_SEASN_SKU"     "ANALYZED"    ""    "1"   "8"   ""    ""    ""    ""A15"."SEASN_IDNT"=17
AND "A15"."SKU_KEY"="MV_DIM_PROD_SKU"."SKU_KEY""   ""
"BITMAP INDEX(RANGE SCAN)
RDW25_ADM_DM.B_WE_ROS_SLW_SKU"     ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "6"   ""A11"."SKU_KEY"="MV_DIM_PROD_SKU"."SKU_KEY"" ""
"HASH
JOIN"      ""    "55"  "2356371"     "77760243"    ""    ""    ""    ""ORG_LOC_DM"."CMPY_KEY"="MV_DIM_PROD_SKU"."CMPY_KEY""        ""
"TABLE ACCESS(FULL)
RDW25_ADM_DM.ORG_LOC_DM@:Q335110000"       "ANALYZED"    "3"   "218" "2616"        ""    ""    ""    ""    ""ORG_LOC_DM"."CHAIN_KEY"=1
AND "ORG_LOC_DM"."DM_RECD_CURR_FLAG"='Y'"
"TABLE ACCESS(FULL)
RDW25_ADM_DM.MV_DIM_PROD_SKU@:Q335110002"  "ANALYZED"    "52"  "10814"       "227094"      ""    ""    ""    ""    ""MV_DIM_PROD_SKU"."CLASS_KEY"=8
OR "MV_DIM_PROD_SKU"."CLASS_KEY"=16 OR
"MV_DIM_PROD_SKU"."CLASS_KEY"=20 OR "MV_DIM_PROD_SKU"."CLASS_KEY"=21
OR "MV_DIM_PROD_SKU"."CLASS_KEY"=23 OR
"MV_DIM_PROD_SKU"."CLASS_KEY"=42 OR "MV_DIM_PROD_SKU"."CLASS_KEY"=46
OR "MV_DIM_PROD_SKU"."CLASS_KEY"=276"

and this is the plan using star_transformation_enabled=true, has=16,
_b_tree_bitmap_plans=false, and does never complete:
"Optimizer"   "Cost"        "Cardinality" "Bytes"       "Partition Start"     "Partition
Stop"      "Partition Id"        "ACCESS PREDICATES"   "FILTER PREDICATES"
"CREATE TABLE STATEMENT"      "CHOOSE"      "338" "1"   "58"  ""    ""    ""    ""    ""
"LOAD AS SELECT"      ""    ""    ""    ""    ""    ""    ""    ""    ""
"TEMP TABLE TRANSFORMATION"   ""    ""    ""    ""    ""    ""    ""    ""    ""
"SORT(GROUP BY)"      ""    "338" "1"   "58"  ""    ""    ""    ""    ""
"TABLE ACCESS(BY LOCAL INDEX ROWID)
RDW25_ADM_DM.WE_ROS_SKU_LOC_WK_DM@:Q335111003"     "ANALYZED"    "334" "1"   "20"  ""    ""    ""    ""    ""A11"."WK_IDNT"=200649
OR "A11"."WK_IDNT"=200650 OR "A11"."WK_IDNT"=200651 OR
"A11"."WK_IDNT"=200652 OR "A11"."WK_IDNT"=200701 OR
"A11"."WK_IDNT"=200702 OR "A11"."WK_IDNT"=200703 OR
"A11"."WK_IDNT"=200704 OR "A11"."WK_IDNT"=200705 OR
"A11"."WK_IDNT"=200706 OR "A11"."WK_IDNT"=200707 OR
"A11"."WK_IDNT"=200708 OR "A11"."WK_IDNT"=200709 OR
"A11"."WK_IDNT"=200710 OR "A11"."WK_IDNT"=200711 OR
"A11"."WK_IDNT"=200712 OR "A11"."WK_IDNT"=200713"
"NESTED LOOPS"        ""    "334" "1"   "58"  ""    ""    ""    ""    ""
"HASH
JOIN"      ""    "4"   "1"   "38"  ""    ""    ""    ""ORG_LOC_DM"."CMPY_KEY"="T1"."C2""   ""
"TABLE ACCESS(FULL)
SYS.SYS_TEMP_0FD9D6987_5A09774E@:Q335111001"       ""    "1"   "1"   "26"  ""    ""    ""    ""    ""
"TABLE ACCESS(FULL)
RDW25_ADM_DM.ORG_LOC_DM@:Q335111000"       "ANALYZED"    "3"   "218" "2616"        ""    ""    ""    ""    ""ORG_LOC_DM"."CHAIN_KEY"=1
AND "ORG_LOC_DM"."DM_RECD_CURR_FLAG"='Y'"
"PARTITION
RANGE(INLIST)"     ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "10"  ""    ""
"BITMAP CONVERSION(TO ROWIDS)"        ""    ""    ""    ""    ""    ""    ""    ""    ""
"BITMAP AND"  ""    ""    ""    ""    ""    ""    ""    ""    ""
"BITMAP CONVERSION(FROM ROWIDS)"      ""    ""    ""    ""    ""    ""    ""    ""    ""
"SORT(ORDER BY)"      ""    ""    ""    ""    ""    ""    ""    ""    ""
"INDEX(RANGE SCAN)
RDW25_ADM_DM.PK_ROS_SLWD@:Q335111002"      "ANALYZED"    "29"  ""    ""    "KEY(INLIST)" "KEY(INLIST)" "10"  ""A11"."SKU_KEY"="T1"."C0"
AND "A11"."LOC_KEY"="ORG_LOC_DM"."LOC_KEY""        "("A11"."WK_IDNT"=200649
OR "A11"."WK_IDNT"=200650 OR "A11"."WK_IDNT"=200651 OR
"A11"."WK_IDNT"=200652 OR "A11"."WK_IDNT"=200701 OR
"A11"."WK_IDNT"=200702 OR "A11"."WK_IDNT"=200703 OR
"A11"."WK_IDNT"=200704 OR "A11"."WK_IDNT"=200705 OR
"A11"."WK_IDNT"=200706 OR "A11"."WK_IDNT"=200707 OR
"A11"."WK_IDNT"=200708 OR "A11"."WK_IDNT"=200709 OR
"A11"."WK_IDNT"=200710 OR "A11"."WK_IDNT"=200711 OR
"A11"."WK_IDNT"=200712 OR "A11"."WK_IDNT"=200713) AND
"A11"."SKU_KEY"="T1"."C1" AND "A11"."LOC_KEY"="ORG_LOC_DM"."LOC_KEY"
AND "A11"."SKU_KEY"="T1"."C0""
"BITMAP MERGE"        ""    ""    ""    ""    ""    ""    ""    ""    ""
"BITMAP KEY ITERATION"        ""    ""    ""    ""    ""    ""    ""    ""    ""
"BUFFER(SORT)"        ""    ""    ""    ""    ""    ""    ""    ""    ""
"TABLE ACCESS(FULL)
RDW25_ADM_DM.ORG_LOC_DM@:Q335111002"       "ANALYZED"    "3"   "218" "2616"        ""    ""    ""    ""    ""ORG_LOC_DM"."CHAIN_KEY"=1
AND "ORG_LOC_DM"."DM_RECD_CURR_FLAG"='Y'"
"BITMAP INDEX(RANGE SCAN)
RDW25_ADM_DM.B_WE_ROS_SLW_LOC@:Q335111002" ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "10"  ""A11"."LOC_KEY"="ORG_LOC_DM"."LOC_KEY""      ""
"BITMAP MERGE"        ""    ""    ""    ""    ""    ""    ""    ""    ""
"BITMAP KEY ITERATION"        ""    ""    ""    ""    ""    ""    ""    ""    ""
"BUFFER(SORT)"        ""    ""    ""    ""    ""    ""    ""    ""    ""
"TABLE ACCESS(FULL)
SYS.SYS_TEMP_0FD9D6987_5A09774E@:Q335111002"       ""    "2"   "1"   "13"  ""    ""    ""    ""    ""
"BITMAP INDEX(RANGE SCAN)
RDW25_ADM_DM.B_WE_ROS_SLW_SKU@:Q335111002" ""    ""    ""    ""    "KEY(INLIST)" "KEY(INLIST)" "10"  ""A11"."SKU_KEY"="T1"."C0""   ""

SYS.SYS_TEMP_0FD9D6987_5A09774E is created as (from the tkprof
report):
INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */ INTO
  "SYS"."SYS_TEMP_0FD9D698F_5A09774E" SELECT /*+ SEMIJOIN_DRIVER */
  "MV_DIM_PROD_SKU"."SKU_KEY" "C0","A15"."SKU_KEY" "C1",
  "MV_DIM_PROD_SKU"."CMPY_KEY" "C2","MV_DIM_PROD_SKU"."CLASS_KEY"
"C3",
  "MV_DIM_PROD_SKU"."COLR_KEY" "C4","MV_DIM_PROD_SKU"."STYLE_KEY" "C5"
FROM
  "RDW25_ADM_DM"."MV_DIM_PROD_SKU" "MV_DIM_PROD_SKU",
  "RDW25_ADM_DM"."PROD_SKU_SEASN_MTX_DM" "A15" WHERE
  ("MV_DIM_PROD_SKU"."CLASS_KEY"=8 OR "MV_DIM_PROD_SKU"."CLASS_KEY"=16
OR
  "MV_DIM_PROD_SKU"."CLASS_KEY"=20 OR "MV_DIM_PROD_SKU"."CLASS_KEY"=21
OR
  "MV_DIM_PROD_SKU"."CLASS_KEY"=23 OR "MV_DIM_PROD_SKU"."CLASS_KEY"=42
OR
  "MV_DIM_PROD_SKU"."CLASS_KEY"=46 OR
"MV_DIM_PROD_SKU"."CLASS_KEY"=276) AND
  "A15"."SKU_KEY"="MV_DIM_PROD_SKU"."SKU_KEY" AND "A15"."SEASN_IDNT"=17