Can someone explain what the authors meant by the marked phrases in this document? (i have attached a print screen): View image: question
What is 'contention'? And why 'CPU time always tends do DEcrease as load on the system increases"? This "load of the system" is the wait time? So if wait time increases CPU time tends to decrease?
If 'CPU time always tends do decrease as load on the system increases", why in the second phrase is written "a system where the proportion of CPU time does NOT decrease significantly as load increases can scale better". Why?
I'm confused in this context, please clarify me.
If you are attending the course of Performance Tuning, this is going to be explained by your instructor. A simple example which I give to my students when this slide comes up is as following.
I ask them to tell me the name of the busiest road in their city which gets the baddest traffic jam in the morning and evening hours(if I am aware about the city, I tell them that road). Once they mention it, I ask them that what would happen if we are going to drive on that road in a 1000cc engine car? will be able to reach 70-80Kms/hour speed in the morning or evening at the peak times? The answer(of course) is a no. I ask them again that how about we get a Ferrari , can we go now by the same speed? The answer is a No again! Then I ask them how about if we drive in that first , 1000cc car in the night at 2-3am, can we go now , over the same road at 60-70kms/hour and they say definitely yes. The next question I ask them is how about if I want to go over 200kms/hour in the same car, can I achieve it? The answer is a no and now they say, a Ferrari would be required.
So what's the moral of this story?
The bottleneck (contention) wasn't the car's engine(the hardware of the machine) but was the traffic on the road. No matter what kind of hardware you bring, you can't make it work more faster than the last one if you won't tweak the bottleneck. Once you do so, using the same hardware, you can achieve the performance you were looking for. But there would be a limit to that hardware and if you want to beyond what it can offer, a h/w upgrade would be required. The CPU being consumed won't be bad as long as you know where and why it is consumed. A FTS and Single Row Access by INdex , both would consume CPU but which access path is thebest for you, you need to see. If you know that FTS can't be avoided, going for a larger HW may be theonly choice you have(for example EXadata) but if you know that you can remove the FTS by a query change, you should do that.
No, I'm reading the SQL Tuning Release 2 (exam 1z0-117) course .
Thank you for that example, nice example, but I cannot correlate this example with what it's mentioned in that course. So why the proportion of CPU decreases as the load on the system increases? Shouldn't CPU proportion increase too in this case?
Which part of your example maps to this phrase: "Conversely, a system where the proportion of CPU time does NOT decrease significantly as load increases can scale better, ..." ?
Any response here, please?
It is not true "Proportion of CPU time to wait time decreases as the load on the system increases" , even in your document(in above link, it looks like guide of course ) say that "tends to decrease". We can not say that exactly(interesting there in your doc say "always tends") when system load increase then it means CPU(does not matter proportion of CPU) time will decrease. We need refer exact statistics. Increasing loading system it mean increase database workload,so database time will increase. But database Time is contain both CPU and wait times. Without exact values(statistics from AWR,etc) we can not say that about CPU time/Wait Time relationships. If i have enough resources (CPU and Memory) when increasing DB workload why my CPU time will decrease?. Yes CPU time can be decrease also can not.
DB Time = CPU Time(or service time) + Wait Time.
But here main matter decreasing DB Time. So it mean that tuning purpose reducing CPU and Wait Times.
If you want to learn
- What is my workload?
- What is exactly my CPU and Wait Time?
- Why CPU time(also wait time) increase (also decrease)?
Then you can use AWR(Statspack) or ASH report and track CPU time(or CPU Time/Wait Time relation ship) for according time periods