SAA-sql access advisor使用(oracle 11.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 => 'OCPYANG_STS');

--step7: Execute the task.
  DBMS_ADVISOR.execute_task(task_name => u_taskname);

END;
/

 

---2.1.4 View the recommendations

-- See recommendation for each query.
SELECT sql_id, rec_id, precost, postcost,
      (precost-postcost)*100/precost AS percent_benefit
FROM user_advisor_sqla_wk_stmts
WHERE task_name = 'ocpyang_sql_access_task' AND workload_name = 'OCPYANG_STS';

-- See the actions for each recommendations.
SELECT rec_id, action_id, SUBSTR(command,1,30) AS command
FROM user_advisor_actions
WHERE task_name = 'ocpyang_sql_access_task'
ORDER BY rec_id, action_id;

-- See what the actions are using sample procedure.
SET SERVEROUTPUT ON SIZE 99999
EXECUTE show_recm('ocpyang_sql_access_task');

-----2.2 案例2:将执行结果保存到文件

---2.2.1  Generate a script to Implement the recommendations

/*********sql 语法

DBMS_ADVISOR.CREATE_FILE (
   buffer       IN  CLOB,
   location     IN  VARCHAR2,
   filename     IN  VARCHAR2);

parameters:
location:Specifies the directory that will contain the new file,
You must use the directory alias as defined by the CREATE DIRECTORY statement,
The Advisor will translate the alias into the actual directory location.

filename:Specifies the output file to receive the script commands,
The filename can only contain the name and an optional file type of the form.

**********************/

 

CONNECT / as sysdba
CREATE  or replace DIRECTORY ADVISOR_RESULTS AS '/home/oracle/recd';

host mkdir /home/oracle/recd

GRANT READ,WRITE ON DIRECTORY ADVISOR_RESULTS TO PUBLIC;

select * from all_directories where DIRECTORY_NAME='ADVISOR_RESULTS';

 

---2.2.2 create a STS

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

--2.2.3Load the SQL tuning set from cache and query the STS

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

 

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

---2.2.4 Examples of Using SQL Access Advisor and  Generate a script

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_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 => 'OCPYANG_STS');

--step7: Execute the task.
  DBMS_ADVISOR.execute_task(task_name => u_taskname);

--step8:
DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(u_taskname),
'ADVISOR_RESULTS','rec01.sql');

END;
/