修正执行计划的利器，SQL PROFILE脚本

2023-02-16 08:29:43

我们先来看下如何手工创建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_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.

默认的执行计划为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，写法为：
LEADING(@"SEL$1" "TEST1"@"SEL$1" "TEST2"@"SEL$1") USE_NL(@"SEL$1" "TEST2"@"SEL$1")

我们来看看是否执行计划已经被修正：

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了。

实在是太easy了，只需要两个步骤就可以去修正一个SQL的执行计划了！！

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


上一篇：JavaScript优化技术


下一篇：20210111 装饰器之案例剖析