SQL Access Advisor Examples-ORACLE 11G using DBMS_ADVISOR

SQL Access Advisor Examples are as follows:

Example1: Steps to Implement Advisor recommendations

Step1: Create a sample table

create table test_adv as select * from

all_objects

Step2: gather table statistics

exec dbms_stats.gather_table_stats  (user, 'TEST_ADV')

Step3: Create Advisor Task using dbms_advisor.quick_tune

declare

    v_sql varchar2(200) := 'select object_name from test_adv where object_id = 1523';

    v_tuning_task varchar2(200) := 'tune_task_advisor_mahaug';

    v_tune_result clob;

begin

    dbms_advisor.quick_tune ( dbms_advisor.sqlaccess_advisor , v_tuning_task, v_sql );

    dbms_output.put_line ('Quick Tune Completed');

end;

Step4:  Sql’s to check the status

select * from

user_advisor_templates

select * from

user_advisor_journal

select * from

user_advisor_recommendations

where task_name = 'tune_task_advisor_mahaug'

select * from

user_advisor_actions

where task_name = 'tune_task_advisor_mahaug'

SELECT description, advisor_name, created, status,

recommendation_count, source, how_created

FROM user_advisor_tasks WHERE task_name = 'tune_task_advisor_mahaug'

Step5: To see the advisor recommendations execute below SQL

SELECT DBMS_ADVISOR.get_task_script

('tune_task_advisor_mahaug') AS script

FROM dual;

Below is the output from above sql statement

----------------------------------------------------------------------------------

Rem  SQL Access Advisor: Version 10.2.0.4.0 - Production

Rem  

Rem  Username:        MAHENDRA

Rem  Task:            tune_task_advisor_mahaug

Rem  Execution date:  05/08/2013 07:09

Rem  

CREATE MATERIALIZED VIEW LOG ON

    "MAHENDRA"."TEST_ADV"

    WITH ROWID ;

CREATE MATERIALIZED VIEW "MAHENDRA"."MV$$_88110000"

    REFRESH FAST WITH ROWID

    ENABLE QUERY REWRITE

    AS SELECT "MAHENDRA"."TEST_ADV"."OBJECT_NAME" M1 FROM MAHENDRA.TEST_ADV WHERE (MAHENDRA.TEST_ADV.OBJECT_ID

       = 1523);

begin

  dbms_stats.gather_table_stats('"MAHENDRA"','"MV$$_88110000"',NULL,dbms_stats.auto_sample_size);

end;

/

-----------------------------------------------------------------------------------------------------------

Step6: After Implementing the recommendations let’s see the execution plan comparison

Execution plan of following SQL

select object_name from test_adv where object_id = 1523

Before:

After:

Step7: Deleting the Task…… That’s It.

exec dbms_advisor.delete_task('test_sql_access_task');

Example2 -Recommendations from a User-Defined Workload

Step1: create a table to store user sql statements

CREATE TABLE user_workload

(

   module                VARCHAR2 (64),

   action                VARCHAR2 (64),

   buffer_gets           NUMBER DEFAULT 0,

   cpu_time              NUMBER DEFAULT 0,

   elapsed_time          NUMBER DEFAULT 0,

   disk_reads            NUMBER DEFAULT 0,

   rows_processed        NUMBER DEFAULT 0,

   executions            NUMBER DEFAULT 1,

   optimizer_cost        NUMBER DEFAULT 0,

   last_execution_date   DATE DEFAULT SYSDATE,

   priority              NUMBER DEFAULT 2,

   sql_text              CLOB,

   stat_period           NUMBER DEFAULT 1,

   username              VARCHAR2 (30) DEFAULT USER

)

Step2: Create show_recm procedure

The following PL/SQL procedure can be used to print out some of the attributes of the recommendations.

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 task_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_name);

  LOOP

     FETCH curs INTO 

       v_action, v_command, v_attr1, v_attr2, v_attr3, v_attr4 ;

   EXIT when curs%NOTFOUND;

   DBMS_OUTPUT.PUT_LINE('Action ID: ' || v_action);

   DBMS_OUTPUT.PUT_LINE('Command : ' || v_command);

   DBMS_OUTPUT.PUT_LINE('Attr1 (name)      : ' || SUBSTR(v_attr1,1,30));

   DBMS_OUTPUT.PUT_LINE('Attr2 (tablespace): ' || SUBSTR(v_attr2,1,30));

   DBMS_OUTPUT.PUT_LINE('Attr3             : ' || SUBSTR(v_attr3,1,30));

   DBMS_OUTPUT.PUT_LINE('Attr4             : ' || v_attr4);

   DBMS_OUTPUT.PUT_LINE('Attr5             : ' || v_attr5);

   DBMS_OUTPUT.PUT_LINE('----------------------------------------'); 

   END LOOP;  

   CLOSE curs;     

   DBMS_OUTPUT.PUT_LINE('=========END RECOMMENDATIONS============');

END show_recm;

/

Step3: Insert sample sql’s which needs to advice into table user_workload

INSERT INTO user_workload (username, module, action, priority, sql_text)

VALUES ('MAHNEDRA', '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 ('MAHNEDRA', '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 ('MAHNEDRA', '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 ('MAHNEDRA', '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;

Step4: create a workload using dbms_advisor.create_sqlwkld

set serveroutput on;

VARIABLE task_id NUMBER;

VARIABLE task_name VARCHAR2(255);

VARIABLE workload_name VARCHAR2(255);

VARIABLE saved_stmts NUMBER;

VARIABLE failed_stmts NUMBER;

sql> execute :workload_name := 'MAH_WORKLOAD_27NOV';

PL/SQL procedure successfully completed.

sql> EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);

PL/SQL procedure successfully completed.

Step5: Load the workload from user-defined table USER_WORKLOAD

sql> EXECUTEDBMS_ADVISOR.IMPORT_SQLWKLD_USER(:workload_name,'APPEND','MAHNEDRA',

'USER_WORKLOAD',:saved_stmts,:failed_stmts);

PL/SQLproceduresuccessfullycompleted.

sql>PRINT:saved_stmts;

SAVED_STMTS

-----------

4

sql>PRINT:failed_stmts;

FAILED_STMTS

------------

1

Step6: Create a advisor task and add task parameters if any

sql> EXECUTE :task_name := 'MAH_TASK_27NOV';

PL/SQL procedure successfully completed.

sql> EXECUTE DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', :task_id, :task_name);

PL/SQL procedure successfully completed.

sql> EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'STORAGE_CHANGE', 100);

PL/SQL procedure successfully completed.

sql> EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -

>    :task_name, 'EXECUTION_TYPE', 'INDEX_ONLY');

PL/SQL procedure successfully completed.

Step7: Create a link between workload and task

sql> EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF(:task_name, :workload_name);

PL/SQL procedure successfully completed.

Step8: Execute task

sql> EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);

PL/SQL procedure successfully completed.

Step9: View the Recommendations

-- See the number of recommendations and the status of the task.

sql> SELECT rec_id, rank, benefit

  2  FROM user_advisor_recommendations WHERE task_name = :task_name;

    REC_ID       RANK    BENEFIT                                               

---------- ---------- ----------                                               

         1          1          0                                               

         2          1          0                                               

-- See recommendation for each query.

sql> SELECT sql_id, rec_id, precost, postcost,

  2        (precost-postcost)*100/precost AS percent_benefit

  3  FROM user_advisor_sqla_wk_stmts

  4  WHERE task_name = :task_name AND workload_name = :workload_name;

    SQL_ID     REC_ID    PRECOST   POSTCOST PERCENT_BENEFIT                    

---------- ---------- ---------- ---------- ---------------                    

       662          2       1551       1551               0                    

       663          1        472        472               0                    

       664          0       1833       1833               0                    

       665          0        299        299               0                    

-- See the actions for each recommendations.

sql> SELECT rec_id, action_id, SUBSTR(command,1,30) AS command

  2  FROM user_advisor_actions

  3  WHERE task_name = :task_name

  4  ORDER BY rec_id, action_id;

    REC_ID  ACTION_ID                                                          

---------- ----------                                                          

COMMAND                                                                        

--------------------------------------------------------------------------------

         1          2                                                          

RETAIN INDEX                                                                    

                                                                               

         2          1                                                          

RETAIN INDEX                                                                    

                                                                               

         2          2                                                          

RETAIN INDEX                                                                    

                                                                               

sql>

sql> -- See what the actions are using sample procedure.

sql> SET SERVEROUTPUT ON SIZE 99999

sql> EXECUTE show_recm(:task_name);

=========================================                                      

Task_name = MAH_TASK_27NOV                                                     

Action ID: 1                                                                   

Command : RETAIN INDEX                                                         

Attr1 (name)      : "MAHENDRA"."PRODUCTS_PK"                                     

Attr2 (tablespace):                                                            

Attr3             : "MAHENDRA"."PRODUCTS"                                        

Attr4             : BTREE                                                      

Attr5             :                                                            

----------------------------------------                                       

Action ID: 2                                                                   

Command : RETAIN INDEX                                                         

Attr1 (name)      : "MAHENDRA"."TIME_PK"                                         

Attr2 (tablespace):                                                            

Attr3             : "MAHENDRA"."TIMES"                                           

Attr4             : BTREE                                                       

Attr5             :                                                            

----------------------------------------                                       

=========END RECOMMENDATIONS============                                       

PL/SQL procedure successfully completed.

Step10: Generate a script to implement the recommendations

create directory ADVISOR_RESULTS as 'D:\'  -- This is in Windows

create directory ADVISOR_RESULTS as '\tmp' -- In Unix

sql> EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name),-

> 'ADVISOR_RESULTS', 'Example1_script.sql');

PL/SQL procedure successfully completed.

Example3 - Generate Recommendations Using a Task Template

Step1: Create a Template

sql> EXECUTE :template_name := 'MAH_TEMPLATE_27NOV';

PL/SQL procedure successfully completed.

sql> EXECUTE DBMS_ADVISOR.CREATE_TASK ( -

>    'SQL Access Advisor',:template_id, :template_name, is_template=>'TRUE');

PL/SQL procedure successfully completed.

sql> print :template_id

TEMPLATE_ID                                                                    

-----------                                                                    

      37801    

Step2: Set Template Parameters

sql> EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -

>    :template_name,  'INDEX_NAME_TEMPLATE', 'MAHENDRA_IDX$$_<SEQ>');

PL/SQL procedure successfully completed.

sql> EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -

>    :template_name, 'MVIEW_NAME_TEMPLATE', 'MAHENDRA_MV$$_<SEQ>');

PL/SQL procedure successfully completed.

sql>

sql> --Set default owners for recommended indexes/materialized views.

sql> EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -

>    :template_name, 'DEF_INDEX_OWNER', 'MAHENDRA');

PL/SQL procedure successfully completed.

sql> EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( -

>    :template_name, 'DEF_MVIEW_OWNER', 'MAHENDRA');

PL/SQL procedure successfully completed.

Step3: Create a task using the template

sql> VARIABLE task_id NUMBER;

sql> VARIABLE task_name VARCHAR2(255);

sql> EXECUTE :task_name := 'MAH_TASK_27NOV_2';

PL/SQL procedure successfully completed.

sql> EXECUTE DBMS_ADVISOR.CREATE_TASK ( -

>    'SQL Access Advisor', :task_id, :task_name, template => 'MAH_TEMPLATE_27NOV');

PL/SQL procedure successfully completed.

Step4: Create a workload and load the workload from user table user_workload

sql> VARIABLE workload_name VARCHAR2(255);

sql> VARIABLE saved_stmts NUMBER;

sql> VARIABLE failed_stmts NUMBER;

sql> EXECUTE :workload_name := 'MAH_WORKLOAD_27NOV_2';

PL/SQL procedure successfully completed.

sql> EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);

PL/SQL procedure successfully completed.

sql> -- Load the workload from user-defined table USER_WORKLOAD

sql>

sql> EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_USER ( -

> :workload_name, 'APPEND', 'MAHENDRA', 'USER_WORKLOAD', :saved_stmts,:failed_stmts);

PL/SQL procedure successfully completed.

Step5: Create a link between workload and task

sql>

sql> EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF(:task_name, :workload_name);

PL/SQL procedure successfully completed.

Step6: Execute the task

sql> EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);

PL/SQL procedure successfully completed.

sql>

Step7: Generate a script … That’s it.

sql> EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name),-

> 'ADVISOR_RESULTS', 'Example2_script.sql');

PL/SQL procedure successfully completed.

Example4 – Filter a workload from the SQL Cache

The following example illustrates collection of a workload from a SQL cache. We first load the cache with a bunch of SQL statements. We then setup some filters to pick only a subset of those statements and import them into a SQLAccess Advisor workload. The workload is then used to generate recommendations.

Step 1 Loading the SQL cache

sql> --Clear any prior contents of the cache.

sql> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

sql> SELECT   t.calendar_month_desc, SUM(s.amount_sold) AS dollars

  2  FROM     sales s, times t WHERE s.time_id = t.time_id

  3  AND  s.time_id between TO_DATE('01-JAN-2000', 'DD-MON-YYYY')

  4                         AND TO_DATE('01-JUL-2000', 'DD-MON-YYYY')

  5  GROUP BY t.calendar_month_desc;

CALENDAR_MONTH_DESC         DOLLARS                                            

------------------------ ----------                                            

2000-02                  23883241.4                                            

2000-04                    25738283                                            

2000-05                  28826864.1                                            

2000-06                  22409099.5                                            

2000-01                    26900002                                            

2000-03                  23172940.5                                            

2000-07                      754712                                            

7 rows selected.

sql> -- Order by

sql> SELECT c.country_id, c.cust_city, c.cust_last_name

  2  FROM customers c WHERE c.country_id IN ('52790', '52789')

  3  ORDER BY c.country_id, c.cust_city, c.cust_last_name;

no rows selected

sql> SELECT e.ename, d.dname

  2  FROM emp e, dept d WHERE e.deptno = d.deptno;

sql> SELECT COUNT(*) FROM dept;

Step 2 Create a workload and setup parameters

sql> VARIABLE task_name VARCHAR2(255);

sql> VARIABLE workload_name VARCHAR2(255);

sql> VARIABLE saved_stmts NUMBER;

sql> VARIABLE failed_stmts NUMBER;

sql> EXECUTE :workload_name := 'MAH_CACHE_WORKLOAD';

PL/SQL procedure successfully completed.

sql> EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);

PL/SQL procedure successfully completed.

sql>

sql>

sql> -- Set up filters

sql>

sql> EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER ( -

>    :workload_name, 'USERNAME_LIST', 'MAHENDRA');

PL/SQL procedure successfully completed.

Step 3 Load the workload from SQL Cache

sql> -- Load the workload from SQL Cache

sql>

sql> EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE ( -

> :workload_name, 'APPEND', 2, :saved_stmts, :failed_stmts);

PL/SQL procedure successfully completed.

sql> PRINT :saved_stmts;

SAVED_STMTS                                                                    

-----------                                                                    

         62                                                                     

sql> PRINT :failed_stmts;

FAILED_STMTS                                                                   

------------                                                                   

          83                                                                    

sql>

sql> --See the workload statements in catalog views

sql> SELECT num_select_stmt, create_date

  2  FROM user_advisor_sqlw_sum

  3  WHERE workload_name = :workload_name;

NUM_SELECT_STMT CREATE_DATE                                                    

--------------- ---------------                                                

             59 27-NOV-13                                                      

sql>

sql> SELECT sql_id, username, optimizer_cost, SUBSTR(sql_text, 1, 30)

  2  FROM user_advisor_sqlw_stmts

  3  WHERE workload_name = :workload_name

  4  ORDER BY sql_id;

Step 4 Add a single statement to the workload

sql> EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT (:workload_name, username => 'MAHENDRA', -

> priority => 1, executions => 10,  sql_text =>  -

>    'select count(*) from customers where cust_state_province=''CA''');

PL/SQL procedure successfully completed.

sql>

sql> SELECT num_select_stmt, create_date

  2  FROM user_advisor_sqlw_sum

  3  WHERE workload_name = :workload_name;

NUM_SELECT_STMT CREATE_DATE                                                    

--------------- ---------------                                                

             60 27-NOV-13            

Step 5: Update a statement in the workload

sql> VARIABLE updated_stmts NUMBER;

sql> EXECUTE DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT ( -

>    :workload_name, 'executions < 10', :updated_stmts, priority => 3);

Step 6 Create a task named MYTASK

sql> EXECUTE :task_name := 'MAH_SQL_CACHE_TEST_TASK';

PL/SQL procedure successfully completed.

sql> EXECUTE DBMS_ADVISOR.CREATE_TASK ('SQL Access Advisor', :task_id, :task_name);

PL/SQL procedure successfully completed.

Step 8 Create a link between a workload and a task and then execute the task

sql> -- create a link between workload and task

sql>

sql> EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF(:task_name, :workload_name);

PL/SQL procedure successfully completed.

sql>

sql> -- execute the task

sql>

sql> EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);

PL/SQL procedure successfully completed.

Step 9 Generate a script .. That’s it

sql>

sql> EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name),-

> 'ADVISOR_RESULTS', 'Example4_script.sql');

PL/SQL procedure successfully completed.

For More details on DBMS_ADVISOR -> DBMS_ADVISOR Package in ORACLE