Question about query performance issue

From: Michael Schmitt <mschmitt_at_uchicago.edu>
Date: Sun, 22 Feb 2009 21:17:12 -0600
Message-ID: <E37E556CF8A6C44381D2DA9FC354D241F636185A38_at_EVS03.ad.uchicago.edu>

Hi All,

I am having an issue on an Oracle 9.2.0.7 database that I am looking for some help with. We have a query that gets formulated by an application and runs for over 6 minutes. If we use the ordered hint on the query it runs around 1 second. I am listing the query, the plans, and some random information below. Since we do not have any way to change the SQL, I am looking for any suggestions on how we can somehow get oracle to use the second plan that is running in a fraction of the time. Stats have been updated on all of the objects

Thanks in advance

Plan from long running query:

Elapsed: 00:06:13.68 (84973364 consistent gets) Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=51 Card=24 Bytes=2616)    1 0 SORT (UNIQUE) (Cost=49 Card=24 Bytes=2616)

   2    1     NESTED LOOPS (Cost=47 Card=24 Bytes=2616)
   3    2       NESTED LOOPS (Cost=47 Card=95369 Bytes=9155424)
   4    3         HASH JOIN (Cost=47 Card=95369 Bytes=8773948)
   5    4           TABLE ACCESS (FULL) OF 'MEDIARENDITIONS' (Cost=4 Card=1107 Bytes=7749)
   6    4           NESTED LOOPS (Cost=42 Card=517 Bytes=43945)
   7    6             INDEX (UNIQUE SCAN) OF 'MEDIATYPES_PK' (UNIQUE)
   8    6             TABLE ACCESS (FULL) OF 'OBJECTS' (Cost=41 Card=517 Bytes=42394)
   9    3         INDEX (UNIQUE SCAN) OF 'MEDIAMASTER_PK' (UNIQUE)
  10    2       INDEX (UNIQUE SCAN) OF 'MEDIAXREFS_PK' (UNIQUE)

Plan when we use the hint:
Elapsed: 00:00:02.41
Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=53 Card=23 Bytes=2507)    1 0 SORT (UNIQUE) (Cost=51 Card=23 Bytes=2507)

   2    1     NESTED LOOPS (Cost=49 Card=23 Bytes=2507)
   3    2       HASH JOIN (Cost=49 Card=135 Bytes=14310)
   4    3         TABLE ACCESS (FULL) OF 'MEDIARENDITIONS' (Cost=4 Card=1106 Bytes=7742)
   5    3         NESTED LOOPS (Cost=44 Card=811 Bytes=80289)
   6    5           HASH JOIN (Cost=44 Card=811 Bytes=77045)
   7    6             TABLE ACCESS (FULL) OF 'OBJECTS' (Cost=41 Card=517 Bytes=42394)
   8    6             INDEX (FAST FULL SCAN) OF 'MEDIAXREFS_PK' (UNIQUE) (Cost=2 Card=6631 Bytes=86203)
   9    5           INDEX (UNIQUE SCAN) OF 'MEDIAMASTER_PK' (UNIQUE)
  10    2       INDEX (UNIQUE SCAN) OF 'MEDIATYPES_PK' (UNIQUE)

Query as formulated by Application

  SELECT   DISTINCT OBJ.ObjectID,
                    OBJ.ObjectNumber,
                    OBJ.DepartmentID SecurityCategoryID,
                    OBJ.SortNumber

    FROM dbo.Objects OBJ,

   dbo.MediaXrefs MX,
   dbo.MediaMaster MM ,
   dbo.MediaRenditions MR ,
   dbo.MediaTypes MT
   WHERE   MR.MediaTypeID =
              MT.MediaTypeID
           AND MM.MediaMasterID =
                 MR.MediaMasterID
           AND MX.MediaMasterID = MM.MediaMasterID
           AND OBJ.ObjectID = MX.ID
           AND ( (    OBJ.ObjectID > -1
                  AND (MT.MediaTypeID = 5)
                  AND OBJ.ObjectStatusID = 1)
                AND OBJ.IsTemplate = 0
                AND MX.TableID = 108)

ORDER BY OBJ.SortNumber ASC

Data:
Objects=17566
Mediatypes=9
Mediarenditions=6634
Mediamaster=6634
Mediaxrefs=6631

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

Received on Sun Feb 22 2009 - 21:17:12 CST