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

Tejas

Passionate traveller,Reviewer of restaurants and bars,tech lover,everything about data processing,analyzing,SQL,PLSQL,pig,hive,zookeeper,mahout,kafka,neo4j