Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Performance problem.

Re: Performance problem.

From: R.Wang <wangr_at_douglas.bc.ca>
Date: Sat, 28 Jul 2007 04:08:07 -0000
Message-ID: <1185595687.302709.227570@e16g2000pri.googlegroups.com>


On Jul 27, 12:18 pm, Serguei.Goumeni..._at_cibc.ca wrote:
> 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.

Hi,

     I'd like to say something here.
    You mentioned that "Statistics on the tables and the indexes is calculated
daily. ". That's good, or not good. It's hard to say.

    It's known issue about the inappropriate performing of CBO, especially on Oracle 9iR2. Sometimes, CBO doesn't act properly. So, if you collect statistics daily, the probability of inapproriate action of CBO would be high. If you'd like to know much on this, please go read my article at http://oraclepoint.com/oralife/?page_id=3

    Also, I recommend a forum section about performance tuning, visit http://www.oraclepoint.com (please register and log in first, then bunch of stuff will be downloadable.)

Cheers,

R.Wang Received on Fri Jul 27 2007 - 23:08:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US