Real Application Testing - Expert Oracle RAC Performance Diagnostics and Tuning - page 110

Testing Methods
There are several methods of testing code. We discussed a few of the testing methods in the early chapters of this
book. In this chapter, the focus is on testing the persistence layer, the layer that finally writes to the database. Testing
the persistence layer can be done either by using a homegrown utility or the new software option available from
Oracle called RAT. 1

Method I—Using Homegrown Utilities
With this method, standard features available with Oracle database combined with some self-developed utilities can
be used.
To get an acceptable user workload, this test will require data and user operations from a real production
environment.
With this method, a load test should be performed against a copy of the production schema (on the new
hardware platform) that contains the actual data from the current live production environment. The purpose of this
test is to tune the instance and the database for application workloads not interfacing the business application.

Workshop
For this test, an extract of the SQL operations from the application is used. One method to extract these operations
from a live system without user intervention is to trace them using Oracle event 10046 and then to parse the trace files
through an application to extract the queries with their respective bind values.

Step 1
In a live production environment, enable Oracle event trace 10046 at Level 8 after connecting to the database as
user 'sys.

ALTER SESSION SET TRACEFILE_IDENTIFIER ='SSKY';
ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

Step 2
Setting the trace file identifier will help organize trace files that are started after the command was issued. The 10046
event generates the trace file in the directory identified by the parameter DIAGNOSTIC_DEST .

■ Depending on the activity on the production servers, the number of trace files and their contents could be large
and consume a considerable amount of disk space. please ensure sufficient disk space is available before attempting
this step.

Note

Step 3
Concatenate all the trace files generated by the event in the user dump destination directory into one file or use the
trcsess utility to combine the trace files into one.

cat *SSKY.trc > SQLQueries.trc

1 RAT requires an additional license.