How to tune this query:
- From: genegurevich@xxxxxxxxxxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 12 Dec 2006 15:33:05 -0600
Hi everybody:
I'm trying to tune the following SQL and am looking for any suggestion. I
am using oracle 9204:
create table ZZ2MQ00 as
select a13.OFFR_CALL_ID OFFR_CALL_ID
from
CCSREP.DT a12,
CCSREP.OFFR a13,
CCSREP.PRDCT a14,
CCSREP.PRDCT_GRP a15
where a13.PROC_MTH_VAL = a12.YR_MTH_NBR and
a13.PRDCT_ID = a14.PRDCT_ID and
a14.PRDCT_GRP_DSC = a15.PRDCT_GRP_DSC and
a14.PRDCT_GRP_ID = a15.PRDCT_GRP_ID
and (a13.PREFR_IND in ('Y')
and a13.SCRN_DSPLY_SEQ_NBR = 1
and (not a15.PRDCT_GRP_DSC in ('product 1','product 2','product 3)
and ((a15.PRDCT_GRP_DSC in ('Rewards')
and a13.OFFR_RSPNS_TYP_CDE in ('A'))
or (a13.PRDCT_ID in ('058')
and a13.OFFR_RSPNS_TYP_CDE in ('T')))
and (a12.YR_NBR = 2006 and a12.MTH_NBR = 11))
group by a13.OFFR_CALL_ID
having count(a13.OFFR_CALL_ID) > 0.0
/
I have executed the explain plan:
---------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 1 | 86 |
9 | | |
| 1 | LOAD AS SELECT | | | |
| | |
|* 2 | FILTER | | | |
| | |
| 3 | SORT GROUP BY | | 1 | 86 |
9 | | |
| 4 | NESTED LOOPS | | 1 | 86 |
6 | | |
| 5 | MERGE JOIN CARTESIAN | | 1 | 62 |
5 | | |
| 6 | NESTED LOOPS | | 1 | 42 |
3 | | |
|* 7 | TABLE ACCESS FULL | PRDCT | 1 | 24 |
2 | | |
|* 8 | TABLE ACCESS BY INDEX ROWID| PRDCT_GRP | 1 | 18 |
1 | | |
|* 9 | INDEX UNIQUE SCAN | PRDCT_GRP_PK | 1 | |
| | |
| 10 | BUFFER SORT | | 1 | 20 |
4 | | |
|* 11 | TABLE ACCESS BY INDEX ROWID| DT | 1 | 20 |
2 | | |
|* 12 | INDEX RANGE SCAN | DT_FK2_X | 1 | |
1 | | |
| 13 | PARTITION RANGE ITERATOR | | | |
| KEY | KEY |
|* 14 | INDEX FAST FULL SCAN | OFFR_ALT8 | 20 | 480 |
3 | KEY | KEY |
This does not look too bad. The tables that are joined via a cartesian join
are small. The large table (OFFR) is
being acessed via an index OFFR_ALT8. The partition pruning seem to be used
too. The index itself is about 15G
and it has 14 partitions.
When the query is running I see a lot of waits for the db file scattered
read . The files are the ones in the tablesaces
where the index OFFR_ALT8 is located. When I check the long ops (via OEM) I
see about 30+ full scans of that index
and nothing else. vmstat shows 1% waits for IO and 80%+ idle CPU.
Any thoughts of what could be the reason for the slow performance?
-
thank you
Gene Gurevich
--
//www.freelists.org/webpage/oracle-l
Other related posts:
