How to optimize the response time for following Oracle 10g query?

Question

How to optimize the response time for the following query:

    SELECT
  /*+parallel */
  cc.customer_id      AS customer_id,
  cc.title1           AS title1,
  cc.forename1        AS forename1,
  cc.forename2        AS forename2,
  cc.surname1         AS surname1,
  cc.surname2         AS surname2,
  cc.company_flag     AS company_flag,
  cc.COMPANY_NAME     AS COMPANY_NAME,
  ext_customer_code   AS code,
  cc.customer_type    AS category,
  ca.address1         AS address1,
  ca.address2         AS address2,
  ca.address3         AS address3,
  ca.address4         AS address4,
  ca.address5         AS address5,
  ca.postcode         AS postcode,
  ca.postcode_prefix  AS postcode_prefix,
  ca.country_code     AS country_code,
  ca.town             AS town,
  ca.county           AS county,
  cc.client_id        AS client_id,
  cc.location_id      AS location_id,
  cc.data_source_id   AS dataSource_id,
  cc.SALUTATION       AS salutation,
  cc.ADDRESS_MATCHKEY AS addressMatchKey,
  ccv.vehicle_id      AS vehicle_id,
  cc.customer_name    AS customer_name,
  cv.REG_NUM          AS REG_NUM
FROM OEM.CDB_CUSTOMERS cc,
  OEM.CDB_ADDRESSES ca,
  OEM.CDB_CUSTOMER_VEHICLES ccv,
  OEM.CDB_VEHICLES cv
WHERE cc.client_id      = ca.client_id
AND cc.address_matchkey = ca.address_matchkey
AND cc.location_id      = ca.location_id
AND cc.customer_id      = ccv.customer_id
AND cc.client_id        = ccv.client_id
AND cc.LOCATION_ID      = ccv.LOCATION_ID
AND ccv.vehicle_id      = cv.vehicle_id
AND ccv.client_id       = cv.CLIENT_ID
AND ccv.LOCATION_ID     = cv.LOCATION_ID
AND cv.LOCATION_ID      = 1
AND ccv.rejection_flag  = 'N'
AND ccv.owner_status   IS NOT NULL
AND cc.client_id        = 1776
AND ca.client_id        = 1776
AND ca.country_code     ='UK'
AND ca.area_id         IN
  ( SELECT start_code postcode_id FROM MDB_FORMAT WHERE NAME = 'PC%1776'
  )
AND cv.model_id IN
  ( SELECT m.start_code model_id FROM MDB_FORMAT m WHERE m.NAME = '10_RWMOD'
  )
ORDER BY customer_name,
  town,
  postcode

Here's execution plan:

| Id  | Operation                        | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                
|   0 | SELECT STATEMENT                 |                          |     1 |   317 |   320   (1)| 00:00:04 |                                                                                                                                                                                                
|   1 |  SORT ORDER BY                   |                          |     1 |   317 |   320   (1)| 00:00:04 |                                                                                                                                                                                                
|   2 |   NESTED LOOPS                   |                          |     1 |   317 |   319   (1)| 00:00:04 |                                                                                                                                                                                                
|   3 |    NESTED LOOPS                  |                          |     1 |   299 |   319   (1)| 00:00:04 |                                                                                                                                                                                                
|   4 |     NESTED LOOPS                 |                          |     1 |   273 |   319   (1)| 00:00:04 |                                                                                                                                                                                                
|*  5 |      HASH JOIN                   |                          |     8 |  1992 |   314   (1)| 00:00:04 |                                                                                                                                                                                                
|   6 |       TABLE ACCESS BY INDEX ROWID| CDB_ADDRESSES            |    10 |  1060 |     1   (0)| 00:00:01 |                                                                                                                                                                                                
|   7 |        NESTED LOOPS              |                          |   501 | 62124 |    30   (0)| 00:00:01 |                                                                                                                                                                                                
|*  8 |         INDEX RANGE SCAN         | MDB_FORMAT_PK            |    48 |   864 |     1   (0)| 00:00:01 |                                                                                                                                                                                                
|*  9 |         INDEX RANGE SCAN         | CDB_ADDRESSES_SMM_IDX1   |    10 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                                
|  10 |       TABLE ACCESS BY INDEX ROWID| CDB_CUSTOMERS            | 28544 |  3484K|   283   (0)| 00:00:04 |                                                                                                                                                                                                
|* 11 |        INDEX RANGE SCAN          | CDB_CUSTOMERS_UK1        | 28544 |       |    38   (0)| 00:00:01 |                                                                                                                                                                                                
|* 12 |      TABLE ACCESS BY INDEX ROWID | CDB_CUSTOMER_VEHICLES    |     1 |    24 |     1   (0)| 00:00:01 |                                                                                                                                                                                                
|* 13 |       INDEX RANGE SCAN           | CDB_CUSTOMER_VEHICLES_PK |     1 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                                
|  14 |     TABLE ACCESS BY INDEX ROWID  | CDB_VEHICLES             |     1 |    26 |     1   (0)| 00:00:01 |                                                                                                                                                                                                
|* 15 |      INDEX RANGE SCAN            | CDB_VEHICLES_PREF_IND    |     1 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                                
|* 16 |    INDEX RANGE SCAN              | MDB_FORMAT_PK            |     1 |    18 |     1   (0)| 00:00:01 |                                                                                                                                                                                                

Predicate Information (identified by operation id):

   5 - access("CC"."CLIENT_ID"="CA"."CLIENT_ID" AND "CC"."ADDRESS_MATCHKEY"="CA"."ADDRESS_MATCHKEY"                                                                                                                                                                                                          
              AND "CC"."LOCATION_ID"="CA"."LOCATION_ID")                                                                                                                                                                                                                                                     
   8 - access("NAME"='PC%1776')                                                                                                                                                                                                                                                                              
   9 - access("CA"."CLIENT_ID"=1776 AND "CA"."AREA_ID"=TO_NUMBER("START_CODE") AND                                                                                                                                                                                                                           
              "CA"."COUNTRY_CODE"='UK' AND "CA"."LOCATION_ID"=1)                                                                                                                                                                                                                                             
  11 - access("CC"."CLIENT_ID"=1776 AND "CC"."LOCATION_ID"=1)                                                                                                                                                                                                                                                
  12 - filter("CCV"."OWNER_STATUS" IS NOT NULL AND "CCV"."REJECTION_FLAG"='N')                                                                                                                                                                                                                               
  13 - access("CCV"."CLIENT_ID"=1776 AND "CC"."CUSTOMER_ID"="CCV"."CUSTOMER_ID" AND                                                                                                                                                                                                                          
              "CCV"."LOCATION_ID"=1)                                                                                                                                                                                                                                                                         
       filter("CCV"."LOCATION_ID"=1)                                                                                                                                                                                                                                                                         
  15 - access("CCV"."VEHICLE_ID"="CV"."VEHICLE_ID" AND "CV"."CLIENT_ID"=1776 AND                                                                                                                                                                                                                             
              "CV"."LOCATION_ID"=1)                                                                                                                                                                                                                                                                          
  16 - access("M"."NAME"='10_RWMOD')                                                                                                                                                                                                                                                                         
       filter("CV"."MODEL_ID"=TO_NUMBER("M"."START_CODE"))                                                                                                                                                                                                                                                   
41 rows selected

Answer 1

Step 1: Make sure the query is correct. I'm not sure what your data model is, but I noticed you say "SELECT start_code postcode_id" and "SELECT m.start_code model_id". Do you mean to alias the same field for two purposes?

Step 2: Analyze the tables. Out-of-date statistics might cause the optimizer to chose a bad plan.

Step 3: Write your join conditions in the FROM clause (e.g., "table1 JOIN table2 ON (table1.id = table2.id)). This makes it obvious to you, the optimizer, and anyone reading your code what you're trying to do. It's easier to rearrange something that's already organized.

Step 4: Repeat Step 1.

Step 5: We'll talk about that when you've gotten there.

Answer 2

Usually its a good idea to mask your customer's info, like change the company name to CO or XXX, you don't even need the schema name to optimize the query.

Answer 3

"It returns about 18000 to 8000 rows depending on filter criteria." The explain plan indicates that it expects to return 1 row (and that's generally because it NEVER expects to return 0 rows).

I expect some of the cardinality estimates are wrong. One item that may confuse it is this

WHERE cc.client_id      = ca.client_id
...
AND cc.client_id        = 1776
AND ca.client_id        = 1776

Items 9 and 11 both have the 1776 filter, but then item 5 makes sure they match. This is redundant, but may be affecting the cardinality estimate.