OraSal: Physical concepts in Oracle database

In this post I want to share with you two physical concepts that are suitable for Oracle Database.

The Heisenberg Uncertainty Principle [1]
The energy [2]

The Heisenberg Uncertainty Principle

The principle is about the capacity to influence a system while you are observing it. Actually, Heisenberg wrote the principle for subatomic particle, but the effect is the same if you consider a database.

For example, if you want to monitor the performance of a query, you can probe the database each 1 second. Obviously this has an impact on the database self. Anyway, you can find more details in [1]

Starting on 10g, Oracle introduced a server-generated alert that have a very low overhead (0.1% of the resources), because it is the system self to inform you and not vice versa.

One of the most important thing of the "Intelligent Self-Management Infrastructure" (this is the name that Oracle introduced), is the V$ACTIVE_SESSION_HISTORY performance view where Oracle samples the active session state, each second. Is like to watch a movie [3]. The basic idea is that we are not interested on what a query is doing in real time, but if it runs for a long time (10 second or more - obviously the time depend on what you are searching and how much time you can wait. In this case, 10 is just a number), sampling each second is a sufficient time to understand what a query is doing. Exactly as you can understand what the horse is doing also if you can see him each second.

The energy

You often hear about "energy". The "energy" is a capacity of a system to do something. More high is the energy, more work a system can do. And also you heard about the "power". The "power" is the "energy" consumed during the time. So, "energy" and "power" are intimately related.

Waht about if you have 2 system? Which one perform better if thay have different energy. Well it depend what you are looking for. In our case we are interested in to do the same thing using less work. In other words using less energy.

"Intelligent Self-Management Infrastructure" has introduced the "DB Time". Database time is defined as the sum of the time spent inside the database processing user requests [2]. It's mean that DB Time measure only the time of the active sessions.

A session can (a) working, consuming CPU, or (b) waiting that something else ends. When a user is waiting for something then the "wait event" give you the reason. Faor example, a user that search data from 2 or more tables can consume CPU when columns need to match to satisfy the join condition or wait until data is found on disk. In this case, the "wait event" could be "db file sequential read".

NOTE: This is a very important concept: Total Wait Time = CPU Time + Wait Time

The sum of the total time users spent to wait or to work is the measured by DB Time. It is like the SI
unit ‘Joule’ used in Physics to measure energy or work done. The rate at which the database time is consumed is the database load average which is analogous to the OS load average (measured as ‘Database Time’/second this unit is analogous to the SI unit ‘Watt’ or ‘Joules/second’ used in Physics to measure power) [2].

The Database Time/second is also called Average Active Session.

Ohhhh. How can Database Time related to Active Sessions? This is a very interesting question. The basic difference between them is that the former is a continue measurement, the latter is discrete. The DB Time is measured directly by the RDBMS using the difference between point times, the Active Session is a sample. But...based on how we define the Active Session, if you reduce samples in always more little interval, at the end Active Session collapse on DB Time.

From [6] I extract 4 slides that explain the mathematics behind these concepts.

This is how you can define DB Time mathematically:

You can define the DB Time as the limit of Active Session when the interval goes to zero:

It means that you can approximate the sum of all ASH samples with DB Time:

By definition, you can visualize the area under the DT time curve as the sum of rectangle that are the time of Active Session. I wrote on this in my old post here (part 1), here (part 2) and here (part 3)