SAA-sql access advisor使用(oracle11.2后版本) - 数据库编程

最近使用SAA，发现网上很多博客讲解的其实在oracle 11.2之后的后续版本中使用或多或少都有问题，

要么版本改变语法改变要么不够全，花点时间罗列一下新版本的使用.

1.使用介绍
*****************************************

步骤:
创建一个任务,并定义参数;
定义负载;
生成一些建议;
查看并应用建议;

SAA的主要建议有:
创建/删除物化视图;
创建/删除物化视图日志;
创建/删除索引;
收集统计信息;
生成SQL脚本:
创建DIRECTORY;
授权给用户;
生成脚本;

*****************************************
2.SAA使用
*****************************************
-----2.1 案例1:

---2.1.1 create a STS

BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'OCPYANG_STS',
sqlset_owner => 'SCOTT',
description => 'ocpyangtest');
END;
/

--2.1.2 Load the sql into SQL tuning set

--方法1:from MEM

BEGIN
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
sqlset_owner =>'SCOTT'
, sqlset_name => 'OCPYANG_STS'
,time_limit => 120 --3600秒
,repeat_interval => 20); --每隔20秒
END;
/

--方法2： FROM AWR

declare
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap_id, &end_snap_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('OCPYANG_STS', baseline_ref_cur);
end;
/

输入 begin_snap 的值: 11647
egin Snapshot Id specified: 11647

输入 end_snap 的值: 11859
nd Snapshot Id specified: 11859

--或指明sql_id
?

declare
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap_id, &end_snap_id,'sql_id='||CHR(39)||'&sql_id'||CHR(39)||'',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('OCPYANG_STS', baseline_ref_cur);
end;
/

---方法3:FROM CURSOR CACHE

DECLARE
cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN cur FOR
SELECT VALUE(x)
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
'parsing_schema_name <> ''SYS'' AND disk_reads > 1000000',
NULL, NULL, NULL, NULL, 1, NULL,'ALL')) x;
--
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_owner =>'SCOTT',
sqlset_name => 'OCPYANG_STS',
populate_cursor => cur);
END;
/

---查看STS具体内容

SELECT sqlset_name, sql_text
FROM dba_sqlset_statements
WHERE sqlset_name = 'OCPYANG_STS';

---2.1.3 Examples of Using SQL Access Advisor

DECLARE
u_taskname VARCHAR2(50) := 'ocpyang_sql_access_task';
u_task_desc VARCHAR2(128) := 'ocpyang SQL Access Task';
u_wkld_name VARCHAR2(50) := 'ocpyang_work_load';
u_saved_rows NUMBER := 0;
u_failed_rows NUMBER := 0;
u_num_found NUMBER;
BEGIN
--step1:reset task
DBMS_ADVISOR.RESET_TASK(task_name => u_taskname);

--step2:delete exists task
dbms_advisor.delete_sqlwkld_ref(u_taskname, u_wkld_name);
dbms_advisor.delete_sqlwkld(u_wkld_name);
dbms_advisor.delete_task(u_taskname);
EXCEPTION
WHEN OTHERS THEN
NULL;

-- step3:Create a SQL Access Advisor task.
DBMS_ADVISOR.create_task (
advisor_name => DBMS_ADVISOR.sqlaccess_advisor,
task_name => u_taskname,
task_desc => u_task_desc);

--step4:Reset the task.
--DBMS_ADVISOR.reset_task(task_name => u_taskname);


--step5:Set task parameters
DBMS_ADVISOR.SET_TASK_PARAMETER (
task_name => u_taskname,
parameter => 'VALID_TABLE_LIST',
value => 'SCOTT.%');


--step6:Create a link between the SQL tuning set and the task
DBMS_ADVISOR.ADD_STS_REF(
task_name => u_taskname,
sts_owner => 'SCOTT',
workload_name =>

SAA-sql access advisor使用(oracle11.2后版本)(一)