ORACLE概要文件--sqlprofile(一）(一)

SQL PROFILE在ORACLE10g中引入，从官方文档和网络资料可以看出，SQL PROFILE的主要目的侧重于SQL优化，其底层的实现细节可能主要依赖于hint。与outline相比，个人人文SQLPROFILE侧重于为优化器提供辅助信息从而促使优化器选择最优的执行计划，而outline侧重于固化执行计划，两者的目的是有区别的。但是由于SQL PROFILE和OUTLINE的底层技术支持均为hint，因此sqlprofile也可以实现固化执行计划的目的。

我们知道oracle在执行sql语句时会尽力选择最优的执行计划，但是出于各个方面的平衡，oracle最终选择的执行计划未必是最优的。在这种情况下，oracle10g引入了自动调整优化器（AUTOMATIC TUNING OPTIMIZER），自动调整优化器作为优化器的一个扩展而存在。在正常环境下，oracle优化器会在最快的时间内获取执行计划，不会利用自动调整优化器。当我们需要调优某条sql语句时，我们将该语句提交给自动调优优化器，自动调优优化器会尽可能选择最优执行计划，而不会考虑选择该执行计划所花费的时间，甚至在某些情况下，自动调整优化器会进行what-if分析，或者实际的执行sql语句来验证执行计划的效率。

下面的图片展示了SQL PROFILE的生成和使用过程：

生成和管理SQL PROFILE可以通过DBMS_SQLTUNE或者企业管理器的图形界面，下面我们使用DBMS_SQLTUNE来演示SQL PROFILE的管理操作，聪明的你如果可以使用DBMS_SQLTUNE来管理SQLPROFILE，肯定也可以使用图形界面。

首先构建我们的测试环境

SQL> create table t1 as select object_id,object_name from dba_objects where rownum<=50000;

表已创建。

SQL> create table t2 as select * from dba_objects;

表已创建。

SQL> create index t2_idx on t2(object_id);

索引已创建。

SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size 1');

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size 1');

PL/SQL 过程已成功完成。

SQL> !cat tune_last_statement.sql
declare
l_sql_id v$sql.sql_id%TYPE;
l_tuning_task varchar2(30);
begin
 select sql_id into l_sql_id from v$sql where sql_text like 'select t1.*,t2.owner from t1,t2 where t1.object_name%'  and rownum =1;
 l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
 dbms_sqltune.execute_tuning_task(l_tuning_task);
 dbms_output.put_line(l_tuning_task);
end

在原始状态下的sql语句执行过程

SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' AND T1.object_id=t2.object_id;

已选择36行。

执行计划
----------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |  2500 |   100K|   370   (1)| 00:00:05 |
|*  1 |  HASH JOIN	   |	  |  2500 |   100K|   370   (1)| 00:00:05 |
|*  2 |   TABLE ACCESS FULL| T1   |  2500 | 75000 |    69   (2)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 75308 |   808K|   300   (1)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
	      NOT NULL)

统计信息
----------------------------------------------------------
	  0  recursive calls
	  1  db block gets
	317  consistent gets
	  0  physical reads
	  0  redo size
       2141  bytes sent via SQL*Net to client
	542  bytes received via SQL*Net from client
	  4  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 36  rows processed

对sql语句进行调优

SQL> @tune_last_statement.sql
 11  /
任务_309

查看调优报告：

SQL> select dbms_sqltune.report_tuning_task('任务_309') as re from dual;

RE
-----------------------------------------------------------------------------------------------------------