Tuning advisors

How to use SQL Access Advisor?

SQL Access Advisor suggests proper set of materialized views,partitioned tables,indexes for given workload. Complex tasks such as partition of an unpartitioned table needs a go through for access advisors. Here I will suggest how to use access advisor for each component.

In case of views, as a virtual table does not hold any data. Every time query will be executed.No need for any refresh mechanisms as there is not holing of data by views. When base table has change in any data, query will be going to execute when being referenced. Here one advantage is I will get up to date data each time. But execution every time will have tremendous impact on performance.

In case of materialized views entire copy of data is held as a separate database object.So no need to execute underlying query till there is no any changes in base tables.It need explicit refresh mechanisms. There is a lag between base tables that we have to specify. When I have multiple joins in view then its better to create materialized view rather than only a view but with proper set of periodic refresh mechanisms. When we create materialized view that is referencing data from different schema then it’s called snapshot

SQL Access Advisor, using the TUNE_MVIEW procedure, also recommends how to optimize materialized views so that they can be fast refreshable and take advantage of general query rewrite.

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, then the resulting materialized view recommendation is 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’;

Here executed TUNE_MVIEW procedure.

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

Now original query has been modified by TUNE_MVIWE procedure by adding supporting aggregate columns COUNT(“SH”.”SALES”.”AMOUNT_SOLD”) M2, COUNT(*) M3

CREATE MATERIALIZED VIEW SH.CUST_MV                                                                        REFRESH FAST WITH ROWID                                                                                                    DISABLE QUERY REWRITE AS SELECT SH.SALES.PROD_ID C1, SH.CUSTOMERS.CUST_ID C2, SUM(“SH”.”SALES”.”AMOUNT_SOLD”) M1,                                          COUNT(“SH”.”SALES”.”AMOUNT_SOLD”) M2,                                                                        COUNT(*) M3  FROM SH.SALES, SH.CUSTOMERS                                                                 WHERE    CUSTOMERS.CUST_ID = SH.SALES.CUST_ID GROUP BY SH.SALES.PROD_ID, SH.CUSTOMERS.CUST_ID;


Author Profile

Passionate traveller,Reviewer of restaurants and bars,tech lover,everything about data processing,analyzing,SQL,PLSQL,pig,hive,zookeeper,mahout,kafka,neo4j
Like Love Haha Wow Sad Angry
Article Name
How to use SQL Access Advisors?
How to create a task? How to use SQL Access advisors? Do I get optimum query as output from raw specified query?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.