DB Time (2 of 3)
DB Time (3 of 3) (soon)
When you fight with tuning, you always have to deal with time. The end user, perceives all problems in term of response time. Time is the link from user experience with database performance.
In Oracle 10g, was introduced the “Intelligent Infrastructure”.
The goal of this infrastructure is:
- self-monitor
- self-diagnostic
- self-tuning
This was in 2004.
In this post and the next, I'm going to talk about 2 tools the Oracle “Intelligent Infrastructure” give us:
- DB Time
- ASH
It unit of measure is microseconds and you can get it directly from Data Dictionary. The views are:
- V$SYS_TIME_MODEL
- V$SESS_TIME_MODEL
The important thing is that not always the waits impact the performance. If not, then we consider this waits as "Idle". Instead, if the wait have performance impact, then we consider the wait as “Non-Idle”.
You can write all of this, through a mathematical expression:
In Oracle 10g, was introduced the “Intelligent Infrastructure”.
The goal of this infrastructure is:
- self-monitor
- self-diagnostic
- self-tuning
This was in 2004.
In this post and the next, I'm going to talk about 2 tools the Oracle “Intelligent Infrastructure” give us:
- DB Time
- ASH
DB Time
It is defined as the “amount of time spent within the database in processing an user request”.
It unit of measure is microseconds and you can get it directly from Data Dictionary. The views are:
- V$SYS_TIME_MODEL
- V$SESS_TIME_MODEL
When a process can't consume CPU, it have to wait in order to use CPU again. We know this wait as "wait event". In other words when a process can't use a CPU, it alert you that it need to wait and the reason of wait is the "wait event".
The important thing is that not always the waits impact the performance. If not, then we consider this waits as "Idle". Instead, if the wait have performance impact, then we consider the wait as “Non-Idle”.
So, an active process (that is, it's working) or consume CPU or wait on an Non-idle wait event.
You can write all of this, through a mathematical expression:
DB Time = CPU Time + Non-Idle Wait Time
We consider “Non-Idle” as obvious, so we rewrite the expression as:
DB Time = CPU Time + Wait Time
Really, the right relation is:
DB Time = CPU Time + Wait Time + gap
This is because when the CPUs are too much active, then the “CPU Queue Time” grows, that is, grows the wait time of processes before they use a CPU again. Oracle calls this specific wait as “CPU Wait Time”. Obviously this is not happy name for a wait event.
You can find more details about "gap", using the below links (see references). In any case, here we are interested to the more usual expression:
DB Time = CPU Time + Wait Time
Reference
- What Is Light Green Oracle Database CPU Wait Time
- How OS CPU Run Queue Time Relates To Oracle Database DB Time
- Oracle Wait Events Investigated with Extended Stack Profiling and Flame Graphs
- AWR Ambiguity: Performance reasoning when the numbers don't add up
Nessun commento:
Posta un commento