As promised I will continue with my series of posts about top 11g new features.
After talking about the SQL Analyzer (Sql Replay) in part I of this series I will continue with another Change Management functionality Oracle database 11g ships with. It is called Replay Database.
This is one of the very big highlights of Oracle database 11g. And this is what many prodution DBAs have been hoping for since a long time.
Any change to a database system, such as an upgrade or changes to the configuration like new hardware or changed parameters, can have dramatic impacts on the performance of the application. It is necessary to test and validate these changed conditions under real life conditions in order to get around possible pitfalls before applying such changes to prodution. This is often a not easy to manage challenge for a DBA.
Oracle database 11g gives us the possiblity to capture a workload of a production system and run it in a testing environment. With this we can identify possible negative impacts of changes and analyze them under real conditions by comparing performance benchmarks for workload periodes in different systems.
How does it work?
On the prodution system we record all client activities for a typical workload periode with a new built in struture in the RDBMS. In fact it is a new built in package named DBMS_WORKLOAD_CAPTURE which is used . The captured worklaod consists of binary files which hold metadata information for the workload and as well as replay files which are stored in a special directory. It is possible to use filters for certain session which should not be captured. The recording user as well as the replaying user needs at least SYSOPER privileges because it is necessary to restart the database before starting the capture process.
After capturing the workload data we need a testing system which should be axactely the same state as our prodution system before the capture process. Now we start one or multiply replay clients (a binary called wrc) to run the pre processed workload against the testing system. Internally another new built in package named DBMS_WORKLOAD _REPLAY is use for this.
After the replay phase we can use analytic tools like ADDM to analyze the workload performance and compare it against teh original wokload performance.
There are a number of new DBA_WORKLOAD_* as well as V$WORKLOAD_* views that can be used to manage and manage the different steps (capture and replay).
Also Oracle Enterprise Manager comes with a very handy interface for the capture process as well as for the replay and analyze steps.
Some good scenarios for database replay could be:
- testing a migration from single instance to RAC
- reproducing of bugs for debugging
Just mentioning that you cannot use this feature yet for preparing the impact of consolidation. Two workloads cannot be mixed (yet, I hope)
Lutz can this option capture only changes, like streams is it redo information based?
Without capturing the select and non-database load(pl/sql) this feature is not a whole, in my opinion most of the problematics query or load source are select queries. If so, maybe in some later releases
best regards.
capture mode option –
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_sqltun.htm#sthref9478
Hi Tonguc,
the input for the recorded workload are all !!! external client requests:
any SQL queries, PL/SQL, PL/SQL remote procedure calls, DML, DDL, OCI calls…
In other words we record everything coming from outrside, as the RDBMS sees it from inside.
What we do not capture are internal activities like background processes and so on..
Cool eh?
=;-)
Lutz
Lutz,
I appreciate your time in putting together a easy to follow overview of the workload capture and replay process.
Cheers,
Vijay