Oracle 11g database replay tutorial (3): full explanation of load replay for complex tasks (1)

Bkjia.com exclusive Article]Oracle Database 11gR1 new database replay DBR) function allows Oracle DBA to capture load from an Oracle 10gR2 environment, and then replay this load in Oracle 11gR1 environment, this section analyzes how to migrate an existing database to a new version and the impact on the overall system performance. This is the last article in this series that describes how to use these features to capture and pre-process loads from the existing Oracle 10gR2 single-instance database environment, then, the same load is replayed in an Oracle 11gR1 RAC test environment. This gives Oracle DBAs an unprecedented opportunity to identify any potential performance bottlenecks during migration to the RAC environment.

The previous article described a relatively simple scenario: how to capture a simulated application load in the current Oracle 11g production environment p + 0, then replay in the same p + 1 environment. This article deals with more difficult tasks, because:

Capture and record application loads from a single-instance Oracle 10gR2 database, including the corresponding automatic workload database AWR) data.

Transfers the Simulated load to the Oracle 11gR1 RAC test environment.

Pre-load, including re- ing to connections of different load balancing services.

Replay the load in the Oracle 11gR1 RAC test environment.

Identifies application performance problems, data differences, and error differences.

I will use the PL/SQL packages DBMS_WORKLOAD_CAPTURE and DBMS_WORKLOAD_REPLAY provided by Oracle 11gR1 to complete these tasks.

Phase 1: Record loads in a single instance Environment

To make the capture and replay scenario simple-and because I hate to waste anything useful-I will use the PL/SQL object used in the previous article to complete the load on a single instance of Oracle 10gR2 capture, because the minimum version of Oracle10gR2 required for load capture is 10.2.0.4, I first use the Database Upgrade assistant DBUA to upgrade the existing database named DB10G to 10.2.0.4, it took me 30 minutes to upgrade in my Oracle 10gR2 production environment, and it went quite well.

Then I run the same script and PL/SQL code to create and start a suitable environment for capturing the load. For more information about the script and code, see the previous articles in this series)
Prepare to capture the load: Now our source database environment is ready, I will start a real load capture, Listing 3.1 see the attachment) shows how to use the Stored Procedure DBMS_WORKLOAD_CAPTURE.ADD_FILTER to apply some filters to exclude user sessions that generate "not interested" Activities and sessions that can be ignored during the capture process. In this example, I want to capture the activity of the EM console.

Start Capture: Listing 3.2 see the attachment) shows how to use the Stored Procedure DBMS_WORKLOAD_CAPTURE.START_CAPTURE to start load capture. This stored procedure first checks the target directory DBRCONTROL) whether a previously executed load capture file exists. If yes, it returns an error and does not allow the capture to continue. However, as long as the capture is started successfully, the alarm log of the DB10G database identifies a DBR capture operation in progress:

. . . 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 . . .

Because I am capturing an Oracle 10gR2 database, I set the dynamic initialization parameter PRE_11G_ENABLE_CAPTURE to TRUE before starting the capture.
Load generation: to simulate parallel execution of similar code for different users, I have prepared a shell script 10gSI_RandomLoadGenerator.sh), which is similar to the shell script in the previous articles in this series, it starts about 80 user sessions to randomly execute some simple CPU-intensive computing queries, and generates complex queries in the AP solution, execute the DML operation to insert thousands of rows of data into the AP solution table.

At the same time, I configure the DB10G database to use only one service name DB10G), regardless of the operation type, and added this service name in my TNSNAMES. ORA configuration file. In the subsequent steps, in the Oracle 11gR1 RAC environment, I will describe how to use DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION to remap the connection to different service names)
Pause load Capture: to pause the capture, I run the Stored Procedure DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE to stop the load capture operation. For more information, see Listing 3.3, see the attachment ), note that the conclusion of successful DBR capture will also be recorded in the database DB10G alarm 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

View the capture results: to view the actual results of the load capture, I run the Stored Procedure DBMS_WORKLOAD_CAPTURE.REPORT to generate a summary report. See Listing 3.4, see the attachment ), for the output text format of this Report, see Report 3.1 see Attachment) or HTML format http://www.databasejournal.com/img/2008/06/DB10G_WorkloadCaptureReport.html ).