Optimise a Query Which is taking too long to Run

Question

Hi All,

Im Using toad for Oracle to run a query which is taking much too long to run, sometimes over 15 minutes.

The query is pulling memos which are left to be approved by managers. The query is not bringing back alot of rows. Typically when it is run it will return about 30 or 40 rows. The query needs to access a few tables for its information so I'm using alot of joins to get this information.

I have attached my query below. If anyone can help with optimising this query I would be very greatfull.

Query:

SELECT (e.error_Description || DECODE(t.trans_Comment, 'N', '', '','', ' - ' || t.trans_Comment)) AS Title,
       t.Date_Time_Recorded AS Date_Recorded,
       DECODE(t.user_ID,0,'System',(SELECT Full_Name FROM employee WHERE t.user_Id = user_id)) AS Recorded_by,
           DECODE(t.user_ID,0, Dm_General.getCalendarShiftName(t.Date_Time_Recorded), (SELECT shift FROM employee WHERE t.user_Id = user_id)) AS Shift,
          l.Lot_Number AS entity_number,
          ms.Line_Num,
          'L' AS Entity_Type, 
           t.entity_id, l.lot_Id AS Lot_Id
      FROM DAT_TRANSACTION t
      JOIN ADM_ERRORCODES e ON e.error_id = t.error_id
      JOIN ADM_ACTIONS a ON a.action_id = t.action_id,
           DAT_LOT l
                 INNER JOIN Status s ON l.Lot_Status_ID = s.Status_ID,
          DAT_MASTER ms
                INNER JOIN ADM_LINE LN ON ms.Line_Num = LN.Line_Num
      WHERE
             (e.memo_req = 'Y' OR a.memo_req = 'Y')
          AND ms.Run_type_Id = Constants.Runtype_Production_Run --Production Run type
           AND s.completed_type NOT IN ('D', 'C', 'R') -- Destroyed /closed / Released
          AND LN.GEN = '2GT'
           AND (NOT EXISTS (SELECT 1 FROM LNK_MEMO_TRANS lnk, DAT_MEMO m
                           WHERE lnk.Trans_ID = t.trans_id AND lnk.Memo_ID = m.Memo_ID
                           AND NVL(m.approve, 'Y') = 'Y'))--If it's null, it's 
                                                  been created and is awaiting approval
          AND l.Master_ID = ms.Master_ID
           AND t.Entity_ID = l.Lot_ID
           AND t.Entity_Type IN ('L', 'G');

Answer 1

The usual cause for bad performance of queries is that Oracle can't find an appropriate index. Use EXPLAIN PLAN with TOAD so Oracle can tell you what it thinks the best way to execute the query. That should give you some idea when it uses indexes and when not.

For general pointers, see http://www.orafaq.com/wiki/Oracle%5Fdatabase%5FPerformance%5FTuning%5FFAQ

See here for EXPLAIN PLAN.

Answer 2

You have some function calls in your SQL:

• dm_general.getcalendarshiftname(t.date_time_recorded)
• constants.runtype_production_run

Function calls are slow in SQL, and depending on the query plan may get called redundantly many times - e.g. computing dm_general.getcalendarshiftname for rows that end up being filtered out of the results.

To see if this is a significant factor, try replacing the function calls with literal constants temporarily and see if the performance improves.

The number of function calls can sometimes be reduced by restructuring the query like this:

select /*+ no_merge(v) */ a, b, c, myfunction(d)
from
   ( select a, b, c, d
     from my_table 
     where ...
   ) v;

This ensures that myfunction is only called for rows that will appear in the results.

Answer 3

I have replaced function calls with literal constants and this speeds it up by only a second or 2. The query is still taking about about 50 seconds to run.

Is there anything I can do around the Joins to help spped this up. Have a used the INNER JOIN function correctly here.

Im not really sure I understand what you mean about the below or how to use it. I get the error d invalid identifier when I try to call the function in the second select select /*+ no_merge(v) */ a, b, c, myfunction(d) from ( select a, b, c, d from my_table where ... ) v;

Any other views would be greatly appreciated

Answer 4

Before we can say anything sensible, we have to take a look at where time is being spent. And that means you have to collect some information first.

Therefore, my standard reaction to a question like this, is this one: http://forums.oracle.com/forums/thread.jspa?threadID=501834

Regards, Rob.