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

Step 4
The TKPROF utility has a record switch that allows you to record the non-recursive SQL statements found in the trace file:

tkprof SQLQueries.trc SQLQueries.rpt record= SQLQueries.sql explain=rapusr/rapusr
table=rapusr.temp sys=no

tkprof should generate two outputs, the .rpt file that would contain the execution statistics for the trace
collection and the .sql file that would contain the SQL statements found in the trace file.

■ a sample perl script, MVRACPDnTap_extractsql.pl , which you can use to extract sQl statements from the
trace file, is provided with the downloads for this topic.

Note

Step 5
Develop another perl or a java program that can read these SQL statements from the output file generated in Step 4
and execute them against the database in multiple sessions. The value of the number of sessions that each statement
will execute can be a parameter to test increased workload.

Step 6
Using the queries extracted from Step 4 previously, perform a load test simulating the estimated user workload
iterating the queries and measuring response times. This step is also an iterative process; the user load should be
gradually increased through iterations. Through each iteration, statistics and other performance reports such as AWR
should be collected. Then, based on the analysis, the instance and database parameters—and most important the
SQL queries—should be tuned. This test could be performed either using a homegrown tool or third-party software
such as Benchmark Factory (BMF). 2

■ performance should be monitored on all the tiers of the database server (o/s, instance, and database) during
both load-testing phases using various performance-monitoring tools, which we discuss later in this chapter along with
other performance tuning methods.

Note

Method II—Using Real Application Testing
Although much of the work can be done manually using Method I, Oracle provides a product or feature, RAT 3 that is
part of the database (can also be accessed using OEM/EM Cloud Control) and performs all the steps in Method I
seamless to the DBA. Similar to other options that are part of OEM, RAT can also be used from the command line
using PL/SQL packages and procedures. The RAT option includes two testing solutions: database replay and SQL
performance analyzer.
Database replay allows testing of system and configuration changes to environments. It allows replaying a full
production workload on the new system or servers that have configuration changes to help determine the overall
impact of the change.
SQL analyzer, on the other hand, helps determine the impact of system and configuration changes on SQL
statements by identifying any variation in SQL execution plans and performance statistics resulting from the change.

2 Benchmark Factory is a software available from Quest Software (now Dell).
3 RAT is available from EM or EM Cloud Control and requires additional licensing.