Re: DW query tuning

On Fri, 05 Apr 2002 01:51:31 GMT, willjamu_at_mindspring.com (James Williams) wrote:

>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

Provide more background like primary key definitions and indexes, and reformat your sql (it is *completely* unreadable), or hire a consultant. Usenet is a volunteer business, so you should make it as easy as possible to assist you, instead of just dumping it.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Apr 04 2002 - 22:56:36 CST