大多数情况下,查询优化器都会在生成的若干种执行计划中找出最优的进行执行。但有的时候,由于SQL复杂,在短时间内无法找到最佳的执行计划,最终导致查询性能很糟糕。
SQL概要就是对某条SQL语句选择生成的统计信息进行修正并改进的数据库对象,SQL概要可以很好地帮助优化器找出更好的执行计划。一旦SQL概要被接受,则该SQL的执行计划将被绑定,具有很好的稳定性。
ORACLE支持的创建SQL概要文件的唯一方法就是运行SQL调优顾问。
一、使用DBMS_SQLTUNE创建调优任务
创建一个与出现问题的SQL语句相关的调优任务,在下面的代码中,编写了SQL代码文本,作为my_sqltext的变量输入:
declare
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select * from test';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext,
user_name => 'hr',
scope => 'COMPREHENSIVE',
time_limit => 60, --优化限时60s
task_name => 'l_tuning_task',
description => 'tune the bad sql');
END;
/
注意:对于复杂的SQL语句,可以适度调整限时。
二、通过SQL_ID进行优化
倘若需要优化的sql是从AWR报告中获取的,我们可以根据SQL_ID简化创建调优任务,在下面的代码中,我们对sql_id='9qjctmkwvny7j'的SQL进行调优任务创建:
declare
l_tuning_task varchar2(30);
begin
l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => '9qjctmkwvny7j');
end;
/
注意:我们也可以通过v$sql系统表获取对应sql的sql_id
三、执行调优任务
根据上面部分,通过SQL或SQL_ID创建一个调优任务后,我们就可以运行SQL调优顾问来生成调优任务相关的查询建议,下面代码为运行任务名为“l_tuning_task”的调优任务:
dbms_sqltune.execute_tuning_task(l_tuning_task);
/
注意:调优任务需要确保数据库账号具有ADMINISTER SQL MANAGEMENT OBJECT
四、运行调优建议报告
现在使用DBMS_SQLTUNE来提取所有的优化建议,其中l_tuning_task为调优任务名称
set serveroutput on
set long 10000
set lines 132
set pages 200
select dbms_sqltune.report_tuning_task("l_tuning_task") from dual
/
注意:当SQL调优顾问推荐创建一个SQL概要时,我们只需要创建并接受SQL概要,即可完成SQL概要文件的启用,不管什么时候执行相关查询,优化器都会考虑SQL概要文件
如下是接受SQL概要的方法:
begin
dbms_sqltune.accept_sql_profile(
task_name => 'l_tuning_task',
task_owner => 'hr',
replace => true,
profile_type => dbms_sqltune.px_profile);
end;
/
领取专属 10元无门槛券
私享最新 技术干货