Question about query performance issue

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

Other related posts: