8.4. Understanding the Advisory Framework

The final component of the CMI is the advisory framework. If you're anything like me, when you hear the term "advisor," you may be picturing an animated paper clip popping up whenever you open SQL*Plus and dispensing wisdom such as, "It looks like you're connecting to a database." But don't worry, no irritating paper clips are going to appear (or dogs or guys named Bob).

Instead, the advisors are a collection of services that aid in the optimization of different aspects of the Oracle 10g database. Each advisor covers a specific subsystem within the database, and can analyze, diagnose, and provide recommendations to eliminate performance issues in the database.

This section takes a quick look at some of the different advisors that are available. We'll then discuss the common interface shared by the advisors. Next, you'll learn about ADDM and see how it proactively monitors the database on your behalf. Lastly, we'll investigate the SQL Tuning Advisor and see how it automates the SQL Tuning process.

8.4.1. An Overview of the Advisors

The Advisory Framework offers a consistent, uniform interface across all advisors allowing them to interact with each other as needed. This framework is enhanced by the use of a common repository (the AWR).

There are a number of advisors available, some of which will be described in detail later in this chapter. The following is a brief overview of the different advisors and a description of their functionality.

8.4.1.1. Automatic Database Diagnostic Monitor (ADDM)

ADDM represents the pinnacle of automatic database performance tuning. Without any human interaction, it proactively identifies performance issues and bottlenecks within the database. In addition, ADDM works with other advisors to offer recommendations on how to resolve the problems detected.

8.4.1.2. The Segment Advisor

The Segment Advisor analyzes space fragmentation within segments and identifies the segments that are good candidates for the new online shrink operation. To put that in simpler terms, Oracle finally has online reorganization capability.

In past versions, to repair fragmentation generally meant dropping and re-creating objects or using a command such as ALTER TABLE MOVE (which is essentially a drop and a re-create). With the Segment Advisor, you are notified of fragmented segments and can reclaim the wasted space using the online shrink operation. There is also an option to reset the high water mark, or to leave it as before.

The Segment Advisor also reports on segment growth trends, to aid in capacity planning and resource monitoring.

8.4.1.3. The Undo Advisor

The Undo Advisor helps determine appropriate sizing for Undo tablespaces and helps determine optimal UNDO_RETENTION settings. This aids in the prevention of Snapshot too old errors. It also assists in setting the proper low threshold value of the Undo retention period for use with the flashback query feature.

The Undo Advisor assists in identifying problems relating to the Undo tablespace and offers advice to help correct those problems.

8.4.1.4. The SQL Tuning Advisor

The SQL Tuning Advisor is used to analyze individual SQL statements and provides recommendations to increase performance. It can be run against SQL identified as problematic by ADDM, against the most resource-intensive SQL (top SQL) from the AWR, or against any user-defined SQL.

The SQL Tuning Advisor is covered in greater detail later in this chapter.

8.4.1.5. The SQL Access Advisor

The SQL Access Advisor is used to analyze a SQL workload (which can consist of one or more SQL statements) and recommend appropriate access structures to improve the performance of the workload. These access structures include, but are not limited to, materialized views and indexes.

8.4.1.6. The Memory Advisor

The Memory Advisor helps you to tune the size of the different Oracle memory structures. The Memory Advisor acts as a sort of supervising advisor with responsibility for the overall instance. Within the realm of the Memory Advisor, the following advisors are available to help optimize individual areas of memory:

• SGA Advisor

• PGA Advisor

• Buffer Cache Advisor

• Library Cache Advisor

Each of these subadvisors aids you in determining the optimum size for its associated Oracle memory structure. They also help to identify and resolve problems relating to the structure.

In keeping with the theme of the CMI, the various advisors all expose a common, uniform interface. This shortens the learning curve and also enables seamless integration across advisors.

This uniform interface also allows the advisors to invoke each other as needed. For instance, the SQL Tuning Advisor may need to invoke the SQL Access Advisor and utilize its services directly.

In addition, the advisors all share a common repository: the AWR.

8.4.1.7. Invoking Advisors

There are several distinct contexts in which the advisors may be invoked. The first is through Oracle Enterprise Manager (EM) Database Control. EM Database Control offers graphical wizards that will guide you through all the steps required to utilize an advisor.

To access the advisors through EM Database Control, navigate to the Advisor Central home page. A link can be found on the EM Database Control home page, under Related Links, as shown in Figure 8.5.

An example of advisor usage through EM Database Control is provided later in this chapter.

The second method advisors can be invoked is through the MMON process. As MMON monitors the system, it may trigger an advisor in response to its findings. When invoked in this manner, the advisors may execute in a limited capacity.

The third method is invocation by a Server Alert recommendation. The alert may suggest that a specific advisor be utilized in order to resolve the alert situation.

The fourth method is through the use of the DBMS_ADVISOR package.

In addition to the common invocation methods, the advisors also share common attributes, such as those listed in Table 8.13.

Table 8.13. Common Attributes of Advisors

Attribute	Description
Mode of operation	You can choose to run the advisor in either LIMITED or COMPREHENSIVE mode. As the names imply, LIMITED executes a fairly shallow analysis, while COMPREHENSIVE utilizes more statistics and system resources to perform a more intensive analysis.
Time limit	The maximum run time for the advisor. This attribute is useful to limit COMPREHENSIVE analysis from running for too long.
Interruptible	Specifies whether an advisor will return partial results in the event of an interruption.
User directives	Specifies whether an advisor will accept user directives.

While not all advisors share all of these attributes, they are common across most of them.

8.4.1.8. DBMS_ADVISOR Package

New to Oracle 10g is the DBMS_ADVISOR package. This package represents an API to execute advisor procedures. DBMS_ADVISOR can be used to access all advisors, as it contains all necessary constants and procedure declarations. Table 8.14 shows the procedures available in the DBMS_ADVISOR package.

Table 8.14. DBMS_ADVISOR Procedures

Procedure	Description
CREATE_TASK	Add a new task in the repository.
DELETE_TASK	Remove a task from the repository.
EXECUTE_TASK	Execute a task.
INTERRUPT_TASK	Suspend a running task.
CREATE_TASK_REPORT	Generate a recommendations report.
RESUME_TASK	Resume execution of a suspended task.
UPDATE_TASK_ATTRIBUTES	Update the attributes of a task.
SET_TASK_PARAMETER	Set or modify parameters for a task.
MARK_RECOMMENDATION	Accept, reject, or ignore one or more recommendations.
CREATE_TASK_SCRIPT	Create a SQL script of all accepted recommendations.

To highlight the usage of the DBMS_ADVISOR package, let's look at the high-level steps that would occur during a typical tuning session:

8.4.1.9. Advisor Views

A set of advisor views is also available to view information related to the advisors. Again, the ADVISOR privilege is required to access these views. Table 8.15 lists the advisor views.

Table 8.15. Advisor Views

View	Description
DBA_ADVISOR_DEFINITIONS	Advisor properties
DBA_ADVISOR_TASKS	Global information about tasks
DBA_ADVISOR_LOG	Current status tasks
DBA_ADVISOR_PARAMETERS	Task parameters
DBA_ADVISOR_COMMANDS	Advisor commands and associated actions
DBA_ADVISOR_OBJECTS	Objects referenced by tasks
DBA_ADVISOR_FINDINGS	Advisor findings
DBA_ADVISOR_RECOMMENDATIONS	Advisor recommendations
DBA_ADVISOR_ACTIONS	Actions associated to the recommendations
DBA_ADVISOR_RATIONALE	Reasons for the recommendations
DBA_ADVISOR_USAGE	Usage for each advisor

Though the data available through these views is accurate, Oracle still recommends the use of Enterprise Manager screens or the use of the CREATE_TASK_REPORT procedure when assessing this information.

8.4.2. Automatic Database Diagnostic Monitor (ADDM)

The Automatic Database Diagnostic Monitor (ADDM) represents another key component in Oracle's Common Manageability Infrastructure. ADDM provides automated proactive tuning of an Oracle 10g instance.

As you have already seen, Oracle 10g gathers an enormous amount of statistics and gathers them on a very frequent basis. Some of these statistics are self-explanatory and easy to understand. Others, however, are quite cryptic and confusing.

For anyone to understand the meaning of all those statistics is a tall order in itself. But to match ADDM, you would also have to analyze those statistics, derive the root cause of any performance problems found, produce a report encapsulating the findings, write the SQL to resolve the problem, and repeat this process every hour.

ADDM provides all of the following benefits:

• Automatic performance diagnostic report produced hourly (default setting)

• Problem diagnosis based on decades of Oracle tuning expertise

• Time-based quantification of problem impacts and recommendation benefits

• Identification of the root causes of problems, not just symptoms

• Recommendations for resolving identified problems

• Identification of non-problem areas of the system

• Minimal overhead to the system during the diagnostic process

8.4.2.1. ADDM Analysis

ADDM is automatically invoked by the MMON process after each AWR snapshot is performed. By default, this occurs every hour. Its analysis is based on the two most recent snapshots to pro-actively identify any performance issues.

The analysis performed by ADDM compares the most current snapshot to the one prior. It identifies system bottlenecks as well as non-problem areas of the system and identifies the root cause of problems. This is vital, as we all know the frustration in troubleshooting an issue, only to find that it was only a symptom of a totally different problem.

8.4.2.2. DB_TIME

ADDM relies heavily on Oracle's new time model statistics. Each component in Oracle has its own statistics set that capture the information relevant to that component. But, to perform analysis on the system as a whole, there is a need for a common scale of comparison that can be applied to all components. That scale is time, and the DB_TIMEstatistic is used to accomplish this.

The DB_TIME statistic is the most important of the time model statistics. DB_TIME captures total time spent in database calls for all components. DB_TIME represents an aggregation of CPU and non-idle wait event time. It is collected cumulatively from instance startup time.

Because DB_TIME is common across database components, the goal of tuning can be simplified to "reducing DB_TIME." And because you are dealing with a common scale, the job of analysis becomes simpler and recommendations easier to quantify. The example in this section illustrates why.

In the past, aggregated statistics were used to make generalized recommendations regarding database performance improvements. Often these recommendations didn't address the real cause of the performance issue. Sometimes they were flat out wrong.

For example, a company upgrades to faster CPUs expecting to see a big increase in performance. If CPU usage wasn't the root cause of their performance issues, they may see no improvement at all.

In contrast, the time model statistics will show exactly where Oracle is spending its time. And, when taken as a percentage of the total time collected, the percentage of potential improvement can be seen immediately. Refer to the example shown in Table 8.16.

In this simplified example, let's assume that these five fictitious statistics (A, B, C, D, and E) represent a particular workload on your system. The DB_TIME column shows the amount of time (in seconds) that each of them used during the workload. By totaling their combined time, you can arrive at a total time of 15,000 seconds.

Table 8.16. Time Model Statistics Example

Statistic	DB_TIME (in seconds)	Total DB_TIME	Percentage of Total DB_TIME
A	10,000	15,000	66%
B	50	15,000	<1%
C	50	15,000	<1%
D	900	15,000	6%
E	4,000	15,000	26%

Then, calculate the percentage of the total time each statistic accounted for. Now you can see a clear picture of where Oracle is spending its time and where to focus your tuning efforts. For example, tuning to decrease DB_TIME for statistics A and E will yield the greatest results. Tuning for statistics B and C would be a waste of time. Even if DB_TIME for those statistics was reduced to 0, you'd see less than a 1 percent improvement. Remember that reducing DB_TIME means that Oracle would be able to support more user requests using the same resources (in other words, increased throughput).

Time model statistics at the session level can be viewed using the V$SESS_TIME_MODEL view. For time model statistics at the system level, use the V$SYS_TIME_MODEL view.

8.4.2.3. Wait Event Changes

ADDM also benefits greatly from the changes made in the Wait Event model in Oracle 10g.The first of these changes is the enhanced granularity of the statistics.

In previous versions of Oracle Database, tools such as Statspack attempted to provide diagnostic tuning services similar to those provided by ADDM. However, they fell short of their goals due to the quality of the statistics available to them. Many important statistics were either absent or were aggregated with others and therefore useless for extracting detail. This lack of granularity made it exceedingly difficult to make accurate determinations for many performance problems.

Oracle 10g, on the other hand, dramatically increased the granularity of the statistics collected. For example, numerous statistics related to locks and latches were aggregated into catchall statistics in Oracle 9i. In 10g, these are separated into many individual statistics. This granularity exposes a whole new level of detail never before available and makes accurate diagnosis much more likely.

The second change to the Wait Event model is the classification of wait events into high-level classes for ease of identification. Every wait event is now classified into one of the following classes:

• Application

• Administration

• Commit

• Concurrency

• Network

• User I/O

• System I/O

• Configuration

By maintaining wait event granularity but assigning wait event classes, Oracle provides the best of both worlds. Statistics can be analyzed at the class level or at the detail level, depending on the need.

8.4.2.4. ADDM Findings

When ADDM analysis is complete, ADDM will return a set of findings, recommendations, and rationales.

Findings represent the performance issues uncovered during analysis. These findings will be classified as a symptom or as a root cause, to aid in the resolution process.

Recommendations are suggestions to aid in resolving the bottleneck. These may include specifics (for example, increase the size of the Java Shared Pool) or recommendations to utilize another advisor (for example, the SQL Tuning Advisor) that can target specific types of performance issues.

Along with the recommendations, ADDM shows the benefit (performance improvement) that will result when the bottleneck is removed.

Finally, the rationale behind the recommendation is reported, to explain the reason for the recommendation.

8.4.2.5. Accessing ADDM through EM Database Control

Though ADDM can be accessed using the DBMS_ADVISOR package, Oracle recommends using EM Database Control to access ADDM. EM Database Control offers a graphical interface to ADDM (and the other advisors as well).

ADDM can be accessed from the Advisor Central page by clicking the ADDM link under the Advisors heading. This link opens the Create ADDM Task page, where new tasks can be defined.

The Advisor Central page also displays the results from recent advisor activity. If any ADDM tasks appear in this section, clicking the name of the task (or selecting the task and then clicking the View Result button) allows you to view the results of a previous ADDM task. Both options can be seen in Figure 8.6.

8.4.3. SQL Tuning Advisor

One of the greatest challenges in database administration has always been SQL statement tuning. Regardless of how well tuned your database may be, poorly written SQL can still bring it to its knees. Even properly written SQL can have a huge detrimental effect on Oracle performance if the query optimizer chooses a poor execution path. However, the introduction of automatic SQL tuning functionality to Oracle 10g promises to make SQL tuning headaches a thing of the past.

This section takes a look at the Automatic Tuning Optimizer (ATO) and the types of analysis it performs on SQL statements. Next, you'll learn how to interface with the ATO through the SQL Tuning Advisor. You'll step through the SQL Tuning Advisor using both the PL/SQL API and through EM Database Control.

8.4.3.1. Automatic Tuning Optimizer (ATO)

Automatic SQL tuning functionality has been designed into the new query optimizer that totally replaces the need for manual SQL tuning. The key to this new functionality lies in the query optimizer's choice of tuning modes.

In its normal mode, the optimizer accepts a SQL statement and generates a reasonable execution plan based on the available statistics. For the most part, it does a very good job and completes in a fraction of a second. The normal mode places a strong emphasis on returning an execution plan quickly, sometimes to the detriment of the actual execution plan itself.

When placed in tuning mode, the query optimizer is referred to as the Automatic Tuning Optimizer (ATO), and its emphasis is on generating a superior execution plan. When the ATO parses a query, it performs a more thorough analysis in an attempt to improve on the execution plan.

This intensive analysis can require multiple minutes to tune a single query and is therefore recommended for complex, high-load statements that have a measurable impact on system performance.

The output from the ATO is not, however, an execution plan. Instead, it produces a group of actions, along with their expected impact, and the rationale behind each.

8.4.3.2. Automatic SQL Tuning

The Automatic SQL Tuning functionality analyzes SQL using four distinct methods:

Statistics Analysis Statistics Analysis is essential to the tuning of any SQL statement, so if essential statistics are missing or stale, the optimizer will be unable to generate an accurate execution plan. Instead, it will return a recommendation to gather the relevant statistics. It will also return additional information in the form of a SQL Profile.

SQL Profiling SQL Profiles contain auxiliary statistics specific to a single query that aid the optimizer in generating an optimal execution path. Through the use of sampling, partial execution, and execution history, a profile is built that will be stored in the data dictionary. When this query is executed in the future, the optimizer will include the profile in its analysis when generating an execution plan. The additional data provided by the profile helps the optimizer to generate a well-tuned plan.

Don't confuse SQL Profiles with Stored Outlines. A Stored Outline effectivelyfreezes a particular execution plan for a query, regardless of changes made to the underlying objects. A SQL Profile can change as underlying objects change, and the data in a SQL Profile remains relevant through data distribution changes. Over a long period of time, though, a SQL Profile may indeed become outdated and require regeneration by simply running Automatic Statistics Tuning on the same SQL statement.

SQL profiles can be applied to SELECT, INSERT, UPDATE, DELETE, CREATE TABLE AS SELECT, and MERGE statements.

Access Path Analysis Access Path Analysis examines whether the addition of one or more new indexes would significantly increase performance for the query. If it identifies a promising candidate, it will return a recommendation for the creation of this new index (or indexes).

Because the addition of new indexes can also have a profound impact on other queries, the optimizer will also return a recommendation to run the SQL Access Advisor (along with a representative workload) to determine the impact of adding the new index.

SQL Structure Analysis SQL Structure Analysis looks at the syntax, semantics, and design of a query and identifies common problems associated with each. It then returns suggestions to restructure the query to achieve superior performance.

For example, consider the following query:

SQL> select distinct client_id from clients;

The inclusion of the DISTINCT clause forces Oracle to perform a sort operation to de-duplicate the result set. Through SQL Structure Analysis, the ATO can determine that CLIENT_ID is the primary key for the CLIENTS table, which ensures that no duplicate values can exist. Therefore, the query will perform exactly the same if the DISTINCT clause were removed, as shown:

SQL> select client_id from clients;

The exclusion of the DISTINCT clause allows Oracle to skip the sort operation, resulting in improved query performance.

8.4.3.3. Using the SQL Tuning Advisor

The interface to the ATO is provided by the SQL Tuning Advisor. The SQL Tuning Advisor accepts one or more SQL statements and invokes the ATO to tune them on your behalf. It then returns the advice, recommendations, rationale, and so on in the same consistent manner as the other advisors.

This section discusses the input, options, and output from the SQL Tuning Advisor.

8.4.3.3.1. SQL Tuning Advisor Input

Input into the SQL Tuning Advisor can come from multiple sources. First and foremost, ADDM identifies high-load SQL each time it executes. For each high-load SQL statement that it finds, it returns a recommendation to run the SQL Tuning Advisor. If you accept the recommendation, the SQL will be passed into the SQL Tuning Advisor for analysis.

High-load SQL can also be manually identified through the AWR views or through the Cursor Cache (for recent SQL not yet in the AWR). These statements can also be processed by the SQL Tuning Advisor.

A SQL Tuning Set (STS) allows a group of SQL statements to be passed into the SQL Tuning Advisor. SQL Tuning Sets are database objects that store information regarding a set of SQL statements (usually representing a specific workload, though not necessarily).

A SQL Tuning Set includes the following information:

• The SQL text for multiple SQL statements

• Execution context information such as schema and application information, bind variable values, and cursor compilation environment

SQL Tuning Sets are covered later in this chapter.

8.4.3.3.2. SQL Tuning Advisor Options

The scope and duration of a SQL Tuning Advisor task can be set to one of two options: limited or comprehensive.

Limited Performs statistics analysis, access path analysis, and SQL structure analysis, and returns recommendations. No SQL Profile recommendations are produced.

Comprehensive Performs all functions included in the Limited mode, but also performs full SQL Profile analysis. Also allows user-specified time limit for the tuning task, because Comprehensive analysis can be very time-consuming.

8.4.3.3.3. SQL Tuning Advisor Output

The SQL Tuning Advisor produces the following output:

• Recommendations on optimizing the execution plan

• Rationale to support the recommendations

• Estimated performance gain

• Script to implement the recommendations

The only action required on your part is to accept or reject the recommendations. If accepted, the recommendations will be implemented automatically.

8.4.3.4. DBMS_SQLTUNE Package

The DBMS_SQLTUNE package exposes the API to directly access the SQL Tuning Advisor. This package allows tuning tasks to be created and executed. It also allows for the processing of the recommendations returned from tuning tasks. This section focuses on the use of the DBMS_5QLTUNE package.

8.4.3.4.1. Creating a Task

Creating a task is the first step in using the SQL Tuning Advisor. The task defines the SQL text, scope, and duration of the tuning effort, among other things. The CREATE_TUNING_TASK procedure provides the interface to define a new task. Table 8.17 describes the interface for the CREATE_TUNING_TASK procedure.

Table 8.17. DBMS_SQLTUNE.CREATE_TUNING_TASK Procedure

Argument	Type	In/Out	Default
SQL_TEXT	CLOB	In
BIND_LIST	SQL_BINDS	In	Default
USER_NAME	VARCHAR2	In	Default
SCOPE	VARCHAR2	In	Default
TIME_LIMIT	NUMBER	In	Default
TASK_NAME	VARCHAR2	In	Default
DESCRIPTION	VARCHAR2	In	Default

Because DBMS_SQLTUNE requires the use of CLOB and SQL_BINDS datatypes, you will use a PL/SQL procedure to call the procedure, as shown here:

DECLARE
  task_name  varchar2(30);
sql_stmt     clob;
BEGIN
  sql_stmt := 'select /*+ full(a) use_hash(a) ' ||
' parallel(a,8) full(b) use_hash(b) parallel(b,8) ' ||
' */ a.type, sum(a.amt_paid) ' ||
' from large_table a, large_table2 b ' ||
' where a.key = b.key ' ||
' and state_id = :bnd';

task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sql_text => sql_stmt,
  bind_list => sql_binds (anydata.ConvertNumber(32));
  user_name => 'BUTERTB',
  scope    => 'COMPREHENSIVE',
  time_limit => 45,
  task_name => 'large_table_task',
  description => 'Tune state totals query'),

dbms_output.put_line('Task ' || task_name ||
has been created.'),
END;
/

The CREATE_TUNING_TASK procedure returns the name of the task (or a system-generated name if one is not defined) when the task is created.

8.4.3.4.2. Executing a Task

Once a tuning task has been created, it can be executed through the EXECUTE_TUNING_TASK procedure:

BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNIN6_TASK( task_name => 'large_table_task'),
END;

The status of the executing task can be monitored by querying the DBA_ADVISOR_LOG view or V$SESSION_LONGOPS:

Select status
From dba_advisor_log
Where task_name = 'large_table_task';

If you want to interrupt a tuning task that is currently executing, use the INTERRUPT_TUNING_TASK procedure:

BEGIN
  DBMS_SQLTUNE.INTERRUPT_TUNING_TASK( task_name => 'large_table_task'),
END;

The following functions and procedures are also available to manage tuning tasks. As their names are self-explanatory, examples and descriptions are not provided.

CANCEL_TUNING_TASK

RESET_TUNING_TASK

DROP_TUNING_TASK

8.4.3.4.3. Task Results

Results from the tuning task can be reported by using the REPORT_TUNING_TASK function. This function produces a report showing all findings and recommendations as well as the rationale, benefits, and SQL to implement the recommendations. This example shows the usage of the REPORT_TUNING_TASK function:

set long 1000
set longchunksize 1000

set linesize 132
select dbms_sqltune.report_tuning_task('large_table_task') from dual;

In the interest of space, the output from this report is not shown.

8.4.3.4.4. Managing SQL Profiles

If the SQL Tuning Advisor returns a recommended SQL Profile, the DBMS_SQLTUNE package can be utilized to accept it. It can also be used to alter or drop SQL Profiles.

8.4.3.4.5. Accepting a SQL Profile

To accept a recommended SQL Profile, use the ACCEPT_SQL_PROFILE procedure. Executing this procedure results in the creation and storage of a new SQL Profile in the database. An example is shown here:

DECLARE
  sqlprofile_name" varchar2(30);
BEGIN
sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
task_name => 'large_table_task',
profile_name => 'large_table_profile'),
END;

8.4.3.4.6. Altering a SQL Profile

Attributes of SQL Profiles can be altered using the ALTER_SQL_PROFILE procedure. For example, if you want to enable the STATUS attribute (to ensure that the SQL Profile will be used during SQL parsing), you could use the following code:

BEGIN
  DBMS_SQLTUNE.ALTER_SQL_PROFILE(
    Name => 'large_table_profile',
    Attribute_name => 'STATUS',
    Value => 'ENABLED'),
END;

8.4.3.4.7. Dropping a SQL Profile

In much the same manner, a SQL Profile can be dropped using the DROP_SQL_PROFILE procedure:

BEGIN
  DBMS_SQLTUNE.DROP_SQL_PROFILE(

Name => 'large_table_profile'),
END;

8.4.3.5. Managing SQL Tuning Sets

As discussed earlier in this chapter, a SQL Tuning Set (STS) is a database object that contains a set of SQL statements along with their execution statistics and execution context information. They can optionally include a user priority ranking for each SQL statement in the set.

SQL Tuning Sets can be created on any SQL statements, but often they represent a specific workload, process, or grouping of high-load SQL. By creating sets, multiple SQL statements can be passed into the SQL Tuning Advisor at once.

SQL Tuning Sets can be managed through the use of the DBMS_SQLTUNE package.

8.4.3.5.1. Creating a SQL Tuning Set

The first step in the creation of an STS is to create an empty STS object in the database. This can be accomplished by using the CREATE_SQLSET procedure:

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    Sqlset_name => 'load_proc_set',
    Description => 'SQL used in load procedure'),
END;

8.4.3.5.2. Loading a SQL Tuning Set

The next step is to populate the new STS with SQL statements (and their associated execution statistics) using the LOAD_SQLSET procedure. Common sources that may be used for this step include the AWR, the Cursor Cache, or even another STS. Predefined functions are available to simplify the process of extracting and loading this information.

DBMS_SQLTUNE offers the following procedures to extract the necessary SQL information from the AWR, Cursor Cache, or from another STS:

• SELECT_WORKLOAD_REPOSITORY

• SELECT_CURSOR_CACHE

• SELECT_SQLSET

As of the time of this writing, the SELECT_CURSOR_CACHE procedure remained undocumented by Oracle, but it clearly exists in the DBMS_SQLTUNE package. As with any undocumented feature, proceed with caution.

These procedures extract all necessary SQL information from their respective containers and make the information available for loading into a new STS. To support this functionality, the LOAD_SQLSET procedure utilizes a weakly defined type known as a SQLSET_CURSOR. The 5QLSET_CURSOR type handles the results returned from queries into these non-standard repositories. The following example may clear this up a bit:

DECLARE

   sql_cursor" DBMS_SQLTUNE.SQLSET_CURSOR;
   begin_snap" number := 1; /* beginning snapshot id
   end_snap" number := 5; /* end snapshot id */

BEGIN

   open sql_cursor for
      select value(p)
      from table (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
        
begin_snap, end_snap) p;
DBMS_SQLTUNE.LOAD_SQLSET(
      sqlset_name => 'load_proc_set',
      populate_cursor => sql_cursor);
END;
/

As this example shows, the SQL information is extracted for a range of snapshots stored in the AWR. The data is held in a SQLSET_CURSOR, which is then used as the source to populate the new STS.

8.4.3.6. SQL Tuning Views

Information gathered for SQL tuning tasks can be viewed through a set of SQL Tuning Information views. Some of the most common are listed here:

• SQL Tuning Information

  • DBA_SQLTUNE_STATTSTTCS

  • DBA_SQLTUNE_BINDS

  • DBA_SQLTUNE_PLANS

• SQL Tuning Sets

  • DBA_SQLSET

  • DBA_SQLSET_BINDS

  • DBA_SQLSET_STATEMENTS

  • DBA_SQLSET_REFERENCES

• SQL Profiles

  • DBA_SQL_PROFILES

• Advisor Information

  • DBA_ADVISOR_TASKS

  • DBA_ADVISOR_FINDINGS

  • DBA_ADVISOR_RECOMMENDATIONS

  • DBA_ADVISOR_RATIONALE

• Dynamic Views

  • VSSQL

  • VSSQLAREA

  • V$SQL_BINDS

8.4.3.7. Using SQL Tuning Advisor through EM Database Control

Oracle's recommended interface into the SQL Tuning Advisor is through EM Database Control. EM Database Control offers a graphical interface as well as context-sensitive integration with other Oracle advisors. Context-sensitive means that any EM Database Control pages displaying SQL statements, SQL Tuning Sets, or snapshots will also offer a link to the SQL Tuning Advisor. In fact, the SQL Tuning Advisor is only available through these screens. If you try to access it through the Advisor Central links, you'll see the screen shown in Figure 8.7, which directs you to the Top SQL, SQL Tuning Sets, Snapshots, or Preserved Snapshot Sets screens.

The process of invoking the SQL Tuning Advisor in EM Database Control is similar regardless of which screen it is called from. On any screen where you can specify one or more SQL statements (whether in a snapshot, SQL Tuning Set, AWR report, and so on), you should find a link to submit the SQL to the SQL Tuning Advisor. For this example, we'll use the AWR Snapshots screen.

The AWR Snapshots screen is designed for snapshot management. However, as shown in Figure 8.8, the Action drop-down list also includes the option of creating a SQL Tuning Set from a range of snapshots. Once an STS is created, you can then call the SQL Tuning Advisor. In this example, we select a beginning snapshot, select Create SQL Tuning Set from the Actions drop-down list, and then click Go.

Next, you'll see the Create SQL Tuning Set screen. In this screen, you will select the ending snapshot ID and assign a name and an optional description to the STS. Technically, the name is optional as well. If it is left blank, Oracle will generate a unique name for the set. When finished, click OK to generate the STS. Figure 8.9 shows an example.

You will now be taken to the SQL Tuning Sets screen, which displays a message that the STS was successfully created. As you can see in Figure 8.10, the screen also displays a list of SQL Tuning Sets and offers a button to invoke the SQL Tuning Advisor (as well as the SQL Access Advisor). To invoke the SQL Tuning Advisor, simply select an STS from the list and then click the Run SQL Tuning Advisor button.

You should now find yourself at the Schedule Advisor screen, where you can set options for the execution of the SQL Tuning Advisor. At the top of the screen, a name and description can be assigned to the SQL Tuning Advisor job, though neither is required. If a name is not defined, Oracle will simply assign a unique name to the job.

The next section of the screen lists the SQL statements included in the tuning set as well as the name of the parsing schema. This section is strictly for review, however. You can't select or deselect statements. The entire set will be processed through the SQL Tuning Advisor.

Below the SQL statements, you can define the scope for the job. As discussed earlier in this chapter, the following options are available:

Limited Perform analysis without SQL Profile recommendations. This option averages about one second per SQL statement.

Comprehensive Perform complete analysis, including SQL Profile recommendations.

Total Time Limit If the Comprehensive option is selected, the total execution time for the Schedule Advisor will not exceed the amount of time specified (in minutes). By default, this is set to 30 minutes.

Comprehensive analysis can consume a great deal of time, so Oracle strongly recommends setting the Total Time Limit to a reasonable number. Also, remember that advisor jobs can be interrupted and resumed, or cancelled altogether if necessary.

Finally, the Schedule section of the screen allows you to schedule execution time for the job. By default, the job executes immediately upon submission. More often, however, you may want the job to execute after work hours to minimize its impact on the system. The Schedule option makes this simple.

When all options have been set, clicking OK will submit the job for execution (either immediately or to the scheduler for future execution). Figure 8.11 shows the Schedule Advisor screen.

When the SQL Tuning Advisor has completed the job, you'll be automatically taken to the SQL Tuning Results screen. If the job was executed through the scheduler, you can navigate to the Advisor Central screen and use the Search option to find it. Simply select SQL Tuning Advisor from the Advisory Type drop-down list, select All from the Advisor Runs drop-down list, and then click Go. A list of all SQL Tuning Advisor jobs will appear. Select the one that you want to view and then click the View Results button.

The SQL Tuning Results screen lists all SQL statements included in the analysis, along with a series of check boxes to indicate its findings. The check boxes are labeled Statistics, SQL Profile, Index, Restructure SQL, Miscellaneous, and Error. A check mark in one or more of these columns indicates that the SQL Tuning Advisor has recommendations for the statement, as shown in Figure 8.12.

To view the recommendations, select the statement with a corresponding check mark and click the View Recommendations button. This will take you to the Recommendations screen, where you can see the findings, recommendations, rationale, and estimated benefits produced by the SQL Tuning Advisor for that statement. You can also access the execution plan for the statement from this screen. Figure 8.13 shows an example of the Recommendations screen.