1 2 Previous Next 15 Replies Latest reply: Nov 25, 2013 4:08 AM by Mynz RSS

    New Server, less Performance on Writes

    Mynz

      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

          Additional information: This is a Standard Edition One

          • 2. Re: New Server, less Performance on Writes
            Nikolay Savvinov

            Hi,

             

            trace it.

             

            Best regards,

              Nikolay

            • 3. Re: New Server, less Performance on Writes
              sybrand_b

              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

                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

                  post formatted EXPLAIN PLAN from both sustems

                  • 6. Re: New Server, less Performance on Writes
                    Mynz

                    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

                      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

                        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

                          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

                            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

                              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

                                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

                                  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
                                    Fahd.Mirza

                                    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