Oracle database experiences

Oracle database blog

Average Active Sessions – Portable Light Sampling Sessions – Part I

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

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: