Tracing Oracle sessions with v$session_longops Oracle Tips by Burleson Consulting

The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

The dbms_application_info package allows programs to add information to the v$session and v$session_longops views to make tracking of session activities simpler and more accurate.

When a program initiates, it should register itself by implementing the set_module procedure to indicate that it is currently using the session.

PROCEDURE set_module (
  module_name  IN  VARCHAR2,
  action_name  IN  VARCHAR2)

The module_name parameter is used to specify the program name, while the action_name parameter is used to indicate current program activity. 

As programs progress, the set_action procedure can be used to alter the value of the action column of the v$session view.

PROCEDURE set_action (
  action_name  IN  VARCHAR2)

Assuming, the add_order procedure, which adds an order into an application schema was being used, the dbms_application_info package would be utilized as follows.

BEGIN
  DBMS_APPLICATION_INFO.set_module(
    module_name => 'add_order',
    action_name => 'insert into orders');
 
  -- Do insert into ORDERS table.

  DBMS_APPLICATION_INFO.set_action(
    action_name => 'insert into order_lines');

  -- Do insert into ORDER_LINESS table.

  DBMS_APPLICATION_INFO.set_action(
    action_name => 'complete');

END;
/

In the above example, the set_module procedure sets the value of the module column in the v$session view to ‘add_order’, while the action column is set to the value ‘insert into orders’.  Notice how the action is amended regularly using the set_action procedure to make sure the action column of the v$session view stays accurate.

The set_client_info procedure allows information to be stored in the client_info column of the v$session view.

PROCEDURE set_client_info (
  client_info  IN  VARCHAR2)

Any additional information relating to the session can be stored in this column, such as the context in which the program has been run.

BEGIN
  DBMS_APPLICATION_INFO.set_client_info(
    client_info => 'Run via job');
END;
/

The following query shows that the values in the v$session view are set correctly.

SQL> SELECT module,
  2         action,
  3         client_info
  4  FROM   v$session
  5  WHERE  username = 'TEST';

MODULE             ACTION           CLIENT_INFO
------------------ ---------------- -------------------------
add_order          complete         Run via job

1 row selected.

SQL>

The set_session_longops procedure can be used to publish information about the progress of long operations by inserting and updating rows in the v$session_longops view.

PROCEDURE set_session_longops (
  rindex       IN OUT  PLS_INTEGER,
  slno         IN OUT  PLS_INTEGER,
  op_name      IN      VARCHAR2    DEFAULT NULL,
  target       IN      PLS_INTEGER DEFAULT 0,
  context      IN      PLS_INTEGER DEFAULT 0,
  sofar        IN      NUMBER      DEFAULT 0,
  totalwork    IN      NUMBER      DEFAULT 0,
  target_desc  IN      VARCHAR2    DEFAULT 'unknown target',
  units        IN      VARCHAR2    DEFAULT NULL)

It is especially useful when operations contain long running loops such as shown in the example below.

DECLARE
  l_rindex     PLS_INTEGER;
  l_slno       PLS_INTEGER;
  l_totalwork  NUMBER;
  l_sofar      NUMBER;
  l_obj        PLS_INTEGER;
BEGIN
  l_rindex    := DBMS_APPLICATION_INFO.set_session_longops_nohint;
  l_sofar     := 0;
  l_totalwork := 10;

  WHILE l_sofar < 10 LOOP
    -- Do some work
    DBMS_LOCK.sleep(5);

    l_sofar := l_sofar + 1;

    DBMS_APPLICATION_INFO.set_session_longops(
      rindex      => l_rindex,
      slno        => l_slno,
      op_name     => 'BATCH_LOAD',
      target      => l_obj,
      context     => 0,
      sofar       => l_sofar,
      totalwork   => l_totalwork,
      target_desc => 'BATCH_LOAD_TABLE',
      units       => 'rows');
  END LOOP;
END;
/

While the above code is running, the contents of the v$session_longops view can be queried as follows.

COLUMN opname FORMAT A20
COLUMN target_desc FORMAT A20
COLUMN units FORMAT A10

SELECT opname,
       target_desc,
       sofar,
       totalwork,
       units
FROM   v$session_longops
WHERE  opname = 'BATCH_LOAD';

The type of output expected from this query is listed below.

OPNAME               TARGET_DESC               SOFAR  TOTALWORK UNITS
-------------------- -------------------- ---------- ---------- ----
BATCH_LOAD           BATCH_LOAD_TABLE              3         10 rows

1 row selected.

In Oracle 10g, the dbms_monitor package can be used to initiate SQL tracing for sessions based on their service, module and action attributes, making use of the dbms_application_info package even more valuable.  The dbms_monitor package will be discussed in more detail later in this chapter.

Using the dbms_session package to identify individual users is the topic discussed in the next section.

dbms_session

Many applications connect to the database as a single Oracle user and control security internally, making identification of individual user sessions difficult.  The dbms_session package contains a procedure called set_identifier, which allows a user-defined identifier, such as a username, to be assigned to a session.  The following example shows how this procedure is used.

SQL> EXEC DBMS_SESSION.set_identifier(client_id => 'tim_hall');

PL/SQL procedure successfully completed.

SQL> SELECT client_identifier
  2  FROM   v$session
  3  WHERE  audsid = SYS_CONTEXT('userenv', 'sessionid');

CLIENT_IDENTIFIER
----------------------------------------------------------------
tim_hall

1 row selected.

In Oracle 10g, the dbms_monitor package can be used to initiate SQL tracing for sessions based on their client_identifier attribute, making the use of the dbms_session package even more valuable.  The dbms_monitor package will be discussed in more detail later in this chapter.

dbms_system

The dbms_system package is an undocumented and unsupported package that contains a number of useful functions and procedures including the ksdwrt procedure. 

PROCEDURE ksdwrt (
  dest  IN  BINARY_INTEGER,
  tst   IN  VARCHAR2)

This procedure allows text to be written directly to the alert log and trace files. The tst parameter specifies the text that should be written, while the dest parameter indicates the destination of the message, which can be one of the following.

• 1 – A trace file.

• 2 – The alert log.

• 3 – Both.

The following example shows how the procedure can be used to write text to the alert log.

SQL> exec dbms_system.ksdwrt(2, ‘*** KSDWRT Test ****’);

Checking the instances alert log will reveal that a message like the following has been appended.

Sat Aug 02 17:14:46 2005

*** KSDWRT Test ****

The procedure name is not very memorable, so it is a good idea to wrap it up in a more obvious procedure.

* write_to_alert_log.sql

-- *****************************************************************
-- Parameters:
--    1) Text to be written to the alert log.
-- *****************************************************************

CREATE OR REPLACE PROCEDURE write_to_alert_log (
  p_text  IN  VARCHAR2) AS
BEGIN
  sys.dbms_system.ksdwrt(2, p_text);
END;
/
SHOW ERRORS

* The alert log is a very important file and filling it with lots of extra text may distract attention from important messages.  In addition, Oracle support may wish to use its contents to diagnose problems so use this functionality with caution.

This concludes the discussion on code instrumentation.  The next section will investigate the identification of PL/SQL performance bottlenecks using the dbms_profiler package.

This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006. You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts: