dbreplay vs SPA (cage match).

DBreplay vs SPA (SQL Profile Analyzer)

DBreplay
– This product is utilized through the following steps

• 
  Capture a production workload during a critical time. All Statements are captured and stored.
  

• 
  Copy (or mount), the capture files on your “replay” server.
  

• 
  Preprocess the workload
  

• 
  Restore your database, up to the point in time of the capture (the capture can give you the exact SCN needed).
  

• 
  Replay the workload on the copy of production
  

• 
  Compare the replay to the original capture statistics (through AWR). You can also compare different runs to each other.
  

Pros

• 
  The entire workload is replayed with the same data set as your source.
  

• 
  GTT (global temporary tables), are primed properly
  

• 
  The workload is same as production (top executed sql are executed multiple times).
  

• 
  Cardinality feedback, and other optimizer pieces that may change with executions should be seen.
  

Cons

• 
  Capturing a production workload, and preparing a copy of production is a huge effort.
  

• 
  This only tests workload that occurs during the capture. If there are different workload windows (i.e. batch vs online), you must capture the
  different workloads and restore to match.
  

• 
  You cannot touch sql, and anything that part of the capture. If you receive errors, (like ora-4031) you must correct the errors before continuing.
  

SPA (SQL Profile Analyzer)
– This product is

utilized through the following steps.

• 
  A sql tuning set is created on the source system (this contains the sql, the plans, and the execution statistics.
  

• 
  The sql tuning set is exported from old imported into new system.
  

• 
  A SPA job is created that will either compare the statistics with the original execution, or compare statistics by executing on both servers
  (through database links).
  

• 
  Information on the sql statements is reported on, and prioritized by the effect on the workload.
  

PROS

• 
  You don’t need to have the database synched up, as long as it is somewhat representative.
  

• 
  You can re-execute against the source system to compare executions.
  

• 
  A report is created comparing each sql execution
  

CONS

• 
  Does not work with GTT (global temporary tables), since they cannot be primed to run this process
  

• 
  The database test set most likely does not match source, so you need to interpret most of the data.
  

• 
  You do not see the interaction of SQL statements.
  

Conclusion – DBReplay is the preferred tool to test performance differences when making an infrastructure change. If GTT’s are utilized, then DBReplay
is the only tool can really give you useful data. If DBReplay cannot be used, or to supplement dbreplay, SPA is also a useful tool. You can also use
SPA to better tune specific sql (utilize DBReplay to identify sql, and SPA to deep dive them).