Oracle 11g从发布到现在,也有几个年头了。而在国内来说,Oracle 10g仍然是主流,甚至一些电信运营商的核心系统仍然在使用9i。作为Oracle 10g的一项新特性,SQL Profiles被使用得并不太多。不管是在论坛、个人的BLOG还是其他一些地方,SQL Profiles的介绍也相对较少。对我个人来说,已经在多个优化场合中使用SQL Profiles,在这里向大家介绍SQL Profiles,就是希望能够了解Oracle数据库的这一功能。
SQL Profiles可以说是Outlines的进化。Outlines能够实现的功能SQL Profiles也完全能够实现,而SQL Profiles具有Outlines不具备的优化,个人认为最重要的有2点:
- SQL Profiles更容易生成、更改和控制。
- SQL Profiles在对SQL语句的支持上做得更好,也就是适用范围更广。
关于这2方面的优点,我后面会详细地阐述。
现在我在使用Outlines的场合,均使用SQL Profiles来替代。有一次准备对1条SQL语句使用Outline进行执行计划的稳定,结果使用Outline之后,系统出现大量的library cache latch的争用,不得不关闭Outline的使用,但是改用SQL Profiles不再有这个问题。这或许是个BUG,不过既然能用SQL Profiles代替,也就没再深入去研究这个问题。
使用SQL Profiles无非是两个目的:
- 锁定或者说是稳定执行计划。
- 在不能修改应用中的SQL的情况下使SQL语句按指定的执行计划运行。
那么SQL Profile到底是什么?在我看来,SQL Profile就是为某一SQL语句提供除了系统统计信息、对象(表和索引等)统计信息之外的其他信息,比如运行环境、额外的更准确的统计信息,以帮助优化器为SQL语句选择更适合的执行计划。这些说法显得比较枯燥,还是来看看下面的测试。
首先建2个测试表:
- 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:
- SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
- 已选择29行。
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 1838229974
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 2498 | 99920 | 219 (4)| 00:00:03 |
- |* 1 | HASH JOIN | | 2498 | 99920 | 219 (4)| 00:00:03 |
- |* 2 | TABLE ACCESS FULL| T1 | 2498 | 72442 | 59 (6)| 00:00:01 |
- | 3 | TABLE ACCESS FULL| T2 | 49954 | 536K| 159 (2)| 00:00:02 |
- ---------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
- 2 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
- 统计信息
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 932 consistent gets
- 0 physical reads
- 0 redo size
- 1352 bytes sent via SQL*Net to client
- 385 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 29 rows processed
这里省略了SELECT出来的具体数据,但是我们关心的是返回的结果行数、执行计划以及逻辑读这些信息。
首先从执行计划可以看到,这条SQL语句在2个表上都是全表扫描。在第1个表T1上,有 like ‘%T1%’这样的条件,导致只能全表扫描,这没有问题。但是第2个表,也是全表扫描,这里有没有问题呢?或者说是有没有优化的余地,答案显然是肯定的。
这里的问题在于执行计划ID=1的那一行,Oracle优化器评估T1 like ‘%T1%’返回的结果行数为2498行,即T1表总行数的5%,如果2个表采用index range scan+nested loop连接,oracle评估的成本会高于full table scan+hash join。下面可以看到Oracle优化器评估的index range_scan+nested loop的成本:
- SQL> explain plan for select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner
- from t1,t2
- where t1.object_name like '%T1%'
- and t1.object_id=t2.object_id;
- 已解释。
- SQL> @showplan
- PLAN_TABLE_OUTPUT
- --------------------------------------------------------------------------------------
- Plan hash value: 3787413387
- --------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 2498 | 99920 | 5061 (1)| 00:01:01 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
- | 2 | NESTED LOOPS | | 2498 | 99920 | 5061 (1)| 00:01:01 |
- |* 3 | TABLE ACCESS FULL | T1 | 2498 | 72442 | 59 (6)| 00:00:01 |
- |* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
- --------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
- 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
从执行计划可以看到Oracle优化器评估的成本为5061,远远高于原来的219。
但是实际的逻辑读是多少呢?
- 统计信息
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 290 consistent gets
- 0 physical reads
- 0 redo size
- 1352 bytes sent via SQL*Net to client
- 385 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 29 rows processed
加了HINT之后实际的逻辑读只有290,低于原始SQL的932。所以这里可以看出来,由于Oracle优化器过高地估计了T1表经过like操作过滤返回的行数,也就过高地估计了nest loop的成本,最终也就选择了不是最优的执行计划。
下面我们用Oracle的SQL Tuning Advisor来尝试这条SQL:
- SQL> var tuning_task varchar2(100);
- SQL> DECLARE
- 2 l_sql_id v$session.prev_sql_id%TYPE;
- 3 l_tuning_task VARCHAR2(30);
- 4 BEGIN
- 5 l_sql_id:='4zbqykx89yc8v';
- 6 l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
- 7 :tuning_task:=l_tuning_task;
- 8 dbms_sqltune.execute_tuning_task(l_tuning_task);
- 9 dbms_output.put_line(l_tuning_task);
- 10 END;
- 11 /
- 任务_74
- PL/SQL 过程已成功完成。
- SQL> print tuning_task;
- TUNING_TASK
- ---------------------------------------------------------------------------------------------------------
- 任务_74
- SQL> SELECT dbms_sqltune.report_tuning_task(:tuning_task) FROM dual;
- DBMS_SQLTUNE.REPORT_TUNING_TASK(:TUNING_TASK)
- --------------------------------------------------------------------------------
- GENERAL INFORMATION SECTION
- -------------------------------------------------------------------------------
- Tuning Task Name : 任务_74
- Tuning Task Owner : TEST1
- Scope : COMPREHENSIVE
- Time Limit(seconds) : 1800
- Completion Status : COMPLETED
- Started at : 12/15/2010 09:56:02
- Completed at : 12/15/2010 09:56:03
- Number of SQL Profile Findings : 1
- -------------------------------------------------------------------------------
- Schema Name: TEST1
- SQL ID : 4zbqykx89yc8v
- SQL Text : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%'
- and t1.object_id=t2.object_id
- -------------------------------------------------------------------------------
- FINDINGS SECTION (1 finding)
- -------------------------------------------------------------------------------
- 1- SQL Profile Finding (see explain plans section below)
- --------------------------------------------------------
- 为此语句找到了性能
- Recommendation (estimated benefit: 46.62%)
- ------------------------------------------
- -考虑接受推荐的 SQL
- executedbms_sqltune.accept_sql_profile(task_name => '任务_74', replace =
- TRUE);
- -------------------------------------------------------------------------------
- EXPLAIN PLANS SECTION
- -------------------------------------------------------------------------------
- 1- Original With Adjusted Cost
- ------------------------------
- Plan hash value: 1838229974
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 29 | 1160 | 219 (4)| 00:00:03 |
- |* 1 | HASH JOIN | | 29 | 1160 | 219 (4)| 00:00:03 |
- |* 2 | TABLE ACCESS FULL| T1 | 29 | 841 | 59 (6)| 00:00:01 |
- | 3 | TABLE ACCESS FULL| T2 | 49954 | 536K| 159 (2)| 00:00:02 |
- ---------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
- 2 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
- 2- Using SQL Profile
- --------------------
- Plan hash value: 3787413387
- --------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 29 | 1160 | 117 (3)| 00:00:02 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
- | 2 | NESTED LOOPS | | 29 | 1160 | 117 (3)| 00:00:02
- |
- |* 3 | TABLE ACCESS FULL | T1 | 29 | 841 | 59 (6)| 00:00:01 |
- |* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
- --------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
- 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
- -------------------------------------------------------------------------------
上面代码中的sql_id是从v$sql来,对应的是没有加hint的SQL。
结果看起来非常棒,SQL Tuning Advisor为我们找到了理想的执行计划,T1表上经过谓词过滤后返回的行数评估为29,相当地精确。我们要做的就是Accept SQL Profile,接受这个SQL Profile。
- SQL> execute dbms_sqltune.accept_sql_profile(task_name => :tuning_task,replace => TRUE,force_match=>true);
- PL/SQL 过程已成功完成。
那么我们再执行其他的类似SQL看看:
- SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T2%' and t1.object_id=t2.object_id;
- 已选择77行。
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 3787413387
- --------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 29 | 1160 | 117 (3)| 00:00:02 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
- | 2 | NESTED LOOPS | | 29 | 1160 | 117 (3)| 00:00:02 |
- |* 3 | TABLE ACCESS FULL | T1 | 29 | 841 | 59 (6)| 00:00:01 |
- |* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
- --------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - filter("T1"."OBJECT_NAME" LIKE '%T2%')
- 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
- Note
- -----
- - SQL profile "SYS_SQLPROF_014b39f084c88000" used for this statement
- 统计信息
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 343 consistent gets
- 0 physical reads
- 0 redo size
- 2840 bytes sent via SQL*Net to client
- 385 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 77 rows processed
这一次,尽管我们更改了LIKE 后面的值,但是执行计划与SQL Tuning Advisor产生的执行计划完全一样。从执行计划的”Note“一节也可以看到,SQL Profile起作用了。SQL Profile的名字为”SYS_SQLPROF_014b39f084c88000″。
- SQL> select name,category,signature,type,status,force_matching from dba_sql_profiles;
- NAME CATEGORY SIGNATURE TYPE STATUS FOR
- ------------------------------ ------------------------------ --------------------- --------- ---------- ---
- SYS_SQLPROF_014b39f084c88000 DEFAULT 3960696072677096522 MANUAL ENABLED YES
一些复杂的SQL,我经常会先通过SQL Tuning Advisor来分析一下,看能不能让Oracle自已找出一个更好的执行计划。
我们来看看,SQL Profiles实际上是些什么:
- SQL< select * from sys.sqlprof$attr;
- SIGNATURE CATEGORY ATTR# ATTR_VAL
- --------------------- ------------------------------ ---------- ----------------------------------------
- 3960696072677096522 DEFAULT 1 OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$
- 1", SCALE_ROWS=0.01161091426)
从sys.sqlprof$attr这个数字字典里面,我们可以看到两样东西:signature和attr。
signature是什么?可以理解为与sql_id、sql_hash_value类似的值,用来标识SQL。在10g以上的版本中,查看v$sql的定义就可以发现2列:exact_matching_signature、force_matching_signature。通过下面的数据可以看出区别:
- SQL> select rownum,a.* from
- (select exact_matching_signature,force_matching_signature,plan_hash_value,sql_text
- from v$sql where sql_text like '%/*%xjs%' and sql_text not like '%v$sql%' order by 1) a;
- ROWNUM EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE SQL_TEXT
- ---------- ------------------------ ------------------------ --------------- --------------------------------------------------
- 1 3939730931515200254 17443893418101517951 3617692013 select /* xjs */ object_name from T1 where obje
- ct_name='t1'
- 2 10964210455693560558 11097449316038436385 3836375644 select /* xjs */ object_name from T1 where rown
- um<=3
- 3 10964210455693560558 11097449316038436385 3836375644 select /* xjs */ object_name from T1 where ro
- wnum<=3
- 4 11217690300719901571 354482119692997204 3836375644 select /* xjs */ 2 from t1 where rownum<=1
- 5 11974975582747367847 354482119692997204 3836375644 select /* xjs */ 1 from t1 where rownum<=1
- 6 12941882703651921406 17443893418101517951 3617692013 select /* xjs */ object_name from T1 where obje
- ct_name='T1'
- 7 17986178357953662359 11097449316038436385 3836375644 select /* xjs */ object_name from T1 where rown
- um<=1
- 8 17986178357953662359 11097449316038436385 3836375644 select /* xjs */ OBJECT_NAME from T1 where rownum<
- =1
- 9 17986178357953662359 11097449316038436385 3836375644 SELECT /* xjs */ object_name from T1 where rown
- um<=1
- 10 17986178357953662359 11097449316038436385 3836375644 select /* xjs */ object_name from t1 where rownum<
- =1
从上面的数据可以看出:
- 第2、3条SQL的exact_matching_signature相同,第7、8、9、10条SQL的exact_matching_signature相同。
- 第2、3条SQL的force_matching_signature相同,第4、5条SQL的force_matching_signature相同,第7、8、9、10条的SQL的force_matching_signature相同。第1、6条SQL的force_matching_signature相同
有如下的结论:对SQL语句,去掉重复的空格(不包括字符常量),将大小写转换成相同,比如均为大写(不包括字符常量)后,如果SQL相同,那么SQL语句的exact_matching_signature就是相同的。对SQL语句,去掉重复的空格(不包括字符常量),将大小写转换成相同,比如均为大写(不包括字符常量),然后去掉SQL中的常量,如果SQL相同,那么SQL语句的force_matching_signature就是相同的。但是例外的情况是:如果SQL中有绑定变量,force_matching_signature就会与exact_matching_signature一样的生成标准。
- SQL> select rownum,a.* from
- (select exact_matching_signature,force_matching_signature,plan_hash_value,sql_text
- from v$sql where sql_text like '%/*%xjs2%' and sql_text not like '%v$sql%' order by 1) a;
- ROWNUM EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE PLAN_HASH_VALUE SQL_TEXT
- ---------- ------------------------ ------------------------ --------------- --------------------------------------------------
- 1 5363536431331905229 5363536431331905229 3836375644 select /* xjs2 */ object_name from T1 where obj
- ect_name='T1' and rownum<=:rn
- 2 5363536431331905229 5363536431331905229 3836375644 select /* xjs2 */ object_name from t1 where obj
- ect_name='T1' and rownum<=:rn
- 3 12992689086515482106 12992689086515482106 3836375644 select /* xjs2 */ object_name from t1 where obj
- ect_name='T2' and rownum<=:rn
可以看到,现在exact_matching_signature与force_matching_signature完全一样了。
从force_matching_signature的特性,我们可以想到一个用途,用于查找没有使用绑定变量的SQL语句,类似于使用plan_hash_value来查找。
回到前面,accept_sql_profile这个过程,force_match参数设为TRUE,那么dba_sql_profiles中的signature则是由SQL的force_matching_signature而来,否则便是exact_matching_signature。对于Outlines来说,则只能是exact_matching_signature。从这个角度上讲,Sql Profiles比Outlines的使用范围更广,因为Sql profiles对没有使用绑定变量的SQL也支持得很好。值得注意的是,Sql profiles的force_match属性是不能更改的,只能在创建时指定,如果要更改,则只能重新创建改Sql Profile。
下面来看看sys.sqlprof$attr数据字典。这里面没有SQL Profile的名字,而是用的sql的signature。大家从attr_val的结果发现了什么?
- OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.01161091426)
可以看到,SQL Profiles的attr_val实际上就是一些Hints,这跟Outlines没有本质上的区别。只是SQL Profiles中的Hint,没有指定SQL使用哪个索引,也没有指定表的连接方法和连接顺序。这里只指定了T1表评估返回的行数,与原始的评估返回的行数的放大缩小的倍数。2498*0.01161091426正好为29。这里就是告诉Oracle优化器,T1表经过谓语过滤后返回行数应该为评估的0.01161091426倍。从这里可以看出,SQL Profiles并不会锁定SQL的执行计划,只是提供了更多、更准确的统计信息给优化器。看下面的测试:
- SQL> exec dbms_stats.set_table_stats('TEST1','T1',numrows=>5000000);
- PL/SQL 过程已成功完成。
- SQL> explain plan for select t1.*,t2.owner
- 2 from t1,t2
- 3 where t1.object_name like '%T1%'
- 4 and t1.object_id=t2.object_id;
- 已解释。
- SQL> @showplan
- PLAN_TABLE_OUTPUT
- ----------------------------------------------------------------------------------
- Plan hash value: 1838229974
- ---------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 2903 | 113K| 448 (53)| 00:00:06 |
- |* 1 | HASH JOIN | | 2903 | 113K| 448 (53)| 00:00:06 |
- |* 2 | TABLE ACCESS FULL| T1 | 2903 | 84187 | 288 (81)| 00:00:04 |
- | 3 | TABLE ACCESS FULL| T2 | 49954 | 536K| 159 (2)| 00:00:02 |
- ---------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
- 2 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
- Note
- -----
- - SQL profile "SYS_SQLPROF_014b39f084c88000" used for this statement
将T1表的统计信息中的表行数改为500万,Oracle就会评估为返回5000000*5%*0.01161091426=2903行。这里执行计划又变回为full scan+hash join。可以看到,虽然SQL Profile起作用了,但是并没有锁定执行计划。
小结:本文简单介绍了什么是SQL Profiles及其作用,如何使用SQL Tuning Advisor来生成SQL Profile,以及生成的SQL Profile产生的Hint。同时也介绍了SQL的signature。
下一篇将会介绍如何手工来为创建、生成SQL Profile,以及如何让SQL Profile也能像Outlines一样锁定SQL的执行计划,以保持SQL执行计划的稳定性。