To begin converting Oracle trace file workloads with HammerDB the first step is to load an Oracle trace file under the File:Open menu option. Before doing this therefore an Oracle trace file must be generated using Oracle Event 10046. (There are numerous methods to generate Oracle trace files of which only event 10046, level 4 to capture bind variables is covered here). As an example the simplest method to start and stop the trace of a session interactively is as follows:
SQL> connect / as sysdba Connected. SQL> alter session set events '10046 trace name context forever, level 4'; Session altered. ... SQL> alter session set events '10046 trace name context off'; Session altered. SQL>
For more advanced use the creation of a logon trigger is recommended. This trigger can then be enabled or disabled to capture the trace information for a particular use. The example uses the user TPCC created for an Oracle HammerDB OLTP test.
create or replace trigger logon_trigger after logon on database begin if (user = 'TPCC') then execute immediate 'alter session set events ''10046 trace name context forever, level 4'''; end if; end;
This will then create a tracefile automatically at logon. The trigger must be created as SYS with SYSDBA privileges, if created by system the trigger will create successfully but fail on the user login. This event will produce a trace file in the diagnostic area specified for the database server. By default the file will be identifiable by ora_SPID.trc, however there are also methods that can be used to set the name of the trace file. Note that in a Shared Server environment (previously MTS) one users’ session may be distributed across numerous trace files as the user processes share multiple server processes. Therefore in this environment it is necessary to reassemble the trace file data before converting with the Oracle utility ‘trcsess’. For more information on the trace file format the document Note:39817.1 Subject “Interpreting Raw SQL_TRACE output “ available from My Oracle Support, however this knowledge is not essential as HammerDB can convert this raw format into a form that can be replayed.
