Oracle database experiences

Oracle database blog

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

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'...

by

...         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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: