Re: Performance problem.

On Jul 25, 7:08 am, 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

Mark's suggestions are probably the key to solving your problem, but here are some other questions:

Are there other performance problems on the problem server? What are the top five waits on each server? Is there some difference in how others are accessing that data between the servers? Are there differences in how the init.ora parameters are defined between the servers? What exactly are the configuration differences? What is your update waiting for?

Have you done any massive loads or deletes? Have you rebuilt the index?

What are the _exact_ versions of Oracle and the OS?

Have you checked I/O performance on each?

It's possible, for example, that you have some hot block in the SGA that everybody is trying to access at the same time, causing performance to go horribly bad beyond a certain threshold. There was a recent thread here where it turned out a batched statement was taking longer than the batch cycle, so it was dogpiling on itself.

Writers may not block readers, and readers may not block writers, but nothing says they might not have to wait. You might need to trace to find out what is really going on.

jg

--
@home.com is bogus.
If you read this column by mistake, you are hereby required to post
your comments below or write me at Foster_at_gripe2ed.com.