In-Reply-To: <FBEIIHEAOIFCBBNIIFOGKEDECGAA.mwf@rsiz.com>
Subject: How to tune this query:
To: oracle-l@freelists.org
Message-ID: <OF5C605CA3.B4375EA0-ON86257242.0073402F-86257242.007660F0@discoverfinancial.com>
From: genegurevich@discoverfinancial.com
Date: Tue, 12 Dec 2006 15:33:05 -0600
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: genegurevich@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