Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 30, 2008

Oracle Database 11g: Database Replay, Part 2 - Page 2

By Jim Czuprynski

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.

Click for larger image

Figure 2.2.1: Workload Capture: Reviewing Capture Task Status

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

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM