Oracle 11g database replay tutorial (2): Basic Server Load balancer instance (1)

Bkjia.com exclusive Article]Oracle 11gR1 provides the ability to capture application loads in the production environment and replay loads in the testing environment, this technology is used to determine the impact on the performance of the system, database, or application after modification. In this article, I will describe how to capture and prepare the load from the current production database in the p + 0 environment, and how to replay the same load in an Oracle 11g test environment as the next p + 1 Database System. This technology gives Oracle DBAs the opportunity to analyze and isolate harmful performance changes.

This article focuses on how:

◆ Capture a real load from an Oracle 11g Database
◆ Capture the corresponding Automatic Load warehouse AWR) data
◆ Prepare a test database environment for the final load Replay
◆ Transfer production environment configuration to test environment
◆ Pre-processing production load
◆ Replay the load on the Test System
◆ Analyze any performance problems and differences found

Simulate application environment

In this article, I need to note that the same database is used for both capture and replay operations to simplify the process. I am using the most basic Oracle 11g seed database and the sample solution installed by default. The DATABASE will run in ARCHIVELOG mode with the FLASHBACK log function, so that the flashback database command can be quickly used to roll back to an initial point during replay.

Phase 1: Recording Load

◆ To create a capture/replay scenario, I have created a new user, table, index, and related PL/SQL objects:

◆ A new user ADMIN will be used as a repository for storing all management objects. At the same time, I have created a table to store the primary key value. For the code for creating the user and table, see Appendix A: ADMINSetup. SQL.

◆ PL/SQL package ADMIN. PKG_SEQUENCING control specifies a new primary key value. For details about this package, see Appendix B: pkg_sequencing.spc. For the initial version of this package, see Appendix C: pkg_sequencing_v1.bdy.

◆ Another user AP, which encapsulates the solution of an account payment system, including the new table AP. VENDORS, AP. INVOICES and AP. INVOICE_ITEMS. For details about how to create this scheme and Related Object scripts, see Appendix D: APSetup. SQL.

◆ To fill in the object of the AP solution, I created a package AP. PKG_LOAD_GENERATOR. For its description file and main file, see Appendix E: pkg_load_generator.spc and Appendix F: pkg_load_generator.bdy.

◆ Finally, APInitialization. the SQL code fills the table AP with hundreds of lines of simulated data. VENDORS, and in the AP table. INVOICES creates 25 invoice records in the table AP. INVOICE_ITEMS creates the corresponding invoice details, collects the original statistics of all objects in the ADMIN and AP solutions, and creates a directory object DBRControl, it is used to store the result script during database replay and capture XML files generated during the load.

Create a load capture

So far, our source database environment has been initialized, and I will start a real load capture. Figure 2.1.1 below shows the initial window for database replay, it is obtained by accessing the software and support of the EM Database Control software. The real application test on the tag is obtained through the database link under the section.

Figure 2.1.1: SLB capture settings: initialization page

If I select the first task, make sure that all the prerequisites listed in the check list are met before executing the capture session.

Figure 2.1.2: Server Load balancer settings: scheduled Environment check list

On the following page, let me select whether to restart the database before the load is officially captured, and filter unwanted session activities such as EM itself ), note that I will follow the Oracle 11g best practices to recommend "Clear capture": I will accept EM's advice to stop and restart the database to create a valid capture start time.

Click to view the larger image) fig 2.1.3: SLB capture settings: Option

The following page displays the name of the capture session and the directory that stores the replay script.

Figure 2.1.4: Server Load balancer settings: set parameters

Next, we need to name the task scheduling. Figure 2.1.5 and figure 2.1.6 show the final task confirmation setting interface.

Figure 2.1.5: Server Load balancer settings: Specify the EM Task Name.

Figure 2.1.6: Load capture settings: final task View

Finally, Oracle 11g requests the last confirmation.

Figure 2.1.7: Server Load balancer settings: Task submission

The capture starts. As long as the Oracle 11g screen is displayed, it actually waits for me to start representative loads on the source database again.

Capture a real load

To simulate parallel execution of similar code by different users, I have prepared a simple shell script. For more information, see Appendix G: RandomLoadGenerator. sh). It performs some simple queries for CPU-intensive computing, queries generated in the AP solution, and inserts thousands of rows of records in the AP solution table, I have configured my source database environment to use multiple service names, each corresponding to a user. For details, see Appendix H: SI_Services_tnsnames.ora, I added these service names to my database configuration file TNSNAMES. as an optional connection alias in ORA.

I started this load in my p + 0 database environment. After the execution, I went back to the EM Database Console to view the execution result, as shown in 2.2.1, click "Stop capture" to end the load capture.

Figure 2.2.1: Load Capture: Review capture task status

At this time, Oracle 11g will ask you to confirm whether to end the capture process and display a time table until the capture is complete.

Click to view the larger image.) Figure 2.2.2: Load Capture: confirm to terminate the capture.

Click to view the larger image) fig 2.2.3: Load Capture: end capture

When the capture is terminated, Oracle 11g will ask whether to capture the recorded load corresponding to the automatic load warehouse AWR) data, as shown in 2.2.4, I chose to capture all relevant AWR snapshots, for later report comparison.

Figure 2.2.4: Server Load balancer: request to generate an AWR Snapshot

Once the capture is complete, you can view the capture results to check whether the capture is successful and whether enough data is contained. If the data is insufficient, the flashback database command allows me to return to the DATABASE status before the capture starts and then start capturing again. I also select the "view load capture Report" button to generate a complete DATABASE capture Report link: http://www.databasejournal.com/img/2008/05/PCW_Report_1.html ).