1. Generating Trace Files

1. Generating Trace Files

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> select sysdate from dual;

SYSDATE
---------
10-APR-18

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.

SQL> connect tpcc/tpcc@RVDB1
Connected.
SQL> select sysdate from dual;

SYSDATE
---------
10-APR-18

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’. A a raw trace file is shown below:

race file /home/oracle/app/oracle/diag/rdbms/rvdb1/RVDB1/trace/RVDB1_ora_13783.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORACLE_HOME = /home/oracle/app/oracle/product/12.1.0/dbhome_1
System name:Linux
Node name:raven
Release:4.1.12-61.1.23.el7uek.x86_64
Version:#2 SMP Tue Dec 20 16:59:23 PST 2016
Machine:x86_64
Instance name: RVDB1
Redo thread mounted by this instance: 1
Oracle process number: 72
Unix process pid: 13783, image: oracle@raven (TNS V1-V3)


*** 2018-04-10 14:35:39.821
*** SESSION ID:(4.48241) 2018-04-10 14:35:39.821
*** CLIENT ID:() 2018-04-10 14:35:39.821
*** SERVICE NAME:(SYS$USERS) 2018-04-10 14:35:39.821
*** MODULE NAME:(sqlplus@raven (TNS V1-V3)) 2018-04-10 14:35:39.821
*** CLIENT DRIVER:(SQL*PLUS) 2018-04-10 14:35:39.821
*** ACTION NAME:() 2018-04-10 14:35:39.821
 
CLOSE #140169830640712:c=0,e=6,dep=0,type=1,tim=530154237
=====================
PARSING IN CURSOR #140169830722080 len=24 dep=0 uid=0 oct=3 lid=0 tim=530194337 hv=2343063137 ad='97b9c9e8' sqlid='7h35uxf5uhmm1'
select sysdate from dual
END OF STMT
PARSE #140169830722080:c=3149,e=39623,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1388734953,tim=530194336
EXEC #140169830722080:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=530194497
FETCH #140169830722080:c=0,e=16,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=530194568
STAT #140169830722080 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)'
FETCH #140169830722080:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1388734953,tim=530213687

*** 2018-04-10 14:36:07.973
CLOSE #140169830722080:c=539,e=34,dep=0,type=0,tim=558307025
=====================
PARSING IN CURSOR #140169830722080 len=55 dep=0 uid=0 oct=42 lid=0 tim=558307224 hv=2217940283 ad='0' sqlid='06nvwn223659v'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #140169830722080:c=0,e=112,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=558307223
EXEC #140169830722080:c=0,e=214,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=558307510

For more information on the trace file format the document Note:39817.1 Subject “Interpreting Raw SQL_TRACE output “ available from My Oracle Support a, however this knowledge is not essential as HammerDB can convert this raw format into a form that can be replayed.

Figure 13.1. Doc 39817.1

Doc 39817.1