Any suggestions on speeding this MView query up?

From: Robert Freeman <robertgfreeman_at_yahoo.com>
Date: Wed, 7 Feb 2007 10:25:59 -0800 (PST)
Message-ID: <84236.70698.qm@web38906.mail.mud.yahoo.com>

All,

I've got the following query that is used to create a fast refresh materialized view. I'm wondering if anyone has any ideas on tuning it. Query and execution plan below (I hope it formats ok).... I'm happy to answer any questions.

I'm seeing about 45% DISK IO wait issues, and about 55% CPU times on the query when it runs as it is.

Note that this is for a Materialized View, and it needs to be able to do fast refresh on demand. So there are limits to what we can do because of that.

Robert

  select ACTMGR.FMMINVDAYDTL.ORGUNT_SID ORGUNT_SID,   ACTMGR.DPTCHRTRES.DPTCHRT_SID DPTCHRT_SID,   ACTMGR.DPTCHRTRES.ANCDPT_SID DPT_SID,
ACTMGR.CALDTL.TMFRAM_SID TMFRAM_SID,
  ACTMGR.CALDTL.STRTDT STRTDT,
  SUM(FMMINVDAYDTL.ONSALEFLG) ONSALEFLG,   COUNT(FMMINVDAYDTL.ONSALEFLG) ONSALEFLGCNT,   SUM(FMMINVDAYDTL.SALES) SALES,
  COUNT(ACTMGR.FMMINVDAYDTL.SALES) SALESCNT,   SUM(FMMINVDAYDTL.SHRINK) SHRINK,
  COUNT(FMMINVDAYDTL.SHRINK) SHRNKCNT,
  SUM(FMMINVDAYDTL.SHRINKQTY) SHRINKQTY,   COUNT(FMMINVDAYDTL.SHRINKQTY) SHRINKQTYCNT,   SUM(FMMINVDAYDTL.SOLDQTY) SOLDQTY,
  COUNT(FMMINVDAYDTL.SOLDQTY) SOLDQTYCNT,   SUM(FMMINVDAYDTL.TCOSTFLG) TCOSTFLG,
  COUNT(FMMINVDAYDTL.TCOSTFLG) TCOSTFLGCNT,   SUM(FMMINVDAYDTL.TFC) TFC,
  COUNT(FMMINVDAYDTL.TFC) TFCCNT,
  SUM(FMMINVDAYDTL.TLC) TLC,
  COUNT(FMMINVDAYDTL.TLC) TLCCNT,
  SUM(FMMINVDAYDTL.TPC) TPC,
  COUNT(FMMINVDAYDTL.TPC) TPCCNT,
  SUM(ACTMGR.FMMINVDAYDTL.WASTE) WASTE,   COUNT(FMMINVDAYDTL.WASTE) WASTECNT,
  COUNT(*) RECORDCNT
from ACTMGR.FMMINVDAYDTL, ACTMGR.CALDTL, ACTMGR.DPTCHRTRES
where ACTMGR.CALDTL.CAL_SID = 100
and ACTMGR.CALDTL.TMFRAM_SID != 10
and ACTMGR.FMMINVDAYDTL.DT between
ACTMGR.CALDTL.STRTDT and ACTMGR.CALDTL.ENDDT and ACTMGR.FMMINVDAYDTL.dpt_sid =
ACTMGR.DPTCHRTRES.RPTDPT_SID
group by ACTMGR.FMMINVDAYDTL.ORGUNT_SID,

ACTMGR.DPTCHRTRES.DPTCHRT_SID, 
ACTMGR.DPTCHRTRES.ANCDPT_SID,
ACTMGR.CALDTL.TMFRAM_SID, ACTMGR.CALDTL.STRTDT;

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 1628439725

| Id  | Operation                | Name         | Rows
 | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT         |              |  

425M|    34G|       |    11M  (9)| 03:28:08 |


|   1 |  HASH GROUP BY           |              |  

425M|    34G|    80G|    11M  (9)| 03:28:08 |
|*  2 |   HASH JOIN              |              |  
425M|    34G|  1960K|  2094K (18)| 00:36:31 |


|   3 |    TABLE ACCESS FULL     | DPTCHRTRES   |

76924 |  1051K|       |    86  (17)| 00:00:01 |


|   4 |    MERGE JOIN            |              |  

116M|  8248M|       |  1407K (23)| 00:24:33 |


|   5 |     SORT JOIN            |              |  

684 | 15048 |       |    11  (19)| 00:00:01 |
|*  6 |      INDEX FAST FULL SCAN| CALDTL1      |  
684 | 15048 |       |    10  (10)| 00:00:01 |
|*  7 |     FILTER               |              |     
 |       |       |            |          |
|*  8 |      SORT JOIN           |              |   
68M|  3389M|    10G|  1207K (10)| 00:21:04 |


|   9 |       TABLE ACCESS FULL  | FMMINVDAYDTL |   

68M|  3389M|       |   180K (16)| 00:03:09 |
-------------------------------------------------------------------------------------------------

Robert G. Freeman
Author:
Portable DBA: Oracle (Oracle Press)
Oracle Database 10g New Features (Oracle Press) Oracle9i RMAN Backup and Recovery (Oracle Press) Oracle9i New Features (Oracle Press)
Oracle Replication (Rampant Tech Press)
Mastering Oracle8i (Sybex)
Oracle8 to 8i Upgrade Exam Cram (Coriolis <RIP>) Oracle 7.3 to 8 Upgrade Exam Cram (Coriolis <RIP>)

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 07 2007 - 12:25:59 CST