10g Release 1 (10.1)
Part Number B10736-01
 Home |
 Book List |
 Contents |
 Index< /font> |
 Master Index |
 Feedback |
| Oracle® Database
Data Warehousing Guide 10g Release 1 (10.1) Part Number B10736-01 |
|
|
View PDF |
Thi s chapter illustrates how to use the SQLAccess Advisor, which is a tuning tool that provides advice on materialized views, indexes, a nd materialized view logs. The chapter contains:
Overview of the SQLAccess Advisor i n the DBMS_ADVISOR Package
Tuning Materialized Views for Fast Refresh and Query Rewrite
Materialize d views and indexes are essential when tuning a database to achieve optimum performance for complex, data-intensive queries. The SQLAccess Advi sor helps you achieve your performance goals by recommending the proper set of materialized views, materialized view logs, and indexe s for a given workload. Understanding and using these structures is essential when optimizing SQL as they can result in significant p erformance improvements in data retrieval. The advantages, however, do not come without a cost. Creation and maintenance of these obj ects can be time consuming, and space requirements can be significant.
The SQLAccess Advisor recommends both bitmap indexes an d B-tree indexes. A bitmap index offers a reduced response time for many types of ad hoc queries and reduced storage requirements com pared 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 SQLAccess Advisor also recommends how to optimize materialized views so that they can be fast refreshab le and take advantage of general query rewrite.
The SQLAccess Advisor can be run from Oracle Enterprise Manager (accessible fr
om the Advisor Central page) using the SQLAccess 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. <
a href="#i1033349">Figure 17-1 illustrates how the SQLAccess 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 SQLAccess Advisor
Using the SQLAccess Advisor Wizard or API, you can do the following:
Recommend materialized views and indexes based on collected or hypothetical workload inf ormation.
Manage workloads.
Mark, update, and remove recommendations.
In addition, you can use the SQLAccess 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 SQLAccess 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 esti
mate 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 a
nalyzed. See PL/SQL Packages and Types Reference for mo
re information regarding the DBMS_STATS package.
One of the easiest wa
ys to use the SQLAccess Advisor is to invoke its wizard, which is available in Oracle Enterprise Manager from the Advisor Central pag
e. If you prefer to use SQLAccess 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
Befor
e 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 SQLAccess 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 SQLAccess Advisor may recom mend dropping certain existing materialized views and indexes if it finds that they are not being used effectively.
Typically, the SQLAccess Advisor uses the workload as the basis for all analysis activities. Although the workload may contain a wide variety o f statements, it carefully ranks the entries according to a specific statistic, business importance, or a combination of statistics a nd business importance. This ranking is critical in that it enables the SQLAccess Advisor to process the most important SQL statement s ahead of those with less business impact.
For a collection of data to be considered a valid workload, the SQLAccess 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 SQLAccess 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
SQLAccess Advisor may be able to better evaluate the current efficiency of the statement. The workload is stored as a separate objec
t, 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 t
he workload. A workload reference will be removed when a parent Advisor task is deleted or when the workload reference is manually re
moved from the Advisor task by the user using the DBMS_ADVISOR.DELETE_SQLWKLD_REF procedure.
You can use the SQLA ccess Advisor without a workload, however, for best results, a workload must be provided in the form of a user-supplied table, SQL Tu ning Set or imported from the SQL Cache. If a workload is not provided, the SQLAccess Advisor can generate and use a hypothetical wor kload based on the dimensions defined in your schema.
Once the workload is loaded into the repository or at the time the recom mendations 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 SQLAccess 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, para meters 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 procedur
e, it does not change until you make another call to SET_TASK_PARAMETER.
See "Defining the Co ntents of a Workload" 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 SQLAccess Advisor Repository.
The re commendation 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 simp le suggestion to a complex solution that requires implementation of a set of database objects such as indexes, materialized views, an d materialized view logs. When an Advisor task is executed, it carefully analyzes collected data and user-adjusted task parameters. T he SQLAccess Advisor will then attempt to form a resolution based on its built-in knowledge. The resolutions are then refined and sto red in the form of a structured recommendation that can be viewed and implemented by the user.
See "Genera ting Recommendations" for more information about generating recommendations.
There are two ways to view the recommendations from the SQLAccess Advisor: using the catalog views or by generat
ing a script using the DBMS_ADVISOR.GET_TASK_SCRIPT procedure. In Enterprise Manager, the recommendations may be display
ed once the SQLAccess Advisor process has completed.
See "Viewing the Recommendations" for a descripti on 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 SQLAccess 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 SQLAccess Advisor.
Supports historical dat a.
Is managed by the server.
This section discusses the steps in using the SQLAccess Advisor, and includes:
< a href="#i1009010">Removing Workloads
Figure 17-2 illustrates the steps in using the SQLAccess Advisor as well as an ov erview of all of the parameters in the SQLAccess Advisor and when it is appropriate to use them.
Y
ou need to have the ADVISOR privilege
to manage or use the SQLAccess Advisor. When processing a workload, the SQLAccess Advisor attempts to validate each statement in orde
r to identify table and column references. Validation is achieved by processing each statement as if it is being executed by the stat
ement's original user. If that user does not have SELECT privileges to a particular table, the SQLAccess Advisor bypasse
s the statement referencing the table. This can cause many statements to be excluded from analysis. If the SQLAccess Advisor excludes
all statements in a workload, the workload is invalid and the SQLAccess 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.
An Advisor task is where you define what it is you want to analyze and where the results of t his analysis should go. A user can create any number of tasks, each with its own specialization. All are based on the same Advisor ta sk model and share the same repository.
You create a task using the CREATE_TASK procedure. The syntax is as follo
ws:
DBMS_ADVISOR.CREATE_TASK ( advisor_name IN VARCHAR2, task_id OUT NUMBE R, task_name IN OUT VARCHAR2, task_desc IN VARCHAR2 := NULL, task_or_template IN VARCHAR2 := NU LL, is_template IN VARCHAR2 := 'FALSE');
The following illustrates an example of using this procedure:
< pre xml:space="preserve">VARIABLE task_id NUMBER; VARIABLE task_name VARCHAR2(255); EXECUTE :task_name := 'MYTASK'; EXECUTE DBMS_ADVI SOR.CREATE_TASK ('SQL Access Advisor', :task_id, :task_name);See PL/SQL Packages and Types Reference for more information regarding the CREATE_TASK and CREATE_SQLWKLD procedures and their parameters.
When an ide al configuration for a task or workload has been identified, this configuration can be saved as a template upon which future tasks an d 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 e nables you to custom fit a tuning analysis to the business operation.
To create a task from a template, you specify the templa
te to be used when a new task is created. At that time, the SQLAccess Advisor copies the data and parameter settings from the templat
e 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 SQLAccess Adv isor to use a task when a new task is created. At that time, the SQLAccess Advisor copies the task template's data and parameter sett ings into the newly created task. You can also set an existing task to be a template by setting the template attribute.
A work load object can also be used as a template for creating new workload objects. Following the same guidelines for using a task as a tem plate, 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
Previous
VARIABLE template_id NUMBER;
VARIABLE template_name VARCHAR2(255);
EXECUTE :templ
ate_name := 'MY_TEMPLATE';
EXECUTE DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor',:template_id, -
:te
mplate_name, is_template => 'TRUE');
Set template parameters. For example, the following sets the naming con ventions for recommended indexes and materialized views and the default tablespaces:
-- set naming conv entions for recommended indexes/mvs EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( - :template_name, 'INDEX_NAME_TEMPLATE', 'SH_IDX$$_& lt;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', :tas
k_id, -
:task_name, template=>'MY_TEMPLATE');
The following example uses a pre-define
d 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');
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 modi fied until all Advisor tasks have removed their dependency on the data. A workload reference will be removed when a parent Advisor ta sk is deleted or when the workload reference is manually removed from the Advisor task by the user.
The SQLAccess Advisor perf orms best when a workload based on usage is available. The SQLAccess 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 t ime and across the life cycle of database instance startup and shutdown.
Before the actual SQL statements for a workload can b
e defined, the workload must be created using the CREATE_SQLWKLD procedure. Then, the workload is loaded using the appro
priate 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 co
nstant value ADVISOR_ALL or %.
The CREATE_SQLWKLD procedure creates a workload and it must exist prior to performing any other wor
kload operations, such as importing or updating SQL statements. The workload is identified by its name, so you should define a name t
hat is unique and is relevant for the operation.
Its syntax is as follows:
DBMS_ADVISOR.CREATE_S QLWKLD ( workload_name IN VARCHAR2, description IN VARCHAR2 := NULL, template IN VAR CHAR2 := NULL. is_template IN BOOLEAN);
The following examples illustrate using this procedure:
Example 17-1 Crea ting a Workload
VARIABLE workload_name VARCHAR2(255); EXECUTE :workload_name := 'M YWORKLOAD'; 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(:t emplate_name, is_template=>'TRUE');
Set template parameters. For example, the following sets the filter so o
nly tables in the sh schema are tuned:
-- set USERNAME_LIST filter to SH EXECUTE DBMS_ADVI SOR.SET_SQLWKLD_PARAMETER( - :template_name, 'USERNAME_LIST', 'SH');
Now create a workload using the templat e:
VARIABLE workload_name VARCHAR2(255); EXECUTE :workload_name := 'MYWORKLOAD'; EXECUTE DBMS_ADVISOR.C REATE_SQLWKLD ( - :workload_name, 'This is my first workload', 'MY_WK_TEMPLATE');
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 b
y 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);
T
he following example links the MYTASK task created to the MYWORKLOAD SQL workload.
EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF('MYTASK', 'MYWORKLOAD');
See 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 info rmation. Ideally, a workload will consist of the SQL statements (unless it is a hypothetical workload) that are being used against th e database. The SQLAccess 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 SQLAccess Advisor by importing it using the IMPORT_WORKLOAD_STS procedure. The syntax of this procedur
e is as follows:
DBMS_ADVISOR.IMPORT_SQLWKLD_STS (workload_name IN VARCHAR2,
sqlset_name IN VARCHAR2,
import_mode IN VARCHAR2 := 'NEW',
priority IN NUMBER := 2,
saved_rows OUT NUMBER,
fai
led_rows OUT NUMBER);
After a workload has been collected and the statements filtered, the SQLAccess Advisor computes usage statistics with respect to the DML statements in the workload.
The following example creates a workload from a SQL Tunin
g Set named MY_STS_WORKLOAD.
VARIABLE sqlsetname VARCHAR2(30);
VARIABLE workload_name VAR
CHAR2(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 par
ameters, owner_name and table_name identify the table where the workload is to be retrieved from.
Th
ere are no restrictions on which schema the workload resides in, the name for the table, or how many of these user-defined tables exi
st. 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 i
s as follows:
DBMS_ADVISOR.IMPORT_SQLWKLD_USER (
workload_name IN VARCHAR2,
import_mode
IN VARCHAR2,
owner_name IN VARCHAR2,
table_name IN VARCHAR2,
saved_rows OUT NUMBER
,
failed_rows OUT NUMBER);
The following example loads MYWORKLOAD workload created earlier, usi
ng a user table SH.USER_WORKLOAD. The table is assumed to be populated with SQL statements and conforms to the format sp
ecified in Table 17-1.
VARIABLE saved_stmts NUMBER; VARIABLE failed_stmts NUMBE R; EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_USER( - 'MYWORKLOAD', 'NEW', 'SH', 'USER_WORKLOAD', :saved_stmts, :failed_stmts);
See 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
| Co lumn | Type | Default | Comments | < /tr>
|---|---|---|---|
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 val ue for executing the query. |
LAST_EXECUTION_DATE |
DATE |
SYSDATE |
Last time the quer y is used. Defaults to not available. |
PRIORITY |
NUMBER |
2 |
Must be one of the foll
owing values:
1- |
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 cont
ents of the SQL cache are analyzed and placed into the workload. The IMPORT_SQLWKLD_SQLCACHE procedure loads a SQL workl
oad from the SQL cache. The syntax is as follows:
DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE ( workload_na me IN VARCHAR2, import_mode IN VARCHAR2, priority IN NUMBER := 2, saved_rows OUT N UMBER, failed_rows OUT NUMBER);
See PL/SQL Packages and Types Reference for more information regarding the IMPORT_SQLWKLD_SQLCACHE pro
cedure 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 f ailed_stmts NUMBER; EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE (- 'MYWORKLOAD', 'APPEND', 2, :saved_stmts, :failed_stmts);The SQLAccess Advisor can retrieve workload information from the SQL cache. If the collected data was retrieved from a server with the instance parameter
cursor_sharingset toSIMILARorFORCE, then user queries with emb edded literal values will be converted to a statement that contains system-generated bind variables. If you are going to use the SQLA ccess Advisor to recommend materialized views, then the server should set the instance parametercursor_sharingtoEXACTso that materialized views withWHEREclauses can be recommended.
In many situations, an application workload may not yet exist. In this case, the SQLAccess Advisor can examine the current logical schema design and form recommendations based on defined relationshi ps among tables. This type of workload is also referred to as a hypothetical workload. The SQLAccess Advisor can produce an initial s et 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 model ing what-if scenarios.
Some of the disadvantages of hypothetical workloads are that they:
Only work if dimensions or primary and foreign key constraints have been defined.
Offer no informatio n 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 or primary and foreign key informati
on. You use the IMPORT_SQLWKLD_SCHEMA procedure. The syntax is as follows:
DBMS_ADVISOR.IM PORT_SQLWKLD_SCHEMA ( workload_name IN VARCHAR2, import_mode IN VARCHAR2, priority IN VARCHAR 2, saved_rows OUT NUMBER, failed_rows OUT NUMBER);
See PL/SQL Packages and Types Reference for more information regarding the I
MPORT_SQLWKLD_SCHEMA procedure and its parameters. You must configure external procedures to use this procedure.
The fo
llowing 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_n
ame VARCHAR2(255);
VARIABLE saved_stmts NUMBER;
VARIABLE failed_stmts NUMBER;
EXECUTE :workload_name := 'SCHEMA_WKLD';
EXECUTE DBMS_A
DVISOR.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 wildca
rds such as SCO% or SCOTT.EMP%. The only form of wildcards supported is SCOTT.%, which specifi
es all tables in a given schema.
You may have created workloads using the Oracle9i Summary Advisor. These workloads can be used by the SQLAcces
s Advisor by importing them using the IMPORT_SQLWLD_SUMADV procedure. To use this procedure, you must know the Oracle9
This procedure collects a SQL workload from a Summary Advisor workload. This procedure is intended to as sist Oracle9i Summary Advisor users in the migration to SQLAccess Advisor. The syntax is as follows:
DBMS_ADVISOR.IMPORT_SQLWKLD_SUMADV (
workload_name IN VARCHAR2,
import_mode IN VARCHAR2,
priority
IN NUMBER := 2,
sumadv_id IN NUMBER,
saved_rows OUT NUMBER,
failed_rows OUT NUM
BER);
See PL/SQL Packages and Types
Reference for more information regarding the IMPORT_SQLWKLD_SUMADV procedure and its parameters.
The fol
lowing example creates a SQL workload from a Oracle9i Summary Advisor workload. The workload_id of the Oracle9<
em>i workload is 777.
Create some variables.
VARIABLE workload _name VARCHAR2(255); VARIABLE saved_stmts NUMBER; VARIABLE failed_stmts NUMBER;
Create a workload named W
KLD_9I.
EXECUTE :workload_name := 'WKLD_9I'; EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name) ;
Import the workload from Oracle9i Summary Advisor.
EXECUTE DBMS_ADVISO R.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 PL/SQL Packages and Types
Reference using SET_SQLWKLD_PARAMETER.
The following example illustrates setting of SQL Workload paramet
ers. Here we set the SQL_LIMIT to 3 and ORDER_LIST to OPTIMIZER_COST. This means that when imp
orting the workload, the statements will be ordered by OPTIMIZER_COST and the top three statements will be kept.
During the import of a workload, various informational me
ssages are recorded in the SQL Workload Journal. These can be viewed using the view USER_ADVISOR_SQLW_JOURNAL. The journ
al is useful to identify why some statements were filtered out of the workload. For example, if a certain SQL statement refers to inv
alid tables, tables with missing statistics or has privilege errors, the information will be recorded in the journal. The amount of i
nformation output can be controlled by setting the JOURNALING parameter.
You can turn journaling off before impor ting workload as follows:
EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER('MYWORKLOAD', 'JOURNALING', 0);
<
/pre>
To view only fatal messages:
EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER('MYWORKLOAD', 'JOURNA
LING', 4);
See PL/SQL Packages and Types Reference for details of all the settings for the JOURNALING parameter.
The information in the journal is for diagn
ostic purposes only and subject to change in future releases. It should not be used within any application.
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 proce
dure 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 := 'SYS
DATE',
stat_period IN NUMBER := 0,
username IN VARCHAR2,
sql_text IN CLOB);
See 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_ADVISO
R.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 proced
ure. Its syntax has two forms. The first form 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 second form lets you delete statements that match a specified search condition.
DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT (workload_name IN VARCHAR2,
search IN V
ARCHAR2,
deleted OUT NUMBER);
The following example deletes from MYWO
RKLOAD with sql_id 10:
EXECUTE DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT('MYWORKLOAD',
10);
The following example deletes from MYWORKLOAD all statements that satisfy the condition executions less
than 5:
VARIABLE deleted_stmts NUMBER; EXECUTE DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT ( - 'MYWORKLOA D', 'executions < 5', :deleted_stmts);
A workload cannot be modified or deleted if it is currently referenced by an ac
tive 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 PL/SQL Packages and Types
Reference for more information regarding the DELETE_SQLWKLD_STATEMENT procedure and its parameters.
You can modify SQL statemen
ts in a workload by using the UPDATE_SQLWKLD_STATEMENT procedure. This procedure updates an existing SQL statement in th
e specified workload.
The syntax takes two forms. The first form enables you to update a SQL statement by specifying its sql_id.
DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT (workload_name IN VARCHAR2,
sql_id IN NUMBER,
module IN VARCHAR2,
action IN VARCHAR2,
priority IN NUMBER,
username IN VARCHAR2);
The second forms enables you to update all SQL statements satisfying a give n search condition.
DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT (workload_name IN VARCHAR2,
search IN VARCHAR2,
updated OUT NUMBER,
module IN VARCHAR2,
action IN VARCHAR2,
priority IN NUMBER,
username IN VARCHAR2);
The following example changes the priority to 3 for statement id 10:
EXECUTE DBMS_ADVISOR.UPDATE_SQLWKLD_S
TATEMENT('MYWORKLOAD', 10, priority=>3);
The following examples changes
the priority to 3 for all statements in MYWORKLOAD that have executions less than 10. The count of updated statements is
returned in the updated_stmts variable.
VARIABLE updated_stmts NUMBER; EXECUTE DBMS_ADVIS OR.UPDATE_SQLWKLD_STATEMENT ( - 'MYWORKLOAD', 'executions < 10', :updated_stmts, priority => 3);
See PL/SQL Packages and Types Reference for more infor
mation 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 wor
kload object or template. Some of these attributes are its description, and whether it is a template or read only. The syntax is as f
ollows:
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,
source IN VARCHAR2 := NULL);
The following example changes the workload
EXECUTE DBMS_ADVISOR.UPDATE_SQLWKLD_ATTRIBUTES ( - 'MYWORKLOAD', read_only=> 'TRUE');
See PL/SQL
Packages and Types Reference for more information regarding the UPDATE_SQLWKLD_ATTRIBUTES procedure and its par
ameters.
The RESET_SQLWKLD procedure resets a workload to its initial starting point. This has the effect of removing all journal and log messages, recalcu
lating volatility statistics, while the workload data remains untouched. This procedure should be executed after any workload adjustm
ents such as adding or removing SQL statements. The following example resets workload MYWORKLOAD.
EXECUTE DBMS_ADVISOR.RESET_SQLWKLD('MYWORKLOAD');
See 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 be
tween the task and the workload must be removed using DELETE_SQLWKLD_REF procedure. The following example deletes the li
nk between task MYTASK and SQL workload MYWORKLOAD:
EXECUTE DBMS_ADVISOR.DELE
TE_SQLWKLD_REF('MYTASK', 'MYWORKLOAD');
When workloads are no longer needed, they can be removed using the procedure DELETE_SQLWK
LD. 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 PL/SQL Packages and Types Ref
erence for more information regarding the DELETE_SQLWKLD procedure and its parameters.
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 defau
lts 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);
There are many task parameters and, to help identify the relevant ones, they have be en 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 |
|---|---|---|---|
ACTION_LIST |
DAYS_TO_EXPIRE |
DEF_INDEX_OWNER |
DML_VOLATILITY |
MODULE_LIST |
REPORT_DATE_FORMAT |
DEF_INDEX_TABLESPACE |
EVALUATION_ONLY |
ORDER_LIST |
JOURNALING |
DEF_MVIEW_OWNER |
EXECUTION_TYPE | <
/tr>
SQL_LIMIT |
DEF_MVI EW_TABLESPACE |
MODE | |
START_TIME |
DEF_MVLOG_TABLSPACE |
REFRESH_MODE | |
USERNAME_LIST |
INDEX_NAME_TEMPLATE |
STORAGE_CHANGE | |
VALID_TABLE_LIST |
MVIEW_NAME_TEMPLATE |
CREATION_COST | |
WORKLOAD_SCOPE |
|||
MODULE_LIMIT |
|||
TIME_LIMIT |
|||
END_TIME |
|||
COMMENTED_FILTER_LIST |
In the following example, set th
e storage change of task MYTASK to 100MB. This indicates 100MB of additional space for recommendations. A zero value wou
ld indicate that no additional space can be allocated. A negative value indicates that the advisor must attempt to trim the current s
pace utilization by the specified amount.
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER('MYTASK','STORAGE_CHA
NGE', 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 PL/SQL Packages and Types Reference for more information regarding the
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 o
f recommendations and actions that have been produced. The recommendations can be queried by task name in {DBA, USER}_ADVISOR_R
ECOMMENDATIONS and the actions for these recommendations can be viewed by task in {DBA, USER}_ADVISOR_ACTIONS.
This procedure perfo
rms the SQLAccess Advisor analysis or evaluation for the specified task. Task execution is a synchronous operation, so control will n
ot 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 gener
ates recommendations, where a recommendation comprises one or more actions, such as creating a materialized view log and a materializ
ed 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 PL/SQL Packages and Types Refer
ence for more information regarding the EXECUTE_TASK procedure and its parameters.
Each recommendation gener
ated by the SQLAccess 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 SQLAccess Advisor in Enter
prise Manager, which graphically displays the recommendations and provides hyperlinks to quickly see which SQL statements benefit fro
m a recommendation. Each recommendation produced by the SQLAccess Advisor is linked to the SQL statement it benefits.
The foll
owing shows the recommendation (rec_id) produced by an Advisor run, with their rank and total benefit. The rank is a mea
sure 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, BEN
EFIT
FROM USER_ADVISOR_RECOMMENDATIONS WHERE TASK_NAME = :task_name;
REC_ID RANK BENEFIT
---------- ---------- --------
--
1 2 2754
2 3 1222
3 1 5499
4 4 59
4
To identify which query benefits from which recommendation, you can use the views DBA_* and USER_ADV
ISOR_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 on e or more actions, which must be implemented together to realize the benefit provided by the recommendation. The SQLAccess Advisor pr oduces 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 recommendatio
ns 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. Not
e 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 recommendation s.
SELECT 'Action Count', COUNT(DISTINCT action_id) cnt
FROM user_advisor_actions WHERE task_name = :ta
sk_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, act
ion_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 attr
1 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 SQLAccess Advisor action information to the
corresponding column in DBA_ADVISOR_ACTIONS.
Table 17-3 SQLAccess Advisor Action Att ributes
| ATTR1 | ATTR2 | ATTR3 | ATTR4 | ATTR5 | ATTR6 | ||
|---|---|---|---|---|---|---|---|
CREATE INDEX |
In dex name | Index tablespace | Target tabl e | BITMAP or BTREE |
Index column list | Unused | Stora ge size in bytes for the index |
CREATE<
/code> |
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 |
| Target table name | MV log tablespace | < td align="left" headers="r4c1-t4 r1c4-t4">INCLUDING NEW VALUES,
EXCLUDING NEW VALUES |
Table column list | < td align="left" headers="r4c1-t4 r1c7-t4">Partition-Unused | |||
CREATE REWRITE E
QUIVALENCE |
Name of equivalence | Checksum value | Unused | Unused | Equivalent SQL statemen t | 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 | < td align="left" headers="r7c1-t4 r1c5-t4">UnusedUnused | Unused | Storage size in bytes for the MV | |
DROP MATERIALIZED VIEW LO
G |
Target table name | Unused | Unused | Unused | Unused | Unused | U nused |
RETAIN INDEX
|
Index name | Unused | Target table | BITMAP or BTREE<
/td>
| Index columns | Unused | Storage size in bytes for the index |
RETAIN MATERIALIZED VIEW |
MV name | Unused | REFRESH |
Unused | 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 at tributes 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 tas
k_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_na
me);
LOOP
FETCH curs INTO
v_action, v_command, v_attr1, v_attr2, v_attr3, v_attr4 ;
EXIT when curs%NOTFOUND;
DB
MS_OUTPUT.PUT_LINE('Action ID: ' || v_action);
DBMS_OUTPUT.PUT_LINE('Command : ' || v_command);
DBMS_OUTPUT.PUT_LINE('Attr1 (na
me) : ' || SUBSTR(v_attr1,1,30));
DBMS_OUTPUT.PUT_LINE('Attr2 (tablespace): ' || SUBSTR(v_attr2,1,30));
DBMS_OUTPUT.PUT_LI
NE('Attr3 : ' || SUBSTR(v_attr3,1,30));
DBMS_OUTPUT.PUT_LINE('Attr4 : ' || v_attr4);
DBMS_OUTPUT.PUT_LI
NE('Attr5 : ' || v_attr5);
DBMS_OUTPUT.PUT_LINE('----------------------------------------');
END LOOP;
CLOS
E 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 (table
space):
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
At
tr5 :
----------------------------------------
..
----------------------------------------
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 PL/SQL Packages and Types Reference for details regarding Attr5 and Attr6.
During the anal
ysis process (EXECUTE_TASK), the Access Advisor will save useful information regarding the analysis to a journal. The jo
urnal can be viewed using the view USER_ADVISOR_JOURNAL. The amount of information output varies depending on the settin
g of task parameter JOURNALING.
You can turn journaling off as follows:
EXECUTE DBM
S_ADVISOR.SET_TASK_PARAMETER('MYTASK', 'JOURNALING', 0);
To view only informational messages:
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER('MYTASK', 'JOURNALING', 1);
See PL/SQL Packages and Types Reference for details of all the settings for the journaling p
arameter.The information in the journal is for diagnostic purposes only and subject to change in future releases. It should not be us
ed within any application.
If the SQLAccess Advisor takes too long to make its recommendations using the proced
ure EXECUTE_TASK, you can stop it by calling the CANCEL_TASK procedures and passing in the task_name for th
is recommendation process. If you use CANCEL_TASK, no recommendations will be made.
The CANCEL_TASK procedure causes a currently executing operation to terminate. An Advisor o
peration 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.
DBMS_ADVISOR.C ANCEL_TASK (task_name IN VARCHAR2);
The following illustrates an example of using this procedure:
EXECUTE DBMS_ADVISOR.CANCEL_TASK('MYTASK');
See PL/SQL Packages and Types Reference for more information regarding the CANCEL_TASK proced
ure and its parameters.
By default, all SQLAccess 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 a
llows 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.
DBMS_ADVISOR.MARK_RECOMMEND ATION ( task_name IN VARCHAR2 id IN NUMBER, action IN VARCHAR2);
The follow
ing example marks a recommendation with ID 2 as REJECT. This recommendation and any dependent recommendations will not a
ppear in the script.
EXECUTE DBMS_ADVISOR.MARK_RECOMMENDATION('MYTASK', 2, 'REJECT');
See <
em>PL/SQL Packages and Types Reference for
more information regarding the MARK_RECOMMENDATIONS procedure and its parameters.
Using the UPDATE_REC_ATTRIBUTES procedure, the SQLAcce
ss 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, ac tion_id IN NUMBER, attribute_name IN VARCHAR2, value IN VARCHAR2);
The attrib
ute_name parameter can take the following values:
BASE_TABLE
Specifies the ba se table reference for the recommended object.
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 ex
ample 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 PL/SQL Packages an
d Types Reference for more information regarding the UPDATE_REC_ATTRIBUTES procedure and its parameters.
An alternative to querying the metadat
a 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 ALT
ER 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), th
e owner for these new objects (DEF_INDEX_OWNER and DEF_MVIEW_OWNER), and the tablespaces (DEF_MVIEW_T
ABLESPACE and DEF_INDEX_TABLESPACE).
The following example shows how to generate a CLOB containing the scr ipt 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 DIRE CTORY ADVISOR_RESULTS TO PUBLIC; GRANT WRITE ON DIRECTORY ADVISOR_RESULTS TO PUBLIC;
The following is a fragment of a scr
ipt generated by this procedure. The script also includes PL/SQL calls to gather stats on the recommended access structures and marks
the recommendations as IMPLEMENTED at the end.
Rem Access Advisor V10.0.0.0.0 - Beta
Rem
Rem Username: SH
Rem Task: MYTASK
Rem Execution date: 15/04/2003 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 ROW
ID, SEQUENCE("PROD_ID","PROD_SUBCATEGORY")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."PRODUCTS"
ADD ROW
ID, SEQUENCE("PROD_ID","PROD_SUBCATEGORY")
INCLUDING NEW VALUES;
..
CREATE MATERIALIZED VIEW "SH"."MV$$_00510002"
REFRESH FAS
T WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH.CUSTOMERS.CUST_STATE_PROVINCE C1, COUNT(*) M1 FROM
SH.CUSTOMERS WHERE (SH.CUST
OMERS.CUST_STATE_PROVINCE = 'CA') GROUP
BY SH.CUSTOMERS.CUST_STATE_PROVINCE;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('"SH"', '"MV$$_005
10002"', 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_A
DVISOR.MARK_RECOMMENDATION('"MYTASK"',2,'IMPLEMENTED');
DBMS_ADVISOR.MARK_RECOMMENDATION('"MYTASK"',3,'IMPLEMENTED');
DBMS_ADVISO
R.MARK_RECOMMENDATION('"MYTASK"',4,'IMPLEMENTED');
END;
/
|
See Also: Oracle Database SQL Reference forCREATE DIRECTORY syntax and PL/SQL Packages and Types Reference for detailed information about th
e GET_TASK_SCRIPT procedure |
The RESET_TASK pr
ocedure 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 PL/SQL Packages and Types Reference for more information regarding the RESET_TASK proced
ure and its parameters.
If you
only want to tune a single SQL statement, the QUICK_TUNE procedure accepts as i
ts 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 th
e results from using QUICK_TUNE. They are exactly the same as those from using EXECUTE_TASK, but this appro
ach is easier to use when there is only a single SQL statement to be tuned. The syntax is as follows:
D BMS_ADVISOR.QUICK_TUNE ( advisor_name IN VARCHAR2, task_name IN VARCHAR2, attr1 IN C LOB, attr2 IN VARCHAR2 := NULL, attr3 IN NUMBER := NULL, task_or_template IN VARCHAR 2 := 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.SQLACCE
SS_ADVISOR, -
:task_name, :sql_stmt);
See PL/SQL Packages and Types Reference for more information regarding the QUICK_TUNE procedure
and its parameters.
Every time recommen dations 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 manageme nt operations that can be performed on tasks:
Using the UPDATE_TASK_ATTRIBUTES p
rocedure, 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 templat e 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 VAR CHAR2 new_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, read_only IN VARCHAR2 := NULL , is_template IN VARCHAR2 := NULL, source IN VARCHAR2 := NULL);
The following example updates t
he name of an task MYTASK to TUNING1:
EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIB
UTES('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 ta
sk MYTASK as a template.
EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES('TUNING1', is_templat
e=>'TRUE');
See PL/SQL Packages a
nd Types Reference for more information regarding the UPDATE_TASK_ATTRIBUTES procedure and its parameters.
The DELETE_TASK procedure delet
es 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 PL/SQL Packages and Types Reference for more information regarding the DELETE_TASK procedure and its parame
ters.
When a task or workload object is created, the parameter DAYS_TO_EXPIRE is set to 30. The value indicates t
he number of days until the task or object will automatically be deleted by the system. If you wish to save a task or workload indefi
nitely, the DAYS_TO_EXPIRE parameter should be set to ADVISOR_UNLIMITED.
You can use the constants shown in Table 17-4 with the SQLAccess Advisor.
Table 17-4 SQLAccess Advisor Constants
| Constant | Description |
|---|---|
ADVISOR_ALL |
A value that is used to in
dicate 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 paramet ers. |
ADVISOR_DEFA ULT |
Indicates the default value. Typically used when setting task or workload parameters. |
ADVI SOR_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<
/code>, it will have no effect on the current operation. This is typically used for setting a parameter as unused for its dependent o
perations. |
SQLACC ESS_GENERAL |
Specifies the name of a default SQLAccess general-purpose task te
mplate. This template will set the DML_VOLATILITY task parameter to TRUE and EXECUTION_TYPE to
FULL. |
SQLACCESS_OLTP |
Specifies the name of a default SQLAccess OLTP task templat
e. This template will set the DML_VOLATILITY task parameter to TRUE and EXECUTION_TYPE to ONLY. |
SQLACCESS_WAREHOUSE |
Specifies the name of a default SQLAcces
s warehouse task template. This template will set the DML_VOLATILITY task parameter to FALSE and EXEC
UTION_TYPE to FULL. |
|
SQLACCESS_ADVISOR |
Contains the formal name of the SQ LAccess Advisor. It can be used when procedures require the Advisor name as an argument. |
This section illustrates som
e typical scenarios for using the SQLAccess Advisor. Oracle Database provides a script that contains this chapter's examples, a
advdemo.sql.
The following examp
le 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, prio
rity, 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-YYY
Y'')
GROUP BY t.calendar_month_desc')
/
--Load all SQL queries.
INSERT INTO user_workload (username, module, action, priority, sql_t
ext)
VALUES ('SH', 'Example1', 'Action', 2,
'SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
SUM(s.amount_sold) sa
les_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 = c
h.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_la
st_name
FROM customers c WHERE c.country_id IN (52790, 52789)
ORDER BY c.country_id, c.cust_city, c.cust_last_name')
/
COMMIT;
CONNE
CT SH/SH;
set serveroutput on;
VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255);
VARIABLE workload_name VARCHAR2(255);
VARI
ABLE saved_stmts NUMBER;
VARIABLE failed_stmts NUMBER;
Step 2 Create a w orkload 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); PRIN T :saved_stmts; PRINT :failed_stmts;
Step 4 Create a task named MYTASK
EXECUTE :task_name := 'MYTASK';
EXECUTE DBMS_ADVISOR.C
REATE_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 us er_advisor_recommendations WHERE task_name = :task_name;
See "Viewing the 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
W
HERE task_name = :task_name AND workload_name = :workload_name;
-- See the actions for each recommendations.
SELECT rec_id, action_i
d, SUBSTR(command,1,30) AS command
FROM user_advisor_actions
WHERE task_name = :task_name
ORDER BY rec_id, action_id;
-- See what th
e actions are using sample procedure.
SET SERVEROUTPUT ON SIZE 99999
EXECUTE show_recm(:task_name);
Step 9 Generate a script to Implement the recommendations
E XECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name),-
'ADVISOR_RESULTS', 'Exam ple1_script.sql');
The following example cr eates a template and then uses it to create a task. It then uses this task to generate recommendations from a user-defined table, sim ilar 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 namin g 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, 'MV IEW_NAME_TEMPLATE', 'SH_MV$$_<SEQ>'); --Set default owners for recommended indexes/materialized views. EXECUTE DBMS_ADVISOR.SE T_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_MVIE W_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 tas k_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.IMPO RT_SQLWKLD_USER ( -
:workload_name, 'APPEND', 'SH', 'USER_WORKLOAD', :saved_stmts,:failed_stmts); < /pre>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_n ame);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 illust rates collection of a workload from a SQL cache. We first load the cache with a bunch of SQL statements. We then setup some filters t o pick only a subset of those statements and import them into a SQLAccess Advisor workload. The workload is then used to generate rec ommendations.
Step 1 Loading the SQL cache
The following statements are executed so they will be in the SQL cache:
CONNECT / AS SYSDBA
--Clear any p
rior contents of the cache.
ALTER SYSTEM FLUSH SHARED_POOL;
CONNECT SH/SH;
SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dol
lars
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;
-- Qu
eries to illustrate filtering
CONNECT scott/tiger;
SELECT e.ename, d.dname
FROM emp e, dept d WHERE e.deptno = d.deptno;
SELECT COUN
T(*) FROM dept;
CONNECT sh/sh
VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255);
VARIABLE workload_name VARCHAR2(255);
VARIA
BLE saved_stmts NUMBER;
VARIABLE failed_stmts NUMBER;
Step 2 Create a wo rkload named MY_CACHE_WORKLOAD
EXECUTE :workload_name := 'MY_CACHE_WORKLOAD';< pre xml:space="preserve">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.I MPORT_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_sql w_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 wor kload_name = :workload_name;
Step 6 Update a statement in the workload
VARIABLE updated_stmts NUMBER;
EXECUTE DBMS_ADVISOR.UP DATE_SQLWKLD_STATEMENT ( - :workload_name, 'executions < 10', :updated_stmts, priority => 3); PRINT :updated_stmts; --See that the change has been made. SELECT sql_id, username, executions, priority FROM user_advisor_sqlw_stmts WHERE workload_name = :work load_name ORDER BY sql_id;
Step 7 Create a task named MYTASK
EXECUTE :task_name := 'MYTASK';
EXECUTE DBMS_ADVISOR.CREATE_TASK
('SQL Access Advisor', :task_id, :task_name);
Step 8 Create a link betw een a workload and a task
EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF(:task_name, :workload_na me);
Step 9 Execute the task
EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);
Step 10 Generate a scr ipt
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name),-
'ADVISOR_RESULTS', 'Example3_script.sql');
This example illustrates how SQLAccess Advisor may be used to evaluate the utilization of existi
ng indexes and materialized views. We assume the workload is loaded into USER_WORKLOAD table as in "
Recommendations From a User-Defined Workload". The indexes and materialized views that are being currently used (by the given wor
kload) will appear as RETAIN actions in the SQLAccess Advisor recommendations.
CONNECT SH/ SH; VARIABLE task_id NUMBER; VARIABLE task_name VARCHAR2(255); VARIABLE workload_name VARCHAR2(255); VARIABLE saved_stmts NUMBER; VAR IABLE failed_stmts NUMBER;
Step 1 Create a workload named WORKLOA D
EXECUTE :workload_name := 'MYWORKLOAD';
EXECUTE DBM S_ADVISOR.CREATE_SQLWKLD(:workload_name);
Step 2 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 3 Create a task named MY_EVAL_TASK
EXECUTE :task_name := 'MY_EVAL_TASK';
EXECUTE DBMS_ADVISOR.CREATE_TASK
('SQL Access Advisor', :task_id, :task_name);
Step 4 Create a link betw een workload and task
EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF(:task_name, :workload_name);
Step 5 Set task parameters to indicate EVALUATION ONLY task
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER (:task_name, 'EVALUATION_ONLY', 'TRUE');
EXECUTE DBMS_ADVISO R.EXECUTE_TASK(:task_name);
Step 7 View evaluation results
--See the number of recommendations and the status of the task.
SE LECT rec_id, rank, benefit FROM user_advisor_recommendations WHERE task_name = :task_name; --See the actions for each recommendation . 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;
Several DBMS_MVIEW procedures help you with mate
rialized view fast refresh and query rewrite. The EXPLAIN_MVIEW procedure can tell you whether a materialized view is fa
st 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 ho
w to optimize your CREATE MATERIALIZED VIEW statement and to meet other requirements such as m
aterialized view log and rewrite equivalence relationship for fast refresh and general query rewrite. TUNE_MVIEW analyze
s and processes the input statement and generates two sets of output results: one for the materialized view implementation and the ot
her for undoing the create materialized view operations. The two sets of output results can be accessed through Oracle views or be st
ored in external script files created by the SQLAccess Advisor. These external script files are ready to execute to implement the mat
erialized view.
With the TUNE_MVIEW procedure, you no longer require a detailed understanding of materialized vie
ws to create a materialized view in an application because the materialized view and its required components (such as materialized vi
ew log) will be created correctly through the procedure.
See PL/SQL Packages and Types Reference for detailed information about the TUNE_MVIEW procedure.
This section discusses th e following information:
The syntax fo
r TUNE_MVIEW is as follows:
DBMS_ADVISOR.TUNE_MVIEW ( task_name IN OUT VARCHAR2, mv_crea te_stmt IN [CLOB | VARCHAR2])
The TUNE_MVIEW procedure takes two input parameters: task_name an
d 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 th
e input CREATE MATERIALIZED VIEW statement does not have the clauses of REFRESH <
code>FAST or ENABLE QUERY REWRITE, or both, TUNE_MVIEW will use the defaul
t clauses REFRESH FORCE and DISABLE QUERY REWRITE to tune the statem
ent 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 i
n them. The defining query could be a simple SELECT statement or a complex query with set operators or inline views. Whe
n the defining query of the materialized view contains the clause REFRESH FAST, TUNE_MVIEW ana
lyzes the query and checks to see if it is fast refreshable. If it is already fast refreshable, the procedure will return a message s
aying "the materialized view is already optimal and cannot be further tuned". Otherwise, the TUNE_MVIEW procedure will s
tart the tuning work on the given statement.
The TUNE_MVIEW procedure can generate the output statements that cor
rect the defining query by adding extra columns such as required aggregate columns or fix the materialized view logs to achieve the <
code>FAST REFRESH goal. In the case of a complex defining query, the TUNE_MVIEW procedure decomposes
the query and generates two or more fast refreshable materialized views or will restate the materialized view in a way to fulfill fa
st refresh requirements as much as possible. The TUNE_MVIEW procedure supports defining queries with the following compl
ex 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 simi
lar to REFRESH FAST, that will redefine the materialized view so that as many of the advanced forms of quer
y rewrite are possible.
The TUNE_MVIEW procedure generates two sets of output results as executable statements. O
ne set of the output (IMPLEMENTATION) is for implementing materialized views and required components such as materialize
d view logs or rewrite equivalences to achieve fast refreshability and query rewritablity as much as possible. The other set of the o
utput (UNDO) is for dropping the materialized views and the rewrite equivalences in case you decide they are not require
d.
The output statements for the IMPLEMENTATION process include:
CREATE
MATERIALIZED VIEW LOG statements: creates any missing materialized view logs required for fas
t 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 case of one output statement, the original defining query is directly restated and transformed. Simple query transformation coul
d 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 ar
e generated and form a nested materialized view hierarchy in which one or more submaterialized views are referenced by a new top-leve
l materialized view modified from the original statement. This is to achieve fast refresh and query rewrite as much as possible. Subm
aterialized views are often fast refreshable.
BUILD_SAFE_REWRITE_EQUIVALENCE statement: ena
bles the rewrite of top-level materialized view using submaterialized views. It is required to enable query rewrite when a compositio
n occurs.
Note that the decomposition result implies no sharing of submaterialized views. That is, in the case of de
composition, the TUNE_MVIEW output will always contain new submaterialized view and it will not reference existing mater
ialized 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 th
e rewrite equivalence relationship built in the IMPLEMENTATION process if needed.
Note that the U
NDO 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 res
ults:
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 e
xecuting TUNE_MVIEW, the results are output into the SQLAccess Advisor repository tables and are accessible through the
Oracle views, USER_TUNE_MVIEW and DBA_TUNE_MVIEW. See Oracle Database Reference for further details.
It is straightforward to have the SQLAccess Advisor generate scripts using the function DBMS_AD
VISOR.GET_TASK_SCRIPT. The following is a simple example. First, a directory must be defined which is where the results will b
e stored:
CREATE DIRECTORY TUNE_RESULTS AS '/tmp/script_dir'; GRANT READ, WRITE ON DIRECTORY TUNE_RESU LTS TO PUBLIC;
Now generate both the implementation and undo scripts and place them in /tmp/script_dir/mv_create.sq
l and /tmp/script_dir/mv_undo.sql, respectively.
EXECUTE DBMS_ADVISOR.CREATE_FILE(D
BMS_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 TU
NE_MVIEW.
Example 17-3 Optimizing the Defining Query for Fast Refresh
This example shows how TUNE_MV
IEW changes the defining query to be fast refreshable. A MATERIALIZED VIEW CREATE state
ment is defined in variable create_mv_ddl. This create statement has the REFRESH FAST clause s
pecified. Its defining query contains a single query block in which an aggregate column, SUM(s.amount_sold), does not ha
ve the required aggregate columns to support fast refresh. If you execute the TUNE_MVIEW statement with this MATER
IALIZED VIEW CREATE statement, the output produced will be fast refreshable:
VARIABLE task_cust_mv VARCHAR2(30); VARIABLE create_mv_ddl VARCHAR2(4000); EXECUTE :task_cust_mv := 'cust_mv'; EXECUTE :creat e_mv_ddl := ' - CREATE MATERIALIZED VIEW cust_mv - REFRESH FAST - DISABLE QUERY REWRITE AS - SELECT s.prod_id, s.cust_id, SUM(s.amou nt_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_ADVIS OR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);
The projected output of TUNE_MVIEW includes an optimized mater
ialized view defining query as follows:
CREATE MATERIALIZED VIEW SH.CUST_MV
REFRESH FAST WITH ROWID
DIS
ABLE QUERY REWRITE AS
SELECT SH.SALES.PROD_ID C1, SH.CUSTOMERS.CUST_ID C2,
SUM("SH"."SALES"."AMOUNT_SOLD") M1,
CO
UNT("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;
The original defining query of cust_mv has been modified
by adding aggregate columns in order to be fast refreshable.
Example 17-4 Access IMPLEMENTATION Output Through USER_TUNE_MVIEW View
SELECT * FROM USER_TUNE_MVIEW WHERE TASK_NAME= :task_cust_m v 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 PUB LIC; 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 s
hows how a materialized view's defining query with set operators can be decomposed into a number of submaterialized views. The input
detail tables are assumed to be sales, customers, and countries, and they do not have materialized view logs.First, you need to execu
te the TUNE_MVIEW statement with the materialized view CREATE statement defined in the variable creat
e_mv_ddl.
EXECUTE :task_cust_mv := 'cust_mv2';
EXECUTE :create_mv_ddl := ' -
CREATE MATERIALIZE
D 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, c
ustomers 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';
EXECUTE DBMS_ADVIS
OR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);
The materialized view defining query contains a UNION set oper
ator and does not support general query rewrite. In order to support general query rewrite, the MATERIALIZED VIEW<
/code> defining query will be decomposed.
The projected output for the IMPLEMENTATION statement will be created a
long with materialized view log statements and two submaterialized views as follows:
CREATE MATERIALIZE
D VIEW LOG ON "SH"."SALES"
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 R
OWID, 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"."COUNTRI
ES"
WITH ROWID, SEQUENCE("COUNTRY_ID","COUNTRY_NAME")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"SH"."C
OUNTRIES"
ADD ROWID, SEQUENCE("COUNTRY_ID","COUNTRY_NAME")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"S
H"."CUSTOMERS"
ADD ROWID, SEQUENCE("CUST_ID","COUNTRY_ID")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"S
H"."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 S
H.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.SA
LES.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.CUS
T_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 R
OWID
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")
"C
NT",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_i
d = 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" "C
UST_ID",
"CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT"
FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1")',-1553577
441)
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
The original defining query of cust_mv ha
s been modified to query the two submaterialized views instead where both submaterialized views are fast refreshable and support gene
ral query rewrite.
The required materialized view logs are added to enable fast refresh of the submaterialized views. It is no
ted that for each detail table, two materialized view log statements are generated: one is CREATE MATERIALIZED VIEW statement and the other is ALTER MATERIALIZED VIEW FORCE st
atement. This is to ensure the CREATE script can be run multiple times.
The BUILD_SAFE_REWRITE_EQUIVALENCE<
/code> statement is to connect the old defining query to the defining query of the new top-level materialized view. It is to ensure t
hat query rewrite will make use of the new top-level materialized view to answer the query.
Example 17-7 Acces s 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 locate
d at /myscript/mv_create2.sql:
CREATE DIRECTORY TUNE_RESULTS AS '/myscript'
GRANT READ, WR
ITE ON DIRECTRY TUNE_RESULTS TO PUBLIC;
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('cust_mv2'),-
'TUNE_RESULTS'
, 'mv_create2.sql');
The following example is to show some optimization in TUNE_MVIEW
. 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 sub-SELECT queries in the original defining query are of similar shape and their predicate expressions
are combined.
Assume that detail tables sales and customers do not have materialized view logs. You
execute the following statement with a given CREATE MATERIALIZED VIEW statement.
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, c ustomers 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.c ust_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 (100 5,1010,1012) - GROUP BY s.prod_id, s.cust_id'; EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);
The mater
ialized 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.
The projected outp
ut for CREATE statement will be created with an optimized submaterialized view combining the two subselect queries and t
he submaterialized view is referenced by a new top-level materialized view as follows:
CREATE MATERIALI
ZED VIEW LOG ON "SH"."SALES"
WITH ROWID, SEQUENCE ("PROD_ID","CUST_ID","AMOUNT_SOLD")
INCLUDING NEW VALUES
ALTER MATERIALIZED VI
EW LOG FORCE ON "SH"."SALES"
ADD ROWID, SEQUENCE ("PROD_ID","CUST_ID","AMOUNT_SOLD")
INCLUDING NEW VALUES
CREATE MATERIALIZED VIE
W LOG ON "SH"."CUSTOMERS"
WITH ROWID, SEQUENCE ("CUST_ID") INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."CUSTOM
ERS"
ADD ROWID, SEQUENCE ("CUST_ID") INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW SH.CUST_MV$SUB1
REFRESH FAST WITH ROWID
ENA
BLE QUERY REWRITE AS
SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2,
SUM("SH"."SALES"."AMOUNT_SOLD") M1,
COUNT("SH"."SAL
ES"."AMOUNT_SOLD")M2, COUNT(*) M3
FROM SH.CUSTOMERS, SH.SALES
WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND
(SH.SALE
S.CUST_ID IN (2005, 1020, 1012, 1010, 1005))
GROUP BY SH.SALES.CUST_ID, SH.SALES.PROD_ID
CREATE MATERIALIZED VIEW SH.CUST_MV
R
EFRESH FORCE WITH ROWID ENABLE QUERY REWRITE AS
(SELECT "CUST_MV$SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID",
"CUST_MV$S
UB1"."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"."M
3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT"
FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1"
WHERE "CUST_MV$SUB1"."C1"=1012 OR "CU
ST_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 AN
D 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 DROP output is as follows:
DROP MATERIALIZED VIEW SH.CU
ST_MV$SUB1
DROP MATERIALIZED VIEW SH.CUST_MV
DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE('SH.CUST_MV$RWEQ')
The origin
al defining query of cust_mv has been optimized by combining the predicate of the two subselect queries in the sub-mater
ialized view CUST_MV$SUB1. The required materialized view logs are also added to enable fast refresh of the submateriali
zed views.
Example 17-10 Access IMPLEMENTATION Output Through USER_TUNE_MVIEW View
The following query accesses t
he IMPLEMENTATION output through USER_TUNE_MVIEW:
SELECT * FROM USER_TUNE_MVI EW WHERE TASK_NAME= 'cust_mv3' AND SCRIPT_TYPE='IMPLEMENTATION';
Example 17-11 Save IMPLEMENTATION Output in a Script File
The following statements save the IMPLEMENTATION output in a script file l
ocated at /myscript/mv_create3.sql:
CREATE DIRECTORY TUNE_RESULTS AS '/myscript'
GRANT REA
D, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC;
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('cust_mv3'), -
'TUNE_R
ESULTS', 'mv_create3.sql')
|
<
img src="../../dcommon/gifs/oracle.gif" alt="Oracle" /> Copyright &c opy; 2001, 2003 Oracle Corporation All Rights Reserved. |
|
