Re: Performance problem.

   Dear Experts:

I solve this problem. We have Oracle 9.2.0.4.0 on the first server (the "slow" one) and 9.2.0.7.0 on the second server (the "fast" one). I made a small modification of the SQL and now it is fast on both environments.

Original SQL:

UPDATE ets_trade_position tp

    SET tp.market_price = (SELECT max(md.close_price)

             FROM ets_market_data md
             WHERE tp.security_id = md.security_id
               AND md.execution_date = (SELECT
max(tmp.execution_date)
                        FROM ets_market_data tmp
                        WHERE tmp.execution_date <=
tp.business_date    -- Line to be modifyed
                          AND tmp.security_id = tp.security_id))
    WHERE tp.market_price IS NULL
      AND tp.business_date = to_date('20-JUL-2007','DD-MON-YYYY');

Modifyed SQL:

UPDATE ets_trade_position tp

    SET tp.market_price = (SELECT max(md.close_price)

             FROM ets_market_data md
             WHERE tp.security_id = md.security_id
               AND md.execution_date = (SELECT
max(tmp.execution_date)
                        FROM ets_market_data tmp
                        WHERE tmp.execution_date <= to_date('20-
JUL-2007','DD-MON-YYYY')  -- This line modifyed
                          AND tmp.security_id = tp.security_id))
    WHERE tp.market_price IS NULL
      AND tp.business_date = to_date('20-JUL-2007','DD-MON-YYYY');

It seems the 9.2.0.7.0 server makes this optimization for free and the 9.2.0.4.0 one does not.
Anyway, thank you for help.
  Serguei. Received on Fri Jul 27 2007 - 14:18:53 CDT