Tuesday, October 6, 2009

DBMS_ADVISOR Process

1. Gather a number of SQL statements that will form the tuning workload.

2. Check that the user running DBMS_ADVISOR has the ADVISOR privilege, and has SELECT access to the tables and views referenced by the SQL statements.

3. Define a task using DBMS_ADVISOR.CREATE_TASK, to create a container for your tuning exercise:

VARIABLE task_id NUMBER;
VARIABLE my_first_task varhcar2(300);
EXECUTE DBMS_ADVISOR.CREATE_TASK ('SQL Access Advisor', :task_id, :my_first_task);
PRINT my_first_task

Then define a workload object, into which we will later load individual SQL statements:

VARIABLE my_first_workload varhcar2(300);
EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:my_first_workload,'This is my first workload');
PRINT my_first_workload

Then, link your task and workload objects:

EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF('my_first_task','my_first_workload');

4. Statements can then be manually added into the workload object:

EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT ('my_first_workload', 'MONTHLY', 'ROLLUP', priority=>1, executions=>20, username => 'DEMO', sql_text => ‘SELECT SUM(sales) FROM sales);

Alternatively, they can be loaded in from a table of SQL statements you create, an SQL Tuning Set, an SQL Cache workload, an Oracle9i Summary Advisor workload; otherwise, a hypothetical workload can be generated from a set of table and view definitions in a schema.

5. Generate recommendations for this task’s workload:

EXECUTE DBMS_ADVISOR.EXECUTE_TASK('my_first_task');

Each recommendation generated by the SQL Access Advisor can be viewed using catalog views such as USER_ADVISOR_RECOMMENDATIONS.
In addition, the procedure GET_TASK_SCRIPT generates an executable SQL script that contains the CREATE, DROP, and ALTER statements to implement the advisor recommendations:

EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('my_first_task'),'ADVISOR_RESULTS', 'script.sql');

As an alternative to generating recommendations using the DBMS_ADVISOR package, the SQL Access Advisor functionality is also available as a wizard within the Enterprise Manager 10g Web site.

If you just want to tune an individual statement you can use the QUICK_TUNE procedure as follows:
BEGIN
DBMS_ADVISOR.quick_tune(
advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
task_name => 'emp_quick_tune',
attr1 => 'SELECT e.* FROM emp e WHERE UPPER(e.ename) = ''SMITH''');
END;
/
Any recommendations can then be displayed using the previous query with the correct task name specified.


· DBA_ADVISOR_TASKS - Basic information about existing tasks.
· DBA_ADVISOR_LOG - Status information about existing tasks.
· DBA_ADVISOR_FINDINGS - Findings identified for an existing task.
· DBA_ADVISOR_RECOMMENDATIONS - Recommendations for the problems identified by an existing task


DBMS_ADVISOR.TUNE_MVIEW
As well as containing functionality that recommends suitable materialized views and indexes, DBMS_ADVISOR contains a procedure called TUNE_MVIEW that is used when working with the query rewrite mechanism.
Oracle9i came with two packages, DBMS_MVIEW.EXPLAIN_MVIEW and DBMS_MVIEW.EXPLAIN_REWRITE that could be used to diagnose why a materialized view wasn't being used for query rewrite. However, although these packages told you why rewrite hadn't happened, they left it down to you to work out how to alter your CREATE MATERIALIZED VIEW statement to ensure that rewrite happened correctly. Oracle 10g’s DBMS_ADVISOR.TUNE_MVIEW builds on this functionality and tells the DBA what changes to make to a materialized view to make it suitable for query rewrite, taking as its input a CREATE MATERIALIZED VIEW statement and outputting a corrected version that supports query rewrite and features such as fast refresh.
Tuning a materialized view follows a similar process to that used with the SQL Access Advisor.
1. First, create a task and supply a CREATE MATERIALIZED VIEW statement to DBMS_ADVISOR.TUNE_MVIEW
EXECUTE DBMS_ADVISOR.TUNE_MVIEW(‘my_first_mv’, ' -
CREATE MATERIALIZED VIEW sales_mv -
REFRESH FAST -
disable QUERY REWRITE AS -
select category, country, sum(sales) -
from product p, geography g, sales s -
where s.product_id = p.product_id -
and s.geography_id = p.geography_id -
group by p.category, g.country
');
2. You can then directly query the resulting tuned CREATE MATERIALIZED VIEW statement from the USER_TUNE_MVIEW view:
SELECT * FROM USER_TUNE_MVIEW
WHERE TASK_NAME= ‘my_first_mv’ AND
SCRIPT_TYPE='IMPLEMENTATION';
3. Or you can output the tuned statement to the filesystem using the DBMS_ADVISOR.GET_TASK_SCRIPT procedure.
SELECT * FROM USER_TUNE_MVIEW
WHERE TASK_NAME= ‘my_first_mv’ AND
SCRIPT_TYPE='IMPLEMENTATION';
The DBMS_ADVISOR.TUNE_MVIEW procedure has the capability to add additional aggregate columns and materialized view logs to the view definition so that it becomes fast refreshable, and it can restate the view definition (sometimes breaking it into separate, simpler definitions) so that it is more likely to satisfy query rewrite restrictions.

DBA Advisor Views:
dba_advisor_journal
dba_advisor_objects
dba_advisor_parameters
dba_advisor_rationale
dba_advisor_recommendations
dba_advisor_sqlw_journal
dba_advisor_tasks
dba_advisor_usage

DBMS_ADVISOR,

SQLAcess Advisor and SQLWorkload Advisor.


DECLARE
taskname varchar2(30) := 'SQLACCESS3638195';
task_desc varchar2(256) := 'SQL Access Advisor';
task_or_template varchar2(30) := 'SQLACCESS_EMTASK';
task_id number := 0;
num_found number;
sts_name varchar2(256) := 'SQLACCESS3638195_sts';
sts_cursor dbms_sqltune.sqlset_cursor;
BEGIN
/* Create Task */
dbms_advisor.create_task(DBMS_ADVISOR.SQLACCESS_ADVISOR,
task_id,
taskname,
task_desc,
task_or_template);

/* Reset Task */
dbms_advisor.reset_task(taskname);

/* Delete Previous STS Workload Task Link */
select count(*)
into num_found
from user_advisor_sqla_wk_map
where task_name = taskname
and workload_name = sts_name;
IF num_found > 0 THEN
dbms_advisor.delete_sqlwkld_ref(taskname,sts_name);
END IF;

/* Delete Previous STS */
select count(*)
into num_found
from user_advisor_sqlw_sum
where workload_name = sts_name;
IF num_found > 0 THEN
dbms_sqltune.delete_sqlset(sts_name);
END IF;

/* Create STS */
dbms_sqltune.create_sqlset(sts_name, 'Obtain workload from cursor cache');

/* Select all statements in the cursor cache. */
OPEN sts_cursor FOR
SELECT VALUE(P)
FROM TABLE(dbms_sqltune.select_cursor_cache) P;

/* Load the statements into STS. */
dbms_sqltune.load_sqlset(sts_name, sts_cursor);
CLOSE sts_cursor;

/* Link STS Workload to Task */
dbms_advisor.add_sqlwkld_ref(taskname,sts_name);

/* Set STS Workload Parameters */
dbms_advisor.set_task_parameter(taskname,'VALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'VALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'SQL_LIMIT','25');
dbms_advisor.set_task_parameter(taskname,'VALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'VALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'VALID_SQLSTRING_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_SQLSTRING_LIST','"@!"');

/* Set Task Parameters */
dbms_advisor.set_task_parameter(taskname,'ANALYSIS_SCOPE','ALL');
dbms_advisor.set_task_parameter(taskname,'RANKING_MEASURE','PRIORITY,OPTIMIZER_COST');
dbms_advisor.set_task_parameter(taskname,'DEF_PARTITION_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'TIME_LIMIT',10000);
dbms_advisor.set_task_parameter(taskname,'MODE','LIMITED');
dbms_advisor.set_task_parameter(taskname,'STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLIMITED);
dbms_advisor.set_task_parameter(taskname,'DML_VOLATILITY','TRUE');
dbms_advisor.set_task_parameter(taskname,'WORKLOAD_SCOPE','PARTIAL');
dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_MVLOG_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'CREATION_COST','TRUE');
dbms_advisor.set_task_parameter(taskname,'JOURNALING','4');
dbms_advisor.set_task_parameter(taskname,'DAYS_TO_EXPIRE','30');

/* Execute Task */
dbms_advisor.execute_task(taskname);
END;


Other Way Go :
Recommendations From a User-Defined Workload

The following example imports workload from a user-defined table, SH.USER_WORKLOAD. It then creates a task called MYTASK, sets the storage budget to 100 MB and runs the task. The recommendations are printed out using a PL/SQL procedure. Finally, it generates a script, which can be used to implement the recommendations.

Step 1 Prepare the USER_WORKLOAD table

The USER_WORKLOAD table is loaded with SQL statements as follows:

CONNECT SH/SH;
-- aggregation with selection
INSERT INTO user_workload (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2,
'SELECT t.week_ending_day, p.prod_subcategory,
SUM(s.amount_sold) AS dollars, s.channel_id, s.promo_id
FROM sales s, times t, products p WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id AND s.prod_id > 10 AND s.prod_id < 50 GROUP BY t.week_ending_day, p.prod_subcategory, s.channel_id, s.promo_id') / -- aggregation with selection INSERT INTO user_workload (username, module, action, priority, sql_text) VALUES ('SH', 'Example1', 'Action', 2, 'SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars FROM sales s , times t WHERE s.time_id = t.time_id AND s.time_id between TO_DATE(''01-JAN-2000'', ''DD-MON-YYYY'') AND TO_DATE(''01-JUL-2000'', ''DD-MON-YYYY'') GROUP BY t.calendar_month_desc') / --Load all SQL queries. INSERT INTO user_workload (username, module, action, priority, sql_text) VALUES ('SH', 'Example1', 'Action', 2, 'SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount FROM sales s, times t, customers c, channels ch WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND c.cust_state_province = ''CA'' AND ch.channel_desc IN (''Internet'',''Catalog'') AND t.calendar_quarter_desc IN (''1999-Q1'',''1999-Q2'') GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc') / -- order by INSERT INTO user_workload (username, module, action, priority, sql_text) VALUES ('SH', 'Example1', 'Action', 2, 'SELECT c.country_id, c.cust_city, c.cust_last_name FROM customers c WHERE c.country_id IN (52790, 52789) ORDER BY c.country_id, c.cust_city, c.cust_last_name') / COMMIT; CONNECT SH/SH; set serveroutput on; VARIABLE task_id NUMBER; VARIABLE task_name VARCHAR2(255); VARIABLE workload_name VARCHAR2(255); Step 2 Create a SQL Tuning Set named MYWORKLOAD EXECUTE :workload_name := 'MYWORKLOAD'; EXECUTE DBMS_SQLTUNE.CREATE_SQLSET(:workload_name, 'test purposeV); Step 3 Load the SQL Tuning Set from the user-defined table SH.USER_WORKLOAD DECLARE sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR; /*a sqlset cursor variable*/ BEGIN OPEN sqlset_cur FOR SELECT SQLSET_ROW(null, sql_text, null, null, username, null, null, 0,0,0,0,0,0,0,0,0,null, 0,0,0,0) AS ROW FROM USER_WORKLOAD; DBMS_SQLTUNE.LOAD_SQLSET(:workload_name, sqlset_cur); END; Step 4 Create a task named MYTASK EXECUTE :task_name := 'MYTASK'; EXECUTE DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', :task_id, :task_name); Step 5 Set task parameters EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'STORAGE_CHANGE', 100); EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'ANALYSIS_SCOPE, INDEX'); Step 6 Create a link between the SQL Tuning Set and the task EXECUTE DBMS_ADVISOR.ADD_STS_REF(:task_name, :workload_name); Step 7 Execute the task EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name); Step 8 View the recommendations -- See the number of recommendations and the status of the task. SELECT rec_id, rank, benefit FROM user_advisor_recommendations WHERE task_name = :task_name; See "Viewing Recommendations" or "Generating SQL Scripts" for further details. -- See recommendation for each query. SELECT sql_id, rec_id, precost, postcost, (precost-postcost)*100/precost AS percent_benefit FROM user_advisor_sqla_wk_stmts WHERE task_name = :task_name AND workload_name = :workload_name; -- See the actions for each recommendations. SELECT rec_id, action_id, SUBSTR(command,1,30) AS command FROM user_advisor_actions WHERE task_name = :task_name ORDER BY rec_id, action_id; -- See what the actions are using sample procedure. SET SERVEROUTPUT ON SIZE 99999 EXECUTE show_recm(:task_name); Generate Recommendations Using a Task Template The following example creates a template and then uses it to create a task. It then uses this task to generate recommendations from a user-defined table, similar to "Recommendations From a User-Defined Workload". CONNECT SH/SH; VARIABLE template_id NUMBER; VARIABLE template_name VARCHAR2(255); Step 1 Create a template called MY_TEMPLATE EXECUTE :template_name := 'MY_TEMPLATE'; EXECUTE DBMS_ADVISOR.CREATE_TASK ( - 'SQL Access Advisor',:template_id, :template_name, is_template=>'TRUE');

Step 2 Set template parameters

Set naming conventions for recommended indexes and materialized views.

EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -
:template_name, 'INDEX_NAME_TEMPLATE', 'SH_IDX$$_');
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -
:template_name, 'MVIEW_NAME_TEMPLATE', 'SH_MV$$_');

--Set default owners for recommended indexes/materialized views.
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -
:template_name, 'DEF_INDEX_OWNER', 'SH');
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -
:template_name, 'DEF_MVIEW_OWNER', 'SH');

--Set default tablespace for recommended indexes/materialized views.
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -
:template_name, 'DEF_INDEX_TABLESPACE', 'SH_INDEXES');
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -
:template_name, 'DEF_MVIEW_TABLESPACE', 'SH_MVIEWS');

Step 3 Create a task using the template

VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255);
EXECUTE :task_name := 'MYTASK';
EXECUTE DBMS_ADVISOR.CREATE_TASK ( -
'SQL Access Advisor', :task_id, :task_name, template => 'MY_TEMPLATE');

--See the parameter settings for task
SELECT parameter_name, parameter_value
FROM user_advisor_parameters
WHERE task_name = :task_name AND (parameter_name LIKE '%MVIEW%'
OR parameter_name LIKE '%INDEX%');

Step 4 Create a SQL Tuning Set named MYWORKLOAD

EXECUTE :workload_name := 'MYWORKLOAD';
EXECUTE DBMS_SQLTUNE.CREATE_SQLSET(:workload_name, 'test_purpose');

Step 5 Load the SQL Tuning Set from the user-defined table SH.USER_WORKLOAD

DECLARE
sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR; /*a sqlset cursor variable*/
BEGIN
OPEN sqlset_cur FOR
SELECT
SQLSET_ROW(null,sql_text,null,null,username, null, null, 0,0,0,0,0,0,0,0,0,
null,0,0,00) AS row
FROM user_workload;
DBMS_SQLTUNE.LOAD_SQLSET(:workload_name, sqlsetcur);
END;

Step 6 Create a link between the workload and the task

EXECUTE DBMS_ADVISOR.ADD_STS_REF(:task_name, :workload_name);

Step 7 Execute the task

EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);

Step 8 Generate a script

EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name),-
'ADVISOR_RESULTS', 'Example2_script.sql');

Evaluate Current Usage of Indexes and Materialized Views

This example illustrates how the SQL Access Advisor can be used to evaluate the utilization of existing 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 workload) will appear as RETAIN actions in the SQL Access Advisor recommendations.

VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255);
VARIABLE workload_name VARCHAR2(255);

Step 1 Create a SQL Tuning Set named WORKLOAD

EXECUTE :workload_name := 'MYWORKLOAD';
EXECUTE DBMS_SQLTUNE.CREATE_SQLSET(:workload_name, 'test_purpose');

Step 2 Load the SQL Tuning Set from the user-defined table SH.USER_WORKLOAD

DECLARE
sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR; /*a sqlset cursor variable*/
BEGIN
OPEN sqlset_cur FOR
SELECT
SQLSET_ROW(null,sql_text,null,null,username, null, null, 0,0,0,0,0,0,0,0,0,
null, 0,0,0,0)
AS ROW
FROM user_workload;
DBMS_SQLTUNE.LOAD_SQLSET(:workload_name, :sqlsetcur);
END;

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 between workload and task

EXECUTE DBMS_ADVISOR.ADD_STS_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');

Step 6 Execute the task

EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);

Step 7 View evaluation results

--See the number of recommendations and the status of the task.
SELECT rec_id, rank, benefit
FROM user_advisor_recommendations WHERE task_name = :task_name;

--See the actions for each recommendation.
SELECT rec_id, action_id, SUBSTR(command,1,30) AS command, attr1 AS name
FROM user_advisor_actions WHERE task_name = :task_name
ORDER BY rec_id, action_id;

Tuning Materialized Views for Fast Refresh and Query Rewrite
The TUNE_MVIEW procedure takes two input parameters: task_name and mv_create_stmt. task_name is a user-provided task identifier used to access the output results. mv_create_stmt is a complete CREATE MATERIALIZED VIEW statement that is to be tuned. If the input CREATE MATERIALIZED VIEW statement does not have the clauses of REFRESH FAST or ENABLE QUERY REWRITE, or both, TUNE_MVIEW will use the default clauses REFRESH FORCE and DISABLE QUERY REWRITE to tune the statement to be fast refreshable if possible or only complete refreshable otherwise.
The TUNE_MVIEW procedure handles a broad range of CREATE MATERIALIZED VIEW statements that can have arbitrary defining queries in them. The defining query could be a simple SELECT statement or a complex query with set operators or inline views. When the defining query of the materialized view contains the clause REFRESH FAST, TUNE_MVIEW analyzes the query and checks to see if it is fast refreshable. If it is already fast refreshable, the procedure will return a message saying "the materialized view is already optimal and cannot be further tuned". Otherwise, the TUNE_MVIEW procedure will start the tuning work on the given statement.
The TUNE_MVIEW procedure can generate the output statements that correct the defining query by adding extra columns such as required aggregate columns or fix the materialized view logs so that FAST REFRESH is possible. In the case of a complex defining query, the TUNE_MVIEW procedure may decompose the query and generates two or more fast refreshable materialized views or will restate the materialized view in a way to fulfill fast refresh requirements as much as possible. The TUNE_MVIEW procedure supports defining queries with the following complex query constructs:
Set operators (UNION, UNION ALL, MINUS, and INTERSECT)
COUNT DISTINCT
SELECT DISTINCT
Inline views
When the ENABLE QUERY REWRITE clause is specified, TUNE_MVIEW will also fix the statement using a process similar to REFRESH FAST, that will redefine the materialized view so that as many of the advanced forms of query rewrite are possible.
The TUNE_MVIEW procedure generates two sets of output results as executable statements. One set of the output (IMPLEMENTATION) is for implementing materialized views and required components such as materialized view logs or rewrite equivalences to achieve fast refreshability and query rewritablity as much as possible. The other set of the output (UNDO) is for dropping the materialized views and the rewrite equivalences in case you decide they are not required.
The output statements for the IMPLEMENTATION process include:
CREATE MATERIALIZED VIEW LOG statements: creates any missing materialized view logs required for fast refresh.
ALTER MATERIALIZED VIEW LOG FORCE statements: fixes any materialized view log related requirements such as missing filter columns, sequence, and so on, required for fast refresh.
One or more CREATE MATERIALIZED VIEW statements: In the case of one output statement, the original defining query is directly restated and transformed. Simple query transformation could be just adding required columns. For example, add rowid column for materialized join view and add aggregate column for materialized aggregate view. In the case of decomposition, multiple CREATE MATERIALIZED VIEW statements are generated and form a nested materialized view hierarchy in which one or more submaterialized views are referenced by a new top-level materialized view modified from the original statement. This is to achieve fast refresh and query rewrite as much as possible. Submaterialized views are often fast refreshable.
BUILD_SAFE_REWRITE_EQUIVALENCE statement: enables the rewrite of top-level materialized views using submaterialized views. It is required to enable query rewrite when a composition occurs.
Note that the decomposition result implies no sharing of submaterialized views. That is, in the case of decomposition, the TUNE_MVIEW output will always contain new submaterialized view and it will not reference existing materialized views.
The output statements for the UNDO process include:
DROP MATERIALIZED VIEW statements to reverse the materialized view creations (including submaterialized views) in the IMPLEMENTATION process.
DROP_REWRITE_EQUIVALENCE statement to remove the rewrite equivalence relationship built in the IMPLEMENTATION process if needed.
Note that the UNDO process does not include statement to drop materialized view logs. This is because materialized view logs can be shared by many different materialized views, some of which may reside on remote Oracle instances.
Accessing TUNE_MVIEW Output Results
There are two ways to access TUNE_MVIEW output results:
Script generation using DBMS_ADVISOR.GET_TASK_SCRIPT function and DBMS_ADVISOR.CREATE_FILE procedure.
Use USER_TUNE_MVIEW or DBA_TUNE_MVIEW views.
Script Generation DBMS_ADVISOR Function and Procedure
The most straightforward method for generating the execution scripts for a recommendation is to use the procedure DBMS_ADVISOR.GET_TASK_SCRIPT. The following is a simple example. First, a directory must be defined which is where the results will be stored:
CREATE DIRECTORY TUNE_RESULTS AS '/tmp/script_dir';
GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC;
Now generate both the implementation and undo scripts and place them in /tmp/script_dir/mv_create.sql and /tmp/script_dir/mv_undo.sql, respectively.
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name),-
'TUNE_RESULTS', 'mv_create.sql');
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name, -
'UNDO'), 'TUNE_RESULTS', 'mv_undo.sql');
Optimizing the Defining Query for Fast Refresh
This example shows how TUNE_MVIEW changes the defining query to be fast refreshable. A CREATE MATERIALIZED VIEW statement is defined in variable create_mv_ddl, which includes a FAST REFRESH clause. Its defining query contains a single query block in which an aggregate column, SUM(s.amount_sold), does not have the required aggregate columns to support fast refresh. If you execute the TUNE_MVIEW statement with this MATERIALIZED VIEW CREATE statement, the resulting materialized view recommendation will be fast refreshable:
VARIABLE task_cust_mv VARCHAR2(30);
VARIABLE create_mv_ddl VARCHAR2(4000);
EXECUTE :task_cust_mv := 'cust_mv';

EXECUTE :create_mv_ddl := '
CREATE MATERIALIZED VIEW cust_mv
REFRESH FAST
DISABLE QUERY REWRITE AS
SELECT s.prod_id, s.cust_id, SUM(s.amount_sold) sum_amount
FROM sales s, customers cs
WHERE s.cust_id = cs.cust_id
GROUP BY s.prod_id, s.cust_id';

EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);

Access IMPLEMENTATION Output Through USER_TUNE_MVIEW View
SELECT STATEMENT FROM USER_TUNE_MVIEW
WHERE TASK_NAME= :task_cust_mv AND SCRIPT_TYPE='IMPLEMENTATION';

Save IMPLEMENTATION Output in a Script File
CREATE DIRECTORY TUNE_RESULTS AS '/myscript'
GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC;

EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_cust_mv), -
'TUNE_RESULTS', 'mv_create.sql');

Access IMPLEMENTATION Output Through USER_TUNE_MVIEW View
SELECT * FROM USER_TUNE_MVIEW
WHERE TASK_NAME='cust_mv2'
AND SCRIPT_TYPE='IMPLEMENTATION';

Save IMPLEMENTATION Output in a Script File
The following statements save the IMPLEMENTATION output in a script file located at /myscript/mv_create2.sql:
CREATE DIRECTORY TUNE_RESULTS AS '/myscript'
GRANT READ, WRITE ON DIRECTRY TUNE_RESULTS TO PUBLIC;
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('cust_mv2'),
'TUNE_RESULTS', 'mv_create2.sql');

Fast Refreshable with Optimized Sub-Materialized View
The example illustrates how TUNE_MVIEW can optimize the materialized view so that fast refresh is possible. In the example, the materialized view's defining query with set operators is transformed into one sub-materialized view and one top-level materialized view. The subselect queries in the original defining query are of similar shape and their predicate expressions are combined.
The materialized view defining query contains a UNION set-operator so that the materialized view itself is not fast-refreshable. However, two subselect queries in the materialized view defining query can be combined as one single query.

No comments:

Post a Comment