Tuesday, February 10, 2015

What Is That Light-Green Oracle Database CPU Wait Time?

This page has been permenately moved. Please CLICK HERE to be redirected.

Thanks, Craig.


What Really Is That Light-Green Oracle Database CPU Wait Time?



Have you ever wondered what that light-green "cpu wait time" really means in Oracle Enterprise Manager? It's what I call, the "gap" time. The "gap" time is the "missing" or the "leftover" time when DB Time does not equal the DB CPU (foreground process CPU consumption) plus the non-idle wait time. And, it happens more often than you might think.

If you have ever noticed that the database time seems too large, then you need to read this article. And, if you really want to know what the light-green "cpu wait time" in your OEM charts is, then you need to read this article. It's that good.

If you're serious about Oracle performance tuning and analysis, you'll want to know I just posted my complete 2015 public training schedule. It's on the main OraPub.com page HERE. Remember, alumni receive a 50% discount...crazy I know.

My Experiment Shows...


My experiment shows a strong relationship between the "gap" time and operating system CPU utilization. This means that a significant portion of the "gap" time is Oracle foreground processes sitting in the CPU run queue ready to consume CPU. This CPU run queue time is not part of DB CPU but it part of DB Time. So, when the CPU run queue time increases, so does DB Time and so does the "gap" time. And I have the data to show it! And you can run the same experiment yourself.

Let me put this another way. Most of the DB Time "gap" is Oracle foreground processes waiting in the operating system CPU run queue so they can eventually and truly consume CPU.

This is really important: When an Oracle foreground process is not consuming CPU but is sitting in the CPU run queue, Oracle Active Session History (ASH) facility records the session sample state as "CPU" and if the Oracle process is a foreground process (not a background process) Oracle's time model records this time as DB Time but not DB CPU. So in both the ASH and time model cases, someone must do some math to calculate this "cpu wait time".

But that name... "cpu wait"!

CPU Wait Time Is A Lousy Name


"CPU wait time" is a lousy name. Why? Mainly because it has caused lots of confusion and speculation. The name would be more appropriately called something like, "cpu queue time." Three reasons come to mind.

First, wait time means something special to Oracle DBAs. To an Oracle DBA anything associate with a "wait" should have a wait event name, a wait occurance, the time should be instrumented (i.e., measured) and should be recorded in the many wait interface related views, such as v$system_event or v$session.

Second, from an Oracle perspective the process is truly "on cpu" because the process is not "waiting." Remember, an Oracle session is either in one of two states; CPU or WAIT. There is no third choice. So the words "CPU Wait" are really confusing.

Third, from an OS perspective or simply a non-Oracle perspective, the Oracle process is sitting in the CPU run queue.

I'm sure in some Oracle Corporation meeting the words "cpu wait" were considered a great idea, but it has caused lots of confusion. And I'm sure it's here to stay.

What Does This "CPU WAIT" Look Like In OEM?


In OEM, the "cpu wait" is a light green color. I grabbed a publically available screenshot off the internet and posted it below. Look familiar? 


OK, so it's really easy to spot in OEM. And if you've seen it before you know EXACTLY what I'm referring to.

What Is CPU Wait Time?


First, let's review what we do know.

1. DB CPU is true Oracle foreground process CPU consumption as reported by the OS through a system call, such as getrusage.

2. CPU Wait time is derived, that is, somebody at Oracle wrote code to calculate the "cpu wait" time.

3. CPU Wait time is a lousy name because it causes lots of confusion.

4. CPU Wait time is shown in OEM as a light green color. DB CPU is shown as a dark/normal green color.

Second, I need to define what I'll call the DB Time "gap." This is not error and I am not implying something is wrong with database time, that it's not useful or anything like that. All I am saying is that sometimes DB Time does not equal DB CPU plus the non-idle wait time. Let's put that in a formula:

DB Time = DB CPU + non Idle Wait Time + gap

Really, What Is CPU Wait Time?


Now I'm ready to answer the question, "What is CPU WAIT time?" Here is the answer stated multiple ways.

"CPU Wait" time is Oracle foreground process OS CPU run queue time.

I ran an experiment (detailed below) and as the OS CPU utilization increased, so did the DB Time "gap" implying that the gap is CPU run queue time or at least a significant part of it.

I ran an experiment and there was a strong correlation between OS CPU utilization and the DB Time "gap" implying that the gap is CPU run queue time.

I ran an experiment and using queuing theory I was able to predict the "gap" time implying that the gap is CPU run queue time. (Whoops... sorry. That's what I'll present in my next post!)

So I'm very comfortable stating that when DB Time is greater than Oracle process CPU consumption plus the non-idle wait time, it's probably the result of Oracle foreground process CPU run queue time.

Yes, there could be some math problems on Oracle's side, there could be uninstrumented time (for sure it's happened before), the operating system could be reporting bogus values or a host of other potential issues. But unless there is an obvious wrong value, I'm sticking with the experimental evidence.

Now I'm going to show the experimental "evidence" that is, that the DB Time "gap" time correlates with the OS CPU utilization.

Let The Data Drive Our Understanding


You can download all the data collection scripts, raw experimental data, Mathematica notepad files, graphic files, etc HERE in a single zip file.

You should be able to run the experiment on any Linux Oracle test system. All you need is a logical IO load and for that I used my free opload tool which, you can download HERE.

The experiment placed an increasing logical IO load on an Linux Oracle 12c system until the operating system CPU utilization exceeded 90%. The load was increased 18 times. During each of the 18 loads, I gathered 31 three minute samples. Each sample contains busy time (v$osstat), idle time (v$osstat), logical IO (v$sysstat "session logical reads"), non-idle wait time (v$system_event where wait_class != 'Idle'), DB CPU (v$sys_time_model), background cpu time (v$sys_time_model), database time (v$sys_time_model DB time) and the sample time (dual table current_timestamp).

The CPU utilization was calculated using the "busy idle" method that I blog about HERE. This method is detailed in my Utilization On Steroids online video seminar.

The workload is defined as the logical IOs per second, lio/s.

Below is a table summarizing the experimental data. The times shown are the averages. If you look at the actual raw experimental data contained in the analysis pack, you'll notice the data is very consistent. This is not suprising since the load I placed should produce a very consistent workload.

Do you see the gaps? Look closely at load 18. The DB Time is 8891.4 seconds. But the sum of DB CPU (996.8 seconds) and the non-idle wait time (2719.2) seconds only equals 3716.0. Yet DB Time is 8891.4. So the "gap" is 5175.3 which is DB Time (8891.3) minus DB CPU (996.8) minus the non-idle wait time (2719.2).

Note: Load 11 and 12 where excluded because of a problem with my data collection. Sorry.

While we can numberically see the DB Time "gap" increase as the CPU utilization increases, check out the graphic in the next section!

The Correlation Between CPU Utilization And DB Time Gap


We can numerically and visually see that as the CPU utilization increases, so does the DB Time "gap." But is there a strong mathematical correlation? To determine this, I used all the experimental samples (except load 11 and 12). Because there was 17 different workloads and with each workload I gathered 31 samples, the correlation comprises of something like 527 samples. Pretty good sample set I'd say.

The correlation coefficient is a strong 0.891. The strongest is 1.0 and the weakest is 0.

Graphically, here is the scatterplot showing the relationship between the CPU utilization and the workload.

Don't expect the DB Time "gap" and OS CPU utilization correlation to be perfect. Remember that DB Time does not include Oracle background process CPU consumption, yet it is obviously part of the OS CPU utilization.

Summary


My experiment indicated the light-green "CPU wait time" is primarily Oracle foreground process operating system CPU run queue time. This is DB Time "gap" time.

My experiment also showed the "gap" time is highly correlated with CPU utilization. Which means, as the CPU utilization increases, so does the "gap" time.

If there are Oracle Database instrumentation bugs or a host of other potential problems, that will also affect the "gap" time.

If you want a more complete and detailed DB Time formula is would be this:

DB Time = DB CPU + Non Idle Wait Time + gap time

In my next post, I'll show you how to calculate the gap time based on queuing theory!

Thanks for reading!

Craig.








31 comments:

  1. Hi Craig -

    "CPU Wait time is a lousy name because it causes lots of confusion."

    Don't really agree here about the lousy name or the source of confusion, so I wouldn't regard it so much as fact than opinion. Yes the name was decided by committee and maybe it could be better, but "gap time" would not be any less confusing and doesn't easily translate into a relationship with "run queue time". I think the real confusion is due to the fact that this value is derived from other values as you describe, and not measured directly like all the other values on the EM Perf Page. The page should describe the calculation somewhere to eliminate the confusion, not look for a better name.

    One reason this was not called "run queue time" is that it is only a lower-bound estimate of run queue time so we did not want to imply that it was how much run queue time was accumulating altogether. It's important to remember (and you don't mention in your post) that when system is CPU-constrained run queue time will distort the wait event timings, perhaps significantly. That is, some (and maybe lots) of run queue time will be "captured" and accounted as wait time. See my slideshare presentation on ASH for details. This distortion can be really severe in the case of frequent low-latency events like latch gets. If you repeat your experiments with a latch-get-and-CPU-heavy workload and measure the run queue time externally, you should see that the "gap time" only accounts for part of the run queue time, the part when session activity was: CPU-run queue-CPU. When session goes CPU-wait-CPU then the run queue time associated with accessing the CPU after the wait is accounted as part of the wait time (and therefore INCLUDED in DB Time)

    JB

    ReplyDelete
  2. Halo Teman, lama saya cari-cari akhirnya ketemu di blog ini.
    Konten yg sangat menarik, Jadi senang berkunjung di sini, Makasih ya sob.
    Salam best Friend :
    Jual Plat Stainless,
    jual Plat Aluminium,
    Lisati Indonesia,
    Lia Property Indonesia

    ReplyDelete
  3. It is really very excellent,i find all articles was amazing.awesome way to get exert tips from everyone,
    not only i like that post all peoples like that post,because of all given information was wonderful and it's very helpful for me.
    dotnet training in Chennai

    ReplyDelete
  4. This blog provides useful information about new techniques and concepts.very impressive lines are given which is very attractive.
    informatica training in chennai

    ReplyDelete
  5. Thank you for taking the time to provide us with your valuable information. We strive to provide our candidates with excellent care and we take your comments to heart.As always, we appreciate your confidence and trust in us.


    VMware Training in Chennai

    ReplyDelete
  6. This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.I like it and help me to development very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.

    PPC Services in Chennai

    ReplyDelete
  7. I do agree with all the ideas you have presented in your post. They’re really convincing and will certainly work. Still, the posts are very short for newbies. Could you please extend them a little from next time? Thanks for the post..

    Web development company in Chennai

    ReplyDelete
  8. Thank you for sharing such a nice and interesting blog with us. i have seen that all will say the same thing repeatedly. But in your blog, I had a chance to get some useful and unique information. I would like to suggest your blog in my dude circle. please keep on updates. hope it might be much useful for us. keep on updating...
    Software Testing Training

    ReplyDelete
  9. It's interesting that many of the bloggers to helped clarify a few things for me as well as giving.Most of ideas can be nice content.The people to give them a good shake to get your point and across the command .

    digital marketing company in chennai
    seo company in chennai

    ReplyDelete
  10. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.
    Software Testing Training

    ReplyDelete
    Replies
    1. Really i enjoyed very much. And this may helpful for lot of peoples. So you are provided such a nice and great article within this.

      Hr Consultancy in Chennai

      Delete
  11. Superb. I really enjoyed very much with this article here. Really it is an amazing article I had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.thank you for sharing such a great blog with us. expecting for your.
    Digital Marketing Company in India

    ReplyDelete
  12. Great site for these post and i am seeing the most of contents have useful for my Carrier.Thanks to such a useful information.Any information are commands like to share him.

    Seo Training in Chennai

    ReplyDelete
  13. Thanks for sharing this information and keep updating us. This is more informatics and it really helped me to know the software Testing.Check the following links about SoftWare Testing .its really helped.


    software Testing Training in Chennai
    testing training in chennai
    Software testing institutes in Chennai

    ReplyDelete
  14. Thanks for sharing the useful information about the topic and good points were stated in the blog for the further information visit the site
    Oracle Fusion Financials Training

    ReplyDelete
  15. I think this is an great blogs. Such a very informative and creative contents. These concept is good for these knowledge.I like it and help me to development very well.Thank you for this brief explanations.

    Oracle Training in Chennai

    ReplyDelete
  16. Being new to the blogging world I feel like there is still so much to learn. Your tips helped to clarify a few things for me as well as giving..
    Android App Development Company
    iOS App Development Company

    ReplyDelete
  17. Really Good blog post.provided a helpful information.keep updating...
    Digital marketing company in Chennai

    ReplyDelete
  18. Good and interesting article . thanks for sharing your post

    Software Testing Training in chennai

    ReplyDelete
  19. Its a wonderful post and very helpful, thanks for all this information. You are including better information regarding this topic in an effective way.Thank you so much

    Installment loans
    Payday loans
    Title loans
    Cash Advances

    ReplyDelete
  20. You have provided an nice article, Thank you very much for this one. And i hope this will be useful for many people.. and i am waiting for your next post keep on updating these kinds of knowledgeable things...
    Fitness SMS
    Fitness Text
    Salon SMS
    Salon Text
    Investor Relation SMS
    Investor Relation Text

    ReplyDelete
  21. Superb. I really enjoyed very much with this article here.
    Really it is an amazing article I had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.thank you for sharing such a great blog with us. expecting for your.

    Oracle Fusion Procurement Online Training

    ReplyDelete
  22. very very amazing explaintion....many things gather about yourself...yes realy i enjoy it
    SEO Company in India

    ReplyDelete
  23. very very amazing explaintion....many things gather about yourself...yes realy i enjoy it
    seo company in Chennai

    ReplyDelete
  24. you are posting such a new idea is very interesting and give updates.
    msbi training in chennai

    ReplyDelete
  25. your post conveys a good messages and interesting things and give more updates.
    digital marketing company in india

    ReplyDelete

  26. This information is impressive; I am inspired with your post writing style & how continuously you describe this topic.


    Pawn Shop

    Pawn Loans

    Pawn Shops

    Pawn Loan

    Pawn Shop near me

    ReplyDelete
  27. great and nice blog thanks sharing..I just want to say that all the information you have given here is awesome...Thank you very much for this one.
    web design Company
    web development Company
    web design Company in chennai
    web development Company in chennai
    web design Company in India
    web development Company in India

    ReplyDelete