This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Nov 25, 2013 2:08 AM by Mynz RSS

New Server, less Performance on Writes

Mynz Newbie
Currently Being Moderated

Hello,

 

I'm facing a problem I've not expected: After a migration from an old server (windows 2003, 10g) to a new server (windows 2008 R2, 11g) INSERTs takes twice as long as before.

 

The new sever is faster, there's more memory, the disks are faster, too. But when doing my imports, INSERTs takes twice as much time as before (read are more than twice as much faster!!!).

 

I checked this behaviour with 10million INSERTs in a table with no "extras" on both databases -> same result.

 

Running statistics -> same result.

 

Now I'm lacking idea's what else to check.

 

Anyone else ?

 

regards,

 

Mynz

  • 1. Re: New Server, less Performance on Writes
    Mynz Newbie
    Currently Being Moderated

    Additional information: This is a Standard Edition One

  • 2. Re: New Server, less Performance on Writes
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    trace it.

     

    Best regards,

      Nikolay

  • 3. Re: New Server, less Performance on Writes
    sybrand_b Guru
    Currently Being Moderated

    Hi,

     

    The first question I always ask myself is

     

       What is it waiting for

     

    So v$session_events, v$session_io, v$session_waits.

    And yes, trace would help.

    Please also do not forget impact of log switches and redo log generation.

     

    ------------

    Sybrand Bakker

    Senior Oracle DBA

  • 4. Re: New Server, less Performance on Writes
    Mynz Newbie
    Currently Being Moderated

    Hello,

     

    I must confess I'm not really familiar with events, waits and trace. Is there something like a "cookbook" ?

     

    I checked some docs from Don Burleson, but nothing fits to my problem here.

     

    The databases were created out of almost the same scripts, especially concerning TABLESPACEs. So I wonder what could be the difference. I'm pretty sure, it's something really obvious, I don't see at the moment.

     

    Regards,

     

    Mynz

  • 5. Re: New Server, less Performance on Writes
    sb92075 Guru
    Currently Being Moderated

    post formatted EXPLAIN PLAN from both sustems

  • 6. Re: New Server, less Performance on Writes
    Mynz Newbie
    Currently Being Moderated

    OK,

     

    let's try. Remote access to both databases is not really fast and I cannot use my usual tools. But I'll manage. give me some time...

     

    Besides, can there be a difference in explain-plan when doing 1000000 INSERTs in a table without any index ? (The 100000, I achieve with PL/SQL. Could it be the time needed for switching between PL/SQL and SQL ???)

     

    Kind regards,

     

    Mynz

  • 7. Re: New Server, less Performance on Writes
    sb92075 Guru
    Currently Being Moderated

    Mynz wrote:

     

    OK,

     

    let's try. Remote access to both databases is not really fast and I cannot use my usual tools. But I'll manage. give me some time...

     

    Besides, can there be a difference in explain-plan when doing 1000000 INSERTs in a table without any index ? (The 100000, I achieve with PL/SQL. Could it be the time needed for switching between PL/SQL and SQL ???)

     

    Kind regards,

     

    Mynz

    same code should have same EXPLAIN PLAN  on different DBs configured with the same parameters.

    Different results occur when something is different; which may or may not be the Oracle database configuration.

    At the OS level, how long on each system to complete OS file copy of file that is 1TB in size?

  • 8. Re: New Server, less Performance on Writes
    Mynz Newbie
    Currently Being Moderated

    Hello,

     

    this is what I use to test:

     

    DROP TABLE Test_WriteSpeed;

    CREATE TABLE Test_WriteSpeed(x NUMBER, y VARCHAR2(256)) TABLESPACE STAT_DAT;

    CREATE OR REPLACE PROCEDURE p$Test_WriteSpeed

      AS

      BEGIN

        FOR i IN 1..10000000

          LOOP

              INSERT INTO Test_WriteSpeed( x,y ) VALUES (i, TO_CHAR(i));

          END LOOP;

      END;

    /

     

    SET TIMING ON;

     

    BEGIN

      p$Test_WriteSpeed;

    END;

    /

     

    The explain-plan, according to TOAD, was

     

    Plan

    1 LOAD TABLE CONVENTIONAL

    2 Rows were inserted.

     

    for the slow system. The fast system didn't give me any information.

     

    The copy-test with one TB, I coudln't perform yet (I don't have TBs on this system, and it's in use at the moment). But the IT-guys found that the caching/acknowledge-behaviour was a bit unsuitable. This "small" change increased writing speed, so that we are now only 1,5 as slow as the old system. This is better, but not good (we're checking for more here).

     

    Regards,

     

    Thomas

  • 9. Re: New Server, less Performance on Writes
    sybrand_b Guru
    Currently Being Moderated

    This is a poor test, isn't it.
    Each time the INSERT is executed you will incur a context switch between the PL/SQL and the SQL engine and you will get a soft parse.

    So you are not only testing the speed of disks, but you are also testing the performance of CPU.

    If you would start tracing the session and start querying the views I recommended you would get much more accurate results.

     

    ----------

    Sybrand Bakker

    Senior Oracle DBA

  • 10. Re: New Server, less Performance on Writes
    Mynz Newbie
    Currently Being Moderated

    Well,

     

    as poor as it is, the test represents what the regular import is doing. And because the new system is bigger, faster, etc. in CPU and memory, the INSERT should be at least faster on new system than on old.

     

    Concerning "context switch between the PL/SQL and the SQL engine", I have in mind that there was a big improvement from 10g to 11g. So even this should not deliever a result as we have it.

     

    All in all, I would expect at least same speed as worst case. Or am I too optimistic ?

     

    Regards,

     

    Thomas

  • 11. Re: New Server, less Performance on Writes
    sybrand_b Guru
    Currently Being Moderated

    Nobody knows what you changed and so far you have been unable or unwilling to answer any question or to implement any analysis method suggested, stating you are 'not familiar' with that.

    As you have access to the online documentation, and nobody here has access to your system, it is up to YOU to follow up the suggestions of this forum.

    You state the  system is bigger and faster, but maybe everything is laid out on RAID-5 drives or your online redolog files are too small.

    If you want help you should allow others to help you to answer your questions.

    So far you have not been helpful at all.

     

    -------------

    Sybrand Bakker

    Senior Oracle DBA

  • 12. Re: New Server, less Performance on Writes
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    1) a Ferrari is generally faster than a bicycle, but it doesn't mean that it will always get you from A to B faster.

    2) your hardware has all the answers, we don't. All we can do is to pursuade you to "talk" to your hardware

    (e.g. by taking a trace file or looking at I/O) -- but so far we haven't been very successful with that.

     

    Best regards,

      Nikolay

  • 13. Re: New Server, less Performance on Writes
    jgarry Guru
    Currently Being Moderated

    Might be worthwhile comparing statspacks between the two systems.  It might be something as silly as, the cpu is faster, so i/o has to wait more for the cpu to be released to see the acknowledgement of the previous i/o... or buffers on controller or disk are saturated, and writes lose... or db writer is getting choked out by log writer... or you're getting messed up by direct reads and writes choking PGA or some undo issue... or...

     

    Also, tkprof is easy to find cookbooks for.

     

    Here's an example of how you might trace: Database using ASSM Tablespace Exhibits Slow Insert Performance After an Uncommitted Delete | Charles Hooper's Oracle Notes

  • 14. Re: New Server, less Performance on Writes
    FahdMirza Oracle ACE
    Currently Being Moderated

    Hello,

       While your insert is running, you may try running the Snapper tool  by Tanel poder. Google it to get it. Its very light-weight and its free and there is no installation. Run it, and check what is waiting for what, and/or paste the output here for further trouble shooting.

     

    Thanks.

1 2 Previous Next

Legend

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