DBMS_ADVISOR Package in ORACLE

The SQLAccess Advisor can be run from Oracle Enterprise Manager (accessible from the Advisor Central page) using the SQLAccess Advisor Wizard or by invoking the DBMS_ADVISOR package

SQL Access advisor helps on the following:

• Recommend materialized views and indexes based on collected or hypothetical workload information.
• Manage workloads.
• Mark, update, and remove recommendations.

• Perform a quick tune using a single SQL statement.
• Show how to make a materialized view fast refreshable.
• Show how to change a materialized view so that general query rewrite is possible.

SQL Access Advisor Flow Chart:

Sql advisor tables/views:

dba_advisor_tasks

dba_advisor_log

dba_advisor_findings

dba_advisor_recommendations

You need advisor privileges to execute SQL access advisor (DBMS_ADVISOR).

Advisor Definitions:

select * from

dba_advisor_definitions

 Creating advisor Task:

declare

    v_advisor_name varchar2(50) := dbms_advisor.sqlaccess_advisor;

    --SQLACCESS_ADVISOR       constant varchar2(30) := 'SQL Access Advisor'; from dbms_advisor package

    v_task_id number;

    v_task_name varchar2(100);

begin

    dbms_advisor.create_task (v_advisor_name, v_task_id, v_task_name );

    dbms_output.put_line ( 'v_task_id -> ' || v_task_id ||'  v_task_name- ' || v_task_name );

exception

    when others then

        dbms_output.put_line ('Error 111-' || dbms_utility.format_error_stack ||'-'|| dbms_utility.format_error_backtrace );

end;

Output:

v_task_id -> 37762  v_task_name- TASK_37762

select task_id, task_name, advisor_name, status, source

from dba_advisor_tasks

where task_id = 37762

TASK_ID TASK_NAME            ADVISOR_NAME                   STATUS               SOURCE

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

  37762 TASK_37762           SQL Access Advisor             INITIAL  

Creating advisor Template:

Predefined templates in dbms_advisor package are as follows

SQLACCESS_GENERAL, SQLACCESS_OLTP, SQLACCESS_WAREHOUSE

declare

    v_advisor_name varchar2(40) := dbms_advisor.sqlaccess_advisor;

    v_template_id number;

    v_template_name varchar2(100) := 'MAH_TEMPLATE';

begin

    dbms_advisor.create_task ( v_advisor_name, v_template_id, v_template_name, is_template => 'TRUE' );

    dbms_output.put_line ( 'v_template_id is -> ' || v_template_id  || ' - v_template_name is -> ' || v_template_name );

exception

    when others then

        dbms_output.put_line ('Error 111-' || dbms_utility.format_error_stack ||'-'|| dbms_utility.format_error_backtrace );

end;

output:

v_template_id is -> 37764 - v_template_name is -> MAH_TEMPLATE

The following sql gives advisor template information.

select *

from dba_advisor_templates

where task_id = 37764

Now, let’s add template parameters.

-- set naming conventions for recommended indexes/mvs

EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER ( 'MAH_TEMPLATE', 'INDEX_NAME_TEMPLATE', 'MAH_IDX$$_<SEQ>');

EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER (   'MAH_TEMPLATE', 'MVIEW_NAME_TEMPLATE', 'MAH_MV$$_<SEQ>');

Let us now create task using the above template

sql> declare

  2      v_advisor_name varchar2(50) := dbms_advisor.sqlaccess_advisor;

  3      v_task_id number;

  4      v_task_name varchar2(100);

  5  begin

  6      dbms_advisor.create_task (v_advisor_name, v_task_id, v_task_name, template => 'MAH_TEMPLATE' );

  7      dbms_output.put_line ( 'v_task_id -> ' || v_task_id ||'  v_task_name- ' || v_task_name );

  8  end;

  9  /

v_task_id -> 37765  v_task_name- TASK_37765

           

PL/SQL procedure successfully completed.

sql> select task_id, task_name, advisor_name, status, source

  2  from dba_advisor_tasks

  3  where task_id = 37765;

TASK_ID TASK_NAME            ADVISOR_NAME                   STATUS   SOURCE

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

   37765 TASK_37765           SQL Access Advisor             INITIAL       MAH_TEMPLATE

Creating workload:

sql> declare

  2      v_workload_name varchar2(40) := 'MAH_WORKLOAD';

  3      v_description varchar2(100) := 'MAH First Work Load';

  4  begin

  5      dbms_advisor.create_sqlwkld (v_workload_name, v_description );

  6  end;

  7  /

PL/SQL procedure successfully completed.

The following sql gives information about workload that is just created.

select * from

DBA_ADVISOR_SQLW_SUM

where workload_name = 'MAH_WORKLOAD'

Creating workload using workload template:

declare

    v_template_name  varchar2(40) := 'MAH_WORKLOAD_TEMPLATE';

begin

    dbms_advisor.create_sqlwkld (v_template_name, is_template => 'TRUE' );

end;

select * from

DBA_ADVISOR_SQLW_TEMPLATES

where workload_name = 'MAH_WORKLOAD_TEMPLATE'

To add template parameters:

declare

    v_template_name  varchar2(40) := 'MAH_WORKLOAD_TEMPLATE';

begin

    -- set USERNAME_LIST filter to SH

DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(v_template_name  , 'USERNAME_LIST', 'SYS');

end;

select * from

DBA_ADVISOR_SQLW_PARAMETERS

where workload_name = 'MAH_WORKLOAD_TEMPLATE'

Now, define workload using the above created template

declare

    v_workload_name varchar2(40) := 'MAH_WORKLOAD_TST1';

    v_description varchar2(100) := 'MAH Second Work Load';

    v_template_name  varchar2(40) := 'MAH_WORKLOAD_TEMPLATE';

begin

    dbms_advisor.create_sqlwkld (v_workload_name, v_description, v_template_name );

end;

Linking a Task and a Workload

declare

    v_workload_name varchar2(40) := 'MAH_WORKLOAD_TST1';

    v_task_name varchar2(40) := 'TASK_37762';

begin

    dbms_advisor.add_sqlwkld_ref (v_task_name, v_workload_name );

end;

The following sql gives details on task and workloads that were linked

select * from

DBA_ADVISOR_SQLA_WK_MAP

Defining the Contents of a Workload

Once a workload has been created, it must then be populated with information. Ideally, a workload will consist of the SQL statements (unless it is a hypothetical workload) that are being used against the database. The SQLAccess Advisor can obtain its workload from the following sources:

·         SQL Tuning Set

·         Loading a User-Defined Workload

·         Loading a SQL Cache Workload

·         Using a Hypothetical Workload

·         Using a Summary Advisor 9i Workload

Loading a user defined workload:

declare

    v_saved_stmts number;

    v_failed_rows number;

    v_workload_name varchar2(40) := 'MAH_WORKLOAD';

begin

    dbms_advisor.import_sqlwkld_user (v_workload_name, 'NEW', USER,'USER_WORKLOAD', v_saved_stmts, v_failed_rows );

    dbms_output.put_line (' saved stmts ->' ||v_saved_stmts ||'- ' || 'failed statements ->' || v_failed_rows );

end;

Note: USER_WORKLOAD table would have exists in the users schema along with some workload statements.

In absence of this table, the following error will raise

ORA-13600: error encountered in Advisor

QSM-00765: cannot validate user-defined workload because table OMSOWN.USER_WORKLOAD is invalid.

ORA-06512: at "SYS.PRVT_WORKLOAD", line 2323

ORA-06512: at "SYS.DBMS_ADVISOR", line 1799

ORA-06512: at line 6

Loading a SQL Cache Workload

sql> alter system flush shared_pool;

System altered.

sql> select sum(quantity_sold), prod_id

  2  from sales

  3  where rownum < 2

  4  group by prod_id

  5  /

SUM(QUANTITY_SOLD)    PROD_ID

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

                18       9465

sql> select count(*) from oeorder_header;

  COUNT(*)

----------

     57958

sql> declare

  2      v_saved_stmts number;

  3      v_failed_rows number;

  4      v_workload_name varchar2(40) := 'MAH_WORKLOAD';

  5  begin

  6      dbms_advisor.IMPORT_SQLWKLD_SQLCACHE  (v_workload_name, 'APPEND',2, v_saved_stmts, v_failed_rows );

  7      dbms_output.put_line (' saved stmts ->' ||v_saved_stmts ||'- ' || 'failed statements ->' || v_failed_rows );

  8  end;

  9  /

saved stmts ->20- failed statements ->46

PL/SQL procedure successfully completed.

SQLAccess Advisor Workload Parameters

begin

-- Order statements by OPTIMIZER_COST

 DBMS_ADVISOR.SET_SQLWKLD_PARAMETER (  'MAH_WORKLOAD', 'ORDER_LIST', 'OPTIMIZER_COST');

-- Max number of statements 3

 DBMS_ADVISOR.SET_SQLWKLD_PARAMETER('MAH_WORKLOAD', 'SQL_LIMIT', 3);

end;

SQL Workload Journal

During the import of a workload, various informational messages are recorded in the SQL Workload Journal. These can be viewed using the view USER_ADVISOR_SQLW_JOURNAL. The journal is useful to identify why some statements were filtered out of the workload.

EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER('MAH_WORKLOAD', 'JOURNALING', 0);

Adding SQL Statements to a Workload

declare

    v_saved_stmts number;

    v_failed_rows number;

    v_workload_name varchar2(40) := 'MAH_WORKLOAD';

    v_sql_text VARCHAR2(400) :=  'SELECT AVG(amount_sold) FROM sales';

begin

     DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT (  v_workload_name, 'MONTHLY', 'ROLLUP', priority=>1, executions=>10,  username => USER,  sql_text => v_sql_text);

end;

Deleting SQL Statements from a Workload

select * from

DBA_ADVISOR_SQLA_WK_STMTS

where workload_name = 'MY_CACHE_WORKLOAD1'

EXECUTE DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT('MY_CACHE_WORKLOAD1', 631);

ORA-13600: error encountered in Advisor

QSM-00790: the workload MY_CACHE_WORKLOAD1 cannot be modified or changed because it is being used by task MYTASK

ORA-06512: at "SYS.PRVT_WORKLOAD", line 1449

ORA-06512: at "SYS.DBMS_ADVISOR", line 1580

ORA-06512: at line 1

Changing SQL Statements in a Workload

EXECUTE DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT('MY_CACHE_WORKLOAD1', 631,

priority=>3);

ORA-13600: error encountered in Advisor

QSM-00790: the workload MY_CACHE_WORKLOAD1 cannot be modified or changed because it is being used by task MYTASK

ORA-06512: at "SYS.PRVT_WORKLOAD", line 2634

ORA-06512: at "SYS.DBMS_ADVISOR", line 1974

Setting Workload Attributes

EXECUTE DBMS_ADVISOR.UPDATE_SQLWKLD_ATTRIBUTES (  'MY_CACHE_WORKLOAD1', read_only=> 'TRUE');

Resetting Workloads

EXECUTE DBMS_ADVISOR.RESET_SQLWKLD('MY_CACHE_WORKLOAD1');

Removing a Link Between a Workload and a Task

EXECUTE DBMS_ADVISOR.DELETE_SQLWKLD_REF('MYTASK', 'MY_CACHE_WORKLOAD1');

Removing Workloads

EXECUTE DBMS_ADVISOR.DELETE_SQLWKLD('MY_CACHE_WORKLOAD1');

EXECUTE_TASK Procedure

EXECUTE DBMS_ADVISOR.EXECUTE_TASK('MYTASK');

Viewing the Recommendations

SELECT * FROM USER_ADVISOR_RECOMMENDATIONS

Canceling Tasks:

EXECUTE DBMS_ADVISOR.CANCEL_TASK('MYTASK');

Marking Recommendations

By default, all SQLAccess Advisor recommendations are ready to be implemented, however, the user can choose to skip or exclude selected recommendations by using the MARK_RECOMMENDATIONprocedure. MARK_RECOMMENDATION allows the user to annotate a recommendation with a REJECT or IGNORE setting, which will cause the GET_TASK_SCRIPT to skip it when producing the implementation procedure.

The following example marks a recommendation with ID 2 as REJECT. This recommendation and any dependent recommendations will not appear in the script.

EXECUTE DBMS_ADVISOR.MARK_RECOMMENDATION('MYTASK', 2, 'REJECT');

Modifying Recommendations

Using the UPDATE_REC_ATTRIBUTES procedure, the SQLAccess Advisor names and assigns ownership to new objects such as indexes and materialized views during the analysis operation. However, it does not necessarily choose appropriate names, so you may manually set the owner, name, and tablespace values for new objects. For recommendations referencing existing database objects, owner and name values cannot be changed. 

EXECUTE DBMS_ADVISOR.UPDATE_REC_ATTRIBUTES('MYTASK', 1, 1,  'TABLESPACE', 'SH_MVIEWS');

Generating SQL Scripts

An alternative to querying the metadata to see the recommendations, is to create a script of the SQL statements for the recommendations, using the procedure GET_TASK_SCRIPT. The resulting script is an executable SQL file that can contain DROP, CREATE, and ALTER statements. For new objects, the names of the materialized views, materialized view logs, and indexes are auto-generated by using the user-specified name template. You should review the generated SQL script before attempting to execute it.

CREATE DIRECTORY ADVISOR_RESULTS AS '/tmp';

GRANT READ ON DIRECTORY ADVISOR_RESULTS TO PUBLIC;

GRANT WRITE ON DIRECTORY ADVISOR_RESULTS TO PUBLIC;

EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('MYTASK'), 'ADVISOR_RESULTS', 'advscript.sql');

When Recommendations are No Longer Required

The RESET_TASK procedure resets a task to its initial starting point. This has the effect of removing all recommendations, and intermediate data from the task. The actual task status is set to INITIAL. The syntax is as follows:

EXECUTE DBMS_ADVISOR.RESET_TASK('MYTASK');

Performing a Quick Tune

declare

    v_sql varchar2(200) := 'select * from emp join dept on (emp.deptno = dept.deptno )';

    v_tuning_task varchar2(200) := 'quick_tune_usage_test1';

    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');

   

    dbms_output.put_line ('Info from DBMS_ADVISOR.get_task_script is as follows ');

    dbms_output.put_line ('-----------------------------------------------------------------------------');

    v_tune_result := DBMS_ADVISOR.get_task_script (v_tuning_task);

    dbms_output.put_line ('report Tune Completed   ' || v_tune_result);

    dbms_output.put_line ('-----------------------------------------------------------------------------');

    dbms_output.put_line ('Info from DBMS_ADVISOR.get_task_report is as follows ');

    dbms_output.put_line ('-----------------------------------------------------------------------------');

    v_tune_result := DBMS_ADVISOR.get_task_script (v_tuning_task);

end;

Note:

·         Tuning task name is case sensitive.

·         Dbms_advisor.sqlaccess_advisor is a constant in dbms_advisor package

SQLACCESS_ADVISOR       constant varchar2(30) := 'SQL Access Advisor';

select task_name, description, advisor_name, status, status_message

from dba_advisor_tasks

where task_name like 'quick_tune_usage_test1'

select  task_name, status, status_message, execution_start, execution_end

 from dba_advisor_log

where task_name like 'quick_tune_usage_test1'

select * from

dba_advisor_recommendations

where task_name like 'quick_tune_usage_test1'

Updating Task Attributes

The following example updates the name of an task MYTASK to TUNING1:

EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES('MYTASK', 'TUNING1');

The following example marks the task TUNING1 to read only

EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES('TUNING1', read_only => 'TRUE');

The following example marks the task MYTASK as a template.

EXECUTE DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES('TUNING1', is_template=>'TRUE');

Deleting Tasks

EXECUTE DBMS_ADVISOR.DELETE_TASK('MYTASK');

For Examples -> SQL Access Advisor Examples-ORACLE 11G using DBMS_ADVISOR