Difficulties converting an Oracle trace file

[jhfoskfd]

I've successfully converted and replayed a small Oracle trace file that only contained 1 simple query. When I try converting a larger trace file that was captured from a live production application user's session the convert function doesn't seem to work. The contents of the Script Editor tab only shows this:

The trace file does contain several completed SQL statements. I believe I followed the instructions correctly (open the trace file using "Open existing file" and then click "Convert Trace to Oratcl"). Any assistance would be greatly appreciated.

Thank you

[jhfoskfd]

Sorry, I should have included that both trace files, the one that did convert correctly and the one that did not seem to convert, where 10046 traces at level 4.

Thank you

[sm-shaw]

Unfortunately, this does not give any information to help you troubleshoot the problem, even basic information such as the database version. This just means it didn't find any trace to convert so perhaps it was a trace of the wrong session. Please make sure you follow the documentation correctly.

[jhfoskfd]

Steve,
As I said, I was able to convert a small trace file and replay it so I think I have followed the very simple instructions provided in the documentation correctly. The trace file that it will not convert is the correct one, taken from the same DB as the one that did convert and using the same process. It does contain several SQL statements. I’m uploading the trace file for your inspection. If there is additional information you’d like to see please let me know and I’ll send it.
trc.zip

[sm-shaw]

OK, so I don't see any lines in here that match PARSING IN CURSOR ... dep=0 so the empty conversion is working exactly as expected in this case as you have no parent statements in the trace.

It is definitely worth reading some of the documentation around trace files to understand them before trying to convert them: e.g. https://asktom.oracle.com/ords/f?p=100:11:::::P11_QUESTION_ID:5289107304876 and doc 39817.1 on oracle support https://www.hammerdb.com/docs/ch14s01.html.

It could be the mistake you are making is jumping in and tracing a session that has already parsed the statement you want to trace, so it is doing bind and execute however you missed the parent statement and therefore the conversion correctly ignores the recursion. (This is why we mention a logon trigger in the documentation).

Note that also Oracle can and do change trace files between versions and doing such conversions is not supported by Oracle, so there is a lot of opportunity for an interested party to improve on what we have. From what I recall we didn't implement ref cursors although had working examples to do this and also memory dumps in tracefiles.

[jhfoskfd]

"This is why we mention a logon trigger in the documentation"
If you look at the trace file you'll see the 1st statement is login auditing "insert into sys.aud$...". The 2nd statement shows firing the logon trigger from your documentation (with the modifications described below). This trace file was not generated mid-stream in a previously existing session. It was created by your logon trigger and contains the entire session, beginning to end.

-- TRIGGER DDL
create or replace trigger trace_10046_l4
after logon on database
DISABLE
begin
if USER IN ('USER1',''USER2','USER3') then
EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER = "10046_L4"';
execute immediate 'alter session set events ''10046 trace name context forever, level 4''';
end if;
end;
/

-- TRIGGER MODIFICATIONS:
"DISABLE" - So the trigger is initially created in a disabled state and can later be enabled / disabled as needed.
"USER IN" - To specify more than 1 username to be traced
"TRACEFILE_IDENTIFIER" - To add an easily identifiable string to the trace file name making them easier to pick out of a directory full of similarly named trade files.

Thank you for the recommended reading list. I checked them out to make sure I hadn't forgotten anything critical here. After 25 years as an Oracle DBA I'm continually surprised at how many things I've either forgotten or never knew to start with. The latter being the more common of course.
The "dep=0" thing is interesting. I'll look into that and generate some trace files from other database versions and patch levels for comparison.

[jhfoskfd]

I think I may have found the reason. Still need to do some more testing to be certain. Oracle has it's own built-in scheduler and this application runs most functions through the scheduler. Those jobs appear to run at recursive level 1 (i.e. dep=1). The handful of trace files that would not convert were from sessions that originated from the scheduler. The one trace file I had that did convert was NOT a session that ran through the scheduler. I'll do further testing tomorrow to verify but it seems like that may be the answer.

[sm-shaw]

Great, so it looks like the HammerDB trace file conversion is working as expected here.

Also, as noted it is known that this feature is known to not be fully complete and there are a number of (closed) related issues to potential enhancements:

#27
#28
#650

This would need an interested party to take this on to improve it.

[jhfoskfd]

My plan for 1st thing this morning was to get back on this testing but production support issue ended up consuming most of the day. I was able however to get trace files from a couple other database and confirm that what I suspected. Sessions that are created buy the job scheduler run everything at recursive level 1 or above (i.e. dep=>1). So yes, HammerDB trace file conversion does appear to be working as designed (as I understand that design anyway).
While I was looking at this 2 thoughts started bouncing around my hollow head. 1) Modify the trigger so it doesn't trace sessions that will never produce SQL you would want to replay (background sessions, anything executed by any of the Oracle internal users, ... and maybe even job scheduler sessions) And 2) write a preprocessor to go through trace files and if they came from the job scheduler, and match some other safety conditions, flag them as dep=0 so the HammerDB trace file conversion can extract useful SQL from them. Something to play with.
Your "interested party" comment might be something worth exploring. If you don't mind I send you an email ([email protected]) to explore that possibility rather than discussing it in the forum. If that address isn't the right one for this conversation you can address that in a reply.

[sm-shaw]

Yes, a preprocessor was definitely one of the ideas, in some cases with large trace files it can take a long to convert, therefore it makes sense to strip out all the recursive SQL in a first pass.
I do still have details of the outstanding tasks when I stopped working on this, so it may make sense adding all of these into an issue as a placeholder.
You can connect to me here