点击这里下载 sql profile 脚本 
profile.sql
我们先来看下如何手工创建sql profile来修正执行计划。
创建两张表test1、test2,在表test2的列object_id 上创建索引，分析两张表。
create table test1 as select * from dba_objects;
create table test2 as select * from dba_objects;
create index test2_oi_ind on test2(object_id);
begin
dbms_stats.gather_table_stats(ownname =>'test',
tabname => 'test1',
no_invalidate => FALSE,
estimate_percent => 100,
force => true,
degree => 5,
method_opt => 'for all columns size 1',
cascade => true);
end;
/
begin
dbms_stats.gather_table_stats(ownname =>'test',
tabname => 'test2',
no_invalidate => FALSE,
estimate_percent => 100,
force => true,
degree => 5,
method_opt => 'for all columns size 1',
cascade => true);
end;
/
然后看看如下一个SQL：
select test2.object_name, test2.object_typefrom test1, test2
where test1.object_type like '%RULE'
and test1.object_id = test2.object_id;
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------------------------
ALERT_QUE$1 RULE
1 row selected.
默认的执行计划为hash join：
Plan hash value: 497311279
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 774 | 33282 | 100 (2)| 00:00:02 |
|* 1 | HASH JOIN | | 774 | 33282 | 100 (2)| 00:00:02 |
|* 2 | TABLE ACCESS FULL| TEST1 | 774 | 9288 | 50 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST2 | 15478 | 468K| 49 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TEST1"."OBJECT_ID"="TEST2"."OBJECT_ID")
2 - filter("TEST1"."OBJECT_TYPE" LIKE '%RULE' AND
"TEST1"."OBJECT_TYPE" IS NOT NULL)
如果我们想让执行计划走NEST LOOP JOIN，而不是优化器选用的HASH JOIN，该如何办？
步骤一：查找到你的查询块的名称
SELECT operation,options,object_name,object_alias
FROM v$sql_plan
WHERE sql_id='7uga1u1twnqw1'
AND child_number=0
/
OPERATION OPTIONS OBJECT_NAME OBJECT_ALIAS
------------------------- -------------------- -------------------- --------------------
SELECT STATEMENT
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL TEST1 TEST1@SEL$1
INDEX RANGE SCAN TEST2_OI_IND TEST2@SEL$1
TABLE ACCESS BY INDEX ROWID TEST2 TEST2@SEL$1
也可以通过select * from table(dbms_xplan.display_cursor(sql_id,null,'outline'))来查看Outline Data来获取查询块。我更喜欢这种方法。
步骤二：构建你的HINT
leading(@"SEL$1" TEST1@"SEL$1") use_nl(@"SEL$1" TEST2@"SEL$1")
步骤三：创建你的SQL PROFILE
declare
l_profile_name varchar2(30);
cl_sql_text clob;
begin
select
sql_fulltext
into
cl_sql_text
from
v$sqlarea
where
sql_id = '7uga1u1twnqw1';
dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text,
profile => sqlprof_attr(q'[leading(@"SEL$1" TEST1@"SEL$1") use_nl(@"SEL$1" TEST2@"SEL$1")]'),
category => '',
name => 'dwrose_xxx',
force_match =>FALSE
);
end;
/
步骤四，确认你的执行计划已经按照要求被改变：
select test2.object_name,test2.object_type from test1,test2 where test1.object_type like '%RULE' and test1.object_id=test2.object_id;
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------------------------
ALERT_QUE$1 RULE
sys@DLSP>select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bhm28h5575bjy, child number 0
-------------------------------------
select test2.object_name,test2.object_type from test1,test2 where
test1.object_type like '%RULE' and test1.object_id=test2.object_id
Plan hash value: 800282841
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1598 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 774 | 33282 | 1598 (1)| 00:00:20 |
|* 3 | TABLE ACCESS FULL | TEST1 | 774 | 9288 | 50 (2)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | TEST2_OI_IND | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 31 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("TEST1"."OBJECT_TYPE" LIKE '%RULE' AND "TEST1"."OBJECT_TYPE" IS NOT
NULL))
4 - access("TEST1"."OBJECT_ID"="TEST2"."OBJECT_ID")
Note
-----
- SQL profile dwrose_xxx used for this statement通过执行计划输出的Note部分可以看到SQL PROFILE已经被使用。
OK，看我们通过如何通过sql profile脚本把执行计划修正为NEST LOOP JOIN，这个过程将变得更加简单：
| >@profile Enter value for sql_id: bhm28h5575bjy -------------第一个步骤：输入SQL_ID
PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- SQL_ID bhm28h5575bjy, child number 0 ------------------------------------- select test2.object_name,test2.object_type from test1,test2 where test1.object_type like '%RULE' and test1.object_id=test2.object_id
Plan hash value: 497311279
---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 100 (100)| | |* 1 | HASH JOIN | | 774 | 33282 | 100 (2)| 00:00:02 | |* 2 | TABLE ACCESS FULL| TEST1 | 774 | 9288 | 50 (2)| 00:00:01 | | 3 | TABLE ACCESS FULL| TEST2 | 15479 | 468K| 49 (0)| 00:00:01 | ----------------------------------------------------------------------------
Outline Data -------------
/*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') OPT_PARAM('_optim_peek_user_binds' 'false') OPT_PARAM('_optimizer_skip_scan_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "TEST1"@"SEL$1") FULL(@"SEL$1" "TEST2"@"SEL$1") LEADING(@"SEL$1" "TEST1"@"SEL$1" "TEST2"@"SEL$1") USE_HASH(@"SEL$1" "TEST2"@"SEL$1") END_OUTLINE_DATA */
Predicate Information (identified by operation id): ---------------------------------------------------
1 - access("TEST1"."OBJECT_ID"="TEST2"."OBJECT_ID") 2 - filter(("TEST1"."OBJECT_TYPE" LIKE '%RULE' AND "TEST1"."OBJECT_TYPE" IS NOT NULL))
42 rows selected. ------------第二个步骤：输入你构造好的HINT，可以写多个，以空格隔开 Enter value for hint_text: LEADING(@"SEL$1" "TEST1"@"SEL$1" "TEST2"@"SEL$1") USE_NL(@"SEL$1" "TEST2"@"SEL$1") Profile profile_bhm28h5575bjy_dwrose created. 需要强调的是，根据上面脚本的输出可看到，Outline Data部分已经为你提供了当前查询计划的HINT，你可以通过改造这些HINT来为新的执行计划所用。例如，我们想让查询走NEST LOOP JOIN，如果是正常的不带查询块的HINT，写法为leading(test1) use_nl(test2)，带上查询块通过参考Outline Data部分提供的HINT，写法为： test@DLSP>select test2.object_name,test2.object_type from test1,test2 where test1.object_type like '%RULE' and test1.object_id=test2.object_id;
OBJECT_NAME OBJECT_TYPE -------------------- -------------------------------------- ALERT_QUE$1 RULE 1 row selected. test@DLSP>select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- SQL_ID bhm28h5575bjy, child number 0 ------------------------------------- select test2.object_name,test2.object_type from test1,test2 where test1.object_type like '%RULE' and test1.object_id=test2.object_id
Plan hash value: 800282841
--------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1598 (100)| | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 774 | 33282 | 1598 (1)| 00:00:20 | |* 3 | TABLE ACCESS FULL | TEST1 | 774 | 9288 | 50 (2)| 00:00:01 | |* 4 | INDEX RANGE SCAN | TEST2_OI_IND | 1 | | 1 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 31 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - filter(("TEST1"."OBJECT_TYPE" LIKE '%RULE' AND "TEST1"."OBJECT_TYPE" IS NOT NULL)) 4 - access("TEST1"."OBJECT_ID"="TEST2"."OBJECT_ID")
Note ----- - SQL profile profile_bhm28h5575bjy_dwrose used for this statement 可以看到执行计划已经被固定为我们需要的NEST LOOP JOIN方式了。Note部分也已经提示这个SQL已经使用到了我们所创建的SQL PROFILE了。 SQL PROFILE脚本如下，enjoy it !!： SET linesize 153 set verify off SET pagesize 10000 accept sql_id - prompt 'Enter value for sql_id: ' - default 'dwrose'
SELECT * FROM TABLE(dbms_xplan.display_cursor('&&sql_id',NULL,'outline'));
accept hint_txt - prompt 'Enter value for hint_text: ' - default 'comment' set feedback off set sqlblanklines on set serveroutput on declare l_profile_name varchar2(30); cl_sql_text clob; begin
select sql_fulltext into cl_sql_text from v$sqlarea where sql_id = '&&sql_id';
select 'profile_'||'&&sql_id'||'_dwrose' into l_profile_name from dual;
dbms_sqltune.import_sql_profile( sql_text => cl_sql_text, profile => sqlprof_attr(q'[&&hint_txt]'), category => '', name => l_profile_name, force_match =>FALSE );
dbms_output.put_line(' '); dbms_output.put_line('Profile '||l_profile_name||' created.'); dbms_output.put_line(' '); end; /
col FIRST_LOAD_TIME for a20 select OBJECT_STATUS,FIRST_LOAD_TIME,plan_hash_value,executions,buffer_gets,LAST_ACTIVE_TIME from v$sql where sql_id='&&sql_id'; pro pro undef sql_id undef hint_txt
set sqlblanklines off set feedback on |
