14.10 How do I…Register applications to simplify performance tracking?

Problem

I know how to use V$SQLAREA to identify poorly performing SQL statements and I know how to create, analyze, and improve optimizer execution paths. My database serves so many users and applications, however, that sometimes I am unable to determine what application issued the problematic SQL statement in the first place. How can I improve my ability to reveal the origin of SQL statements?

Technique

The DBMS_APPLICATION_INFO package, a built-in package that Oracle supplies, exists primarily to allow application developers to add more description to the dynamic performance view V$SQLAREA. Five procedures exist in this package, but the two most useful are SET_MODULE and SET_ACTION. The first updates the MODULE column in the V$SQLAREA view; the second sets the ACTION column in the view. Queries on the V$SQLAREA view can be much more instructive about application activity after DBMS_APPLICATION_INFO has enhanced the contents of V$SQLAREA.

Steps

1. Run SQL*Plus, connect as the WAITE user, and use the START command to load and execute the CHP14_24.SQL script. The script’s contents and output appear in Listing 14.5.

Listing 14.5 Registering an application and generating database activity with the CHP14_24.SQL script in SQL*Plus

2 DECLARE

3 i integer;

4 CURSOR dtc_c1 IS

5 SELECT column_name

6 FROM dba_tab_columns;

7 CURSOR dv IS

8 SELECT column_name

9 FROM dba_ind_columns;

10 BEGIN

11 DBMS_APPLICATION_INFO.SET_MODULE

12 ( module_name => ‘How-To 14-10’,

13 action_name => user | | ‘ reading dba_tab_columns’

14 );

15 for table_rec IN dtc_c1

16 LOOP

17 i := 0;

18 END LOOP;

19 DBMS_APPLICATION_INFO.SET_ACTION

20 ( action_name => user | | ‘ reading dba_ind_columns’);

21 for table_rec IN dv

22 LOOP

23 i := 0;

24 END LOOP;

25 DBMS_APPLICATION_INFO.SET_MODULE

26 ( module_name => null,

27 action_name => null

28 );

29* END

The script sets the module name to How-To 14.10 and then uses the user system variable to set the action name to WAITE reading dba_tab_columns. In lines 15 through 17 the script generates some database activity by selecting all of the rows from the DBA_TAB_COLUMNS view and performing an assignment statement. In lines 19 through 20, DBMS_APPLICATION_INFO resets the action name, again with the user system variable, to WAITE reading dba_ind_ columns. The final loop in lines 21 through 24 selects every row from the DBA_IND_COLUMNS view and performs an assignment statement. The script sets the module and action names back to the null value in lines 25 through 28.

2. Query the V$SQLAREA dynamic performance view by using the SQL*Plus START command to load and execute the CHP14_25.SQL script, as shown in Figure 14.25.

This script is very similar to the CHP14_1.SQL script introduced in How-To 14.1. It prompts the user for a number of physical reads to serve as the cut off criteria for report inclusion. Unlike the CHP14_1.SQL script, however, it returns the ACTION and MODULE columns. The V$SQLAREA rows displayed in Figure 14.25 contain the values for ACTION and MODULE that Step 1 established using the DBMS_APPLICATION_INFO package.

How It Works

Step 1 uses the DBMS_APPLICATION_INFO package to register the script CHP14_24.SQL and proceeds to generate a non-trivial amount of database activity. Step 2 queries the V$SQLAREA dynamic performance view to reveal the most resource intensive SQL statements and the corresponding ACTION and MODULE columns.

Comments

The last call to the SET_MODULE procedure of the DBMS_APPLICATIION_INFO package is important. It sets the module and action names back to null at the culmination of the script. Without this clean-up process, Oracle will incorrectly attribute subsequent SQL statements to the How-To 14.10 module. This oversight would actually make application tracking more difficult! Do not forget to reset the module and action names at the culmination of an application.

If your database experiences heavy application traffic, it may be beneficial to educate developers in application registration so that the origins of problematic SQL statements are more apparent during tuning efforts.