Capturing an Actual Workload
To simulate multiple executions of similar code by different users, I’ve prepared a simple shell script named RandomLoadGenerator.sh that executes simple queries that do CPU intensive computations, generates complex queries against the AP schema, and also “grinds through” the insertion of several thousand rows into the new AP schema’s tables. I’ve also configured my source database environment to use multiple service names, one for each class of users, as shown in SI_Services_tnsnames.ora. I’ve added those service names as potential connection aliases in my database’s TNSNAMES.ORA configuration file.
I started this workload against my P+0 database environment. Once it was complete, I logged back into EM Database Control and viewed the results of its execution as shown in Figure 2.2.1 below, and then clicked on the Stop Capture button to terminate the workload capture.
Oracle 11g then asks for confirmation of the capture task’s termination, and displays a stopwatch until the capture has been completed.
Figure 2.2.2: Workload Capture: Confirming Capture Termination
Figure 2.2.3: Workload Capture: Confirming Capture Termination
Just as capture termination completes, Oracle 11g asks if I’d like to capture the corresponding Automatic Workload Repository (AWR) data for the recorded workload. As shown in Figure 2.2.4 below, I’ve selected to capture any related AWR snapshot(s) for later comparison reporting.
Figure 2.2.4: Workload Capture: Requesting AWR Snapshot Generation
Once the capture is complete, I can review the Oracle 11g’s capture results to determine if the capture was successful and if it contains sufficient data. If I find that it’s insufficient, the FLASHBACK DATABASE command set allows me to “rewind” the database to just before capture started and then rerun the capture. I also selected the View Workload Capture Report button and generated a Database Capture Report that summarizes the completed capture.
Phase 2: Preparing for Replay
Even though I’ve successfully completed capturing a sufficient workload within my P+0 database environment, I still have some work to do before I can play it back against the P+1 environment.
Resetting the P+0 Environment
Since I’m using the same database for both the source and target environments, I’ll first need to reset my current P+0 database system to the state immediately before I started workload capture. My database is in flashback logging mode, so I’ll simply use Recovery Manager’s FLASHBACK DATABASE command to “rewind” the database to its initial state:
$> rman target /
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> reset database to incarnation 6;
RMAN> flashback database to scn= 4162947;
Migrating to the P+1 Environment
My next step is to apply the changes necessary to bring my database environment to the P+1 state. For this rather simple demonstration, I’ll make two changes that are almost certain to impact the performance of my database in P+1 mode:
- I’ve prepared an improved version of procedure ADMIN.PKG_SEQUENCING.NEXT_ID that uses sequences instead of table ADMIN.NEXT_IDS to determine the next primary key values for tables in the AP schema. This should dramatically increase the performance of procedure AP.PKG_LOAD_GENERATOR.RANDOMDML, which creates a large number of random entries in the AP.INVOICES and AP.INVOICE_ITEMS tables, during workload replay.
- I’ll also drop the index on AP.INVOICES.CUSTOMER_ID and recalculate statistics on the AP schema. Since procedure AP.PKG_LOAD_GENERATOR.RANDOMQUERY often uses this index to select rows efficiently when it generates random queries against view AP.RV_INVOICE_DETAILS, I should expect to see a noticeable regression in its performance during replay of the workload.
“Massaging” the Workload
Now that my P+1 environment is in place, I’m ready to preprocess the workload for playback. Once again, I’ll use EM Database Control to initiate the preprocessing sequence. Figure 2.3.1 shows the results of selecting the Preprocess Workload option from the main Database Replay panel once I’ve selected the DBRControl directory object as the target for preprocessing:
Figure 2.3.1: Preprocess Captured Workload: Selecting a Captured Workload
Once I’ve chosen the desired workload, Oracle 11g reminds me that I’ve got to use the same database version to eventually replay the selected workload ...
Figure 2.3.2: Preprocess Captured Workload: Database Version Warning
... and then sets up a new EM scheduled task to complete the preprocessing.
Figure 2.3.3: Preprocess Captured Workload: Scheduling the Preprocessing Task
Oracle 11g prompts for a final confirmation before submitting the preprocessing task, and then it fires it off immediately.
Figure 2.3.4: Preprocess Captured Workload: Final Confirmation
» 
Comment and Contribute
