Re: DW query tuning

Have you tried adding the INDEX_COMBINE hint?

Regards,
-Darren

"James Williams" <willjamu_at_mindspring.com> wrote in message news:3cad0302.1861086_at_news.mindspring.com...
> Running Oracle 8.1.7.3 on Solaris.
>
> Trying to get the two below queries to run better. Have not been able
> to get the below to pick up their bitmapped indexes on the fact table
> columns. Users want more as usual. Fresh run of dbms_stats to gather
> statistics for this small warehouse.
>
> QL> list
> 1 SELECT
> 2 SUM(FACT_SO_SUMMARY.NON_CGI_COUNT),
> 3 AVG(DECODE(FACT_SO_SUMMARY.NON_CGI_COUNT,1,
> 4 FACT_SO_SUMMARY.ELAPSED_WORK_TIME,NULL)),
> 5 DIM_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
> 6 DIM_DATE2.YEAR,
> 7 LPAD(TO_CHAR(DIM_DATE2.MONTH_NUMBER),2,0)||'-'||
> 8 SUBSTR(DIM_DATE2.MONTH_NAME,1,3),
> 9 SUM(FACT_SO_SUMMARY.COMPLETED_SO_COUNT),
> 10 SUM(FACT_SO_SUMMARY.CGI_COUNT)/SUM(FACT_SO_SUMMARY.CGI_COUNT +
> 11 FACT_SO_SUMMARY.NON_CGI_COUNT),
> 12 AVG(DECODE(FACT_SO_SUMMARY.NON_CGI_COUNT,1,
> 13 FACT_SO_SUMMARY.ELAPSED_ONSITE_TIME,NULL)),
> 14 AVG(DECODE(FACT_SO_SUMMARY.NON_CGI_COUNT,1,
> 15 FACT_SO_SUMMARY.ELAPSED_ENROUTE_TIME,NULL))
> 16 FROM
> 17 FACT_SO_SUMMARY,
> 18 DIM_BUSINESS_UNIT,
> 19 DIM_DATE DIM_DATE2
> 20 WHERE
> 21 (FACT_SO_SUMMARY.COMPLETED_DATE=DIM_DATE2.DATE_ID)
> 22 AND
> 23
> (FACT_SO_SUMMARY.BUSINESS_UNIT_ID=DIM_BUSINESS_UNIT.BUSINESS_UNIT_ID)
> 24 AND
> 25 ((DIM_DATE2.GREGORIAN_DATE BETWEEN ADD_MONTHS
> 26 (TRUNC(SYSDATE,'MONTH'),-13) AND SYSDATE ))
> 27 GROUP BY
> 28 DIM_BUSINESS_UNIT.BUSINESS_UNIT_DESC, DIM_DATE2.YEAR,
> 29 LPAD(TO_CHAR(DIM_DATE2.MONTH_NUMBER),2,0)||'-'||
> 30* SUBSTR(DIM_DATE2.MONTH_NAME,1,3)
> SQL>
>
> 39 rows selected.
>
> Elapsed: 00:00:17.96
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=398 Card=11812 Byt
> es=791404)
>
> 1 0 SORT (GROUP BY) (Cost=398 Card=11812 Bytes=791404)
> 2 1 HASH JOIN (Cost=225 Card=31130 Bytes=2085710)
> 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'DIM_DATE' (Cost=33 C
> ard=69 Bytes=1932)
>
> 4 3 INDEX (RANGE SCAN) OF 'DIM_DATE_IND02' (NON-UNIQUE)
> (Cost=6 Card=69)
>
> 5 2 HASH JOIN (Cost=191 Card=409200 Bytes=15958800)
> 6 5 TABLE ACCESS (FULL) OF 'DIM_BUSINESS_UNIT' (Cost=1 C
> ard=4 Bytes=40)
>
> 7 5 TABLE ACCESS (FULL) OF 'FACT_SO_SUMMARY' (Cost=189 C
> ard=409200 Bytes=11866800)
>
>
>
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 12 db block gets
> 3112 consistent gets
> 2203 physical reads
> 0 redo size
> 7743 bytes sent via SQL*Net to client
> 647 bytes received via SQL*Net from client
> 4 SQL*Net roundtrips to/from client
> 4 sorts (memory)
> 0 sorts (disk)
> 39 rows processed
>
>
> SQL> get oess4
> 1 SELECT
> 2 DIM_DATE2.YEAR,
> 3 AVG(DECODE(DIM_SERVICE_ORDER.CUST_REQUEST_FLAG,'Y',
> 4 FACT_SO_SUMMARY.DAYS_TO_SERVICE,NULL)),
> 5 DIM_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
> 6 'Q'||TO_CHAR(DIM_DATE2.QUARTER_NUMBER)
> 7 FROM
> 8 DIM_DATE DIM_DATE2,
> 9 FACT_SO_SUMMARY,
> 10 DIM_SERVICE_ORDER,
> 11 DIM_BUSINESS_UNIT
> 12 WHERE
> 13 (FACT_SO_SUMMARY.SERVICE_ORDER_ID =
> DIM_SERVICE_ORDER.SERVICE_ORDER_ID) AND
> 14 (FACT_SO_SUMMARY.COMPLETED_DATE=DIM_DATE2.DATE_ID) AND
> 15 (FACT_SO_SUMMARY.BUSINESS_UNIT_ID =
> DIM_BUSINESS_UNIT.BUSINESS_UNIT_ID) AND
> 16 ((DIM_DATE2.GREGORIAN_DATE BETWEEN TRUNC(SYSDATE, 'YEAR') AND
> SYSDATE)
> 17 AND DIM_SERVICE_ORDER.CUST_REQUEST_FLAG = 'Y' AND
> 18 DIM_SERVICE_ORDER.WORK_COMPLETED_FLAG = 'Y' AND
> 19 DIM_SERVICE_ORDER.JOB_CODE IN
> 20 ('CC','LU', 'MOM01', 'MOM05',
> 21 'MOV01', 'MOV03', 'MOV05', 'MOV06',
> 22 'MOV08', 'MOV09', 'MOV11', 'MOV12',
> 23 'MOV13', 'MOV14', 'MOV17', 'OFF', 'ON',
> 24 'ON', 'UB', 'MOS01', 'MOP01'))
> 25 GROUP BY
> 26 DIM_DATE2.YEAR,
> 27 DIM_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
> 28* 'Q'||TO_CHAR(DIM_DATE2.QUARTER_NUMBER)
>
>
> Elapsed: 00:00:14.36
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=627 Card=24 Bytes=
> 1440)
>
> 1 0 SORT (GROUP BY) (Cost=627 Card=24 Bytes=1440)
> 2 1 HASH JOIN (Cost=613 Card=78 Bytes=4680)
> 3 2 HASH JOIN (Cost=194 Card=2256 Bytes=103776)
> 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'DIM_DATE' (Cost=2
> Card=5 Bytes=100)
>
> 5 4 INDEX (RANGE SCAN) OF 'DIM_DATE_IND02' (NON-UNIQUE
> ) (Cost=2 Card=5)
>
> 6 3 HASH JOIN (Cost=191 Card=409200 Bytes=10639200)
> 7 6 TABLE ACCESS (FULL) OF 'DIM_BUSINESS_UNIT' (Cost=1
> Card=4 Bytes=40)
>
> 8 6 TABLE ACCESS (FULL) OF 'FACT_SO_SUMMARY' (Cost=189
> Card=409200 Bytes=6547200)
>
> 9 2 TABLE ACCESS (FULL) OF 'DIM_SERVICE_ORDER' (Cost=418 C
> ard=13185 Bytes=184590)
>
>
>
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 26 db block gets
> 9954 consistent gets
> 7669 physical reads
> 0 redo size
> 828 bytes sent via SQL*Net to client
> 425 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 3 rows processed
Received on Fri Apr 05 2002 - 09:36:50 CST