by Jonas Mason, Senior Consultant
In this last blog of three on the HammerDB Capture and Replay feature, I discuss the command line execution of the replay of SQL contained in TCL files against a destination instance. In Part I of this series, I covered using HammerDB’s GUI to capture and replay SQL, which can be a bottleneck when more than 30 Oracle trace files must be converted to TCL, and then replayed against a destination instance. In Part II of this series, I covered the command line approach to converting more than 30 Oracle trace files to TCL command line. This proved to be a much more efficient method for converting files.
Replay execution of the 300 TCL files using the command line took about 10 minutes once the initial setup was complete. In prior replay efforts, taking a sample of 50 of the TCL files to replay using the GUI took hours to complete. This improved command line approach allowed for 100% playback of the TCL files and created a report on the errors encountered, which was very helpful for this use case. This blog demonstrates how to output the errors associated with SQL replayed and write errors and select results to files. The command line capture and replay approach outlined in this blog also enables back-to-back capture and replay tests to occur with faster turnaround and less labor.
Despite this more efficient method of replaying TCL files, limitations associated with replaying DML in sync between multiple session trace files still exists. Errors should be expected with DML, unless a single session is responsible for all DML during capture, and the destination instance is recovered to the same SCN as your source instance (when you began your SQL capture). Therefore, tracing a single session in a QA environment for replay purposes could have benefits.
The primary objective in this capture and replay scenario was to determine if SQL executed in 10g would parse and execute in a 12c environment. Errors returned in the parsing of SQL in 12c provided developers with the SQL to remediate.
HammerDB Replay of SQL in Converted TCL
In this section, I discuss the command line approach to replaying SQL contained in TCL files against a destination instance. The output I captured replaying SQL included: 1) errors and 2) results from select statements. While there may be some value in reviewing wait events during the replay period using a Statspack or AWR report, limitations on replaying DML with this method would cause incomplete reports, and therefore any findings would require significant caveats. If I am mostly looking at select statement performance however, HammerDB SQL replay coupled with a SQL tuning product to compare performance in both instances would be useful.
On the destination server and instance I placed my converted TCL files in the HammerDB home, and tested the replay of one TCL file from the command line:
#tclsh prod_ora_22851.trc.tcl
{00.00.0000 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production} PROD .99
{x: Parts Request}
x.com Fxxs http://www.fxxs.net/ xxs.net
3613
864647 {} Y N A Y Y Y Y 0 {xx: Parts Request}
3
MN
ORA-02291: integrity constraint (PROD.LN_XX_FK) violated - parent key not found
  while executing
"orabindexec $curn4 :SYS_B_1 {Email status updated to OPT-OUT by lead.} :SYS_B_2 {6867} :SYS_B_3 {Y} :SYS_B_0 {112282102} "
If the TCL script successfully connects to the instance and the SQL executes, you will see select results and errors. If you aren’t interested in capturing the select output or errors for some reason, just create a script to loop through your *.tcl files to execute them serially against the instance.
I needed to write errors and results to file, so modified my command line as follows:
tclsh prod_ora_22851.trc.tcl > prod_ora_22851.trc.tcl.result
Since I had 300 TCL files to execute, I created a batch script with 300 cmd line entries. I made this script executable and ran it. Select results were written to *.trc.tcl.result files, and errors were written to tcl_error_detail.log in the play_tcl batch script.
./play_tcl &> tcl_error_detail.log
The tcl_error_detail.log contains detail that identifies problematic SQL within TCL files:
(file "prod_ora_21565.trc.tcl" line 53)
ORA-01790: expression must have same datatype as corresponding expression
  while executing
ORA-01008: not all variables bound
  while executing
"orabindexec $curn2 :SYS_B_5 {mm/dd/yyyy hh:mi:ss am} :SYS_B_1 {112374021} :SYS_B_6 {2} :SYS_B_2 {9/12/2017 10:00:00 AM} :SYS_B_7 {SS \#1} :SYS_B_3 {mm..."
  (file "prod_ora_21877.trc.tcl" line 365)
Lastly, I reviewed select output to validate result sets returned from successful select execution.
Conclusion
If you have 30 or more trace files to convert to HammerDB TCL scripts for replay purposes, the command line approach is a significant time saver over the HammerDB GUI approach. If the capture and replay process needs to be repeated often, and without significant manual labor, this command line approach can be adopted and further refined to meet those needs.
In this case, the HammerDB capture and replay features met my specific purpose of identifying problematic SQL from a parsing perspective, which informed development efforts for an upgrade. Since SQL execution times and destination instance explain plans can be compared to source instance explain plans, regression can be identified and remediated. Any findings and work performed at this SQL level should be augmented with synthetic transactional and I/O related tests.
House of Brick consultants are not typically faced with projects where the capture and replay of SQL is required. It is more typical for us to use a combination of synthetic tools and reports including Calibrate IO, SLOB, HammerDB, Swingbench, SQL Tuning Advisor, Access Advisor, dd, sar, vmstat, iostat, and AWR/statspack. In our experience, these repeatable artificial tests best inform right sizing efforts.
Please note: this blog contains code examples provided for your reference. All sample code is provided for illustrative purposes only. Use of information appearing in this blog is solely at your own risk. Please read our full disclaimer for details.
