I am using Oracle 9i Database on Windows 2000 Server. When I ran the query for expensive SQL statements in my production database I found the below statement. How do I tune this query?
UPDATE EQUIPMENT_USES SET EXPORT_RELEASE_NBR=:b1,CHANGED=DB_DATE
> ,CHANGER=USER WHERE GKEY IN (SELECT EQUSE_GKEY FROM BL_EQUIPME
> NT WHERE BL_GKEY = :b2 AND CATEGORY IN ( 'I','T','S','R' ))
Are you sure that the query needs tuning? Just because it's identified as "expensive" does not mean that there is a problem. Are users satisfied with the amount of time it takes them to perform operations using your database applications? If the answer to that question is "no," then you should trace the operations that perform unacceptably, determine from the formatted trace file which database operations consume the most time, and then determine the reason that they consume so much time.