Managing Database Performance |
413 |
RMAN also allows you to recover all corrupt blocks in a database using BMR. Query the V$DATABASE_BLOCK_CORRUPTION view to measure the extent of the damage, then launch RMAN to perform the recovery:
RMAN> RECOVER CORRUPTION LIST;
When a block is repaired, it is removed from the V$DATABASE_BLOCK_CORRUPTION view.
Block media recovery will fail if there is physical corruption in the redo logs that results in a checksum failure.
Managing Database Performance
In the following sections, you will learn how to use the SQL Tuning Advisor, the SQL Access Advisor, and Database Replay. Each of these tools can be used by the DBA to analyze and improve database performance. The Advisors operate directly on the database you wish to tune, while the Database Replay feature allows you to test a production workload on a test system to determine ways to improve performance without directly impacting the production system.
Using the SQL Tuning Advisor
The SQL Tuning Advisor is a tool that you can use to analyze the performance of one or more SQL statements. To improve SQL performance, the Advisor may suggest new or
modified indexes, SQL profiles, restructuring your SQL statements, or gathering statistics. The SQL Tuning Advisor runs in one of two modes, Automatic or Manual. The Automatic Tuning Advisor is scheduled to run during the maintenance window, finds ways to improve high-load SQL statements, and automatically takes action. Use the SQL Tuning Advisor
in Manual mode to analyze collections of SQL statements or individual SQL statements. In Manual mode, the SQL Tuning Advisor is used to analyze a collection of SQL statements called a SQL Tuning Set.
From the database home page in EM, in the Related Links section, choose the Advisor Central link, then SQL Advisors, and you’ll see the page in Figure 10.12.
Automatic SQL Tuning Advisor
From the SQL Advisors page, click the Automatic SQL Tuning Results Summary link. The page that appears, shown in Figure 10.13, will display the results from the most recent Automatic SQL Tuning Advisor job.
From the results page, you can click the Configure button to configure the Automatic SQL Tuning tasks that will run during each daily maintenance window, as shown in Figure 10.14.
Managing Database Performance |
415 |
And you can view the recommendations made by the Tuning Advisor, as shown in Figure 10.16.
F i g u r e 10 .14 Automatic Maintenance Task Configuration page
F i gu r e 10 .15 Automatic SQL Tuning Result Details page
416 Chapter 10 n Diagnosing the Database and Managing Performance
F i g u r e 10 .16 Automatic SQL Tuning recommendations
Manual SQL Tuning Advisor
From the SQL Advisors page, select the SQL Tuning Advisor to configure manual tuning. On the SQL Tuning Advisor page you will input the parameters for collecting SQL statement information, including scheduling information for task-data collection.
If there are no defined SQL tuning sets, then you have the opportunity to create a new one. On the SQL Tuning Sets page, click the Create button; this will begin a five-step process to create a new SQL tuning set, as shown in Figure 10.17.
F i gu r e 10 .17 Creating a new SQL tuning set
Managing Database Performance |
417 |
Step 1 is to type a name for your tuning set, the schema owner, and a description, as shown in Figure 10.18.
Step 2 is to choose the load methods; in the case, as shown in Figure 10.19, a duration of 24 hours with samples taken at 5-minute intervals.
F i g u r e 10 .18 SQL tuning set options
F i gu r e 10 .19 SQL tuning set load methods
In step 3 you set criteria for SQL statements to include in the tuning set, as shown in Figure 10.20. The drop-down menu allows you to select from a predefined list to add additional filter attributes.
In step 4 (Figure 10.21), you create and schedule a job to collect the SQL statement information and load it into a SQL tuning set. We want to start collecting immediately for this example. Click Next for the final review.
In step 5 we review, confirm, and submit to begin collection to the tuning set, shown in Figure 10.22.
418 Chapter 10 n Diagnosing the Database and Managing Performance
F i g u r e 10 . 20 SQL tuning set filter options
F i gu r e 10 . 21 SQL tuning set schedule
F i gu r e 10 . 22 SQL tuning set review
Managing Database Performance |
419 |
The confirmation page, shown in Figure 10.23, indicates that the SQL tuning set and collection job have been successfully created.
Return to the SQL Advisor page and select the name of this SQL tuning set; you should receive quick confirmation that SQL collection is in progress—the SQL statements count will increase. After you have collected SQL statements in a set, you can run the SQL Tuning Advisor using the tuning set. In Figure 10.24, we identify which SQL tuning set to process, the scope of analysis, and when to schedule the Advisor process. For this example, we will use a comprehensive scope of analysis and schedule it to run immediately.
F i g u r e 10 . 23 SQL tuning set confirmation
F i gu r e 10 . 2 4 SQL tuning advisor schedule
422 Chapter 10 n Diagnosing the Database and Managing Performance
Oracle also provides SQL tuning informational views, if you prefer to use them instead of EM:
NN DBA_ADVISOR_TASKS
NN DBA_ADVISOR_EXECUTIONS
NN DBA_ADVISOR_FINDINGS
NN DBA_ADVISOR_RECOMMENDATIONS
NN DBA_ADVISOR_RATIONALE
NN DBA_SQLTUNE_STATISTICS
NN DBA_SQLTUNE_BINDS
NN DBA_SQLTUNE_PLANS
NN DBA_SQLSET
NN DBA_SQLSET_BINDS
NN DBA_SQLSET_STATEMENTS
NN DBA_SQLSET_PREFERENCES
NN DBA_SQLSET_PLANS
NN USER_SQLSET_PLANS
NN DBA_SQL_PROFILES
Using the SQL Access Advisor to Tune a Workload
The SQL Access Advisor is a tuning tool that assists the DBA by offering recommendations for indexes, partitioning, and materialized view logs for a workload.
Indexing recommendations may include B-tree, bitmap, and function-based indexes. The SQL Access Advisor may recommend partitioning tables, new partitioned indexes, and new partitioned materialized views. It also provides recommendations on how to improve the performance of materialized views by using Fast Refresh and Query Rewrite.
You can manually execute the SQL Access Advisor functions and procedures included in the DBMS_ADVISOR package. For this exercise, we will use Enterprise Manager. From the database home page in EM, in the Related Links section, chose the Advisor Central link, then SQL Advisors. From the SQL Advisors home page, shown in Figure 10.29, choose the SQL Access Advisor.
From the Initial Options page, shown in Figure 10.30, choose the Recommend New Access Structures option.
First, select the workload source. We’ll use a tuning set that we’ve already created, shown in Figure 10.31.
Next, choose the depth and breadth of recommendation options, shown in Figure 10.32. For this exercise, we just want to view index recommendations. The Advanced Options link allows you to select space restrictions, tuning prioritization, workload scope and volatility, default storage schema and tablespace names for indexes and materialized views, and tablespace names for materialized view logs and partitions.
Managing Database Performance |
423 |
F i g u r e 10 . 29 SQL Advisors home page
F i gu r e 10 . 3 0 SQL Access Advisor initial options
F i gu r e 10 . 31 Choose Workload Source for SQL Access Advisor
424 Chapter 10 n Diagnosing the Database and Managing Performance
F i g u r e 10 . 3 2 Recommendation options for SQL Access Advisor task
Step 3 is to schedule the SQL Access Advisor task, shown in Figure 10.33.
F i gu r e 10 . 3 3 Scheduling the SQL Access Advisor task
Managing Database Performance |
425 |
In step 4, we review, verify, and submit, as shown in Figure 10.34.
Once the task is submitted, you receive confirmation that the task was submitted successfully. You can monitor the task through completion from the Advisor Central home page, shown in Figure 10.35.
Once the task is complete, in the Advisor Tasks section, click the Results Name link to view the detailed recommendations for the task. The results of our task are shown in Figure 10.36.
F i g u r e 10 . 3 4 Review and submit the SQL Access Advisor task
F i gu r e 10 . 3 5 Monitor the SQL Access Advisor task
426 Chapter 10 n Diagnosing the Database and Managing Performance
F i g u r e 10 . 3 6 Results for SQL Access Advisor task
For this simplified example, there were no recommendations, as shown in Figure 10.37, because the sample tables are too small. With larger tables and more SQL statements to work with, we would see legitimate recommendations.
F i gu r e 10 . 37 Recommendations for the SQL Access Advisor task
Managing Database Performance |
427 |
To implement the recommendations, select the recommended items and then click the Schedule Implementation button. Figure 10.38 shows the Schedule Implementation page.
Click the Submit button to implement the recommendations. The confirmation note will appear on the Results page, shown in Figure 10.39.
F i g u r e 10 . 3 8 Implement recommendations for the SQL Access Advisor task
F i gu r e 10 . 3 9 Results confirmed for the SQL Access Advisor task
428 Chapter 10 n Diagnosing the Database and Managing Performance
When implementing recommendations in SQL Access Advisor, be aware that certain operations will take time to complete. Partitioning an existing large table may take a long time, so keep that in mind before implementing it. The partitioning process creates a copy of the existing unpartitioned table, so make sure you have sufficient disk space for the operation.
In Exercise 10.2, you will run the SQL Access Advisor on your database instance and determine which tuning steps should be applied.
E x e r c i s e 10 . 2
Using the SQL Access Advisor
In this exercise, you will utilize the SQL Access Advisor to tune a workload. Since the workload is highly dependent on your database configuration, you will need to provide a workload, execute the advisor, and take the recommended actions.
1.Select a workload that you’ve already created, or create a new workload.
2.For the depth and breadth of recommendation options, you want to review only indexing recommendations.
3.Review the recommendations. If they make sense to you for your database, then implement them.
Understanding Database Replay
One of the most difficult tasks for the professional DBA is setting up and conducting valid workload performance tests. It’s easy to test the performance of a single query but often very challenging to test how an entire workload will perform in a different environment. For many organizations, the cost to test a platform migration is prohibitive, and the perceived risk of not testing is too high. Organizations need to know how infrastructure changes will affect database application performance and if there’s any impact to servicelevel agreements.
Database Replay allows the DBA to capture a workload on one database and replay it on another. Database Replay is platform-independent, so it is very useful when planning a hardware or operating-system change to understand how workload performance might also change. If you have multiple platforms or components available to test, you can conduct
a valid and repeatable performance comparison. The DBA and team can utilize Database Replay to identify performance bottlenecks in the workload; determine if storage, CPU, memory, or OS changes can remove the bottlenecks; then run additional comparisons after the changes are made.
Managing Database Performance |
429 |
These are the basic steps of Database Replay:
NN |
Capture the database workload. |
|
|
NN |
Preprocess the workload. |
NN |
Replay the workload. |
NN |
Analyze the workload. |
|
Let’s look at each of these individually.
Capture a Workload
We start by capturing all the external client requests performed against a database and writing the information to a platform-independent binary capture file. The workload capture contains the following client request info:
NN |
SQL text |
|
|
NN |
Bind variable values |
NN |
Information about transactions |
|
Workload capture can be initiated from Oracle Enterprise Manager or through the DBMS_WORKLOAD_CAPTURE package. For this text, we will focus on EM.
There a few basic steps that should be followed before capturing a workload:
NNMake sure you have a replay database that is similar in data content to the capture system. You can accomplish this by using Oracle or third-party tools to keep the data synchronized close to the capture start time. Consider RMAN, a standby database, or export/import.
NNOracle recommends a clean shutdown and restart of the capture database before beginning workload capture. Start the database instance in RESTRICTED mode, start the capture, and the instance will automatically switch to UNRESTRICTED. If a database instance restart is not feasible, then quiesce the database or verify that there are no transactions running at the time the workload capture begins.
NNDefine either inclusion or exclusion workload filters to include or exclude specific user sessions. The default is to capture all user sessions; you can use include or exclude filters, but not both.
NNSet up a capture directory, and make sure it’s empty and has plenty of space. The workload capture will stop if it runs out of space.
Using Enterprise Manager, click the Software and Support tab. From the page shown in Figure 10.40, choose the Database Replay link under the Real Application Testing heading.
Note that EM provides an overview and lists the typical steps to perform a database replay, as shown in Figure 10.41.
Once all of the prerequisites are verified, you can start the workload capture by clicking on the Go to Task icon in the rightmost column of task 1, shown in Figure 10.41. In step 1, you acknowledge that the prerequisites have been met (see Figure 10.42).
430 Chapter 10 n Diagnosing the Database and Managing Performance
Restrictions and Limitations of Workload Capture
NN |
Only one workload capture can run at a time. |
NN |
Distributed transactions will be replayed as local transactions |
The following are not captured: |
|
NN |
Background activities and database scheduler jobs |
NN |
Direct path load of data from external files using utilities such as SQL*Loader |
NN |
Shared server requests (Oracle MTS) |
NN |
Oracle streams |
NN |
Advanced replication streams |
NN |
Non-PL/SQL-based Advanced Queuing (AQ) |
NN |
Flashback queries |
NN |
Object navigations based on Oracle Call Interface (OCI) |
NN |
Non-SQL-based object access |
NN |
Remote DESCRIBE and COMMIT operations |
In an Oracle Real Application Cluster (RAC) database, workload capture is for the database, not for a single instance. Following Oracle’s recommendation to capture a clean workload, you will need to shut down and restart all instances in this manner:
1.Shut down all instances associated with the database.
2.Start one of the instances.
3.Begin the workload capture.
4.Start the remaining instances.
In step 2, you choose to restart the database and select workload filters, as shown in Figure 10.43.
In step 3, shown in Figure 10.44, you specify the name of the capture file, the directory object, and the database-instance shutdown and startup options. If the directory doesn’t exist, you can create it using an OS program, and then click the Create Directory button to assign the directory to a directory object. Figure 10.44 shows confirmation that the directory object was created successfully.
Managing Database Performance |
431 |
F i g u r e 10 . 4 0 Software and support home page
F i gu r e 10 . 41 Database replay home page
F i gu r e 10 . 4 2 The Capture Workload: Plan Environment screen
432 Chapter 10 n Diagnosing the Database and Managing Performance
F i g u r e 10 . 4 3 The Capture Workload: Options screen
F i gu r e 10 . 4 4 The Capture Workload: Parameters screen
Managing Database Performance |
433 |
Specify the job schedule, parameters, and credentials in step 4, shown in Figure 10.45. Step 5 is the final review and acknowledgement, shown in Figure 10.46. Click the Submit
button to begin the workload capture. You will be asked to confirm that you wish to restart the database and begin the capture. If you wish to continue, click the Yes button.
F i g u r e 10 . 4 5 The Capture Workload: Schedule screen
F i gu r e 10 . 4 6 The Capture Workload: Review screen
434 Chapter 10 n Diagnosing the Database and Managing Performance
After you select yes, you will be directed to an information page (see Figure 10.47) while the database is restarted.
Click Refresh to log on to the database after it has restarted. When you log on, you will be directed to the View Workload Capture screen, shown in Figure 10.48, where you may observe the capture in progress. Click the summary icon to change the view. Click the Report button to see the detailed workload capture report. Click Stop Capture to end the workload capture. You will be asked to acknowledge before the capture is stopped. Once you stop the capture, you will be presented with the option to export the workload to the AWR workload directory. If you choose not to save the AWR data at this time, you may do so later. Click the OK button to return to the Database Replay page.
F i g u r e 10 . 47 The Confirmation screen
F i gu r e 10 . 4 8 The View Workload Capture screen
Managing Database Performance |
435 |
For this example, we used the general-purpose database supplied with Oracle 11g. We ran queries from three SQL*Plus sessions and from an MS-Access session.
Preprocess a Captured Workload
The next task is to preprocess the captured workload. Launch the task by clicking the Go to Task icon at the end of the row on task 2 (see Figure 10.49).
To preprocess a workload, select a workload directory and the relevant data will be populated to the EM screen, as shown in Figure 10.50. Once you have acknowledged the correct workload, click Preprocess Workload
F i g u r e 10 . 4 9 Database Replay preprocess
F i gu r e 10 . 5 0 The Preprocess Captured Workload screen
436 Chapter 10 n Diagnosing the Database and Managing Performance
The next screen is shown in Figure 10.51; in step 1, you confirm the capture database version, username, and instance name.
In step 2, you schedule the preprocess job, as shown in Figure 10.52. For this exercise, we will start immediately upon completion of these steps. You will need to provide host OS credentials for the host machine where the database replay capture directory object resides.
In step 3, you review the preprocess job and submit it, as shown in Figure 10.53. When the job is submitted, you will be returned to the Database Replay screen and receive confirmation that the preprocess job has been submitted, as seen in Figure 10.54.
Click the refresh button to verify that the job has completed successfully.
F i g u r e 10 . 51 The Preprocess Captured Workload: Database Version screen
F i gu r e 10 . 52 The Preprocess Captured Workload: Schedule screen
Managing Database Performance |
437 |
F i g u r e 10 . 5 3 The Preprocess Captured Workload: Review screen
F i gu r e 10 . 5 4 The job Confirmation screen
From the Database Replay home page, click the View Workload Capture History link to see the status of captured workloads, shown in Figure 10.55.
F i gu r e 10 . 55 The View Workload Capture History screen
438 Chapter 10 n Diagnosing the Database and Managing Performance
Replay a Captured Workload
To replay a workload, you need a test database that has data that’s similar to the data in the capture database. We created a general-purpose database named STDB using the Database Configuration Assistant, and it is basically the same as the ORCL database we used to capture the workload.
You can perform Workload Replay using the DBMS_WORKLOAD_REPLAY supplied package, but for this example, we will use EM. From the test database EM home page, choose the Software and Support tab, and under Real Application Testing, click the Database Replay. The next page presented is the Database Replay page; in this example, we chose task 3, replay workload. Note that in Figure 10.56, there are no active captures or replays at this time.
As we did during workload capture, we need to specify a directory object, as shown in Figure 10.57, and provide OS credentials to create the directory. Once it’s created, click the Test File System button to verify and then click OK.
The confirmation page in Figure 10.58 gives you the chance to verify the playback information before continuing. Click the Setup Replay button to continue.
F i g u r e 10 . 56 Database Replay home page
F i gu r e 10 . 57 The Create Directory Object screen
Managing Database Performance |
439 |
F i g u r e 10 . 5 8 The Database Replay Confirmation page
The next page, shown in Figure 10.59, reminds you to verify each of the prerequisites before continuing. If you have verified that each of the prerequisites has been met and you’re ready to continue, click the Continue button.
The page shown in Figure 10.60 reminds you that Database Replay should be performed on an isolated test system and to make sure there are no external references on the target test database. Verify that there are no DB links, directory objects, or streams. When you’re ready to proceed, click the Continue button.
F i gu r e 10 . 59 Database Replay prerequisites
440 Chapter 10 n Diagnosing the Database and Managing Performance
F i g u r e 10 .6 0 Database Replay external references
Step 1 of the replay options, Choose Initial Options, allows you to add a custom name for the replay. See Figure 10.61.
In step 2, Customize Options, you can modify the connection mappings on the first page, shown in Figure 10.62, and choose the replay parameters on the second page, shown in Figure 10.63. On the Connection Mappings page, you can designate a connect descriptor and test it; use a single TNS net service name for each client, or use separate connect descriptors for each client.
In step 3, shown in Figure 10.64, you are reminded to prepare the replay clients. You will run the replay clients from the OS, not within Enterprise Manager, so now is a good time to make sure you’re ready to run the clients. Click Next to continue.
F i gu r e 10 .61 The Choose Initial Options screen
Managing Database Performance |
441 |
F i g u r e 10 .62 Database Replay connection mappings
F i gu r e 10 .6 3 Database Replay replay parameters
Managing Database Performance |
443 |
F i g u r e 10 .6 6 Database Replay review
Once the replay has begun, you can monitor the progress, as shown in Figure 10.67. At the OS prompt where you ran the wc command, you’ll notice an acknowledgement that the replay has started, and the replay start time matches the system time:
Replay started (15:16:51)
F i gu r e 10 .67 The View Workload Replay screen
444 Chapter 10 n Diagnosing the Database and Managing Performance
When the replay is complete, the command window will indicate replay completion time. You can then view the report and analyze the results:
Replay finished (15:51:19)
Analyze the Workload Replay Results
The basic steps to analyze the results are to view the capture report, view the replay report, and compare the results. To view the capture report from the database replay page in EM, click the View Workload Capture History link, select the capture report you wish to analyze, and then click the View button. On the subsequent capture summary page, click the View Workload Capture Report button.
We will not go into a detailed analysis of the report, but there are a few key sections of the report to review:
NN |
Workload Captured |
|
|
NN |
Workload Not Captured |
|
|
NN |
SQL Text |
NN |
Workload Filters |
To view the replay report, connect to the replay database using EM. From the database replay page, click the replay workload Go to Task icon. From the drop-down box, choose the directory object for the replay. When the Replay Workload page is populated with capture summary and replay history information, select the appropriate replay name and then click the Replay History View button. Once the workload replay summary is presented, click the View Workload Replay Report button, shown in Figure 10.68.
F i g u r e 10 .6 8 The View Workload Replay summary