Analytic Functions preserve the "PARTITION BY" clause (Part 6): LAST_VALUE explained

<< Part05

LAST_VALUE is an analytic function. Here there is the documentation.

"LAST_VALUE is an analytic function that is useful for data densification. It returns the last value in an ordered set of values. 

If the last value in the set is null, then the function returns NULL unless you specify IGNORE NULLS. If you specify IGNORE NULLS, then LAST_VALUE returns the last non-null value in the set, or NULL if all values are null.

The default is RESPECT NULLS."

The first thing, is to show how LAST_VALUE works using RESPECT NULLS (for simplicity I'm going to use only 3 columns)

with
-----------
fix_1_ as (
-----------
SELECT
    elaboration_day,
    machine_id,
    lag(location_type) over (partition by machine_id order by elaboration_day) next_r,
    case
        when lag(location_type) over (partition by machine_id order by elaboration_day) is null
        then elaboration_day
        when lag(location_type) over (partition by machine_id order by elaboration_day) != location_type
        then elaboration_day
        else null
    end fix,
    location_type,
    commercial_id,
    warehouse_id
FROM
    awp_machine
    where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy'))
-------------
, fix_2_ as (
-------------
SELECT
    elaboration_day,
    LAST_VALUE (fix) RESPECT NULLS
            OVER (PARTITION BY machine_id ORDER BY elaboration_day
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) fix,
    location_type
FROM
    fix_1_)
----------------------
select
    *
 from fix_2_
 order by elaboration_day;

 

By definition from the documentation

Because I partitioned by "machine_id", in this simple case, the set are all rows from 01//Jan to 15/Jan.

The first value NOT NULL is the 01/Jan/20. The second value NOT NULL is the 04/Jan/20. The 3th, the 10/Jan/20 and the 4th, 13/Jan/20.

All others rows are NULLS.

Using the IGNORE NULLS option (always by definition):

with
-----------
fix_1_ as (
-----------
SELECT
    elaboration_day,
    machine_id,
    lag(location_type) over (partition by machine_id order by elaboration_day) next_r,
    case
        when lag(location_type) over (partition by machine_id order by elaboration_day) is null
        then elaboration_day
        when lag(location_type) over (partition by machine_id order by elaboration_day) != location_type
        then elaboration_day
        else null
    end fix,
    location_type,
    commercial_id,
    warehouse_id
FROM
    awp_machine
    where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy'))
-------------
, fix_2_ as (
-------------
SELECT
    elaboration_day,
    LAST_VALUE (fix) IGNORE NULLS
            OVER (PARTITION BY machine_id ORDER BY elaboration_day
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) fix,
    location_type
FROM
    fix_1_)
----------------------
select
    *
 from fix_2_
 order by elaboration_day;

 

The key here is the "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" option. In fact

• On 2/Jan/20 the not null LAST_VALUE from 01/Jan (UNBOUNDED PRECEDING) to 02/Jan  (CURRENT ROW), is 01/Jan/20. On 03/Jan, the LAST_VALUE NOT NULL from 01/Jan (UNBOUNDED PRECEDING) to 03/Jan (CURRENT ROW), is 01/Jan/20.
• On 04/Jan, the not null LAST_VALUE from 01/Jan (UNBOUNDED PRECEDING) to 04/Jan  (CURRENT ROW), is 04/Jan/20. On 05/Jan, the LAST_VALUE NOT NULL from 01/Jan (UNBOUNDED PRECEDING) to 05/Jan (CURRENT ROW), is 04/Jan/20.
• On 10/Jan, the not null LAST_VALUE from 01/Jan (UNBOUNDED PRECEDING) to 10/Jan  (CURRENT ROW), is 10/Jan/20. On 11/Jan, the LAST_VALUE NOT NULL from 01/Jan (UNBOUNDED PRECEDING) to 11/Jan (CURRENT ROW), is 10/Jan/20.
•  On 13/Jan, the not null LAST_VALUE from 01/Jan (UNBOUNDED PRECEDING) to 13/Jan  (CURRENT ROW), is 13/Jan/20. On 15/Jan, the LAST_VALUE NOT NULL from 01/Jan (UNBOUNDED PRECEDING) to 15/Jan (CURRENT ROW), is 13/Jan/20
  

<< Part05

Analytic Functions preserve the "PARTITION BY" clause (Part 5)

<< Part04     Part06 >>

Now, using the LAST_VALUE analytic function, I can "fill the blank"

with
-----------
fix_1_ as (
-----------
SELECT
    elaboration_day,
    machine_id,
    lag(location_type) over (partition by machine_id order by elaboration_day) next_r,
    case
        when lag(location_type) over (partition by machine_id order by elaboration_day) is null
        then elaboration_day
        when lag(location_type) over (partition by machine_id order by elaboration_day) != location_type
        then elaboration_day
        else null
    end fix,
    location_type,
    commercial_id,
    warehouse_id
FROM
    awp_machine
    where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy'))
-------------
, fix_2_ as (
-------------
SELECT
    elaboration_day,
    machine_id,
    LAST_VALUE (fix) IGNORE NULLS
            OVER (PARTITION BY machine_id ORDER BY elaboration_day
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) fix,
    location_type,
    commercial_id,
    warehouse_id
FROM
    fix_1_)
select * from fix_2_
    order by elaboration_day;

 

 

I'll explain why the LAST_VALUE "fill the blank" in a different post.

At this point I can separate the 4 interval. To show this I use again COUNT and ROW_NUMBER analytic function

with
-----------
fix_1_ as (
-----------
SELECT
    elaboration_day,
    machine_id,
    lag(location_type) over (partition by machine_id order by elaboration_day) next_r,
    case
        when lag(location_type) over (partition by machine_id order by elaboration_day) is null
        then elaboration_day
        when lag(location_type) over (partition by machine_id order by elaboration_day) != location_type
        then elaboration_day
        else null
    end fix,
    location_type,
    commercial_id,
    warehouse_id
FROM
    awp_machine
    where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy'))
-------------
, fix_2_ as (
-------------
SELECT
    elaboration_day,
    machine_id,
    LAST_VALUE (fix) IGNORE NULLS
            OVER (PARTITION BY machine_id ORDER BY elaboration_day
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) fix,
    location_type,
    commercial_id,
    warehouse_id
FROM
    fix_1_)
----------------------
select
    elaboration_day,
    machine_id,
    count(location_type) over (partition by machine_id, location_type, fix) nrd,
    row_number() over (partition by machine_id, location_type, fix order by elaboration_day) rn,
    location_type,
    commercial_id,
    warehouse_id
from fix_2_
    order by elaboration_day;

 

How you can see, now COUNT function (NRD column) count only rows in it a local range and not over all. The same is true for ROW_NUMBER function (NR column) that restart each time LOCATION_TYPE change.

<< Part04     Part06 >>

 

Analytic Functions preserve the "PARTITION BY" clause (Part 4)

<< Part03     Part05>>

Now the question is: is it exists a way to not preserve the partitioning?

There are a lot of solutions to this question. In the next posts I'm going to show you the one I used.

First of all, I have to evaluate the previous row: if the location change, then there is a "point of change" as in the following picture

SELECT
    elaboration_day,
    lag(location_type) over (partition by machine_id order by elaboration_day) next_r,
    location_type,
    commercial_id,
    warehouse_id
FROM
    awp_machine
    where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy')
    order by elaboration_day;

 

 So, there are 2 different conditions to evaluate the "point of change":

1. WHEN next "location_type" is NULL (true only for the first row)
   
2. WHEN next "location_type" != actual "location_type"
   

 IF one of these 2 conditions are true THEN I use the elaboration_day, ELSE NULL

 SELECT
    elaboration_day,
    lag(location_type) over (partition by machine_id order by elaboration_day) next_r,
    case
        when lag(location_type) over (partition by machine_id order by elaboration_day) is null
        then elaboration_day
        when lag(location_type) over (partition by machine_id order by elaboration_day) != location_type
        then elaboration_day
        else null
    end fix,
    location_type,
    commercial_id,
    warehouse_id
FROM
    awp_machine
    where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy')
    order by elaboration_day;

 

<< Part03     Part05 >>

Analytic Functions preserve the "PARTITION BY" clause (Part 3)

<< Part02     Part04 >>

In order to understand why, I use the ROW_NUMBER analytic function

SELECT
    elaboration_day,
    row_number() over (partition by machine_id, location_type order by elaboration_day) rn,
    location_type,
    commercial_id,
    warehouse_id
FROM
    awp_machine
    where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy')
    order by elaboration_day;

How you can see, the ROW_NUMBER, preserve the PARTITIONING and numerate in ordered way the days inside the partition.

It could be strange, but it's the (right) way the analytic function works.

<< Part02     Part04 >>

 

Analytic Functions preserve the "PARTITION BY" clause (Part 2)

<< Part01     Part03 >>

Question. I want to know how many continues days the slot-machine was in specific location

Start using minimum columns:

SELECT
    elaboration_day,
    location_type,
    commercial_id,
    warehouse_id
FROM
    awp_machine
    where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy')
    order by elaboration_day;

 If I count the days by LOCATION_TYPE:

SELECT
    elaboration_day,
    count(location_type) over (partition by machine_id, location_type) nrd,
    location_type,
    comme+rcial_id,
    warehouse_id
FROM
    awp_machine
    where elaboration_day >= to_date('01/01/2020', 'dd/mm/yyyy')
    order by elaboration_day;

I don't have 3 + 3 + 6 + 3 days. Instead I have 6 + 9 days as shown in the following picture

This is because the COUNT analytic function, count the days where the machine was during the interval I use (15 days in this case: 01/Jan - 15/Jan)

 

 

<< Part01     Part03 >>

Analytic Functions preserve the "PARTITION BY" clause (Part 1)

Part02 >>

The Analytic Functions are very powerful and I use them every day. What I learned is that some behaviors could seems strange or bugs, but it's the normal way how to the Analytic Function works.

This is the scenario: a slot-machine has a counter that identify how much money is inserted until now. For example if on 03/Jan at 23:59:59, the counter is 10, then 10€ are inserted into machine until 03/Jan.

On  04/Jan at 23:59:59, the counter is 11. This means that 11€ are inserted into machine until 04/Jan.

On 04/Jan the machine gained 1€ (11 - 10).

In order to know how much money a slot machine has gained in one day I need the counters of the machine, day by day.

A slot machine can be in a Bar or in Warehouse if it needs repair. If it is in the Bar then people can play, otherwise, if the machine is in Warehouse, it isn't used and gain 0 (zero). Anyway, because the machine is "ACTIVE" (potentially it could gain money), by default we assign 560€ as "FLAT" value (it is a convention used by the govern).

So, the CNTTOTIN is the counter IN and represent the money the people insert into machine. At the same way, there is the CNTTOTOT and represent the money the people win (coin OUT from the machine).

When FLAT is used, it means that the machine is not read, then the previous CNTTOTIN is used as counter. In this way, the TAXABLE report the FLAT value, meanwhile the WINNINGS is set to 0 (zero).

 

 

COLUMN NAME	MEANING
ELABORATION_DAY	Day of elaboration
MACHINE_ID	Machine identifier
CNTTOTIN	IN counter
CNTTOTOT	OUT counter
COMMERCIAL_ID	Location identifier
WAREHOUSE_ID	Warehouse identifier
WINNINGS	How much money the people wins in a day (2)
TAXABLE	How much money the machine gained in a day (flat value if "active" and in warehouse) (1)(2)
LOCATION_TYPE	CME for COMMERCIAL LOCATION; WRH for WAREHOUSE
CONTRACT_BASE_ID	Contract identifier if the machine is in WRH, NULL if in CME
CONTRACT_COMMERCIAL_ID	Contract identifier if the machine is in CME, NULL if in WRH
OPERATION_TYPE	"Read" if the cpunter is detected, "Flat" otherwise

(1) The column name reflect the fact that from the value, we don't have yet substracted taxe

(2) The values are in €cent

 

Here, how to create the table and popolate it.

 

CREATE TABLE AWP_MACHINE (

    ELABORATION_DAY          DATE, 

    MACHINE_ID               NUMBER(15),

    CNTTOTOT                 NUMBER(15),

    CNTTOTIN                 NUMBER(15),

    COMMERCIAL_ID            NUMBER(15),

    WAREHOUSE_ID             NUMBER(15),

    WINNINGS                 NUMBER(15),

    TAXABLE                  NUMBER(15),

    LOCATION_TYPE            VARCHAR2(3 BYTE),

    CONTRACT_BASE_ID         NUMBER(15),

    CONTRACT_COMMERCIAL_ID   NUMBER(15),

    OPERATION_TYPE           VARCHAR2(8 BYTE) );

Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('01/01/2020', 'DD/MM/YYYY'), 1205770, 73535500, 55272700, 542703, NULL,
    200, 1000, 'CME', 2656069, 'Read');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('02/01/2020', 'DD/MM/YYYY'), 1205770, 73537900, 55274700, 542703, NULL,
    2000, 2400, 'CME', 2656069, 'Read');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('03/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, 542703, NULL,
    10300, 9100, 'CME', 2656069, 'Read');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('04/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, NULL, 943382,
    0, 56000, 'WRH', 2669766, 'Flat');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('05/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, NULL, 943382,
    0, 56000, 'WRH', 2669766, 'Flat');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('06/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, NULL, 943382,
    0, 56000, 'WRH', 2669766, 'Flat');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('07/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, NULL, 943382,
    0, 56000, 'WRH', 2669766, 'Flat');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('08/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, NULL, 943382,
    0, 56000, 'WRH', 2669766, 'Flat');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('09/01/2020', 'DD/MM/YYYY'), 1205770, 73547000, 55285000, NULL, 943382,
    0, 56000, 'WRH', 2669766, 'Flat');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('10/01/2020', 'DD/MM/YYYY'), 1205770, 73556400, 55288700, 542703, NULL,
    3700, 9400, 'CME', 2656069, 'Read');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('11/01/2020', 'DD/MM/YYYY'), 1205770, 73558800, 55289100, 542703, NULL,
    400, 2400, 'CME', 2656069, 'Read');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('12/01/2020', 'DD/MM/YYYY'), 1205770, 73560200, 55290100, 542703, NULL,
    1000, 1400, 'CME', 2656069, 'Read');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('13/01/2020', 'DD/MM/YYYY'), 1205770, 73560200, 55290100, NULL, 943382,
    0, 56000, 'WRH', 2669766, 'Flat');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('14/01/2020', 'DD/MM/YYYY'), 1205770, 73560200, 55290100, NULL, 943382,
    0, 56000, 'WRH', 2669766, 'Flat');
Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('15/01/2020', 'DD/MM/YYYY'), 1205770, 73560200, 55290100, NULL, 943382,
    0, 56000, 'WRH', 2669766, 'Flat');
COMMIT;

In order to calculate TAXABLE and WINNINGS for the first day, you need an extra insert

Insert into AWP_MACHINE
   (ELABORATION_DAY, MACHINE_ID, CNTTOTIN, CNTTOTOT, COMMERCIAL_ID, WAREHOUSE_ID,
    WINNINGS, TAXABLE, LOCATION_TYPE, CONTRACT_COMMERCIAL_ID, OPERATION_TYPE)
 Values
   (TO_DATE('31/12/2019', 'DD/MM/YYYY'), 1205770, 73534500, 55272500, 542703, NULL,
    NULL, NULL, 'CME', 2656069, 'Read');

Part02 >>

Lock Chains 02: GV$SESSION

In my previous post, I showed the chain of the locks using a query for just an instance. Recently Kaley Crum, an active member of the Orapub community, modified my statement so now you can use it in RAC configuration.

I only modified a little, the output:

col BLOCKING_SESSION for a10
col LAST_LOCKED_SESSION for a10
col PATH_EVENT for a120
set lines 210
set pages 99
with
  snap as (
   select
        inst_id
      , sid
      , blocking_instance
      , blocking_session
      , sql_id
      , prev_sql_id
      , event
      , state
    from
      gv$session)
select
    to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') now
  , CONNECT_BY_ISCYCLE cycle
  , CONNECT_BY_ROOT inst_id ||':'||
    CONNECT_BY_ROOT sid blocking_session
  , inst_id ||':'||
    SID last_locked_session
  , SUBSTR(SYS_CONNECT_BY_PATH(
        DECODE(state,
                     'WAITING', sid|| '@' || inst_id ||'/'||nvl(sql_id, prev_sql_id||'(p)')||'/'||event,
                                sid|| '@' || inst_id ||'/'||nvl(sql_id, prev_sql_id||'(p)')||'/ON CPU'), ' -> ')
          ,5) path_event
 from snap 
where
  level > 1
connect by
  nocycle prior sid = blocking_session
       and prior inst_id = blocking_instance;

Picture 01 - Click to enlarge

Note: How to read the output

1. The column BLOCKING_SESSION and LAST_LOCKED_SESSION are composed of 2 members separated by the colon. For example, on the first line, 1:1313 mean that on the instance 1 the session 1313 is the head of the lock chain where the last element is the session 609 on the instance 1.

2. In the PATH_EVENT column, the "session" and "instance" are related by an "at". For example the on the last line, 1377@1 mean that the session 1377 on the instance 2 is locking the session 1441 on the instance 2

Info about SCAN

In my company, we have more than 1000 databases and sometimes is very hard to manage all of them.

So I created some scripts that help me to manage some things.

One of this is the information on the SCANs

The SCAN port, the SCAN name and the network interface where the SCAN listeners are configured are different. So, in order to check the information of the SCAN configuration, I wrote following script

The output is something like this

###################################################################
########################### SCAN info #############################
###################################################################
Scan Name     :  nodecl-scan
Scan Port     :  1555
Dedined on    :  Network: 1
Subnet IPv4   :  10.10.226.0/255.255.254.0
Network Card  :  eth0
LISTENER_SCAN1:  10.10.227.38/eth0:2 (ONLINE on node01)
LISTENER_SCAN2:  10.10.227.39/eth0:3 (ONLINE on node02)
LISTENER_SCAN3:  10.10.227.40/eth0:3 (ONLINE on node01)
###################################################################

As you can see, the output shows also the interface where the listener is bound. In this case, on the node01, the listeners are on eth0:2 and eth0:3 with 10.10.267.38 and 10.10.267.40, respectively. On the node02 instead, the SCAN listener 2, with IP address 10.10.267.39, is on eth0:3

In case of the crs is down, the script returns the following error

>>>>> CRS is not working correctly <<<<<

>>>>> Check the crs status <<<<<

CRS-6750: unable to get the active version CRS-6752: Active version query failed

Active Session History: Updating the X$ASH, part 03

Part 01
Part 02

Here I try to show you what happens and why Oracle updates the V$ASH view. Please read the pdf by Graham Wood, for more details.

What I analyze is the session "session_id=1732" with "session_serial#=31065" saw in the previous post.

This is what happens

Click for enlarge

The session 1732 (serial# 31065) is sampled 3 times: at sample_id 50243482, 50243483, 50243484. In the last sample_id, there is a change of state of the session. In fact, querying the V$ACTIVE_SESSION_HISTORY, you see

Click for enlarge

But what happens behind the lines is something like this. Pay attention that my conclusions are based on my experiment

Click for enlarge

(1) SAMPLE_ID 50243482/SAMPLE_TIME 15-DEC-17 02:50:40.054

At the 15/12/2017 14:50:40.838429, one row is inserted in X$ASH with SAMPLE_TIME, 14:50:40.054

(2) SAMPLE_ID 50243484/SAMPLE_TIME 15-DEC-17 02:50:42.064

At the 15/12/2017 14:50:42.016313, one row is inserted in X$ASH with SAMPLE_TIME, 14:50:41.064

(3) SAMPLE_ID 50243484/SAMPLE_TIME 15-DEC-17 02:50:41.064

At the 15/12/2017 14:50:43.192604, one row is inserted in X$ASH with SAMPLE_TIME, 14:50:42.064

Note that, at this time, the session 1732 is waiting, but Oracle doesn't know yet how match time the session will wait

(4) SAMPLE_ID 50243484/SAMPLE_TIME 15-DEC-17 02:50:41.064 (the same of the previous one)

At the 15/12/2017 14:50:44.372309 (sample_id 50243485), the last row is updated in X$ASH with SAMPLE_TIME, 14:50:42.064

At sample_id 50243485, the session 1732 finish it works and Oracle know how much time the session waited. So he can update the state of the wait on X$ASH

Here I left the previous row, just to show what happening.

(5) The final result

What you see finally, is a consistent state of the session

Part 01
Part 02

Active Session History: Updating the X$ASH, part 02

Part01
Part03

Starting from previous output, running the following query

set lines 160
col SAMPLE_TIME for a30
set pages 99
select
    sample_id
  , session_id
  , session_serial#
  , time_waited
  , count(*)
  , sample_time 
from 
    my_active_session_history 
group by 
    sample_id
  , session_id
  , session_serial#
  , time_waited
  , sample_time
order by 
    session_id 
  , sample_id
/

you can aggregate the result. The output is here.

I get an extract of this output to try to explain what happens.

What my anonymous PL/SQL (see the previous post) does, is to polling the V$SESSION_ACTIVE_SESSION every second and save the output in MY_ACTIVE_SESSION_HISTORY table.

Because each time, the script read the same rows plus one (the V$ASH is renewed each second), for the same sessions, you see a lot of rows for each loop

Consider, for example, the session 1732/serial 31065.

select
    sample_id
  , session_id
  , session_serial#
  , time_waited
  , count(*)
  , sample_time
from
    my_active_session_history
    where session_id=1732 and session_serial#=31065
group by
    sample_id
  , session_id
  , session_serial#
  , time_waited
  , sample_time
order by
    session_id
  , sample_id
/

Click for enlarge

It appears 50 times for the snap_id 50243482, 49 times for the snap_id 50243483 and 48 times for snap_id 50243484 (see note).

What happens to the session 1732, where for the sample_id=50243484 you see two distinct rows?

The following statement has this output (it is just the detail of the session 1732):

select 
    row_number() over (partition by sample_id order by sample_id, inserting_time) rn
  , inserting_time
  , sample_id
  , wait_time
  , time_waited
  , session_state
  , event
from
    my_active_session_history 
where 
   session_id=1732 and session_serial#=31065
order by 
   sample_id, 1
/

Because I'm interested just in the first rows of each change of "sample_id" and/or change of the "state" inside the same sample_id, I want to see only this changes


with 
  mash as (
    select 
        inserting_time
      , time_waited
      , sample_id
      , sample_time
      , session_id
      , wait_time
      , event
      , session_state
      , row_number() over (partition by sample_id, time_waited, session_state, event order by inserting_time) rn
    from 
      my_active_session_history
    where 
      session_id=1732 and session_serial#=31065
)
select 
    sample_id
  , sample_time
  , inserting_time
  , time_waited
  , session_state
  , event
from 
  mash
where 
  rn =1
order by 
    sample_id
  , inserting_time
/

Click for enlarge

What happens here?

1. At sample_id =50243482 (14:50:40) the sid 1732 is working (it's ON CPU). This means that TIME_WAITED is 0 (zero)
2. One second after (14:50:41), sample_id=50243483, the sid 1732 is still working and TIME_WAITED is still 0 (zero)
3. One second after (14:50:42), sample_id=50243484, the sid 1732 wait on direct path read event, but the TIME_WAITED column is not updated. So while the SESSION_STATE/EVENT columns report the correct state of the session, the TIME_WAITED, not yet (inserting_time=15-DEC-17 02.50.43.192604 PM). Because of this Oracle update on the same SNAP_ID (50243484) this row with the correct value of TIME_WAITED column (inserting_time=15-DEC-17 02.50.44.372309 PM).

In the next session, I try to explain this "update" in a visual way

Note

Is it correct that each next sample_id, have one less? When my PL/SQL script inserts a row the first time, it does at 15-DEC-17 02.50.40.833420 PM (sample_id=50243482). The second time, it inserts at 15-DEC-17 02.50.42.016313 PM (sample_id=50243483): it inserts two seconds after the first time. The third time it inserts at 15-DEC-17 02.50.43.192604.
Anyway, the last record for all samples is the same: 15-DEC-17 02.51.40.488761 PM. Because all samples end at the same time (MX column in the next picture), each of them count one record less.

You can run the following select that show you the min and max timestamp

select * from (
  select
      sample_id
    , session_id
    , session_serial#
    , time_waited
    , sample_time
    , inserting_time
    , max(inserting_time) over (partition by sample_id) mx
    , count(*) over (partition by sample_id) cnt
    , row_number() over (partition by sample_id order by inserting_time) rn_min
    , row_number() over (partition by sample_id order by inserting_time desc) rn_max
  from
      my_active_session_history
  where 
      session_id=1732 and session_serial#=31065
  order by
      session_id
    , sample_id)
where rn_min=1 or rn_max=1
/

Click for enlarge

Please, pay attention. While the inserting_time is the instant when the row is inserted in the MY_ACTIVE_SESSION_HISTORY, the sampling happens each second (in the previous picture I put both SAMPLE_TIME and INSERTING_TIME)

Part01
Part03

Active Session History: updating the X$ASH, part 01

Part 02
Part 03

I'm a proud member of OraPub community. Some days ago I was speaking with Kaley Crum, another member of the OraPub community.

The context was ASH (Active Session History).

He told me that at some time, Oracle does an update of X$ASH in order to update the values of the TIME_WAITED column.

I didn't trust him because my conviction was that Oracle makes just an insert in this X$.

Well, I was wrong. Kaley right. Thanks to him to learn me something new.

On this argument, what is TIME_WAITED column, and some trouble with it. Kayle wrote a post himself.

Also, and I recommend it to you, a couple of post by Craig Shallahamer. You can find them here and here. And not forget the one wrote by Alberto Dell'Era.

And don't miss the pdf of Graham Wood, slide 35, where he shows the fallacies of the TIME_WAITED column in the V$ACTIVE_SESSION_HISTORY view. This series of posts are based on this slide nr 35.

What I want to do is try to explayn myself the problem, but here, in Part 01, I want just write the test I made.


1) Prepare the enviroment

create table my_active_session_history as select * from v$active_session_history where 1=2;
 
alter table my_active_session_history add (inserting_time timestamp);


2) Run the following anonymous PL/SQL


3) Run following statement, to report the result

col INSERTING_TIME for a30
col SAMPLE_TIME for a30
set lines 120
select 
    inserting_time
  , sample_id
  , sample_time
  , session_id
  , session_serial#
  , event
  , session_state
  , time_waited
from
    my_active_session_history
order by 
    session_id
  , sample_id
  , inserting_time;

You can see the result of this last statement, here.

Part 02
Part 03