FAQ
When you say "We ran statistics the same way we ran them in Oracle9"
what does that mean? Did you use the same
dbms_stats.gather_command(s) ? You realize that the defaults have
changed, do you. Unless you change the defaults "back" to how they
were in 9i the same command will gather statistics differently. Most
importantly, 10g gathers histograms by default which 9i didn't do.
And then, of course, there is the automatic stats gathering job
unless you disabled that.
At 06:05 PM 3/19/2009, Jack van Zanen wrote:
We have just upgraded our development environment from 9.2 to 10.2


Can someone can help us identify why 10.2.0.4 optimizer taking
double the time to execute the same statement for exact same number
of rows ( 3803685 )
We can see, it is picking up a different plan, NL is 9.2 against HJ in 10.2.
We ran statistics the same way we ran them in Oracle9
Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

Search Discussions

  • Greg Rahn at Mar 20, 2009 at 12:48 am

    On Thu, Mar 19, 2009 at 5:05 PM, Jack van Zanen wrote:
    We have just upgraded our development environment from 9.2 to 10.2

    Can someone can help us identify why 10.2.0.4 optimizer taking double the
    time to execute the same statement for exact same number of rows ( 3803685 )
    We can see, it is picking up a different plan, NL is 9.2 against HJ in 10.2.
    We ran statistics the same way we ran them in Oracle9
    What is your exact dbms_stats command? Do you recognize that some
    defaults have changed in10g, so the "same way" may not really be the
    "same way"?
    !! We already had a look at note 295819.1  which mentions the two parameters
    "_optimizer_cost_based_transformation" and "_gby_hash_aggregation_enabled"
    I would hold off mucking with parameters at this point. That should
    be a last resort and only under the recommendation of Oracle Support.
    This SQL is part of a long running batch job which ran about 20 minutes
    faster than on 9i, but this step doubled in time so we could potentially
    gain some more.

    SQL Statement :

    INSERT INTO ...
    SELECT /*+ ALL_ROWS */ ...
    WHERE 1=1
    AND ET.INSTALL_ID BETWEEN :B2
    AND :B1 AND EH.DEVICE_NO = ET.DEVICE_NO AND EH.VALID_TO = ET.VALID_TO
    AND EQ.EQUIPMENT_NO = EH.EQUIPMENT_NO
    I see 2 potential issues with this SQL.
    - It has a hint. I would recommend that you remove it see what the
    optimizer plan is
    - It uses bind variables. You may be mixing bind variables and bind
    peeking with histograms. That can be a nasty cocktail, if you know
    what I mean. I would see if CCS.CCS_EASTL.DEVICE_NO has a histogram
    on it. You may not want one if it does.

    This might be a useful read as well:
    http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_bidw_optimizer_10gr2_0208.pdf
  • Dion Cho at Mar 20, 2009 at 4:41 am
    Well, 10g acutally outperforms 9i. You have lower logical reads in
    10g(8,338,678 --> 1,495,846)

    In 10g, logical reads == physical reads, which means that you have no cached
    blocks.

    With some blocks cached, the performance would be enhanced.

    But one problem is that Oracle denies to cache the blocks read by multi
    block I/O on the big table when the buffer cache is full.
    In this case, you might want to load the big tables into keep buffer pool.

    Or you might just like to use 9i version execution plan.

    Dion Cho - Oracle Performance Storyteller

    http://dioncho.wordpress.com (english)
    http://ukja.tistory.com (korean)
    On Fri, Mar 20, 2009 at 9:05 AM, Jack van Zanen wrote:

    We have just upgraded our development environment from 9.2 to 10.2


    Can someone can help us identify why 10.2.0.4 optimizer taking double the
    time to execute the same statement for exact same number of rows ( 3803685 )
    We can see, it is picking up a different plan, NL is 9.2 against HJ in
    10.2.
    We ran statistics the same way we ran them in Oracle9

    !! We already had a look at note 295819.1 which mentions the two
    parameters "_optimizer_cost_based_transformation" and
    "_gby_hash_aggregation_enabled"

    This SQL is part of a long running batch job which ran about 20 minutes
    faster than on 9i, but this step doubled in time so we could potentially
    gain some more.

    SQL Statement :

    INSERT INTO INSTALL_DEVICE ( INSTALL_ID, DEVICE, MATERIAL_NO, DEVICE_NO,
    EQUIPMENT_NO, REGISTER_GROUP, INSTALL_DATE, DEVICE_LOCATION,
    DEVICE_VALID_FROM, DEVICE_VALID_TO, EQUIP_VALID_FROM, EQUIP_VALID_TO,
    MODIFIED_DATE )
    SELECT /*+ ALL_ROWS */ ET.INSTALL_ID, LTRIM(EQ.SERIAL_NO2,
    '0') DEVICE, EQ.MATERIAL_NO, EH.DEVICE_NO, EH.EQUIPMENT_NO,
    EH.REGISTER_GROUP, EH.INSTALL_DATE, EH.DEVICE_LOCATION, ET.VALID_FROM
    VALID_FROM, ET.VALID_TO VALID_TO, EH.VALID_FROM EQUIP_VALID_FROM,
    EH.VALID_TO EQUIP_VALID_TO, ET.MODIFIED_DATE
    FROM CCS.CCS_EASTL ET,
    CCS.CCS_EGERH EH,
    CCS.CCS_EQUI EQ
    WHERE 1=1
    AND ET.INSTALL_ID BETWEEN :B2
    AND :B1 AND EH.DEVICE_NO = ET.DEVICE_NO AND EH.VALID_TO = ET.VALID_TO
    AND EQ.EQUIPMENT_NO = EH.EQUIPMENT_NO
    Oracle 9.2 (10046 Trace Details)

    call count cpu elapsed disk query current
    rows
    ------- ------ -------- ---------- ---------- ---------- ----------
    ----------
    Parse 1 0.00 0.19 0 0
    0 0
    Execute 22 342.15 647.50 641661 8338678 14429392
    3803685
    Fetch 0 0.00 0.00 0 0
    0 0
    ------- ------ -------- ---------- ---------- ---------- ----------
    ----------
    total 23 342.15 647.69 641661 8338678 14429392
    3803685

    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 640 (recursive depth: 2)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    3803685 FILTER
    3803685 NESTED LOOPS
    3803685 HASH JOIN
    3804760 TABLE ACCESS FULL CCS_EASTL
    183904820 TABLE ACCESS FULL CCS_EGERH
    3803685 TABLE ACCESS BY INDEX ROWID CCS_EQUI
    3803685 INDEX UNIQUE SCAN CCS_EQUI_KEY (object id 278281)

    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total
    Waited
    ---------------------------------------- Waited ----------
    ------------
    db file sequential read 612 0.15
    3.30
    db file scattered read 20428 0.57
    220.18
    log file switch completion 30 0.05
    0.82
    log buffer space 75 0.50
    4.63
    latch free 8 0.01
    0.03
    log file sync 14 0.03
    0.25

    ********************************************************************************

    Oracle 10.2 (10046 Trace Details)

    call count cpu elapsed disk query current
    rows
    ------- ------ -------- ---------- ---------- ---------- ----------
    ----------
    Parse 1 0.00 0.02 0 0
    0 0
    Execute 22 694.85 1242.63 1419950 1495846 14933397
    3803685
    Fetch 0 0.00 0.00 0 0
    0 0
    ------- ------ -------- ---------- ---------- ---------- ----------
    ----------
    total 23 694.85 1242.66 1419950 1495846 14933397
    3803685

    Misses in library cache during parse: 1
    Misses in library cache during execute: 2
    Optimizer mode: ALL_ROWS
    Parsing user id: 640 (recursive depth: 2)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    3592526 FILTER (cr=1356558 pr=1355369 pw=0 time=955288705 us)
    3592526 HASH JOIN (cr=1356558 pr=1355369 pw=0 time=901159201 us)
    3592526 HASH JOIN (cr=635607 pr=634978 pw=0 time=548271804 us)
    3593551 TABLE ACCESS FULL CCS_EASTL (cr=182049 pr=181793 pw=0
    time=187608037 us)
    175545510 TABLE ACCESS FULL CCS_EGERH (cr=453558 pr=453185 pw=0
    time=707111 us)
    104295387 TABLE ACCESS FULL CCS_EQUI (cr=720951 pr=720391 pw=0
    time=327816867 us)

    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total
    Waited
    ---------------------------------------- Waited ----------
    ------------
    db file sequential read 59 0.01
    0.09
    db file scattered read 44944 0.90
    445.14
    latch: cache buffers lru chain 23 0.00
    0.02
    log file switch completion 33 0.08
    0.58
    latch: object queue header operation 7 0.00
    0.00
    latch: cache buffers chains 3 0.00
    0.00
    latch: shared pool 5 0.01
    0.01
    log buffer space 2 0.06
    0.07

    ********************************************************************************

    Brgds



    Jack

    -------------------------
    This e-mail and any attachments may contain confidential material for the
    sole use of the intended recipient. If you are not the intended recipient,
    please be aware that any disclosure, copying, distribution or use of this
    e-mail or any attachment is prohibited. If you have received this e-mail in
    error, please contact the sender and delete all copies.
    Thank you for your cooperation
    --
    http://www.freelists.org/webpage/oracle-l
  • Michael McMullen at Mar 20, 2009 at 11:57 am
    I'll try to find the article (I think it's an oracle white paper) where they
    went through an 9i to 10g upgrade. It started out being 10-20% slower for a
    key batch job and then by the time they were done it was 10% faster.

    I believe the key steps were.

    Take out any hidden parameters ie start with base 10g pfile, nothing from
    9i

    Take out hints from your sql. Really you want to start out cold, nothing
    left from 9i

    Have system statistics -- I myself hate system statistics. You get a
    brand new box, how do you set them to mimic your full prod without running
    your processes. It seems like a swag to me. They vary depending on the
    workload of the system. I like to bring over one business area at a time to
    a new box, get things running smoothly and then the next. But if I run
    system stats after I bring a bunch of new biz areas over, they can change
    which could change the execution plans of the first biz area I brought over.
    I find it very much a chicken/egg sort of thing.

    Be really depressed, as the article was long and involved a lot of steps
    and if it was a live production system you would have been screwed.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 20, '09 at 12:32a
activeMar 20, '09 at 11:57a
posts4
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase