RE: Query performance issue

From: Elliott, Patrick <patrick.elliott_at_medtronic.com>
Date: Wed, 4 Jun 2008 11:01:09 -0500
Message-ID: <3B8B6A1700202C43A89D61CE495C894E0F0BE84F5D@MSPM1BMSGM103.ent.core.medtronic.com>


Here are the things I would check:

1)  Check your tables to see if they are fragmented.  Full table scans will take longer when you have a lot of unused space.  If your QA database was populated with an export/import, then this might explain the difference because an import reorganizes the tables.
2)  You may have rebuilt the indexes in QA also which would make them perform better.
3)  Are you contending with online activity in production that would slow the query down?
4)  Are their chained/migrated rows in production (see number 1 above)?  Index lookups, and to a lesser extent full table scans, are made slower by chained/migrated rows.
5)  Is the hardware identical in QA?
6)  Are all of the init.ora parameters identical.  (i.e.  db_cache_size)

You really need more than just an explain plan to tell you why you are seeing differences. I would recommend turning on trace and/or running a tkprof if needed.

Pat

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Schauss, Peter Sent: Wednesday, June 04, 2008 9:08 AM
To: oracle-l_at_freelists.org
Subject: Query performance issue

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



[CONFIDENTIALITY AND PRIVACY NOTICE]

Information transmitted by this email is proprietary to Medtronic and is intended for use only by the individual or entity to which it is addressed, and may contain information that is private, privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please delete this mail from your records.
 
To view this notice in other languages you can either select the following link or manually copy and paste the link into the address bar of a web browser: http://emaildisclaimer.medtronic.com
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jun 04 2008 - 11:01:09 CDT

Original text of this message