CHAPTER 25. Performance Tuning. Exam Objectives

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1 CHAPTER 25 Performance Tuning Exam Objectives In this chapter you w...
Author: Easter Ramsey
1 downloads 0 Views 1MB Size
All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

CHAPTER 25 Performance Tuning

Exam Objectives In this chapter you will learn to • 052.13.1 Use Automatic Memory Management • 052.13.2 Use Memory Advisors • 052.13.3 Troubleshoot Invalid and Unusable Objects • 053.14.1 Implement Automatic Memory Management • 053.14.2 Manually Configure SGA Parameters • 053.14.3 Configure Automatic PGA Memory Management • 053.15.1 Use the SQL Tuning Advisor • 053.15.2 Use the SQL Access Advisor to Tune a Workload • 053.15.3 Understand Database Replay

1

ch25.indd 1

6/26/2009 2:17:28 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

2 Performance management is a huge subject. The treatment given in the core OCP syllabus is little more than an introduction. There is an additional qualification (the “Oracle Database 11g Performance Tuning Certified Expert”) that requires passing another exam that takes performance management further. But even after studying for and passing that, you aren’t finished. As an Oracle DBA, you will study performance monitoring and enhancement techniques throughout your whole career. Indeed, you may want “Don’t worry—I’ll find the problem soon” inscribed on your tombstone. The topics discussed here are tuning memory; tuning SQL; tuning segment access; identifying objects that are invalid or unusable; and using the Database Replay facility to generate workloads for testing.

Managing Memory Memory usage in the Oracle instance falls into two categories: Program Global Areas (the PGAs) that are private to each session, and the System Global Area (the SGA) that is shared by all the Oracle processes. From release 9i it has been possible to automate the management of the PGA. From release 10g it has been possible to automate the management of the SGA. Release 11g can manage both PGA and SGA together. TIP All Oracle memory usage is virtual memory. The Oracle processes have no way of knowing if the memory to which they are connecting is in RAM or has been swapped (or paged) to disk. However, swapping will cripple performance and should be avoided.

PGA Memory Management A user session against an Oracle instance consists of a user process connected to a server process. The user process generates SQL statements and sends them to the server process for execution: this is the client-server split. Associated with the server process is a block of nonsharable memory: the PGA. When executing SQL, the server process makes use of the PGA to store session-specific data, including • Temporary tables • Sorting rows • Merging bitmaps • Variables • The call stack For some data in the PGA, use of memory is non-negotiable. For example, if the session needs memory for its call stack, that memory must be made available. For other structures (such as temporary table storage) use of PGA is nice but not essential, because if necessary the data can be written out to a disk-based storage structure— though this will impact adversely on performance.

ch25.indd 2

6/26/2009 2:17:51 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

3

PART III

Every SQL statement uses memory in the SGA (specifically, the shared SQL area, in the shared pool) and also will require a minimum amount of PGA memory (sometimes referred to as the private SQL area), without which it cannot execute. Making more PGA memory available will often reduce execution time, but the reduction is not linear. Typically, there will be three stages of memory allocation: these are known as optimal, one-pass, and multipass. The optimal memory allocation will allow the statement to execute purely in memory, with no requirement to make use of temporary storage on disk. The optimal memory allocation is sufficient to accommodate all the input data and any auxiliary data structures that the statement must create. The one-pass memory allocation is insufficient for optimal execution and therefore forces an extra pass over the data. The multipass memory allocation is even smaller and means that several passes over the data will be needed. As an example, consider a sort operation. The ideal situation is that all the rows to be sorted can be read into the PGA and sorted there. The memory required for this is the optimal memory allocation. If the optimal memory allocation is not available, then the rows must be separated into batches. Each batch will be read into memory, sorted, and written out to disk. This results in a set of sorted batches on disk, which must then be read back into memory and merged into a final sorted list of all the rows. The PGA memory needed for this is the one-pass allocation: the sort operation has had to become multiple sorts followed by a merge. If the one-pass memory allocation is not available, then the merge phase as well as the sort phase will require use of temporary disk storage. This is a multipass execution. EXAM TIP A statement’s shared SQL area is in the shared pool of the SGA; its private SQL area is in the session’s PGA. The ideal situation is that all SQL statements should execute optimally, but this goal may be impossible to reach. In data warehouse operations, the optimal memory allocation can be many gigabytes if the queries are addressing vast tables. In such environments, one-pass executions may be the best that can be achieved. Multipass executions should be avoided if at all possible. For example, to sort 10GB of data may require something over 10GB of memory to run optimally, but only 40MB to run with one pass. Only if less than 40MB is available will the sort become multipass, and execution times will then increase substantially. Managing PGA memory can be automatic, and Oracle Corporation strongly recommends that it should be. The older manual management techniques are supported only for backward compatibility and will not be discussed here. To implement automatic PGA memory management, you set a target for the total PGA memory allocation, summed up for all sessions. The Oracle instance will then pass out memory from this total to sessions on demand. When a session has finished executing its statement, the PGA it was using can be allocated to another session. This system relies on the fact that at any one moment only some of the connected sessions will need any negotiable PGA memory. They will all need a certain amount of PGA memory to retain the state of the session even when the session is idle, but this will

ch25.indd 3

6/26/2009 2:17:51 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

4 leave enough from the total so that those sessions actually running statements can have what they need. At least, that is what one hopes. TIP It is sometimes impossible to achieve optimal memory allocations, because the memory requirements can be huge. One-pass executions are bad but may be unavoidable. Multipass executions are disastrous, and if these are occurring, you should talk to the system administrators about available hardware and to the programmers about tuning their SQL. Automatic PGA memory management is enabled with two instance parameters: • WORKAREA_SIZE_POLICY • PGA_AGGREGATE_TARGET The WORKAREA_SIZE_POLICY will default to AUTO, meaning that Oracle can assign PGA to sessions on demand, while attempting to keep the total allocated PGA within the PGA_AGGREGATE_TARGET. This parameter defaults to the greater of 10MB or 20 percent of the size of the SGA and should be adjusted upward until a satisfactory proportion of statements are executing optimally, but not set so high that memory is over-allocated and the operating system has to page virtual memory to disk. TIP For many systems, the default for PGA_AGGREGATE_TARGET will be far too low for optimal performance.

SGA Memory Management The SGA contains several memory structures, which can be sized independently. These are • The shared pool • The database buffer cache • The large pool • The Streams pool • The Java pool • The log buffer As a general rule, the memory allocation to the large pool, the Java pool, and the Streams pool is not a matter for negotiation—either the memory is needed or it isn’t. If these structures are undersized, there will be errors; if they are oversized, there will be no performance improvement. The memory allocation to the shared pool, the database buffer cache, and the log buffer is negotiable: if less than optimal, there will not be errors but performance will degrade. The exception is the shared pool: if this is chronically undersized, there will be errors.

ch25.indd 4

6/26/2009 2:17:51 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

5 TIP Do not throw memory at Oracle unnecessarily. An oversized shared pool or log buffer is seriously bad for performance. An oversized buffer cache is less likely to be a problem, unless it is so oversized that the system is having to swap.

• SHARED_POOL_SIZE • DB_CACHE_SIZE

PART III

SGA memory management can be automatic (and Oracle Corporation advises that it should be) with the exception of the log buffer. The DBA sets a total size for the SGA, and the instance will apportion this total to the various structures, ensuring that there are no errors from undersizing of SGA components and that memory above this minimum is allocated where it will do the most good. The components will be resized on demand, so that if a component needs more memory, it will be taken from a component that can spare it. The log buffer is the one SGA component whose size is fixed at instance startup and that cannot be automatically managed. The parameters for manual management of the SGA are

• LARGE_POOL_SIZE • STREAMS_POOL_SIZE • JAVA_POOL_SIZE To enable automatic SGA management, leave all of these on default (or set to zero) and set one parameter to enable automatic shared memory management (ASMM): • SGA_TARGET When using ASMM, the instance will monitor demand for memory in the various SGA components and pass out memory to the components as required, downsizing components if this is necessary to keep the total allocated memory within the target. Also included within the target is the log buffer. This is sized with the LOG_BUFFER parameter, which is static: the log buffer is created at instance startup and cannot be resized subsequently. TIP The default for LOG_BUFFER is probably correct.You can set the parameter to higher than default, but this will often cause a degradation in performance. If you set it to less than the default, your setting will often be ignored. EXAM TIP The log buffer is the only SGA structure that cannot be adjusted dynamically. It cannot therefore be automatically managed. If you set any of the parameters that control the automatically managed SGA components, the value given will act as minimum size below which ASMM will never reduce that component.

ch25.indd 5

6/26/2009 2:17:52 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

6 Automatic Memory Management The Automatic Memory Management mechanism lets the Oracle instance manage server memory usage as a whole, by setting one parameter: MEMORY_TARGET. This takes the automatic PGA management (enabled with PGA_AGGREGATE_TARGET) and the automatic shared memory management (enabled with SGA_TARGET) a step further, by letting Oracle transfer memory between PGAs and SGA on demand. TIP To enable automatic memory management, set the one parameter MEMORY_TARGET, and do not set any of the other parameters listed previously, with the exception of LOG_BUFFER—though this too can often be left on default. Automatic memory management is not just a tool to make database administration easy. It will often give big performance benefits as well. Many databases will experience different patterns of activity at different times, which could benefit from different memory configurations. For example, it is not uncommon for a database used for order processing to experience a very transaction processing workload during most of the month, and then a heavy query processing workload during month-end reporting runs. Transaction processing will typically not be demanding on PGA memory but will require a large database buffer cache. Query processing will often require large PGA allocations, but not much buffer cache. EXAM TIP The MEMORY_TARGET parameter is dynamic—it can be adjusted without shutting down the instance—but only within a limit set by another parameter: MEMORY_MAX_TARGET. This is static, so it can only be raised by adjusting with the SCOPE=SPFILE clause and restarting the instance. Manually transferring memory between SGA and PGA in response to changing patterns of activity is not a practical option, and many systems will not be able to allocate enough memory to both concurrently to satisfy their peak demands. Automatic memory management is able to transfer memory between SGA and PGA as necessary to optimize performance within an overall memory constraint. This overall constraint must be determined by the DBA and the system administrator together. There is little point in the DBA setting an upper limit that is so large that the operating system has to page SGA and PGA to a swap device; the system administrator will be able to advise on a suitable maximum value. EXAM TIP If you set the parameters PGA_AGGREGATE_TARGET or SGA_ TARGET when AMM is enabled, the values you specify will be a minimum size beneath which AMM will never reduce the PGA or SGA. Exercise 25-1: Set the Memory Management Parameters In this exercise, you will disable Automatic Memory Management (if it is enabled) and set the SGA and PGA targets independently. Make all the changes using syntax that will

ch25.indd 6

6/26/2009 2:17:52 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

7 only affect the running instance: do not propagate the changes to the spfile, unless you are prepared to reverse them later. 1. Connect to your database with SQL*Plus as user SYSTEM. 2. Ensure that none of the parameters for managing the dynamic SGA memory structures manually are set: alter alter alter alter

system system system system

set set set set

db_cache_size=0 scope=memory; shared_pool_size=0 scope=memory; large_pool_size=0 scope=memory; java_pool_size=0 scope=memory;

3. Disable Automatic Memory Management: alter system set memory_target=0 scope=memory;

alter system set pga_aggregate_target=10m scope=memory; alter system set sga_target=64m scope=memory;

PART III

4. Set the parameters to size PGA and SGA independently, using the lowest permitted values:

The second command may take a few minutes to complete, and it may fail if Oracle cannot reduce the SGA to the requested value. 5. Determine the actual size of the currently allocated PGAs, by summing up the value for the statistic “session pga memory” across all sessions: select sum(value) from v$sesstat natural join v$statname where name='session pga memory';

The figure will be significantly in excess of the 10MB requested in Step 4. This is because 10MB is a value that is so low that Oracle cannot keep to it. The PGA target is only a target, not a hard limit. 6. Determine the actual size of the SGA: select sum(bytes) from v$sgastat;

This figure too may be greater than that requested in Step 4.

The Memory Advisors The Oracle instance collects a vast amount of information regarding activity and performance. These statistics enable the memory advisors. These are tools that will calculate the effect of varying the sizes of the SGA and PGA memory structures. The Automatic Memory Management facility uses the advisors to make decisions about memory allocation, and they are also visible to the DBA through various views and through Enterprise Manager. Figure 25-1 shows three queries that display memory advisor information. The first query in Figure 25-1 shows the PGA advisor. The third selected column shows an estimate for the amount of disk I/O that would be needed if the PGA target were set to the figure shown in the first column. The second column expresses this figure as a proportion of the actual setting. The fifth row of the output is the current setting: a PGA_TARGET_FACTOR of 1. It can be seen that if another 30MB of memory

ch25.indd 7

6/26/2009 2:17:53 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

8

Figure 25-1 The memory advisors, queried with SQL*Plus

were added to the target, less I/O would be needed but that adding more than this would give no further benefit. The second query in Figure 25-1 shows the SGA advisor. This relates the size of the SGA to a projected value for DB_TIME. DB_TIME is an overall figure for the amount of time taken by the database to execute SQL; minimizing DB_TIME is the overall objective of all tuning. It can be seen that if the SGA were raised from its current value of 196MB to 294MB, DB_TIME would reduce but that there would be no point in going further. The third query is against the memory target advisor, which gives advice on the total (SGA plus PGA) memory allocation. This shows that the optimal value is 450MB, as opposed to the current value of 300MB. If using automatic Memory Management (enabled with the MEMORY_TARGET parameter), then this last query is all that need be used. It can be seen that virtually all of the DB_TIME saving could be achieved by raising the target to 375MB, and if the system administrators say sufficient memory is not available to allocate the optimal amount, then this is what the DBA should ask for.

ch25.indd 8

6/26/2009 2:17:54 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

9 EXAM TIP The advisors will not be enabled unless the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. Exercise 25-2: Use the Memory Advisors In this exercise, you will gather advice about memory allocation using the advisors through Database Control. 1. Connect to your database as user SYS with Database control. 2. From the database home page, click the Advisor Central link in the Related Links section. Then on the Advisors tab, take the Memory Advisors link in the Advisors section.

4. Click ADVICE to see a graphical representation of a query against the V$SHARED_ POOL_ADVICE view. The following illustration is an example, which shows that the optimal SGA would be nearly 200MB.

PART III

3. On the SGA tab, in the Current Allocation section you will the current SGA memory usage, which will be the same total as that returned for the query in Exercise 25-1, Step 4, and the breakdown into the individual components.

5. On the PGA tab, you will see the target (set to 10MB) and the actual allocated PGA, which will be higher. The ADVICE button will show a graphical representation of a query against V$PGA_TARGET_ADVICE.

ch25.indd 9

6/26/2009 2:17:54 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

10 6. Enable Automatic Memory Management by clicking the ENABLE button at the top of the Memory Advisors window. This will display the value of MEMORY_ MAX_TARGET, along with a possible memory size, and suggest a value for MEMORY_TARGET. In the following illustration, these values are 300MB and 166MB.

7. Leave the memory parameters on default, and click OK. 8. The memory advisor will now be available: click ADVICE to see the suggested value for the target. 9. Return the instance to the original memory configuration (as it was before Exercise 25-1) by shutting it down and restarting.

The SQL Tuning Advisor The SQL Tuning Advisor analyzes one or more SQL statements, 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.

ch25.indd 10

6/26/2009 2:17:54 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

11 The Capabilities of the SQL Tuning Advisor Whether the SQL Tuning Advisor runs automatically or you run it 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, creating materialized views, and partitioning.

From the preceding list, you can configure the Automatic SQL Tuning task to implement automatically 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. When profiling a SQL statement, the optimizer partially runs the statement, experimenting with various execution plans. The execution statistics generated during this process update the profile. Information on how the statement actually ran can be used by the optimizer subsequently when the statement is encountered during normal database operation. Note that the 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. The SQL Tuning Advisor can use a number of sources for its analysis:

PART III

• Structure Analysis Restructure the SQL statements to see if better execution plans are generated.

• The SQL statements currently cached in the library cache of the shared pool • A precreated set of statements • Statements retrieved from the AWR • An individual ad hoc statement There is a graphical interface to the SQL Tuning Advisor, and also a set of PL/SQL APIs.

Using the SQL Tuning Advisor with Enterprise Manager You can set the SQL Tuning Advisor options in Enterprise Manager. From the database home page, take the Advisor Central link in the Related Links section. Under the Advisors heading, click the SQL Advisors link. The SQL Advisors window shown in

ch25.indd 11

6/26/2009 2:17:54 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

12

Figure 25-2 The SQL Advisors window

Figure 25-2 gives a choice between the SQL Access Advisor (described later in this chapter, the SQL Repair Advisor, and the SQL Tuning Advisor (described here), with a description of their capabilities. Next, click the SQL Tuning Advisor link. On the page in Figure 25-3, 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. In this case, unless you have any existing SQL Tuning sets, you cannot submit this job yet. Therefore, you can navigate to one of the pages using the links in the Overview section of the page in Figure 25-3: Top Activity (statements from the library cache), Historical SQL (statements from an AWR snapshot), or SQL Tuning Sets (created previously). They can provide data sources that can be used to create a SQL Tuning

ch25.indd 12

6/26/2009 2:17:54 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

13

PART III

Figure 25-3 Schedule SQL tuning task in Database Control

Set as input to the SQL Tuning Advisor. When you click the Top Activity link in Figure 25-3, you see the Top Activity page in Figure 25-4. At the bottom of the page, you see the Top SQL activity for the selected time period, which in this example is from about 1:14 P.M. to 1:19 P.M. The top SQL includes the execution of a PL/SQL package, an UPDATE statement, and an INSERT statement. Clicking the Select All link adds all three of these statements to a SQL

ch25.indd 13

6/26/2009 2:17:54 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

14

Figure 25-4 The Database Control Top Activity window

Tuning Set that you can use on the SQL Tuning Advisor page in Figure 25-3. After clicking the Select All link, click the GO button next to the Schedule SQL Tuning Advisor action in the drop-down menu. You will see the page in Figure 25-5 with a SQL Tuning Set created from the SQL statements you selected on the previous page. In Figure 25-5, you schedule a comprehensive analysis of the selected SQL statements. You also want to run the job immediately. Clicking SUBMIT submits the job for processing. Figure 25-6 shows the job in progress. After the job submitted in Figure 25-5 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 25-7.

ch25.indd 14

6/26/2009 2:17:54 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

15

PART III

Figure 25-5 Schedule SQL Tuning Advisor with SQL tuning set

Figure 25-6 SQL Tuning Advisor job progress

ch25.indd 15

6/26/2009 2:17:55 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

16

Figure 25-7 Advisor Central task results

Clicking 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 in Figure 25-8. 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 implement the SQL Profile recommendation by clicking IMPLEMENT ALL PROFILES.

ch25.indd 16

6/26/2009 2:17:55 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

17

PART III

Figure 25-8 SQL Tuning Advisor recommendations summary

Selecting the radio button for the fourth SQL statement in Figure 25-8, you can see a detailed explanation of all recommendations for this SQL statement in Figure 25-9. In Figure 25-9, 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.

ch25.indd 17

6/26/2009 2:17:55 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

18

Figure 25-9 SQL Tuning Advisor detailed recommendations

The SQL Tuning Advisor API: the DBMS_SQLTUNE Package 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 Tuning Set. • EXECUTE_TUNING_TASK TUNING_TASK. • REPORT_TUNING_TASK SQL Tuning Advisor.

Create a tuning task for a SQL statement or a SQL Execute a tuning task created with CREATE_ Show the results and recommendations from the

In addition, you can use the following data dictionary views to query the name and status of tuning jobs: • DBA_ADVISOR_LOG

ch25.indd 18

Task names, status, and execution statistics for all tasks

6/26/2009 2:17:55 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

19 • 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 25-3: Run the SQL Tuning Advisor for a SQL Statement In this exercise, you will use DBMS_SQLTUNE to generate recommendations for one of the SQL statements in Figure 25-8. 1. Connect to the database with SQL*Plus as user SYSTEM, and create the table to be used in this exercise: 2. These commands, executed at the SQL prompt, will create a variable to store the name of the task, create a task to tune one statement, and then run the task: variable vtask varchar2(100); execute :vtask := dbms_sqltune.create_tuning_task(sql_text=>'select distinct object_id from object_analysis'); execute dbms_sqltune.execute_tuning_task(:vtask);

PART III

create table object_analysis as select * from all_objects;

3. Retrieve the recommendations from the tuning task, first setting up SQL*Plus to display them: set long 10000 set longchunksize 10000 select dbms_sqltune.report_tuning_task(:vtask) from dual;

The illustration shows Steps 2 and 3.

4. Study the output of the tuning task, as retrieved in Step 3. Following the detail of the task, there will be a recommendation to gather statistics on the table, and an example of a procedure call to do this. 5. Tidy up: drop table object_analysis;

ch25.indd 19

6/26/2009 2:17:56 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

20

The SQL Access Advisor The SQL Access Advisor performs an analysis of overall SQL performance using a workload specification, concentrating on the segment structures. The workload specification can be one of the following: • • • •

A single SQL statement A SQL statement tuning set Current SQL cache contents A hypothetical workload imputed from the DDL of a set of objects

Recommendations from the SQL Access Advisor include new indexes, materialized views, and partitioning. There is a graphical interface through Enterprise Manager, and a PL/SQL API.

Using the SQL Access Advisor with Database Control The four steps to create a set of recommendations are as follows: 1. 2. 3. 4.

Create a task. Define the workload. Generate the recommendations. Review and implement the recommendations.

From the SQL Advisors window in Figure 25-2, click the SQL Access Advisor link. You will see the page shown in Figure 25-10, where you can perform one of two tasks: verify that existing structures such as indexes and materialized views are being used, or recommend new structures.

Figure 25-10 SQL Access Advisor options

ch25.indd 20

6/26/2009 2:17:56 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

21

PART III

For this example, you want to find new access structures, so you select the second radio button. If you select the Inherit Options check box, you can choose a template that may fit your environment, such as OLTP or data warehousing. When you click CONTINUE, you will see the first step of the wizard as in Figure 25-11. For the source of the tuning activity, you can select one of three sources: recent SQL from the cache, an existing SQL Tuning set (such as the SQL Tuning set created in the SQL Tuning Advisor example earlier in this chapter), or a generated workload based on the type of queries that appear likely given the structure of the objects in one or more schemas. In this example, we want to analyze all current and recent SQL activity. Therefore click the corresponding radio button and then click NEXT. The next page, in Figure 25-12, lets you select which types of access structures that the SQL Access Advisor should recommend: indexes, materialized views, and 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 specify alternate locations for recommended indexes and materialized views. Select the Indexes and Materialized Views check boxes and select the Comprehensive radio button. Finally, click NEXT. The next page, in Figure 25-13, 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 25-11 Specify SQL Access Advisor workload source

ch25.indd 21

6/26/2009 2:17:56 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

22

Figure 25-12 Specifying SQL Access Advisor recommendation options

Figure 25-13 Specifying SQL Access Advisor scheduling options

ch25.indd 22

6/26/2009 2:17:56 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

23

PART III

In Figure 25-13, accept the default options and click NEXT to proceed to Step 4 of the wizard, as you can see in Figure 25-14. 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 25-14. This will show the API calls being generated by the wizard. This is a useful source of code for scripted tuning jobs that can be incorporated into a batch job, which includes other SQL commands or processes that you cannot easily perform repeatedly within EM. Click the SUBMIT button shown in Figure 25-14 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 VIEW RESULT. The page in Figure 25-15 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.

Using the SQL Access Advisor with DBMS_ADVISOR Using the SQL Access Advisor via the DBMS_ADVISOR package can get quite complex. Using Enterprise Manager is an easier way to run most day-to-day analyses. There is, however, one procedure designed to make the job easy. DBMS_ADVISOR.QUICK_TUNE is straightforward and takes as input a single SQL statement to tune. As a result, it

Figure 25-14 Reviewing SQL Access Advisor options

ch25.indd 23

6/26/2009 2:17:56 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

24

Figure 25-15 SQL Access Advisor recommendation summary

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. The procedure requires (as a minimum) three arguments: the name of the advisor, the name of the task to be run, and the statement. For example: SQL> execute dbms_advisor.quick_tune(3 dbms_advisor.sqlaccess_advisor,4 'task1',5 'select distinct object_id from object_analysis'6 );7 / PL/SQL procedure successfully completed. SQL>

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

ch25.indd 24

6/26/2009 2:17:56 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

25 generated by the QUICK_TUNE procedure. First, create a directory object to point to a file system directory to hold the script: SQL> create directory tune_scripts as '/u06/tune_scripts'; Directory created. SQL>

Next, use CREATE_FILE to create the script containing the implementation recommendations:

PART III

SQL> begin 2 dbms_advisor.create_file 3 (dbms_advisor.get_task_script('task1'), 4 'TUNE_SCRIPTS', 5 'tune_fts.sql' 6 ); 7 end; 8 / PL/SQL procedure successfully completed. SQL>

In this example, the file tune_fts.sql looks like this: Rem Rem Rem Rem Rem Rem

SQL Access Advisor: Version 11.1.0.6.0 – Production Username: Task: Execution date:

JON task1

CREATE MATERIALIZED VIEW LOG ON "JON"."OBJECT_ANALYSIS" WITH ROWID, SEQUENCE("OBJECT_ID") INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW "JON"."MV$$_0BDC0000" REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT JON.OBJECT_ANALYSIS.OBJECT_ID C1, COUNT(*) M1 FROM JON.OBJECT_ANALYSIS GROUP BY JON.OBJECT_ANALYSIS.OBJECT_ID; begin dbms_stats.gather_table_stats ('"JON"','"MV$$_0BDC0000"',NULL,dbms_stats.auto_sample_size); end; /

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.

ch25.indd 25

6/26/2009 2:17:57 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

26

Identifying and Fixing Invalid and Unusable Objects First, the terminology. Ideally, all objects have a status of valid. PL/SQL objects, and views can become invalid; indexes can become unusable. Depending on the reason for an object becoming invalid, it may become valid automatically when next accessed. Unusable indexes must be made valid by rebuilding, which does not occur automatically.

Invalid Objects The code of named PL/SQL objects is stored within the data dictionary. These can be • Procedures • Functions • Triggers • Packages • Object types Most, if not all, of these procedural objects will refer to data objects, such as tables. When a procedural object is compiled, the compiler checks the data objects to which it refers in order to confirm that the definition is correct for the code. For example, if the code refers to a column, the column must exist or the code will not compile. If any of the data objects to which a procedural object refers changes after the procedural object has been compiled, then the procedure will be marked INVALID. Procedural objects may also be invalid for more mundane reasons: perhaps the programmer made a simple syntactical mistake. In that case, the object will be created INVALID and will be useless. The same situation can occur with views. When created, they may be fine, but they may be invalidated if the detail tables on which they are based have their definitions changed. EXAM TIP Oracle will always attempt to recompile invalid PL/SQL objects and views automatically, but this may not succeed.You do not have to do it manually—though it may be advisable to do so. Objects can be created invalid because of programmer error, or they can become invalid some time after creation. The view DBA_OBJECTS (and the derived views ALL_OBJECTS and USER_OBJECTS) has a column, STATUS, which should ideally always be VALID. To identify all invalid objects in the database, as user SYSTEM run the query select owner,object_name,object_type from dba_objects where status='INVALID';

If any objects are listed by this query, the first question to ask is whether the object was ever valid. It may never have worked and not be needed, in which case the best thing

ch25.indd 26

6/26/2009 2:17:57 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

27 to do may be to drop it. But if, as is likely, you do not know if the object was ever valid, then a sensible first step is to attempt to compile it. The first time an invalid object is accessed, Oracle will attempt to compile it automatically—but if the compilation fails, the user will receive an error. Clearly, it is better for the DBA to compile it first; then, if there is an error, they can try to fix it before a user notices. Even if the object does compile when it is accessed, there may be a delay while the compilation takes place; it is better for perceived performance if this delay is avoided by proactive DBA work. To repair invalid objects, first attempt to compile them. The syntax is ALTER object_type object_name COMPILE ;

For example, the statement SQL> alter procedure hr.add_reg compile;

SQL> alter view rname compile;

PART III

will attempt to compile the procedure ADD_REG in the HR schema, and the statement

will compile the view RNAME in your current schema. If the compilation succeeds, you have no further problems. If it fails, then you need to work out why. If a procedure does not compile, use the SQL*Plus command SHOW ERRORS to see why not (unfortunately, SHOW ERRORS is not supported for views). Often a useful starting point in identifying the cause of compilation errors is to use the DBA_DEPENDENCIES view, described here: SQL> desc dba_dependencies; Name --------------------------------OWNER NAME TYPE REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE REFERENCED_LINK_NAME DEPENDENCY_TYPE

Null? -------NOT NULL NOT NULL

Type ------------VARCHAR2(30) VARCHAR2(30) VARCHAR2(17) VARCHAR2(30) VARCHAR2(64) VARCHAR2(17) VARCHAR2(128) VARCHAR2(4)

For every object, identified by OWNER and NAME, there will be rows for each object on which it depends. For example, if a view retrieves columns from a dozen tables, they will each be listed as a REFERENCED_NAME. If a view does not compile, then investigating these tables would be sensible. There will be occasions when you are faced with the need to recompile hundreds or thousands of invalid objects. Typically, this occurs after an upgrade to an application, or perhaps after applying patches. Rather than recompiling them individually, use the supplied utility script. On Unix, SQL> @?/rdbms/admin/utlrp

or on Windows, SQL> @?\rdbms\admin\utlrp

ch25.indd 27

6/26/2009 2:17:57 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

28 This script, which should be run when connected AS SYSDBA, will attempt to compile all invalid objects. If after running it there are still some invalid objects, you can assume that they have problems that should be addressed individually.

Unusable Indexes If a procedural object, such as a stored PL/SQL function or a view, becomes invalid, the DBA does not necessarily have to do anything: the first time it is accessed, Oracle will attempt to recompile it—and this may well succeed. But if an index becomes unusable for any reason, it must always be repaired explicitly before it can be used. An index consists of the index key values, sorted into order, each with the relevant rowid. The rowid is the physical pointer to the location of the row to which the index key refers. If the rowids of the table are changed, then the index will be marked as unusable. This could occur for a number of reasons. Perhaps the most common is that the table has been moved, with the ALTER TABLE . . . MOVE command. This will change the physical placement of all the rows, and therefore the index entries will be pointing to the wrong place. Oracle will be aware of this and will therefore not permit use of the index. In earlier releases of the Oracle database, it was more than likely that users would detect unusable indexes because their sessions would return errors. When executing SQL statements, if the session attempted to use an unusable index, it would immediately return an error, and the statement would fail. From release 10g and higher the database changes this behavior. If a statement attempts to use an unusable index, the statement will revert to an execution plan that does not require the index. Thus, statements will always succeed—but perhaps at greatly reduced performance. This behavior is controlled by the instance parameter SKIP_UNUSABLE_INDEXES, which defaults to TRUE. The exception to this is if the index is necessary to enforce a constraint: if the index on a primary key column becomes unusable, the table will be locked for DML. TIP If you wish your database to react as earlier releases, where unusable indexes would cause errors, issue the command ALTER SYSTEM SET SKIP_UNUSABLE_INDEXES=FALSE;. To detect indexes that have become unusable, query the DBA_INDEXES view: SQL> select owner, index_name from dba_indexes where status='UNUSABLE';

Indexes are marked unusable if the rowid pointers are no longer correct. To repair the index, it must be recreated with the ALTER INDEX . . . REBUILD command. This will make a pass through the table, generating a new index with correct rowid pointers for each index key. When the new index is completed, the original, unusable, index is dropped.

ch25.indd 28

6/26/2009 2:17:57 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

29 TIP While an index rebuild is in progress, additional storage space is required; plan ahead to make sure it is available.

EXAM TIP NOLOGGING disables redo generation only for the index rebuild; all subsequent DML against the index will generate redo as normal. Unless ONLINE is specified, the table will be locked for DML while the rebuild is going on.

PART III

The syntax of the REBUILD command has several options. The more important ones are TABLESPACE, ONLINE, and NOLOGGING. By default, the index will be rebuilt within its current tablespace, but by specifying a tablespace with the TABLESPACE keyword, it can be moved to a different one. Also by default, during the course of the rebuild the table will be locked for DML. This can be avoided by using the ONLINE keyword. The NOLOGGING keyword instructs Oracle not to generate redo for the index rebuild operation. This will make the rebuild proceed much faster, but it does mean that the tablespace containing the index should be backed up immediately. Until the tablespace is backed up, the index will not survive media damage requiring use of restore and recovery.

TIP Rebuilding indexes may also be necessary as part of normal database administration. Indexes become inefficient with time—particularly if there are many deletions, or updates that affect the key values of rows. Exercise 25-4: Repair Invalid Objects and Unusable Indexes In this exercise, you will create some objects, break them, and repair them. 1. Using SQL*Plus, connect to your database as user SYSTEM. 2. Create a table to be used for the exercise: create table valid_t as select * from all_users;

3. Create some objects dependent on this table: create create create insert end; /

index valid_i on valid_t (username); view valid_v as select * from valid_t; procedure valid_p as begin into valid_t values ('name',99,sysdate);

4. Confirm the status of the objects: select object_name,object_type,status from user_objects where object_name like 'VALID%'; select status from user_indexes where index_name ='VALID_I';

ch25.indd 29

6/26/2009 2:17:58 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

30 They will all have the STATUS of VALID, and the index will be USABLE. Steps 2, 3, and 4 are shown here:

5. Perform DDL command on the table that will break the objects: alter table valid_t drop column created; alter table valid_t move;

6. Rerun the queries from Step 4. Note that the procedure and the view are now INVALID, and the index is UNUSABLE. 7. Attempt to use the invalid objects: execute valid_p; select * from valid_v;

8. Attempt to correct the errors: alter view valid_v compile; alter procedure valid_p compile; alter index valid_i rebuild online nologging;

The compilations will fail, because the table has been changed, but the index will rebuild: it was never INVALID, merely UNUSABLE because the table move had changed all the rowids. 9. Correct the errors by adding the column back to the table: alter table valid_t add (created date);

10. Repeat Step 7. The statements will succeed.

ch25.indd 30

6/26/2009 2:17:58 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

31 11. Rerun the queries in Step 4. All the objects are fine now, because the attempts to use them forced an automatic recompilation. 12. Tidy up by dropping the objects: drop table valid_t; drop procedure valid_p; drop view valid_v;

Database Replay

PART III

Changes occur in your software and hardware environment, whether it be upgrades to operating system, database, or application 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, before the change is implemented. 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

ch25.indd 31

6/26/2009 2:17:58 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

32 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 relative chronological order 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 events • 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. If possible, start the workload capture when there are no active sessions. Any ongoing transactions will be captured but may not play back correctly because only part of each ongoing transaction will be played back in the target system. You can begin the capture process using the START_CAPTURE procedure in the PL/SQL package DBMS_WORKLOAD_CAPTURE. This requires a minimum of two arguments: a name for the capture, and an Oracle directory to which to save the capture. For example, SQL> execute dbms_workload_capture.start_capture('Data Warehouse Migration','REP_CAP_DIR');

Then run the application as normal. All the workload will be captured, until you terminate the capture: SQL> execute dbms_workload_capture.finish_capture;

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

ch25.indd 32

6/26/2009 2:17:58 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

33 After moving the capture files to a location accessible to the replay system (which could be the original database), use the DBMS_WORKLOAD_REPLAY.PROCESS_ CAPTURE procedure, which requires a single argument: the Oracle directory where the capture files exist. For example, SQL> execute dbms_workload_replay.process_capture('REP_CAP_DIR');

Launch the Replay

PART III

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, “speed up” or “slow down” one or more client requests, depending on the requirements of the new environment. Database Replay uses one or more replay clients to recreate 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. EXAM TIP One replay client can replay the statements from many sessions.

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.

TIP Failing to remap all references to the production system could be disastrous, as your replay exercise might then update the production database. By default, the order of all COMMIT statements is 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 preservation of the COMMIT order to run the replay faster. To launch a replay, in the target database run two procedures to prepare the database: execute dbms_workload_replay.initialize_replay('replay 1','REP_CAP_DIR'); execute dbms_workload_replay.prepare_replay;

The INITIALIZE_REPLAY procedure requires two arguments (the name of the operation, and the directory containing the data) and puts the database into a state

ch25.indd 33

6/26/2009 2:17:58 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

34 where a replay can be launched. The PREPARE_REPLAY procedure has optional arguments that will control how quickly the replay will run. Having completed the preparatory work, the replay clients must now be started. These are independent operating system processes, launched by running an executable file in the $ORACLE_HOME/bin directory. This will launch a replay client. The basic syntax is wrc /

Finally, the replay can be started: SQL> execute dbms_workload_replay.start_replay;

This last procedure call will allow the connected clients to start running the statements in the captured workload.

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: declare capture_dir_id number; curr_replay_id number; replay_report clob; begin /* retrieve pointer to all captured sessions */ /* in the replay directory */ capture_dir_id := dbms_workload_replay.get_replay_info(dir => 'REP_CAP_DIR'); /* get latest replay session id */ select max(id) into curr_replay_id from dba_workload_replays where capture_id = capture_dir_id; /* generate the report */ replay_report := dbms_workload_replay.report (replay_id => curr_replay_id, format => dbms_workload_replay.type_text); end;

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

ch25.indd 34

6/26/2009 2:17:58 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

35 ensures that you retrieve the latest report. You can use DELETE_REPLAY_INFO to delete a report in the replay directory. Exercise 25-5: Use Database Replay In this exercise, you will generate and capture a workload, and replay it after adding an index. The report will show improved performance. 1. Using SQL*Plus, connect to your database as user SYSTEM. Create a user, a table, and a directory (substitute a path suitable for your environment) to be used for this exercise:

2. Start the capture: exec dbms_workload_capture.start_capture('play capture','PLAY_D');

PART III

grant dba to player identified by player; create table player.t1 as select * from all_objects; create directory play_d as '/home/oracle/play_d'; grant all on directory play_d to public;

The illustration shows Steps 1 and 2.

3. Connect as the new user, and run a PL/SQL block that will generate a workload on the database: connect player / player declare m number; begin for i in 1..1000 loop select count(1) into m from t1 where object_id=1; end loop; end;

ch25.indd 35

6/26/2009 2:17:58 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

36 This illustration shows Step 3.

4. Connect as SYSTEM. Terminate the capture, and process it: execute dbms_workload_capture.finish_capture; execute dbms_workload_replay.process_capture('PLAY_D');

5. Replay the workload. Note that when launching the replay client, you must specify the physical path, not the Oracle directory: exec dbms_workload_replay.initialize_replay('play capture','PLAY_D'); exec dbms_workload_replay.prepare_replay;

In a separate window, launch the client from an operating system prompt: wrc player/player replaydir=/home/oracle/play_d

In the SQL*Plus session, start the replay: exec dbms_workload_replay.start_replay;

The illustrations show this step.

ch25.indd 36

6/26/2009 2:17:59 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

37

select id,capture_id,directory,start_time,end_time from dba_workload_replays;

PART III

6. To retrieve the replay report, you must first obtain its ID number:

Set the SQL*Plus buffer size, and retrieve the report: set long 100000 select dbms_workload_replay.report(,'TEXT') from dual;

Study the report, and note the values of the DB Time for the Replay and the Capture: they will be similar. 7. Connected as PLAYER, add a primary key constraint to the table: alter table t1 add constraint t1_pk primary key (object_id);

8. Repeat Steps 5 and 6. You will observe a significant drop in the DB Time value, demonstrating the improvement in performance that follows from defining the constraint. 9. Tidy up: drop user player cascade;

Two-Minute Drill Use Automatic Memory Management • Automatic Shared Memory Management can be enabled with the parameter SGA_TARGET. • Automatic PGA Management can be enabled with the parameter PGA_ AGGREGATE_TARGET. • Automatic Memory Management can be enabled with the parameter MEMORY_TARGET.

ch25.indd 37

6/26/2009 2:17:59 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

38 Use Memory Advisors • There are advisors for PGA, SGA, and total memory usage. • The advisors can be accessed by querying dynamic performance views, or through Enterprise Manager.

Troubleshoot Invalid and Unusable Objects • Procedural objects will become invalid if the objects on which they depend are changed. • Indexes will become unusable if their table is moved. • Oracle will attempt to recompile invalid procedural object automatically. • Unusable indexes must be rebuilt manually. • Oracle will not attempt to use an unusable index, by default, so there will be no errors.

Implement Automatic Memory Management • You configure AMM by setting the parameter MEMORY_TARGET. • MEMORY_MAX_TARGET is an upper bound for MEMORY_TARGET. The former is not a dynamic parameter. • When MEMORY_TARGET is set and both SGA_TARGET and PGA_AGGREGATE_ TARGET are set, then SGA_TARGET and PGA_AGGREGATE_TARGET are used as minimum values.

Manually Configure SGA Parameters • You can set MEMORY_TARGET to zero and SGA_TARGET to a nonzero value to exercise more control over SGA memory. • Adjustments to automatically tuned SGA parameters are saved across instance restarts. • ASMM uses the MMAN background process to coordinate changes in memory size. • The five auto-tuned ASMM initialization parameters are SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, DB_CACHE_SIZE, and STREAMS_ POOL_SIZE. • The manually tuned ASMM initialization parameters are DB_KEEP_CACHE_ SIZE, DB_RECYCLE_CACHE_SIZE, DB_nK_CACHE_SIZE, and LOG_BUFFER. • You can easily disable ASMM by setting SGA_TARGET to zero, but then the current auto-tuned ASMM initialization parameters are set to their current values. • The static parameter SGA_MAX_SIZE is the upper limit for the value of SGA_ TARGET

ch25.indd 38

6/26/2009 2:17:59 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

39 Configure Automatic PGA Memory Management • The parameter PGA_AGGREGATE_TARGET sets an upper limit for memory used by all server and background processes and enables auto-tuning of PGA memory. • PGA memory areas include private SQL areas, named cursors, work areas for sorting operations, and session-specific memory variables.

Use the SQL Tuning Advisor • The SQL Tuning Advisor performs statistics analysis, SQL Profile analysis, access path analysis, and structure analysis. • The 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.

PART III

• The SQL Tuning Advisor can automatically implement SQL Profiles.

Use the SQL Access Advisor to Tune a Workload • 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 the SQL Access Advisor include new indexes, materialized views, and partitioning. • The four steps composing 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.

Understand Database Replay • 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. • 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.

ch25.indd 39

6/26/2009 2:17:59 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

40

Self Test 1. Where are private SQL areas stored? (Choose the best answer.) A. In each session’s PGA, always B. In each session’s PGA, unless a PGA Aggregate Target has been set C. In the PGA, unless Automatic Memory Management has been enabled D. In the shared pool of the SGA, always 2. Which memory structure is fixed in size at instance startup? (Choose the best answer.) A. The shared pool B. The large pool C. The Java pool D. The log buffer E. None are fixed, if Automatic Memory Management has been enabled 3. When Automatic Memory Management is enabled, what is not possible? (Choose the best answer.) A. Transfer of memory between sessions’ PGAs B. Transfer of memory between structures within the SGA C. Transfer of memory from SGA to PGA, and vice versa D. Increasing the total memory usage after instance startup E. All of the above are possible 4. Storage of what structures can exist in the PGA? (Choose all correct answers.) A. Shared SQL areas B. Private SQL areas C. Global temporary tables D. Sort areas E. Bitmap merge areas F. Cached object definitions 5. Which instance parameter can disable the memory advisors? (Choose the best answer.) A. DB_CACHE_ADVICE B. MEMORY_TARGET C. STATISTICS_LEVEL D. TIMED_STATISTICS

ch25.indd 40

6/26/2009 2:17:59 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

41 6. Which of these parameters cannot be changed without an instance restart? (Choose all correct answers.) A. MEMORY_MAX_TARGET B. MEMORY_TARGET C. PGA_AGGREGATE_TARGET D. SGA_TARGET 7. If you create a table and a procedure that refers to it, and then change the definition of the table, what will happen when you try to run the procedure? (Choose the best answer.) A. The procedure will recompile automatically and run successfully. C. The procedure will run with reduced performance until you analyze the table. D. The procedure may or may not compile, depending on the nature of the change.

PART III

B. The procedure will fail until you recompile it.

8. If a SELECT statement attempts to use an UNUSABLE index, what will happen? (Choose the best answer.) A. The statement will fail. B. The statement will succeed, but at reduced performance. C. The index will be rebuilt automatically if possible. D. It depends on the SKIP_UNUSABLE_INDEXES parameter. 9. You determine that an index is unusable, and decide to rebuild it. Which of the following statements, if any, are correct? (Choose all correct answers.) A. The NOLOGGING and ONLINE keywords cannot be used together when rebuilding the index. B. A rebuild may require double the disk space while it is in progress. C. If you do not use the ONLINE keyword during a rebuild, the table will be unavailable for SELECT and DML statements. D. The NOLOGGING keyword applied to a rebuild means that DML against the index will not generate redo. 10. If a primary key index becomes unusable, what will the effect be upon an application that uses it? (Choose the best answer.) A. SELECT will succeed, but perhaps at reduced performance. B. DML commands will succeed, but perhaps at reduced performance. C. The primary key constraint can no longer be enforced. D. The table may be locked for DML.

ch25.indd 41

6/26/2009 2:17:59 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

42 11. Identify the true statement about Automatic Memory Management (AMM). (Choose the best answer.) A. MEMORY_TARGET and MEMORY_MAX_TARGET must both be set to enable AMM. B. MEMORY_TARGET enables AMM, and it is a static parameter. C. MEMORY_MAX_TARGET enables AMM, and it is a static parameter. D. MEMORY_TARGET enables AMM, and it is a dynamic parameter. 12. 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 13. 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 14. 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 15. 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 16. Which of the following changes can the SQL Access Advisor recommend? (Choose two answers.)

ch25.indd 42

6/26/2009 2:17:59 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

43 A. Restructuring one or more SQL statements B. Gathering statistics for selected SQL statements C. Adding a materialized view log D. Enabling query rewrite 17. 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

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

PART III

E. The SQL Access Advisor requires a workload, AWR snapshot, or STS and cannot analyze a single SQL statement

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 19. 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 1. ý

A. Private SQL areas are private to each session, in the session’s PGA.

þ B, C, and D. B is wrong because automatic PGA management is not relevant to where the private SQL area is stored, only to how it is managed. C and D are wrong because private SQL areas are always in the PGA. 2. ý

D. The log buffer cannot be changed after startup.

þ A, B, C, and E. A, B, and C are wrong because all these structures can be resized. E is wrong because not even Automatic Memory Management makes the log buffer resizable.

ch25.indd 43

6/26/2009 2:18:00 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

44 3. ý E. Memory can be transferred between all structures (except the log buffer), and the total can be increased. þ A, B, C, and D. These are wrong because all are possible—though D, the increase of total memory usage, is only possible up to the value specified by the MEMORY_MAX_TARGET parameter. 4. ý B, C, D, and E. These are all PGA memory structures, though they may spill to a temporary segment in the users’ temporary tablespace. þ A and F. These are wrong because these structures both exist in the shared pool of the SGA. 5. ý C. STATISTICS_LEVEL must be on TYPICAL or FULL, or the advisors will not run. þ A, D, and B. A and D are wrong, because these parameters (which still exist only for backward compatibility) are controlled by STATISTICS_LEVEL. B is wrong because MEMORY_TARGET determines whether implementing the advice is automatic or manual. 6. ý A. MEMORY_MAX_TARGET is a static parameter: it cannot be changed without restarting the instance. þ B, C, and D. All these parameters are dynamic, meaning that they can be changed without restarting the instance. 7. ý

D. Oracle will attempt recompilation, but this may not succeed.

þ A, B, and C. A is wrong because this will not necessarily succeed if the nature of the change is such that the procedure needs to be rewritten. B is wrong because manual recompilation is not necessary (though it may be a good idea). C is wrong because it refers to object statistics, which are not relevant to a problem of this kind. 8. ý D. The SKIP_UNUSABLE_INDEXES parameter will control whether the statement produces an error or reverts to an alternative plan. þ A, B, and C. A and B are both wrong because they make an assumption about the SKIP_UNUSABLE_INDEXES parameter: A assumes it is FALSE; B assumes it is true. C is wrong because indexes are never rebuilt automatically. 9. ý B. A rebuild requires additional space, as the new index is built before the original index is dropped. þ A, C, and D. A is wrong because NOLOGGING and ONLINE can be used together. C is wrong because without ONLINE the index is locked for DML, but not for SELECT. D is wrong because DML always generates redo—it is only the DDL that will not generate redo. 10. ý D. Loss of a primary key index means that DML against the constrained column(s) will be impossible.

ch25.indd 44

6/26/2009 2:18:00 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

Chapter 25: Performance Tuning

45 þ A, B, and C. A is wrong because this is not certain—it is dependent on the SKIP_UNUSABLE_INDEXES setting. B is wrong because DML commands will fail if they affect the constrained column(s). C is wrong because the constraint will be enforced—by locking the table. 11. ý D. MEMORY_TARGET enables AMM; it is a dynamic parameter and cannot be more than MEMORY_MAX_TARGET. þ A, B, and C. A is wrong because the MEMORY_MAX_TARGET can be left on default. B is wrong because MEMORY_TARGET is dynamic. C is wrong because while MEMORY_MAX_TARGET provides a limit for AMM, it does not enable it. 12. ý D. Only the SQL Access Advisor recommends changes to materialized views, including creating materialized view logs.

13. ý A, B, and E. The 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.

PART III

þ A, B, C, and E. The SQL Tuning Advisor performs statistics analysis, SQL Profiling, access paths, and structure analysis.

þ C and D. 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. 14. ý B. DBMS_SQLTUNE.EXECUTE_TUNING_TASK runs a SQL Tuning Advisor task created with DBMS_SQLTUNE.CREATE_TUNING_TASK. þ A, C, and D. These are not valid packages or procedures. 15. ý 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, the SQL Access Advisor also uses statistics to analyze overall SQL performance. þ C. C is wrong because you cannot use the captured Database Replay information as a source for the SQL Access Advisor. 16. ý C and D. The SQL Access Advisor recommends materialized views, materialized view logs, and enabling query rewrite. In addition, the SQL Access Advisor will also recommend new indexes or partitions. þ A and B. The SQL Tuning Advisor recommends SQL statement restructuring and statistics gathering, not the SQL Access Advisor. 17. ý 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, C, and E. A, B, and C are wrong because these procedures do not exist. E is wrong because the SQL Access Advisor can run an analysis on a single SQL statement, just as SQL Tuning Advisor can.

ch25.indd 45

6/26/2009 2:18:00 PM

All-in-1 / OCA/OCP Oracle Database 11g All-in-One / Watson, Ramklass / 162-918-1

OCA/OCP Oracle Database 11g All-in-One Exam Guide

46 18. ý A. The database links, external tables, directory objects, and connection string remappings need to occur during the workload replay step immediately before replay is initiated. þ B, C, D, and E. 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. 19. ý 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 they may cause data divergence during replay. Thus, Oracle recommends restarting the instance before initiating capture. þ A and C. 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.

ch25.indd 46

6/26/2009 2:18:00 PM