oracle b99999,Oracle 优化篇+SAA（SQL Access Advisor）用法

说明：本文为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_SQLSET

DECLARE

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_TASK

Action ID: 1

Command : CREATE INDEX

Attr1 (name) : "SCOTT"."ZZT_IDX$$_0000"

Attr2 (tablespace): "USERS"

Attr3 (table) : "SCOTT"."EMP"

Attr4 (type) : BTREE

Attr5 (columns) : ("SAL")

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

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

PL/SQL 过程已成功完成。

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

over