Re: Performance problem.

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Wed, 25 Jul 2007 07:21:59 -0700
Message-ID: <1185373319.338073.88950@57g2000hsv.googlegroups.com>

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

I know you said that a range scan is used in both plans however any difference in the plans is significant so compare both explain plans using dbms_xplan. If possible pull the actual run time plan from v $sql_plan.

Database parameters that affect the optimizer varies by version and is not limited to the following list of parameters:

db_file_multiblock_read_count

sort_area_size
hash_area_size
hash_join_enabled
optimizer_dynamic_sampling
optimizer_features_enable
optimizer_index_caching
optimizer_index_cost_adj

optimizer_max_permutations
optimizer_mode

HTH -- Mark D Powell -- Received on Wed Jul 25 2007 - 09:21:59 CDT