This discussion is archived
7 Replies Latest reply: Feb 20, 2013 4:38 AM by 977635 RSS

Tuning question:   AWR Top 5 Foreground Wait events

977635 Newbie
Currently Being Moderated
This is a question regarding Oracle 11GR2, (on Solaris) - or maybe more of a confirmation that I am assessing this correctly.

I ran an AWR report for a period of one hour during peak time this morning.

When I look at the instance efficiency, everything looks good (>99%), except %Non-Parse CPU which is down at 76.85%.
My Soft Parse % is 99.23%, so it seems to be using shared pool more than it was a month ago when my soft-parse was at 53.80%.
But what is Non-Parse CPU?

Okay, next, my top 5 foreground wait events look like this:?
Top 5 Timed Foreground Events

Event                      Waits       Time(s)     Avg wait (ms)     % DB time     Wait Class
DB CPU                             7,421                          68.84      
db file sequential read     567,055       1,988                    4      18.44             User I/O
direct path read     23,035         530                   23       4.92             User I/O
log file sync             33,881         325                   10       3.01             Commit
read by other session     245,776         314                    1       2.91             User I/O
So, with DB CPU using 68.84% of the DB time, it appears to me our database is CPU starved.
But, when I run top or sar on the os, it seems idle most of the time.

Anyway, next we see db file sequential read is using 18.44% of CPU time.
This isn't real high, but since it is second highest on the list, I'm assuming this means that the read/write time to disk is not optimal.
Should I be concerned about this, or focus on the CPU time?
  • 1. Re: Tuning question:   AWR Top 5 Foreground Wait events
    Rob_J Journeyer
    Currently Being Moderated
    Hi,

    Your DB CPU is not starved, then. A session has to be waiting on something, that just happens to be mainly CPU in this case. If your servers are not using maxing out the CPU, you don't have a problem with CPU on the server. See this about [url http://www.ora00600.com/scripts/owi/cputime_top5.html]CPU time in top events to calculate your actual available CPU seconds compared to how many you are using.

    The next thing you would want to look at is the SQL and see if you can make the statement more efficient so that they don't use as much CPU.

    What is the response time on the disk? Check that to see how quickly they are responding. Anything like 5 msec or below is good. Atually, it says 4msec on the report so that's not a problem. Again, you might be able to reduce the wiats on db file sequenital read if you tune your queries. Is it spread across a load of queries or specific ones take up most of the processing power? It will say in the report.

    have a look at the log file sync wait event and you might be able to get that down a little. 10msec, compared to the systems I use, is quite high. We have 1.8msec response time for log file sync. Not saying you can, but maybe you can reduce that a little. Look at this AFTER the SQL queries.

    Most of the time it's the SQL in a half decently configured database which is going to give you the biggest gains.

    Rob
  • 2. Re: Tuning question:   AWR Top 5 Foreground Wait events
    977635 Newbie
    Currently Being Moderated
    Thanks Rob. That seems to make more sense because as i said, when I run sar or check top, the cpu and disks are pretty much idle.

    yeah, I know that tuning the SQL is where the best gains are returned, but I'm only the DBA and anyway, the sql is all provided by the vendor and we can't really change it without violating our certification and contract with the vendor. (Although some of our reports may be able to be modified. Actually, I'd prefer to get the reports out of the database entirely and put them into another database on another server that they can run the reports from. This could be done with MVs I suppose, but would still be a lot of trouble to set up and would still impact the primary database because any updates to the tables that had MVs on them would now have to update across a db link to another database - more cpu processing).

    So, what do you make of this:

    I ran the AWR report a month ago and it shows a very low efficiency for Soft Parse % (53.80%).
    Now, I've made a few changes such as setting cursor_sharing to FORCE, and increased shared_pool_reserved_size, increased session_cached_cursors, and also unrelated, but set filesystemio_options to SETALL (was async). I also set btree_bitmap_plans to FALSE.

    Now, my Soft Parse % is at 99.23%. Do you think this is related to the changes I made?
  • 3. Re: Tuning question:   AWR Top 5 Foreground Wait events
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    I've written a blog post on interpreting AWR CPU information: http://savvinov.com/2012/04/06/awr-reports-interpreting-cpu-usage/. Hopefully it will clarify things for you.

    Best regards,
    Nikolay
  • 4. Re: Tuning question:   AWR Top 5 Foreground Wait events
    977635 Newbie
    Currently Being Moderated
    Thank you very much. I'll read through it.
  • 5. Re: Tuning question:   AWR Top 5 Foreground Wait events
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    If your database server has 4 CPU Cores, total available CPU time is a 4 x 3600 == 14400 seconds. (If it has 8 cores, total available CPU time is 28800 seconds). Compare the database CPU time of 7,421 seconds with the total available CPU time.

    we see db file sequential read is using 18.44% of CPU time
    It is actually 18.44% of DB Time. DB Time is CPU Time + Wait Time (other than Idle Waits) for database sessions. Your average wait time is 4ms which is not high. The average wait time depends on the presence of filesystem buffer cache, SAN storage cache (and the hit ratios in these caches) in addition to disk i/o seek+access+transfer time.


    Hemant K Chitale
  • 6. Re: Tuning question:   AWR Top 5 Foreground Wait events
    Rob_J Journeyer
    Currently Being Moderated
    Hi,
    yeah, I know that tuning the SQL is where the best gains are returned, but I'm only the DBA and anyway, the sql is all provided by the vendor and we can't really change it without violating our certification and contract with the vendor. (Although some of our reports may be able to be modified. Actually, I'd prefer to get the reports out of the database entirely and put them into another database on another server that they can run the reports from. This could be done with MVs I suppose, but would still be a lot of trouble to set up and would still impact the primary database because any updates to the tables that had MVs on them would now have to update across a db link to another database - more cpu processing).
    OK, well if you can't change the SQL then there isn't much you can do about it. Every system has to have waits so maybe the SQL is tuned as much as possible and this is the costs associated with running the system and it's running optimally...
    So, what do you make of this:

    I ran the AWR report a month ago and it shows a very low efficiency for Soft Parse % (53.80%).
    Now, I've made a few changes such as setting cursor_sharing to FORCE, and increased shared_pool_reserved_size, increased session_cached_cursors, and also unrelated, but set filesystemio_options to SETALL (was async). I also set btree_bitmap_plans to FALSE.
    My first response would be, did you make all of these changes at once!?

    So I assume that the remainder of the parsing was hard parsing. What was your cursor_sharing parameter set to originally? Does the application code not use bind variables? If not, I retract what I said earlier about the system being optimal...Ideally, I think you would want it set to EXACT because that would mean that your application uses binds and I believe is the recommended approach.

    Increasing the cached cursors would help remove the need for hard parsing, so that is likely to increase the soft parse ratio, especially if previously you were thrashing the shared pool so parsed statements were being aged out and having to be hard parsed again the next execution.

    Are you using automatic memory management? Personally, I thought you would only want to increase the shared_pool_reserved_size when you had frequent requests for larger chunks of memory for some sql or pl/sql which couldn't not be found in a contiguous chunk in the shared pool. I could be wrong about that. What made you increase it? Were you seeing issues?

    Why did you change the btree parameter to FALSE?
    Now, my Soft Parse % is at 99.23%. Do you think this is related to the changes I made?
    Overall, I'd say that providing everything else remains the same (load, user queries, etc) and you made the changes and saw this effect then yes. Which ones of the changes resulted in this you can't be as sure about if you made all of them at the same time. I'd say, though, that it's going to be the session cached cursors and cursor sharing parameters which made the biggest difference to that stat.

    The biggest question is: Is performance better as a result??
  • 7. Re: Tuning question:   AWR Top 5 Foreground Wait events
    977635 Newbie
    Currently Being Moderated
    My first response would be, did you make all of these changes at once!?
    No... only changes that relate to each other. Then, we monitor and wait at least a week before making another change.
    What was your cursor_sharing parameter set to originally? Does the application code not use bind variables?
    Actually, I don't know what it was originally set to.
    The application vendor, BMC recommended it to be set to FORCE.
    When I came to this site, someone had set it to EXACT. No. Our application uses very few bind variables.
    Are you using automatic memory management?  
    Yes, but regarding shared_pool_reserved_size, I don't know why it was increased.
    Perhaps this was requested by another person, but I checked REQUEST_MISSED from v$shared_pool_reserved, and it is zero (which is what we want).
    Perhaps we can "reset" this parameter and let Oracle AMM maintain it.
    But if I recall, Oracle only maintains this at 5% of shared_pool, which I also remember that recommended setting is 10%.
    Perhaps you have some input on this that would be helpful.
    Why did you change the _b_tree parameter to FALSE?
    I read some web pages that there is an Oracle bug that causes the CBO to think there were bitmapped indexes and then it tries to use them causing performance problems.
    Well, this was a mistake. :-) And recently I did some testing and proved that leaving it at "TRUE" was absolutely better.
    Oh well... we live and learn. Sometimes we make mistakes.

    The one thing that is strange that I did not understand was when I looked at the explain plan, it showed several "BITMAP CONVERSION TO ROWIDS" even though we don't have any bitmap indexes on the subject tables in the query. But, when setting btree parameter to FALSE, I got TABLE ACCESS BY INDEX ROWID which seemed like the logical better thing. I think this is what originally made me think that we were in fact hitting this bug.

    But further testing revealed that having btree_bitmap_plans=TRUE was actually a better explain plan and lowered cost and faster query results. While it is puzzling why the explain plan using this setting shows BITMAP CONVERSION TO ROWIDS when in fact we do not have any bitmap indexes, I'm ignoring that and going with it since it is faster and the costs are lower.

    Go figure!

    :-)

    Edited by: 974632 on Feb 19, 2013 12:08 PM

Legend

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