作者为: 

SHOUG成员 – ORACLE ACS高级顾问罗敏

 

 

 

1. 多年前的一段往事

记得多年以前在一个10g平台的数据仓库项目上遇到一个非常难优化的SQL语句，当时即便我采集了统计信息、甚至在语句中增加了HINT，Oracle产生的执行计划都不如人意。最后，不得不通过SR寻求老外高手的指点，他建议我采用10g刚出炉的一个新技术，即让我为该语句生成SQL Profile信息，然后再执行该语句。一切OK了，太神了！

也记得当时我问老外，以后是不是遇到非常复杂的、优化难度很大的SQL语句，就扔给Oracle，特别是产生一遍SQL Profile来辅助优化器时？鬼子不无得意地回答：“That’s right!”

 

2. 再次感叹SQL Profile的牛！

若干年之后的2014年，在面对一条将近200行的SQL语句进行优化时，发现该语句执行计划已经基本找不出明显问题，例如既没有全表扫描，也没有全索引扫描，甚至语句的Cost也非常低（当然Cost并不十分准确）。但是语句执行效率并不高，达到30秒，资源消耗也非常高，例如Buffer Gets达到1,246,155次。客户当然不满意，如何进一步优化？

山穷水尽之际，想起了上述多年前的往事，更想起了神奇的SQL Profile技术。于是，在搜索到最新的11g文档《Automatic SQL Tuning and SQL Profiles (Doc ID 271196.1)》之后，照猫画虎般地开练了。效果如何？以下就是优化前后的对比：

这是优化之前的各项指标：

Stat Name	Statement Total	Per Execution	% Snap Total
Elapsed Time (ms)	30,273	30,272.96	17.76
CPU Time (ms)	29,968	29,968.19	17.79
Executions	1
Buffer Gets	1,246,155	1,246,155.00	14.68
Disk Reads	5,437	5,437.00	0.80

这是优化之后的各项指标：

Stat Name	Statement Total	Per Execution	% Snap Total
Elapsed Time (ms)	4,653	4,652.71	3.00
CPU Time (ms)	4,470	4,470.23	2.90
Executions	1
Buffer Gets	303,480	303,480.00	2.32
Disk Reads	9,740	9,740.00	1.39

可见，语句响应速度从30秒下降到4秒多，Buffer Gets从1,246,155下降到303,480！我对语句没做任何改动，也没创建新的索引，执行计划就更好了，实际效果更是如此的好！SQL Profile牛啊！

 

3. 实施细节

下面就是11g自动优化工具和SQL Profile技术综合运用的详细过程：

• 生成自动优化任务

declare

my_task_name VARCHAR2(30);

my_sqltext CLOB;

begin

my_sqltext := ‘<欲调优的SQL语句文本>’;

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_text => my_sqltext,

user_name => ‘<用户名>’,

scope => ‘COMPREHENSIVE’,

time_limit => 60,

task_name => ‘test1’,

description => ‘Task to tune a query on a specified table’);

end;

/

• 执行自动优化任务

begin

DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘test1’);

end;

/

• 查询Oracle产生的自动优化报告

set long 10000

set longchunksize 1000

set linesize 100

set heading off

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘test1’) from DUAL;

set heading on

• 接受Oracle自动优化任务产生的SQL Profile

DECLARE

my_sqlprofile_name VARCHAR2(30);

begin

my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (

task_name => ‘test1’,

name => ‘test1’);

end;

/

OK了，可以运行需要调优的语句，并观察优化效果了。效果就是上面显示的那组令人激动不已的数据，而且在Oracle产生的新执行计划中，明白无误地显示采用SQL Profile了：

“SQL profile “test1” used for this statement ”

 

各位读者现在只需将你需要优化的语句和所属用户名填入上述脚本之中，也可以照葫芦画瓢开练了。

 

4. SQL Profile到底是什么东西？

SQL Profile信息存储在Oracle数据字典之中，除了dba_sql_profiles视图显示的有限信息之外，的确有种看不见、摸不着的讳莫如深的感觉。SQL Profile到底是什么东西？其实SQL Profiling可以与表和统计信息的关系相类比，SQL Profile就是一条SQL语句的统计信息。例如：当我们遇到一个复杂且资源消耗非常大SQL语句时，Oracle可通过一些取样的数据，或者可以执行该语句一个片段，以及分析该语句的历史执行情况，来评估整体执行计划是否最优化。而这些辅助信息，就是SQL Profile信息，并保存在数据字典之中。

SQL Profiling工作原理如下图：

即上图上半部分显示11g自动优化工具SQL Tuning Advisor在针对某条SQL语句产生SQL Profile信息之后，在上图的下半部分，当Oracle正式需要执行该SQL语句时，优化器不仅利用该语句所访问对象的统计信息，而且利用SQL Profile信息，来产生整体上更优的执行计划。

 

5. 什么时候该使用自动调优工具和SQL Profile？

Oracle 11g的自动调优工具和SQL Profile的确像潘多拉盒子一样充满魔力。继续上述优化案例，尽管该语句被Oracle优化了，但我仔细对照了优化前后50多步的执行计划，怎么也没找出到底是哪些步骤被Oracle优化得效果如此之好，真是太神奇了!

是否一遇到复杂语句就依靠自动调优工具和SQL Profile进行优化呢？且慢，首先，尽管应用性能问题很多，但最主要的问题还是一些传统的、基础性问题。例如：缺乏合适的索引；复合索引设计不合理，特别是索引顺序不对，导致索引效率不高；SQL语句中错误地使用函数，导致索引无法使用；等等。针对这些问题，合理运用20%的基础技术，特别是索引技术，其实能解决80%的问题。这些技术也是DBA和应用开发人员的基本功和基本设计开发规范，过度依赖自动化工具反而会让我们自己的基本技能退化的。其次，自动调优工具和SQL Profile也非包治百病的灵丹妙药，也有看走眼的时候。Oracle自动工具怎么可能比你更了解你的数据模型和数据分布情况，进而给出更准确的优化策略呢？第三，Oracle自动工具使用起来也并不简单，而且需要DBA与开发人员紧密配合，针对大部分基础性问题，有经验的DBA和开发人员其实一眼就能看出问题，何必杀鸡用牛刀呢？

那何时使用自动调优工具和SQL Profile进行优化呢？本人的经验：当针对一些复杂SQL语句，运用传统的、人工分析方法难以奏效时，建议尝试使用这些新技术。

 

无论如何，Oracle 11g的自动调优工具和SQL Profile还是牛！不得不服！