Re: Performance problem.

On 25 Jul, 15:08, Serguei.Goumeni..._at_cibc.ca wrote:
> 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

What operating system? Memory parameters/differences spring to mind.

HTH -g Received on Wed Jul 25 2007 - 09:24:21 CDT