DBMS_SESSION : Managing Sessions From a Connection Pool in Oracle Databases

Amongst other things, the DBMS_SESSION package contains procedures to help manage sessions being used as part of a connection pool. Depending on the software managing the connection pool, some of these issues may already be handled.

Client Identifier (SET_IDENTIFIER and CLEAR_IDENTIFIER)
Contexts (CLEAR_CONTEXT and CLEAR_ALL_CONTEXT)
Packages (RESET_PACKAGE)
Memory (FREE_UNUSED_USER_MEMORY)
End-To-End Tracing From Java

Client Identifier (`SET_IDENTIFIER` and `CLEAR_IDENTIFIER`)

In many client-server applications it was common to allocate a different database user for each real user of the system. This made it simple to identify and audit the users at the database level. If applications used a single database user and managed security internally, this made identifying the real users of the system difficult. The issue was complicated further by multi-tier architectures that used connection pooling.

To counter this, Oracle 9iR1 introduced the SET_IDENTIFIER and CLEAR_IDENTIFIER procedures to allow the real user to be associated with a session, regardless of what database user was being used for the connection. The procedures amend the content of the CLIENT_IDENTIFIER column in the V$SESSION view. When a connection is taken from the connection pool the application should call the SET_IDENTIFIER procedure, passing the real user information as a parameter.

CONN test/test

EXEC DBMS_SESSION.set_identifier('tim_hall');

COLUMN username FORMAT A20
COLUMN client_identifier FORMAT A20

SELECT USER AS username, SYS_CONTEXT('userenv', 'client_identifier') AS client_identifier FROM dual;

USERNAME             CLIENT_IDENTIFIER
-------------------- --------------------
TEST                 tim_hall

SQL>

The information is visible in the V$SESSION view when queried from privileged user in another session.

-- Start a new session, leaving the existing session running.
CONN / AS SYSDBA

COLUMN username FORMAT A20
COLUMN client_identifier FORMAT A20

SELECT username, client_identifier FROM v$session WHERE username = 'TEST';

USERNAME             CLIENT_IDENTIFIER
-------------------- --------------------
TEST                 tim_hall

SQL>

Before the session is released back into the connection pool, the application should call the CLEAR_IDENTIFIER procedure.

-- Back in the original session.
EXEC DBMS_SESSION.clear_identifier;

SELECT USER AS username, SYS_CONTEXT('userenv', 'client_identifier') AS client_identifier FROM dual;

USERNAME             CLIENT_IDENTIFIER
-------------------- --------------------
TEST

SQL>

The CLIENT_IDENTIFIER column of the V$SESSION view is blanked for the session.

-- Back in the privileged session.
SELECT username, client_identifier FROM v$session WHERE username = 'TEST';

USERNAME             CLIENT_IDENTIFIER
-------------------- --------------------
TEST

SQL>

Later releases of Oracle included the CLIENT_IDENTIFIER information in the audit trail, SQL trace files and performance tuning tools, making it even more useful.

Contexts (`CLEAR_CONTEXT` and `CLEAR_ALL_CONTEXT`)

Contexts are namespaces used to store name-value pairs. Although contexts were available in Oracle 8i, the CLEAR_CONTEXT procedure was not introduced until 9iR1, while the CLEAR_ALL_CONTEXT procedure was introduced in 10gR1. If a session is being used as part of a connection pool and the state of its contexts are not reinitialized, this can lead to unexpected behavior.

CONN / AS SYSDBA

GRANT CREATE ANY CONTEXT TO test;

CONN test/test

-- Create the context.
CREATE OR REPLACE CONTEXT parameter_ctx USING context_api;

-- Create the package to manage the context.
CREATE OR REPLACE PACKAGE context_api AS
  PROCEDURE set_parameter(p_name   IN  VARCHAR2,
                          p_value  IN  VARCHAR2);
  PROCEDURE clear_context (p_name  IN  VARCHAR2);
  PROCEDURE clear_all_context;
END context_api;
/

CREATE OR REPLACE PACKAGE BODY context_api IS
  PROCEDURE set_parameter (p_name   IN  VARCHAR2,
                           p_value  IN  VARCHAR2) IS
  BEGIN
    DBMS_SESSION.set_context('parameter_ctx', p_name, p_value);
  END set_parameter;

  PROCEDURE clear_context (p_name  IN  VARCHAR2) IS
  BEGIN
    DBMS_SESSION.clear_context('parameter_ctx', attribute => p_name);
  END clear_context;

  PROCEDURE clear_all_context IS
  BEGIN
    DBMS_SESSION.clear_all_context('parameter_ctx');
  END clear_all_context;
END context_api;
/

-- Set two values in the context and check them.
EXEC context_api.set_parameter('variable1', 'one');
EXEC context_api.set_parameter('variable2', 'two');

COLUMN variable1 FORMAT A20
COLUMN variable2 FORMAT A20

SELECT SYS_CONTEXT('parameter_ctx', 'variable1') AS variable1,
       SYS_CONTEXT('parameter_ctx', 'variable2') AS variable2
FROM   dual;

VARIABLE1            VARIABLE2
-------------------- --------------------
one                  two

SQL>

-- Clear one of the name-value pairs and retest.
EXEC context_api.clear_context('variable1');

SELECT SYS_CONTEXT('parameter_ctx', 'variable1') AS variable1,
       SYS_CONTEXT('parameter_ctx', 'variable2') AS variable2
FROM   dual;

VARIABLE1            VARIABLE2
-------------------- --------------------
                     two

SQL> 

-- Clear all name-value pairs and retest.
EXEC context_api.clear_all_context;

SELECT SYS_CONTEXT('parameter_ctx', 'variable1') AS variable1,
       SYS_CONTEXT('parameter_ctx', 'variable2') AS variable2
FROM   dual;

VARIABLE1            VARIABLE2
-------------------- --------------------


SQL>

Note. Calling CLEAR_CONTEXT with the ATTRIBUTE parameter set to NULL is similar to calling the CLEAR_ALL_CONTEXT procedure.

Packages (`RESET_PACKAGE`)

Sessions have the ability to alter package state by amending the values of package variables. If a session is being used as part of a connection pool and the state of its packages are not reinitialized, this can lead to unexpected behavior. To solve this, the RESET_PACKAGE procedure, available since Oracle 7, can be called to reinitialize all packages.

-- Create a package with a package variable and GET/SET methods.
CREATE OR REPLACE PACKAGE p1 AS
  FUNCTION get_value RETURN NUMBER;
  PROCEDURE set_value (p_value IN NUMBER);
END p1;
/

CREATE OR REPLACE PACKAGE BODY p1 AS
  g_number NUMBER := 1;

  FUNCTION get_value RETURN NUMBER AS
  BEGIN
    RETURN g_number;
  END get_value;

  PROCEDURE set_value (p_value IN NUMBER) AS
  BEGIN
    g_number := p_value;
  END set_value;
END p1;
/

-- Check the current value.
SELECT p1.get_value FROM dual;

 GET_VALUE
----------
         1

SQL>

-- Alter the value and retest.
EXEC p1.set_value(2);

SELECT p1.get_value FROM dual;

 GET_VALUE
----------
         2

SQL>

-- Reset the package state and retest.
EXEC DBMS_SESSION.reset_package;

SELECT p1.get_value FROM dual;

 GET_VALUE
----------
         1

SQL>

Memory (`FREE_UNUSED_USER_MEMORY`)

If a session performs operations that cause it to allocate a large amount of memory in the PGA or UGA, this memory will not be reclaimed until the session disconnects. For sessions in the connection pool this can present a problem unless they are managed properly. As the name suggests, the FREE_UNUSED_USER_MEMORY procedure, available since Oracle 7, frees up unused memory in the session.

CONN / AS SYSDBA

GRANT SELECT ON v_$mystat TO test;
GRANT SELECT ON v_$statname TO test;

CONN test/test

-- Create a package with a collection as a package variable.
CREATE OR REPLACE PACKAGE p1 AS
  FUNCTION get_pga_size RETURN NUMBER;
  PROCEDURE populate_tab;
  PROCEDURE empty_tab;
END p1;
/

CREATE OR REPLACE PACKAGE BODY p1 AS
  TYPE t_tab IS TABLE OF all_objects%ROWTYPE;
  g_tab t_tab;

  FUNCTION get_pga_size RETURN NUMBER AS
    l_number NUMBER;
  BEGIN
    SELECT ms.value
    INTO   l_number
    FROM   v$mystat ms
           JOIN v$statname sn ON sn.statistic# = ms.statistic#
    WHERE  sn.name = 'session pga memory';
    RETURN l_number;
  END get_pga_size;

  PROCEDURE populate_tab AS
  BEGIN
    SELECT *
    BULK COLLECT INTO g_tab
    FROM  all_objects;
  END populate_tab;

  PROCEDURE empty_tab AS
  BEGIN
    g_tab.delete;
  END empty_tab;
END p1;
/

-- Check the current PGA size.
SELECT p1.get_pga_size FROM dual;

GET_PGA_SIZE
------------
     3416168

SQL>

-- Populate the collection and retest.
EXEC p1.populate_tab;

SELECT p1.get_pga_size FROM dual;

GET_PGA_SIZE
------------
    42279016

SQL>

-- Empty the collection and retest.
EXEC p1.empty_tab;

SELECT p1.get_pga_size FROM dual;

GET_PGA_SIZE
------------
    42279016

SQL>

-- Free unused memory and retest.
EXEC DBMS_SESSION.reset_package;

SELECT p1.get_pga_size FROM dual;

GET_PGA_SIZE
------------
     3285096

SQL>

End-To-End Tracing From Java

Java programs connecting to the database using JDBC can set the MODULE, ACTION and CLIENT_IDENTIFIER columns of the V$SESSION view without calls to the DBMS_APPLICATION_INFO or DBMS_SESSION packages. This make it a more scalable solution, since extra calls to the database are not required.

When a new connection (conn) is made, or a connection is pulled from the connection pool, the details are set using the following type of code.

try {
  String e2eMetrics[] = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
  e2eMetrics[OracleConnection.END_TO_END_ACTION_INDEX]   = "Starting";
  e2eMetrics[OracleConnection.END_TO_END_MODULE_INDEX]   = "MyProgram";
  e2eMetrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = "tim_hall";
  ((OracleConnection) conn).setEndToEndMetrics(e2eMetrics, (short) 0);
} catch (SQLException sqle) {
  // Do something...
}

Before releasing a connection back to the connection pool, the details should be blanked.

try {
  String e2eMetrics[] = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
  e2eMetrics[OracleConnection.END_TO_END_ACTION_INDEX]   = null;
  e2eMetrics[OracleConnection.END_TO_END_MODULE_INDEX]   = null;
  e2eMetrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = null;
  ((OracleConnection) conn).setEndToEndMetrics(e2eMetrics, Short.MIN_VALUE);
} catch (SQLException sqle) {
  // Do something...
}

For more information see:

Hope this helps. Regards Tim...

Back to the Top.

DBMS_SESSION : Managing Sessions From a Connection Pool in Oracle Databases

Client Identifier (SET_IDENTIFIER and CLEAR_IDENTIFIER)

Contexts (CLEAR_CONTEXT and CLEAR_ALL_CONTEXT)

Packages (RESET_PACKAGE)

Memory (FREE_UNUSED_USER_MEMORY)

End-To-End Tracing From Java

Client Identifier (`SET_IDENTIFIER` and `CLEAR_IDENTIFIER`)

Contexts (`CLEAR_CONTEXT` and `CLEAR_ALL_CONTEXT`)

Packages (`RESET_PACKAGE`)

Memory (`FREE_UNUSED_USER_MEMORY`)