Performance degradation after upgrading from 10gR2 to 11gR2
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Performance degradation after upgrading from 10gR2 to 11gR2

  1. #1
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233

    Performance degradation after upgrading from 10gR2 to 11gR2

    Hi guys,

    We upgraded our DWH DB from version 10.2.0.4 to 11.2.0.3 three months ago.
    Since then, we have experienced a performance degradation of 30% in Reports' queries.
    After a deep investigation, we've found out that since the upgrading there is a huge increase of SORT MERGE joins on expense of HASH joins:
    Code:
      1  select count(1),trunc(timestamp,'MM')
      2  from DBA_HIST_SQL_PLAN
      3  where OPERATION like '%SORT%'
      4  and options like '%JOIN%'
      5  and timestamp >= to_date('01/01/2013','dd/mm/yyyy')
      6  group by trunc(timestamp,'MM')
      7* order by trunc(timestamp,'MM')
    SQL> /
     
      COUNT(1) TRUNC(TIM
    ---------- ---------
           736 01-JAN-13
           672 01-FEB-13
           647 01-MAR-13
          2362 01-APR-13
          3960 01-MAY-13
          2460 01-JUN-13

    Since the upgrading we've treated some reports with the hint USE_HASH in order to improve reports but there are too many reports which were screwed up by SMJ.

    What can be the reason that the optimizer chooses by mistake SMJ instead of HJ?

    Our DB is hosted by the following server:
    Server type and version - SunOS 5.10 Generic_144488-08 (64-bit)
    CPUs - 32 X 2 (3Gb)
    Memory Size (MB) - 524288


    Thanks in advance,
    Nir

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,013
    There is a known issue with 11.2.0.3 and direct reads. Essentially Oracle takes what it considered to be a full table scan on a large table and serializes it reading directly from disk. Since the read is directly from disk it needs to flush the dirty blocks first. The reason for this behavior is so that you don't have one large query flush everything out of the db_cache. I turned off this feature on a few databases and turned on parallel_automatic_tuning. This seemed to help. You can also change what Oracle considers to be a small table by making that larger, hence fewer queries will be direct read queries.

    alter system set parallel_automatic_tuning=TRUE scope=spfile;
    alter system set "_serial_direct_read" =FALSE;

    http://www.pythian.com/blog/upgraded...reads-trouble/
    this space intentionally left blank

  3. #3
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Thanks a lot!
    Very interesting and makse a lot of sence because we have also a dramatic increasing in "direact read/write path" wait events.
    For testing , I've changed in session level the parameter _serial_direct_read to FALSE and I checked a problematic report which its execution plan was screwed with SMJ.
    The execution plan has been changed to the good one with HJ!
    I assume I'll need to open a SR to Oracle to get their approval for this changing...

    Best regards,
    Nir

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,013
    I don't think that you need approval for that change. But it could still help if you want to create an SR. Maybe Oracle will see that this change was ill advised.
    this space intentionally left blank

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Cool

    To make 11g work without performance degradation on RH Linux using OS file system, we had to set:
    Code:
    FILESYSTEMIO_OPTIONS = SETALL
    Perhaps it will help for you also
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  6. #6
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Thanks again gandolf989. I'll open SR and let's see what Oracle will recommend.
    LKBrwn_DBA - thanks , but we work on Sun Solaris , so I don't know if changing this parameter helps...

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,013
    Quote Originally Posted by LKBrwn_DBA View Post
    To make 11g work without performance degradation on RH Linux using OS file system, we had to set:
    Code:
    FILESYSTEMIO_OPTIONS = SETALL
    Perhaps it will help for you also
    Is that like pressing the easy button? It looks like your parameter does not help if you already use ASM.
    this space intentionally left blank

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    We actually bumped into a similar issue while testing upgrading a DWH to 11gR2.
    In our case the solution was to gather SYSTEM statistics on the new hardware as well as fully refresh table/index statistics.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi PAVB,

    Thanks!
    The Db size is ~ 50Tb , so I don't see a case to refresh statistcs all over the tables/indexes.
    In addition , we don't have any change in hardware , so I don't think that taking SYSTEM stats will help ....

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by nir_s View Post
    ... we don't have any change in hardware , so I don't think that taking SYSTEM stats will help ....
    You are correct, different scenario. Please disregard my previous post.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width