instrumentation in Informatica

 Instrumentation in applications is important. Especially when it comes to troubleshooting performance 😏. Unfortunately some applications seems to miss this feature. 

For me, Informatica Workflow Manager is such an application. I might be wrong - if I am, please tell me! 

But recently I was facing several issues in Oracle databases where I was only told the workflow name and workflow instance. After a lot of guesswork to identify the problematic sessions and SQLs, I was sure there must be something more useful, so I started to browse & search the internet. 

I found the Transaction Environment SQL. Even not explicit stated, for mee it seems a good point to identify a specific activity. Informatica has some Built-in Variables which are valid candidates, e.g. $PMWorkflowName. 

My first suggestion

BEGIN
DBMS_APPLICATION_INFO.set_module(
    module_name => '$PMWorkflowName',
    action_name => '$PMWorkflowRunInstanceName');
END;

failed with some internal errors:

ORA-06550: line 4, column 35:

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

 

   := . ( % ;

 

Database driver error...

Function Name : executeDirect

SQL Stmt : BEGIN

DBMS_APPLICATION_INFO.set_module(

    module_name => 'WF_SHAREPOINT_MYCOMPLAINCE',

    action_name => 's_M_STG_MC_RUN')

Oracle Fatal Error

Database driver error...

Function Name : ExecuteDirect

 

Oracle Fatal Error

]

Some more research and another KB article showed  a small additional requirements: 
Semicolons ( ; ) are seen as termination character. As there are multiple semicolons inside the PL/SQL block, they must be escaped by a backslash ( \ ).

With this knowledge, the working code is 

BEGIN
DBMS_APPLICATION_INFO.set_module(
    module_name => '$PMWorkflowName',
    action_name => '$PMSessionName')\;
DBMS_SESSION.set_identifier( $PMWorkflowRunId )\;
DBMS_SESSION.set_context('E2E_CONTEXT', 'ECID_UID', $PMWorkflowRunId )\;
END\;

This solution wil not fit every need, but hopefully it can be seen as a valid startingpoint for better instrumentation in Informatica. 

comparison between SQL*TRACE and ASH - get the right information

I'm quite interested in analyzing and improving Performance in Oracle databases. 

There are different approaches. A lot was discussed already if tracing or sampling is appropriate. Namely SQL*Trace or ASH/AWR. 

I will not favor one side here, just pick one aspect and compare both methods. 

Todays aspect is to compare how a specific user experience can be identified. As samples (ASH) are always enabled, it means to filter the right data out of this heap of data; In SQL*Trace it's to scope what or where SQL*Trace should be enabled. 

As I'm a lazy guy, I refer to Cary Millsaps Mastering Oracle Trace Data. In my copy of the book, Chapter 3 (Tracing Oracle) he shows an ordered list of tracing preferences. I will follow this list:

1. If the application set a distinct client ID for each business task execution ...
   SQL*Trace can be enabled by DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE
   ASH can be filtered on column CLIENT_ID
2. If the application set a distinct module and action name ... 
   SQL*Trace can be enabled by DBMS_MONITOR.SERV_MODE_ACT_TRACE_ENABLE
   ASH can be filtered on columns MODULE and ACTION
3. If the program run long enough to be identified ... and still run long enough ... 
   SQL*Trace can be enabled for this session by DBMS_MONITOR.SESSION_TRACE_ENABLE 
   ASH can be filtered on columns SESSION_ID and SESSION_SERIAL# 
4. If there is any x such as SYS_CONTEXT('USERENV', x)  ...
   SQL*Trace can be enabled by an AFTER LOGON trigger
   ASH use this AFTER LOGON trigger to set CLIENT_ID, MODULE or ACTION and then filter on any of those
5. If a new service can be created for the problematic experience ... 
   SQL*Trace can be enabled by DBMS_MONITOR.SERV_MODE_ACT_TRACE_ENABLE
   ASH can be filtered on column SERVICE_HASH (with a little join of DBA_SERVICES)
6. If nothing else helps ... 
   SQL*Trace can be enabled for everything a short period of time by DBMS_MONITOR.DATABASE_TRACE_ENABLE and the user experience can be identified later
   ASH is on all the time, still the user experience mist be identified by the same investigation effort 

This comparison seems to be equal between SQL*Trace and ASH sampling. But I know of a small but specific difference: Wherever the user experience can not be accurately enabled by any setting, it must be filtered by other means. Here SQL*Trace has a big advantage, especially when investigated by a toolset like mrskew in Method-R Workbench: Some activities can be identified by specific start and stop statements, e.g. getting some parameters from a configuration table with a SELECT at the beginning, and write a trace line at the end. mrskew can be used to generate an artificial experience-ID for every experience between these 2 markers. In ASH, as the sampling might not catch any of these statements for a specific execution. This needs additional triggers on the statements to set any CLIENT_ID, MODULE or ACTION as in point 4 above. For sure more work in  this case, especially for SELECTs. 

My high level summary on identifying the users experience: both SQL*Trace and ASH are comparable. The big advantage of ASH is it's always on implementation, whereas SQL*Trace has advantages when it's about specific SQLs as markers.