 |
|
Database Replay using the Command Line
Oracle 11g New Features Tips by Burleson
Consulting
June 27, 2008 |
Oracle 11g SQL New Features Tips
After capturing SQL statements from the cursor
cache or AWR snapshots in a production database, pack it into a
staging table and run it for testing on another database to find out
how the statements perform under changed conditions with the SQL
Performance Analyzer feature. SPA runs every statement of the
workload only one time, serially, and skips side effects of DDL and
DML statements.
The next step will be to see how a realistic
workload coming in from external clients with all its concurrency
and contention problems and realistic timing relations can be tested
on another database. This is the biggest highlight among the 11g New
Features. Oracle has invented a new technology which uses external
clients to replay a workload that has been recorded as multiple
streams in special external files. The capture includes the
following requests coming in to the database system from all
external clients:
The following activities cannot be captured:
-
Direct path load of external files
-
Shared Server requests
-
Streams
-
Advanced Replication streams
-
Non PL/SQL AQ
-
Flashback queries
-
OCI based object navigations
-
Non SQL based object access
-
Distributed transactions
-
Remote describe/commit operations
Background processes and all internal activities
of the instance and database are not captured.
Database replay using the command line
The technology behind database replay is a new
executable plus two built in packages:
-
DBMS_WORKLOAD_CAPTURE is the recording infrastructure
used for the capturing. The execute privilege is needed on this
package for capturing a workload. Run the capture with sysRemote DBA
privileges.
-
DBMS_WORKLOAD_REPLAY is used by the replay clients to
replay the captured workload. The execute privilege is also needed
on this package for replaying a workload.
-
Oracle uses the new executable
$ORACLE_HOME/bin/wrc
to start external clients reading the captured streams from the
capture files.
The packages are created automatically if the
database is created with DBCA. For a manually created database, run
the script $ORACLE_HOME/rdbms/admin/dbmswrr.sql to create
the two packages.
 |
This is an
excerpt from the new book
Oracle 11g New Features: Expert Guide to the Important
New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J.
Jain, Brian Carr.
You can buy it direct from the publisher
for 30% off. |
