accessadvisor_sqlcache.txt

在oracle中

--创建目录WORK_DIR
create directory TUNE_DIR as 'E:\oracle\product\10.2.0\Tune';

--查看task执行的status
SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'my_sql_tuning_task';

--全部sql
DECLARE 
taskname varchar2(30);
task_desc varchar2(256);
task_or_template varchar2(30);
task_id number;
wkld_name varchar2(30);
saved_rows number;
failed_rows number;
num_found number;
tune_stamp varchar2(20);


BEGIN
task_id := 0;
saved_rows := 0;
failed_rows := 0;
tune_stamp:=to_char(sysdate,'YYYYMMDDHH24MISS');
taskname := 'sqlaccesstune'||tune_stamp;
task_desc := 'SQL 访问指导';
task_or_template := 'SQLACCESS_EMTASK';
wkld_name := 'wkld'||tune_stamp;
/* Create Task */
dbms_advisor.create_task(DBMS_ADVISOR.SQLACCESS_ADVISOR,task_id,taskname,task_desc,task_or_template);
/* Reset Task */
dbms_advisor.reset_task(taskname);
/* Create Workload */
select count(*) into num_found from user_advisor_sqlw_sum where workload_name = wkld_name;
IF num_found = 0 THEN
dbms_advisor.create_sqlwkld(wkld_name,null);
END IF;
/* Reset Workload */
dbms_advisor.reset_sqlwkld(wkld_name);
/* Link Workload to Task */
select count(*) into num_found from user_advisor_sqla_wk_map where task_name = taskname and workload_name = wkld_name;
IF num_found = 0 THEN
dbms_advisor.add_sqlwkld_ref(taskname,wkld_name);
END IF;
/* Set Workload Parameters */
dbms_advisor.set_sqlwkld_parameter(wkld_name,'ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'SQL_LIMIT',DBMS_ADVISOR.ADVISOR_UNLIMITED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'ORDER_LIST','PRIORITY,OPTIMIZER_COST');
dbms_advisor.set_sqlwkld_parameter(wkld_name,'USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'VALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'VALID_SQLSTRING_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_SQLSTRING_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'JOURNALING','4');
dbms_advisor.set_sqlwkld_parameter(wkld_name,'DAYS_TO_EXPIRE','30');
dbms_advisor.import_sqlwkld_sqlcache(wkld_name,'REPLACE',2,saved_rows,failed_rows);
/* Set Task Parameters */
dbms_advisor.set_task_parameter(taskname,'EXECUTION_TYPE','INDEX_ONLY');
dbms_advisor.set_task_parameter(taskname,'MODE','COMPREHENSIVE');
dbms_advisor.set_task_parameter(taskname,'STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLIMITED);
dbms_advisor.set_task_parameter(taskname,'DML_VOLATILITY','TRUE');
dbms_advisor.set_task_parameter(taskname,'ORDER_LIST','PRIORITY,OPTIMIZER_COST');
dbms_advisor.set_task_parameter(taskname,'WORKLOAD_SCOPE','PARTIAL');
dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_MVLOG_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'CREATION_COST','TRUE');
dbms_advisor.set_task_parameter(taskname,'EVALUATION_ONLY','FALSE');
dbms_advisor.set_task_parameter(taskname,'JOURNALING','4');
dbms_advisor.set_task_parameter(taskname,'DAYS_TO_EXPIRE','30');
/* Execute Task */
dbms_advisor.execute_task(taskname);
dbms_advisor.create_file(dbms_advisor.get_task_script(taskname),'TUNE_DIR','AccessAdvisor_SqlCache'||tune_stamp||'.txt');
END;


--按用户
--修改'USERNAME_LIST'部分的用户列表
DECLARE 
taskname varchar2(30);
task_desc varchar2(256);
task_or_template varchar2(30);
task_id number;
wkld_name varchar2(30);
saved_rows number;
failed_rows number;
num_found number;
tune_stamp varchar2(20);

BEGIN
task_id := 0;
saved_rows := 0;
failed_rows := 0;
tune_stamp:=to_char(sysdate,'YYYYMMDDHH24MISS');
taskname := 'sqlaccesstune'||tune_stamp;
task_desc := 'SQL 访问指导';
task_or_template := 'SQLACCESS_EMTASK';
wkld_name := 'wkld'||tune_stamp;

/* Create Task */
dbms_advisor.create_task(DBMS_ADVISOR.SQLACCESS_ADVISOR,task_id,taskname,task_desc,task_or_template);
/* Reset Task */
dbms_advisor.reset_task(taskname);
/* Create Workload */
select count(*) into num_found from user_advisor_sqlw_sum where workload_name = wkld_name;
IF num_found = 0 THEN
dbms_advisor.create_sqlwkld(wkld_name,null);
END IF;
/* Reset Workload */
dbms_advisor.reset_sqlwkld(wkld_name);
/* Link Workload to Task */
select count(*) into num_found from user_advisor_sqla_wk_map where task_name = taskname and workload_name = wkld_name;
IF num_found = 0 THEN
dbms_advisor.add_sqlwkld_ref(taskname,wkld_name);
END IF;
/* Set Workload Parameters */
dbms_advisor.set_sqlwkld_parameter(wkld_name,'ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'SQL_LIMIT','25');
dbms_advisor.set_sqlwkld_parameter(wkld_name,'ORDER_LIST','PRIORITY,OPTIMIZER_COST');
dbms_advisor.set_sqlwkld_parameter(wkld_name,'USERNAME_LIST','HR, SOSV3');
dbms_advisor.set_sqlwkld_parameter(wkld_name,'VALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'VALID_SQLSTRING_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_SQLSTRING_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_sqlwkld_parameter(wkld_name,'JOURNALING','4');
dbms_advisor.set_sqlwkld_parameter(wkld_name,'DAYS_TO_EXPIRE','30');
dbms_advisor.import_sqlwkld_sqlcache(wkld_name,'REPLACE',2,saved_rows,failed_rows);
/* Set Task Parameters */
dbms_advisor.set_task_parameter(taskname,'EXECUTION_TYPE','INDEX_ONLY');
dbms_advisor.set_task_parameter(taskname,'MODE','COMPREHENSIVE');
dbms_advisor.set_task_parameter(taskname,'STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLIMITED);
dbms_advisor.set_task_parameter(taskname,'DML_VOLATILITY','TRUE');
dbms_advisor.set_task_parameter(taskname,'ORDER_LIST','PRIORITY,OPTIMIZER_COST');
dbms_advisor.set_task_parameter(taskname,'WORKLOAD_SCOPE','PARTIAL');
dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_MVLOG_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'CREATION_COST','TRUE');
dbms_advisor.set_task_parameter(taskname,'EVALUATION_ONLY','FALSE');
dbms_advisor.set_task_parameter(taskname,'JOURNALING','4');
dbms_advisor.set_task_parameter(taskname,'DAYS_TO_EXPIRE','30');
/* Execute Task */
dbms_advisor.execute_task(taskname);
dbms_advisor.create_file(dbms_advisor.get_task_script(taskname),'TUNE_DIR','AccessAdvisor_SqlCache'||tune_stamp||'.txt');
END;


--表模式
--创建表
--仅username和sql_text是必须的
CREATE TABLE USER_WORKLOAD(
    MODULE VARCHAR2(64),      -- Empty string Application module name.
    ACTION VARCHAR2(64),      -- Empty string Application action.
    BUFFER_GETS NUMBER,       -- 0 Total buffer-gets for the statement.
    CPU_TIME NUMBER,          -- 0 Total CPU time in seconds for the statement.
    ELAPSED_TIME NUMBER,      -- 0 Total elapsed time in seconds for the statement.
    DISK_READS NUMBER,        -- 0 Total number of disk-read operations used by the statement.
    ROWS_PROCESSED NUMBER,    -- 0 Total number of rows process by this SQL statement.
    EXECUTIONS NUMBER,        -- 1 Total number of times the statement is executed.
    OPTIMIZER_COST NUMBER,    -- 0 Optimizer's calculated cost value for executing the query.
    LAST_EXECUTION_DATE DATE, -- SYSDATE Last time the query is used. Defaults to not available.
    PRIORITY NUMBER,          -- 2 Must be one of the following values:1- HIGH, 2- MEDIUM, or 3- LOW
    SQL_TEXT VARCHAR2(4000),        -- or CLOB or LONG or VARCHAR2 None The SQL statement. This is a required column.
    STAT_PERIOD NUMBER,       -- 1 Period of time that corresponds to the execution statistics in seconds.
    USERNAME VARCHAR(30)      --Current user User submitting the query. This is a required column.
);

--须替换Schema
--向表中插入SQL_TEXT列插入sql语句
VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255);
EXECUTE :task_name := 'MYTASK';
VARIABLE workload_name VARCHAR2(255);
EXECUTE :workload_name := 'MYWORKLOAD';
VARIABLE saved_stmts NUMBER;
VARIABLE failed_stmts NUMBER;
--create task;
EXECUTE DBMS_ADVISOR.CREATE_TASK ('SQL Access Advisor', :task_id, :task_name);
--create workload
EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name,'This is my first workload');
--link
EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF('MYTASK', 'MYWORKLOAD');
--LOAD USER-DEFINED TABLE
EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_USER('MYWORKLOAD', 'NEW', 'SOSV3', 'USER_WORKLOAD', :saved_stmts, :failed_stmts);
--APPPEND SQL CACHE
--EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE ('MYWORKLOAD', 'APPEND', 2, :saved_stmts, :failed_stmts);
-- Order statements by OPTIMIZER_COST
EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER ('MYWORKLOAD', 'ORDER_LIST', 'OPTIMIZER_COST');
-- Max number of statements 3
EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER('MYWORKLOAD', 'SQL_LIMIT', 100);
--EXECUTE TASK
EXECUTE DBMS_ADVISOR.EXECUTE_TASK('MYTASK');
--REPORT
EXECUTE dbms_advisor.get_task_report('MYTASK','TEXT','ALL'),'TUNE_DIR','sqlaccess.txt');