8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

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.

Related articles.

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 sys.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 sys.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
    sys.DBMS_SESSION.set_context('parameter_ctx', p_name, p_value);
  END set_parameter;

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

  PROCEDURE clear_all_context IS
  BEGIN
    sys.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>

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

SQL>

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

SELECT p1.get_pga_size FROM dual;

GET_PGA_SIZE
------------
    47641136

SQL>

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

SELECT p1.get_pga_size FROM dual;

GET_PGA_SIZE
------------
    47641136

SQL>

-- Free unused memory and retest.
EXEC sys.DBMS_SESSION.free_unused_user_memory;

SELECT p1.get_pga_size FROM dual;

GET_PGA_SIZE
------------
     5632560

SQL>

End-To-End Tracing From Java

From 12c onward setEndToEndMetrics is deprecated in favour of setClientInfo, which backwards compatible with setEndToEndMetrics. Thanks to Stefan Koehler for pointing this out.

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 makes 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";
  // Pre-12c
  //((OracleConnection) conn).setEndToEndMetrics(e2eMetrics, (short) 0);
  // 12c Onward
  ((OracleConnection) conn).setClientInfo(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;
  // Pre-12c
  //((OracleConnection) conn).setEndToEndMetrics(e2eMetrics, Short.MIN_VALUE);
  // 12c Onward
  ((OracleConnection) conn).setClientInfo(e2eMetrics, Short.MIN_VALUE);
} catch (SQLException sqle) {
  // Do something...
}

For more information see:

Hope this helps. Regards Tim...

Back to the Top.