End to End instrumentation is an extremely powerful feature for investigating or monitoring performance across all layers of a modern multi-tiered application.
To facilitate this, the application is expected to tag each database operation with a MODULE and ACTION string. These are free form and can be populated with whatever is most appropriate for the application, however releases up to and including 11.2.0.1 have limited their lengths to – a restrictive – 48 and 32 bytes respectively.
When I was reviewing our recently upgraded 11.2.0.2 environment I was excited to discover:
SQL> SELECT 2 column_name 3 ,data_length 4 FROM dba_tab_columns 5 WHERE table_name = 'V_$SESSION' 6 AND column_name IN ('MODULE','ACTION'); COLUMN_NAME DATA_LENGTH ------------------------------ ----------- MODULE 64 ACTION 64
Confirming this wasn’t simply the view definition, I checked the package specification for DBMS_APPLICATION_INFO:
procedure set_module(module_name varchar2, action_name varchar2); -- Input arguments: -- module_name -- The name of the module that will now be running. The maximum -- length of the module name is 64 bytes. Longer names will be -- truncated. -- action_name -- The name of the action that will now be running. The maximum -- length of the action_name is 64 bytes. Longer names will be -- truncated. If the action name is not being specified, then null -- should be passed for this value.
Excellent! That gives us a bit more to work with! Let’s try it…
SQL> SQL> select lengthb('Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do') AS module_length_bytes 2 ,lengthb('Ut enim ad minim veniam, quis nostrud exercitation ullamco labor') AS action_length_bytes 3 from dual; MODULE_LENGTH_BYTES ACTION_LENGTH_BYTES ------------------- ------------------- 64 64 SQL> begin 2 dbms_application_info.set_module('Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do' 3 ,'Ut enim ad minim veniam, quis nostrud exercitation ullamco labor'); 4 end; 5 / PL/SQL procedure successfully completed. SQL> SELECT module 2 ,length(module) 3 ,action 4 ,length(action) 5 FROM v$session 6 WHERE sid = (SELECT sid 7 FROM v$mystat 8 WHERE rownum = 1) 9 / MODULE LENGTH(MODULE) ACTION LENGTH(ACTION) ---------------------------------------------------------------- -------------- ---------------------------------------------------------------- -------------- Lorem ipsum dolor sit amet, consectetur adipisic 48 Ut enim ad minim veniam, quis no 32 SQL> BEGIN 2 dbms_monitor.serv_mod_act_trace_enable('RAC1' 3 ,'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do' 4 ,'Ut enim ad minim veniam, quis nostrud exercitation ullamco labor'); 5 END; 6 / BEGIN * ERROR at line 1: ORA-12899: value too large for column "SYS"."WRI$_TRACING_ENABLED"."QUALIFIER_ID1" (actual: 64, maximum: 48) ORA-06512: at "SYS.DBMS_MONITOR", line 78 ORA-06512: at "ORACLE.PK_DBMS_MONITOR", line 13 ORA-06512: at line 2
Oh well.. Guess it’s sticking to 48 and 32 bytes for now then.
UPDATE: Oracle support have pointed out the, rather odd, “_module_action_old_length” parameter:
SQL> alter session set "_module_action_old_length" = false; Session altered. SQL> begin dbms_application_info.set_module('Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do' ,'Ut enim ad minim veniam, quis nostrud exercitation ullamco labor'); end; / SQL> SELECT module ,length(module) ,action ,length(action) FROM v$session WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1) / MODULE LENGTH(MODULE) ACTION LENGTH(ACTION) ---------------------------------------------------------------- -------------- ---------------------------------------------------------------- -------------- Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do 64 Ut enim ad minim veniam, quis nostrud exercitation ullamco labor 64
So you can take advantage of the larger fields on 11.2.0.3. However, the usefulness of doing so is questionable so far as you still can’t enable tracing for values longer than the “old lengths”
SQL> BEGIN dbms_monitor.serv_mod_act_trace_enable('RAC1' ,'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do' ,'Ut enim ad minim veniam, quis nostrud exercitation ullamco labor'); END; / 2 3 4 5 6 BEGIN * ERROR at line 1: ORA-12899: value too large for column "SYS"."WRI$_TRACING_ENABLED"."QUALIFIER_ID1" (actual: 64, maximum: 48) ORA-06512: at "SYS.DBMS_MONITOR", line 78 ORA-06512: at line 2 SQL> set lin 100 SQL> desc "SYS"."WRI$_TRACING_ENABLED" Name Null? Type ----------------------------------------------------- -------- ------------------------------------ TRACE_TYPE NOT NULL NUMBER PRIMARY_ID VARCHAR2(64) QUALIFIER_ID1 VARCHAR2(48) QUALIFIER_ID2 VARCHAR2(32) INSTANCE_NAME VARCHAR2(16) FLAGS NUMBER