Oracle database experiences

Oracle database blog

Tag Archives: 11gR2

Custom Partitioning – Part VI – List-Range partitioning – Instead Of Trigger – Multi level partitioning 2

Leave a comment Posted by mourmansk on March 12, 2017

Scope

In the last post we have worked on the part 1 of the implementation and we have created one view and three triggers as shown in the following figure:

In this post we will implement the “Part 2” e.g. the Range partitioning for the ‘Y’ archive_state column rows.
We will set up 4 sub-partitions in the same way than the part III:

One for a birth_date < 01.01.1980,
One for a birth_date >= 01.01.1980 and < 01.01.1990,
One for a birth_date >= 01.01.1990 and < 01.01.1995,
One for a birth_date >= 01.01.1995.

Let’s create the four sub-partitions:

DEV3@pdb1> CREATE TABLE DEV3.P_CUSTOMER_ARCHIVED_MINVALUE
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.P_CUSTOMER_ARCHIVED_1980
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.P_CUSTOMER_ARCHIVED_1990
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.P_CUSTOMER_ARCHIVED_MAXVALUE
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

Now we will create a view that is the UNION of all the previous tables (sub-partitions).

DEV3@pdb1> CREATE OR REPLACE VIEW DEV3.P_CUSTOMER_ARCHIVED
  2  AS
  3   SELECT * FROM DEV3.P_CUSTOMER_ARCHIVED_MINVALUE
  4  UNION ALL
  5   SELECT * FROM DEV3.P_CUSTOMER_ARCHIVED_1980
  6  UNION ALL
  7   SELECT * FROM DEV3.P_CUSTOMER_ARCHIVED_1990
  8  UNION ALL
  9   SELECT * FROM DEV3.P_CUSTOMER_ARCHIVED_MAXVALUE;

View created.

Instead of Trigger – Insert

We will create an instead of trigger for the insert operation in the same way than for the range partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TI_P_CUSTOMER_ARCHIVED
INSTEAD OF INSERT ON DEV3.P_CUSTOMER_ARCHIVED
-- =======================================================================
--  Trigger       : TI_P_CUSTOMER_ARCHIVED
--  Description   : Insert trigger for custom partitioning (List-Range)
--                  Range sub-partition
--  Author        : Arnaud Fargues
--  Creation      : 04.02.2017
--  Modifications :
-- =======================================================================
BEGIN

   CASE
      -- < 1980
      WHEN :new.birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') THEN
         INSERT INTO DEV3.P_CUSTOMER_ARCHIVED_MINVALUE
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            archive_state,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
      -- >= 1980 and < 1990
      WHEN :new.birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND :new.birth_date < TO_DATE('01.01.1990','DD.MM.YYYY') THEN
         INSERT INTO DEV3.P_CUSTOMER_ARCHIVED_1980
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            archive_state,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
      -- >= 1990 and < 1995
      WHEN :new.birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND :new.birth_date < TO_DATE('01.01.1995','DD.MM.YYYY') THEN
         INSERT INTO DEV3.P_CUSTOMER_ARCHIVED_1990
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            archive_state,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
      -- >= 1995
      WHEN :new.birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY') OR :new.birth_date IS NULL THEN
         INSERT INTO DEV3.P_CUSTOMER_ARCHIVED_MAXVALUE
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            archive_state,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
   END CASE;
END;

Instead of Trigger – Delete

We will create an instead of trigger for the delete operation in the same way than for the range partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TD_P_CUSTOMER_ARCHIVED
INSTEAD OF DELETE ON DEV3.P_CUSTOMER_ARCHIVED
-- =======================================================================
--  Trigger       : TD_P_CUSTOMER_ARCHIVED
--  Description   : Delete trigger for custom partitioning (List-Range)
--                  Range sub-partition
--  Author        : Arnaud Fargues
--  Creation      : 04.02.2017
--  Modifications :
-- =======================================================================
BEGIN

   CASE
      -- < 1980
      WHEN :old.birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') THEN
         DELETE DEV3.P_CUSTOMER_ARCHIVED_MINVALUE
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND archive_state = :old.archive_state
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      -- >= 1980 and < 1990
      WHEN :old.birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1990','DD.MM.YYYY') THEN
         DELETE DEV3.P_CUSTOMER_ARCHIVED_1980
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND archive_state = :old.archive_state
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      -- >= 1990 and < 1995
      WHEN :old.birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1995','DD.MM.YYYY') THEN
         DELETE DEV3.P_CUSTOMER_ARCHIVED_1990
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND archive_state = :old.archive_state
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      -- >= 1995
      WHEN :old.birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY') THEN
         DELETE DEV3.P_CUSTOMER_ARCHIVED_MAXVALUE
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND archive_state = :old.archive_state
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
   END CASE;
END;

Instead of Trigger – Update

We will create an instead of trigger for the update operation in the same way than for the range partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TU_P_CUSTOMER_ARCHIVED
INSTEAD OF UPDATE ON DEV3.P_CUSTOMER_ARCHIVED
-- =======================================================================
--  Trigger       : TU_P_CUSTOMER_ARCHIVED
--  Description   : Update trigger for custom partitioning (List-Range)
--                  Range sub-partition
--  Author        : Arnaud Fargues
--  Creation      : 04.02.2017
--  Modifications : 06.05.2017 AF Update
-- =======================================================================
DECLARE
   FUNCTION get_partition(p_birth_date DATE)
   RETURN NUMBER
   IS
      v_return NUMBER := 0;
   BEGIN
      CASE
          WHEN p_birth_date < TO_DATE('01.01.1980','DD.MM.YYYY')
             THEN v_return := 1;
          WHEN p_birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND p_birth_date < TO_DATE('01.01.1990','DD.MM.YYYY')
             THEN v_return := 2;
          WHEN p_birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND p_birth_date < TO_DATE('01.01.1995','DD.MM.YYYY')
             THEN v_return := 3;
          WHEN p_birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY')
             THEN v_return := 4;
          ELSE v_return := -1;
      END CASE;
      RETURN v_return;
   END;
BEGIN
   -- Same paritition
   IF get_partition(:old.birth_date) = get_partition(:new.birth_date)
   THEN
      CASE
         -- < 1980
         WHEN :old.birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') THEN
            UPDATE DEV3.P_CUSTOMER_ARCHIVED_MINVALUE
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                archive_state= :new.archive_state,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND archive_state = :old.archive_state
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
         -- >= 1980 and < 1990
         WHEN :old.birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1990','DD.MM.YYYY') THEN
            UPDATE DEV3.P_CUSTOMER_ARCHIVED_1980
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                archive_state= :new.archive_state,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND archive_state = :old.archive_state
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date));
         -- >= 1990 and < 1995
         WHEN :old.birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1995','DD.MM.YYYY') THEN
            UPDATE DEV3.P_CUSTOMER_ARCHIVED_1990
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                archive_state= :new.archive_state,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND archive_state = :old.archive_state
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
         -- >= 1995
         WHEN :old.birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY') THEN
            UPDATE DEV3.P_CUSTOMER_ARCHIVED_MAXVALUE
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                archive_state= :new.archive_state,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND archive_state = :old.archive_state
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));

      END CASE;
   ELSE

      -- Delete --
      DELETE DEV3.P_CUSTOMER_ARCHIVED
      WHERE cust_id = :old.cust_id
        AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
        AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
        AND birth_date = :old.birth_date
        AND archive_state= :old.archive_state
        AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
        AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));

      -- Insert --
      INSERT INTO DEV3.P_CUSTOMER_ARCHIVED
        (
         cust_id,
         first_name,
         last_name,
         birth_date,
         archive_state,
         creation_date,
         modification_date
        )
      VALUES
        (
         :new.cust_id,
         :new.first_name,
         :new.last_name,
         :new.birth_date,
         :new.archive_state,
         :new.creation_date,
         :new.modification_date
        );

   END IF;

END;

Basically, we must know if the updated row remains in the same partition or not. We have created the subprogram function get_partition in order to have a “partition id”.

09.05.2017

The DEV3.TU_P_CUSTOMER_ARCHIVED trigger source code has been updated.

Partitioning, PL/SQL 11gR2, 12cR1, Composite partitioning, Custom partitioning, DBMS_RANDOM, DELETE, INSERT, Instead of trigger, List-Range partitioning, Multi-level partitioning, Partitioned table, Partitions, Range partitioning, Sub-partitions, SUBPROGRAM, TRUNC, UPDATE

Custom Partitioning – Part V – List-Range partitioning – Instead Of Trigger – Multi level partitioning 1

Leave a comment Posted by mourmansk on February 12, 2017

Scope

In this post we will use a composite partitioning that we will call a multi-level partitioning and more precisely an homogeneous multi-level partitioning.

Case study

Let’s create the CUSTOMER_MODEL_M table as follow in the DEV3 schema:

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_MODEL_M
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        DEFAULT 'N' NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

and a sequence:

DEV3@pdb1> CREATE SEQUENCE  DEV3.S_CUSTOMER_M INCREMENT BY 1 START WITH 1 CACHE 20 NOCYCLE;

Sequence created.

Let’s now fill this table:

DEV3@pdb1> INSERT INTO DEV3.CUSTOMER_MODEL_M
  2    (
  3     cust_id,
  4     first_name,
  5     last_name,
  6     birth_date,
  7     creation_date,
  8     modification_date
  9    )
 10  SELECT DEV3.S_CUSTOMER_M.NEXTVAL,
 11         DBMS_RANDOM.STRING('a',TRUNC(DBMS_RANDOM.VALUE(8,20))),
 12         DBMS_RANDOM.STRING('u',TRUNC(DBMS_RANDOM.VALUE(8,20))),
 13         SYSDATE - (366*18) - TRUNC(DBMS_RANDOM.VALUE(1,10000)),
 14         SYSDATE,
 15         NULL
 16  FROM DUAL
 17  CONNECT BY LEVEL <= 10000;

10000 rows created.

DEV3@pdb1> COMMIT;

Commit complete.

Basically, the column archive_state indicates that a row is archived (‘Y’) or not archived (‘N’).
We will archive some rows as follow:

DEV3@pdb1> UPDATE DEV3.CUSTOMER_MODEL_M
  2  SET archive_state = 'Y'
  3  WHERE cust_id < 8001;

8000 rows updated.

DEV3@pdb1> COMMIT;

Commit complete.

We would like to partition this table firstly on the key archive_state column using a list partitioning distribution method and secondly on the key birth_date column using a range partitioning distribution method.
In our multi-level partitioning paradigm, the list partitioning is the first level and the range partitioning is the second level. We call it homogeneous, because there is the same distribution method at each level.

The following figure describes this case:

We will start by implementing the “Part 1” e.g. the range partitioning for the ‘N’ archive_state column rows.
We will set up 4 sub-partitions in the same way than in the part III:

One for a birth_date < 01.01.1980,
One for a birth_date >= 01.01.1980 and < 01.01.1990,
One for a birth_date >= 01.01.1990 and < 01.01.1995,
One for a birth_date >= 01.01.1995.

Let’s create the four sub-partitions:

DEV3@pdb1> CREATE TABLE DEV3.P_CUSTOMER_ACTIVE_MINVALUE
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.P_CUSTOMER_ACTIVE_1980
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.P_CUSTOMER_ACTIVE_1990
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.P_CUSTOMER_ACTIVE_MAXVALUE
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    archive_state     CHAR(1)        NOT NULL,
  7    creation_date     DATE,
  8    modification_date DATE);

Table created.

Now we will create a view that is the UNION of all the previous tables (sub-partitions).

DEV3@pdb1> CREATE OR REPLACE VIEW DEV3.P_CUSTOMER_ACTIVE
  2  AS
  3   SELECT * FROM DEV3.P_CUSTOMER_ACTIVE_MINVALUE
  4  UNION ALL
  5   SELECT * FROM DEV3.P_CUSTOMER_ACTIVE_1980
  6  UNION ALL
  7   SELECT * FROM DEV3.P_CUSTOMER_ACTIVE_1990
  8  UNION ALL
  9   SELECT * FROM DEV3.P_CUSTOMER_ACTIVE_MAXVALUE;

View created.

Instead of Trigger – Insert

We will create an instead of trigger for the insert operation in the same way than for the range partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TI_P_CUSTOMER_ACTIVE
INSTEAD OF INSERT ON DEV3.P_CUSTOMER_ACTIVE
-- =======================================================================
--  Trigger       : TI_P_CUSTOMER_ACTIVE
--  Description   : Insert trigger for custom partitioning (List-Range)
--                  Range sub-partition
--  Author        : Arnaud Fargues
--  Creation      : 04.02.2017
--  Modifications :
-- =======================================================================
BEGIN

   CASE
      -- < 1980
      WHEN :new.birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') THEN
         INSERT INTO DEV3.P_CUSTOMER_ACTIVE_MINVALUE
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            archive_state,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
      -- >= 1980 and < 1990
      WHEN :new.birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND :new.birth_date < TO_DATE('01.01.1990','DD.MM.YYYY') THEN
         INSERT INTO DEV3.P_CUSTOMER_ACTIVE_1980
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            archive_state,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
      -- >= 1990 and < 1995
      WHEN :new.birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND :new.birth_date < TO_DATE('01.01.1995','DD.MM.YYYY') THEN
         INSERT INTO DEV3.P_CUSTOMER_ACTIVE_1990
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            archive_state,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
      -- >= 1995
      WHEN :new.birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY') OR :new.birth_date IS NULL THEN
         INSERT INTO DEV3.P_CUSTOMER_ACTIVE_MAXVALUE
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            archive_state,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.archive_state,
            :new.creation_date,
            :new.modification_date
           );
   END CASE;
END;

Instead of Trigger – Delete

We will create an instead of trigger for the delete operation in the same way than for the range partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TD_P_CUSTOMER_ACTIVE
INSTEAD OF DELETE ON DEV3.P_CUSTOMER_ACTIVE
-- =======================================================================
--  Trigger       : TD_P_CUSTOMER_ACTIVE
--  Description   : Delete trigger for custom partitioning (List-Range)
--                  Range sub-partition
--  Author        : Arnaud Fargues
--  Creation      : 04.02.2017
--  Modifications :
-- =======================================================================
BEGIN

   CASE
      -- < 1980
      WHEN :old.birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') THEN
         DELETE DEV3.P_CUSTOMER_ACTIVE_MINVALUE
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND archive_state = :old.archive_state
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      -- >= 1980 and < 1990
      WHEN :old.birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1990','DD.MM.YYYY') THEN
         DELETE DEV3.P_CUSTOMER_ACTIVE_1980
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND archive_state = :old.archive_state
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      -- >= 1990 and < 1995
      WHEN :old.birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1995','DD.MM.YYYY') THEN
         DELETE DEV3.P_CUSTOMER_ACTIVE_1990
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND archive_state = :old.archive_state
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      -- >= 1995
      WHEN :old.birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY') THEN
         DELETE DEV3.P_CUSTOMER_ACTIVE_MAXVALUE
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND archive_state = :old.archive_state
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
   END CASE;
END;

Instead of Trigger – Update

We will create an instead of trigger for the update operation in the same way than for the range partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TU_P_CUSTOMER_ACTIVE
INSTEAD OF UPDATE ON DEV3.P_CUSTOMER_ACTIVE
-- =======================================================================
--  Trigger       : TU_P_CUSTOMER_ACTIVE
--  Description   : Update trigger for custom partitioning (List-Range)
--                  Range sub-partition
--  Author        : Arnaud Fargues
--  Creation      : 04.02.2017
--  Modifications : 06.05.2017 AF Update
-- =======================================================================
DECLARE
   FUNCTION get_partition(p_birth_date DATE)
   RETURN NUMBER
   IS
      v_return NUMBER := 0;
   BEGIN
      CASE
          WHEN p_birth_date < TO_DATE('01.01.1980','DD.MM.YYYY')
             THEN v_return := 1;
          WHEN p_birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND p_birth_date < TO_DATE('01.01.1990','DD.MM.YYYY')
             THEN v_return := 2;
          WHEN p_birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND p_birth_date < TO_DATE('01.01.1995','DD.MM.YYYY')
             THEN v_return := 3;
          WHEN p_birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY')
             THEN v_return := 4;
          ELSE v_return := -1;
      END CASE;
      RETURN v_return;
   END;
BEGIN
   -- Same paritition
   IF get_partition(:old.birth_date) = get_partition(:new.birth_date)
   THEN
      CASE
         -- < 1980
         WHEN :old.birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') THEN
            UPDATE DEV3.P_CUSTOMER_ACTIVE_MINVALUE
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                archive_state= :new.archive_state,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND archive_state = :old.archive_state
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
         -- >= 1980 and < 1990
         WHEN :old.birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1990','DD.MM.YYYY') THEN
            UPDATE DEV3.P_CUSTOMER_ACTIVE_1980
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                archive_state= :new.archive_state,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND archive_state = :old.archive_state
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date));
         -- >= 1990 and < 1995
         WHEN :old.birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1995','DD.MM.YYYY') THEN
            UPDATE DEV3.P_CUSTOMER_ACTIVE_1990
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                archive_state= :new.archive_state,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND archive_state = :old.archive_state
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
         -- >= 1995
         WHEN :old.birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY') THEN
            UPDATE DEV3.P_CUSTOMER_ACTIVE_MAXVALUE
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                archive_state= :new.archive_state,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND archive_state = :old.archive_state
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));

      END CASE;
   ELSE

      -- Delete --
      DELETE DEV3.P_CUSTOMER_ACTIVE
      WHERE cust_id = :old.cust_id
        AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
        AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
        AND birth_date = :old.birth_date
        AND archive_state= :old.archive_state
        AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
        AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));

      -- Insert --
      INSERT INTO DEV3.P_CUSTOMER_ACTIVE
        (
         cust_id,
         first_name,
         last_name,
         birth_date,
         archive_state,
         creation_date,
         modification_date
        )
      VALUES
        (
         :new.cust_id,
         :new.first_name,
         :new.last_name,
         :new.birth_date,
         :new.archive_state,
         :new.creation_date,
         :new.modification_date
        );

   END IF;

END;

Basically, we must know if the updated row remains in the same partition or not. We have created the subprogram function get_partition in order to have a “partition id”.

09.05.2017

The DEV3.TU_P_CUSTOMER_ACTIVE trigger source code has been updated.

Custom Partitioning – Part IV – Hash partitioning – Instead Of Trigger – INSERT – DELETE – UPDATE

Leave a comment Posted by mourmansk on January 10, 2017

Scope

In this post we will use the hash partitioning distribution method.
We would like to partition the table DEV3.CUSTOMER_MODEL in five homogeneous partitions i.e. with nearly the same number of rows in each.

First approach

The cust_id column is a good candidate for the partitioning key because it is derived from a sequence. More precisely, we will use the following partitioning expression : MOD(ABS(cust_id),5)

Let’s execute the following query on the source table which will give us the number of rows in each of the future partitions using this distribution method:

DEV3@pdb1> SELECT 'HASH_CUST_' || MOD(ABS(cust_id),5) partition_name,
  2         COUNT(*)
  3  FROM DEV3.CUSTOMER_MODEL
  4  GROUP BY 'HASH_CUST_' || MOD(ABS(cust_id),5)
  5  ORDER BY 1;

PARTITION_NAME                                       COUNT(*)
-------------------------------------------------- ----------
HASH_CUST_0                                              2000
HASH_CUST_1                                              2000
HASH_CUST_2                                              2000
HASH_CUST_3                                              2000
HASH_CUST_4                                              2000

5 rows selected.

As you can see there is the same number of rows in each partition.

Second approach

In the standard hash partitioning distribution method the ora_hash function is used internally on the partitioning keys to distribute the rows in the different partitions. We will use in this case ORA_HASH((cust_id,4) as the partitioning expression.

Let’s execute the following query on the source table which will give us the number of rows in each of the future partitions using this distribution method:

DEV3@pdb1> SELECT 'HASH_CUST1_' || ORA_HASH((cust_id),4) partition_name,
  2         COUNT(*)
  3  FROM DEV3.CUSTOMER_MODEL
  4  GROUP BY 'HASH_CUST1_' || ORA_HASH((cust_id),4)
  5  ORDER BY 1;

PARTITION_NAME                                        COUNT(*)
--------------------------------------------------- ----------
HASH_CUST1_0                                              2045
HASH_CUST1_1                                              1992
HASH_CUST1_2                                              2010
HASH_CUST1_3                                              1979
HASH_CUST1_4                                              1974

5 rows selected.

The lines are much less well distributed than with the previous distribution method.

If you want more information on the ora_hash function linked to the hash partitioning distribution method, I suggest you to consult the post of Jonathan Lewis which can be found at https://jonathanlewis.wordpress.com/2009/11/21/ora_hash-function/.

We will choose the first approach to implement the hash partitioning.

Let’s create the five partitions:

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_HASH_CUST_0
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_HASH_CUST_1
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_HASH_CUST_2
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_HASH_CUST_3
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_HASH_CUST_4
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

Now we will create a view that is the UNION of all the previous tables (partitions).

DEV3@pdb1> CREATE OR REPLACE VIEW DEV3.CUSTOMER2
  2  AS
  3   SELECT * FROM DEV3.CUSTOMER_HASH_CUST_0
  4  UNION ALL
  5   SELECT * FROM DEV3.CUSTOMER_HASH_CUST_1
  6  UNION ALL
  7   SELECT * FROM DEV3.CUSTOMER_HASH_CUST_2
  8  UNION ALL
  9   SELECT * FROM DEV3.CUSTOMER_HASH_CUST_3
 10  UNION ALL
 11   SELECT * FROM DEV3.CUSTOMER_HASH_CUST_4;

View created.

We will also create a function that will compute the partition id as follow:

CREATE OR REPLACE FUNCTION GET_HASH_PARTITION_ID (p_cust_id NUMBER)
RETURN NUMBER AS
-- =======================================================================
--  Function      : GET_HASH_PARTITION_ID
--  Description   : Return the custom hash partitioning id
--  Author        : Arnaud Fargues
--  Creation      : 11.12.2016
--  Modifications : 
-- =======================================================================
   v_return NUMBER := 0;

BEGIN
   IF p_cust_id IS NULL
   THEN
      v_return := -1;
   ELSE
      v_return := MOD(ABS(p_cust_id),5);
   END IF;
   
   RETURN v_return;
   
END GET_HASH_PARTITION_ID;

We will create an instead of trigger for the insert operation in the same way than for the range partitioning:

Instead of Trigger – Insert

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TI_CUSTOMER2
INSTEAD OF INSERT ON DEV3.CUSTOMER2
-- =======================================================================
--  Trigger       : TI_CUSTOMER2
--  Description   : Insert trigger for custom partitioning (hash)
--  Author        : Arnaud Fargues
--  Creation      : 04.12.2016
--  Modifications : 07.01.2017 AF Update
-- =======================================================================
BEGIN

   CASE
      -- MOD(5) = 0
      WHEN get_hash_partition_id(:new.cust_id) = 0 THEN
         INSERT INTO DEV3.CUSTOMER_HASH_CUST_0
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
      -- MOD(5) = 1
      WHEN get_hash_partition_id(:new.cust_id) = 1 THEN
         INSERT INTO DEV3.CUSTOMER_HASH_CUST_1
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
      -- MOD(5) = 2
      WHEN get_hash_partition_id(:new.cust_id) = 2 THEN
         INSERT INTO DEV3.CUSTOMER_HASH_CUST_2
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
      -- MOD(5) = 3
      WHEN get_hash_partition_id(:new.cust_id) = 3 THEN
         INSERT INTO DEV3.CUSTOMER_HASH_CUST_3
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
      -- MOD(5) = 4
      WHEN get_hash_partition_id(:new.cust_id) IN (4,-1) THEN
         INSERT INTO DEV3.CUSTOMER_HASH_CUST_4
           (
            cust_id,
            first_name,
            last_name,
            birth_date,
            creation_date,
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
   END CASE;
END;

Remark: The value for partition id = -1 has been added in the last insert section (DEV3.CUSTOMER_HASH_CUST_4) in order to have all possible values for the get_hash_partition_id function in the CASE statement.

Now we will partition the source table as follow:

DEV3@pdb1> INSERT INTO DEV3.CUSTOMER2
  2    (
  3     cust_id,
  4     first_name,
  5     last_name,
  6     birth_date,
  7     creation_date,
  8     modification_date
  9    )
 10  SELECT *
 11  FROM DEV3.CUSTOMER_MODEL;

10000 rows created.

DEV3@pdb1> COMMIT;

Commit complete.

Let’s execute the following check query:

DEV3@pdb1>  SELECT 'CUSTOMER_HASH_CUST_0' part, COUNT(*)
  2   FROM DEV3.CUSTOMER_HASH_CUST_0
  3  UNION ALL
  4   SELECT 'CUSTOMER_HASH_CUST_1' part, COUNT(*)
  5   FROM DEV3.CUSTOMER_HASH_CUST_1
  6  UNION ALL
  7   SELECT 'CUSTOMER_HASH_CUST_2' part, COUNT(*)
  8   FROM DEV3.CUSTOMER_HASH_CUST_2
  9  UNION ALL
 10   SELECT 'CUSTOMER_HASH_CUST_3' part, COUNT(*)
 11   FROM DEV3.CUSTOMER_HASH_CUST_3
 12  UNION ALL
 13   SELECT 'CUSTOMER_HASH_CUST_4' part, COUNT(*)
 14   FROM DEV3.CUSTOMER_HASH_CUST_4;

PART                   COUNT(*)
-------------------- ----------
CUSTOMER_HASH_CUST_0       2000
CUSTOMER_HASH_CUST_1       2000
CUSTOMER_HASH_CUST_2       2000
CUSTOMER_HASH_CUST_3       2000
CUSTOMER_HASH_CUST_4       2000

5 rows selected.

Instead of Trigger – Delete

We will create an instead of trigger for the delete operation in the same way than for the range partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TD_CUSTOMER2
INSTEAD OF DELETE ON DEV3.CUSTOMER2
-- =======================================================================
--  Trigger       : TD_CUSTOMER2
--  Description   : Delete trigger for custom partitioning (hash)
--  Author        : Arnaud Fargues
--  Creation      : 10.12.2016
--  Modifications :
-- =======================================================================
BEGIN

   CASE
      -- MOD(5) = 0
      WHEN get_hash_partition_id(:old.cust_id) = 0 THEN
         DELETE DEV3.CUSTOMER_HASH_CUST_0
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      -- MOD(5) = 1
      WHEN get_hash_partition_id(:old.cust_id) = 1 THEN
         DELETE DEV3.CUSTOMER_HASH_CUST_1
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      -- MOD(5) = 2
      WHEN get_hash_partition_id(:old.cust_id) = 2 THEN
         DELETE DEV3.CUSTOMER_HASH_CUST_2
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      -- MOD(5) = 3
      WHEN get_hash_partition_id(:old.cust_id) = 3 THEN
         DELETE DEV3.CUSTOMER_HASH_CUST_3
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      -- MOD(5) = 4
      WHEN get_hash_partition_id(:old.cust_id) = 4 THEN
         DELETE DEV3.CUSTOMER_HASH_CUST_4
         WHERE cust_id = :old.cust_id
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));

  END CASE;
END;

Instead of Trigger – Update

We will create an instead of trigger for the update operation in the same way than for the range partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TU_CUSTOMER2
INSTEAD OF UPDATE ON DEV3.CUSTOMER2
-- =======================================================================
--  Trigger       : TU_CUSTOMER2
--  Description   : Update trigger for custom partitioning (hash)
--  Author        : Arnaud Fargues
--  Creation      : 10.12.2016
--  Modifications :
-- =======================================================================

BEGIN
   -- Same paritition
   IF get_hash_partition_id(:old.cust_id) = get_hash_partition_id(:new.cust_id)
   THEN
      CASE
         -- MOD(5) = 0
         WHEN get_hash_partition_id(:old.cust_id) = 0 THEN
            UPDATE DEV3.CUSTOMER_HASH_CUST_0
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
         -- MOD(5) = 1
         WHEN get_hash_partition_id(:old.cust_id) = 1 THEN
            UPDATE DEV3.CUSTOMER_HASH_CUST_1
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
         -- MOD(5) = 2
         WHEN get_hash_partition_id(:old.cust_id) = 2 THEN
            UPDATE DEV3.CUSTOMER_HASH_CUST_2
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
         -- MOD(5) = 3
         WHEN get_hash_partition_id(:old.cust_id) = 3 THEN
            UPDATE DEV3.CUSTOMER_HASH_CUST_3
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
         -- MOD(5) = 4
         WHEN get_hash_partition_id(:old.cust_id) = 4 THEN 
            UPDATE DEV3.CUSTOMER_HASH_CUST_4
            SET cust_id = :new.cust_id,
                first_name = :new.first_name,
                last_name= :new.last_name,
                birth_date= :new.birth_date,
                creation_date= :new.creation_date,
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));
      END CASE;
   ELSE

      -- Delete --
      DELETE DEV3.CUSTOMER2
      WHERE cust_id = :old.cust_id
        AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
        AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
        AND birth_date = :old.birth_date
        AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
        AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));

      -- Insert --
      INSERT INTO DEV3.CUSTOMER2
        (
         cust_id,
         first_name,
         last_name,
         birth_date,
         creation_date,
         modification_date
        )
      VALUES
        (
         :new.cust_id,
         :new.first_name,
         :new.last_name,
         :new.birth_date,
         :new.creation_date,
         :new.modification_date
        );

   END IF;

END;

Basically, we must know if the updated row remains in the same partition or not.

Partitioning, PL/SQL 11gR2, 12cR1, ABS, COUNT, Custom partitioning, DELETE, Hash partitioning, INSERT, Instead of trigger, MOD, ORA_HASH, Partitioned table, Partitions, UPDATE

Custom Partitioning – Part III – Range partitioning – Instead Of Trigger – INSERT – DELETE – UPDATE

Leave a comment Posted by mourmansk on December 4, 2016

Scope

In this post we will use the range partitioning distribution method.
We would like to partition the table DEV3.CUSTOMER_MODEL based on the birth_date column.

We will set up 4 partitions:

One for a birth_date < 01.01.1980,
One for a birth_date >= 01.01.1980 and < 01.01.1990,
One for a birth_date >= 01.01.1990 and < 01.01.1995,
One for a birth_date >= 01.01.1995.

Let’s create the four partitions:

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_BIRTH_DT_MINVALUE
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_BIRTH_DT_1980
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_BIRTH_DT_1990
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_BIRTH_DT_MAXVALUE
  2  ( cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4    last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

Remarks:

In a range partitioning method you can have a MAXVALUE partition that we have used,
We have also defined a MINVALUE partition which represents the first “temporal” partition e.g. based on the minimum birth_date.

Now we will create a view that is the UNION of all the previous tables (partitions).

DEV3@pdb1> CREATE OR REPLACE VIEW DEV3.CUSTOMER1
  2  AS
  3   SELECT * FROM DEV3.CUSTOMER_BIRTH_DT_MINVALUE
  4  UNION ALL
  5   SELECT * FROM DEV3.CUSTOMER_BIRTH_DT_1980
  6  UNION ALL
  7   SELECT * FROM DEV3.CUSTOMER_BIRTH_DT_1990
  8  UNION ALL
  9   SELECT * FROM DEV3.CUSTOMER_BIRTH_DT_MAXVALUE;

View created.

We will create an instead of trigger for the insert operation in the same way than for the list partitioning.

Instead of Trigger – Insert

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TI_CUSTOMER1
INSTEAD OF INSERT ON DEV3.CUSTOMER1 
-- =======================================================================
--  Trigger       : TI_CUSTOMER1
--  Description   : Insert trigger for custom partitioning (range)
--  Author        : Arnaud Fargues
--  Creation      : 20.11.2016
--  Modifications : 02.12.2016 AF Update
-- =======================================================================
BEGIN

   CASE 
      -- < 1980
      WHEN :new.birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') THEN   
         INSERT INTO DEV3.CUSTOMER_BIRTH_DT_MINVALUE
           (
            cust_id,    
            first_name,  
            last_name, 
            birth_date,
            creation_date,      
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
      -- >= 1980 and < 1990 
      WHEN :new.birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND :new.birth_date < TO_DATE('01.01.1990','DD.MM.YYYY') THEN   
         INSERT INTO DEV3.CUSTOMER_BIRTH_DT_1980
           (
            cust_id,    
            first_name,  
            last_name,
            birth_date,
            creation_date,      
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
      -- >= 1990 and < 1995
      WHEN :new.birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND :new.birth_date < TO_DATE('01.01.1995','DD.MM.YYYY') THEN   
         INSERT INTO DEV3.CUSTOMER_BIRTH_DT_1990
           (
            cust_id,    
            first_name,  
            last_name,
            birth_date,
            creation_date,      
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
      -- >= 1995
      WHEN :new.birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY') OR :new.birth_date IS NULL THEN   
         INSERT INTO DEV3.CUSTOMER_BIRTH_DT_MAXVALUE
           (
            cust_id,    
            first_name,  
            last_name,
            birth_date,
            creation_date,      
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
   END CASE;
END;

Note that the condition “:new.birth_date IS NULL” has been added in the last insert section (DEV3.CUSTOMER_BIRTH_DT_MAXVALUE) in order to have all possible values for birth_date column in the CASE statement.

What happens if we try to insert a NULL value in the birth_date column:

DEV3@pdb1> INSERT INTO DEV3.CUSTOMER1
  2    (
  3     cust_id,
  4     first_name,
  5     last_name,
  6     birth_date,
  7     creation_date,
  8     modification_date
  9    )
 10  VALUES
 11    (
 12     -9,
 13     '1',
 14     '2',
 15     NULL,
 16     NULL,
 17     NULL
 18     );
INSERT INTO DEV3.CUSTOMER1
                 *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("DEV3"."CUSTOMER_BIRTH_DT_MAXVALUE"."BIRTH_DATE")
ORA-06512: at "DEV3.TI_CUSTOMER1", line 66
ORA-04088: error during execution of trigger 'DEV3.TI_CUSTOMER1'

This message is not very clear because it doesn’t hide the instead of trigger implementation.

Let’s execute the following query on the source table which will give us the number of rows in each of the previous defined partitions:

DEV3@pdb1> SELECT SUM(p_minvalue) nb_p_minvalue,
  2         SUM(p_1980)     nb_p_1980,
  3         SUM(p_1990)     nb_p_1990,
  4         SUM(p_maxvalue) nb_p_maxvalue
  5  FROM
  6     (SELECT CASE
  7            WHEN birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') THEN 1
  8            ELSE 0
  9         END p_minvalue,
 10         CASE
 11            WHEN birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND birth_date < TO_DATE('01.01.1990','DD.MM.YYYY') THEN 1
 12            ELSE 0
 13         END p_1980,
 14         CASE
 15            WHEN birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND birth_date < TO_DATE('01.01.1995','DD.MM.YYYY') THEN 1
 16            ELSE 0
 17         END p_1990,
 18         CASE
 19            WHEN birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY') THEN 1
 20            ELSE 0
 21         END p_maxvalue
 22      FROM DEV3.CUSTOMER_MODEL
 23      );

NB_P_MINVALUE  NB_P_1980  NB_P_1990 NB_P_MAXVALUE
------------- ---------- ---------- -------------
         3236       3710       1751          1303

1 row selected.

Now we will partition the source table as follow:

DEV3@pdb1> INSERT INTO DEV3.CUSTOMER1
  2    (
  3     cust_id,
  4     first_name,
  5     last_name,
  6     birth_date,
  7     creation_date,
  8     modification_date
  9    )
 10  SELECT *
 11  FROM DEV3.CUSTOMER_MODEL;

10000 rows created.

DEV3@pdb1> COMMIT;

Commit complete.

Let’s execute the following check query:

DEV3@pdb1>  SELECT 'CUSTOMER_BIRTH_DT_MINVALUE' part, COUNT(*)
  2   FROM DEV3.CUSTOMER_BIRTH_DT_MINVALUE
  3  UNION ALL
  4   SELECT 'CUSTOMER_BIRTH_DT_1980' part, COUNT(*)
  5   FROM DEV3.CUSTOMER_BIRTH_DT_1980
  6  UNION ALL
  7   SELECT 'CUSTOMER_BIRTH_DT_1990' part, COUNT(*)
  8   FROM DEV3.CUSTOMER_BIRTH_DT_1990
  9  UNION ALL
 10   SELECT 'CUSTOMER_BIRTH_DT_MAXVALUE' part, COUNT(*)
 11   FROM DEV3.CUSTOMER_BIRTH_DT_MAXVALUE;

PART                         COUNT(*)
-------------------------- ----------
CUSTOMER_BIRTH_DT_MINVALUE       3236
CUSTOMER_BIRTH_DT_1980           3710
CUSTOMER_BIRTH_DT_1990           1751
CUSTOMER_BIRTH_DT_MAXVALUE       1303

4 rows selected.

Instead of Trigger – Delete

We will create an instead of trigger for the delete operation in the same way than for the list partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TD_CUSTOMER1 
INSTEAD OF DELETE ON DEV3.CUSTOMER1 
-- =======================================================================
--  Trigger       : TD_CUSTOMER1
--  Description   : Delete trigger for custom partitioning (range)
--  Author        : Arnaud Fargues
--  Creation      : 20.11.2016
--  Modifications : 
-- =======================================================================
BEGIN
  
   CASE 
      -- < 1980
      WHEN :old.birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') THEN   
         DELETE DEV3.CUSTOMER_BIRTH_DT_MINVALUE
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date   
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
      -- >= 1980 and < 1990 
      WHEN :old.birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1990','DD.MM.YYYY') THEN   
         DELETE DEV3.CUSTOMER_BIRTH_DT_1980
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date   
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
      -- >= 1990 and < 1995 
      WHEN :old.birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1995','DD.MM.YYYY') THEN   
         DELETE DEV3.CUSTOMER_BIRTH_DT_1990
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date   
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
      -- >= 1995 
      WHEN :old.birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY') THEN   
         DELETE DEV3.CUSTOMER_BIRTH_DT_MAXVALUE
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date   
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
   END CASE;
END;

Instead of Trigger – Update

We will create an instead of trigger for the update operation in the same way than for the list partitioning:

You will find below the source code of this trigger:

CREATE OR REPLACE TRIGGER DEV3.TU_CUSTOMER1 
INSTEAD OF UPDATE ON DEV3.CUSTOMER1 
-- =======================================================================
--  Trigger       : TU_CUSTOMER
--  Description   : Update trigger for custom partitioning (range)
--  Author        : Arnaud Fargues
--  Creation      : 20.11.2016
--  Modifications : 02.12.2016 AF Update
-- =======================================================================
DECLARE
   FUNCTION get_partition(v_birth_date DATE)
   RETURN NUMBER 
   IS
      v_return NUMBER := 0;
   BEGIN
      CASE
          WHEN v_birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') 
             THEN v_return := 1;
          WHEN v_birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND v_birth_date < TO_DATE('01.01.1990','DD.MM.YYYY')             
             THEN v_return := 2;
          WHEN v_birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND v_birth_date < TO_DATE('01.01.1995','DD.MM.YYYY')              
             THEN v_return := 3;
          WHEN v_birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY')              
             THEN v_return := 4;
          ELSE v_return := -1;
      END CASE;
      RETURN v_return;
   END;
BEGIN  
   -- Same paritition
   IF get_partition(:old.birth_date) = get_partition(:new.birth_date)
   THEN
      CASE 
         -- < 1980
         WHEN :old.birth_date < TO_DATE('01.01.1980','DD.MM.YYYY') THEN   
            UPDATE DEV3.CUSTOMER_BIRTH_DT_MINVALUE
            SET cust_id = :new.cust_id,   
                first_name = :new.first_name,   
                last_name= :new.last_name,   
                birth_date= :new.birth_date,   
                creation_date= :new.creation_date,        
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id  
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date   
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
         -- >= 1980 and < 1990 
         WHEN :old.birth_date >= TO_DATE('01.01.1980','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1990','DD.MM.YYYY') THEN   
            UPDATE DEV3.CUSTOMER_BIRTH_DT_1980
            SET cust_id = :new.cust_id,   
                first_name = :new.first_name,   
                last_name= :new.last_name,   
                birth_date= :new.birth_date,   
                creation_date= :new.creation_date,        
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id  
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date   
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date));
         -- >= 1990 and < 1995
         WHEN :old.birth_date >= TO_DATE('01.01.1990','DD.MM.YYYY') AND :old.birth_date < TO_DATE('01.01.1995','DD.MM.YYYY') THEN   
            UPDATE DEV3.CUSTOMER_BIRTH_DT_1990
            SET cust_id = :new.cust_id,   
                first_name = :new.first_name,   
                last_name= :new.last_name,   
                birth_date= :new.birth_date,   
                creation_date= :new.creation_date,        
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id  
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date   
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
         -- >= 1995
         WHEN :old.birth_date >= TO_DATE('01.01.1995','DD.MM.YYYY') THEN   
            UPDATE DEV3.CUSTOMER_BIRTH_DT_MAXVALUE
            SET cust_id = :new.cust_id,   
                first_name = :new.first_name,   
                last_name= :new.last_name,   
                birth_date= :new.birth_date,   
                creation_date= :new.creation_date,        
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id  
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date   
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  

      END CASE;
   ELSE
      
      -- Delete --
      DELETE DEV3.CUSTOMER1
      WHERE cust_id = :old.cust_id  
        AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
        AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
        AND birth_date = :old.birth_date   
        AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
        AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
 
      -- Insert --
      INSERT INTO DEV3.CUSTOMER1
        (
         cust_id,    
         first_name,  
         last_name, 
         birth_date,
         creation_date,      
         modification_date
        )
      VALUES
        (
         :new.cust_id,
         :new.first_name,
         :new.last_name,
         :new.birth_date,
         :new.creation_date,
         :new.modification_date
        );
        
   END IF;
  
END;

Basically, we must know if the updated row remains in the same partition or not. We have created the subprogram function get_partition in order to have a “partition unique id”.

Partitioning, PL/SQL 11gR2, 12cR1, COUNT, Custom partitioning, DELETE, INSERT, Instead of trigger, Partitioned table, Partitions, Range partitioning, SUBPROGRAM, SUM, TO_DATE, UPDATE

Custom Partitioning – Part II – List partitioning – Instead Of Trigger – DELETE – UPDATE

Leave a comment Posted by mourmansk on November 1, 2016

Instead of Trigger – Delete

The delete operation is similar to the insert operation e.g. we must find first in which partition the row is located. Nevertheless it is not as simple as it seems at first glance.
We must delete the rows based on all the “:old” column values (an instead of DML trigger is a row-level trigger) because currently we do not have any unique key on the partitioned table even if the cust_id column is filled with a sequence value. The partitioned index topic will be discussed in a later post.

Let’s try several approaches for the delete instead of trigger:

First approach – Standard

...
      -- A,E --
      WHEN v_old_partition_list IN ('A','E') THEN   
         DELETE DEV3.CUSTOMER_AE
         WHERE cust_id = :old.cust_id    
           AND first_name = :old.first_name   
           AND last_name = :old.last_name   
           AND birth_date = :old.birth_date   
           AND creation_date = :old.creation_date        
           AND modification_date = :old.modification_date;  
...

The issue with this approach is that the NULL values are not handled e.g. if the value :old.modification_date is NULL and a row has the column modification_date equal to NULL the condition modification_date= :old.modification_date will not be equal to TRUE.

Second approach – NVL

...
      -- A,E --
      WHEN v_old_partition_list IN ('A','E') THEN   
         DELETE DEV3.CUSTOMER_AE
         WHERE cust_id = :old.cust_id    
           AND NVL(first_name,'Unknown') = NVL(:old.first_name,'Unknown')  
           AND NVL(last_name,'Unknown') = NVL(:old.last_name,'Unknown')   
           AND birth_date = :old.birth_date   
           AND NVL(creation_date,TO_DATE('10000101','YYYYMMDD')) = NVL(:old.creation_date,TO_DATE('10000101','YYYYMMDD'))  
           AND NVL(modification_date,TO_DATE('10000101','YYYYMMDD')) = NVL(:old.modification_date,TO_DATE('10000101','YYYYMMDD'));  
...

We have added the NVL function to compare nullable columns with a default value. The issue with this approach is that the default value that we use, can be an existing value in the column e.g. we could have a row whose first_name value is set to ‘Unknown’.

Third approach – DECODE

...
      -- A,E --
      WHEN v_old_partition_list IN ('A','E') THEN   
         DELETE DEV3.CUSTOMER_AE
         WHERE cust_id = :old.cust_id    
           AND DECODE(first_name,:old.first_name,1,0) = 1
           AND DECODE(last_name,:old.last_name,1,0) = 1 
           AND birth_date = :old.birth_date   
           AND DECODE(creation_date,:old.creation_date,1,0) = 1 
           AND DECODE(modification_date,:old.modification_date,1,0) = 1;  
...

We have added the DECODE function to compare nullable columns. DECODE is one of the few functions that can compare NULL values. A drawback is that the decode function works with numeric and character types. It can not handle directly clob/lob datatypes.

Fourth approach – IS NULL – IS NOT NULL

...
      -- A,E --
      WHEN v_old_partition_list IN ('A','E') THEN   
         DELETE DEV3.CUSTOMER_AE
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date   
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
...

For each nullable column we have made a condition for NULL values and a condition for not NULL values. This approach can not also handle directly clob/lob datatypes, nevertheless we will choose this one to implement the delete operation.

You will find below the source code of instead the trigger for the delete operation:

CREATE OR REPLACE TRIGGER DEV3.TD_CUSTOMER 
INSTEAD OF DELETE ON DEV3.CUSTOMER 
-- =======================================================================
--  Trigger       : TD_CUSTOMER
--  Description   : Delete trigger for custom partitioning (list)
--  Author        : Arnaud Fargues
--  Creation      : 01.10.2016
--  Modifications : 
-- =======================================================================
DECLARE
   v_old_partition_list VARCHAR2(5);
BEGIN
   v_old_partition_list := SUBSTR(UPPER(:old.last_name),1,1);
  
   CASE 
      -- A,E --
      WHEN v_old_partition_list IN ('A','E') THEN   
         DELETE DEV3.CUSTOMER_AE
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date   
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
      -- B --      
      WHEN v_old_partition_list IN ('B') THEN    
         DELETE DEV3.CUSTOMER_B
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date   
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
      -- Default --      
      ELSE
         DELETE DEV3.CUSTOMER_DEFAULT
         WHERE cust_id = :old.cust_id  
           AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
           AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
           AND birth_date = :old.birth_date   
           AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
           AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
  END CASE;
END;

Instead of Trigger – Update

The update operation is more complicated than the other ones, because we must first know if we are going to move the row from one partition to another partition.

You will find below the source code of the instead of trigger for the update operation:

CREATE OR REPLACE TRIGGER DEV3.TU_CUSTOMER 
INSTEAD OF UPDATE ON DEV3.CUSTOMER 
-- =======================================================================
--  Trigger       : TU_CUSTOMER
--  Description   : Update trigger for custom partitioning (list)
--  Author        : Arnaud Fargues
--  Creation      : 01.10.2016
--  Modifications : 
-- =======================================================================
DECLARE
   v_old_partition_list VARCHAR2(5);
   v_new_partition_list VARCHAR2(5);
BEGIN
   v_old_partition_list := SUBSTR(UPPER(:old.last_name),1,1);
   v_new_partition_list := SUBSTR(UPPER(:new.last_name),1,1);
  
   -- Same partition
   IF (    (v_old_partition_list IS NULL AND v_new_partition_list IS NULL) 
        OR ((v_old_partition_list IS NOT NULL AND v_new_partition_list IS NOT NULL) AND v_old_partition_list = v_new_partition_list))
   THEN
      CASE 
         -- A,E --
         WHEN v_old_partition_list IN ('A','E') THEN   
            UPDATE DEV3.CUSTOMER_AE
            SET cust_id = :new.cust_id,   
                first_name = :new.first_name,   
                last_name= :new.last_name,   
                birth_date= :new.birth_date,   
                creation_date= :new.creation_date,        
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id  
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date   
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
         -- B --      
         WHEN v_old_partition_list IN ('B') THEN    
            UPDATE DEV3.CUSTOMER_B
            SET cust_id = :new.cust_id,   
                first_name = :new.first_name,   
                last_name= :new.last_name,   
                birth_date= :new.birth_date,   
                creation_date= :new.creation_date,        
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id  
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date   
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  

         -- Default --      
         ELSE
            UPDATE DEV3.CUSTOMER_DEFAULT
            SET cust_id = :new.cust_id,   
                first_name = :new.first_name,   
                last_name= :new.last_name,   
                birth_date= :new.birth_date,   
                creation_date= :new.creation_date,        
                modification_date= :new.modification_date
            WHERE cust_id = :old.cust_id  
              AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
              AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
              AND birth_date = :old.birth_date   
              AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
              AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  

      END CASE;
   ELSE
      
      -- Delete --
      DELETE DEV3.CUSTOMER
      WHERE cust_id = :old.cust_id  
        AND ((first_name IS NULL AND :old.first_name IS NULL) OR ((first_name IS NOT NULL AND :old.first_name IS NOT NULL) AND first_name = :old.first_name))
        AND ((last_name IS NULL AND :old.last_name IS NULL) OR ((last_name IS NOT NULL AND :old.last_name IS NOT NULL) AND last_name = :old.last_name))
        AND birth_date = :old.birth_date   
        AND ((creation_date IS NULL AND :old.creation_date IS NULL) OR ((creation_date IS NOT NULL AND :old.creation_date IS NOT NULL) AND creation_date = :old.creation_date))
        AND ((modification_date IS NULL AND :old.modification_date IS NULL) OR ((modification_date IS NOT NULL AND :old.modification_date IS NOT NULL) AND modification_date = :old.modification_date));  
 
      -- Insert --
      INSERT INTO DEV3.CUSTOMER
        (
         cust_id,    
         first_name,  
         last_name, 
         birth_date,
         creation_date,      
         modification_date
        )
      VALUES
        (
         :new.cust_id,
         :new.first_name,
         :new.last_name,
         :new.birth_date,
         :new.creation_date,
         :new.modification_date
        );
        
   END IF;
  
END;

Basically, if the updated row remains in the same partition, we do an update, meanwhile if the updated row moves to another partition we must first delete the row from the “old” partition and insert it into the “new” partition. Like the delete operation we must update the rows based on all the “:old” column values.

Remark:

Currently you can not issue a merge statement on the CUSTOMER view.

Warning : The custom list partitioning has only be tested on development databases!

Partitioning, PL/SQL 11gR2, 12cR1, Custom partitioning, DECODE, DELETE, Instead of trigger, List partitioning, NULL, NVL, Partitioned table, Partitions, SUBSTR, UPDATE, UPPER

Custom Partitioning – Part I – List partitioning – Instead Of Trigger

Leave a comment Posted by mourmansk on September 21, 2016

Scope

In this series of post, we will focus on partitioning tables without having the “Partitioning” option which is an option of Oracle Database Enterprise Edition.
We will start in a first step to partition tables.

Partitioning concepts

Basically a partitioned table is a set of several sub-tables called partitions. The partitioning is based on one or more fields (called partitioning key) and on a distribution method:

The main distribution methods are:

List partitioning,
Range partitioning,
Hash partitioning.

You can have a single level partitioning e.g. only one distribution method on a table or a composite partitioning where we apply a first distribution method on a table, and a second distribution method on the partitions (which give us sub-partitions).

CUSTOMER table example

In the rest of this post we will use a 12cR1 PDB database.

Let’s create a CUSTOMER_MODEL table in the DEV3 schema as follow:

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_MODEL
  2  (  cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4     last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

and a sequence:

DEV3@pdb1> CREATE SEQUENCE  DEV3.S_CUSTOMER INCREMENT BY 1 START WITH 1 CACHE 20 NOCYCLE;

Sequence created.

Let’s now fill this table:

DEV3@pdb1> INSERT INTO DEV3.CUSTOMER_MODEL
  2    (
  3     cust_id,
  4     first_name,
  5     last_name,
  6     birth_date,
  7     creation_date,
  8     modification_date
  9    )
 10  SELECT DEV3.S_CUSTOMER.NEXTVAL,
 11         DBMS_RANDOM.STRING('a',TRUNC(DBMS_RANDOM.VALUE(8,20))),
 12         DBMS_RANDOM.STRING('u',TRUNC(DBMS_RANDOM.VALUE(8,20))),
 13         SYSDATE - (366*18) - TRUNC(DBMS_RANDOM.VALUE(1,10000)),
 14         SYSDATE,
 15         NULL
 16  FROM DUAL
 17  CONNECT BY LEVEL <= 10000;

10000 rows created.

DEV3@pdb1> COMMIT;

Commit complete.

At this point we would like to partition this table based on the first character of the last_name column.
In this case the partitioning key is similar to a virtual column because this expression is not a base column of the table, and the distribution method is similar to a List partitioning method.

Remark: In a List partitioning method there is a default partition that we will use.

Custom partitioning principles

We will set up 3 partitions:

One for a last_name column begining with an A or an E,
One for a last_name column begining with an B,
One for the other cases.

Let’s create the partitions:

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_AE
  2  (  cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4     last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_B
  2  (  cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4     last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

DEV3@pdb1> CREATE TABLE DEV3.CUSTOMER_DEFAULT
  2  (  cust_id           NUMBER         NOT NULL,
  3    first_name        VARCHAR2(100),
  4     last_name         VARCHAR2(100),
  5    birth_date        DATE           NOT NULL,
  6    creation_date     DATE,
  7    modification_date DATE);

Table created.

Now we will create a view that is the UNION of all the previous tables (partitions).

DEV3@pdb1> CREATE OR REPLACE VIEW DEV3.CUSTOMER
  2  AS
  3   SELECT * FROM DEV3.CUSTOMER_AE
  4  UNION ALL
  5   SELECT * FROM DEV3.CUSTOMER_B
  6  UNION ALL
  7   SELECT * FROM DEV3.CUSTOMER_DEFAULT;

View created.

At this point, we can only select rows of the partitioned table (CUSTOMER) but we can not insert, update and delete rows directly through this view.
Nevertheless, we could manually copy the rows of the table CUSTOMER_MODEL whose first character of the last_name is ‘B’ in the following way:

INSERT INTO DEV3.CUSTOMER_B
  (
   cust_id,    
   first_name,  
   last_name, 
   birth_date,
   creation_date,      
   modification_date
  )       
SELECT *
FROM DEV3.CUSTOMER_MODEL
WHERE SUBSTR(UPPER(last_name),1,1) = 'B';

and we could manually also delete and update a row by finding first in which partition it is.

We could have a more transparent process using the Instead of trigger.

Instead of Trigger – Insert

This kind of trigger is used to update for example a non updatable view which is our case.

We will start with the insert operation:

CREATE OR REPLACE TRIGGER DEV3.TI_CUSTOMER 
INSTEAD OF INSERT ON DEV3.CUSTOMER
-- =======================================================================
--  Trigger       : TI_CUSTOMER
--  Description   : Insert trigger for custom partitioning (list)
--  Author        : Arnaud Fargues
--  Creation      : 20.09.2016
--  Modifications : 
-- =======================================================================
DECLARE
   v_partition_list VARCHAR2(5);
BEGIN
   v_partition_list := SUBSTR(UPPER(:new.last_name),1,1);
  
   CASE 
      -- A,E --
      WHEN v_partition_list IN ('A','E') THEN   
         INSERT INTO DEV3.CUSTOMER_AE
           (
            cust_id,    
            first_name,  
            last_name, 
            birth_date,
            creation_date,      
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
      -- B --      
      WHEN v_partition_list IN ('B') THEN    
         INSERT INTO DEV3.CUSTOMER_B
           (
            cust_id,    
            first_name,  
            last_name,
            birth_date,
            creation_date,      
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
      -- Default --      
      ELSE
         INSERT INTO DEV3.CUSTOMER_DEFAULT
           (
            cust_id,    
            first_name,  
            last_name,
            birth_date,
            creation_date,      
            modification_date
           )
         VALUES
           (
            :new.cust_id,
            :new.first_name,
            :new.last_name,
            :new.birth_date,
            :new.creation_date,
            :new.modification_date
           );
   END CASE;
END;

Let’s execute the following query on the source table:

DEV3@pdb1> SELECT SUBSTR(UPPER(last_name),1,1) fl,
  2         COUNT(*)
  3  FROM DEV3.CUSTOMER_MODEL
  4  GROUP BY CUBE(SUBSTR(UPPER(last_name),1,1))
  5  ORDER BY 1;

FL     COUNT(*)
---- ----------
A           374
B           386
C           419
D           413
E           403
F           360
G           425
H           363
I           415
J           359
K           399
L           359
M           394
N           373
O           362
P           359
Q           353
R           390
S           438
T           360
U           373
V           416
W           375
X           369
Y           363
Z           400
          10000

27 rows selected.

We have:

777 rows whose last_name is begining with A or E,
386 rows whose last_name is begining with B.

Now we will partition the source table as follow:

DEV3@pdb1> INSERT INTO DEV3.CUSTOMER
  2    (
  3     cust_id,
  4     first_name,
  5     last_name,
  6     birth_date,
  7     creation_date,
  8     modification_date
  9    )
 10  SELECT *
 11  FROM DEV3.CUSTOMER_MODEL;

10000 rows created.

DEV3@pdb1> COMMIT;

Commit complete.

Let’s execute the following check query:

DEV3@pdb1>  SELECT 'CUSTOMER_AE' part, COUNT(*)
  2   FROM DEV3.CUSTOMER_AE
  3  UNION ALL
  4   SELECT 'CUSTOMER_B' part, COUNT(*)
  5   FROM DEV3.CUSTOMER_B
  6  UNION ALL
  7   SELECT 'CUSTOMER_DEFAULT' part, COUNT(*)
  8   FROM DEV3.CUSTOMER_DEFAULT;

PART               COUNT(*)
---------------- ----------
CUSTOMER_AE             777
CUSTOMER_B              386
CUSTOMER_DEFAULT       8837

3 rows selected.

Remarks:

If you have already a trigger on the table to be partitioned, you might integrate this behaviour in the Instead of trigger,
We could have create a virtual column on the source table equal to “SUBSTR(UPPER(last_name),1,1)” and use it in the Instead of trigger.

Shortly:

We will see how to delete and update rows in the partitioned table.

Partitioning, PL/SQL 11gR2, 12cR1, CONNECT BY LEVEL, Custom partitioning, DBMS_RANDOM, Distribution method, Hash Partioning, Instead of trigger, List partitioning, Partitioned table, Partitioning key, Partitions, Range partitioning, SUBSTR, TRUNC, UPPER, Virtual column

Average Active Sessions – Portable Light Sampling Sessions – Part II

Leave a comment Posted by mourmansk on August 1, 2016

Scope

In the previous post, we have sampled active sessions in the global temporary table PLAN_TABLE. If we want to display the average active sessions like in the part XXI, we will have first to process the data. Nevertheless, in this case we could not use directly the view v$aas_light_sampling_sessions. Instead, we will use a SELECT query based on this view and stored in the PLAN_TABLE table.

aas_light_sampling_sessions

The underlying SELECT of the view v$aas_light_sampling_sessions will be stored in the table PLAN_TABLE. Basically the table lss$_sampling_session will be replaced by the following sub-query:

SELECT lss."SESSION_STATE"                           session_state, 
       lss."WAIT_CLASS#"                             wait_class#,
       TO_DATE(lss."SAMPLE_TIME",'YYYYMMDDHH24MISS') sample_time
FROM plan_table,
     XMLTABLE('/ROW'
     PASSING xmltype(plan_table.other_xml)
     COLUMNS 
         "SESSION_STATE" CHAR(1)      PATH 'SESSION_STATE',
         "WAIT_CLASS#"   NUMBER       PATH 'WAIT_CLASSH',
         "SAMPLE_TIME"   VARCHAR2(16) PATH 'SAMPLE_TIME') 
     lss
WHERE id > 0
  AND id IS NOT NULL;

The XML data field values are available as column values with the XMLTABLE function. You will find below a SQL script to store the SELECT statement linked to aas_light_sampling_sessions:

-- =======================================================================
--  File Name     : aas_light_sampling_sessions.sql
--  Area          :
--  Description   : Portable Light Sampling Sessions
--  Author        : Arnaud Fargues
--  Tested On     : 12.1, 11.2
--  Plateform     : 
--  Requires      : 
--  Creation Date : 14.05.2016
--  Modified      : 31.07.2016  
-- =======================================================================

INSERT INTO plan_table(object_name,other_xml)
VALUES ('aas_light_sampling_sessions',TO_CLOB('
SELECT sample_yyyymmddhhmi,
SUBSTR(sample_yyyymmddhhmi,9,2) || '':'' || SUBSTR(sample_yyyymmddhhmi,11,2) BEGIN_TIME,
aas1 C_CPU,
aas1+aas2 C_OTHER,
aas1+aas2+aas3 C_APPLICATION,
aas1+aas2+aas3+aas4 C_CONFIGURATION,
aas1+aas2+aas3+aas4+aas5 C_ADMINISTRATIVE,
aas1+aas2+aas3+aas4+aas5+aas6 C_CONCURRENCY,
aas1+aas2+aas3+aas4+aas5+aas6+aas7 C_COMMIT,
aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8 C_NETWORK,
aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9 C_USER_IO,
aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10 C_SYSTEM_IO,
aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11 C_SCHEDULER,
aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12 C_CLUSTER,
aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12+aas13 C_QUEUEING,
MAX(CEIL(aas1+aas2+aas3+aas4+aas5+aas6+aas7+aas8+aas9+aas10+aas11+aas12+aas13)) OVER() MAX_AAS
FROM (SELECT sample_yyyymmddhhmi,
TRUNC((ccpu/(nb_samples)),3) aas1,
TRUNC((cother/(nb_samples)),3) aas2,
TRUNC((capplication/(nb_samples)),3) aas3,
TRUNC((cconfiguration/(nb_samples)),3) aas4,
TRUNC((cadministrative/(nb_samples)),3) aas5,
TRUNC((cconcurrency/(nb_samples)),3) aas6,
TRUNC((ccommit/(nb_samples)),3) aas7,
TRUNC((cnetwork/(nb_samples)),3) aas8,
TRUNC((cuser_io/(nb_samples)),3) aas9,
TRUNC((csystem_io/(nb_samples)),3) aas10, 
TRUNC((cscheduler/(nb_samples)),3) aas11, 
TRUNC((ccluster/(nb_samples)),3) aas12, 
TRUNC((cqueueing/(nb_samples)),3)aas13
FROM (SELECT NVL(co_base_qry.sample_yyyymmddhhmi,time_window.start_time) sample_yyyymmddhhmi,
NVL(co_base_qry.nb_samples,1) nb_samples,
NVL(co_base_qry.ccpu,0) ccpu,
NVL(co_base_qry.cother,0) cother,
NVL(co_base_qry.capplication,0) capplication,
NVL(co_base_qry.cconfiguration,0) cconfiguration,
NVL(co_base_qry.cadministrative,0) cadministrative,
NVL(co_base_qry.cconcurrency,0) cconcurrency,
NVL(co_base_qry.ccommit,0) ccommit,
NVL(co_base_qry.cnetwork,0) cnetwork,
NVL(co_base_qry.cuser_io,0) cuser_io,
NVL(co_base_qry.csystem_io,0) csystem_io,
NVL(co_base_qry.cscheduler,0) cscheduler,
NVL(co_base_qry.ccluster,0) ccluster,
NVL(co_base_qry.cqueueing,0)cqueueing
FROM (SELECT TO_CHAR(SYSDATE - (LEVEL/24/60),''YYYYMMDDHH24MI'') start_time
FROM DUAL 
CONNECT BY LEVEL <= 61
) time_window
LEFT OUTER JOIN 
(SELECT TO_CHAR(sample_time,''YYYYMMDDHH24MI'') sample_yyyymmddhhmi,
30 nb_samples,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),-1,1)) ccpu,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),0,1)) cother,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),1,1)) capplication,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),2,1)) cconfiguration,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),3,1)) cadministrative,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),4,1)) cconcurrency,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),5,1)) ccommit,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),7,1)) cnetwork,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),8,1)) cuser_io,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),9,1)) csystem_io,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),10,1)) cscheduler,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),11,1)) ccluster,
COUNT(DECODE(DECODE(session_state,''C'',-1,wait_class#),12,1)) cqueueing
FROM (
SELECT lss."SESSION_STATE" session_state, 
lss."WAIT_CLASS#" wait_class#,
TO_DATE(lss."SAMPLE_TIME",''YYYYMMDDHH24MISS'') sample_time
FROM plan_table,
XMLTABLE(''/ROW''
PASSING xmltype(plan_table.other_xml)
COLUMNS 
"SESSION_STATE" CHAR(1) PATH ''SESSION_STATE'',
"WAIT_CLASS#" NUMBER PATH ''WAIT_CLASSH'',
"SAMPLE_TIME" VARCHAR2(16) PATH ''SAMPLE_TIME'') 
lss
WHERE id > 0
AND id IS NOT NULL)
GROUP BY TO_CHAR(sample_time,''YYYYMMDDHH24MI'')
) co_base_qry
ON time_window.start_time=co_base_qry.sample_yyyymmddhhmi
)
) ORDER BY sample_yyyymmddhhmi'));

COMMIT;

As you can see, the query has less than 4000 characters. It is due to the fact that we are using the function DBMS_XMLGEN.GETXMLTYPE in a way that can handle only queries with less than 4000 characters.

Modified SVG source query

We will use the query defined in the part XXI as a starting point. We will modify the source query as follow:

SELECT XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE(other_xml)
    ...
FROM plan_table
WHERE object_name='aas_light_sampling_sessions';

XSLT variables

The variable “graph_name” is set to ‘PLSS Active Sessions – Waiting + Working’,
The variable “cpu_count” is set to ‘4’.

Modified query

SET LINESIZE      2000
SET LONGCHUNKSIZE 30000
SET LONG          30000

SET FEEDBACK OFF
SET VERIFY   OFF
SET PAGESIZE 0

SET HEADING OFF

SPOOL generate_svg_plss.svg

SELECT XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE(other_xml)
,      XMLTYPE.CREATEXML
   (TO_CLOB(
    '<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
     <xsl:variable name="margin_top">20</xsl:variable>
     <xsl:variable name="margin_bottom">30</xsl:variable>
     <xsl:variable name="margin_left">40</xsl:variable>
     <xsl:variable name="margin_right">100</xsl:variable>
     <xsl:variable name="bar_width">10</xsl:variable>
     <xsl:variable name="graph_width"><xsl:value-of select="600+$margin_left+$margin_right"/></xsl:variable>
     <xsl:variable name="graph_height"><xsl:value-of select="100+$margin_top+$margin_bottom"/></xsl:variable>
     <xsl:variable name="graph_name">PLSS Active Sessions - Waiting + Working</xsl:variable>
     <xsl:variable name="graph_unit">Session Count</xsl:variable>
     <xsl:variable name="yval_max"><xsl:value-of select="/descendant::MAX_AAS[position()=1]"/></xsl:variable>
     <xsl:variable name="yval_min">0</xsl:variable>
     <xsl:variable name="cpu_count"><xsl:value-of select="4"/></xsl:variable>
       <xsl:template match="/">'
           )
    || 
    TO_CLOB(
         '<svg xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns="http://www.w3.org/2000/svg" width="{$graph_width}" height="{$graph_height}">
           <text x="{$margin_left+1}" y="{($margin_top)-5}" style="fill: #000000; stroke: none;font-size:10px;text-anchor=start"><xsl:value-of select="$graph_name"/></text>
           <text x="{($margin_bottom)-($graph_height)}" y="10" transform="rotate(-90)" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="$graph_unit"/></text>
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-0}"   x2="{($graph_width)-($margin_right)+5}" y2="{($graph_height)-($margin_bottom)-0}"  style="stroke:lightblue;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-25}"  x2="{($graph_width)-($margin_right)+5}" y2="{($graph_height)-($margin_bottom)-25}" style="stroke:lightblue;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-50}"  x2="{($graph_width)-($margin_right)+5}" y2="{($graph_height)-($margin_bottom)-50}" style="stroke:lightblue;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-75}"  x2="{($graph_width)-($margin_right)+5}" y2="{($graph_height)-($margin_bottom)-75}" style="stroke:lightblue;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-100}" x2="{($graph_width)-($margin_right)+5}" y2="{($graph_height)-($margin_bottom)-100}" style="stroke:lightblue;stroke-width:1" />
           <text x="{($margin_left)-24}" y="{($graph_height)-($margin_bottom)-2}"   style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="format-number(($yval_min),''00.00'')"/></text>
           <text x="{($margin_left)-24}" y="{($graph_height)-($margin_bottom)-25}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="format-number((($yval_min)+(1*(($yval_max)-($yval_min)) div 4)),''00.00'')"/></text>
           <text x="{($margin_left)-24}" y="{($graph_height)-($margin_bottom)-50}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="format-number((($yval_min)+((($yval_max)-($yval_min)) div 2)),''00.00'')"/></text>
           <text x="{($margin_left)-24}" y="{($graph_height)-($margin_bottom)-75}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="format-number((($yval_min)+(3*(($yval_max)-($yval_min)) div 4)),''00.00'')"/></text>
           <text x="{($margin_left)-24}" y="{($graph_height)-($margin_bottom)-100}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="format-number(($yval_max),''00.00'')"/></text>
           <line x1="{$margin_left}" y1="{($graph_height)-($margin_bottom)}" x2="{$margin_left}" y2="{($graph_height)-($margin_bottom)-100}" style="stroke:lightblue;stroke-width:1" />'
           )
    || 
    TO_CLOB(
          '<xsl:for-each select="ROWSET/ROW/BEGIN_TIME">
             <xsl:choose>
               <xsl:when test="(position()-1) mod 5=0">
                 <text x="{($margin_left)-9+($bar_width*(position()-1))}" y="{($graph_height)-($margin_bottom)+12}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="self::node()"/></text>
                 <line x1="{($margin_left)+($bar_width*(position()-1))}" y1="{($graph_height)-($margin_bottom)+4}" x2="{($margin_left)+($bar_width*(position()-1))}" y2="{($graph_height)-($margin_bottom)-100}" style="stroke:lightblue;stroke-width:1" /> 
               </xsl:when>
               <xsl:otherwise>
                 <line x1="{($margin_left)+($bar_width*(position()-1))}" y1="{($graph_height)-($margin_bottom)+3}" x2="{($margin_left)+($bar_width*(position()-1))}" y2="{($graph_height)-($margin_bottom)+0}" style="stroke:lightblue;stroke-width:1" /> 
               </xsl:otherwise>
             </xsl:choose>    
           </xsl:for-each>
           <xsl:variable name="v_path0">
             <xsl:for-each select="ROWSET/ROW/C_CPU">
               <xsl:variable name="x_val0">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val0">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val0,'','',$y_val0,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path0} {$x_val0},{($graph_height)-($margin_bottom)} {$margin_left},{($graph_height)-($margin_bottom)}" style="fill:limegreen;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path1">
             <xsl:for-each select="ROWSET/ROW/C_OTHER">
               <xsl:sort select="position()" order="descending" data-type="number"/>
               <xsl:variable name="x_val1">
                 <xsl:value-of select="$margin_left+$bar_width*(last()-position())"/>
               </xsl:variable>
               <xsl:variable name="y_val1">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val1,'','',$y_val1,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path0}{$v_path1}" style="fill:hotpink;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path2">
             <xsl:for-each select="ROWSET/ROW/C_APPLICATION">
               <xsl:variable name="x_val2">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val2">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val2,'','',$y_val2,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path1}{$v_path2}" style="fill:indianred;stroke:none;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:variable name="v_path3">
             <xsl:for-each select="ROWSET/ROW/C_CONFIGURATION">
               <xsl:sort select="position()" order="descending" data-type="number"/>
               <xsl:variable name="x_val3">
                 <xsl:value-of select="$margin_left+$bar_width*(last()-position())"/>
               </xsl:variable>
               <xsl:variable name="y_val3">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val3,'','',$y_val3,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path2}{$v_path3}" style="fill:olive;stroke:none;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:variable name="v_path4">
             <xsl:for-each select="ROWSET/ROW/C_ADMINISTRATIVE">
               <xsl:variable name="x_val4">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val4">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val4,'','',$y_val4,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path3}{$v_path4}" style="fill:gray;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path5">
             <xsl:for-each select="ROWSET/ROW/C_CONCURRENCY">
               <xsl:sort select="position()" order="descending" data-type="number"/>
               <xsl:variable name="x_val5">
                 <xsl:value-of select="$margin_left+$bar_width*(last()-position())"/>
               </xsl:variable>
               <xsl:variable name="y_val5">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val5,'','',$y_val5,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path4}{$v_path5}" style="fill:sienna;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path6">
             <xsl:for-each select="ROWSET/ROW/C_COMMIT">
               <xsl:variable name="x_val6">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val6">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val6,'','',$y_val6,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path5}{$v_path6}" style="fill:orange;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path7">
             <xsl:for-each select="ROWSET/ROW/C_NETWORK">
               <xsl:sort select="position()" order="descending" data-type="number"/>
               <xsl:variable name="x_val7">
                 <xsl:value-of select="$margin_left+$bar_width*(last()-position())"/>
               </xsl:variable>
               <xsl:variable name="y_val7">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val7,'','',$y_val7,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path6}{$v_path7}" style="fill:tan;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path8">
             <xsl:for-each select="ROWSET/ROW/C_USER_IO">
               <xsl:variable name="x_val8">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val8">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val8,'','',$y_val8,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path7}{$v_path8}" style="fill:royalblue;stroke:none;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:variable name="v_path9">
             <xsl:for-each select="ROWSET/ROW/C_SYSTEM_IO">
               <xsl:sort select="position()" order="descending" data-type="number"/>
               <xsl:variable name="x_val9">
                 <xsl:value-of select="$margin_left+$bar_width*(last()-position())"/>
               </xsl:variable>
               <xsl:variable name="y_val9">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val9,'','',$y_val9,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path8}{$v_path9}" style="fill:skyblue;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path10">
             <xsl:for-each select="ROWSET/ROW/C_SCHEDULER">
               <xsl:variable name="x_val10">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val10">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val10,'','',$y_val10,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path9}{$v_path10}" style="fill:lightcyan;stroke:none;stroke-width:1" />'
           )
   ||
   TO_CLOB(
          '<xsl:variable name="v_path11">
             <xsl:for-each select="ROWSET/ROW/C_CLUSTER">
               <xsl:sort select="position()" order="descending" data-type="number"/>
               <xsl:variable name="x_val11">
                 <xsl:value-of select="$margin_left+$bar_width*(last()-position())"/>
               </xsl:variable>
               <xsl:variable name="y_val11">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val11,'','',$y_val11,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path10}{$v_path11}" style="fill:lightgray;stroke:none;stroke-width:1" />
           <xsl:variable name="v_path12">
             <xsl:for-each select="ROWSET/ROW/C_QUEUEING">
               <xsl:variable name="x_val12">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val12">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val12,'','',$y_val12,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polygon points="{$v_path11}{$v_path12}" style="fill:bisque;stroke:none;stroke-width:1" />
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+5}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">CPU</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+0}" width="{10}" height="{6}" fill="limegreen" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+13}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Other</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+8}" width="{10}" height="{6}" fill="hotpink" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+21}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Application</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+16}" width="{10}" height="{6}" fill="indianred" stroke="black"/>   
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+29}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Configuration</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+24}" width="{10}" height="{6}" fill="olive" stroke="black"/>   
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+37}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Administrative</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+32}" width="{10}" height="{6}" fill="gray" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+45}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Concurrency</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+40}" width="{10}" height="{6}" fill="sienna" stroke="black"/>'
           ) 
    || 
    TO_CLOB(
          '<text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+53}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Commit</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+48}" width="{10}" height="{6}" fill="orange" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+61}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Network</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+56}" width="{10}" height="{6}" fill="tan" stroke="black"/>  
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+69}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">User IO</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+64}" width="{10}" height="{6}" fill="royalblue" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+77}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">System IO</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+72}" width="{10}" height="{6}" fill="skyblue" stroke="black"/>
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+85}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Scheduler</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+80}" width="{10}" height="{6}" fill="lightcyan" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+93}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Cluster</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+88}" width="{10}" height="{6}" fill="lightgray" stroke="black"/> 
           <text x="{($graph_width)-($margin_right)+33}" y="{($margin_top)+101}" style="fill:#000000; stroke: none;font-size:8px;text-anchor=start">Queueing</text>
           <rect x="{($graph_width)-($margin_right)+20}" y="{($margin_top)+96}" width="{10}" height="{6}" fill="bisque" stroke="black"/> 
           <xsl:choose>
             <xsl:when test="$yval_max&gt;$cpu_count">
               <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-((($yval_min)-($cpu_count))*(100 div (($yval_min)-($yval_max))))}" x2="{($graph_width)-($margin_right)+5}" y2="{($graph_height)-($margin_bottom)-((($yval_min)-($cpu_count))*(100 div (($yval_min)-($yval_max))))}" style="stroke-dasharray: 9, 5;stroke:red;stroke-width:1" />
               <text x="{($margin_left)+2}" y="{($graph_height)-($margin_bottom)-((($yval_min)-($cpu_count))*(100 div (($yval_min)-($yval_max))))-2}" style="fill:red; stroke: none;font-size:8px;text-anchor=start">CPU cores</text>
             </xsl:when>
             <xsl:otherwise>
               <text x="{($margin_left)-38}" y="{($graph_height)-($margin_bottom)-93}" style="fill:red; stroke: none;font-size:6px;text-anchor=start"><xsl:value-of select="format-number((($yval_max) div ($cpu_count))*100,''00'')"/>% cpu cores</text>
             </xsl:otherwise>
           </xsl:choose>   
         </svg>
       </xsl:template>
     </xsl:stylesheet>'
           )
   )
   ) 
FROM plan_table
WHERE object_name='aas_light_sampling_sessions';

SPOOL OFF
SET HEADING ON

SET VERIFY   ON
SET FEEDBACK ON

This query will be saved in the file generate_svg_plss.sql.

Sampling + SVG generation

In order to display the average active sessions of the PDB1 container database we will:

Connect to the PDB1 container database as the PLSS user,
Execute the previous script aas_light_sampling_sessions.sql in order to store the aas_light_sampling_sessions query,
Execute the script plss_sampling.sql in order to sample the active sessions every 2 seconds on a one minute period,
Execute the script generate_svg_plss.sql in order to generate a SVG graph,
Repeat the steps 3 and 4 as many times as you want.

You will find below an example of such a script:

-- =======================================================================
--  File Name     : plss_master.sql
--  Area          :
--  Description   : Portable Light Sampling Sessions
--  Author        : Arnaud Fargues
--  Tested On     : 12.1, 11.2
--  Plateform     : 
--  Requires      : 
--  Creation Date : 31.07.2016
--  Modified      :   
-- =======================================================================

SET DEFINE OFF

--
--Load aas_light_sampling_sessions query into PLAN_TABLE
--
@aas_light_sampling_sessions.sql

--
--1
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--2
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--3
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--4
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--5
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--6
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--7
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--8
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--9
--
@plss_sampling.sql

@generate_svg_plss.sql

--
--10
--
@plss_sampling.sql

@generate_svg_plss.sql

Result:

Remark:

You could repeat the steps @plss_sampling.sql and @generate_svg_plss.sql a high number of times and if you want to display the average active sessions every 5 minutes for example, you could copy the file generate_svg_plss.svg every 5 minutes to check this activity.

Database Monitoring, Performance Tuning, PL/SQL 11gR2, 12cR1, Average active sessions, CDB database, CDB$ROOT, CEIL, CLOB, CONNECT BY, Container, DECODE, Global Tempory Table, MAX, NVL, PDB, Performance monitoring, PLAN_TABLE, PLSS, Pluggable Database, Portable Light sampling sessions, SUBSTR, TO_CHAR, TO_DATE, TRUNC, XML, XMLTABLE, XMLType

Average Active Sessions – Portable Light Sampling Sessions – Part I

Leave a comment Posted by mourmansk on May 22, 2016

Scope

In some situations, it might happened that you have access to an Oracle database but you can not set up a Light Sampling Sessions installation because you have limited privileges e.g. you don’t have the CREATE TABLE system privilege.
However with some limited privileges you could display a graph on the Average Active Sessions.
In the rest of this post we will make the assumption that an Oracle user has been created in a 12.1 PDB database.

User creation

Let’s create a user PLSS in the PDB1 container:

@> connect / as sysdba
Connected.
SYS@orcl> ALTER SESSION SET CONTAINER=PDB1;

Session altered.

SYS@orcl> show con_name;

CON_NAME
------------------------------
PDB1
SYS@orcl> CREATE USER PLSS IDENTIFIED BY plss
  2  CONTAINER=CURRENT;

User created.

This user will have only the CREATE SESSION privilege and will have access to the V$SESSION view:

User privileges

SYS@orcl> GRANT CREATE SESSION TO PLSS;

Grant succeeded.

SYS@orcl> GRANT SELECT ON SYS.V_$SESSION TO PLSS;

Grant succeeded.

Plss sampling

As you can see the Oracle user PLSS has limited privileges. How can he store data in the database?
We could use the global temporary table PLAN_TABLE. The data could be stored in this table during the session lifetime.

Basically, we will use an anonymous block similar to the procedure DBMS_SAMPLING_SESSION.start_sampling to record active sessions as following:

-- =======================================================================
--  File Name     : plss_sampling.sql
--  Area          :
--  Description   : Portable Light Sampling Sessions
--  Author        : Arnaud Fargues
--  Tested On     : 12.1, 11.2
--  Plateform     : 
--  Requires      : 
--  Creation Date : 12.05.2016
--  Modified      :   
-- =======================================================================

DECLARE
   v_nb_sample      INTEGER;
   v_index          INTEGER;
   v_cur_date       DATE;
   v_sample_id      NUMBER;
   v_sample_per_min NUMBER;
   v_session_sid    NUMBER;
   v_start_date     DATE;

   v_sampling_interval_sec NUMBER := 2;
   v_sampling_window_min   NUMBER := 1;

BEGIN

   v_sample_per_min := 60 / v_sampling_interval_sec;
   v_nb_sample      := v_sampling_window_min * v_sample_per_min;

   SELECT NVL(MAX(id),0)
   INTO v_sample_id
   FROM plan_table;

   SELECT SYS_CONTEXT ('USERENV', 'SID')
   INTO v_session_sid
   FROM DUAL;

   FOR v_index IN 1..v_nb_sample
   LOOP
      v_sample_id := v_sample_id + 1;

      v_cur_date := SYSDATE;

      INSERT INTO plan_table (id,other_xml)
      SELECT v_sample_id,
             XMLSERIALIZE(CONTENT
         (XMLELEMENT("ROW",
             XMLELEMENT("SAMPLE_ID",v_sample_id ),
             XMLELEMENT("SAMPLE_TIME",TO_CHAR(v_cur_date,'YYYYMMDDHH24MISS')),
             XMLELEMENT("SID",SID),
             XMLELEMENT("SERIALH",SERIAL#),
             XMLELEMENT("SESSION_STATE",decode(state,'WAITING','W','C')),
             XMLELEMENT("USERH",user#),
             XMLELEMENT("TYPE",type),
             XMLELEMENT("COMMAND",command),
             XMLELEMENT("PROGRAM",program),
             XMLELEMENT("MODULE",module),
             XMLELEMENT("ACTION",action),
             XMLELEMENT("SERVICE_NAME",service_name),
             XMLELEMENT("CLIENT_IDENTIFIER",client_identifier),
             XMLELEMENT("EVENTH",event#),
             XMLELEMENT("EVENT",event),
             XMLELEMENT("SEQH",seq#),
             XMLELEMENT("P1",p1),
             XMLELEMENT("P2",p2),
             XMLELEMENT("P3",p3),
             XMLELEMENT("WAIT_CLASSH",wait_class#),
             XMLELEMENT("WAIT_TIME_MICRO",wait_time_micro),
             XMLELEMENT("SQL_ID",sql_id),
             XMLELEMENT("SQL_HASH_VALUE",sql_hash_value),
             XMLELEMENT("SQL_CHILD_NUMBER",sql_child_number) )))
      FROM v$session
      WHERE status='ACTIVE'
        AND ((state = 'WAITING' AND wait_class# <> 6)
              OR (state <> 'WAITING' AND sid <> v_session_sid));

      v_start_date := SYSDATE;
      
      WHILE SYSDATE <= v_start_date + (v_sampling_interval_sec/86400) 
      LOOP
         NULL;
      END LOOP;

   END LOOP;
END ;
/

Remarks:

Every sample will be stored in the column other_xml of PLAN_TABLE as XML data,
The sample_id values will be also stored in the column id of PLAN_TABLE,
The function XMLSERIALIZE is used to convert XML datatype into CLOB datatype,
We have used the function XMLELEMENT instead of XMLFOREST in order to store explicitly NULL values,
The symbol # contained in some column names has been replaced by H because such a symbol is invalid in a XML tag name,
We have replaced the DBMS_LOCK.SLEEP procedure with a WHILE LOOP,
We will record the active sessions every 2 seconds during 1 minute.

Shortly

We will see how to generate a SVG graph with those “temporary data”.

Database Monitoring, Performance Tuning, PL/SQL 11gR2, 12cR1, Average active sessions, CDB database, CDB$ROOT, CLOB, Container, Global Tempory Table, MAX, NVL, PDB, Performance monitoring, PLAN_TABLE, PLSS, Pluggable Database, Portable Light sampling sessions, SYS_CONTEXT, V$SESSION, XML, XMLELEMENT, XMLFOREST, XMLSERIALIZE

Average Active Sessions – Light Sampling Sessions – Database Group – RAC Update

Leave a comment Posted by mourmansk on January 26, 2016

Scope

Back to part XXII, it could be interesting to have a consolidated activity view of non-CDB databases or of PDB pluggable databases belonging to different CDBs.
We will define the concept of “database group”, in which one database will be the primary database (like the CDB$ROOT container database) and the other databases will be defined as secondary databases.

We will take two databases as an example. In our case we will use a CDB database where the CDB$ROOT container will be the primary database and the PDB1 container will be the secondary database. We will also modify the structure of the table lss$_sampling_session in order to handle RAC instances.

Let’s begin with the PDB1 container.

PDB1 – Tablespace modification

We will use the ‘LSS_REPOSITORY’ tablespace that was already created in the post “Average Active Sessions and CDB – Light Sampling Sessions”, but we will increase its size to 150 Mo:

@> connect / as sysdba
Connected.
SYS@orcl> ALTER SESSION SET CONTAINER=PDB1;

Session altered.

SYS@orcl> show con_name;

CON_NAME
------------------------------
PDB1
SYS@orcl> ALTER DATABASE
  2  DATAFILE 'C:\APP\ORACLE\ORADATA\ORCL\PDB1\LSS_REPOSITORY01.DBF' RESIZE 150 M;

Database altered.

PDB1 – Local user creation

Connected to the PDB1 container, we will create the LSS local user:

SYS@orcl> CREATE USER LSS IDENTIFIED BY lss
  2  CONTAINER=CURRENT
  3  DEFAULT TABLESPACE LSS_REPOSITORY
  4  QUOTA UNLIMITED ON LSS_REPOSITORY;

User created.

PDB1 – Base privileges

We will grant the following privileges to the LSS user:

SYS@orcl> GRANT CREATE SESSION      TO LSS;

Grant succeeded.

SYS@orcl> GRANT SELECT_CATALOG_ROLE TO LSS;

Grant succeeded.

SYS@orcl> GRANT CREATE PROCEDURE    TO LSS;

Grant succeeded.

SYS@orcl> GRANT CREATE VIEW         TO LSS;

Grant succeeded.

SYS@orcl> GRANT CREATE TABLE        TO LSS;

Grant succeeded.

PDB1 – Table creation

Let’s now create the table lss$_sampling_session in the LSS schema:

@> connect lss@pdb1
Enter password:
Connected.
LSS@pdb1> show con_name;

CON_NAME
------------------------------
PDB1
LSS@pdb1> CREATE TABLE lss$_sampling_session
  2  AS
  3  SELECT CAST (1 AS NUMBER) sample_id,
  4         CAST (NULL AS DATE) sample_time,
  5         sid,
  6         serial#,
  7         user#,
  8         type,
  9         command,
 10         program,
 11         module,
 12         action,
 13         service_name,
 14         client_identifier,
 15         CAST ('1' AS CHAR(1)) session_state,
 16         state,
 17         event#,
 18         event,
 19         seq#,
 20         p1,
 21         p2,
 22         p3,
 23         wait_class#,
 24         wait_time_micro,
 25         sql_id,
 26         con_id,
 27         inst_id,
 28         sql_hash_value,
 29         sql_child_number,
 30         CAST (1 AS NUMBER) element_id
 31  FROM gv$session
 32  WHERE 1=2;

Table created.

We have added four new columns:

inst_id: to handle RAC instances,
sql_hash_value and sql_child_number: to handle more precisely sql statements,
element_id: to handle the database group. This number must be unique in all the databases that belong to a database group.

PDB1 – Other privileges

Let’s grant the SELECT privilege on the underlying object of gv$session in order to use it in a procedure:

@> connect / as sysdba
Connected.
SYS@orcl> ALTER SESSION SET CONTAINER=PDB1;

Session altered.

SYS@orcl> show con_name;

CON_NAME
------------------------------
PDB1
SYS@orcl> GRANT SELECT ON SYS.GV_$SESSION TO LSS;

Grant succeeded.

Let’s grant the EXECUTE privilege on the DBMS_LOCK package:

SYS@orcl> GRANT EXECUTE ON DBMS_LOCK TO LSS;

Grant succeeded.

PDB1 – Package specifications

Let’s create the package specifications as following:

CREATE OR REPLACE PACKAGE LSS.DBMS_SAMPLING_SESSION AS 

-- =======================================================================
--  Package       : DBMS_SAMPLING_SESSION
--  Description   : Sample active sessions
--  Author        : Arnaud Fargues
--  Creation      : 14.06.2015
--  Modifications : 
-- =======================================================================

   PROCEDURE start_sampling (
      p_sampling_window_min   IN NUMBER DEFAULT 60,
      p_sampling_interval_sec IN NUMBER DEFAULT 5);

END DBMS_SAMPLING_SESSION;
/

PDB1 – Package body

You will find below the package body:

CREATE OR REPLACE PACKAGE BODY LSS.DBMS_SAMPLING_SESSION AS

PROCEDURE start_sampling (
   p_sampling_window_min   IN NUMBER DEFAULT 60,
   p_sampling_interval_sec IN NUMBER DEFAULT 5)

-- =======================================================================
--  Procedure     : start_sampling
--  Description   : Starts sampling active sessions
--  Parameters    : p_sampling_window_min   : sampling period time in minutes
--                  p_sampling_interval_sec : sampling period interval in seconds
--  Author        : Arnaud Fargues
--  Creation      : 14.06.2015
--  Modifications : 10.12.2015 Handle RAC instances
--                  12.01.2016 Add columns sql_hash_value and sql_child_number
--                  22.01.2016 Add column element_id
-- =======================================================================

AS
   v_nb_sample      INTEGER;
   v_index          INTEGER;
   v_cur_date       DATE;
   v_sample_id      NUMBER;
   v_sample_per_min NUMBER;
   v_session_sid    NUMBER;
BEGIN

   v_sample_per_min := 60 / p_sampling_interval_sec;
   v_nb_sample      := p_sampling_window_min * v_sample_per_min;
   
   SELECT NVL(MAX(sample_id),0)
   INTO v_sample_id
   FROM lss$_sampling_session;

   SELECT SYS_CONTEXT ('USERENV', 'SID')
   INTO v_session_sid
   FROM DUAL;

   FOR v_index IN 1..v_nb_sample
   LOOP
      v_sample_id := v_sample_id + 1;

      DBMS_APPLICATION_INFO.SET_MODULE(
         module_name => 'DBMS_SAMPLING_SESSION',
         action_name => 'Sample:'|| TO_CHAR(v_sample_id));

      v_cur_date := SYSDATE;

      INSERT INTO lss$_sampling_session(
         sample_id,
         sample_time,
         inst_id,
         sid,
         serial#,
         session_state,
         state,
         user#,
         type,
         command,
         program,
         module,
         action,
         service_name,
         client_identifier,
         event#,
         event,
         seq#,
         p1,
         p2,
         p3,
         wait_class#,
         wait_time_micro,
         sql_id,
         sql_hash_value,
         sql_child_number,
         con_id,
         element_id)
      SELECT
         v_sample_id,
         v_cur_date,
         inst_id,
         sid,
         serial#,
         decode(state,'WAITING','W','C'),
         state,
         user#,
         type,
         command,
         program,
         module,
         action,
         service_name,
         client_identifier,
         event#,
         event,
         seq#,
         p1,
         p2,
         p3,
         wait_class#,
         wait_time_micro,
         sql_id,
         sql_hash_value,
         sql_child_number,
         con_id,
         2
      FROM gv$session
      WHERE status='ACTIVE'
        AND ((state = 'WAITING' AND wait_class# <> 6)
              OR (state <> 'WAITING' AND ((SID = v_session_sid AND event# <> 389) OR sid <> v_session_sid)));

      IF v_index MOD v_sample_per_min = 0 THEN
         COMMIT;
      END IF;

      DBMS_LOCK.SLEEP(p_sampling_interval_sec);

   END LOOP;
END start_sampling;

END DBMS_SAMPLING_SESSION;
/

Remarks:

For the PDB1 container which is a secondary database the element_id value is 2,
For non-CDB databases, you can set the con_id column value to 0.

Let’s continue with the CDB$ROOT container.

CDB$ROOT – Table update

We will add the four new columns to the table lss$_sampling_session in the C##LSS schema:

@> connect c##lss@orcl
Enter password:
Connected.
C##LSS@orcl> show con_name;

CON_NAME
------------------------------
CDB$ROOT
C##LSS@orcl> ALTER TABLE lss$_sampling_session
  2  ADD (inst_id           NUMBER,
  3       sql_hash_value    NUMBER,
  4       sql_child_number  NUMBER,
  5       element_id        NUMBER);

Table altered.

CDB$ROOT – Other privileges

Let’s grant the SELECT privilege on the underlying object of gv$session in order to use it in a procedure instead of v$session:

@> connect / as sysdba
Connected.
SYS@orcl> show con_name

CON_NAME
------------------------------
CDB$ROOT
SYS@orcl> GRANT SELECT ON SYS.GV_$SESSION TO C##LSS;

Grant succeeded.

Gv$session is a view that contains data linked to several containers (CDB$ROOT, PDB’s). Because the common user C##LSS should see data of all those containers we will set the CONTAINER_DATA attribute as follow:

SYS@orcl> ALTER USER C##LSS
  2  SET CONTAINER_DATA = (CDB$ROOT,PDB1)
  3  FOR gv_$session CONTAINER=CURRENT;

User altered.

CDB$ROOT – Package body

The package body is the same than for the PDB1 container except that the value of element_id is 1 for CDB$ROOT:

You must replace this code fragment

...         con_id,
         2
      FROM gv$session
      WHERE status='ACTIVE'...

...         con_id,
         1
      FROM gv$session
      WHERE status='ACTIVE'...

Shortly

We will consolidated the two database activities in a stacked area graph and compare it to the graph described in the part XXII.

Database Monitoring, Performance Tuning, PL/SQL, RAC 11gR2, 12cR1, Average active sessions, CDB database, CDB$ROOT, Container, CONTAINER_DATA, Database group, DBMS_APPLICATION_INFO, DBMS_LOCK, GV$SESSION, Light sampling sessions, LSS, Non-CDB database, PDB, Performance monitoring, Pluggable Database, Primary database, RAC, Secondary database

Average Active Sessions and CDB – Light Sampling Sessions

Leave a comment Posted by mourmansk on July 13, 2015

Scope

Suppose that you have a 12c CDB database. An extra column “con_id” has been added to the view v$waitclassmetric, which is linked to the container identifier. Unfortunately connected as a common user to the container “CDB$ROOT” when you query this view the column “con_id” has always the value of 0 which means CDB wide.

If you want to know the details of the average active sessions by PDB, you could use the view v$active_session_history which belongs to ASH (Active Session History) but for the following reasons this view is not always usable or available:

You have an Oracle Database 9i release,
You have an Oracle Database Standard Edition,
You have an Oracle Database Enterprise Edition without the “Diagnostics Pack” licensed,
Etc.

Some open projects “simulate” ASH, we can quote L-ASH and S-ASH for example which can be found at the following address http://datavirtualizer.com/ash-masters/33-2/ . We will develop a “light” ASH like feature that will sample
the active sessions for CDB and non-CDB databases. We will first focus on CDB databases.

Light Sampling Sessions (LSS)

Every 5 seconds by default, we will save the active sessions of the v$session view from the CDB$ROOT container, into a specific table lss$_sampling_session. An active session is:

A session whose status is ACTIVE and,
(a session whose state is WAITING and the wait class is not IDLE) or (a session whose state is not WAITING).

In the rest of this post, we will make the assumption that we are using an Oracle Database 12cR1 Standard Edition One with only one PDB (single-tenant environment).

Remark:

LSS will not replace the ASH features. Instead it will give information about sampled active sessions.

CDB$ROOT tablespace creation

We will create a specific tablespace ‘LSS_REPOSITORY’ to host LSS. Let’s start with the CDB$ROOT container:

@> connect / as sysdba
Connected.
SYS@orcl> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SYS@orcl> CREATE TABLESPACE "LSS_REPOSITORY" DATAFILE
  2  'C:\APP\ORACLE\ORADATA\ORCL\LSS_REPOSITORY01.DBF' SIZE 150 M
  3  AUTOEXTEND ON MAXSIZE 2 G
  4  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  5  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

PDB tablespace creation

We will create the same tablespace in all the PDB (PDB1 in our case):

SYS@orcl> ALTER SESSION SET CONTAINER=PDB1;

Session altered.

SYS@orcl> show con_name;

CON_NAME
------------------------------
PDB1
SYS@orcl> CREATE TABLESPACE "LSS_REPOSITORY" DATAFILE
  2  'C:\APP\ORACLE\ORADATA\ORCL\PDB1\LSS_REPOSITORY01.DBF' SIZE 5 M
  3  AUTOEXTEND ON MAXSIZE 2 G
  4  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  5  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

You could create this tablespace in all the PDB with a smaller size what we have done.

Common user creation

Connected to the CDB$ROOT container, we will create the C##LSS common user:

SYS@orcl> CREATE USER C##LSS IDENTIFIED BY lss
  2  CONTAINER=ALL
  3  DEFAULT TABLESPACE LSS_REPOSITORY
  4  QUOTA UNLIMITED ON LSS_REPOSITORY;

User created.

Base privileges

We will grant the following privileges to the C##LSS user:

SYS@orcl> GRANT CREATE SESSION      TO C##LSS;

Grant succeeded.

SYS@orcl> GRANT SELECT_CATALOG_ROLE TO C##LSS;

Grant succeeded.

SYS@orcl> GRANT CREATE PROCEDURE    TO C##LSS;

Grant succeeded.

SYS@orcl> GRANT CREATE VIEW         TO C##LSS;

Grant succeeded.

SYS@orcl> GRANT CREATE TABLE        TO C##LSS;

Grant succeeded.

Table creation

Let’s now create the table lss$_sampling_session in the C##LSS schema:

@> connect c##lss@orcl
Enter password:
Connected.
C##LSS@orcl> show con_name;

CON_NAME
------------------------------
CDB$ROOT
C##LSS@orcl> CREATE TABLE lss$_sampling_session
  2  AS
  3  SELECT CAST (1 AS NUMBER) sample_id,
  4         CAST (NULL AS DATE) sample_time,
  5         sid,
  6         serial#,
  7         user#,
  8         type,
  9         command,
 10         program,
 11         module,
 12         action,
 13         service_name,
 14         client_identifier,
 15         CAST ('1' AS CHAR(1)) session_state,
 16         state,
 17         event#,
 18         event,
 19         seq#,
 20         p1,
 21         p2,
 22         p3,
 23         wait_class#,
 24         wait_time_micro,
 25         sql_id,
 26         con_id
 27  FROM v$session
 28  WHERE 1=2;

Table created.

Other privileges

Let’s grant the SELECT privilege on the underlying object of the v$session view in order to use it in a procedure:

@> connect / as sysdba
Connected.
SYS@orcl> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SYS@orcl> GRANT SELECT ON SYS.V_$SESSION TO C##LSS;

Grant succeeded.

Let’s grant the EXECUTE privilege on the DBMS_LOCK package:

SYS@orcl> GRANT EXECUTE ON DBMS_LOCK TO C##LSS;

Grant succeeded.

V$session is a view that contains data linked to several containers (CDB$ROOT, PDBs). Because the common user C##LSS should see data of all those containers we will set the CONTAINER_DATA attribute as follow:

SYS@orcl> ALTER USER C##LSS
  2  SET CONTAINER_DATA = (CDB$ROOT,PDB1)
  3  FOR v_$session CONTAINER=CURRENT;

User altered.

Like with the SELECT privilege, the target object is the underlying table of the v$session view.

Package specifications

Let’s create the package specifications as following:

CREATE OR REPLACE PACKAGE C##LSS.DBMS_SAMPLING_SESSION AS 

-- =======================================================================
--  Package       : DBMS_SAMPLING_SESSION
--  Description   : Sample active sessions
--  Author        : Arnaud Fargues
--  Creation      : 14.06.2015
--  Modifications : 
-- =======================================================================

   PROCEDURE start_sampling (
      p_sampling_window_min   IN NUMBER DEFAULT 60,
      p_sampling_interval_sec IN NUMBER DEFAULT 5);

END DBMS_SAMPLING_SESSION;
/

The first parameter is the sampling period time in minutes (default 60 minutes),
The second parameter is the sampling period interval in seconds (default 5 seconds).

Package body

You will find below the package body:

CREATE OR REPLACE PACKAGE BODY C##LSS.DBMS_SAMPLING_SESSION AS

PROCEDURE start_sampling (
   p_sampling_window_min   IN NUMBER DEFAULT 60,
   p_sampling_interval_sec IN NUMBER DEFAULT 5) 

-- =======================================================================
--  Procedure     : start_sampling
--  Description   : Starts sampling active sessions
--  Parameters    : p_sampling_window_min   : sampling period time in minutes
--                  p_sampling_interval_sec : sampling period interval in seconds
--  Author        : Arnaud Fargues
--  Creation      : 14.06.2015
--  Modifications : 
-- =======================================================================

AS
   v_nb_sample      INTEGER;
   v_index          INTEGER;
   v_cur_date       DATE;
   v_sample_id      NUMBER;
   v_sample_per_min NUMBER;
   v_session_sid    NUMBER;
BEGIN

   v_sample_per_min := 60 / p_sampling_interval_sec;
   v_nb_sample      := p_sampling_window_min * v_sample_per_min; 
   
   SELECT NVL(MAX(sample_id),0)
   INTO v_sample_id
   FROM lss$_sampling_session;
   
   SELECT SYS_CONTEXT ('USERENV', 'SID') 
   INTO v_session_sid
   FROM DUAL;
   
   FOR v_index IN 1..v_nb_sample
   LOOP
      v_sample_id := v_sample_id + 1;
      
      DBMS_APPLICATION_INFO.SET_MODULE( 
         module_name => 'DBMS_SAMPLING_SESSION', 
         action_name => 'Sample:'|| TO_CHAR(v_sample_id)); 
   
      v_cur_date := SYSDATE;

      INSERT INTO lss$_sampling_session(
         sample_id,    
         sample_time,  
         sid,          
         serial#,      
         session_state,
         state,
         user#,        
         type,         
         command,      
         program,      
         module,       
         action,       
         service_name, 
         client_identifier, 
         event#,       
         event,        
         seq#,         
         p1,           
         p2,           
         p3,           
         wait_class#,  
         wait_time_micro, 
         sql_id,       
         con_id)       
      SELECT 
         v_sample_id,
         v_cur_date,
         sid, 
         serial#,
         decode(state,'WAITING','W','C'),  
         state,
         user#,
         type,
         command,      
         program,
         module,
         action,
         service_name, 
         client_identifier, 
         event#,      
         event,        
         seq#,         
         p1,           
         p2,           
         p3,           
         wait_class#,  
         wait_time_micro, 
         sql_id,
         con_id 
      FROM v$session 
      WHERE status='ACTIVE'
        AND ((state = 'WAITING' AND wait_class# <> 6)
              OR (state <> 'WAITING' AND ((SID = v_session_sid AND event# <> 389) OR sid <> v_session_sid))); 
    
      IF v_index MOD v_sample_per_min = 0 THEN
         COMMIT; 
      END IF;
      
      DBMS_LOCK.SLEEP(p_sampling_interval_sec);
            
   END LOOP;
END start_sampling;

END DBMS_SAMPLING_SESSION;
/

Remarks:

The ‘wait_time’ column is deprecated since release 11gR1 and has been replaced with the columns ‘state’ and ‘wait_time_micro’,
The sampling data is commited every minute,
In an Oracle Database 11g release the con_id column of the select query could be replaced by 0,
I have only excluded the sampling session sid with the event 389 (‘PL/SQL lock timer’ event in 12cR1 database), but we could exclude this sampling session sid,
The package DBMS_APPLICATION_INFO has been used to track the sampling process,
Only a subset of columns of the v$session view has been sampled.

Usage:

We will connect to the C##LSS user in the CDB$ROOT container:

@> connect c##lss@orcl
Enter password:
Connected.
C##LSS@orcl> show con_name;

CON_NAME
------------------------------
CDB$ROOT
C##LSS@orcl> exec DBMS_SAMPLING_SESSION.start_sampling(p_sampling_window_min => 40);

Result:

C##LSS@orcl> SELECT CON_ID,
  2         COUNT(*)
  3  FROM LSS$_SAMPLING_SESSION
  4  GROUP BY CON_ID
  5  ORDER BY CON_ID;

    CON_ID   COUNT(*)
---------- ----------
         0         15
         1          7
         3        167

3 rows selected.

Database Monitoring, Performance Tuning, PL/SQL 11gR2, 12cR1, ASH, Average active sessions, CDB database, CDB$ROOT, Container, CONTAINER_DATA, DBMS_APPLICATION_INFO, DBMS_LOCK, Light sampling sessions, LSS, PDB, Performance monitoring, Pluggable Database, V$ACTIVE_SESSION_HISTORY, V$SESSION, V$WAITCLASSMETRIC

← Older posts

Oracle database experiences

Tag Archives: 11gR2

Custom Partitioning – Part IV – Hash partitioning – Instead Of Trigger – INSERT – DELETE – UPDATE

Custom Partitioning – Part III – Range partitioning – Instead Of Trigger – INSERT – DELETE – UPDATE

Average Active Sessions – Portable Light Sampling Sessions – Part II

Average Active Sessions – Portable Light Sampling Sessions – Part I

Average Active Sessions – Light Sampling Sessions – Database Group – RAC Update

Average Active Sessions and CDB – Light Sampling Sessions

Recent Posts

Archives

Categories

Info