Datawarehousing query performance

From: James A. Williams <jwilliam_at_aglresources.com>
Date: 4 Apr 2002 09:53:42 -0800
Message-ID: <5003a2b9.0204040953.1bf1395b_at_posting.google.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:53:42 CEST