SQL Profiles-Part I
http://www.laoxiong.net/sql-profiles-part.html
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执行计划的稳定性。
SQL Profiles-PartII
http://www.laoxiong.net/sql-profiles-partii.html
在上一篇《SQL Profiles-Part I》,我向大家介绍了什么是SQL Profiles及其作用,如何使用SQL Tuning Advisor来生成SQL Profile,以及生成的SQL Profile产生的Hint。同时也介绍了SQL的signature。那么在今天,将向大家介绍如何手工创建SQL Profiles(即不通过SQL Tuning Advisor)来达成2个目的:
锁定或者说稳定SQL执行计划。
在不能修改应用的SQL的情况下,来改变或者说是强制使SQL使用我们指定的执行计划,即使原始的SQL包含了Hints。
那么,这里最关键的一点是,如何来手工创建SQL Profiles?
答案是,正如上一篇中有朋友的留言,使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程。
SQL> desc dbms_sqltune ... PROCEDURE IMPORT_SQL_PROFILE 参数名称 类型 输入/输出默认值? ------------------------------ ----------------------- ------ -------- SQL_TEXT CLOB IN PROFILE SQLPROF_ATTR IN NAME VARCHAR2 IN DEFAULT DESCRIPTION VARCHAR2 IN DEFAULT CATEGORY VARCHAR2 IN DEFAULT VALIDATE BOOLEAN IN DEFAULT REPLACE BOOLEAN IN DEFAULT FORCE_MATCH BOOLEAN IN DEFAULT ...
这个过程其名字与实际功能有所差异,其实可以理解为CREATE OR REPLACE SQL_PROFILE。过程中的PROFILE参数为SYS.SQLPROF_ATTR,这种类型其实就是VARCHAR2的集合类型(COLLECTION):
SQL> select text from dba_source where name='SQLPROF_ATTR' and owner='SYS'; TYPE sqlprof_attr AS VARRAY(2000) of VARCHAR2(500)
下面我们就用这个过程来创建SQL PROFILE:
为避免干扰,将上一篇测试中生成的SQL Profile删除掉,同时恢复T1表的统计信息中的表行数:
SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_014b39f084c88000'); PL/SQL 过程已成功完成。 SQL> exec dbms_stats.set_table_stats('TEST1','T1',numrows=>49953); PL/SQL 过程已成功完成。
现在我们手工创建一个SQL Profile:
SQL> declare 2 v_hints sys.sqlprof_attr; 3 begin 4 v_hints:=sys.sqlprof_attr('USE_NL(T1 T2)','INDEX(T2)'); 5 dbms_sqltune.import_sql_profile('select t1.*,t2.owner from t1,t2 where t1.object_name like ''%T1%'' and t1.object_id=t2.object_id', 6 v_hints,'SQLPROFILE_NAME1',force_match=>true); 7 end; 8 / PL/SQL 过程已成功完成。 SQL> select attr_val from dba_sql_profiles a, sys.sqlprof$attr b 2 where a.signature = b.signature 3 and a.name='SQLPROFILE_NAME1'; ATTR_VAL ---------------------------------------- USE_NL(T1 T2) INDEX(T2)
下面执行SQL Profiles对应的SQL:
SQL> select t1.*,t2.owner 2 from t1,t2 3 where t1.object_name like '%T1%' 4 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%') Note ----- - SQL profile "SQLPROFILE_NAME1" used for this statement 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 933 consistent gets
可以看到,SQL使用了SQL Profile,不过没有达到我们预期的效果。
看起来是SQL Profile使用的Hints有问题。我们重新设置SQL Profile的Hints,在Hints中加上“Query Block Name”。这一次在执行IMPORT_SQL_PROFILE过程时,将REPLACE参数设置为TRUE,以替换现有的SQL Profile:
SQL> declare 2 v_hints sys.sqlprof_attr; 3 begin 4 v_hints:=sys.sqlprof_attr('USE_NL(T1@SEL$1 T2@SEL$1)','INDEX(T2@SEL$1)'); 5 dbms_sqltune.import_sql_profile('select t1.*,t2.owner from t1,t2 where t1.object_name like ''%T1%'' and t1.object_id=t2.object_id', 6 v_hints,'SQLPROFILE_NAME1',force_match=>true,replace=>true); 7 end; 8 / PL/SQL 过程已成功完成。 SQL> select attr_val from dba_sql_profiles a, sys.sqlprof$attr b 2 where a.signature = b.signature 3 and a.name='SQLPROFILE_NAME1'; ATTR_VAL ---------------------------------------- USE_NL(T1@SEL$1 T2@SEL$1) INDEX(T2@SEL$1)
再次执行下面的SQL:
SQL> select t1.*,t2.owner 2 from t1,t2 3 where t1.object_name like '%T1%' 4 and t1.object_id=t2.object_id; 已选择29行。 执行计划 ---------------------------------------------------------- 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") Note ----- - SQL profile "SQLPROFILE_NAME1" used for this statement 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 294 consistent gets
这一次达到了预期的效果。看起来在SQL Profiles中对Hints还有一定的要求。
那么我们再一次手工修改T1表的统计信息,看看结果如何:
SQL> exec dbms_stats.set_table_stats('TEST1','T1',numrows=>5000000); PL/SQL 过程已成功完成。 SQL> select t1.*,t2.owner 2 from t1,t2 3 where t1.object_name like '%T1%' 4 and t1.object_id=t2.object_id; 已选择29行。 执行计划 ---------------------------------------------------------- Plan hash value: 3787413387 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 250K| 9765K| 500K (1)| 01:40:12 | | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 250K| 9765K| 500K (1)| 01:40:12 | |* 3 | TABLE ACCESS FULL | T1 | 250K| 7080K| 288 (81)| 00:00:04 | |* 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") Note ----- - SQL profile "SQLPROFILE_NAME1" used for this statement 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 294 consistent gets
可以看到,Oracle优化器评估表T1经过Like条件过滤后返回的行数虽然很大,但是这里的执行计划仍然与未修改统计信息之前一样,使用range scan+ nested loop join。
通过以上的测试,我们明白了DBMS_SQLTUNE.IMPORT_SQL_PROFILE的使用,同时也验证了这种方式的有效性,SQL Profiles能够像Outlines一样,能够稳定SQL的执行计划。
接下来我们需要完成两个任务。
任务一:对现有的SQL稳定其执行计划。
这里的问题是:稳定一条SQL语句的Hints从哪里来?简单的sql,没问题,我们可以手工构造,但是复杂的SQL,手工构造相对比较复杂,同时手工构造的Hints不一定能够保证SQL的执行计划就会稳定。从10g开始,v$sql_plan中就包括了SQL语句OUTLINE数据,也就是稳定执行计划的Hints。从下面可以看到:
SQL> select t1.*,t2.owner 2 from t1,t2 3 where t1.object_name like '%T1%' 4 and t1.object_id=t2.object_id; 已选择29行。 SQL> select * from table(dbms_xplan.display_cursor(null,null,'outline')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------- SQL_ID 6m45w7r0xgdfj, child number 0 ------------------------------------- select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id Plan hash value: 3787413387 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 5061 (100)| | | 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 | -------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "T1"@"SEL$1") INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID")) LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1") USE_NL(@"SEL$1" "T2"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("T1"."OBJECT_NAME" LIKE '%T1%') 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") Note ----- - SQL profile "SQLPROFILE_NAME1" used for this statement
上面所显示的“Outline Data”即是我们稳定SQL执行计划需要的Hints(我们甚至可以将这些Hints直接写到我们的SQL中)。对需要稳定执行计划的SQL,我们所要做的就是如前面所示,将Hints与SQL文本一起创建一个SQL Profile。这里不得不提到一个SQL脚本,来自MOS。”SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly [ID 215187.1]“,在这篇文章中,可以下载到sqlt.zip,这个压缩文件内有一个文件:coe_xfr_sql_profile.sql。这个脚本可以用于从shared pool、awr中提取指定的SQL ID的Outline Data并创建SQL Profile。下面是示例:
SQL> select /*+ proftest1 */ t1.*,t2.owner 2 from t1,t2 3 where t1.object_name like '%T1%' 4 and t1.object_id=t2.object_id; --在shared pool中查找刚刚执行的SQL,其ID为b4zvp712np1bp-- SQL> @coe_xfr_sql_profile.sql Parameter 1: SQL_ID (required) 输入 1 的值: b4zvp712np1bp PLAN_HASH_VALUE AVG_ET_SECS --------------- ----------- 2959412835 .112 Parameter 2: PLAN_HASH_VALUE (required) 输入 2 的值: 2959412835 Values passed: ~~~~~~~~~~~~~ SQL_ID : "b4zvp712np1bp" PLAN_HASH_VALUE: "2959412835" Execute coe_xfr_sql_profile_b4zvp712np1bp_2959412835.sql on TARGET system in order to create a custom SQL Profile with plan 2959412835 linked to adjusted sql_text. COE_XFR_SQL_PROFILE completed. SQL>@coe_xfr_sql_profile_b4zvp712np1bp_2959412835.sql PL/SQL 过程已成功完成。 SQL>WHENEVER SQLERROR CONTINUE SQL>SET ECHO OFF; SIGNATURE --------------------- 6058051510930011685 ... manual custom SQL Profile has been created COE_XFR_SQL_PROFILE_b4zvp712np1bp_2959412835 completed SQL>select attr_val from sys.sqlprof$attr where signature=6058051510930011685; ATTR_VAL -------------------------------------------------- BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "T2"@"SEL$1") FULL(@"SEL$1" "T1"@"SEL$1") LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1") USE_HASH(@"SEL$1" "T1"@"SEL$1") END_OUTLINE_DATA
coe_xfr_sql_profile.sql这个脚本首先要求输入sql id,然后从shared pool、awr中获取sql执行的各个执行计划的统计信息(执行计划不稳定的SQL通常会有多个不同的执行计划),然后输入你认为是正确的、需要稳定的执行计划的hash value,脚本就会生成另一个脚本,这里为coe_xfr_sql_profile_b4zvp712np1bp_2959412835.sql,然后运行这个脚本,就会创建出稳定执行计划所需要的SQL Profile,SQL Profile的名字为:coe+sql_id+plan_hash_value,这里为coe_b4zvp712np1bp_2959412835。注意,这里创建的SQL Profile,force match默认为FALSE,我们可以手工修改脚本将其改为TRUE,同时我们也可以按意愿来修改生成的脚本的其他内容。
除了上面提到的脚本,http://kerryosborne.oracle-guy.com这个BLOG里面也有许多与SQL Profiles相关的脚本。其中create_sql_profile.sql可完成类似的功能,只不过功能相对简单,只能从shared pool中生成SQL Profile,因此也更方便。
任务二:在不能修改SQL的情况下改变并固定SQL的执行计划,即使原始的SQL使用了Hints。
常常遇到这样的情况,SQL语句其执行计划有问题,或者是SQL使用了错误的Hints(比如 /*+ RULE */)导致SQL性能较差,但是应用又不能修改或者时间内不能修改,那么我们怎么来改变SQL的执行计划呢。有3种办法,一种是调整统计信息,这个不建议使用,因为比较复杂、不稳定可靠(统计信息可能会重新收集),影响面广(会影响其他访问此对象的SQL)。第二种是使用OUTLINE,这种方法比较复杂。第三种就是我们今天要介绍的使用SQL Profiles了。
使用SQL Profiles来改变SQL的执行计划,其本质上就是使用Hints来改变SQL的执行计划。对于简单的SQL,我们同样可以像前面一样手工构造Hints然后再使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE来实现。但是这种方法还是略显烦琐。那么通常的方法就是”乾坤大挪移“了:
取得原始SQL的文本(如有可能还包括sql id)
构造一个与原始SQL在逻辑上、结构上完全相同的SQL。这里强制逻辑上和结构上相同,SQL解析的用户名、SQL中引用对象的用户名甚至是一些predicate条件都可以不同。当然能够与原始SQL完全一样就更省事。
执行我们构造的SQL,并取得构造的SQL的Outline Data。
使用原始SQL的文本和构造的SQL的Outline Data创建SQL Profile。
下面我们来演示一下整个过程。我们这里要修改执行计划的SQL是:
select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
我们首先需要执行这一条SQL,然后取得的SQL ID为gmvb9bp7f9kqd:
SQL> select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner 2 from t1,t2 3 where t1.object_name like '%T1%' 4 and t1.object_id=t2.object_id; 已选择29行。 执行计划 ---------------------------------------------------------- Plan hash value: 2959412835 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 250K| 9765K| | 998 (25)| 00:00:12 | |* 1 | HASH JOIN | | 250K| 9765K| 1128K| 998 (25)| 00:00:12 | | 2 | TABLE ACCESS FULL| T2 | 49954 | 536K| | 159 (2)| 00:00:02 | |* 3 | TABLE ACCESS FULL| T1 | 250K| 7080K| | 288 (81)| 00:00:04 | -----------------------------------------------------------------------------------
然后我们构造一条SQL,让这条SQL按我们希望的执行计划运行,构造的SQL其ID为cymak300cycmd:
SQL> select /*+ modify_sql index(t1) use_nl(t1 t2) */ t1.*,t2.owner 2 from t1,t2 3 where t1.object_name like '%T1%' 4 and t1.object_id=t2.object_id; 已选择29行。 执行计划 ---------------------------------------------------------- Plan hash value: 3787413387 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 250K| 9765K| 500K (1)| 01:40:12 | | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 250K| 9765K| 500K (1)| 01:40:12 | |* 3 | TABLE ACCESS FULL | T1 | 250K| 7080K| 288 (81)| 00:00:04 | |* 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")
然后使用coe_xfr_sql_profile.sql脚本来提取我们构造的SQL的Outline Data,生成的结果为coe_xfr_sql_profile_cymak300cycmd_3787413387.sql,打开结果文件,可以看到有这么一段:
h := SYS.SQLPROF_ATTR( q'[BEGIN_OUTLINE_DATA]', q'[IGNORE_OPTIM_EMBEDDED_HINTS]', q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.1')]', q'[ALL_ROWS]', q'[OUTLINE_LEAF(@"SEL$1")]', q'[FULL(@"SEL$1" "T1"@"SEL$1")]', q'[INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))]', q'[LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")]', q'[USE_NL(@"SEL$1" "T2"@"SEL$1")]', q'[END_OUTLINE_DATA]');
再针对gmvb9bp7f9kqd使用coe_xfr_sql_profile.sql,生成的结果文件为coe_xfr_sql_profile_gmvb9bp7f9kqd_2959412835.sql。手工修改这个文件,将里面h := SYS.SQLPROF_ATTR…那一段替换成我们之前得到的那一段。这一次我们将这个文件中的force_match从FALSE改成TRUE。
最后我们运行coe_xfr_sql_profile_gmvb9bp7f9kqd_2959412835.sql这个脚本文件:
SQL>@coe_xfr_sql_profile_gmvb9bp7f9kqd_2959412835.sql PL/SQL 过程已成功完成。 SQL>WHENEVER SQLERROR CONTINUE SQL>SET ECHO OFF; SIGNATURE --------------------- 15409905709853673912 ... manual custom SQL Profile has been created COE_XFR_SQL_PROFILE_gmvb9bp7f9kqd_2959412835 completed
这样就完成了我们所需要的SQL Profile的创建。下面再看看原来的SQL执行情况(这里我故意将like条件改了一下,以查看force match是否起作用):
SQL>select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner 2 from t1,t2 3 where t1.object_name like '%T2%' 4 and t1.object_id=t2.object_id; 已选择77行。 执行计划 ---------------------------------------------------------- Plan hash value: 3787413387 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 250K| 9765K| 500K (1)| 01:40:12 | | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 250K| 9765K| 500K (1)| 01:40:12 | |* 3 | TABLE ACCESS FULL | T1 | 250K| 7080K| 288 (81)| 00:00:04 | |* 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 "coe_gmvb9bp7f9kqd_2959412835" used for this statement 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 363 consistent gets
可以看到SQL Profile起作用了。
最后一步,生成SQL Profile时稍显复杂,不过我们可以修改之前提到的create_sql_profile.sql文件来达到同样的目的,只不过前几个步骤仍然是不可省略的。将里面的代码:
select extractvalue(value(d), '/hint') as outline_hints bulk collect into ar_profile_hints from xmltable('/*/outline_data/hint' passing (select xmltype(other_xml) as xmlval from v$sql_plan where sql_id = '&&sql_id' and child_number = &&child_no and other_xml is not null)) d;
改为
select extractvalue(value(d), '/hint') as outline_hints bulk collect into ar_profile_hints from xmltable('/*/outline_data/hint' passing (select xmltype(other_xml) as xmlval from v$sql_plan where sql_id = '&&modi_sql_id' and child_number = &&modi_child_no and other_xml is not null)) d;
注意这里modi_sql_id和modi_child_no为我们构造的SQL执行后的id及child_number。同时这2个变量在文件前面需要定义,此处不再细述。
小结:本文承接上一篇,介绍了如何利用SQL Profile来稳定执行计划;如何利用SQL Profile来改变SQL的执行计划。对于SQL Profiles来说,不属于任何一个用户,比Outlines更具有操控性灵活性。对于SQL Profiles的category,这里不做介绍,有兴趣的朋友请参考文档。
SQL Profiles使用实例
Reference:
http://www.muzijiang.cn/index.php/2010/07/sql_profiles_usage_sample/
在统计信息不完整或者缺失时,有时优化器会产生错误的判断,产生性能比较低下的执行计划。传统的方式,通过手工添加相关的提示生成正确的执行计划。但对于那些封装好的应用程序,修改代码不是一件容易的事情,相信这个问题也曾经困扰过很多的DBA。在Oracle10g中可以很方便的使用自动SQL调整(Automatic SQL Tuning)来调整这些SQL语句,而不用去修改应用代码。其中使用的关键技术就是强大的SQL Profiles。
下面通过一个小例子,看看SQL Profiles是如何进行SQL语句自动调整的。
1. 使用HR用户登录,创建测试用例
SQL> create table test (n number );
Table created.
SQL> begin
for i in 1 .. 10000 loop
insert into test values(i);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
2. 创建索引并进行分析
SQL> create index test_idx on test(n);
Index created.
SQL> analyze table test estimate statistics;
Table analyzed.
3. 执行SQL语句,模拟一个性能低下的执行计划
SQL> select /*+ no_index(test test_idx) */ * from hr.test where n=1;
Execution Plan
———————————————————-
Plan hash value: 1357081020
————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 3 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 3 | 6 (0)| 00:00:01 |
————————————————————————–
4. 使用SYS用户登录,创建自动调整任务
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := ‘select /*+ no_index(test test_idx) */ * from hr.test where n=1′;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => ‘SCOTT’,
scope => ‘COMPREHENSIVE’,
time_limit => 60,
task_name => ‘tuning_task_1′,
description => ‘Task to tune a query on a specified table’);
END;
/
PL/SQL procedure successfully completed.
5. 执行自动调整任务并输出结果
SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘tuning_task_1′);
PL/SQL procedure successfully completed.
SQL> SET LONG 1000
SQL> SET LONGCHUNKSIZE 1000
SQL> SET LINESIZE 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘tuning_task_1′) from DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TUNING_TASK_1′)
——————————————————————————-
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : tuning_task_1
Tuning Task Owner : SYS
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 07/02/2010 15:48:07
Completed at : 07/02/2010 15:48:08
Number of SQL Profile Findings : 1
——————————————————————————-
Schema Name: SCOTT
SQL ID : 8m2dnmd4a7vx0
SQL Text : select /*+ no_index(test test_idx) */ * from hr.test where n=1
——————————————————————————-
FINDINGS SECTION (1 finding)
——————————————————————————-
1- SQL Profile Finding (see explain plans section below)
——————————————————–
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 84.11%)
——————————————
– Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => ‘tuning_task_1′,
replace => TRUE);
——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-
1- Original With Adjusted Cost
——————————
Plan hash value: 1357081020
————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 3 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 3 | 6 (0)| 00:00:01 |
————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter(“N”=1)
2- Using SQL Profile
——————–
Plan hash value: 2882402178
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 3 | 1 (0)| 00:00:01 |
—————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – access(“N”=1)
——————————————————————————-
从上面的输出可以看出,自动调整任务发现了一个更好的执行计划,并推荐使用这个计划。
6. 接受执行计划
DECLARE
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => ‘tuning_task_1′,
name => ‘my_sql_profile’);
END;
/
PL/SQL procedure successfully completed.
SQL> select to_char(sql_text) from dba_sql_profiles;
TO_CHAR(SQL_TEXT)
————————————————————————
select /*+ no_index(test test_idx) */ * from hr.test where n=1
7. 再次执行相同的语句,验证结果
SQL> select /*+ no_index(test test_idx) */ * from hr.test where n=1;
Execution Plan
———————————————————-
Plan hash value: 2882402178
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 3 | 1 (0)| 00:00:01 |
—————————————————————————–
这里可以看出,Oracle已经使用了不同的执行计划。
dbms_xplan之display_cursor函数的使用
http://blog.csdn.net/robinson_0612/article/details/6866925
分类: SQL/PLSQL 优化 2011-10-12 16:34 1246人阅读 评论(0) 收藏 举报
DBMS_XPLAN包中display_cursor函数不同于display函数,display_cursor用于显示SQL语句的真实的执行计划,在大多数情况下,显示真实
的执行计划有助于更好的分析SQL语句的全过程,尤其是运行此SQL语句实时的I/O开销。通过对比预估的I/O与真实的I/O开销来判断SQL语句所存
在问题,如缺少统计信息,SQL语句执行的次数,根据实际中间结果集的大小来选择合适的连接方式等。本文仅仅讲述display_cursor函数的使
用。
有关执行计划中各字段模块的描述请参考: 执行计划中各字段各模块描述
有关由SQL语句来获取执行计划请参考: 使用 EXPLAIN PLAN 获取SQL语句执行计划
有关使用autotrace来获取执行计划请参考: 启用 AUTOTRACE 功能
有关dbms_xplan之display函数请参考: dbms_xplan之display函数的使用
一、display_cursor函数用法
1、display_cursor函数语法
[sql] view plaincopyprint?
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');
2、display_cursor函数参数描述
sql_id
指定位于库缓存执行计划中SQL语句的父游标。默认值为null。当使用默认值时当前会话的最后一条SQL语句的执行计划将被返回
可以通过查询V$SQL 或 V$SQLAREA的SQL_ID列来获得SQL语句的SQL_ID。
child_number
指定父游标下子游标的序号。即指定被返回执行计划的SQL语句的子游标。默认值为0。如果为null,则sql_id所指父游标下所有子游标
的执行计划都将被返回。
format
控制SQL语句执行计划的输出部分,即哪些可以显示哪些不显示。使用与display函数的format参数与修饰符在这里同样适用。
除此之外当在开启statistics_level=all时或使用gather_plan_statistics提示可以获得执行计划中实时的统计信息
有关详细的format格式描述请参考:dbms_xplan之display函数的使用 中format参数的描述
下面给出启用统计信息时format新增的修饰符
iostats 控制I/O统计的显示
last 默认,显示所有执行计算过的统计。如果指定该值,则只显示最后一次执行的统计信息
memstats 控制pga相关统计的显示
allstats 此为iostats memstats的快捷方式,即allstats包含了iostats和memstats
run_stats_last 等同于iostats last。只能用于oracle 10g R1
run_stats_tot 等同于iostats。只能用于oracle 10g R1
二、演示使用display_cursor函数获取执行计划
1、当前数据库版本以及加载执行计划到库缓存
[sql] view plaincopyprint?
SQL> select * from v$version where rownum<2;
BANNER
------------——
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
SQL> SELECT ename,dname,loc
2 FROM emp e, dept d
3 WHERE e.deptno = d.deptno
4 AND e.empno = 7788;
ENAME DNAME LOC
-- --— ---
SCOTT RESEARCH DALLAS
2、查看真实的执行计划
[sql] view plaincopyprint?
/*----不传递任何参数给display_cursor函数,显示当前会话最后一条SQL语句的执行计划--—-*/
//
/* Author: Robinson Cheng */
/* Blog: http://blog.csdn.net/robinson_0612 */
/* MSN: moc.liamtoh|2160_nosnibor#moc.liamtoh|2160_nosnibor */
/* QQ: 645746311 */
//
SQL> select * from table(dbms_xplan.display_cursor(null,null));
PLAN_TABLE_OUTPUT
------------------
SQL_ID a67wqmkfb9j65, child number 0
-------—
SELECT ename,dname,loc FROM emp e, dept d WHERE e.deptno = d.deptno AND
e.empno = 7788
Plan hash value: 2385808155
------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | NESTED LOOPS | | 1 | 63 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 33 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 409 | 12270 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
-----------------—-
Predicate Information (identified by operation id):
-----------
3 - access("E"."EMPNO"=7788)
5 - access("E"."DEPTNO"="D"."DEPTNO")
/*---— 获得SQL语句的SQL_ID,可以看出此SQL_ID与上面显示的执行计划中的SQL_ID一致 --—*/
SQL> select sql_id,address,plan_hash_value,hash_value,child_number from v$sql
2 where sql_text like '%SELECT ename%' and sql_text not like '%from v$sql%';
SQL_ID ADDRESS PLAN_HASH_VALUE HASH_VALUE CHILD_NUMBER
--- ---- ---— -- --—
a67wqmkfb9j65 0000000091DBFBC8 2385808155 2629092549 0
/*--— 传递SQL_ID以及format参数,并配合修饰符控制执行计划的输出 ------*/
SQL> select * from table(dbms_xplan.display_cursor('a67wqmkfb9j65',null,'typical -predicate -rows'));
PLAN_TABLE_OUTPUT
----------------—
SQL_ID a67wqmkfb9j65, child number 0
-------
SELECT ename,dname,loc FROM emp e, dept d WHERE e.deptno = d.deptno
AND e.empno = 7788
Plan hash value: 2385808155
----------------
| Id | Operation | Name | Bytes | Cost (%CPU)| Time |
----------------
| 0 | SELECT STATEMENT | | | 3 (100)| |
| 1 | NESTED LOOPS | | 63 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 33 | 2 (0)| 00:00:01 |
| 3 | INDEX UNIQUE SCAN | PK_EMP | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 12270 | 1 (0)| 00:00:01 |
| 5 | INDEX UNIQUE SCAN | PK_DEPT | | 0 (0)| |
----------------
3、查看真实执行计划并获得统计信息
前提条件
设置参数statistics_level为all,可以基于session级别以及实例级别
或者启用gather_plan_statistics提示
[sql] view plaincopyprint?
/*---查看实例参数statistics_level的值,并在会话级别将其设定为all ---*/
SQL> show parameter statistics_le
NAME TYPE VALUE
-------- --- ------
statistics_level string ALL
SQL> alter session set statistics_level=all;
Session altered.
SQL> select e.ename,e.sal,s.grade
2 from emp e
3 join salgrade s
4 on e.sal between losal and hisal
5 and e.deptno = 20;
ENAME SAL GRADE
-- -- --
SCOTT 3000 4
FORD 3000 4
JONES 2975 4
ADAMS 1100 1
SMITH 800 1
/*-— 执行上述SQL语句后获得其真实的执行计划,使用了iostats last -predicate -note 修饰符控制显示输出 -*/
SQL> set pagesize 0
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last -predicate -note'));
SQL_ID 243b0tpjxj6wv, child number 0
-------—
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between losal and
hisal and e.deptno = 20
Plan hash value: 4204027666
-------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------
| 1 | MERGE JOIN | | 1 | 1 | 5 |00:00:00.01 | 14 |
| 2 | SORT JOIN | | 1 | 5 | 5 |00:00:00.01 | 7 |
| 3 | TABLE ACCESS FULL | EMP | 1 | 5 | 5 |00:00:00.01 | 7 |
| 4 | FILTER | | 5 | | 5 |00:00:00.01 | 7 |
| 5 | SORT JOIN | | 5 | 5 | 14 |00:00:00.01 | 7 |
| 6 | TABLE ACCESS FULL| SALGRADE | 1 | 5 | 5 |00:00:00.01 | 7 |
-------------------
/*---- 修改会话级别的参数statistics_level为typical并验证修改结果 ----*/
SQL> alter session set statistics_level=typical;
SQL> col name format a40
SQL> col value format a25
SQL> col display_value format a25
SQL> select name, value, display_value, isses_modifiable
2 from v$parameter
3 where isses_modifiable = 'TRUE'
4 and name like '%&input_name%';
Enter value for input_name: statistics_level
old 4: and name like '%&input_name%'
new 4: and name like '%statistics_level%'
NAME VALUE DISPLAY_VALUE ISSES
-------- ----- ----- -
statistics_level TYPICAL TYPICAL TRUE
/*-- 使用提示gather_plan_statistics,并获得其真实执行计划,使用了allstats -rows修饰符控制显示输出 -*/
SQL> set pagesize 180
SQL> SELECT /*+ gather_plan_statistics */ ename,dname,loc
2 FROM emp e, dept d
3 WHERE e.deptno = d.deptno
4 AND d.deptno=20 ORDER BY 1,2,3;
ENAME DNAME LOC
-- --— ---
ADAMS RESEARCH DALLAS
FORD RESEARCH DALLAS
JONES RESEARCH DALLAS
SCOTT RESEARCH DALLAS
SMITH RESEARCH DALLAS
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats -rows'));
PLAN_TABLE_OUTPUT
-------------------—
SQL_ID d2hh42yzqqjz7, child number 0
-------—
SELECT /*+ gather_plan_statistics */ ename,dname,loc FROM emp e, dept d WHERE e.deptno = d.deptno AND
d.deptno=20 ORDER BY 1,2,3
Plan hash value: 3339094711
-----------------------—
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
-----------------------—
| 1 | SORT ORDER BY | | 1 | 5 |00:00:00.01 | 9 | 2048 | 2048 | 1/0/0|
| 2 | NESTED LOOPS | | 1 | 5 |00:00:00.01 | 9 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 |00:00:00.01 | 2 | | | |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 |00:00:00.01 | 1 | | | |
|* 5 | TABLE ACCESS FULL | EMP | 1 | 5 |00:00:00.01 | 7 | | | |
-----------------------—
Predicate Information (identified by operation id):
-----------
4 - access("D"."DEPTNO"=20)
5 - filter("E"."DEPTNO"=20)
Note
-
- dynamic sampling used for this statement
三、总结
1、与display函数不同,display_cursor显示的为真实的执行计划
2、对于format参数,使用与display函数的各个值,同样适用于display_cursor函数
3、当statistics_level为all或使用gather_plan_statistics提示可以获得执行时的统计信息
4、根据真实与预估的统计信息可以初步判断SQL效率低下的原因,如统计信息的准确性、主要的开销位于那些步骤等