'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_