Appendix AC

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:

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 
Note 

The columns marked with ** are the ones you have control over, and can set.

The meanings are as follows:

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.



Expert One on One Oracle
Full Frontal PR: Getting People Talking about You, Your Business, or Your Product
ISBN: 1590595254
EAN: 2147483647
Year: 2005
Pages: 41
Authors: Richard Laermer, Michael Prichinello
BUY ON AMAZON

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net