本文共 5647 字，大约阅读时间需要 18 分钟。

说明：本文为SAA（SQL Access Advisor）使用参考手册

用途：本文仅供初学者熟悉了解SQL Access Advisor或优化参考

标签：SQL Access Advisor、SAA、SAA使用方法、Oracle优化、SQL调优、SAA流程图

温馨提示：如果您发现本文哪里写的有问题或者有更好的写法请留言或私信我进行修改优化

高级内容：由于篇幅原因，部分高级内容和详情没有在此展现，如有需要可以留言或私信

---

★知识点

※ SQL Access Advisor是伴随着10G出现的一个优化工具,提供对表分区,物化视图,索引,物化视图日志优化建议 ※ 查看advisor的默认参数：select * from dba_advisor_def_parameters order by 2;

★ SAA流程图

---

★ STS作为输入源

--本文选取了STS的输入源类型之A【Cursor Cache】 → dbms_sqltune.select_cursor_cache --模拟DML操作产生CURSOR_CACHE sqlplus scott/tiger set line 170 set pages 200 select count(*) from scott.emp where sal>3000;

--删除SAA exec dbms_advisor.delete_task('ZZT_SQL_ACCESS_TASK'); exec dbms_advisor.delete_sts_ref('ZZT_SQL_ACCESS_TASK','SCOTT','ZZT_SQL_TUNING_SET');

--删除STS BEGIN   DBMS_SQLTUNE.DROP_SQLSET(sqlset_name  => 'ZZT_SQL_TUNING_SET',                            sqlset_owner => 'SCOTT'); END; /

--创建STS BEGIN   DBMS_SQLTUNE.CREATE_SQLSET(     sqlset_name  => 'ZZT_SQL_TUNING_SET',      sqlset_owner => 'SCOTT',     description  => 'test'); END; /

--查看STS select * from dba_sqlset;

--加载符合条件的SQL到STS

--从18C开始STS系统包发生了变化DBMS_SQLTUNE→DBMS_SQLSETDECLARE  zzt_cur_sqlarea DBMS_SQLTUNE.SQLSET_CURSOR; --定义游标参数  --zzt_cur_sqlarea sys_refcursor;    --也可以直接使用系统游标BEGIN  OPEN zzt_cur_sqlarea FOR    SELECT VALUE(p)      FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(basic_filter   => 'parsing_schema_name = ''SCOTT''',                                                  attribute_list => 'all')) p;  -- load the tuning set  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name       => 'ZZT_SQL_TUNING_SET',                           populate_cursor   => zzt_cur_sqlarea,                           sqlset_owner      => 'SCOTT',                           load_option       => 'INSERT',                            update_option     => 'REPLACE',                           update_condition  => 'new.executions >= old.executions',                           update_attributes => 'ALL',                           ignore_null       => TRUE,                           commit_rows       => NULL);END;/

--读取最新的STS详情 --COLUMN SQL_TEXT FORMAT a30    --COLUMN SCH FORMAT a3 --COLUMN ELAPSED FORMAT 999999999 SELECT *   FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name  => 'ZZT_SQL_TUNING_SET',                                         sqlset_owner => 'SCOTT'))  where lower(SQL_TEXT) like 'select count(*) from scott%'  order by FORCE_MATCHING_SIGNATURE;

 

--SAA

--删除旧任务 exec dbms_advisor.delete_task('ZZT_SQL_ACCESS_TASK'); exec dbms_advisor.delete_sts_ref('ZZT_SQL_ACCESS_TASK','SCOTT','ZZT_SQL_TUNING_SET');

-- Examples of Using SQL Access Advisor

variable  zzt_saa_task_name    VARCHAR2(50) ;variable  zzt_saa_task_desc   VARCHAR2(128);variable  zzt_saa_wkld_name   VARCHAR2(50) ;execute   :zzt_saa_task_name  := 'ZZT_SQL_ACCESS_TASK';execute   :zzt_saa_task_desc := 'ZZT SQL Access Task';execute   :zzt_saa_wkld_name := 'ZZT_SQL_TUNING_SET' ;DECLARE--此处使用绑定变量，也可以使用常规变量BEGIN    -- create a sql access advisor task.    DBMS_ADVISOR.create_task(advisor_name => DBMS_ADVISOR.sqlaccess_advisor,                             task_name    => :zzt_saa_task_name,                             task_desc    => :zzt_saa_task_desc);    -- reset the task.    DBMS_ADVISOR.reset_task(task_name => :zzt_saa_task_name);    -- Create a link between the SQL tuning set and the task    DBMS_ADVISOR.ADD_STS_REF(task_name     => :zzt_saa_task_name,                             sts_owner     => 'SCOTT',                             workload_name => :zzt_saa_wkld_name);    -- set saa task parameters    dbms_advisor.set_task_parameter(:zzt_saa_task_name, 'execution_type','INDEX_ONLY');    -- Execute the task.    DBMS_ADVISOR.execute_task(task_name => :zzt_saa_task_name);END;/

-- 查看SAA建议详情的存储过程show_recm

-- 该存储过程已被修改优化-- 调试时如果有报错，可以执行命令查看详情：show errors;CREATE OR REPLACE PROCEDURE show_recm (in_task_name IN VARCHAR2) IS CURSOR curs IS  SELECT DISTINCT action_id, command, attr1, attr2, attr3, attr4 ,dbms_lob.substr(attr5,50) attr5   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, v_attr5 ;   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 (table)     : ' || SUBSTR(v_attr3,1,30));   DBMS_OUTPUT.PUT_LINE('Attr4 (type)      : ' || v_attr4);   DBMS_OUTPUT.PUT_LINE('Attr5 (columns)   : ' || v_attr5);   DBMS_OUTPUT.PUT_LINE('----------------------------------------');     END LOOP;      CLOSE curs;         DBMS_OUTPUT.PUT_LINE('=========END RECOMMENDATIONS============');END show_recm;/

-- 查看SAA建议 -- SEE WHAT THE ACTIONS ARE USING SAMPLE PROCEDURE. SET SERVEROUTPUT ON SIZE 99999 set long 999999 set pages 200 set line 170 EXECUTE SHOW_RECM('ZZT_SQL_ACCESS_TASK');

--输出样例

SQL> EXECUTE SHOW_RECM('ZZT_SQL_ACCESS_TASK');=========================================Task_name = ZZT_SQL_ACCESS_TASKAction ID: 1Command  : CREATE INDEXAttr1 (name)      : "SCOTT"."ZZT_IDX$$_0000"Attr2 (tablespace): "USERS"Attr3 (table)     : "SCOTT"."EMP"Attr4 (type)      : BTREEAttr5 (columns)   : ("SAL")----------------------------------------=========END RECOMMENDATIONS============PL/SQL 过程已成功完成。

 

★ 参考文档

※ ※ ※

---

※ 如果您觉得文章写的还不错， 别忘了在文末给作者点个赞哦 ~

over

转载地址：https://blog.csdn.net/zzt_2009/article/details/107517194 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！