1 2 Previous Next 16 Replies Latest reply: Oct 10, 2013 3:48 PM by smee RSS

    Transactions in awr

    smee

      Hi all,

       

      I'm using awr report to understand and tune my database. I use tool to test my database (a lot of inserts and updates).

      Here is my OS and DB config:

      - Microsoft Windows Server 2008 R2; 16G ram; Intel i5 quad core.

      - Oracle 10.2.0.5.0; 4G sga; 2G pga; 3 groups with 2 redo log file and each is 600M; 3 DBWRs.

       

      As a test I use 300 inserts and/or updates by second and I tested it for 16 hour. My question is why I have only 178 user calls per sec in my awr report? I'm also interested to increase number of transactions per sec, does anyone have an idea how to do this? I tried to increase number of DBWRs but it did not help me.

       

      Here is part of awr report:

      "

      Report Summary

      Cache Sizes

       

      BeginEnd
      Buffer Cache:3,296M3,280MStd Block Size:8K
      Shared Pool Size:736M752MLog Buffer:14,348K

      Load Profile

       

      Per SecondPer Transaction
      Redo size:1,341,755.437,505.35
      Logical reads:17,518.0697.99
      Block changes:10,417.9458.27
      Physical reads:0.130.00
      Physical writes:84.470.47
      User calls:178.871.00
      Parses:1,624.989.09
      Hard parses:178.841.00
      Sorts:537.333.01
      Logons:0.030.00
      Executes:2,024.1611.32
      Transactions:178.77

       

      % Blocks changed per Read:59.47Recursive Call %:97.09
      Rollback per transaction %:0.00Rows per Sort:1.69

      Instance Efficiency Percentages (Target 100%)

       

      Buffer Nowait %:100.00Redo NoWait %:100.00
      Buffer Hit %:100.00In-memory Sort %:100.00
      Library Hit %:91.52Soft Parse %:88.99
      Execute to Parse %:19.72Latch Hit %:100.00
      Parse CPU to Parse Elapsd %:99.10% Non-Parse CPU:62.17

      Shared Pool Statistics

       

      BeginEnd
      Memory Usage %:87.1185.78
      % SQL with executions>1:6.957.32
      % Memory for SQL w/exec>1:7.046.90

      Top 5 Timed Events

       

      EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait Class
      CPU time 1,898 86.9
      log file sync634,937318114.6Commit
      log file parallel write641,991293013.4System I/O
      control file sequential read4,98482.4System I/O
      control file parallel write2,04831.1System I/O

       

      "

      Tnx in advance,

      smee

        • 1. Re: Transactions in awr
          Alvaro
          My question is why I have only 178 user calls per sec in my awr report?

           

          User calls metric definition : User Calls (per second)

           

          It's not simply a count of the queries you submitted. It factors in parses and logons as well. Also, it's important to notice that these metrics are normalized within an snapshot, that is to say, it wasn't every single second that you reach 178.87 user calls, rather that was the average of user calls per sec during the snapshot range awr analyzed.

           

          I'm also interested to increase number of transactions per sec, does anyone have an idea how to do this?

          Submit more transactions ! Insert , update,delete, etc.

           

          Regards

          • 2. Re: Transactions in awr
            Jonathan Lewis

            You haven't told us anything about how you've implemented your insert/update mechanism, nor how you know that you're doing 300 per second. From the stats I'd have to guess that you're trying to call a pl/sql procedure with a changing input parameter from a client program and the pl/sql procedure does a couple of changes and then commits. This would explain

             

            a) 178 USER calls when you think you're doing 300 inserts/updates

            b) USER calls = hard parses = transactions (nearly)

            c) parses much higher than user calls

            d) executes much higher than user calls

            e) Non-parse CPU being only 62% (i.e. CPU used in parsing being high)

             

             

            Regards

            Jonathan Lewis

            • 3. Re: Transactions in awr
              smee

              Hi Alvaro.

               

              Thank you for your response.

               

              "This metric represents the number of logins, parses, or execute calls per second during the sample period." I thought that I'll have at least 300 user calls, and more - because I execute that number of queries.

               

              "Submit more transactions ! Insert , update,delete, etc." - I already tried this whit 1000 transactions per sec, but the result was more or less same.

               

              I'm still wonder if I could send 1000 queries per second to the database, can I reach same number of transaction in the database.

               

              BR,

              smee

              • 4. Re: Transactions in awr
                Alvaro

                As Mr. Lewis has inferred it seems your parse activity is what is limiting your throughput here. How are you sending this 300 transactions per second?

                 

                If it's serialized (say in a LOOP) then you may be setting it to 300 but it's only sending as fast as the database is retuning, which is 178.

                 

                This is likely what is happening. By increasing the activity you are only increasing the backlog of your procedure not the throughput of the database, and so the user calls do not vary significantly.

                 

                This would also explain why you have set such high values of "transaction per second" on your app/proc and yet no concurrency-class events seem to arise.

                • 5. Re: Transactions in awr
                  smee

                  Yes, both of you have right.

                   

                  The tool I use collect 300 queries in sec, then send them to the database. It is like a buffer, every second 300 queries are filled in that buffer and if first 300 queries does not execute in sec the buffer grows, and then I send 600 queries......

                   

                  So then I have to see why and how Oracle cannot execute 300 queries in sec.

                  The first thing is definitively slow hdd, maybe slow network, indexes..

                   

                  Thank you for help.

                  smee

                  • 6. Re: Transactions in awr
                    Alvaro

                    One of the key things of increasing DML activity throughput would be to move your redo log files to faster disks, even SSD's. (log file write and sync events)

                     

                    Check on your redo log switch frequency, you should be doing one every 20 or 15 minutes tops, if you're doing more than that it's likely you will get a better performance increasing their size.

                     

                    Also you may you want to investigate cursor sharing to reduce some of that parsing activity which seems a bit high.

                    • 7. Re: Transactions in awr
                      Jonathan Lewis

                      Alvaro wrote:

                       

                      One of the key things of increasing DML activity throughput would be to move your redo log files to faster disks, even SSD's. (log file write and sync events)

                       

                      Check on your redo log switch frequency, you should be doing one every 20 or 15 minutes tops, if you're doing more than that it's likely you will get a better performance increasing their size.

                       

                      Also you may you want to investigate cursor sharing to reduce some of that parsing activity which seems a bit high.

                       

                      The figures show that he's spending less than 0.5 milliseconds per log file write, and just a little more on log file sync.  The total wait for log file sync in 1 hour is 318 seconds, compared to 1,900 seconds of CPU usage.  (Which means there's 1,400 seconds of time "lost" somewhere to the way he's writing his code - and 38% of his reported CPU is going into parsing whatever he sends to the database anyway). Do you really think that his redo log files are the critical point here ?

                       

                      Regards

                      Jonathan Lewis

                      • 8. Re: Transactions in awr
                        smee

                        Yes,

                         

                        I have from 22 to 24 log switches per hour. Maybe I have to increase log files to be greater at least 6 time then they are now.

                         

                        Thanks again, I'll do further investigate.

                        • 9. Re: Transactions in awr
                          Jonathan Lewis

                          smee wrote:

                           

                          Yes,

                           

                          I have from 22 to 24 log switches per hour. Maybe I have to increase log files to be greater at least 6 time then they are now.

                           

                          Thanks again, I'll do further investigate.

                           

                          So how much time are you losing per hour waiting on log file switch completion ?

                          It's less than 3 seconds according to the report you posted.

                          How much further investigation does this need ?

                           

                          Regards

                          Jonathan Lewis

                          • 10. Re: Transactions in awr
                            smee

                            Hi Jonathan,

                             

                            I want to investigate if this is bottleneck, and other possible bottlenecks.

                             

                            log file switch completion (awr):

                            waits 342

                            total wait time (s) 12

                            Avg wait (ms) 35.

                             

                            I resized my redo log files(1.2G) and will see if there will be any changes.

                             

                            Tnx,

                            smee

                            • 11. Re: Transactions in awr
                              Jonathan Lewis

                              smee wrote:

                               

                              Hi Jonathan,

                               

                              I want to investigate if this is bottleneck, and other possible bottlenecks.

                               

                              log file switch completion (awr):

                              waits 342

                              total wait time (s) 12

                              Avg wait (ms) 35.

                               

                              I resized my redo log files(1.2G) and will see if there will be any changes.

                               

                              Tnx,

                              smee

                               

                              And is that 12 seconds also from a 1 hour AWR report ?

                               

                              There's nothing wrong with taking some action to claw back a few seconds on a production system if it is very easy to do and totally safe - but chasing 12 seconds in 3,600 when you're trying to understand what's really wrong is NOT the right approach.

                               

                              Regards

                              Jonathan Lewis

                              • 12. Re: Transactions in awr
                                smee

                                Hi Jonathan,

                                 

                                It is from 16 hour AWR report. And I also have to correct myself and say that I have 11 redo log file switches per 1 hour (it is my mistake).

                                 

                                When you said in that way, it is bad approach

                                My starting point was actually the goal I want to reach - 300 queries (insert/update) per sec or 1 transaction with 300 queries per sec. And I reached that goal, but only for 5 minutes..

                                 

                                Tnx,

                                smee

                                • 13. Re: Transactions in awr
                                  jgarry

                                  Alvaro wrote:

                                   

                                  One of the key things of increasing DML activity throughput would be to move your redo log files to faster disks, even SSD's. (log file write and sync events)

                                   

                                  Maybe look at  Storage Myths: Put Oracle Redo on SSD | flashdba and De-Confusing SSD (for Oracle Databases)

                                  • 14. Re: Transactions in awr
                                    Alvaro

                                    Thank you. Will give it a read.

                                    1 2 Previous Next