11
Using Database Tuning Advisors
CERTIFICATION OBJECTIVES
11.01 Use the SQL Tuning Advisor
11.02 Use the SQL Access Advisor to tune a workload
11.03 Understand Database Replay
From a tuning perspective, every system has a performance bottleneck that may move from component to component over a time period of days or even weeks The goal of performance design is to make sure that the physical limitations of the applications and the associated hardware—I/O throughput rates, memory sizes, query performance, and so on—do not impact performance of the business of the company. If the application performance limits the business process it is supposed to be supporting, the application must be tuned. During the design process, the limits of the application environment—including the hardware and the design of the application’s interactions with the database—must be evaluated. No environment provides infinite computing capacity, so every environment is destined to fail at some performance point. In the process of designing the application, you should strive to have your performance needs amply served by the performance capabilities of the environment.
Performance tuning is the final step in a four-step process: planning, implementing, and monitoring must precede it. If you tune only for the sake of tuning, you are failing to address the full cycle of activity and will likely never resolve the underlying flaws that caused the performance problem. Tuning activities can be further subdivided into three parts: performance planning, instance tuning, and SQL tuning. Once you are at the SQL tuning step, you can use the tools presented in this chapter: the SQL Tuning Advisor, the SQL Access Advisor, and Database Replay.
SQL Tuning Advisor, the most granular of the tuning tools, takes one or more SQL statements as input, analyzes all access paths, and generates advice or recommendations to improve the SQL statements. The recommendations may include new indexes, a restructured SQL statement, or the creation of a SQL profile. Within a maintenance window, Oracle automatically runs the SQL Tuning Advisor on high-load statements identified and recorded in the Automatic Workload Repository (AWR).
The SQL Access Advisor, in contrast, takes a much wider view of a tuning operation. It takes all SQL statements within a workload, such as all SQL run by an application within a specified period, and recommends indexes, materialized views, and partitioning schemes to improve the overall performance of the application. Tuning SQL statements individually may be counterproductive. Tuning a single SQL statement in the workload may improve performance at the expense of other SQL statements, and vice versa.
Finally, Database Replay makes it easy to capture a workload in one configuration and replay it one time or several times on another configuration. For example, you may want to compare the performance difference of a given workload on your production system to the performance of the same workload on a server with multiple CPUs and a later release of the operating system or database software. After replaying the workload on the new configuration, Database Replay generates a Workload Replay report that identifies differences in execution times, result sets, and error conditions.
CERTIFICATION OBJECTIVE 11.01
Use the SQL Tuning Advisor
The SQL Tuning Advisor analyzes one or more SQL statements sequentially, examining statistics, and potentially recommends creating a SQL profile, new indexes, materialized views, or a revised SQL statement. You can run the SQL Tuning Advisor manually; however, it is run automatically during every maintenance window on the most resource-intensive SQL statements identified within the production workload. Optionally, you can specify that the analysis performed during the maintenance window automatically implements recommended SQL profiles.
Overview of SQL Tuning Advisor
Whether the SQL Tuning Advisor runs automatically or you run it manually on one or more SQL statements, it performs the same types of analyses:
Statistics Analysis Check for stale or missing statistics, and recommend refreshing or creating them.
SQL Profiling Collect auxiliary statistics on a SQL statement along with partial execution statistics and store them in a SQL Profile.
Access Paths Analyze the impact of creating new indexes, materialized views, and partitioning.
Structure Analysis Restructure the SQL statements to see if better execution plans are generated.
From the preceding list, you can configure the Automatic SQL Tuning task to automatically implement SQL Profiles if the performance improvement improves by a factor of three. All other recommendations, and SQL Profile recommendations for minimal performance improvements, must be implemented manually after reviewing the Automatic SQL Tuning Report.
Note that SQL Tuning Advisor considers each SQL statement individually. If it recommends an index for a SELECT statement, it may help the performance of the query but may dramatically reduce the performance of DML activity against the table in a heavily OLTP environment. Thus, the SQL Access Advisor, discussed later in this chapter, may be a better analysis tool to analyze all operations against one or more tables in a workload.
Using SQL Tuning Advisor
The SQL Tuning Advisor can use a number of sources for its analysis. If you are experiencing a significant slowdown in database performance, you can run SQL Tuning Advisor on the top SQL statements currently running. In addition, you can specify a single SQL statement or a set of SQL statements. This is specified as a SQL Tuning Set as input to SQL Tuning Advisor. Finally, you can retrieve historical SQL statements from AWR snapshots.
The following sections show you how to configure SQL Tuning Advisor and view its recommendations using EM. Finally, you’ll see how to perform the same tasks using the PL/SQL packages DBMS_AUTO_TASK_ADMIN and DBMS_SQLTUNE.
Configuring SQL Tuning Advisor
You can set SQL Tuning Advisor options in Enterprise Manager. Oracle recommends using EM to run the SQL Tuning Advisor due to the relative complexity of using the DBMS_SQLTUNE PL/SQL packages; however, you’ll see how to use DBMS_SQLTUNE later in this chapter for those occasions where you need more precise control over the tuning process.
From the EM home page, select the Server tab. Near the bottom of the page, click on the Advisor Central link. Under the Advisors heading, click on the SQL Advisors link. You will see the SQL Advisors page shown in Figure 11-1.
FIGURE 11-1 EM SQL Advisors page
Next, click on the SQL Tuning Advisor link. On the page in Figure 11-2, you can specify the options for a manual invocation of the SQL Tuning Advisor. In this example, you specify a 30-minute time limit for the job, to be run immediately, with only limited analysis of each SQL statement. Using the limited analysis option does not generate any SQL profile recommendations. Unless they already exist on your system, one or more SQL Tuning sets must be created before you can submit this job.
FIGURE 11-2 EM Schedule SQL Tuning Advisor
When you click on one of the links in the Overview section of the page shown in Figure 11-2 (Top Activity, Historical SQL, or SQL Tuning Sets), you can create data sources that will create a SQL Tuning Set that you can use as input to the SQL Tuning Advisor. When you click on the Top link in Figure 11-2, you see the Top Activity page shown in Figure 11-3.
FIGURE 11-3 EM Top Activity page
At the bottom of the page, you see the Top SQL activity for the selected time period, which in this example is from 12:55 P.M. to 1:00 P.M. The top SQL includes the execution of a PL/SQL package, an UPDATE statement, and an INSERT statement. Clicking on the Select All link adds all three of these statements to a SQL Tuning Set that you can use on the SQL Tuning Advisor page shown in Figure 11-2. After clicking on the Select All link, click on the Go button next to the Schedule SQL Tuning Advisor action in the drop down menu. You will see the page shown in Figure 11-4 with a SQL Tuning Set created from the SQL statements you selected on the previous page.
FIGURE 11-4 Schedule SQL Tuning Advisor with SQL tuning set
Running SQL Tuning Advisor
In the page shown in Figure 11-4, you schedule a comprehensive analysis of the selected SQL statements. You also want to run the job immediately. Clicking on the Submit button submits the job for processing. Figure 11-5 shows the job in progress.
FIGURE 11-5 SQL Tuning Advisor job progress
Viewing SQL Tuning Advisor Recommendations
After the job submitted in Figure 11-4 completes, you see the results of the analysis. You can also access the results from the Advisor Central page as you can see in Figure 11-6.
FIGURE 11-6 Advisor Central task results
Clicking on the SQL Tuning Advisor task name or selecting the radio button next to the task and clicking View Result, you see the results of the analysis as shown in Figure 11-7.
FIGURE 11-7 SQL Tuning Advisor recommendations summary
Note that the results include recommendations for user SQL (e.g., RJB) and for system accounts (e.g., SYSMAN and DBSNMP). Each SQL statement has one or more types of recommendation, including statistics gathering, creating a SQL profile, creating an index, or revising the SQL statement itself. You can easily implement the SQL Profile recommendation by clicking on the Implement All Profiles button.
Selecting the radio button for the fourth SQL statement shown in Figure 11-7, you can see a detailed explanation of all recommendations for this SQL statement in Figure 11-8.
FIGURE 11-8 SQL Tuning Advisor detailed recommendations
In the page shown in Figure 11-8, you are advised to implement one of the recommendations, such as saving a SQL profile for future executions or creating an index on one of the tables in the SQL query.
Using DBMS_SQLTUNE
If you need to have more control over your tuning tasks or want to run a specific set of tuning tasks repeatedly, you can use the DBMS_SQLTUNE PL/SQL package to create, run, and monitor a SQL Tuning Advisor job.
For a basic analysis of a SQL statement, you will use the following procedures within DBMS_SQLTUNE:
CREATE_TUNING_TASK Create a tuning task for a SQL statement or a SQL Tuning Set.
EXECUTE_TUNING_TASK Execute a tuning task created with CREATE_TUNING_TASK.
REPORT_TUNING_TASK Show the results and recommendations from the SQL Tuning Advisor.
In addition, you can use the following data dictionary views to query the name and status of tuning jobs:
DBA_ADVISOR_LOG Task names, status, and execution statistics for all tasks.
DBA_/USER_ADVISOR_TASKS More detailed information about advisor tasks, such as advisor name, user-specified description, and execution type for the current user.
V$ADVISOR_PROGRESS More detailed information about the completion status and time remaining for an advisor task.
EXERCISE 11-1
Run SQL Tuning Advisor for a SQL Statement
In this exercise, you will use DBMS_SQLTUNE to generate recommendations for one of the SQL statements shown in Figure 11-7.
1. Within an anonymous PL/SQL block, define a tuning task for a SQL statement:
Notice that the values provided to DBMS_SQLTUNE.CREATE_TUNING_TASK correspond to the values provided to EM in Figure 11-4, except that in this case you are specifying a SQL statement explicitly instead of creating a tuning set from a 5-minute interval.
2. Use the SET_TUNING_TASK_PARAMETER procedure to change the time limit to 30 minutes instead of 60 minutes as you originally specified when you set up the task:
3. Initiate the tuning task using the EXECUTE_TUNING_TASK procedure:
4. Query the status of the task by joining DBA_ADVISOR_TASKS and V$ADVISOR_PROGRESS:
5. Retrieve the recommendations from the tuning task:
Note the dramatic increase in ease of use using EM versus the PL/SQL procedures. A SQL tuning set using DBMS_SQLTUNE was not even loaded. CREATE_SQLSET, DBMS_SQLTUNE.LOAD_SQLSET, and so forth. That would have added yet another layer of complexity to this exercise.
CERTIFICATION OBJECTIVE 11.02
Use the SQL Access Advisor to Tune a Workload
At first glance, the SQL Access Advisor appears to perform the same function as the SQL Tuning Advisor. However, there are some key differences that will be detailed in the following sections, such as how the analysis is performed and the types of recommendations generated.
Understanding the SQL Access Advisor
The SQL Access Advisor performs an analysis of overall SQL performance using a workload specification. The workload specification can be one of the following:
A single SQL statement
A SQL tuning set
Current SQL cache contents
Statistics
A schema name
Recommendations from SQL Access Advisor include new indexes, materialized views, and partitioning. Another SQL Access Advisor procedure, TUNE_MVIEW recommends changes to materialized views to support fast refresh and query rewrite.
The exam will contain only a few questions about SQL Access Advisor and SQL Tuning Advisor. The exam will require you to know the primary difference between the two. In other words, the SQL Tuning Advisor tunes each SQL statement separately, whereas the SQL Access Advisor tunes all SQL statements simultaneously.Using the SQL Access Advisor with EM
As with the SQL Tuning Advisor and the DBMS_SQLTUNE package, you can use EM for running the SQL Access Advisor instead of using the DBMS_ADVISOR package directly. The four steps to create a set of recommendations are as follows:
1. Create a task
2. Define the workload
3. Generate the recommendations
4. Review and implement the recommendations
If you are using EM to run the SQL Access Advisor, step 1 is performed automatically.
From the Advisor Central page shown in Figure 11-1, click on the SQL Access Advisor link. You will see the page shown in Figure 11-9 where you can perform one of two tasks: (1) verify that existing structures such as indexes and materialized views are being used, or (2) recommend new structures. In other words, you may be able to drop existing indexes if they are not used for SELECT statements.
FIGURE 11-9 SQL Access Advisor options
For this example, you want to find new access structures so you would select the second radio button. If you select the Inherit Options checkbox, you can choose a template that may fit your environment, such as OLTP or data warehousing. When you click on the Continue button, you will see the first step of the wizard shown in Figure 11-10.
FIGURE 11-10 Specify SQL Access Advisor workload source
For the source of the tuning activity, you can select one of three sources: (1) recent SQL from the cache, (2) an existing SQL Tuning set (such as the SQL Tuning set created in the SQL Tuning Advisor example earlier in this chapter), or (3) a set of schemas and tables within those schemas. In this example, you want to analyze all current and recent SQL activity. Therefore click on the corresponding radio button and then click on the Next button.
The next page, shown in Figure 11-11, lets you select which types of access structures that SQL Access Advisor should recommend: (1) indexes, (2) materialized views, and (3) partitioning. In addition, you can direct the SQL Access Advisor to perform a limited analysis on just the high-cost statements, or perform a relatively time-consuming analysis on all relationships between the tables in the specified workload. The Advanced Options section lets you further refine the analysis based on disk space limitations as well as specifying alternate locations for recommended indexes and materialized views. Select the Indexes and Materialized Views check boxes and the Comprehensive radio button. Finally, click on the Next button.
FIGURE 11-11 Specifying SQL Access Advisor recommendation options
The next page, shown in Figure 11-12 specifies the scheduling options for the tuning task. As you can see, EM will automatically create the task for you. Other options on this page include how much logging the SQL Access Advisor generates, how long the task will remain in the database, the total time allotted to this task, and when to start the task.
FIGURE 11-12 Specifying SQL Access Advisor scheduling options
In the page shown in Figure 11-12, accept the default options and click on the Next button to proceed to step 4 of the wizard, as you can see in Figure 11-13. Step 4 summarizes the options you have chosen and gives you a chance to revise the options before submitting the job.
Note the Show SQL button in Figure 11-13. You can use the SQL code to better understand what goes on “under the hood". You can also use the SQL code as the basis of a scripted tuning job that can be incorporated into a batch job, which includes other SQL commands or processes that you cannot easily perform repeatedly within EM. Clicking on the Show SQL button in this example produces the following SQL code:
Click on the Submit button shown in Figure 11-13 to start the analysis. From the Advisor Central page, you can monitor the progress of the job. When the job completes, select the job and click on the View Result button. The page in Figure 11-14 shows a summary of the improvements you can make if you implement the recommendations in the second tab. The SQL Statements tab shows you the statements analyzed and gives you the option to implement the recommendations. The Details tab recaps the options you chose to run this analysis. In this particular analysis, almost half of the recent SQL statements may benefit dramatically if the recommendations are implemented.
FIGURE 11-14 SQL Access Advisor recommendation summary
Using the SQL Access Advisor with DBMS_ADVISOR
As you can see in the sample SQL in the previous section, using the SQL Access Advisor via the DBMS_ADVISOR package can get quite complex, and using EM is an easier way to run most day to day analyses.
On the contrary, the procedure DBMS_ADVISOR.QUICK_TUNE is straightforward and takes as input a single SQL statement to tune. As a result, it performs much like the SQL Tuning Advisor but can perform a much more in-depth analysis, producing more recommendations than the SQL Tuning Advisor, such as materialized view recommendations.
To run the procedure, create an anonymous PL/SQL block and specify the advisor name, a task name, and the SQL statement, as in this example:
The results of the tuning effort reside in the data dictionary view USER_ADVISOR_ACTIONS but the output is not very readable. Therefore, you can use the procedure CREATE_FILE to create the script you can use to implement the recommendations generated by the QUICK_TUNE procedure. First, create a directory object to point to a file system directory to hold the script:
Next, use CREATE_FILE to create the script containing the implementation recommendations:
In this example, the file tune_fts.sql looks like this:
The recommendations include creating a materialized view log, creating a materialized view that can be used for query rewrite, and collecting statistics on the materialized view.
CERTIFICATION OBJECTIVE 11.03
Understand Database Replay
Changes occur in your software and hardware environment, whether it be upgrades to operating system software, database software, or increasing the number of CPUs. Less desirable changes may also occur: due to budgetary constraints, the server hosting your database may soon be part of a consolidation effort and new applications will be added to the server. In any case, you need to measure the impact of these changes. Database Replay will help to assess the change in performance on a test system by capturing the workload on the production server, and then replaying the workload on the test system. This way you can resolve performance problems and ensure that the new production environment will still run your database applications with the same results as the old production system.
Using Database Replay consists of four main steps:
1. Workload capture
2. Workload preprocessing
3. Workload replay
4. Analysis and reporting
Database Replay Workload Capture
The first step of the Database Replay process is to capture the workload. Depending on your environment, you may only need to capture a couple of hours or even a day or two. This depends on the mix of applications in your environment and what time during the day they run.
Included in the capture process are all external database calls from clients; database background activities and internal scheduler jobs are not captured. The client requests are recorded in platform-independent binary files that can be replayed on a database that is installed on a completely different hardware or software platform. As of Oracle Database 11g Release 2, you can even capture client requests on an Oracle Database 10g database and replay them on an Oracle Database 11g Release 2 platform to test a database software upgrade. Each recorded client request contains the following information:
SQL text
Bind values
Transaction information
The transaction information includes timestamps as well. This gives you the option to replay the workload faster, slower, or at the same rate as the original workload. In any case, the timestamps ensure that a client request is replayed in the same chronological order relative to all other recorded requests.
Certain client requests are not captured in a workload. These requests include the following:
Direct path load operations using SQL*Loader
Oracle Streams operations
Advanced Replication streams
Non-PL/SQL-based Advanced Queuing operations
Flashback queries
Oracle Call Interface object navigations
Non-SQL-based object access
Distributed transactions
Remote DESCRIBE and COMMIT commands
Any distributed transactions will still be captured but will be replayed as if they were originally run as local transactions.
The recording process itself incurs minimal overhead on the production system. However, depending on how long you are capturing, you need to ensure that there is enough disk space to hold the captured workload. If you run out of disk space during a capture session, the capture stops.
Be sure to shut down and restart the database instance before starting the capture operation. This ensures that all active transactions complete or roll back before capture begins. Note that ongoing transactions will be captured but not played back correctly because only part of each ongoing transaction will be played back in the target system. You can begin and end the capture process using the PL/SQL package DBMS_WORKLOAD_CAPTURE, as in these code snippets:
Database Replay Workload Preprocessing
After the capture operation is complete, the captured information must be preprocessed by transforming the captured data into replay files that can be easily replayed on the target system. The preprocessing step only needs to happen once for each capture operation. Once preprocessed, it can be replayed over and over on one or more target systems. The preprocessing step makes sense even if you’re going to replay it only once. You want the replay clients to efficiently send their requests to the new server without delays due to preprocessing the capture data on the fly.
As with nearly every database operation, you can use EM or a PL/SQL package to perform the preprocessing step. After moving the capture files to a location accessible to the replay system, you can use PL/SQL to process the capture files located in the directory object REP_CAP_DIR as follows:
Database Replay Workload Replay
During the replay phase, the preprocessed workload executes on the target system using the same timing, concurrency, and transaction dependencies as on the source system. You can, however, "speedup” or “slowdown” one or more client requests depending on the requirements of the new environment.
Database Replay uses one or more replay clients to re-create all client requests. You may only need one replay client, or you may need more replay clients than the original number of clients on the source database. Oracle provides a calibration tool that you run against a captured workload to calculate the number of replay clients you will need to ensure that the workload is played back at the desired rate.
Before running the first replay, you must perform some prerequisite checks:
Ensure that the target system has access to the replay directory.
Remap references to other production systems via database links, external .tables, directory objects, URLs, and E-mail notifications.
Remap connection strings to the replay system from the production system.
Failure to remap all references to the production system will certainly cause disruption and unnecessary load on the production system.
By default, the order of all COMMIT statements are preserved, which is usually the best option to prevent data divergence. However, if most or all of the transactions are independent, you can turn off the preservation of COMMIT order to run the replay faster.
You use the PL/SQL package DBMS_WORKLOAD_REPLAY to initialize and start the replay process, as in this example:
In addition, you can pause and resume the replay using the PAUSE_REPLAY and RESUME_REPLAY procedures, or terminate the replay early by using CANCEL_REPLAY.
Database Replay Analysis and Reporting
Database Replay generates a report detailing actual results of the replay, including all exception conditions such as data divergence due to incorrect or out of sync DML statements or SQL queries. Also, included in the report are detailed time-based statistics such as total DB time and average session time. You can also use AWR reports to perform a detailed comparison between the captured workload and the replayed workload.
You can use the PL/SQL package DBMS_WORKLOAD_REPLAY to retrieve and generate a report, as in this example:
Note that you may have more than one report in the replay directory if you have performed the replay more than once. The SELECT statement in the PL/SQL block ensures that you retrieve the latest report. You can use DELETE_REPLAY_INFO to delete a report in the replay directory.
CERTIFICATION SUMMARY
This chapter gave you a whirlwind tour of SQL tuning, starting out with the SQL Tuning Advisor. You can run it manually but in a default installation of Oracle Database 11g, the SQL Tuning Advisor is run automatically during the maintenance window. This is run on the most resource-intensive SQL captured during normal database operations, whether it be data warehouse queries, on-line customer orders, or a mix of both. You can also run the SQL Tuning Advisor manually against one or more specified SQL statements that are currently consuming more resources than you would expect. Whether you run SQL Tuning Advisor manually or as part of the maintenance window, each SQL statement is considered individually with no dependencies on any other SQL statements within the SQL Tuning Advisor job—or any other objects in the database. The SQL Tuning Advisor recommends indexes, statistics collection, and even restructuring the SQL statement itself in order to produce a better execution plan.
Next, the chapter provided an in-depth look at the SQL Access Advisor. It shares some features of the SQL Tuning Advisor in that you can specify a single SQL statement as input. Typically, you will specify a SQL tuning set, an entire schema name, or the current SQL cache contents as input, providing an analysis considering all SQL statements in the set as a whole. This ensures that the improvements made to one SQL statement do not result in dramatically decreased performance of other SQL statements in the set. Recommendations from the SQL Access Advisor include creating new indexes, materialized views, and partitions.
Finally, an overview of Database Replay was given. Although Database Replay is not a tuning tool per se, it can be used to compare performance between a production system and a new system. The new system can involve improved hardware, updated operating system software, or even a new version of the database. Database Replay can help identify bottlenecks before moving a production system, thus ensuring a smooth upgrade.
TWO-MINUTE DRILL
Use the SQL Tuning Advisor
SQL Tuning Advisor performs statistics analysis, SQL Profile analysis, access path analysis, and structure analysis.
SQL Tuning Advisor can automatically implement SQL Profiles.
SQL Tuning Advisor tunes each SQL statement individually.
You can specify a SQL Tuning Set, a workload, recent SQL activity, or a single SQL statement as input to the SQL Tuning Advisor.
You use the DBMS_SQLTUNE PL/SQL package to run the SQL Tuning Advisor as an alternative to using Enterprise Manager.
You use the views DBA_ADVISOR_LOG, DBA_ADVISOR_TASKS, and V$ADVISOR_PROGRESS to monitor the progress and review the results of a SQL Tuning Advisor session.
Use the SQL Access Advisor to Tune a Workload
The SQL Access Advisor analyzes a workload as a whole.
A SQL Access Advisor workload can consist of a single SQL statement, a SQL Tuning Set, the current SQL cache contents, existing statistics, or a schema name.
Recommendations from SQL Access Advisor include new indexes, materialized views, and partitioning.
The four steps comprising a SQL Access Advisor session are: (1) creating the task, (2) defining the workload, (3) generating the recommendations, and (4) reviewing and implementing the recommendations.
You can either review usage of existing structures or recommend new structures in a SQL Access Advisor session.
The procedure DBMS_ADVISOR.QUICK_TUNE automatically creates a task and analyzes a single SQL statement.
The results from a SQL Access Advisor tuning session are recorded in DBA/ USER_ADVISOR_ACTION. However, you can use DBMS_ADVISOR_CREATE_FILE to create a more readable report.
Understand Database Replay
Database Replay can help to assess the change in performance on a test system by capturing the workload on a production server, and replaying the workload on a test system.
Database Replay consists of four steps: (1) workload capture, (2) workload preprocessing, (3) workload replay, and (4) analysis and reporting.
Each recorded client request contains the SQL text, bind values, and transaction information, including a timestamp.
Client requests such as SQL*Loader operations, Oracle Streams, flashback queries, distributed transactions, and remote DESCRIBE or COMMIT commands are not included in the capture operation.
The production system should be shut down and restarted before initiating the capture operation.
You use the DBMS_WORKLOAD_CAPTURE.START_CAPTURE procedure to initiate the capture operation.
Captured replay information needs to be processed only once for any number of target environments, and for any number of replays on the target system.
You use the DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE to process a captured workload before replaying it.
Before replaying a workload, you need to remap references to the production system.
By default, the order of all COMMIT statements is preserved in a replay.
You initialize a workload replay by using DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY.
You start a workload replay by using DBMS_WORKLOAD_REPLAY.START_REPLAY.
You can generate a report from a replay by retrieving the desired replay within a replay directory, and running DBMS_WORKLOAD_REPLAY.REPORT.
SELF TEST
The following questions will help you measure your understanding of the material presented in this chapter. Read all the choices carefully, because there might be more than one correct answer. Choose all correct answers for each question.
Use the SQL Tuning Advisor
1. The SQL Tuning Advisor performs all but which of the following analyses? (Choose the best answer.)
A. Structure analysis
B. SQL Profile analysis
C. Access paths
D. Changes to materialized views
E. Statistics analysis
2. Which of the following can you use as input for the SQL Tuning Advisor? (Choose all that apply.)
A. A single SQL statement provided by a user
B. An existing SQL Tuning Set (STS)
C. A preprocessed Database Replay workload
D. A schema name
E. A SQL statement identified in EM as using excessive resources
3. Which of the following procedures will run a SQL Tuning Advisor job against a SQL Tuning Set? (Choose the best answer.)
A. DBMS_QUICKTUNE.EXECUTE_TUNING_TASK
B. DBMS_SQLTUNE.EXECUTE_TUNING_TASK
C. DBMS_SQLTUNE.RUN_TUNING_TASK
D. DBMS_ADVISOR.EXECUTE_TUNING_TASK
Use the SQL Access Advisor to Tune a Workload
4. Which of the following can you use as input for the SQL Access Advisor? (Choose all that apply.)
A. A single SQL statement provided by a user
B. An existing SQL Tuning Set (STS)
C. A preprocessed Database Replay workload
D. A schema name
E. Current SQL cache contents
5. Which of the following changes can the SQL Access Advisor recommend? (Choose two answers.)
A. Restructuring one or more SQL statements.
B. Gathering statistics for selected SQL statements.
C. Adding a materialized view log.
D. Enabling query rewrite.
6. Which of the following procedures will run a SQL Access Advisor job against a single SQL statement? (Choose the best answer.)
A. DBMS_QUICKTUNE.EXECUTE_TUNING_TASK
B. DBMS_ADVISOR.EXECUTE_TUNING_TASK
C. DBMS_SQLTUNE.RUN_TUNING_TASK
D. DBMS_ADVISOR.QUICK_TUNE
E. SQL Access Advisor requires a workload, AWR snapshot, or STS, and cannot analyze a single SQL statement.
Understand Database Replay
7. You want to remap your database links so that they do not reference production database objects. Within which Database Replay step do you perform the remapping? (Choose the best answer.)
A. During the workload replay step
B. During the workload preprocessing step
C. During the workload capture step
D. Before the workload capture starts
E. You do not need to remap, since it happens automatically
8. Which of the following database client operations are captured during Database Replay? (Choose all that apply.)
A. A flashback query
B. Distributed transactions
C. Oracle Streams operations
D. A CREATE TABLE statement
E. A transaction started before capturing begins
SELF TEST ANSWERS
Use the SQL Tuning Advisor
1. 
D. Only the SQL Access Advisor recommends changes to materialized views including creating materialized view logs.
A, B, C, and E are wrong. The SQL Tuning Advisor performs statistics analysis, SQL Profiling, access paths, and structure analysis.
2. A, B, and E. SQL Tuning Advisor can use currently running SQL statements, a single statement provided by any user, an existing SQL Tuning Set, or historical SQL statements from AWR snapshots.
C is wrong because you cannot use Database Replay workloads to specify SQL for SQL Tuning Advisor. D is wrong because you cannot specify a schema or table names, you can only specify SQL statements.
3. B. DBMS_SQLTUNE.EXECUTE_TUNING_TASK runs a SQL Tuning Advisor task created with DBMS_SQLTUNE.CREATE_TUNING_TASK.
A, C, and D are not valid packages or procedures.
Use the SQL Access Advisor to Tune a Workload
4. A, B, D, and E. In addition to a single SQL statement (using QUICK_TUNE), an existing STS, a schema name, and current SQL cache contents, SQL Access Advisor also uses statistics to analyze overall SQL performance.
C is wrong because you cannot use the captured Database Replay information as a source for SQL Access Advisor.
5. C and D. The SQL Access Advisor recommends materialized views, materialized view logs, and enabling query rewrite. In addition, SQL Access Advisor will also recommend new indexes or partitions.
A and B are wrong. SQL Tuning Advisor recommends SQL statement restructuring and statistics gathering, not SQL Access Advisor.
6. D. DBMS_ADVISOR.QUICKTUNE runs an analysis on a single SQL statement. You provide the name of the tuning task, which the procedure automatically creates, along with the SQL to be tuned.
A, B, and C are wrong because these procedures do not exist. E is wrong because SQL Access Advisor can run an analysis on a single SQL statement, just as SQL Tuning Advisor can.
Understand Database Replay
7. A. The database links, external tables, directory objects, and connection string remappings need to occur during the workload capture step immediately before replay is initiated.
B, C, and D are wrong because you do not perform the remapping during these steps. E is wrong because you need to perform the remapping manually.
8. B, D, and E. Most SQL statements are captured, including the SQL statement’s text, bind values, and transaction information. Distributed transactions are captured but replayed as local transactions. Even transactions started before capturing begins are captured, but may cause data divergence during replay. Thus, Oracle recommends restarting the instance before initiating capture.
A and C are wrong. In addition to flashback queries and Oracle Streams operations, OCI object navigations, non-SQL based object access, SQL*Loader operations, and remote COMMIT and DESCRIBE commands are not captured.