SAA-sql access advisor使用(oracle 11.2後版本)，saa-sqladvisor

SAA-sql access advisor使用(oracle 11.2後版本)，saa-sqladvisor

最近使用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;
/