Oracle database experiences

Oracle database blog

Average Active Sessions and CDB – Light Sampling Sessions

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