Performance problem.

   Dear Experts,
I have a performance problem at two Oracle 9.2 servers. There is a simple SQL update statement (see the attachment) which needs 6 seconds at one server and 30 minutes at another. In both cases the index range scan is used. Statistics on the tables and the indexes is calculated daily. These SQL updates are executed at the same time each morning. Tests done during different time gave the same time consuming. The volumes of data are the same on both servers. Obviously there are some differences in the configuration of these servers. What Oracle parameters / settings you can recommend my DBA to look at?

  Regards,
    Serguei.

Attachment:

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
                          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');

Here is the Oracle plan:

Operation Object Name Rows KBytes Cost

UPDATE STATEMENT Optimizer Mode=CHOOSE		1,727  	 0	5,332
  UPDATE	ETS_BSR.ETS_TRADE_POSITION
    TABLE ACCESS BY INDEX ROWID	ETS_BSR.ETS_TRADE_POSITION	1,727
33.73  	5,332
      INDEX RANGE SCAN	ETS_BSR.IX_ETS_TRADE_POSITION_DATE	279,825	0
1,144
    SORT AGGREGATE		1  	0.021
      FILTER
        TABLE ACCESS BY INDEX ROWID	ETS_BSR.ETS_MARKET_DATA	1  	0.021
	7
          AND-EQUAL
            INDEX RANGE SCAN	ETS_BSR.IX_ETS_MARKET_DATA_SECURITY_ID
            INDEX RANGE SCAN	ETS_BSR.IX_ETS_MARKET_DATA_DATE
              SORT AGGREGATE		1  	0.016
                VIEW	ETS_BSR.index$_join$_003	7  	0.109  	13
                  HASH JOIN		7  	0.109
                    INDEX RANGE SCAN
ETS_BSR.IX_ETS_MARKET_DATA_SECURITY_ID	7  	0.109  	3
                    INDEX RANGE SCAN	ETS_BSR.IX_ETS_MARKET_DATA_DATE
7  	0.109  	3
        SORT AGGREGATE		1  	0.016
          VIEW	ETS_BSR.index$_join$_003	7  	0.109  	13
            HASH JOIN		7  	0.109
              INDEX RANGE SCAN	ETS_BSR.IX_ETS_MARKET_DATA_SECURITY_ID
7  	0.109  	3
              INDEX RANGE SCAN	ETS_BSR.IX_ETS_MARKET_DATA_DATE	7
0.109  	3