SQL Access Advisor (PL/SQL example)

SQL access advisor can give advises about creating materialized views , materialized logs, indices etc..

I will demonstrate short example how to use sql access advisor for user defined workload for one sql statement.
DBMS_ADVISOR is the package for managing SQL access advisor.

1. Create tuning task

dbms_advisor.create_task(dbms_advisor.SQLACCESS_ADVISOR,'DemoTask1',v_task);

2. Set task parameters, in this example I’ve set ANALYSIS_SCOPE and MODE parameters.

dbms_advisor.set_task_parameter('DemoTask1', 'ANALYSIS_SCOPE', 'ALL');
dbms_advisor.set_task_parameter('DemoTask1', 'MODE', 'COMPREHENSIVE');

There are a lot of more parameters you can set, refer to oracle docs http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_advis.htm#i1001341
3. Create the workload

DBMS_ADVISOR.CREATE_SQLWKLD(v_workload, 'Demo Workload');

4. Add workload statement for which you want to get recommendations

dbms_advisor.add_sqlwkld_statement(v_workload,
                                     NULL,
                                     NULL,
                                     username   => 'SH',
                                     sql_text   => 'select sum(s.amount_sold) 
                                                  from sales s, customers c
                                                  where s.cust_id = c.cust_id
                                                  group by c.cust_id');

5. Add reference between the workload and the task

dbms_advisor.add_sqlwkld_ref('DemoTask1', 'DemoWorkload1');

6. Run the tuning task

dbms_advisor.execute_task('DemoTask1');

7. View recomendations

Complete code:

declare
  v_workload varchar2(100) := 'DemoWorkload1';
  v_task     varchar2(100) := 'DemoTask1';
begin

  dbms_advisor.create_task(dbms_advisor.SQLACCESS_ADVISOR,'DemoTask1',v_task);

  dbms_advisor.set_task_parameter('DemoTask1', 'ANALYSIS_SCOPE', 'ALL');
  dbms_advisor.set_task_parameter('DemoTask1', 'MODE', 'COMPREHENSIVE');

  DBMS_ADVISOR.CREATE_SQLWKLD(v_workload, 'Demo Workload');
  dbms_advisor.add_sqlwkld_statement(v_workload,
                                     NULL,
                                     NULL,
                                     username   => 'SH',
                                     sql_text   => 'select sum(s.amount_sold) 
                                                  from sales s, customers c
                                                  where s.cust_id = c.cust_id
                                                  group by c.cust_id');
  dbms_advisor.add_sqlwkld_ref('DemoTask1', 'DemoWorkload1');
  dbms_advisor.execute_task('DemoTask1');
end;

Let’s see SQL acccess advisor recomendation by invoking dbms_output.put_line(dbms_advisor.get_task_script(‘DemoTask1’));

Rem  SQL Access Advisor: Version 11.2.0.3.0 - Production
Rem  
Rem  Username:        SH
Rem  Task:            DemoTask1
Rem  Execution date:   
Rem  

CREATE MATERIALIZED VIEW LOG ON
    "SH"."CUSTOMERS"
    WITH ROWID, SEQUENCE("CUST_ID")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "SH"."SALES"
    WITH ROWID, SEQUENCE("CUST_ID","QUANTITY_SOLD","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW "SH"."MV$$_00B20000"
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT SH.SALES.CUST_ID C1, SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD")
       M2, SUM("SH"."SALES"."QUANTITY_SOLD") M3, COUNT("SH"."SALES"."QUANTITY_SOLD")
       M4, COUNT(*) M5 FROM SH.CUSTOMERS, SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID
       GROUP BY SH.SALES.CUST_ID;

begin
  dbms_stats.gather_table_stats('"SH"','"MV$$_00B20000"',NULL,dbms_stats.auto_sample_size);
end;
/

As you can see the recommendation is to create materialized view log and materialized view with more summaries not just one like I used in the SQL statement.

Leave a Reply