sqltrace - stitch together one EXPERIENCE ID from different tracefiles

 

Today I stumbled across an interesting question:
(slightly edited)

How can I cut one specific EXPERIENCE ID from several SQL-tracefiles and merge it together in one (artificial) tracefile for further processing.

For those who are familiar with SQL-Trace files, the term EXPERIENCE ID might be dubious. That's for a good reason: The EXPERIENCE ID is developed by Cary Millsap and Jeff Holt to identify one specific experience. It can be implemented by several ways - here an example from The Method R Guide to Mastering Oracle Trace Data:

begin 
    dbms_session.session_trace_enable(true, false, 'first_execution');
	
    dbms_log.ksdwrt(1, '*** EXPERIENCE ID:('||sys_guid()||')'); 
    dbms_session.set_identifier(sys_context('userenv','session_user')); 
    dbms_application_info.set_module('OE', 'BOOK');
end;
/

select 'your application goes here' from dual
/
begin
    dbms_application_info.set_module('', '');
    dbms_session.set_identifier('');
    dbms_log.ksdwrt(1, '*** EXPERIENCE ID:()'); dbms_session.session_trace_disable();
end;
/

You can imagine, all the tools in the Method R Workbench can deal with this artificial trace entry. 

Now the initial request is still somehow special: Normally one EXPERIENCE ID will be visible only in one tracefile. But in this special implementation, one experience can be spread over several tracefiles due to the applications special handling of its connection pool.

There is no simple tool to grab all the different pieces and glue them together: 

• oracles trcsess does not know about the EXPERIENCE ID, so it is of no help here to identify ine experience. 
• Method-Rs mrcrop can create single files for each EXPERIENCE ID, but it doesn't glue them together as required. 

But both can be combined in a quite nice way: 

mrcrop can filter for a specific experience with the parameters experience ‑‑id=$experience, 

and trcsess can stitch together several tracefiles, when they have one of its filter-criteria in common. 

This leads to a simple pseudo-code which does the trick :

for each experience ID:
  {purge previous mrcrop directories}
  for each XE_ora_*.trc
    mrcrop experience --id=$experience XE_ora_file.trc
  trcsess output=$experience.trc service=xepdb1 XE_ora_*/XE_ora*.trc 

By this we have one tracefile for each experience - goal achieved.