Browse oracle-l: This Month's Archive • Main Archive Page • Related posts • Previous by Date • Next by Date

Query performance issue

From: "Schauss, Peter" <peter.schauss@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 4 Jun 2008 09:08:29 -0500

Oracle 8.1.7.4 - Solaris 5.9 - Cost based optimizer

I have a query which takes 28 minutes to run on my production system and
about a two minutes to run on my QA database.

The query looks like this:

Select ...
FROM
        siebel.W_ACTIVITY_F,
        siebel.W_REGION_D,
        siebel.W_LOV_D,
        siebel.W_DAY_D,
        siebel.W_PARAM_G
WHERE 
        W_ACTIVITY_F.REGION_WID = W_REGION_D.ROW_WID
AND 
        W_ACTIVITY_F.STATUS_WID = W_LOV_D.ROW_WID 
AND
        W_ACTIVITY_F.ACTUAL_START_WID = W_DAY_D.ROW_WID
GROUP BY 
        W_ACTIVITY_F.RESOLUTION_WID, 
        W_ACTIVITY_F.PRIORITY_WID, 
        W_ACTIVITY_F.CATEGORY_WID, 
        W_ACTIVITY_F.ACT_TYPE_WID, 
        W_ACTIVITY_F.STATUS_WID, 
        W_ACTIVITY_F.OWNER_WID, 
        W_ACTIVITY_F.REGION_WID, 
        W_DAY_D.CAL_YEAR,
        W_DAY_D.CAL_MONTH;

Explain plan (on both databases) looks like this:

| SELECT STATEMENT                                          |
|     3M|  407M|  50788 |      |      |            
|  SORT GROUP BY                                            |
|     3M|  407M|  50788 |      |      |            
|   HASH JOIN                                               |
|     3M|  407M|   1658 |      |      |            
|    TABLE ACCESS FULL                                      |W_DAY_D
|    11K|  149K|     15 |      |      |            
|    HASH JOIN                                              |
|     3M|  359M|   1536 |      |      |            
|     TABLE ACCESS FULL                                     |W_LOV_D
|    25K|  468K|      8 |      |      |            
|     NESTED LOOPS                                          |
|     3M|  289M|   1292 |      |      |            
|      MERGE JOIN CARTESIAN                                 |
|   645 |   13K|      2 |      |      |            
|       TABLE ACCESS FULL                                   |W_PARAM_G
|     1 |   10 |      1 |      |      |            
|       SORT JOIN                                           |
|   645 |    7K|      1 |      |      |            
|        TABLE ACCESS FULL                                  |W_REGION_D
|   645 |    7K|      1 |      |      |            
|      TABLE ACCESS BY INDEX ROWID
|W_ACTIVITY_F   |     3M|  209M|      2 |      |      |            
|       INDEX RANGE SCAN
|W_ACTIVITY_F_F |     3M|      |      1 |      |      |    

(Note table W_PARAM_G contains only one row so the cartesian join is not
as bad as it sounds.)      

Row counts for w_day_d, w_lov_d, and w_region_d within 10% of each other
on both databases.
The row count for w_activity_f is 3x10**6 on production and 2x10**6 on
qa.

The 10046 trace for production lists 3x10**5 db file file sequential
read waits while the trace for QA lists 2x10**4.

Would the 50% difference in the size of my w_activity_f table be
sufficient to account for the 10-fold increase in the number of
sequential read waits and the corresponding 10-fold difference in run
time or should I be looking for hardware or other issues?

Thanks,
Peter Schauss
  
--
http://www.freelists.org/webpage/oracle-l

Follow-Ups:

RE: Query performance issue
From: Elliott, Patrick

RE: Query performance issue
From: Mark W. Farnham

Query performance issue

Other related posts: