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