课程第43次

secoug · 发表于 2019-8-24 09:34:43

Notice: This blog is written by Bo Tang.

SQL Tuning Advisor不能选择limited，但是SQL Access Advisor可以

QQ图片20190824093206.png

SQL Access Advisor一定要选择“删除无效对象”：

QQ图片20190824093416.png

DECLARE
taskname varchar2(30) := 'SQLACCESS4393242';
task_desc varchar2(256) := 'SQL Access Advisor';
task_or_template varchar2(30) := 'SQLACCESS_EMTASK';
task_id number := 0;
num_found number;
sts_name varchar2(256) := 'SQLTUNINGSET1';
sts_owner varchar2(30) := 'SYS';
BEGIN
/* 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);
/* Delete Previous STS Workload Task Link */
select count(*) into num_found from user_advisor_sqla_wk_map where task_name = taskname and workload_name = sts_name;
IF num_found > 0 THEN
dbms_advisor.delete_sts_ref(taskname, sts_owner, sts_name);
END IF;
/* Link STS Workload to Task */
dbms_advisor.add_sts_ref(taskname,sts_owner, sts_name);
/* Set STS Workload Parameters */
dbms_advisor.set_task_parameter(taskname,'VALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'VALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'SQL_LIMIT',DBMS_ADVISOR.ADVISOR_UNLIMITED);
dbms_advisor.set_task_parameter(taskname,'VALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'VALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'VALID_SQLSTRING_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_SQLSTRING_LIST','"@!"');
/* Set Task Parameters */
dbms_advisor.set_task_parameter(taskname,'ANALYSIS_SCOPE','ALL');
dbms_advisor.set_task_parameter(taskname,'RANKING_MEASURE','PRIORITY,OPTIMIZER_COST');
dbms_advisor.set_task_parameter(taskname,'DEF_PARTITION_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'TIME_LIMIT',10000);
dbms_advisor.set_task_parameter(taskname,'MODE','LIMITED');
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,'WORKLOAD_SCOPE','FULL');
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,'JOURNALING','4');
dbms_advisor.set_task_parameter(taskname,'DAYS_TO_EXPIRE','30');
/* Execute Task */
dbms_advisor.execute_task(taskname);
END;

复制代码

红条和蓝条有高度差，就说明有调优的结果。如果一样高，请务必收集一下可能会涉及到的对象的优化器统计信息（利用同一个SQL调优集，做STA），再来SAA。

begin
dbms_advisor.quick_tune(advisor_name => dbms_advisor.SQLACCESS_ADVISOR,
task_name => 'MYTASK11',
attr1 => 'select max(line) from hr.tbig where length(TEXT) >=100 group by text having max(line) >=60',
template => dbms_advisor.SQLACCESS_OLTP);
end;
begin
dbms_advisor.quick_tune(advisor_name => dbms_advisor.SQLACCESS_ADVISOR,
task_name => 'MYTASK12',
attr1 => 'select max(line) from hr.tbig where length(TEXT) >=100 group by text having max(line) >=60',
template => dbms_advisor.SQLACCESS_WAREHOUSE);
end;

复制代码

CREATE MATERIALIZED VIEW LOG ON
"HR"."TBIG"
WITH ROWID, SEQUENCE("LINE","TEXT")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW "SYS"."MV$_01DD0000"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT HR.TBIG.TEXT C1, MAX("HR"."TBIG"."LINE") M1, COUNT(*) M2 FROM HR.TBIG
GROUP BY HR.TBIG.TEXT;
begin
dbms_stats.gather_table_stats('"SYS"','"MV$_01DD0000"',NULL,dbms_stats.auto_sample_size);
end;
/
CREATE INDEX "SYS"."MV$_01DD0000_IDX$_01DD0000"
ON "SYS"."MV$_01DD0000"
("M1")
COMPUTE STATISTICS;

复制代码

<p>CREATE MATERIALIZED VIEW LOG ON
"HR"."TBIG"
WITH ROWID, SEQUENCE("LINE","TEXT")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW "SYS"."MV$_01DD0000"
REFRESH FORCE WITH ROWID
ENABLE QUERY REWRITE
AS SELECT HR.TBIG.TEXT C1, MAX("HR"."TBIG"."LINE") M1, COUNT(*) M2 FROM HR.TBIG
where length(TEXT) >=100
GROUP BY HR.TBIG.TEXT
having max(line) >=60</p><p>;
begin
dbms_stats.gather_table_stats('"SYS"','"MV$_01DD0000"',NULL,dbms_stats.auto_sample_size);
end;
/
CREATE INDEX "SYS"."MV$_01DD0000_IDX$_01DD0000"
ON "SYS"."MV$_01DD0000"
("M1")
COMPUTE STATISTICS;
drop MATERIALIZED VIEW "SYS"."MV$_01DD0000";
</p>

复制代码

快速调优有隐式的SQL调优集。

SPA的结果：

QQ图片20190824110143-2.png