Full Frontal PR: Getting People Talking about You, Your Business, or Your Product

Overview

This is one of the most under-utilized features in the set of supplied packages, yet I cannot think of a single application that would not benefit from its use. Have you ever asked yourself:

• I wonder what that session is doing, what form is it running, what code module is executing?

• I wonder how far along that stored procedure is?

• I wonder how far along that batch job is?

• I wonder what bind variable values were being used on that query?

DBMS_APPLICATION_INFO is the package that can be used to answer all of these questions, and more. It allows us to set up to three columns in our row of the V$SESSION table - the CLIENT_INFO, ACTION, and MODULE columns. It provides functions not only to set these values, but also to return them. Further, there is a parameter to the built-in USERENV or SYS_CONTEXT function that will allow us to access the CLIENT_INFO column easily in any query. I can SELECT USERENV('CLIENT_INFO') FROM DUAL for example, or use WHERE SOME_COLUMN = SYS_CONTEXT( 'USERENV','CLIENT_INFO') in my queries. The values we set in the V$ tables are immediately visible. We do not need to commit them to 'see' them, making them very useful for communicating with the 'outside'. Lastly, it allows us to set values in the dynamic performance view V$SESSION_LONGOPS (LONG OPerationS) as well - useful for recording the progress of long running jobs.

Many Oracle tools, such as SQL*PLUS, already make use of this facility. For example, I have a script, SHOWSQL.SQL, which I use to see what SQL people are currently running in the database (this is available on the Apress web site at http://www.apress.com/). Part of this script dumps out the V$SESSION table for all entries where CLIENT_INFO, MODULE, or ACTION is NOT NULL. Whenever I run it, I see, for example:

USERNAME MODULE ACTION CLIENT_INFO -------------------- --------------- --------------- ---------------------- OPS$TKYTE(107,19225) 01@ showsql.sql OPS$TKYTE(22,50901) SQL*Plus

The first line shows my current session running the script SHOWSQL.SQL with a level of 01. This means that this script has not called another script yet. If I were to create a script TEST.SQL with just @SHOWSQL in it, then SQL*PLUS would set 02 in front of SHOWSQL to show that it is nested. The second line shows another SQL*PLUS session. It is not running any scripts right now (it may have been executing a command entered on the command line directly). If you add the appropriate calls to DBMS_APPLICATION_INFO to your application, you can do the same thing, enhancing the abilities of you and your DBA to monitor your application.

The calls to set these values in the V$SESSION table are simply:

• SET_MODULE - This API call allows you to set both the MODULE, and ACTION columns in V$SESSION. The name of the module is limited to 48 bytes and the value of the action is limited to 32 bytes. The name of the module would typically be your application name. The initial action might be something like STARTUP or INITIALIZING to indicate the program is just starting.

• SET_ACTION - This API calls allows you to set the ACTION column in V$SESSION. ACTION should be a descriptive term to let you know where in your program you are. You might set action to be the name of the currently active form in a forms application for example, or the name of a subroutine in a Pro*C or PL/SQL routine.

• SET_CLIENT_INFO - This API call allows you to store up to 64 bytes of any sort of application specification information you might wish to keep. A common use of this is to parameterize views (see below) and queries.

There are corresponding API calls to read this information back out as well. In addition to setting values in the V$SESSION table, this package allows you to set information in the V$SESSION_LONGOPS dynamic performance view. This view allows you to store more than one row of information in various columns. We will take an in depth look at this functionality in a moment.

Using the Client Info

The SET_CLIENT_INFO call gives us the ability to not only set a value in a column of the V$SESSION table, but also gives us access to that variable via the built-in function userenv (Oracle 7.3 and up) or sys_context (preferred function in Oracle 8i and up). For example, with this we can create a parameterized view, a view whose results depend on the value in the CLIENT_INFO field. The following example demonstrates this concept:

scott@TKYTE816> exec dbms_application_info.set_client_info('KING');      PL/SQL procedure successfully completed.      scott@TKYTE816> select userenv('CLIENT_INFO') from dual;      USERENV('CLIENT_INFO') ------------------------------------ KING      scott@TKYTE816> select sys_context('userenv','client_info') from dual;      SYS_CONTEXT('USERENV','CLIENT_INFO') ------------------------------------ KING      scott@TKYTE816> create or replace view   2 emp_view   3 as   4 select ename, empno   5 from emp   6 where ename = sys_context( 'userenv', 'client_info');      View created.      scott@TKYTE816> select * from emp_view;      ENAME EMPNO ---------- ---------- KING 7839      scott@TKYTE816> exec dbms_application_info.set_client_info('BLAKE');      PL/SQL procedure successfully completed.      scott@TKYTE816> select * from emp_view;      ENAME EMPNO ---------- ---------- BLAKE 7698

As you can see, we can set this value and we can also easily use it in queries where we could use a constant. This allows us to create complex views with predicates that get their values at run-time. One of the issues with views can be in the area of predicate merging. If the optimizer were able to 'merge' the predicate into the view definition, it would run really fast. If not, it runs really slow. This feature, using the client info, allows us to 'merge' the predicate ahead of time when the optimizer cannot. The application developer must set the value and just SELECT * from the view. Then, the 'right' data will come out.

Another place where I make use of this functionality is to store the bind variables I am using in my query (and other pieces of information), so I can see what my procedures are doing very quickly. For example, if you have a long running process you might instrument it like this:

tkyte@TKYTE816> declare   2 l_owner varchar2(30) default 'SYS';   3 l_cnt number default 0;   4 begin 5 dbms_application_info.set_client_info( 'owner='||l_owner );   6   7 for x in ( select * from all_objects where owner = l_owner )   8 loop   9 l_cnt := l_cnt+1; 10 dbms_application_info.set_action( 'processing row ' || l_cnt ); 11 end loop; 12 end; 13 /

Now, using that SHOWSQL.SQL script once again, I can see:

tkyte@TKYTE816> @showsql      USERNAME SID SERIAL# PROCESS STATUS ------------------------------ ---------- ---------- --------- ---------- TKYTE 8 206 780:716 ACTIVE TKYTE 11 635 1004:1144 ACTIVE -------------------- TKYTE(11,635) ospid = 1004:1144 program = SQLPLUS.EXE Saturday 15:59 Saturday 16:15 SELECT * FROM ALL_OBJECTS WHERE OWNER = :b1      USERNAME MODULE ACTION CLIENT_INFO --------------- --------------- --------------- --------------------------- TKYTE(8,206) 01@ showsql.sql TKYTE(11,635) SQL*Plus processing row owner=SYS                                 5393

Session (11,635) is running the query SELECT * FROM ALL_OBJECTS WHERE OWNER = :B1. The report also shows me that owner=SYS in this case, and at the point in time we were looking at it, it had already processed 5,393 rows. In the next section, we'll see how using SESSION LONGOPS can take this a step further, if you know how many operations or steps your procedure will be performing.

Using V$SESSION_LONGOPS

Many operations in the database may take a considerable amount of time. Parallel execution, Recovery Manager, large sorts, loads, and so on fall into this category. These long running operations take advantage of their ability to set values in the dynamic performance view, V$SESSION_LONGOPS to let us know how far along in their work they are, and so can your applications. This view displays the status of various database operations that run for longer than six seconds. That is, functions the database performs that the Oracle developers felt would normally take longer than six seconds have been instrumented to populate the V$SESSION_LONGOPS view. This does not mean anything that takes longer than six seconds will automatically appear in this view. These operations currently include many backup and recovery functions, statistics gathering, and query execution. More operations are added for every Oracle release.

Changes made to this view are immediately visible to other sessions, without the need to commit your transaction. For any process that updates this view, you will be able to monitor their progress from another session by querying the V$SESSION_LONGOPS view. You too have the ability to populate rows in this view, typically one row, but you may use others if you like.

The API to set the values in this view is defined as:

PROCEDURE SET_SESSION_LONGOPS Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- RINDEX BINARY_INTEGER IN/OUT SLNO BINARY_INTEGER IN/OUT OP_NAME VARCHAR2 IN DEFAULT TARGET BINARY_INTEGER IN DEFAULT CONTEXT BINARY_INTEGER IN DEFAULT SOFAR NUMBER IN DEFAULT TOTALWORK NUMBER IN DEFAULT TARGET_DESC VARCHAR2 IN DEFAULT UNITS VARCHAR2 IN DEFAULT

with the following meanings:

• RINDEX - Tells the server which row to modify in the V$SESSION_LONGOPS view. If you set this value to DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS_NOHINT, a new row will be allocated in this view for you, and the index of this row will be returned in RINDEX. Subsequent calls to SET_SESSION_LONGOPS with the same value for RINDEX will update this already existing row.

• SLNO - An internal value. You should initially pass a Null number in, and ignore its value otherwise. You should pass the same value in with each call.

• OP_NAME - The name of the long running process. It is limited to 64 bytes in size, and should be set to some string that will be easily identified and provides some meaning to you.

• TARGET - Typically used to hold the object ID that is the target of the long running operation (for example, the object ID of the table being loaded). You may supply any number you wish here, or leave it Null.

• CONTEXT - A user-defined number. This number would have meaning only to you. It is simply any number you wish to store.

• SOFAR - This is defined as any number you want to store, but if you make this number be some percentage or indicator of the amount of work done, the database will attempt to estimate your time to completion for you. For example, if you have 25 things to do, and they all take more or less the same amount of time, you could set SOFAR to the number of things done so far, and then set the next parameter TOTALWORK. The server will figure out how long it took you to get to where you are, and estimate how long it will take you to complete.

• TOTALWORK - This is defined as any number you want to store, but the same caveat for SOFAR applies here. If SOFAR is a percentage of TOTALWORK, representing your progress, the server will compute the time remaining to complete your task.

• TARGET_DESC - This is used to describe the contents of the TARGET input from above. If the TARGET actually contained an object ID, this might contain the object name for that object ID.

• UNITS - A descriptive term that categorizes what SOFAR and TOTALWORK are measured in. Units might be 'files', 'iterations', or 'calls' for example.

These are the values you can set. When you look at the V$SESSION_LONGOPS view, you'll see it has many more columns than these however:

ops$tkyte@ORA8I.WORLD> desc v$session_longops Name Null? Type -------------------------------- -------- ---------------------- SID NUMBER SERIAL# NUMBER OPNAME VARCHAR2(64) ** TARGET VARCHAR2(64) ** TARGET_DESC VARCHAR2(32) ** SOFAR NUMBER ** TOTALWORK NUMBER ** UNITS VARCHAR2(32) ** START_TIME DATE LAST_UPDATE_TIME DATE TIME_REMAINING NUMBER ELAPSED_SECONDS NUMBER CONTEXT NUMBER ** MESSAGE VARCHAR2(512) USERNAME VARCHAR2(30) SQL_ADDRESS RAW(4) SQL_HASH_VALUE NUMBER QCSID NUMBER

The meanings are as follows:

• The SID and SERIAL# columns are used to join back to V$SESSION, to pick up the session information.

• The START_TIME column marks the time this record was created (typically your first call to DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS).

• The LAST_UPDATE_TIME column represents the time of your last call to SET_SESSION_LONGOPS.

• The TIME_REMAINING is an estimate in seconds of the time to completion. It is equal to ROUND(ELAPSED_SECONDS*((TOTALWORK/SOFAR)-1)).

• The ELAPSED_SECONDS column is the time in seconds since the start of the long running operation, and the last update time.

• The MESSAGE column is a derived column. It concatenates together pieces of the OPNAME, TARGET_DESC, TARGET, SOFAR, TOTALWORK, and UNITS column to make a readable description of the work in process.

• The USERNAME is the name of the user this process is executing under.

• The SQL_ADDRESS and SQL_HASH_VALUE may be used to look into V$SQLAREA to see what SQL statement this process was last executing.

• The QCSID is used with parallel query. It would be the session of the parallel coordinator.

So, what can you expect from this particular view? A small example will clearly show what it can provide for you. In one session, if you run a block of code such as:

tkyte@TKYTE816> declare   2 l_nohint number default            dbms_application_info.set_session_longops_nohint;   3 l_rindex number default l_nohint;   4 l_slno number;   5 begin   6 for i in 1 .. 25   7 loop   8 dbms_lock.sleep(2);   9 dbms_application_info.set_session_longops 10 ( rindex => l_rindex, 11 slno => l_slno, 12 op_name => 'my long running operation', 13 target => 1234, 14 target_desc => '1234 is my target', 15 context => 0, 16 sofar => i, 17 totalwork => 25, 18 units => 'loops' 19 ); 20 end loop; 21 end; 22 /

This is a long running operation that will take 50 seconds to complete (the DBMS_LOCK.SLEEP just sleeps for two seconds). In another session, we can monitor this session via the query below (see the Chapter 23 on Invoker and Definer Rights for the definition of the PRINT_TABLE utility used in this code):

tkyte@TKYTE816> begin   2 print_table( 'select b.*   3 from v$session a, v$session_longops b   4 where a.sid = b.sid   5 and a.serial# = b.serial#' );   6 end;   7 / SID : 11 SERIAL# : 635 OPNAME : my long running operation TARGET : 1234 TARGET_DESC : 1234 is my target SOFAR : 2 TOTALWORK : 25 UNITS : loops START_TIME : 28-apr-2001 16:02:46 LAST_UPDATE_TIME : 28-apr-2001 16:02:46 TIME_REMAINING : 0 ELAPSED_SECONDS : 0 CONTEXT : 0 MESSAGE : my long running operation: 1234 is my target                                 1234: 2 out of 25 loops done USERNAME : TKYTE SQL_ADDRESS : 036C3758 SQL_HASH_VALUE : 1723303299 QCSID : 0 -----------------      PL/SQL procedure successfully completed.      ops$tkyte@ORA8I.WORLD> / SID : 11 SERIAL# : 635 OPNAME : my long running operation TARGET : 1234 TARGET_DESC : 1234 is my target SOFAR : 6 TOTALWORK : 25 UNITS : loops START_TIME : 28-apr-2001 16:02:46 LAST_UPDATE_TIME : 28-apr-2001 16:02:55 TIME_REMAINING : 29 ELAPSED_SECONDS : 9 CONTEXT : 0 MESSAGE : my long running operation: 1234 is my target                                 1234: 6 out of 25 loops done USERNAME : TKYTE SQL_ADDRESS : 036C3758 SQL_HASH_VALUE : 1723303299 QCSID : 0 -----------------      PL/SQL procedure successfully completed.      ops$tkyte@ORA8I.WORLD> / SID : 11 SERIAL# : 635 OPNAME : my long running operation TARGET : 1234 TARGET_DESC : 1234 is my target SOFAR : 10 TOTALWORK : 25 UNITS : loops START_TIME : 28-apr-2001 16:02:46 LAST_UPDATE_TIME : 28-apr-2001 16:03:04 TIME_REMAINING : 27 ELAPSED_SECONDS : 18 CONTEXT : 0 MESSAGE : my long running operation: 1234 is my target                                 1234: 10 out of 25 loops done USERNAME : TKYTE SQL_ADDRESS : 036C3758 SQL_HASH_VALUE : 1723303299 QCSID : 0 -----------------      PL/SQL procedure successfully completed.

The first question you might ask is, 'why did I join V$SESSION_LONGOPS to V$SESSION if I did not actually select any information from V$SESSION?' This is because the view V$SESSION_LONGOPS will contain values from rows of current, as well as legacy sessions. This view is not 'emptied out' when you log out. The data you left there remains until some other session comes along, and reuses your slot. Therefore, to see long operations information for current sessions only, you want to join or use a sub-query to get current sessions only.

As you can see from the rather simple example, this information could be quite invaluable to you and your DBA, as far as monitoring long running stored procedures, batch jobs, reports, and so on, goes. A little bit of instrumentation can save a lot of guesswork in production. Rather than trying to 'guess' where a job might be and how long it might take to complete, you can get an accurate view of where it is, and an educated guess as to the length of time it will take to complete.

Summary

Here, we have looked at the DBMS_APPLICATION_INFO package, an often overlooked and under-utilized package. Every application can, and should, make use of this particular package, just to register itself in the database so the DBA, or anyone monitoring the system, can tell what applications are using it. For any process that takes more than a few seconds, the use of V$SESSION_LONGOPS is critical. To show that a process is not 'hanging' but is moving along at a steady pace, this feature is the only way to go. Oracle Enterprise Manager (OEM), and many third party tools, are aware of these views and will automatically integrate your information into their display.