The oldest posts, are written in Italian. If you are interested and you want read the post in English, please use Google Translator. You can find it on the right side. If the translation is wrong, please email me: I'll try to translate for you.

lunedì, marzo 14, 2016

DB Time (1 di 3) (ENG)

Here the Italian version

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

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:

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


Nessun commento: