Any suggestions on speeding this MView query up?
- From: Robert Freeman <robertgfreeman@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 7 Feb 2007 10:25:59 -0800 (PST)
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>)
--
//www.freelists.org/webpage/oracle-l
Other related posts:
