oracle saa使用

原文：https://blog.csdn.net/zzt_2009/article/details/107517194说明：本文为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_CACHEsqlplus scott/tigerset line 170set pages 200select count(*) from scott.emp where sal>3000;--删除SAAexec dbms_advisor.delete_task('ZZT_SQL_ACCESS_TASK');exec dbms_advisor.delete_sts_ref('ZZT_SQL_ACCESS_TASK','SCOTT','ZZT_SQL_TUNING_SET');--删除STSBEGIN DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'ZZT_SQL_TUNING_SET', sqlset_owner => 'SCOTT');END;/--创建STSBEGIN DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'ZZT_SQL_TUNING_SET', sqlset_owner => 'SCOTT', description => 'test');END;/--查看STSselect * 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 999999999SELECT * 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 Advisorvariable 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 99999set long 999999set pages 200set line 170EXECUTE 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 过程已成功完成。 ★ 参考文档※ Oracle官方文档>>>SQL Access Advisor※ Angus_yang>>>SAA-sql access advisor使用(oracle 11.2后版本)※ davedba>>>Oracle SQL Access Advisor 说明