Physical concept in Oracle database

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

1. The Heisenberg Uncertainty Principle [1]
2. 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)

 

 

REFERENCE

[1] The Self-Managing Database: Automatic Health Monitoring and Alerting or here

[2] Automatic Performance Diagnosis and Tuning in Oracle or here

[3] ASH like a movie

[4] AAS Metric

[5] AAS

[6] Average Active Sessions RMOUG2007

SQL MODEL clause: First example

With this serie of posts, I want you show how to use SQL as spreadsheet.

For our first example, I consider the following query

SELECT
    employee_id,
    last_name,
    salary,
    commission_pct,
    department_id
FROM EMPLOYEES
WHERE (manager_id = 100 OR manager_id IS NULL)
ORDER BY department_id, employee_id;

 

 

This is how it is in a spreadsheet

 

What I want now is to calculate the "salary bonus" as

salary * commission_pct

The query is very simple

SELECT
    employee_id,
    last_name,
    salary,
    commission_pct,
    department_id,
    salary * commission_pct salary_bonus
FROM EMPLOYEES
WHERE (manager_id = 100 OR manager_id IS NULL)
ORDER BY department_id, employee_id;

and the output is

 

In this case, the spreadsheet is

In the formula, I used C2 and D2 columns. As you can see, Excel use 0 (zero) as result also when the commision_pct is NULL.

What about, I want to set the "salary_bonus" of King to SALARY*0,45?

 

The formula used in F14 cell is C14*0,45 (pay attention: here the decimal separator is the "comma", not the "full stop". This is because the Excel I'm using is configured to use the Italian language. The NLS_TERRITORY in the database is AMERICA, instead: I'm using AMERICAN_AMERICA.WE8MSWIN1252 as NLS_LANG on my laptop, so in SQL you can see the decimal separator as "full stop").

Now in SQL:

WITH
--------------
    bonus AS (
--------------
    SELECT
        employee_id,
        last_name,
        salary,
        commission_pct,
        department_id,
        salary * commission_pct salary_bonus
    FROM EMPLOYEES
    WHERE (manager_id = 100 OR manager_id IS NULL))
--------------
--------------
SELECT *
FROM bonus
MODEL
    DIMENSION BY (employee_id)
    MEASURES (
        last_name,
        salary,
        commission_pct,
        department_id,
        salary_bonus)
    RULES(
        salary_bonus[100] = salary[100] * .45)
ORDER BY department_id, employee_id;

 

 Some notes:

1. SALARY_BONUS is a calculated column, so you can't use it directly in the MODEL clause. For this reason, I used WITH
2. The columns in the result set are the one you specify in the DIMENSION BY and MEASURES (and PARTITION BY not used in this example)
3. The order of the columns positions depend on the order you specify them in the MEASURES
4. The values of MEASURES have to be unique, otherwise the ora-32638 is returned
5. You can use Live SQL, to run your test 
6. You can find all scripts of Oracle sample schema on GitHub