This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jan 10, 2011 2:38 PM by jgarry RSS

db issue in 11gr2 (after migration)

user9023903 Newbie
Currently Being Moderated
Dear all,

We migrated our database from 10.1.2 rac solaris 9 to 11.2 on solaris 10. (Single threaded CPU server to multi threaded cpu server).

As I was checking this note. Migration from fast single threaded CPU machine to CMT UltraSPARC T1 and T2 results in increased CPU reporting and diminished performance (Doc ID 781763.1). I believe we are facing the same issue here as our application was running fine on single threaded cpu and moving to this
t series multi threaded machine,we are facing the issue. Explain plans are the same,object
s and stats are updated.

New production : 

SELECT EMP_CODE , EMP_DEDUC_EARN_CODE , EMP_DEDUC_EARN_BAS_FLAG , 
  M_DEDUC_EARN_TYPE , MAX (EMP_DEDUC_EARN_RATE) "AMOUNT" 
FROM
 EMP_DEDUC_EARN A , M_EARWHERE EMP_CORP_CODE =  :1  AND 
  EMP_COMP_CODE =  :1  AND EMP_DEDUC_EARN_CODE =  M_DEDUC_EARN_CODE  AND 
  NVL(M_DEDUC_EARN_FIX_FLAG , '1' )  =  '0'   GROUP BY EMP_CODE , 
  EMP_DEDUC_EARN_CODE , EMP_DEDUC_EARN_BAS_FLAG , EMP_EFF_DATE , 
  M_DEDUC_EARN_TYPE  HAVING EMP_EFF_DATE =  (SELECT MAX (EMP_EFF_DATE) FROM 
  EMPWHERE EMP_CORP_CODE =  :1  AND EMP_COMP_CODE =  :1  AND 
  EMP_CODE =  :1  AND EMP_DEDUC_EARN_CODE =  A.EMP_DEDUC_EARN_CODE   )  AND 
  EMP_CODE =  :1  



call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    673      1.55       1.55          0          0          0           0
Fetch     1347     74.81      74.61          1     113319          0         674
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2021     76.36      76.17          1     113319          0         674

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 123  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  FILTER  (cr=168 pr=1 pw=0 time=120938 us)
      8114       8114       8114   HASH GROUP BY (cr=162 pr=1 pw=0 time=111513 us cost=54 size=39 card=1)
      8114       8114       8114    HASH JOIN  (cr=162 pr=1 pw=0 time=44409 us cost=52 size=302055 card=7745)
        20         20         20     TABLE ACCESS FULL M_DEDUC_EARN (cr=2 pr=1 pw=0 time=6640 us cost=3 size=160 card=20)
      8615       8615       8615     TABLE ACCESS FULL EMP_DEDUC_EARN (cr=160 pr=0 pw=0 time=15127 us cost=48 size=252092 card=8132)
         4          4          4   SORT AGGREGATE (cr=6 pr=0 pw=0 time=289 us)
         1          1          1    FIRST ROW  (cr=6 pr=0 pw=0 time=217 us cost=2 size=24 card=1)
         1          1          1     INDEX RANGE SCAN (MIN/MAX) EMP_DEDUC_EARN_IDX_003 (cr=6 pr=0 pw=0 time=200 us cost=2 size=24 card=1)(object id 73348)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    2020        0.00          0.02
  SQL*Net message from client                  2020        0.00          0.85
  db file sequential read                         1        0.00          0.00
********************************************************************************
OLD PRODUCTION : 


SELECT EMP_CODE , EMP_DEDUC_EARN_CODE , EMP_DEDUC_EARN_BAS_FLAG , 
  M_DEDUC_EARN_TYPE , MAX (EMP_DEDUC_EARN_RATE) "AMOUNT" 
FROM
 EMP_DEDUC_EARN A , M_EARWHERE EMP_CORP_CODE =  :1  AND 
  EMP_COMP_CODE =  :1  AND EMP_DEDUC_EARN_CODE =  M_DEDUC_EARN_CODE  AND 
  NVL(M_DEDUC_EARN_FIX_FLAG , '1' )  =  '0'   GROUP BY EMP_CODE , 
  EMP_DEDUC_EARN_CODE , EMP_DEDUC_EARN_BAS_FLAG , EMP_EFF_DATE , 
  M_DEDUC_EARN_TYPE  HAVING EMP_EFF_DATE =  (SELECT MAX (EMP_EFF_DATE) FROM 
  EMPWHERE EMP_CORP_CODE =  :1  AND EMP_COMP_CODE =  :1  AND 
  EMP_CODE =  :1  AND EMP_DEDUC_EARN_CODE =  A.EMP_DEDUC_EARN_CODE   )  AND 
  EMP_CODE =  :1  


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    644      0.38       0.45          0          0          0           0
Fetch     1300     31.24      31.10          0     175168          0         656
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1945     31.62      31.55          0     175168          0         656


Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 359  

Rows     Row Source Operation
-------  ---------------------------------------------------
    656  FILTER  (cr=175168 pr=0 pw=0 time=28892286 us)
4149936   SORT GROUP BY (cr=170016 pr=0 pw=0 time=29079990 us)
4149936    HASH JOIN  (cr=170016 pr=0 pw=0 time=13103362 us)
  12880     TABLE ACCESS FULL M_DEDUC_EARN (cr=10304 pr=0 pw=0 time=309525 us)
4395944     TABLE ACCESS FULL EMP_DEDUC_EARN (cr=159712 pr=0 pw=0 time=4910186 us)
   2576   SORT AGGREGATE (cr=5152 pr=0 pw=0 time=140681 us)
    656    FIRST ROW  (cr=5152 pr=0 pw=0 time=104068 us)
    656     INDEX RANGE SCAN (MIN/MAX) EMP_DEDUC_EARN_IDX_003 (cr=5152 pr=0 pw=0 time=97482 us)(object id 129278)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    1944        0.00          0.00
  SQL*Net message from client                  1944        0.00          0.54
********************************************************************************
  • 1. Re: db issue in 11gr2 (after migration)
    Mohamed ELAzab Pro
    Currently Being Moderated
    Hello,
    In 11g you can run with the 10g optimizer.i think you can try and see if the performance will be the same as 10g.if this happen please open a case with oracle.
    Kind regards
    Mohamed
  • 2. Re: db issue in 11gr2 (after migration)
    Mohamed ELAzab Pro
    Currently Being Moderated
    Hello,
    Please also refer to this note:
    www.oracle.com/.../bi.../twp-upgrading-10g-to-11g-what-to-ex-133707.pdf
    kind regards
    Mohamed
  • 3. Re: db issue in 11gr2 (after migration)
    user9023903 Newbie
    Currently Being Moderated
    Thanks Mohamed. DB is migrated and not upgraded. Anyway I will check and let you know
  • 4. Re: db issue in 11gr2 (after migration)
    Mohamed ELAzab Pro
    Currently Being Moderated
    Ok brother but i shared this note to give you more control over the optimizer new features.
    kind regards
    Mohamed
  • 5. Re: db issue in 11gr2 (after migration)
    user9023903 Newbie
    Currently Being Moderated
    Thanks Mohamed,

    Can you shed some more lights on 11g you can run with the 10g optimizer.

    reverting parameter optimizer_features_enable to the old version can help ?


    new db :

    optimizer_features_enable 11.2.0.2
    and old

    optimizer_features_enable 10.1.0.3
  • 6. Re: db issue in 11gr2 (after migration)
    Mohamed ELAzab Pro
    Currently Being Moderated
    Hello,
    Yes you can do the following:
    1- convert the optimizer to the old version.
    2-Generate an Optimizer Trace
    The optimizer can be commanded to produce a trace of the costing decisions it makes with the
    command. This is occasionally used to provide Oracle Support with additional information about the
    optimizer actions.
    ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
    The location of the optimizer trace in the same location as the other trace files, and the name of the
    trace file, can be modified with the command:
    ALTER SESSION SET TRACEFILE_IDENTIFIER='opt';
    The trace file does not require formatting but it is quite large, be sure to increase the size allowed for
    the trace in the session with:
    ALTER SESSION SET MAX_DUMPFILE_SIZE=UNLIMITED;
    Stop the tracing in your session by exiting the session or the command:
    ALTER SESSION SET EVENTS '10053 trace name context off';
    then do compare both the old optimizer run and the new optimizer run as follow:
    Comparing Statistics
    When it comes to deploying a new application or application module it is standard practice to
    test and tune the application in a test environment before it is moved to production. However,
    even with testing it is possible that SQL statements in the application will have different
    execution plans in production then they did on the test system. One of the key reasons an
    execution plan can differ from one system to another (from test and production) is because the
    optimizer statistics on each system are different. In Oracle Database 10g Release 2, the
    DIFF_TABLE_STATS_* functions can be used to compare statistics for a table from two
    different sources. The statistics can be from:
    • A user statistics table and current statistics in the dictionary
    • A single user statistics table containing two sets of statistics that can be identified using statids
    • Two different user statistics tables
    • Two points in history
    • Current statistics and a point in history
    • Pending Statistics with the current statistics in the dictionary
    • Pending Statistics with a user statistics table
    The function also compares the statistics of the dependent objects (indexes, columns, partitions).
    The function displays statistics for the object(s) from both sources if the difference between the
    statistics exceeds a specified threshold. The threshold can be specified as an argument to the
    function; the default value is 10%. The statistics corresponding to the first source will be used as
    the basis for computing the differential percentage.
    In the example below, we compare the current dictionary statistics for the EMP table with the
    statistics for EMP in the statistics table TAB1; the SQL statement will generate a report-like output
    on the screen.
    SQL> select report, maxdiffpct from
    table(DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB(‘SCOTT’,’EMP’,’TAB1’ ));
    to Enable Query Optimizer Features :
    The OPTIMIZER_FEATURES_ENABLE parameter acts as an umbrella parameter for the query
    optimizer. This parameter can be used to enable a series of optimizer-related features, depending on
    the release. It accepts one of a list of valid string values corresponding to the release numbers, such
    as 8.0.4, 8.1.7, and 9.2.0.
    The OPTIMIZER_FEATURES_ENABLE parameter allows you to upgrade the Oracle Server yet
    preserve the old behavior of the query optimizer after the upgrade. For example, when you upgrade
    the Oracle Server from release 8.1.5 to release 8.1.6, the default value of the
    OPTIMIZER_FEATURES_ENABLE parameter changes from 8.1.5 to 8.1.6. This upgrade results in
    the query optimizer enabling optimization features based on 8.1.6 as opposed to 8.1.5. For plan
    stability or backward compatibility reasons, you might not want the query plans to change because of
    new optimizer features in a new release. In such a case, you can set the
    OPTIMIZER_FEATURES_ENABLE parameter to an earlier version.
    For example, the following setting enables the use of the optimizer features in generating query plans
    in Oracle9i, Release 2:
    OPTIMIZER_FEATURES_ENABLE='9.2.0';
    please note that tunning is troubleshooting.i am just trying to give you an example of how to start to tune and know what is different.
  • 7. Re: db issue in 11gr2 (after migration)
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    I do not believe the optimizer settings will help in this case - this is a hardware architecture issue. Pl post your new hardware specs. Pl re-read the MOS Doc you mention. Pl also see MOS Doc 1181315.1 (Important Considerations for Operating Oracle RAC on T-Series Servers)

    HTH
    Srini
  • 8. Re: db issue in 11gr2 (after migration)
    CharlesHooper Expert
    Currently Being Moderated
    user9023903 wrote:
    Dear all,

    We migrated our database from 10.1.2 rac solaris 9 to 11.2 on solaris 10. (Single threaded CPU server to multi threaded cpu server).

    As I was checking this note. Migration from fast single threaded CPU machine to CMT UltraSPARC T1 and T2 results in increased CPU reporting and diminished performance (Doc ID 781763.1). I believe we are facing the same issue here as our application was running fine on single threaded cpu and moving to this
    t series multi threaded machine,we are facing the issue. Explain plans are the same,object
    s and stats are updated.

    New production : 
    
    SELECT EMP_CODE , EMP_DEDUC_EARN_CODE , EMP_DEDUC_EARN_BAS_FLAG , 
    M_DEDUC_EARN_TYPE , MAX (EMP_DEDUC_EARN_RATE) "AMOUNT" 
    FROM
    EMP_DEDUC_EARN A , M_EARWHERE EMP_CORP_CODE =  :1  AND 
    EMP_COMP_CODE =  :1  AND EMP_DEDUC_EARN_CODE =  M_DEDUC_EARN_CODE  AND 
    NVL(M_DEDUC_EARN_FIX_FLAG , '1' )  =  '0'   GROUP BY EMP_CODE , 
    EMP_DEDUC_EARN_CODE , EMP_DEDUC_EARN_BAS_FLAG , EMP_EFF_DATE , 
    M_DEDUC_EARN_TYPE  HAVING EMP_EFF_DATE =  (SELECT MAX (EMP_EFF_DATE) FROM 
    EMPWHERE EMP_CORP_CODE =  :1  AND EMP_COMP_CODE =  :1  AND 
    EMP_CODE =  :1  AND EMP_DEDUC_EARN_CODE =  A.EMP_DEDUC_EARN_CODE   )  AND 
    EMP_CODE =  :1  
    
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute    673      1.55       1.55          0          0          0           0
    Fetch     1347     74.81      74.61          1     113319          0         674
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total     2021     76.36      76.17          1     113319          0         674
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 123  
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
    1          1          1  FILTER  (cr=168 pr=1 pw=0 time=120938 us)
    8114       8114       8114   HASH GROUP BY (cr=162 pr=1 pw=0 time=111513 us cost=54 size=39 card=1)
    8114       8114       8114    HASH JOIN  (cr=162 pr=1 pw=0 time=44409 us cost=52 size=302055 card=7745)
    20         20         20     TABLE ACCESS FULL M_DEDUC_EARN (cr=2 pr=1 pw=0 time=6640 us cost=3 size=160 card=20)
    8615       8615       8615     TABLE ACCESS FULL EMP_DEDUC_EARN (cr=160 pr=0 pw=0 time=15127 us cost=48 size=252092 card=8132)
    4          4          4   SORT AGGREGATE (cr=6 pr=0 pw=0 time=289 us)
    1          1          1    FIRST ROW  (cr=6 pr=0 pw=0 time=217 us cost=2 size=24 card=1)
    1          1          1     INDEX RANGE SCAN (MIN/MAX) EMP_DEDUC_EARN_IDX_003 (cr=6 pr=0 pw=0 time=200 us cost=2 size=24 card=1)(object id 73348)
    
    
    Elapsed times include waiting on following events:
    Event waited on                             Times   Max. Wait  Total Waited
    ----------------------------------------   Waited  ----------  ------------
    SQL*Net message to client                    2020        0.00          0.02
    SQL*Net message from client                  2020        0.00          0.85
    db file sequential read                         1        0.00          0.00
    ********************************************************************************
    OLD PRODUCTION : 
    
    
    SELECT EMP_CODE , EMP_DEDUC_EARN_CODE , EMP_DEDUC_EARN_BAS_FLAG , 
    M_DEDUC_EARN_TYPE , MAX (EMP_DEDUC_EARN_RATE) "AMOUNT" 
    FROM
    EMP_DEDUC_EARN A , M_EARWHERE EMP_CORP_CODE =  :1  AND 
    EMP_COMP_CODE =  :1  AND EMP_DEDUC_EARN_CODE =  M_DEDUC_EARN_CODE  AND 
    NVL(M_DEDUC_EARN_FIX_FLAG , '1' )  =  '0'   GROUP BY EMP_CODE , 
    EMP_DEDUC_EARN_CODE , EMP_DEDUC_EARN_BAS_FLAG , EMP_EFF_DATE , 
    M_DEDUC_EARN_TYPE  HAVING EMP_EFF_DATE =  (SELECT MAX (EMP_EFF_DATE) FROM 
    EMPWHERE EMP_CORP_CODE =  :1  AND EMP_COMP_CODE =  :1  AND 
    EMP_CODE =  :1  AND EMP_DEDUC_EARN_CODE =  A.EMP_DEDUC_EARN_CODE   )  AND 
    EMP_CODE =  :1  
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute    644      0.38       0.45          0          0          0           0
    Fetch     1300     31.24      31.10          0     175168          0         656
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total     1945     31.62      31.55          0     175168          0         656
    
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 359  
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
    656  FILTER  (cr=175168 pr=0 pw=0 time=28892286 us)
    4149936   SORT GROUP BY (cr=170016 pr=0 pw=0 time=29079990 us)
    4149936    HASH JOIN  (cr=170016 pr=0 pw=0 time=13103362 us)
    12880     TABLE ACCESS FULL M_DEDUC_EARN (cr=10304 pr=0 pw=0 time=309525 us)
    4395944     TABLE ACCESS FULL EMP_DEDUC_EARN (cr=159712 pr=0 pw=0 time=4910186 us)
    2576   SORT AGGREGATE (cr=5152 pr=0 pw=0 time=140681 us)
    656    FIRST ROW  (cr=5152 pr=0 pw=0 time=104068 us)
    656     INDEX RANGE SCAN (MIN/MAX) EMP_DEDUC_EARN_IDX_003 (cr=5152 pr=0 pw=0 time=97482 us)(object id 129278)
    
    
    Elapsed times include waiting on following events:
    Event waited on                             Times   Max. Wait  Total Waited
    ----------------------------------------   Waited  ----------  ------------
    SQL*Net message to client                    1944        0.00          0.00
    SQL*Net message from client                  1944        0.00          0.54
    ********************************************************************************
    Other than the "HASH GROUP BY" operation on the second line of the printed execution plan for 11.2.0.2, and the "SORT GROUP BY" operation on the second line of the printed execution plan for 10.1.0.3, the execution plans are identical. Keep in mind that 11.2.0.2, due to adaptive cursor sharing, is capable of changing the execution plan for future executions (there was a single parse call in this case, so adaptive cursor sharing likely did not take place). Also, keep in mind that 11.2.0.2 by default in a 10046 trace will output the row source operation execution plan after the first execution, rather than when the cursor was closed - this explains the difference in the ROWS column in the execution plan. If we look closely at the summary information, 11.2.0.2 performed 113,319 consistent gets in 673 executions, while 10.1.0.3 performed 175,168 consistent gets in 644 executions. Each execution in 11.2.0.2 is in theory more efficient than each execution in 10.1.0.3, yet the average time per execution is much longer.

    If you search the forum for T2 processor, you will find this thread:
    Help with Parallelization on our SPARC server...we are drowning!

    In the above thread, John Brady stated the following:
    "Be very, very wary of the Sun T1 or T2 (Niagara) based systems. They are terrible at database performance. Be aware that at 1.2 GHz with 8 cores and 4 threads each core, this is effectively 32 * 300 MHz CPUs. In which case my strongest advice is buy another system and throw this one out. The Niagara processors are well known as being very bad for running real world Oracle database on - in spite of everything Sun may say about them."

    Translated, if your old server had a 1.2GHz single core CPU and the new server had a 2.0GHz 8 core CPU (each with 4 threads), each core in the 8 core CPU operates at an effective 500MHz (41.6% as fast as the single core), and this is very close to the 41.4% speed difference when the time of the old server is divided by the time for the new server.

    It seems that I saw the above SQL statement recently on the OTN forums:
    SELECT EMP_CODE , EMP_DEDUC_EARN_CODE , EMP_DEDUC_EARN_BAS_FLAG , 
      M_DEDUC_EARN_TYPE , MAX (EMP_DEDUC_EARN_RATE) "AMOUNT" 
    FROM
      EMP_DEDUC_EARN A,
      M_EAR
    WHERE
      EMP_CORP_CODE =  :1
      AND EMP_COMP_CODE =  :1
      AND EMP_DEDUC_EARN_CODE =  M_DEDUC_EARN_CODE
      AND NVL(M_DEDUC_EARN_FIX_FLAG , '1' )  =  '0'
    GROUP BY
      EMP_CODE, 
      EMP_DEDUC_EARN_CODE,
      EMP_DEDUC_EARN_BAS_FLAG,
      EMP_EFF_DATE, 
      M_DEDUC_EARN_TYPE  
    HAVING EMP_EFF_DATE = (
      SELECT
        MAX (EMP_EFF_DATE)
      FROM 
        EMP
      WHERE
        EMP_CORP_CODE =  :1
        AND EMP_COMP_CODE =  :1
        AND EMP_CODE =  :1
      AND EMP_DEDUC_EARN_CODE =  A.EMP_DEDUC_EARN_CODE)
      AND EMP_CODE =  :1
    The reason why I recall that is because of the "AND EMP_CODE = :1" in the HAVING clause - it should appear in the WHERE clause(s). If you fix that problem, you might find that the SQL statement is much more efficient, even on the new server - remember, filter early.

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.

    Edited by: Charles Hooper on Jan 9, 2011 9:16 AM
    Cleaned up the wording in a couple of sentences
  • 9. Re: db issue in 11gr2 (after migration)
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user9023903 wrote:
    Dear all,

    We migrated our database from 10.1.2 rac solaris 9 to 11.2 on solaris 10. (Single threaded CPU server to multi threaded cpu server).

    As I was checking this note. Migration from fast single threaded CPU machine to CMT UltraSPARC T1 and T2 results in increased CPU reporting and diminished performance (Doc ID 781763.1). I believe we are facing the same issue here as our application was running fine on single threaded cpu and moving to this
    t series multi threaded machine,we are facing the issue. Explain plans are the same,object
    s and stats are updated.
    It is worth noting that the query time (allowing for granularity errirs) 100% CPU - so, what is the nominal CPU speed of the old and new equipment, and while this job was running were there a number of other concurrent processes equally busy on the system.

    Note that you've changed from a "hash group by" to a "sort group by" at line 2. I don't think that this is hugely significant given the results when you scale the "time=" figures to get a consistent view across the two sets of results, but you might want to test the impact of running with hash aggregation disabled for this query (11g has a hint "no_use_hash_aggregation".


    Regards
    Jonathan Lewis
  • 10. Re: db issue in 11gr2 (after migration)
    user9023903 Newbie
    Currently Being Moderated
    Thanks Charles for the excellent info and patience.

    The problem is the client doesn't want to make any change in the queries as this are application based queries and they always quote if the performance is good in the old server and no way they can resist the performance to be low in the new servers.
  • 11. Re: db issue in 11gr2 (after migration)
    user9023903 Newbie
    Currently Being Moderated
    Thanks JOnathan,


    and while this job was running were there a number of other concurrent processes equally busy on the system.

    No we didn't go live with the new system and the new system is idle all the time.
  • 12. Re: db issue in 11gr2 (after migration)
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    The UltraSparc T1 and T2 CMTs are slower CPUs but with more cores and threads.

    These servers are good where you have many sessions concurrently active and you want to parallelise them -- e.g. ParallelQueries.
    These servers are NOT the right solution where you have CPU intensive sessions that cannot be parallelised.

    see http://blogs.sun.com/glennf/entry/throughput_computing_series_utilizing_cmt

    http://blogs.sun.com/glennf/entry/optimizing_oracle_schema_analyze_with

    http://blogs.sun.com/glennf/entry/optimizing_oracle_dss_operations_with

    Hemant K Chitale
  • 13. Re: db issue in 11gr2 (after migration)
    603349 Explorer
    Currently Being Moderated
    Charles Hooper wrote:

    In the above thread, John Brady stated the following:
    "Be very, very wary of the Sun T1 or T2 (Niagara) based systems. They are terrible at database performance. Be aware that at 1.2 GHz with 8 cores and 4 threads each core, this is effectively 32 * 300 MHz CPUs. In which case my strongest advice is buy another system and throw this one out. The Niagara processors are well known as being very bad for running real world Oracle database on - in spite of everything Sun may say about them."

    Translated, if your old server had a 1.2GHz single core CPU and the new server had a 2.0GHz 8 core CPU (each with 4 threads), each core in the 8 core CPU operates at an effective 500MHz (41.6% as fast as the single core), and this is very close to the 41.4% speed difference when the time of the old server is divided by the time for the new server.
    One can not take the clock speed of a CPU and divide it by cores or threads to get an "effective" clock speed - that's not how CPUs work (this is bad math). Also, one can not accurately compare clock speeds (GHz) across CPU architectures (Intel/AMD to SPARC T-series, etc.).

    Each CPU core has its own instruction pipeline and that pipeline is shared between threads, however, if you are running just a single process test and it is not stalled on anything (and nothing else is running on the system), it will have the ability to use 100% of that CPU core (at full clock speed).

    I'd suggest this (now somewhat dated) write up on some of the areas where people have misunderstandings around CMT:
    http://blogs.sun.com/deniss/entry/lesons_learned_from_t1

    The seemingly continued misunderstanding about the T-series CMT stuff is that:
    - it is best suited for throughput based workloads that keep a system busy
    - single threaded performance tends to be ~40% less than other platforms (which seems to match closely to the OPs observations).

    I'd also suggest this OOW-09 session on CMT topics:
    http://wikis.sun.com/download/attachments/128484865/OOW09+Optimal+Oracle+DB+Perf+1_1.pdf

    --
    Regards,
    Greg Rahn
    http://structureddata.org
  • 14. Re: db issue in 11gr2 (after migration)
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Yes, the "lessons_learned_from_t1" post is very useful.
    These CMTs are designed to use the CPU to the fullest, provided you submit jobs that require and can use that many CPU threads. A single job, not using any parallelism, isn't using the hardware.


    Hemant K Chitale
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points