How to tune this query:

From: <genegurevich_at_discoverfinancial.com>
Date: Tue, 12 Dec 2006 15:33:05 -0600
Message-ID: <OF5C605CA3.B4375EA0-ON86257242.0073402F-86257242.007660F0@discoverfinancial.com>

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

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

Received on Tue Dec 12 2006 - 15:33:05 CST