Oracle Database 11g: Database Replay, Part 3
June 30, 2008
Synopsis. Oracle Database 11gR1s new Database Replay (DBR) features allow an Oracle DBA the capability to capture a workload from an Oracle 10gR2 database environment and then replay that same workload against an Oracle 11gR1 database environment to analyze how the transition to the new Oracle database release will impact overall systems performance. The final article in this series illustrates how to use these features to capture and prepare a workload from a current Oracle 10gR2 single-instance database environment and then replay that identical workload in an Oracle 11gR1 Real Application Clusters (RAC) testing environment. This offers an Oracle DBA the unprecedented opportunity to identify any potential performance bottlenecks well in advance of the transition to a RAC environment.
The prior article in this series demonstrated a relatively straightforward scenario: how to capture a simulated application workload within the current Oracle 11g production environment (P+0) and then replay that same workload in the next iteration of that same environment (P+1). This article tackles a somewhat more ambitious task because it will illustrate how to:
Since I concentrated on how to perform these tasks almost exclusively within the Oracle 11gR1 Enterprise Manager GUI, Ill concentrate on how to use Oracle 11gR1s supplied PL/SQL packages, DBMS_WORKLOAD_CAPTURE and DBMS_WORKLOAD_REPLAY, to accomplish the same tasks via SQL*Plus and scripting.
Phase 1: Record a Workload in Single-Instance Environment
To keep this capture and replay scenario simple and because I hate wasting anything useful - Ill utilize the same PL/SQL objects that I constructed in the prior article to generate a workload for capture against a single-instance Oracle 10gR2 database. Since the minimum required release level to capture a workload in Oracle 10gR2 is 10.2.0.4. I first used the Database Upgrade Assistant (DBUA) to patch an existing Oracle 10gR2 database named DB10G up to Oracle Release 10.2.0.4 a relatively painless process that takes about 30 minutes in my simulated Oracle 10gR2 production environment.
I then executed the same scripts and PL/SQL code to create and initialize a suitable environment just prior to executing a simulated application workload for capture. (See the first section of the prior article for a summary of the scripts and code to accomplish this.)
Preparing for Workload Capture. Now that my source databases environment is initialized, Ill initiate the capture of an actual workload. Listing 3.1 shows how I used procedure DBMS_WORKLOAD_CAPTURE.ADD_FILTER to first apply some appropriate filters to eliminate capture of user sessions that are producing uninteresting activity or need to be ignored during the capture period. In this case, I want to make sure that any activity related to either Enterprise Manager Database Control or Grid Control wont be captured.
Starting Workload Capture. The code shown in Listing 3.2 illustrates how I used procedure DBMS_WORKLOAD_CAPTURE.START_CAPTURE to initiate workload capture. This procedure first checks the target directory (DBRCONTROL) for any prior executions of workload capture files; if any are found, it returns an error and wont allow the current workload capture attempt to continue. If the capture startup is successful, however, the DB10G databases alert log will recognize that a DBR capture operation is underway:
. . . Mon Jun 23 19:40:41 2008 ALTER SYSTEM SET pre_11g_enable_capture=TRUE SCOPE=BOTH; Mon Jun 23 19:40:44 2008 DBMS_WORKLOAD_CAPTURE.START_CAPTURE(): Starting database capture at 06/23/2008 19:40:44 . . .
Since Im capturing the workload against an Oracle 10gR2 database, note that I also had to set the dynamic initialization parameter PRE_11G_ENABLE_CAPTURE to TRUE before starting the capture.
Generating a Workload. To simulate multiple executions of similar code by different users, I prepared and executed a simple shell script named 10gSI_RandomLoadGenerator.sh. Like its similar predecessors in the prior article, it starts up approximately 80 user sessions that perform a random set of tasks like executing simple queries that do CPU-intensive computations, generating complex queries against the AP schema, or performing intense bursts of DML that add several thousand rows into the AP schemas tables.
Note that Ive also configured the DB10G database to use just one service name (DB10G) regardless of the type of operation being performed. Ive added this service name as a potential connection alias in my databases TNSNAMES.ORA configuration file. (In later steps, Ill illustrate how to remap this connection to a different service name during workload replay against my Oracle 11gR1 RAC environment with DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION.)
Halting Workload Capture. To halt the workloads capture, I executed procedure DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE to stop the workload capture operation (see Listing 3.3). Note that the successful conclusion of the DBR capture operation is also recorded in database DB10Gs alert log:
. . . Mon Jun 23 19:42:21 2008 Thread 1 advanced to log sequence 43 (LGWR switch) Current log# 3 seq# 43 mem# 0: /u01/app/oracle/oradata/db10g/redo03.log Mon Jun 23 19:44:35 2008 DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE(): Stopped database capture successfully at 06/23/2008 19:44:32
Viewing Workload Capture Results. To review the results of what the workload capture operation actually captured, I executed procedure DBMS_WORKLOAD_CAPTURE.REPORT to generate a summary report (see Listing 3.4). The output from this report can be viewed in text-only format in Report 3.1 or in HTML format here.
Phase 2: Preparing for Replay
After I successfully captured an appropriate workload against my Oracle 10gR2 single-instance database, I tackled the preparation of my target environment an Oracle 11gR1 Real Application Cluster (RAC) clustered database:
Preparing the Workload. Now that my Database Replay target environment is in place, Im ready to prepare the workload that I captured in single-instance mode for its eventual replay in a RAC database environment:
Listing 3.6 shows the commands I issued to create the physical directories on each node, the corresponding directory object in the RACDB database, and (after all workload capture recorded files have been copied to the appropriate physical directories) how I executed procedure DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE to preprocess the captured workload