This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Oct 10, 2013 1:48 PM by smee RSS

Transactions in awr

smee Newbie
Currently Being Moderated

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 Pro
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Oracle ACE Director
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Oracle ACE Director
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Oracle ACE Director
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    Thank you. Will give it a read.

1 2 Previous Next

Legend

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