DW query tuning

From: James Williams <willjamu_at_mindspring.com>
Date: Fri, 05 Apr 2002 01:51:31 GMT
Message-ID: <3cad0302.1861086@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 Thu Apr 04 2002 - 19:51:31 CST