Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) Part Number B14211-01 |
|
|
View PDF |
This chapter illustrates how to use the SQL Access Advisor, which is a tuning tool that provides advice on materialized views, indexes, and materialized view logs. The chapter contains:
Overview of the SQL Access Advisor in the DBMS_ADVISOR Package
Tuning Materialized Views for Fast Refresh and Query Rewrite
Materialized views and indexes are essential when tuning a database to achieve optimum performance for complex, data-intensive queries. The SQL Access Advisor helps you achieve your performance goals by recommending the proper set of materialized views, materialized view logs, and indexes for a given workload. Understanding and using these structures is essential when optimizing SQL as they can result in significant performance improvements in data retrieval. The advantages, however, do not come without a cost. Creation and maintenance of these objects can be time consuming, and space requirements can be significant.
The SQL Access Advisor recommends bitmap, function-based, and B-tree indexes. A bitmap index offers a reduced response time for many types of ad hoc queries and reduced storage requirements compared to other indexing techniques. B-tree indexes are most commonly used in a data warehouse to index unique or near-unique keys.
Another component of the SQL Access Advisor also recommends how to optimize materialized views so that they can be fast refreshable and take advantage of general query rewrite.
The SQL Access Advisor can be run from Oracle Enterprise Manager (accessible from the Advisor Central page) using the SQL Access Advisor Wizard or by invoking the DBMS_ADVISOR
package. The DBMS_ADVISOR
package consists of a collection of analysis and advisory functions and procedures callable from any PL/SQL program. Figure 17-1 illustrates how the SQL Access Advisor recommends materialized views for a given workload obtained from a user-defined table or the SQL cache. If a workload is not provided, it can generate and use a hypothetical workload also.
Figure 17-1 Materialized Views and the SQL Access Advisor
Using the SQL Access Advisor Wizard or API, you can do the following:
Recommend materialized views and indexes based on collected or hypothetical workload information.
Manage workloads.
Mark, update, and remove recommendations.
In addition, you can use the SQL Access Advisor API to do the following:
Perform a quick tune using a single SQL statement.
Show how to make a materialized view fast refreshable.
Show how to change a materialized view so that general query rewrite is possible.
The SQL Access Advisor's recommendations are significantly improved if you gather structural statistics about table and index cardinalities, and the distinct cardinalities of every dimension level column, JOIN
KEY
column, and fact table key column. You do this by gathering either exact or estimated statistics with the DBMS_STATS
package. Because gathering statistics is time-consuming and extreme statistical accuracy is not required, it is generally preferable to estimate statistics. Without these statistics, any queries referencing that table will be marked as invalid in the workload, resulting in no recommendations being made for those queries. It is also recommended that all existing indexes and materialized views have been analyzed. See Oracle Database PL/SQL Packages and Types Reference for more information regarding the DBMS_STATS
package.
One of the easiest ways to use the SQL Access Advisor is to invoke its wizard, which is available in Oracle Enterprise Manager from the Advisor Central page. If you prefer to use SQL Access Advisor through the DBMS_ADVISOR
package, this section describes the basic components and the sequence in which the various procedures must be called.
This section describes the four steps in generating a set of recommendations:
Step 1 Create a task
Before any recommendations can be made, a task must be created. The task is important because it is where all information relating to the recommendation process resides, including the results of the recommendation process. If you use the wizard in Oracle Enterprise Manager or the DBMS_ADVISOR.QUICK_TUNE
procedure, the task is created automatically for you. In all other cases, you must create a task using the DBMS_ADVISOR.CREATE_TASK
procedure.
You can control what a task does by defining parameters for that task using the DBMS_ADVISOR
.SET_TASK_PARAMETER
procedure.
See "Creating Tasks" for more information about creating tasks.
Step 2 Define the workload
The workload is one of the primary inputs for the SQL Access Advisor, and it consists of one or more SQL statements, plus various statistics and attributes that fully describe each statement. If the workload contains all SQL statements from a target business application, the workload is considered a full workload; if the workload contains a subset of SQL statements, it is known as a partial workload. The difference between a full and a partial workload is that in the former case, the SQL Access Advisor may recommend dropping certain existing materialized views and indexes if it finds that they are not being used effectively.
Typically, the SQL Access Advisor uses the workload as the basis for all analysis activities. Although the workload may contain a wide variety of statements, it carefully ranks the entries according to a specific statistic, business importance, or a combination of statistics and business importance. This ranking is critical in that it enables the SQL Access Advisor to process the most important SQL statements ahead of those with less business impact.
For a collection of data to be considered a valid workload, the SQL Access Advisor may require particular attributes to be present. Although analysis can be performed if some of the items are missing, the quality of the recommendations may be greatly diminished. For example, the SQL Access Advisor requires a workload to contain a SQL query and the user who executed the query. All other attributes are optional; however, if the workload also contained I/O and CPU information, then the SQL Access Advisor may be able to better evaluate the current efficiency of the statement. The workload is stored as a separate object, which is created using the DBMS_ADVISOR.CREATE_SQLWKLD
procedure, and can easily be shared among many Advisor tasks. Because the workload is independent, it must be linked to a task using the DBMS_ADVISOR.ADD_SQLWKLD_REF
procedure. Once this link has been established, the workload cannot be deleted or modified until all Advisor tasks have removed their dependency on the workload. A workload reference will be removed when a parent Advisor task is deleted or when the workload reference is manually removed from the Advisor task by the user using the DBMS_ADVISOR.DELETE_SQLWKLD_REF
procedure.
You can use the SQL Access Advisor without a workload, however, for best results, a workload must be provided in the form of a user-supplied table, SQL Tuning Set or imported from the SQL Cache. If a workload is not provided, the SQL Access Advisor can generate and use a hypothetical workload based on the dimensions defined in your schema.
Once the workload is loaded into the repository or at the time the recommendations are generated, a filter can be applied to the workload to restrict what is analyzed. This provides the ability to generate different sets of recommendations based on different workload scenarios.
The recommendation process and customization of the workload are controlled by SQL Access Advisor parameters. These parameters control various aspects of the recommendation process, such as the type of recommendation that is required and the naming conventions for what it recommends. With respect to the workload, parameters control how long the workload exists and what filtering is to be applied to the workload.
To set these parameters, use the SET_TASK_PARAMETER
and SET_SQLWKLD_PARAMETER
procedures. Parameters are persistent in that they remain set for the lifespan of the task or workload object. When a parameter value is set using the SET_TASK_PARAMETER
procedure, it does not change until you make another call to SET_TASK_PARAMETER
.
See "Defining Workload Contents" for more information about workloads.
Step 3 Generate the recommendations
Once a task exists and a workload is linked to the task and the appropriate parameters are set, you can generate recommendations using the DBMS_ADVISOR.EXECUTE_TASK
procedure. These recommendations are stored in the SQL Access Advisor Repository.
The recommendation process generates a number of recommendations and each recommendation will comprise of one or more actions. For example, create a materialized view and then analyze it to gather statistical information.
A task recommendation can range from a simple suggestion to a complex solution that requires implementation of a set of database objects such as indexes, materialized views, and materialized view logs. When an Advisor task is executed, it carefully analyzes collected data and user-adjusted task parameters. The SQL Access Advisor will then attempt to form a resolution based on its built-in knowledge. The resolutions are then refined and stored in the form of a structured recommendation that can be viewed and implemented by the user.
See "Generating Recommendations" for more information about generating recommendations.
Step 4 View and implement the recommendations
There are two ways to view the recommendations from the SQL Access Advisor: using the catalog views or by generating a script using the DBMS_ADVISOR.GET_TASK_SCRIPT
procedure. In Enterprise Manager, the recommendations may be displayed once the SQL Access Advisor process has completed.
See "Viewing Recommendations" for a description of using the catalog views to view the recommendations. See "Generating SQL Scripts" to see how to create a script.
Not all recommendations have to be accepted and you can mark the ones that should be included in the recommendation script.
The final step is then implementing the recommendations and verifying that query performance has improved.
All the information needed and generated by the SQL Access Advisor is held in the Advisor repository, which is a part of the database dictionary. The benefits of using the repository are that it:
Collects a complete workload for the SQL Access Advisor.
Supports historical data.
Is managed by the server.
This section discusses general information about, as well as the steps needed to use, the SQL Access Advisor, and includes:
Figure 17-2 illustrates the steps in using the SQL Access Advisor as well as an overview of all of the parameters in the SQL Access Advisor and when it is appropriate to use them.
You need to have the ADVISOR
privilege to manage or use the SQL Access Advisor. When processing a workload, the SQL Access Advisor attempts to validate each statement in order to identify table and column references. Validation is achieved by processing each statement as if it is being executed by the statement's original user. If that user does not have SELECT
privileges to a particular table, the SQL Access Advisor bypasses the statement referencing the table. This can cause many statements to be excluded from analysis. If the SQL Access Advisor excludes all statements in a workload, the workload is invalid and the SQL Access Advisor returns the following message:
QSM-00774, there are no SQL statements to process for task TASK_NAME
To avoid missing critical workload queries, the current database user must have SELECT
privileges on the tables targeted for materialized view analysis. For those tables, these SELECT
privileges cannot be obtained through a role.
This section discusses the following aspects of setting up tasks and templates:
An Advisor task is where you define what it is you want to analyze and where the results of this analysis should go. A user can create any number of tasks, each with its own specialization. All are based on the same Advisor task model and share the same repository.
You create a task using the CREATE_TASK
procedure. The syntax is as follows:
DBMS_ADVISOR.CREATE_TASK ( advisor_name IN VARCHAR2, task_id OUT NUMBER, task_name IN OUT VARCHAR2, task_desc IN VARCHAR2 := NULL, template IN VARCHAR2 := NULL, is_template IN VARCHAR2 := 'FALSE', how_created IN VARCHAR2 := NULL); DBMS_ADVISOR.CREATE_TASK ( advisor_name IN VARCHAR2, task_name IN VARCHAR2, task_desc IN VARCHAR2 := NULL, template IN VARCHAR2 := NULL, is_template IN VARCHAR2 := 'FALSE', how_created IN VARCHAR2 := NULL);
The following illustrates an example of using this procedure:
VARIABLE task_id NUMBER; VARIABLE task_name VARCHAR2(255); EXECUTE :task_name := 'MYTASK'; EXECUTE DBMS_ADVISOR.CREATE_TASK ('SQL Access Advisor', :task_id, :task_name);
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the CREATE_TASK
and CREATE_SQLWKLD
procedures and their parameters.
When an ideal configuration for a task or workload has been identified, this configuration can be saved as a template upon which future tasks and workloads can be based.
This enables you to set up any number of tasks or workloads that can be used as intelligent starting points or templates for future task creation. By setting up a template, you can save time when performing tuning analysis. It also enables you to custom fit a tuning analysis to the business operation.
To create a task from a template, you specify the template to be used when a new task is created. At that time, the SQL Access Advisor copies the data and parameter settings from the template into the newly created task. You can also set an existing task to be a template by setting the template attribute when creating the task or later using the UPDATE_TASK_ATTRIBUTE
procedure.
To use a task as a template, you tell the SQL Access Advisor to use a task when a new task is created. At that time, the SQL Access Advisor copies the task template's data and parameter settings into the newly created task. You can also set an existing task to be a template by setting the template attribute. This can be done at the command line or in Enterprise Manager.
A workload object can also be used as a template for creating new workload objects. Following the same guidelines for using a task as a template, a workload object can benefit from having a well-defined starting point. Like a task template, a template workload object can only be used to create similar workload objects.
You can create a template as in the following example.
Create a template called MY_TEMPLATE
.
VARIABLE template_id NUMBER; VARIABLE template_name VARCHAR2(255); EXECUTE :template_name := 'MY_TEMPLATE'; EXECUTE DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor',:template_id, - :template_name, is_template => 'TRUE');
Set template parameters. For example, the following sets the naming conventions for recommended indexes and materialized views and the default tablespaces:
-- set naming conventions for recommended indexes/mvs EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'INDEX_NAME_TEMPLATE', 'SH_IDX$$_<SEQ>'); EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'MVIEW_NAME_TEMPLATE', 'SH_MV$$_<SEQ>'); -- set default tablespace for recommended indexes/mvs EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'DEF_INDEX_TABLESPACE', 'SH_INDEXES'); EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'DEF_MVIEW_TABLESPACE', 'SH_MVIEWS');
This template can now be used as a starting point to create a task as follows:
VARIABLE task_id NUMBER; VARIABLE task_name VARCHAR2(255); EXECUTE :task_name := 'MYTASK'; EXECUTE DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', :task_id, - :task_name, template=>'MY_TEMPLATE');
The following example uses a pre-defined template SQLACCESS_WAREHOUSE
. See Table 17-4 for more information.
EXECUTE DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', - :task_id, :task_name, template=>'SQLACCESS_WAREHOUSE');
This section discusses the following aspects of managing workloads:
Because the workload is stored as a separate workload object, it can easily be shared among many Advisor tasks. Once a workload object has been referenced by an Advisor task, a workload object cannot be deleted or modified until all Advisor tasks have removed their dependency on the data. A workload reference will be removed when a parent Advisor task is deleted or when the workload reference is manually removed from the Advisor task by the user.
The SQL Access Advisor performs best when a workload based on usage is available. The SQL Access Workload Repository is capable of storing multiple workloads, so that the different uses of a real-world data warehousing or transaction processing environment can be viewed over a long period of time and across the life cycle of database instance startup and shutdown.
Before the actual SQL statements for a workload can be defined, the workload must be created using the CREATE_SQLWKLD
procedure. Then, the workload is loaded using the appropriate IMPORT_SQLWKLD
procedure. A specific workload can be removed by calling the DELETE_SQLWKLD
procedure and passing it a valid workload name. To remove all workloads for the current user, call DELETE_SQLWKLD
and pass the constant value ADVISOR_ALL
or %
.
The CREATE_SQLWKLD
procedure creates a workload and it must exist prior to performing any other workload operations, such as importing or updating SQL statements. The workload is identified by its name, so you should define a name that is unique and is relevant for the operation.
Its syntax is as follows:
DBMS_ADVISOR.CREATE_SQLWKLD ( workload_name IN OUT VARCHAR2, description IN VARCHAR2 := NULL, template IN VARCHAR2 := NULL. is_template IN VARCHAR2 := 'FALSE');
The following examples illustrate using this procedure:
Example 17-1 Creating a Workload
VARIABLE workload_name VARCHAR2(255); EXECUTE :workload_name := 'MYWORKLOAD'; EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name,'This is my first workload');
Example 17-2 Creating a Workload from a Template
Create the variables.
VARIABLE template_id NUMBER; VARIABLE template_name VARCHAR2(255);
Create a template called MY_WK_TEMPLATE
.
EXECUTE :template_name := 'MY_WK_TEMPLATE'; EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:template_name, is_template=>'TRUE');
Set template parameters. For example, the following sets the filter so only tables in the sh
schema are tuned:
-- set USERNAME_LIST filter to SH EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER( - :template_name, 'USERNAME_LIST', 'SH');
Now create a workload using the template:
VARIABLE workload_name VARCHAR2(255); EXECUTE :workload_name := 'MYWORKLOAD'; EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD ( - :workload_name, 'This is my first workload', 'MY_WK_TEMPLATE');
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the CREATE_SQLWKLD
procedure and its parameters.
Before the recommendation process can begin, the task must be linked to a workload. You achieve this by using the ADD_SQKLWKLD_REF
procedure and the task and workload are linked by using their respective names. This procedure establishes a link between the Advisor task and a workload. Once a connection between an Advisor task and a workload is made, the workload is protected from removal. The syntax is as follows:
DBMS_ADVISOR.ADD_SQLWKLD_REF (task_name IN VARCHAR2, workload_name IN VARCHAR2);
The following example links the MYTASK
task created to the MYWORKLOAD
SQL workload.
EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF('MYTASK', 'MYWORKLOAD');
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the ADD_SQLWKLD_REF
procedure and its parameters.
Once a workload has been created, it must then be populated with information. Ideally, a workload will consist of the SQL statements (unless it is a hypothetical workload) that are being used against the database. The SQL Access Advisor can obtain its workload from the following sources:
A SQL Tuning Set is the workload from the workload repository. You can use a SQL Tuning Set as the workload for the SQL Access Advisor by importing it using the IMPORT_WORKLOAD_STS
procedure. The syntax of this procedure is as follows:
DBMS_ADVISOR.IMPORT_SQLWKLD_STS (workload_name IN VARCHAR2, sts_owner IN VARCHAR2, sts_name IN VARCHAR2, import_mode IN VARCHAR2 := 'NEW', priority IN NUMBER := 2, saved_rows OUT NUMBER, failed_rows OUT NUMBER); DBMS_ADVISOR.IMPORT_SQLWKLD_STS (workload_name IN VARCHAR2, sts_name IN VARCHAR2, import_mode IN VARCHAR2 := 'NEW', priority IN NUMBER := 2, saved_rows OUT NUMBER, failed_rows OUT NUMBER);
After a workload has been collected and the statements filtered, the SQL Access Advisor computes usage statistics with respect to the DML statements in the workload.
The following example creates a workload from a SQL Tuning Set named MY_STS_WORKLOAD
.
VARIABLE sqlsetname VARCHAR2(30); VARIABLE workload_name VARCHAR2(30); VARIABLE saved_stmts NUMBER; VARIABLE failed_stmts NUMBER; EXECUTE :sqlsetname := 'MY_STS_WORKLOAD'; EXECUTE :workload_name := 'MY_WORKLOAD'; EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD (:workload_name); EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_STS (:workload_name , - :sqlsetname, 'NEW', 1, :saved_stmts, :failed_stmts);
To load a user-defined workload, use the IMPORT_SQLWKLD_USER
procedure. This procedure collects an application workload from a user-constructed table or view and saves it in the Advisor repository. The two parameters, owner_name
and table_name
identify the table where the workload is to be retrieved from.
There are no restrictions on which schema the workload resides in, the name for the table, or how many of these user-defined tables exist. The only requirements are that the format of the user table must correspond to the USER_WORKLOAD
table, as described in Table 17-1, and that the user have SELECT
access to the workload table or view. The syntax is as follows:
DBMS_ADVISOR.IMPORT_SQLWKLD_USER ( workload_name IN VARCHAR2, import_mode IN VARCHAR2 := 'NEW', owner_name IN VARCHAR2, table_name IN VARCHAR2, saved_rows OUT NUMBER, failed_rows OUT NUMBER);
The following example loads MYWORKLOAD
workload created earlier, using a user table SH.USER_WORKLOAD
. The table is assumed to be populated with SQL statements and conforms to the format specified in Table 17-1.
VARIABLE saved_stmts NUMBER; VARIABLE failed_stmts NUMBER; EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_USER( - 'MYWORKLOAD', 'NEW', 'SH', 'USER_WORKLOAD', :saved_stmts, :failed_stmts);
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the IMPORT_SQLWKLD_USER
procedure and its parameters.
Table 17-1 USER_WORKLOAD Table Format
Column | Type | Default | Comments |
---|---|---|---|
MODULE |
VARCHAR2(64) |
Empty string | Application module name. |
ACTION |
VARCHAR2(64) |
Empty string | Application action. |
BUFFER_GETS |
NUMBER |
0 | Total buffer-gets for the statement. |
CPU_TIME |
NUMBER |
0 | Total CPU time in seconds for the statement. |
ELAPSED_TIME |
NUMBER |
0 | Total elapsed time in seconds for the statement. |
DISK_READS |
NUMBER |
0 | Total number of disk-read operations used by the statement. |
ROWS_PROCESSED |
NUMBER |
0 | Total number of rows process by this SQL statement. |
EXECUTIONS |
NUMBER |
1 | Total number of times the statement is executed. |
OPTIMIZER_COST |
NUMBER |
0 | Optimizer's calculated cost value for executing the query. |
LAST_EXECUTION_DATE |
DATE |
SYSDATE |
Last time the query is used. Defaults to not available. |
PRIORITY |
NUMBER |
2 |
Must be one of the following values:
1- |
SQL_TEXT |
CLOB or LONG or VARCHAR2 |
None | The SQL statement. This is a required column. |
STAT_PERIOD |
NUMBER |
1 | Period of time that corresponds to the execution statistics in seconds. |
USERNAME |
VARCHAR(30) |
Current user | User submitting the query. This is a required column. |
You obtain a SQL cache workload using the procedure IMPORT_SQLWKLD_SQLCACHE
. At the time this procedure is called, the current contents of the SQL cache are analyzed and placed into the workload. The IMPORT_SQLWKLD_SQLCACHE
procedure loads a SQL workload from the SQL cache. The syntax is as follows:
DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE ( workload_name IN VARCHAR2, import_mode IN VARCHAR2, priority IN NUMBER := 2, saved_rows OUT NUMBER, failed_rows OUT NUMBER);
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the IMPORT_SQLWKLD_SQLCACHE
procedure and its parameters.
The following example loads the MYWORKLOAD
workload created earlier from the SQL Cache. The priority of the loaded workload statements is 2 (medium).
VARIABLE saved_stmts NUMBER; VARIABLE failed_stmts NUMBER; EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE (- 'MYWORKLOAD', 'APPEND', 2, :saved_stmts, :failed_stmts);
The SQL Access Advisor can retrieve workload information from the SQL cache. If the collected data was retrieved from a server with the instance parameter cursor_sharing
set to SIMILAR
or FORCE
, then user queries with embedded literal values will be converted to a statement that contains system-generated bind variables. If you are going to use the SQL Access Advisor to recommend materialized views, then the server should set the instance parameter cursor_sharing
to EXACT
so that materialized views with WHERE
clauses can be recommended.
In many situations, an application workload may not yet exist. In this case, the SQL Access Advisor can examine the current logical schema design and form recommendations based on defined relationships among tables. This type of workload is also referred to as a hypothetical workload. The SQL Access Advisor can produce an initial set of recommendations and become a solid base for tuning an application.
The benefits of using hypothetical workloads are that they:
Require only schema and table relationships.
Are effective for modeling what-if scenarios.
Some of the disadvantages of hypothetical workloads are that they:
Only work if dimensions have been defined.
Offer no information about the impact of DML on the recommended access structures.
Are not necessarily complete.
To successfully import a hypothetical workload, the target schemas must contain dimension information. You use the IMPORT_SQLWKLD_SCHEMA
procedure. The syntax is as follows:
DBMS_ADVISOR.IMPORT_SQLWKLD_SCHEMA ( workload_name IN VARCHAR2, import_mode IN VARCHAR2 := 'NEW', priority IN NUMBER := 2, saved_rows OUT NUMBER, failed_rows OUT NUMBER);
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the IMPORT_SQLWKLD_SCHEMA
procedure and its parameters. You must configure external procedures to use this procedure.
The following example creates a hypothetical workload called SCHEMA_WKLD
, sets VALID_TABLE_LIST
to sh
and calls IMPORT_SQLWKLD_SCHEMA
to generate a hypothetical workload.
VARIABLE workload_name VARCHAR2(255); VARIABLE saved_stmts NUMBER; VARIABLE failed_stmts NUMBER; EXECUTE :workload_name := 'SCHEMA_WKLD'; EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name); EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER (:workload_name, - VALID_TABLE_LIST, 'SH'); EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SCHEMA ( - :workload_name, 'NEW', 2, :saved_stmts, :failed_stmts);
When using IMPORT_SQLWKLD_SCHEMA
, the VALID_TABLE_LIST
parameter cannot contain wildcards such as SCO%
or SCOTT.EMP%
. The only form of wildcards supported is SCOTT.%
, which specifies all tables in a given schema.
You may have created workloads using the Oracle Database 9i Summary Advisor. These workloads can be used by the SQL Access Advisor by importing them using the IMPORT_SQLWLD_SUMADV
procedure. To use this procedure, you must know the Oracle Database 9i workload ID.
This procedure collects a SQL workload from a Summary Advisor workload. This procedure is intended to assist Oracle Database 9i Summary Advisor users in the migration to SQL Access Advisor. The syntax is as follows:
DBMS_ADVISOR.IMPORT_SQLWKLD_SUMADV ( workload_name IN VARCHAR2, import_mode IN VARCHAR2 := 'NEW', priority IN NUMBER := 2, sumadv_id IN NUMBER, saved_rows OUT NUMBER, failed_rows OUT NUMBER);
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the IMPORT_SQLWKLD_SUMADV
procedure and its parameters.
The following example creates a SQL workload from an Oracle Database 9i Summary Advisor workload. The workload_id
of the Oracle Database 9i workload is 777.
Create some variables.
VARIABLE workload_name VARCHAR2(255); VARIABLE saved_stmts NUMBER; VARIABLE failed_stmts NUMBER;
Create a workload named WKLD_9I
.
EXECUTE :workload_name := 'WKLD_9I'; EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);
Import the workload from Oracle Database 9i Summary Advisor.
EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SUMADV ( - :workload_name, 'NEW', 2, 777, :saved_stmts, :failed_stmts);
A SQL workload can be filtered at the time of loading by setting one of more of the parameters listed in Oracle Database PL/SQL Packages and Types Reference using SET_SQLWKLD_PARAMETER
.
The following example illustrates setting of SQL Workload parameters. Here we set the SQL_LIMIT
to 3 and ORDER_LIST
to OPTIMIZER_COST
. This means that when importing the workload, the statements will be ordered by OPTIMIZER_COST
and the top three statements will be kept.
-- Order statements by OPTIMIZER_COST EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER ( - 'MYWORKLOAD', 'ORDER_LIST', 'OPTIMIZER_COST'); -- Max number of statements 3 EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER('MYWORKLOAD', 'SQL_LIMIT', 3);
An alternative to importing a workload is to manually specify the SQL statements and add them to your workload using the ADD_SQLWKLD_STATEMENT
procedure. This procedure adds a SQL statement to the specified workload. The syntax is as follows:
DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT ( workload_name IN VARCHAR2, module IN VARCHAR2, action IN VARCHAR2, cpu_time IN NUMBER := 0, elapsed_time IN NUMBER := 0, disk_reads IN NUMBER := 0, buffer_gets IN NUMBER := 0, rows_processed IN NUMBER := 0, optimizer_cost IN NUMBER := 0, executions IN NUMBER := 1, priority IN NUMBER := 2, last_execution_date IN DATE := 'SYSDATE', stat_period IN NUMBER := 0, username IN VARCHAR2, sql_text IN CLOB);
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the ADD_SQLWKLD_STATEMENT
procedure and its parameters. The following example adds a single statement to the MYWORKLOAD
workload:
VARIABLE sql_text VARCHAR2(400); EXECUTE :sql_text := 'SELECT AVG(amount_sold) FROM sales'; EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT ( - 'MYWORKLOAD', 'MONTHLY', 'ROLLUP', priority=>1, executions=>10, - username => 'SH', sql_text => :sql_text);
You can delete an existing SQL statement from a specified workload using the DELETE_SQLWKLD_STATEMENT
procedure. It lets you delete a statement specified by a given sql_id
.
DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT (workload_name IN VARCHAR2, sql_id IN NUMBER);
The following example deletes from MYWORKLOAD
with sql_id
10:
EXECUTE DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT('MYWORKLOAD', 10);
A workload cannot be modified or deleted if it is currently referenced by an active task. A task is considered active if it is not in its initial state. See the RESET_TASK
procedure to set a task to its initial state. See Oracle Database PL/SQL Packages and Types Reference for more information regarding the DELETE_SQLWKLD_STATEMENT
procedure and its parameters.
You can modify SQL statements in a workload by using the UPDATE_SQLWKLD_STATEMENT
procedure. This procedure updates an existing SQL statement in the specified workload.
It enables you to update a SQL statement by specifying its sql_id
. The syntax is as follows:
DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT ( workload_name IN VARCHAR2, sql_id IN NUMBER, application IN VARCHAR2 := NULL, action IN VARCHAR2 := NULL, priority IN NUMBER := NULL, username IN VARCHAR2 := NULL); DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT ( workload_name IN VARCHAR2, search IN VARCHAR2, updated OUT NUMBER, application IN VARCHAR2 := NULL, action IN VARCHAR2 := NULL, priority IN NUMBER := NULL, username IN VARCHAR2 := NULL);
The following example changes the priority to 3 for statement ID 10:
EXECUTE DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT('MYWORKLOAD', 10, priority=>3);
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the UPDATE_SQLWKLD_STATEMENT
procedure and its parameters.
There are several other operations that can be performed upon a workload, including the following:
The UPDATE_SQLWKLD_ATTRIBUTES
procedure changes various attributes of a workload object or template. Some of these attributes are its description, and whether it is a template or read only. The syntax is as follows:
DBMS_ADVISOR.UPDATE_SQLWKLD_ATTRIBUTES ( workload_name IN VARCHAR2, new_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, read_only IN VARCHAR2 := NULL, is_template IN VARCHAR2 := NULL, how_created IN VARCHAR2 := NULL);
The following example changes the workload MYWORKLOAD
to read-only:
EXECUTE DBMS_ADVISOR.UPDATE_SQLWKLD_ATTRIBUTES ( - 'MYWORKLOAD', read_only=> 'TRUE');
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the UPDATE_SQLWKLD_ATTRIBUTES
procedure and its parameters.
The RESET_SQLWKLD
procedure resets a workload to its initial starting point. This has the effect of removing all journal and log messages, recalculating volatility statistics, while the workload data remains untouched. This procedure should be executed after any workload adjustments such as adding or removing SQL statements. The following example resets workload MYWORKLOAD
.
EXECUTE DBMS_ADVISOR.RESET_SQLWKLD('MYWORKLOAD');
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the RESET_SQLWKLD
procedure and its parameters.
Before a task or a workload can be deleted, if it is linked to a workload or task respectively, then the link between the task and the workload must be removed using DELETE_SQLWKLD_REF
procedure. The following example deletes the link between task MYTASK
and SQL workload MYWORKLOAD
:
EXECUTE DBMS_ADVISOR.DELETE_SQLWKLD_REF('MYTASK', 'MYWORKLOAD');
When workloads are no longer needed, they can be removed using the procedure DELETE_SQLWKLD
. You can delete all workloads or a specific collection, but a workload cannot be deleted if it is still linked to a task.
The following procedure is an example of removing a specific workload. It deletes an existing workload from the repository.
DBMS_ADVISOR.DELETE_SQLWKLD (workload_name IN VARCHAR2); EXECUTE DBMS_ADVISOR.DELETE_SQLWKLD('MYWORKLOAD');
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the DELETE_SQLWKLD
procedure and its parameters.
This section discusses the following aspects of working with recommendations:
Before recommendations can be generated, the parameters for the task must first be defined using the SET_TASK_PARAMETER
procedure. If parameters are not defined, then the defaults are used.
You can set task parameters by using the SET_TASK_PARAMETER
procedure. The syntax is as follows.
DBMS_ADVISOR.SET_TASK_PARAMETER ( task_name IN VARCHAR2, parameter IN VARCHAR2, value IN [VARCHAR2 | NUMBER]);
There are many task parameters and, to help identify the relevant ones, they have been grouped into categories in Table 17-2.
Table 17-2 Types of Advisor Task Parameters And Their Uses
Workload Filtering | Task Configuration | Schema Attributes | Recommendation Options |
---|---|---|---|
END_TIME |
DAYS_TO_EXPIRE |
DEF_INDEX_OWNER |
DML_VOLATILITY |
INVALID_ACTION_LIST |
REPORT_DATE_FORMAT |
DEF_INDEX_TABLESPACE |
EVALUATION_ONLY |
INVALID_MODULE_LIST |
JOURNALING |
DEF_MVIEW_OWNER |
EXECUTION_TYPE |
INVALID_SQLTRING_LIMIT |
|
DEF_MVIEW_TABLESPACE |
MODE |
INVALID_TABLE_LIST |
|
DEF_MVLOG_TABLSPACE |
REFRESH_MODE |
INVALID_USERNAME_LIST |
|
INDEX_NAME_TEMPLATE |
STORAGE_CHANGE |
ORDER_LIST |
|
MVIEW_NAME_TEMPLATE |
CREATION_COST |
SQL_LIMIT |
|
|
WORKLOAD_SCOPE |
START_TIME |
|
|
|
TIME_LIMIT |
|
|
|
VALID_ACTION_LIST |
|
|
|
VALID_MODULE_LIST |
|
|
|
VALID_SQLSTRING_LIST |
|
|
|
VALID_TABLE_LIST |
|
||
VALID_USERNAME_LIST |
In the following example, set the storage change of task MYTASK
to 100MB. This indicates 100MB of additional space for recommendations. A zero value would indicate that no additional space can be allocated. A negative value indicates that the advisor must attempt to trim the current space utilization by the specified amount.
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER('MYTASK','STORAGE_CHANGE', 100000000);
In the following example, we set the VALID_TABLE_LIST
parameter to filter out all queries that do no consist of tables SH.SALES
and SH.CUSTOMERS
.
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - 'MYTASK', 'VALID_TABLE_LIST', 'SH.SALES, SH.CUSTOMERS');
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the SET_TASK_PARAMETER
procedure and its parameters.
When executing a task, SQL Access Advisor operates in two modes: problem solving and evaluation. By default, SQL Access Advisor will attempt to solve access method problems by looking for enhancements to index structures, materialized views and materialized view logs. When doing evaluation only, SQL Access Advisor will only comment on what access structures the supplied workload will use. For example, a problem solving run may recommend creating a new index, adding a new column to a materialized view log, and so on, while an evaluation only scenario will only produce recommendations such as retain index, retain materialized view, and so on. No adjustments to access methods will be considered when doing evaluations. It is strictly a method of viewing existing access method structures and how they are being used by the supplied workload.
You can generate recommendations by using the EXECUTE_TASK
procedure with your task name. After the procedure finishes, you can check the DBA_ADVISOR_LOG
table for the actual execution status and the number of recommendations and actions that have been produced. The recommendations can be queried by task name in {DBA, USER}_ADVISOR_RECOMMENDATIONS
and the actions for these recommendations can be viewed by task in {DBA, USER}_ADVISOR_ACTIONS.
This procedure performs the SQL Access Advisor analysis or evaluation for the specified task. Task execution is a synchronous operation, so control will not be returned to the user until the operation has completed, or a user-interrupt was detected. Upon return or execution of the task, you can check the DBA_ADVISOR_LOG
table for the actual execution status.
Running EXECUTE_TASK
generates recommendations, where a recommendation comprises one or more actions, such as creating a materialized view log and a materialized view. The syntax is as follows:
DBMS_ADVISOR.EXECUTE_TASK (task_name IN VARCHAR2);
The following illustrates an example of using this procedure:
EXECUTE DBMS_ADVISOR.EXECUTE_TASK('MYTASK');
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the EXECUTE_TASK
procedure and its parameters.
Each recommendation generated by the SQL Access Advisor can be viewed using several catalog views, such as (
DBA
, USER)_ADVISOR_RECOMMENDATIONS
. However, it is easier to use the GET_TASK_SCRIPT
procedure or use the SQL Access Advisor in Enterprise Manager, which graphically displays the recommendations and provides hyperlinks to quickly see which SQL statements benefit from a recommendation. Each recommendation produced by the SQL Access Advisor is linked to the SQL statement it benefits.
The following shows the recommendation (rec_id
) produced by an Advisor run, with their rank and total benefit. The rank is a measure of the importance of the queries that the recommendation helps. The benefit is the total improvement in execution cost (in terms of optimizer cost) of all the queries using the recommendation.
VARIABLE workload_name VARCHAR2(255); VARIABLE task_name VARCHAR2(255); EXECUTE :task_name := 'MYTASK'; EXECUTE :workload_name := 'MYWORKLOAD'; SELECT REC_ID, RANK, BENEFIT FROM USER_ADVISOR_RECOMMENDATIONS WHERE TASK_NAME = :task_name; REC_ID RANK BENEFIT ---------- ---------- ---------- 1 2 2754 2 3 1222 3 1 5499 4 4 594
To identify which query benefits from which recommendation, you can use the views DBA_*
and USER_ADVISOR_SQLA_WK_STMTS
. The precost and postcost numbers are in terms of the estimated optimizer cost (shown in EXPLAIN
PLAN
) without and with the recommended access structure changes, respectively. To see recommendations for each query, issue the following statement:
SELECT sql_id, rec_id, precost, postcost, (precost-postcost)*100/precost AS percent_benefit FROM USER_ADVISOR_SQLA_WK_STMTS WHERE TASK_NAME = :task_name AND workload_name = :workload_name; SQL_ID REC_ID PRECOST POSTCOST PERCENT_BENEFIT ---------- ---------- ---------- ---------- --------------- 121 1 3003 249 91.7082917 122 2 1404 182 87.037037 123 3 5503 4 99.9273124 124 4 730 136 81.369863
Each recommendation consists of one or more actions, which must be implemented together to realize the benefit provided by the recommendation. The SQL Access Advisor produces the following types of actions:
CREATE|DROP|RETAIN MATERIALIZED VIEW
CREATE|ALTER|RETAIN MATERIALIZED VIEW LOG
CREATE|DROP|RETAIN INDEX
GATHER STATS
The CREATE
actions corresponds to new access structures. RETAIN
recommendation indicate that existing access structures must be kept. DROP
recommendations are only produced if the WORKLOAD_SCOPE
parameter is set to FULL
. The GATHER
STATS
action will generate a call to DBMS_STATS
procedure to gather statistics on a newly generated access structure. Note that multiple recommendations may refer to the same action, however when generating a script for the recommendation, you will only see each action once.
In the following example, you can see how many distinct actions there are for this set of recommendations.
SELECT 'Action Count', COUNT(DISTINCT action_id) cnt FROM user_advisor_actions WHERE task_name = :task_name; 'ACTIONCOUNT CNT ------------ ---------- Action Count 20 -- see the actions for each recommendations SELECT rec_id, action_id, SUBSTR(command,1,30) AS command FROM user_advisor_actions WHERE task_name = :task_name ORDER BY rec_id, action_id; REC_ID ACTION_ID COMMAND ---------- ---------- ------------------------------ 1 5 CREATE MATERIALIZED VIEW LOG 1 6 ALTER MATERIALIZED VIEW LOG 1 7 CREATE MATERIALIZED VIEW LOG 1 8 ALTER MATERIALIZED VIEW LOG 1 9 CREATE MATERIALIZED VIEW LOG 1 10 ALTER MATERIALIZED VIEW LOG 1 11 CREATE MATERIALIZED VIEW 1 12 GATHER TABLE STATISTICS 1 19 CREATE INDEX 1 20 GATHER INDEX STATISTICS 2 5 CREATE MATERIALIZED VIEW LOG 2 6 ALTER MATERIALIZED VIEW LOG 2 9 CREATE MATERIALIZED VIEW LOG ...
Each action has several attributes that pertain to the properties of the access structure. The name and tablespace for each access structure when applicable are placed in attr1
and attr2
respectively. The space occupied by each new access structure is in num_attr1
. All other attributes are different for each action.
Table 17-3 maps SQL Access Advisor action information to the corresponding column in DBA_ADVISOR_ACTIONS
.
Table 17-3 SQL Access Advisor Action Attributes
ATTR1 | ATTR2 | ATTR3 | ATTR4 | ATTR5 | ATTR6 | NUM_ATTR1 | |
---|---|---|---|---|---|---|---|
CREATE INDEX |
Index name | Index tablespace | Target table | BITMAP or BTREE |
Index column list / expression | Unused | Storage size in bytes for the index |
CREATE MATERIALIZED VIEW |
MV name | MV tablespace | REFRESH COMPLETE REFRESH FAST , REFRESH FORCE , NEVER REFRESH |
ENABLE QUERY REWRITE , DISABLE QUERY REWRITE |
SQL SELECT statement |
Unused | Storage size in bytes for the MV |
CREATE MATERIALIZED VIEW LOG |
Target table name | MV log tablespace | ROWID PRIMARY KEY , SEQUENCE OBJECT ID |
INCLUDING NEW VALUES , EXCLUDING NEW VALUES |
Table column list | Partitioning subclauses | Unused |
CREATE REWRITE EQUIVALENCE |
Name of equivalence | Checksum value | Unused | Unused | Source SQL statement | Equivalent SQL statement | Unused |
DROP INDEX |
Index name | Unused | Unused | Unused | Index columns | Unused | Storage size in bytes for the index |
DROP MATERIALIZED VIEW |
MV name | Unused | Unused | Unused | Unused | Unused | Storage size in bytes for the MV |
DROP MATERIALIZED VIEW LOG |
Target table name | Unused | Unused | Unused | Unused | Unused | Unused |
RETAIN INDEX |
Index name | Unused | Target table | BITMAP or BTREE |
Index columns | Unused | Storage size in bytes for the index |
RETAIN MATERIALIZED VIEW |
MV name | Unused | REFRESH COMPLETE or REFRESH FAST |
Unused | SQL SELECT statement |
Unused | Storage size in bytes for the MV |
RETAIN MATERIALIZED VIEW LOG |
Target table name | Unused | Unused | Unused | Unused | Unused | Unused |
The following PL/SQL procedure can be used to print out some of the attributes of the recommendations.
CONNECT SH/SH; CREATE OR REPLACE PROCEDURE show_recm (in_task_name IN VARCHAR2) IS CURSOR curs IS SELECT DISTINCT action_id, command, attr1, attr2, attr3, attr4 FROM user_advisor_actions WHERE task_name = in_task_name ORDER BY action_id; v_action number; v_command VARCHAR2(32); v_attr1 VARCHAR2(4000); v_attr2 VARCHAR2(4000); v_attr3 VARCHAR2(4000); v_attr4 VARCHAR2(4000); v_attr5 VARCHAR2(4000); BEGIN OPEN curs; DBMS_OUTPUT.PUT_LINE('========================================='); DBMS_OUTPUT.PUT_LINE('Task_name = ' || in_task_name); LOOP FETCH curs INTO v_action, v_command, v_attr1, v_attr2, v_attr3, v_attr4 ; EXIT when curs%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Action ID: ' || v_action); DBMS_OUTPUT.PUT_LINE('Command : ' || v_command); DBMS_OUTPUT.PUT_LINE('Attr1 (name) : ' || SUBSTR(v_attr1,1,30)); DBMS_OUTPUT.PUT_LINE('Attr2 (tablespace): ' || SUBSTR(v_attr2,1,30)); DBMS_OUTPUT.PUT_LINE('Attr3 : ' || SUBSTR(v_attr3,1,30)); DBMS_OUTPUT.PUT_LINE('Attr4 : ' || v_attr4); DBMS_OUTPUT.PUT_LINE('Attr5 : ' || v_attr5); DBMS_OUTPUT.PUT_LINE('----------------------------------------'); END LOOP; CLOSE curs; DBMS_OUTPUT.PUT_LINE('=========END RECOMMENDATIONS============'); END show_recm; / -- see what the actions are using sample procedure set serveroutput on size 99999 EXECUTE show_recm(:task_name); A fragment of a sample output from this procedure is as follows: Task_name = MYTASK Action ID: 1 Command : CREATE MATERIALIZED VIEW LOG Attr1 (name) : "SH"."CUSTOMERS" Attr2 (tablespace): Attr3 : ROWID, SEQUENCE Attr4 : INCLUDING NEW VALUES Attr5 : ---------------------------------------- .. ---------------------------------------- Action ID: 15 Command : CREATE MATERIALIZED VIEW Attr1 (name) : "SH"."SH_MV$$_0004" Attr2 (tablespace): "SH_MVIEWS" Attr3 : REFRESH FAST WITH ROWID Attr4 : ENABLE QUERY REWRITE Attr5 : ---------------------------------------- .. ---------------------------------------- Action ID: 19 Command : CREATE INDEX Attr1 (name) : "SH"."SH_IDX$$_0013" Attr2 (tablespace): "SH_INDEXES" Attr3 : "SH"."SH_MV$$_0002" Attr4 : BITMAP Attr5 :
See Oracle Database PL/SQL Packages and Types Reference for details regarding Attr5
and Attr6
.
During the analysis process (EXECUTE_TASK
), the SQL Access Advisor saves useful information regarding the analysis to a journal. The journal can be viewed using the view USER_ADVISOR_JOURNAL
. The amount of information output varies depending on the setting of task parameter JOURNALING
.
During the import of a workload, various informational messages are recorded in the SQL Workload Journal. These can be viewed using the view USER_ADVISOR_SQLW_JOURNAL
. The journal is useful to identify why some statements were filtered out of the workload. For example, if a certain SQL statement refers to invalid tables, tables with missing statistics or has privilege errors, the information will be recorded in the journal. The amount of information output can be controlled by setting the JOURNALING
parameter.
To turn journaling off, issue the following statement:
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER('MYTASK', 'JOURNALING', 0);
To turn journaling off before importing the workload, issue the following statement:
EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER('MYWORKLOAD', 'JOURNALING', 0);
To view only informational messages, issue the following statement:
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER('MYTASK', 'JOURNALING', 4);
To view only fatal messages, issue the following statement:
EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER('MYWORKLOAD', 'JOURNALING', 1);
The information in the journal is for diagnostic purposes only and subject to change in future releases. It should not be used within any application.
See Oracle Database PL/SQL Packages and Types Reference for details of all the settings for the JOURNALING
parameter.
If the SQL Access Advisor takes too long to make its recommendations using the procedure EXECUTE_TASK
, you can stop it by calling the CANCEL_TASK
procedure and passing in the task_name
for this recommendation process. If you use CANCEL_TASK
, no recommendations will be made. In addition, you can interrupt the recommendation process with the INTERRUPT_TASK
procedure.
The INTERRUPT_TASK
procedure causes an Advisor operation to terminate as if it has reached its normal end. As a result, the user can see any recommendations that have been formed up to the point of the interrupt.
An interrupted task cannot be restarted. The syntax is as follows:
DBMS_ADVISOR.INTERRUPT_TASK (task_name IN VARCHAR2);
The following illustrates an example of using this procedure:
EXECUTE DBMS_ADVISOR.INTERRUPT_TASK ('MY_TASK');
The CANCEL_TASK
procedure causes a currently executing operation to terminate. An Advisor operation may take a few seconds to respond to the call. Because all Advisor task procedures are synchronous, to cancel an operation, you must use a separate database session.
A cancel command effective restores the task to its condition prior to the start of the cancelled operation. Therefore, a cancelled task or data object cannot be restarted. Its syntax is as follows:
DBMS_ADVISOR.CANCEL_TASK (task_name IN VARCHAR2);
The following illustrates an example of using this procedure:
EXECUTE DBMS_ADVISOR.CANCEL_TASK('MYTASK');
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the CANCEL_TASK
procedure and its parameters.
By default, all SQL Access Advisor recommendations are ready to be implemented, however, the user can choose to skip or exclude selected recommendations by using the MARK_RECOMMENDATION
procedure. MARK_RECOMMENDATION
allows the user to annotate a recommendation with a REJECT
or IGNORE
setting, which will cause the GET_TASK_SCRIPT
to skip it when producing the implementation procedure. The syntax is as follows:
DBMS_ADVISOR.MARK_RECOMMENDATION ( task_name IN VARCHAR2 id IN NUMBER, action IN VARCHAR2);
The following example marks a recommendation with ID 2 as REJECT
. This recommendation and any dependent recommendations will not appear in the script.
EXECUTE DBMS_ADVISOR.MARK_RECOMMENDATION('MYTASK', 2, 'REJECT');
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the MARK_RECOMMENDATIONS
procedure and its parameters.
Using the UPDATE_REC_ATTRIBUTES
procedure, the SQL Access Advisor names and assigns ownership to new objects such as indexes and materialized views during the analysis operation. However, it does not necessarily choose appropriate names, so you may manually set the owner, name, and tablespace values for new objects. For recommendations referencing existing database objects, owner and name values cannot be changed. The syntax is as follows:
DBMS_ADVISOR.UPDATE_REC_ATTRIBUTES ( task_name IN VARCHAR2 rec_id IN NUMBER, action_id IN NUMBER, attribute_name IN VARCHAR2, value IN VARCHAR2);
The attribute_name
parameter can take the following values:
OWNER
Specifies the owner name of the recommended object.
NAME
Specifies the name of the recommended object.
TABLESPACE
Specifies the tablespace of the recommended object.
The following example modifies the attribute TABLESPACE
for recommendation ID 1, action ID 1 to SH_MVIEWS
.
EXECUTE DBMS_ADVISOR.UPDATE_REC_ATTRIBUTES('MYTASK', 1, 1, - 'TABLESPACE', 'SH_MVIEWS');
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the UPDATE_REC_ATTRIBUTES
procedure and its parameters.
An alternative to querying the metadata to see the recommendations, is to create a script of the SQL statements for the recommendations, using the procedure GET_TASK_SCRIPT
. The resulting script is an executable SQL file that can contain DROP
, CREATE
, and ALTER
statements. For new objects, the names of the materialized views, materialized view logs, and indexes are auto-generated by using the user-specified name template. You should review the generated SQL script before attempting to execute it.
There are four task parameters that control the naming conventions (MVIEW_NAME_TEMPLATE
and INDEX_NAME_TEMPLATE
), the owner for these new objects (DEF_INDEX_OWNER
and DEF_MVIEW_OWNER
), and the tablespaces (DEF_MVIEW_TABLESPACE
and DEF_INDEX_TABLESPACE
).
The following example shows how to generate a CLOB containing the script for the recommendations:
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('MYTASK'), - 'ADVISOR_RESULTS', 'advscript.sql');
To save the script to a file, a directory path must be supplied so that the procedure CREATE_FILE
knows where to store the script. In addition, read and write privileges must be granted on this directory. The following example shows how to save an advisor script CLOB
to a file:
-- create a directory and grant permissions to read/write to it CONNECT SH/SH; CREATE DIRECTORY ADVISOR_RESULTS AS '/mydir'; GRANT READ ON DIRECTORY ADVISOR_RESULTS TO PUBLIC; GRANT WRITE ON DIRECTORY ADVISOR_RESULTS TO PUBLIC;
The following is a fragment of a script generated by this procedure. The script also includes PL/SQL calls to gather statistics on the recommended access structures and marks the recommendations as IMPLEMENTED
at the end:
Rem Access Advisor V10.1.0.0.0 - Production Rem Rem Username: SH Rem Task: MYTASK Rem Execution date: 15/04/2005 11:35 Rem set feedback 1 set linesize 80 set trimspool on set tab off set pagesize 60 whenever sqlerror CONTINUE CREATE MATERIALIZED VIEW LOG ON "SH"."PRODUCTS" WITH ROWID, SEQUENCE("PROD_ID","PROD_SUBCATEGORY") INCLUDING NEW VALUES; ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."PRODUCTS" ADD ROWID, SEQUENCE("PROD_ID","PROD_SUBCATEGORY") INCLUDING NEW VALUES; .. CREATE MATERIALIZED VIEW "SH"."MV$$_00510002" REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT SH.CUSTOMERS.CUST_STATE_PROVINCE C1, COUNT(*) M1 FROM SH.CUSTOMERS WHERE (SH.CUSTOMERS.CUST_STATE_PROVINCE = 'CA') GROUP BY SH.CUSTOMERS.CUST_STATE_PROVINCE; BEGIN DBMS_STATS.GATHER_TABLE_STATS('"SH"', '"MV$$_00510002"', NULL, DBMS_STATS.AUTO_SAMPLE_SIZE); END; / .. CREATE BITMAP INDEX "SH"."MV$$_00510004_IDX$$_00510013" ON "SH"."MV$$_00510004" ("C4"); whenever sqlerror EXIT SQL.SQLCODE BEGIN DBMS_ADVISOR.MARK_RECOMMENDATION('"MYTASK"',1,'IMPLEMENTED'); DBMS_ADVISOR.MARK_RECOMMENDATION('"MYTASK"',2,'IMPLEMENTED'); DBMS_ADVISOR.MARK_RECOMMENDATION('"MYTASK"',3,'IMPLEMENTED'); DBMS_ADVISOR.MARK_RECOMMENDATION('"MYTASK"',4,'IMPLEMENTED'); END; /
See Also:
Oracle Database SQL Reference forCREATE
DIRECTORY
syntax and Oracle Database PL/SQL Packages and Types Reference for detailed information about the GET_TASK_SCRIPT
procedureThe RESET_TASK
procedure resets a task to its initial starting point. This has the effect of removing all recommendations, and intermediate data from the task. The actual task status is set to INITIAL
. The syntax is as follows:
DBMS_ADVISOR.RESET_TASK (task_name IN VARCHAR2);
The following illustrates an example of using this procedure:
EXECUTE DBMS_ADVISOR.RESET_TASK('MYTASK');
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the RESET_TASK
procedure and its parameters.
If you only want to tune a single SQL statement, the QUICK_TUNE
procedure accepts as its input a task_name and a SQL statement. It will then create a task and workload and execute that task. There is no difference in the results from using QUICK_TUNE
. They are exactly the same as those from using EXECUTE_TASK
, but this approach is easier to use when there is only a single SQL statement to be tuned. The syntax is as follows:
DBMS_ADVISOR.QUICK_TUNE ( advisor_name IN VARCHAR2, task_name IN VARCHAR2, attr1 IN CLOB, attr2 IN VARCHAR2 := NULL, attr3 IN NUMBER := NULL, task_or_template IN VARCHAR2 := NULL);
The following example shows how to quick tune a single SQL statement:
VARIABLE task_name VARCHAR2(255); VARIABLE sql_stmt VARCHAR2(4000); EXECUTE :sql_stmt := 'SELECT COUNT(*) FROM customers WHERE cust_state_province=''CA'''; EXECUTE :task_name := 'MY_QUICKTUNE_TASK'; EXECUTE DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR, - :task_name, :sql_stmt);
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the QUICK_TUNE
procedure and its parameters.
Every time recommendations are generated, tasks are created and, unless some maintenance is performed on these tasks, they will grow over time and will occupy storage space. There may be tasks that you want to keep and prevent accidental deletion. Therefore, there are several management operations that can be performed on tasks:
Using the UPDATE_TASK_ATTRIBUTES
procedure, you can:
Change the name of a task.
Give a task a description.
Set the task to be read only so it cannot be changed.
Make the task a template upon which other tasks can be defined.
Changes various attributes of a task or a task template.
The syntax is as follows:
DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES ( task_name IN VARCHAR2 new_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, read_only IN VARCHAR2 := NULL, is_template IN VARCHAR2 := NULL, how_created IN VARCHAR2 := NULL);
The following example updates the name of an task MYTASK
to TUNING1
:
EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES('MYTASK', 'TUNING1');
The following example marks the task TUNING1
to read only
EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES('TUNING1', read_only => 'TRUE');
The following example marks the task MYTASK
as a template.
EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES('TUNING1', is_template=>'TRUE');
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the UPDATE_TASK_ATTRIBUTES
procedure and its parameters.
The DELETE_TASK
procedure deletes existing Advisor tasks from the repository. The syntax is as follows:
DBMS_ADVISOR.DELETE_TASK (task_name IN VARCHAR2);
The following illustrates an example of using this procedure:
EXECUTE DBMS_ADVISOR.DELETE_TASK('MYTASK');
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the DELETE_TASK
procedure and its parameters.
When a task or workload object is created, the parameter DAYS_TO_EXPIRE
is set to 30. The value indicates the number of days until the task or object will automatically be deleted by the system. If you wish to save a task or workload indefinitely, the DAYS_TO_EXPIRE
parameter should be set to ADVISOR_UNLIMITED
.
You can use the constants shown in Table 17-4 with the SQL Access Advisor.
Table 17-4 SQL Access Advisor Constants
Constant | Description |
---|---|
ADVISOR_ALL |
A value that is used to indicate all possible values. For string parameters, this value is equivalent to the wildcard % character. |
ADVISOR_CURRENT |
Indicates the current time or active set of elements. Typically, this is used in time parameters. |
ADVISOR_DEFAULT |
Indicates the default value. Typically used when setting task or workload parameters. |
ADVISOR_UNLIMITED |
A value that represents an unlimited numeric value. |
ADVISOR_UNUSED |
A value that represents an unused entity. When a parameter is set to ADVISOR_UNUSED , it will have no effect on the current operation. This is typically used for setting a parameter as unused for its dependent operations. |
SQLACCESS_GENERAL |
Specifies the name of a default SQL Access general-purpose task template. This template will set the DML_VOLATILITY task parameter to TRUE and EXECUTION_TYPE to FULL . |
SQLACCESS_OLTP |
Specifies the name of a default SQL Access OLTP task template. This template will set the DML_VOLATILITY task parameter to TRUE and EXECUTION_TYPE to INDEX ONLY . |
SQLACCESS_WAREHOUSE |
Specifies the name of a default SQL Access warehouse task template. This template will set the DML_VOLATILITY task parameter to FALSE and EXECUTION_TYPE to FULL . |
SQLACCESS_ADVISOR |
Contains the formal name of the SQL Access Advisor. It can be used when procedures require the Advisor name as an argument. |
This section illustrates some typical scenarios for using the SQL Access Advisor. Oracle Database provides a script that contains this chapter's examples, aadvdemo.sql
.
The following example imports workload from a user-defined table, SH.USER_WORKLOAD
. It then creates a task called MYTASK
, sets the storage budget to 100 MB and runs the task. The recommendations are printed out using a PL/SQL procedure. Finally, it generates a script, which can be used to implement the recommendations.
Step 1 Prepare the USER_WORKLOAD table
The USER_WORKLOAD
table is loaded with SQL statements as follows:
CONNECT SH/SH; -- aggregation with selection INSERT INTO user_workload (username, module, action, priority, sql_text) VALUES ('SH', 'Example1', 'Action', 2, 'SELECT t.week_ending_day, p.prod_subcategory, SUM(s.amount_sold) AS dollars, s.channel_id, s.promo_id FROM sales s, times t, products p WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id AND s.prod_id > 10 AND s.prod_id < 50 GROUP BY t.week_ending_day, p.prod_subcategory, s.channel_id, s.promo_id') / -- aggregation with selection INSERT INTO user_workload (username, module, action, priority, sql_text) VALUES ('SH', 'Example1', 'Action', 2, 'SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars FROM sales s , times t WHERE s.time_id = t.time_id AND s.time_id between TO_DATE(''01-JAN-2000'', ''DD-MON-YYYY'') AND TO_DATE(''01-JUL-2000'', ''DD-MON-YYYY'') GROUP BY t.calendar_month_desc') / --Load all SQL queries. INSERT INTO user_workload (username, module, action, priority, sql_text) VALUES ('SH', 'Example1', 'Action', 2, 'SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount FROM sales s, times t, customers c, channels ch WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND c.cust_state_province = ''CA'' AND ch.channel_desc IN (''Internet'',''Catalog'') AND t.calendar_quarter_desc IN (''1999-Q1'',''1999-Q2'') GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc') / -- order by INSERT INTO user_workload (username, module, action, priority, sql_text) VALUES ('SH', 'Example1', 'Action', 2, 'SELECT c.country_id, c.cust_city, c.cust_last_name FROM customers c WHERE c.country_id IN (52790, 52789) ORDER BY c.country_id, c.cust_city, c.cust_last_name') / COMMIT; CONNECT SH/SH; set serveroutput on; VARIABLE task_id NUMBER; VARIABLE task_name VARCHAR2(255); VARIABLE workload_name VARCHAR2(255); VARIABLE saved_stmts NUMBER; VARIABLE failed_stmts NUMBER;
Step 2 Create a workload named MYWORKLOAD
EXECUTE :workload_name := 'MYWORKLOAD';
EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);
Step 3 Load the workload from user-defined table SH.USER_WORKLOAD
EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_USER (:workload_name, 'APPEND', 'SH', -
'USER_WORKLOAD', :saved_stmts, :failed_stmts); PRINT :saved_stmts; PRINT :failed_stmts;
Step 4 Create a task named MYTASK
EXECUTE :task_name := 'MYTASK';
EXECUTE DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', :task_id, :task_name);
Step 5 Set task parameters
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'STORAGE_CHANGE', 100);
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :task_name, 'EXECUTION_TYPE', 'INDEX_ONLY');
Step 6 Create a link between workload and task
EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF(:task_name, :workload_name);
Step 7 Execute the task
EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);
Step 8 View the recommendations
-- See the number of recommendations and the status of the task.
SELECT rec_id, rank, benefit FROM user_advisor_recommendations WHERE task_name = :task_name;
See "Viewing Recommendations" or "Generating SQL Scripts" for further details.
-- See recommendation for each query. SELECT sql_id, rec_id, precost, postcost, (precost-postcost)*100/precost AS percent_benefit FROM user_advisor_sqla_wk_stmts WHERE task_name = :task_name AND workload_name = :workload_name; -- See the actions for each recommendations. SELECT rec_id, action_id, SUBSTR(command,1,30) AS command FROM user_advisor_actions WHERE task_name = :task_name ORDER BY rec_id, action_id; -- See what the actions are using sample procedure. SET SERVEROUTPUT ON SIZE 99999 EXECUTE show_recm(:task_name);
Step 9 Generate a script to Implement the recommendations
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name),- 'ADVISOR_RESULTS', 'Example1_script.sql');
The following example creates a template and then uses it to create a task. It then uses this task to generate recommendations from a user-defined table, similar to "Recommendations From a User-Defined Workload".
CONNECT SH/SH; VARIABLE template_id NUMBER; VARIABLE template_name VARCHAR2(255);
Step 1 Create a template called MY_TEMPLATE
EXECUTE :template_name := 'MY_TEMPLATE';
EXECUTE DBMS_ADVISOR.CREATE_TASK ( - 'SQL Access Advisor',:template_id, :template_name, is_template=>'TRUE');
Step 2 Set template parameters
Set naming conventions for recommended indexes/materialized views.
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'INDEX_NAME_TEMPLATE', 'SH_IDX$$_<SEQ>'); EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'MVIEW_NAME_TEMPLATE', 'SH_MV$$_<SEQ>'); --Set default owners for recommended indexes/materialized views. EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'DEF_INDEX_OWNER', 'SH'); EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'DEF_MVIEW_OWNER', 'SH'); --Set default tablespace for recommended indexes/materialized views. EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'DEF_INDEX_TABLESPACE', 'SH_INDEXES'); EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'DEF_MVIEW_TABLESPACE', 'SH_MVIEWS');
Step 3 Create a task using the template
VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255); EXECUTE :task_name := 'MYTASK'; EXECUTE DBMS_ADVISOR.CREATE_TASK ( - 'SQL Access Advisor', :task_id, :task_name, template => 'MY_TEMPLATE'); --See the parameter settings for task SELECT parameter_name, parameter_value FROM user_advisor_parameters WHERE task_name = :task_name AND (parameter_name LIKE '%MVIEW%' OR parameter_name LIKE '%INDEX%');
Step 4 Create a workload named MYWORKLOAD
VARIABLE workload_name VARCHAR2(255);
VARIABLE saved_stmts NUMBER; VARIABLE failed_stmts NUMBER; EXECUTE :workload_name := 'MYWORKLOAD'; EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);
Step 5 Load the workload from user-defined table SH.USER_WORKLOAD
EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_USER ( -
:workload_name, 'APPEND', 'SH', 'USER_WORKLOAD', :saved_stmts,:failed_stmts);
Step 6 Create a link between the workload and the task
EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF(:task_name, :workload_name);
Step 7 Execute the task
EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);
Step 8 Generate a script
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name),-
'ADVISOR_RESULTS', 'Example2_script.sql');
The following example illustrates collection of a workload from a SQL cache. We first load the cache with a bunch of SQL statements. We then setup some filters to pick only a subset of those statements and import them into a SQL Access Advisor workload. The workload is then used to generate recommendations.
Step 1 Loading the SQL cache
The following statements are executed so they will be in the SQL cache:
CONNECT / AS SYSDBA --Clear any prior contents of the cache. ALTER SYSTEM FLUSH SHARED_POOL; CONNECT SH/SH; SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars FROM sales s, times t WHERE s.time_id = t.time_id AND s.time_id between TO_DATE('01-JAN-2000', 'DD-MON-YYYY') AND TO_DATE('01-JUL-2000', 'DD-MON-YYYY') GROUP BY t.calendar_month_desc; -- Order by SELECT c.country_id, c.cust_city, c.cust_last_name FROM customers c WHERE c.country_id IN ('52790', '52789') ORDER BY c.country_id, c.cust_city, c.cust_last_name; -- Queries to illustrate filtering CONNECT scott/tiger; SELECT e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno; SELECT COUNT(*) FROM dept; CONNECT sh/sh VARIABLE task_id NUMBER; VARIABLE task_name VARCHAR2(255); VARIABLE workload_name VARCHAR2(255); VARIABLE saved_stmts NUMBER; VARIABLE failed_stmts NUMBER;
Step 2 Create a workload named MY_CACHE_WORKLOAD
EXECUTE :workload_name := 'MY_CACHE_WORKLOAD';
EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);
Step 3 Set up filters
Load only SQL statements containing SH
tables
EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER ( - :workload_name, 'USERNAME_LIST', 'SH');
Step 4 Load the workload from SQL Cache
EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE ( -
:workload_name, 'APPEND', 2, :saved_stmts, :failed_stmts); PRINT :saved_stmts; PRINT :failed_stmts; --See the workload statements in catalog views SELECT num_select_stmt, create_date FROM user_advisor_sqlw_sum WHERE workload_name = :workload_name; SELECT sql_id, username, optimizer_cost, SUBSTR(sql_text, 1, 30) FROM user_advisor_sqlw_stmts WHERE workload_name = :workload_name ORDER BY sql_id;
Step 5 Add a single statement to the workload
EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT (:workload_name, username => 'SH', -
priority => 1, executions => 10, sql_text => - 'select count(*) from customers where cust_state_province=''CA'''); SELECT num_select_stmt, create_date FROM user_advisor_sqlw_sum WHERE workload_name = :workload_name;
Step 6 Create a task named MYTASK
EXECUTE :task_name := 'MYTASK';
EXECUTE DBMS_ADVISOR.CREATE_TASK ('SQL Access Advisor', :task_id, :task_name);
Step 7 Create a link between a workload and a task
EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF(:task_name, :workload_name);
Step 8 Execute the task
EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);
Step 9 Generate a script
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name),-
'ADVISOR_RESULTS', 'Example3_script.sql');
Several DBMS_MVIEW
procedures can help you create materialized views that are optimized for fast refresh and query rewrite. The EXPLAIN_MVIEW
procedure can tell you whether a materialized view is fast refreshable or eligible for general query rewrite and EXPLAIN_REWRITE
will tell you whether query rewrite will occur. However, neither tells you how to achieve fast refresh or query rewrite.
To further facilitate the use of materialized views, the TUNE_MVIEW
procedure shows you how to optimize your CREATE
MATERIALIZED
VIEW
statement and to meet other requirements such as materialized view log and rewrite equivalence relationship for fast refresh and general query rewrite. TUNE_MVIEW
analyzes and processes the CREATE
MATERIALIZED
VIEW
statement and generates two sets of output results: one for the materialized view implementation and the other for undoing the create materialized view operations. The two sets of output results can be accessed through Oracle views or be stored in external script files created by the SQL Access Advisor. These external script files are ready to execute to implement the materialized view.
With the TUNE_MVIEW
procedure, you no longer require a detailed understanding of materialized views to create a materialized view in an application because the materialized view and its required components (such as a materialized view log) will be created correctly through the procedure.
See Oracle Database PL/SQL Packages and Types Reference for detailed information about the TUNE_MVIEW
procedure.
This section discusses the following information:
The syntax for TUNE_MVIEW
is as follows:
DBMS_ADVISOR.TUNE_MVIEW ( task_name IN OUT VARCHAR2, mv_create_stmt IN [CLOB | VARCHAR2])
The TUNE_MVIEW
procedure takes two input parameters: task_name
and mv_create_stmt
. task_name
is a user-provided task identifier used to access the output results. mv_create_stmt
is a complete CREATE
MATERIALIZED
VIEW
statement that is to be tuned. If the input CREATE
MATERIALIZED
VIEW
statement does not have the clauses of REFRESH
FAST
or ENABLE
QUERY
REWRITE
, or both, TUNE_MVIEW
will use the default clauses REFRESH
FORCE
and DISABLE
QUERY
REWRITE
to tune the statement to be fast refreshable if possible or only complete refreshable otherwise.
The TUNE_MVIEW
procedure handles a broad range of CREATE
MATERIALIZED
VIEW
statements that can have arbitrary defining queries in them. The defining query could be a simple SELECT
statement or a complex query with set operators or inline views. When the defining query of the materialized view contains the clause REFRESH
FAST
, TUNE_MVIEW
analyzes the query and checks to see if it is fast refreshable. If it is already fast refreshable, the procedure will return a message saying "the materialized view is already optimal and cannot be further tuned". Otherwise, the TUNE_MVIEW
procedure will start the tuning work on the given statement.
The TUNE_MVIEW
procedure can generate the output statements that correct the defining query by adding extra columns such as required aggregate columns or fix the materialized view logs so that FAST
REFRESH
is possible. In the case of a complex defining query, the TUNE_MVIEW
procedure may decompose the query and generates two or more fast refreshable materialized views or will restate the materialized view in a way to fulfill fast refresh requirements as much as possible. The TUNE_MVIEW
procedure supports defining queries with the following complex query constructs:
Set operators (UNION
, UNION
ALL
, MINUS
, and INTERSECT
)
COUNT
DISTINCT
SELECT
DISTINCT
Inline views
When the ENABLE
QUERY
REWRITE
clause is specified, TUNE_MVIEW
will also fix the statement using a process similar to REFRESH
FAST
, that will redefine the materialized view so that as many of the advanced forms of query rewrite are possible.
The TUNE_MVIEW
procedure generates two sets of output results as executable statements. One set of the output (IMPLEMENTATION
) is for implementing materialized views and required components such as materialized view logs or rewrite equivalences to achieve fast refreshability and query rewritablity as much as possible. The other set of the output (UNDO
) is for dropping the materialized views and the rewrite equivalences in case you decide they are not required.
The output statements for the IMPLEMENTATION
process include:
CREATE
MATERIALIZED
VIEW
LOG
statements: creates any missing materialized view logs required for fast refresh.
ALTER
MATERIALIZED
VIEW
LOG
FORCE
statements: fixes any materialized view log related requirements such as missing filter columns, sequence, and so on, required for fast refresh.
One or more CREATE
MATERIALIZED
VIEW
statements: In the case of one output statement, the original defining query is directly restated and transformed. Simple query transformation could be just adding required columns. For example, add rowid column for materialized join view and add aggregate column for materialized aggregate view. In the case of decomposition, multiple CREATE
MATERIALIZED
VIEW
statements are generated and form a nested materialized view hierarchy in which one or more submaterialized views are referenced by a new top-level materialized view modified from the original statement. This is to achieve fast refresh and query rewrite as much as possible. Submaterialized views are often fast refreshable.
BUILD_SAFE_REWRITE_EQUIVALENCE
statement: enables the rewrite of top-level materialized views using submaterialized views. It is required to enable query rewrite when a composition occurs.
Note that the decomposition result implies no sharing of submaterialized views. That is, in the case of decomposition, the TUNE_MVIEW
output will always contain new submaterialized view and it will not reference existing materialized views.
The output statements for the UNDO
process include:
DROP
MATERIALIZED
VIEW
statements to reverse the materialized view creations (including submaterialized views) in the IMPLEMENTATION
process.
DROP_REWRITE_EQUIVALENCE
statement to remove the rewrite equivalence relationship built in the IMPLEMENTATION
process if needed.
Note that the UNDO
process does not include statement to drop materialized view logs. This is because materialized view logs can be shared by many different materialized views, some of which may reside on remote Oracle instances.
There are two ways to access TUNE_MVIEW
output results:
Script generation using DBMS_ADVISOR.GET_TASK_SCRIPT
function and DBMS_ADVISOR.CREATE_FILE
procedure.
Use USER_TUNE_MVIEW
or DBA_TUNE_MVIEW
views.
After executing TUNE_MVIEW
, the results are output into the SQL Access Advisor repository tables and are accessible through the Oracle views, USER_TUNE_MVIEW
and DBA_TUNE_MVIEW
. See Oracle Database Reference for further details.
The most straightforward method for generating the execution scripts for a recommendation is to use the procedure DBMS_ADVISOR.GET_TASK_SCRIPT
. The following is a simple example. First, a directory must be defined which is where the results will be stored:
CREATE DIRECTORY TUNE_RESULTS AS '/tmp/script_dir'; GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC;
Now generate both the implementation and undo scripts and place them in /tmp/script_dir/mv_create.sql
and /tmp/script_dir/mv_undo.sql
, respectively.
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name),- 'TUNE_RESULTS', 'mv_create.sql'); EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name, - 'UNDO'), 'TUNE_RESULTS', 'mv_undo.sql');
Now let us review some examples using the TUNE_MVIEW
procedure.
Example 17-3 Optimizing the Defining Query for Fast Refresh
This example shows how TUNE_MVIEW
changes the defining query to be fast refreshable. A CREATE
MATERIALIZED
VIEW
statement is defined in variable create_mv_ddl
, which includes a FAST
REFRESH
clause. Its defining query contains a single query block in which an aggregate column, SUM(s.amount_sold)
, does not have the required aggregate columns to support fast refresh. If you execute the TUNE_MVIEW
statement with this MATERIALIZED
VIEW
CREATE
statement, the resulting materialized view recommendation will be fast refreshable:
VARIABLE task_cust_mv VARCHAR2(30); VARIABLE create_mv_ddl VARCHAR2(4000); EXECUTE :task_cust_mv := 'cust_mv'; EXECUTE :create_mv_ddl := ' CREATE MATERIALIZED VIEW cust_mv REFRESH FAST DISABLE QUERY REWRITE AS SELECT s.prod_id, s.cust_id, SUM(s.amount_sold) sum_amount FROM sales s, customers cs WHERE s.cust_id = cs.cust_id GROUP BY s.prod_id, s.cust_id'; EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);
The original defining query of cust_mv
has been modified by adding aggregate columns in order to be fast refreshable.
The output from TUNE_MVIEW
includes an optimized materialized view defining query as follows:
CREATE MATERIALIZED VIEW SH.CUST_MV REFRESH FAST WITH ROWID DISABLE QUERY REWRITE AS SELECT SH.SALES.PROD_ID C1, SH.CUSTOMERS.CUST_ID C2, SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.SALES, SH.CUSTOMERS WHERE SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID GROUP BY SH.SALES.PROD_ID, SH.CUSTOMERS.CUST_ID;
The UNDO
output is as follows:
DROP MATERIALIZED VIEW SH.CUST_MV;
Example 17-4 Access IMPLEMENTATION Output Through USER_TUNE_MVIEW View
SELECT STATEMENT FROM USER_TUNE_MVIEW WHERE TASK_NAME= :task_cust_mv AND SCRIPT_TYPE='IMPLEMENTATION';
Example 17-5 Save IMPLEMENTATION Output in a Script File
CREATE DIRECTORY TUNE_RESULTS AS '/myscript' GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC; EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_cust_mv), - 'TUNE_RESULTS', 'mv_create.sql');
Example 17-6 Enable Query Rewrite by Creating Multiple Materialized Views
This example shows how a materialized view's defining query with set operators UNION
, which is not supported by query rewrite, can be decomposed into a number of submaterialized views and then query rewrite is possible. The input detail tables are assumed to be sales
, customers
, and countries
, and they do not have materialized view logs.First, you need to execute the TUNE_MVIEW
statement with the CREATE
MATERIALIZED
VIEW
statement defined in the variable create_mv_ddl
.
EXECUTE :task_cust_mv := 'cust_mv2'; EXECUTE :create_mv_ddl := ' CREATE MATERIALIZED VIEW cust_mv ENABLE QUERY REWRITE AS SELECT s.prod_id, s.cust_id, COUNT(*) cnt, SUM(s.amount_sold) sum_amount FROM sales s, customers cs, countries cn WHERE s.cust_id = cs.cust_id AND cs.country_id = cn.country_id AND cn.country_name IN (''USA'',''Canada'') GROUP BY s.prod_id, s.cust_id UNION SELECT s.prod_id, s.cust_id, COUNT(*) cnt, SUM(s.amount_sold) sum_amount FROM sales s, customers cs WHERE s.cust_id = cs.cust_id AND s.cust_id IN (1005,1010,1012) GROUP BY s.prod_id, s.cust_id';
The materialized view defining query contains a UNION
set operator that does not support general query rewrite but if it is decomposed into multiple materialized views, query rewrite is possible. In order to support general query rewrite, the MATERIALIZED
VIEW
defining query will be decomposed.
EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);
The following recommendation from TUNE_MVIEW
is comprised of the materialized view logs and multiple materialized view:
CREATE MATERIALIZED VIEW LOG ON "SH"."CUSTOMERS" WITH ROWID, SEQUENCE("CUST_ID") INCLUDING NEW VALUES; ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."CUSTOMERS" ADD ROWID, SEQUENCE("CUST_ID") INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON "SH"."SALES" WITH ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD") INCLUDING NEW VALUES; ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."SALES" ADD ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD") INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON "SH"."COUNTRIES" WITH ROWID, SEQUENCE("COUNTRY_ID","COUNTRY_NAME") INCLUDING NEW VALUES; ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."COUNTRIES" ADD ROWID, SEQUENCE("COUNTRY_ID","COUNTRY_NAME") INCLUDING NEW VALUES; ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."CUSTOMERS" ADD ROWID, SEQUENCE("CUST_ID","COUNTRY_ID") INCLUDING NEW VALUES; ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."SALES" ADD ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD") INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW SH.CUST_MV$SUB1 REFRESH FAST WITH ROWID ON COMMIT ENABLE QUERY REWRITE AS SELECT SH.SALES.PROD_ID C1, SH.CUSTOMERS.CUST_ID C2, SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.SALES, SH.CUSTOMERS WHERE SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID AND (SH.SALES.CUST_ID IN (1012, 1010, 1005)) GROUP BY SH.SALES.PROD_ID, SH.CUSTOMERS.CUST_ID; CREATE MATERIALIZED VIEW SH.CUST_MV$SUB2 REFRESH FAST WITH ROWID ON COMMIT ENABLE QUERY REWRITE AS SELECT SH.SALES.PROD_ID C1, SH.CUSTOMERS.CUST_ID C2, SH.COUNTRIES.COUNTRY_NAME C3, SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES". "AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.SALES, SH.CUSTOMERS, SH.COUNTRIES WHERE SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID AND SH.COUNTRIES.COUNTRY_ID = SH.CUSTOMERS.COUNTRY_ID AND (SH.COUNTRIES.COUNTRY_NAME IN ('USA', 'Canada')) GROUP BY SH.SALES.PROD_ID, SH.CUSTOMERS.CUST_ID, SH.COUNTRIES.COUNTRY_NAME; CREATE MATERIALIZED VIEW SH.CUST_MV REFRESH FORCE WITH ROWID ENABLE QUERY REWRITE AS (SELECT "CUST_MV$SUB2"."C1" "PROD_ID","CUST_MV$SUB2"."C2" "CUST_ID",SUM("CUST_MV$SUB2"."M3") "CNT",SUM("CUST_MV$SUB2"."M1") "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB2" "CUST_MV$SUB2" GROUP BY "CUST_MV$SUB2"."C1","CUST_MV$SUB2"."C2")UNION (SELECT "CUST_MV$SUB1"."C1" "PROD_ID","CUST_MV$SUB1"."C2" "CUST_ID",SUM("CUST_MV$SUB1"."M3") "CNT",SUM("CUST_MV$SUB1"."M1") "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" GROUP BY "CUST_MV$SUB1"."C1","CUST_MV$SUB1"."C2"); BEGIN DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE ('SH.CUST_MV$RWEQ', 'SELECT s.prod_id, s.cust_id, COUNT(*) cnt, SUM(s.amount_sold) sum_amount FROM sales s, customers cs, countries cn WHERE s.cust_id = cs.cust_id AND cs.country_id = cn.country_id AND cn.country_name IN (''USA'',''Canada'') GROUP BY s.prod_id, s.cust_id UNION SELECT s.prod_id, s.cust_id, COUNT(*) cnt, SUM(s.amount_sold) sum_amount FROM sales s, customers cs WHERE s.cust_id = cs.cust_id AND s.cust_id IN (1005,1010,1012) GROUP BY s.prod_id, s.cust_id', '(SELECT "CUST_MV$SUB2"."C3" "PROD_ID","CUST_MV$SUB2"."C2" "CUST_ID", SUM("CUST_MV$SUB2"."M3") "CNT", SUM("CUST_MV$SUB2"."M1") "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB2" "CUST_MV$SUB2" GROUP BY "CUST_MV$SUB2"."C3","CUST_MV$SUB2"."C2") UNION (SELECT "CUST_MV$SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID", "CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1")',-1553577441) END; /;
The DROP
output is as follows:
DROP MATERIALIZED VIEW SH.CUST_MV$SUB1 DROP MATERIALIZED VIEW SH.CUST_MV$SUB2 DROP MATERIALIZED VIEW SH.CUST_MV DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE('SH.CUST_MV$RWEQ')
The original defining query of cust_mv
has been decomposed into two submaterialized views seen as cust_mv$SUB1
and cust_mv$SUB2
. One additional column COUNT(amount_sold)
has been added in cust_mv$SUB1
to make that materialized view fast refreshable.
The original defining query of cust_mv
has been modified to query the two submaterialized views instead where both submaterialized views are fast refreshable and support general query rewrite.
The required materialized view logs are added to enable fast refresh of the submaterialized views. Note that, for each detail table, two materialized view log statements are generated: one is the CREATE
MATERIALIZED
VIEW
statement and the other is an ALTER
MATERIALIZED
VIEW
FORCE
statement. This is to ensure the CREATE
script can be run multiple times.
The BUILD_SAFE_REWRITE_EQUIVALENCE
statement is to connect the old defining query to the defining query of the new top-level materialized view. It is to ensure that query rewrite will make use of the new top-level materialized view to answer the query.
Example 17-7 Access IMPLEMENTATION Output Through USER_TUNE_MVIEW View
SELECT * FROM USER_TUNE_MVIEW WHERE TASK_NAME='cust_mv2' AND SCRIPT_TYPE='IMPLEMENTATION';
Example 17-8 Save IMPLEMENTATION Output in a Script File
The following statements save the IMPLEMENTATION
output in a script file located at /myscript/mv_create2.sql
:
CREATE DIRECTORY TUNE_RESULTS AS '/myscript' GRANT READ, WRITE ON DIRECTRY TUNE_RESULTS TO PUBLIC; EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('cust_mv2'), 'TUNE_RESULTS', 'mv_create2.sql');
The example illustrates how TUNE_MVIEW
can optimize the materialized view so that fast refresh is possible. In the example, the materialized view's defining query with set operators is transformed into one sub-materialized view and one top-level materialized view. The subselect queries in the original defining query are of similar shape and their predicate expressions are combined.
The materialized view defining query contains a UNION
set-operator so that the materialized view itself is not fast-refreshable. However, two subselect queries in the materialized view defining query can be combined as one single query.
EXECUTE :create_mv_ddl := ' CREATE MATERIALIZED VIEW cust_mv REFRESH FAST ON DEMAND ENABLE QUERY REWRITE AS SELECT s.prod_id, s.cust_id, COUNT(*) cnt, SUM(s.amount_sold) sum_amount FROM sales s, customers cs WHERE s.cust_id = cs.cust_id AND s.cust_id IN (2005,1020) GROUP BY s.prod_id, s.cust_id UNION SELECT s.prod_id, s.cust_id, COUNT(*) cnt, SUM(s.amount_sold) sum_amount FROM sales s, customers cs - WHERE s.cust_id = cs.cust_id AND s.cust_id IN (1005,1010,1012) GROUP BY s.prod_id, s.cust_id'; EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);
The following recommendation will be made by TUNE_MVIEW
with an optimized submaterialized view combining the two subselect queries and the submaterialized view is referenced by a new top-level materialized view as follows:
CREATE MATERIALIZED VIEW LOG ON "SH"."SALES" WITH ROWID, SEQUENCE ("PROD_ID","CUST_ID","AMOUNT_SOLD") INCLUDING NEW VALUES ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."SALES" ADD ROWID, SEQUENCE ("PROD_ID","CUST_ID","AMOUNT_SOLD") INCLUDING NEW VALUES CREATE MATERIALIZED VIEW LOG ON "SH"."CUSTOMERS" WITH ROWID, SEQUENCE ("CUST_ID") INCLUDING NEW VALUES ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."CUSTOMERS" ADD ROWID, SEQUENCE ("CUST_ID") INCLUDING NEW VALUES CREATE MATERIALIZED VIEW SH.CUST_MV$SUB1 REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD")M2, COUNT(*) M3 FROM SH.CUSTOMERS, SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND (SH.SALES.CUST_ID IN (2005, 1020, 1012, 1010, 1005)) GROUP BY SH.SALES.CUST_ID, SH.SALES.PROD_ID CREATE MATERIALIZED VIEW SH.CUST_MV REFRESH FORCE WITH ROWID ENABLE QUERY REWRITE AS (SELECT "CUST_MV$SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID", "CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" WHERE "CUST_MV$SUB1"."C1"=2005 OR "CUST_MV$SUB1"."C1"=1020) UNION (SELECT "CUST_MV$SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID", "CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" WHERE "CUST_MV$SUB1"."C1"=1012 OR "CUST_MV$SUB1"."C1"=1010 OR "CUST_MV$SUB1"."C1"=1005) DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE ('SH.CUST_MV$RWEQ', 'SELECT s.prod_id, s.cust_id, COUNT(*) cnt, SUM(s.amount_sold) sum_amount FROM sales s, customers cs WHERE s.cust_id = cs.cust_id AND s.cust_id IN (2005,1020) GROUP BY s.prod_id, s.cust_id UNION SELECT s.prod_id, s.cust_id, COUNT(*) cnt, SUM(s.amount_sold) sum_amount FROM sales s, customers cs WHERE s.cust_id = cs.cust_id AND s.cust_id IN (1005,1010,1012) GROUP BY s.prod_id, s.cust_id', '(SELECT "CUST_MV$SUB1"."C2" "PROD_ID", "CUST_MV$SUB1"."C1" "CUST_ID", "CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" WHERE "CUST_MV$SUB1"."C1"=2005OR "CUST_MV$SUB1"."C1"=1020) UNION (SELECT "CUST_MV$SUB1"."C2" "PROD_ID", "CUST_MV$SUB1"."C1" "CUST_ID", "CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" WHERE "CUST_MV$SUB1"."C1"=1012 OR "CUST_MV$SUB1"."C1"=1010 OR "CUST_MV$SUB1"."C1"=1005)', 1811223110);
The original defining query of cust_mv
has been optimized by combining the predicate of the two subselect queries in the sub-materialized view CUST_MV$SUB1
. The required materialized view logs are also added to enable fast refresh of the submaterialized views.
The DROP
output is as follows:
DROP MATERIALIZED VIEW SH.CUST_MV$SUB1 DROP MATERIALIZED VIEW SH.CUST_MV DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE('SH.CUST_MV$RWEQ');
The following statements save the IMPLEMENTATION
output in a script file located at /myscript/mv_create3.sql
:
CREATE DIRECTORY TUNE_RESULTS AS '/myscript' GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC; EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('cust_mv3'), 'TUNE_RESULTS', 'mv_create3.sql');
In addition to using the DBMS_ADVISOR
package to accomplish basic Advisor tasks, you can also use the SQL Access Advisor Wizard in Enterprise Manager to define appropriate structures such as indexes and materialized views for optimizing SQL queries.
To run this advisor, navigate to the Advisor Central page and click SQL Access Advisor. The SQL Access Advisor page is displayed. The following steps illustrate an example of how to generate and then implement recommendations from there:
The following choices are available for where your workload source can come from:
Use Default Options
Inherit Options from a Task or Template
Figure 17-3 shows the initial screen. In this example, choose Use Default Options and click Continue.
Figure 17-3 SQL Access Advisor: Selecting the Initial Options
After choosing the initial options, you then need to choose where the workload source should come from. The following choices are available:
Current and Recent SQL Activity
Import Workload from SQL Repository
User-Defined Workload; Import SQL from a Table or View
Create a Hypothetical Workload from the Following Schemas and Tables
This example uses SQL from the SQL cache, so select Current and Recent SQL Activity, as shown in Figure 17-4.
Extracting the entire contents of the SQL cache will result in all SQL statements for all applications being used as the workload. By clicking on Filter Options, you can filter the workload using a variety of options. In this example, only SQL statements executed by user sh
will be considered.
To use SQL from the cache, you will need to save some SQL statements as a script and then load the script. To do this, first save the following statement as advis.sql
:
ALTER SYTEM FLUSH shared_pool; GRANT ADVISOR TO sh; CONNECT sh/sh; SELECT c.country_id, c.cust_city, c.cust_last_name FROM sh.customers c WHERE c.country_id in (''US'', ''UK'') ORDER BY c.country_id, c.cust_city, c.cust_last_name;
Then, load advis.sql
from iSQL*Plus.
Next, click Filter Options, which brings up more options. Then select Filter Workload Based on these Options and check Only SQL statements executed by the following users. Then enter SH in the Users field and click Next. The Recommendation Options page is displayed.
Figure 17-4 SQL Access Advisor: Choosing the Workload Source
From the Recommendation Options page, as shown in Figure 17-5, you can choose whether you want recommendations for indexes, materialized views, or both indexes and materialized views. For this example, select Indexes. Next, you can choose whether you want a Limited Mode or a Comprehensive Mode. For this example, choose Limited Mode, and do not make any selections under Advanced Options. Then click Next, and the Schedule page is displayed.
Figure 17-5 SQL Access Advisor: Choosing the Recommendation Options
From the Schedule page, as shown in Figure 17-6, enter a task name, select Standard from the Schedule Type drop-down list, and select the time you want the Advisor Task to execute. For this example, select Immediately, and click Next. The Review page is displayed.
From the Review page, as shown in Figure 17-7, review your choices and click Submit. A Confirmation page is displayed.
Figure 17-7 SQL Access Advisor: Reviewing Your Selections
After the task has executed, a page listing scheduled tasks in the Advisor Central is displayed, as shown in Figure 17-8. You may have to refresh the screen to get the most current information.
Next, click View Result, and a more detailed Results page is displayed, as shown in Figure 17-9.
Figure 17-9 SQL Access Advisor: Overview of the Recommendations
In Figure 17-9, you can see the potential increase in workload I/O and query execution performance as well as the SQL statements needed to achieve these improvements. You can then drill down on the results by clicking the recommendations or SQL statements. Figure 17-10 illustrates the details.
You can also see the suggested SQL statements in Figure 17-11. These statements will create indexes that should improve performance significantly.