Oracle 固定SQL执行计划的方法

Floating warm sun

2674人浏览 · 2023-05-30 14:38:43

Floating warm sun · 2023-05-30 14:38:43 发布

一、SQL Profile

分为两种：自动(Automatic)类型的SQL Profile、手动(Manual)类型的SQL Profile

1.自动(Automatic)类型的SQL Profile

1.1.创建测试表T1

SQL> create table t1 (id number);

Table created.

SQL> declare
begin
for i in 1 .. 10000
loop
insert into t1 values(i);
commit;
end loop;
end;
/

PL/SQL procedure successfully completed.

1.2.创建索引

SQL> create index idx_t1 on t1(id);

Index created.

1.3.对t1表收集统计信息

SQL> exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'T1',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>2,cascade=>true,granularity=>'ALL');

PL/SQL procedure successfully completed.

1.4.使查询不走索引，模拟错误的执行计划

SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where id=1;

	ID
----------
	 1

--查看上面sql的执行计划
SQL> explain plan for  select /*+ no_index(t1 idx_t1) */ * from t1 where id=1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation	      | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	     |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1	 |     1 |     4 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("ID"=1)

此时可以看到执行计划是走全表扫描的，而不是走索引（idx_t1）范围扫描，是一个错误的执行计划。

1.5.通过使用sql调优顾问，对sql生成自动类型的SQL Profile

1.5.1首先创建一个自动调优任务

SQL> declare
my_task_name varchar2(30);
my_sqltext CLOB;
begin
my_sqltext := 'select /*+ no_index(t1 idx_t1) */ * from t1 where id=1';
my_task_name := dbms_sqltune.create_tuning_task(
sql_text => my_sqltext,
user_name => 'TEST',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'my_sql_tuning_2',
description => 'task to tune a query on table t1');
end;
/

PL/SQL procedure successfully completed.

1.5.2.执行自动调优任务

SQL> begin
dbms_sqltune.execute_tuning_task(task_name => 'my_sql_tuning_2');
end;
/

PL/SQL procedure successfully completed.

1.5.3.查看自动调优任务的结果

SQL> select dbms_sqltune.report_tuning_task(task_name => 'my_sql_tuning_2') from dual;

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_2
Tuning Task Owner  : TEST
Workload Type      : Single SQL Statement
Execution Count    : 3
Current Execution  : EXEC_1254
Execution Type     : TUNE SQL
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 03/31/2023 15:13:20
Completed at       : 03/31/2023 15:13:20

-------------------------------------------------------------------------------
Schema Name: TEST
SQL ID     : fqrvjn6x6kdmb
SQL Text   : select /*+ no_index(t1 idx_t1) */ * from t1 where id=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: 90.91%)  --提供了一个配置SQL Profile的建议，是一个比原始sql更好的执行计划。
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_2',
            task_owner => 'TEST', replace => TRUE);


                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time (s):             .000074           .000007      90.54 %
  CPU Time (s):                  .00005                 0        100 %
  User I/O Time (s):                  0                 0 
  Buffer Gets:                       22                 2       90.9 %
  Physical Read Requests:             0                 0 
  Physical Write Requests:            0                 0 
  Physical Read Bytes:                0                 0 
  Physical Write Bytes:               0                 0 
  Rows Processed:                     1                 1 
  Fetches:                            1                 1 
  Executions:                         1                 1 

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     4 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("ID"=1)

2- Using SQL Profile
--------------------
Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("ID"=1)

1.5.4.接受并使用上面的SQL Profile

SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_2',task_owner => 'TEST', replace => TRUE);

PL/SQL procedure successfully completed.

--注：执行accept_sql_profile可能会遇到以下报错：
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 16459

--解决方法
删除调优任务，按照之前步骤重新创建并运行调优任务：
SQL> exec DBMS_SQLTUNE.DROP_TUNING_TASK('<task_name>');

PL/SQL procedure successfully completed.

1.5.5.再次执行查询，查看SQL Profile效果

SQL> explain plan for select /*+ no_index(t1 idx_t1) */ * from t1 where id=1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation	     | Name   | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |	      |	1     |	4     |	1   (0)    | 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |	1     |	4     |	1   (0)    | 00:00:01 |
---------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
   1 - access("ID"=1)

Note
-----
   - SQL profile "SYS_SQLPROF_018736e035950000" used for this statement  ---说明SQL profile固定执行计划生效

1.5.6.修改where条件 where id=2 测试SQL profile固定执行计划是否可用

SQL> explain plan for  select /*+ no_index(t1 idx_t1) */ * from t1 where id=2;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation	      | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	     |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1	 |     1 |     4 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------

   1 - filter("ID"=2)
   
此时可以看到执行计划是走全表扫描的，而不是走索引（idx_t1）范围扫描，说明SQL profile固定执行计划失效了。
想使SQL profile对改动后的SQL生效，可以在accept_sql_profile时加入force_match=true参数。

1.5.7.重新执行dbms_sqltune.accept_sql_profile，添加参数 force_match=true

SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_2',task_owner => 'TEST', replace => TRUE,force_match => TRUE);

PL/SQL procedure successfully completed.

--查看执行计划
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation	     | Name   | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |	      |	1     |	4     |	1   (0)    | 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |	1     |	4     |	1   (0)    | 00:00:01 |
---------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------

   1 - access("ID"=3)

Note
-----
   - SQL profile "SYS_SQLPROF_018736f4009e0001" used for this statement

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation	     | Name   | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |	      |	1     |	4     |	1   (0)    | 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |	1     |	4     |	1   (0)    | 00:00:01 |
---------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------

   1 - access("ID"=4)

Note
-----
   - SQL profile "SYS_SQLPROF_018736f4009e0001" used for this statement
   
通过上面执行计划可以看出，不论是将条件改成 where=3 还是 where=4，所对应的SQL profile依然有效。

1.6.删除SQL profile

select NAME,SQL_TEXT from dba_sql_profiles;
exec dbms_sqltune.drop_sql_profile(name => 'SYS_SQLPROF_018736e035950000');

2.手动(Manual)类型的SQL Profile

2.1.查看当前执行计划

SQL> explain plan for select /*+ no_index(t1 idx_t1) */ * from t1 where id=3;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation	      | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	     |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1	 |     1 |     4 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("ID"=3)

2.2.强制索引后的执行计划

SQL> explain plan for select /*+ index(t1 idx_t1) */ * from t1 where id=3;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation	     | Name   | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |	      |	1     |	4     |	1   (0)    | 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |	1     |	4     |	1   (0)    | 00:00:01 |
---------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - access("ID"=3)

2.3.查看上述两个SQL的哈希值

SQL> select SQL_TEXT,SQL_ID from v$sqltext where sql_text like 'select %id=3%';

SQL_TEXT							                         SQL_ID   
-------------------------------------------------------- -------------
select /*+ index(t1 idx_t1) */ * from t1 where id=3	     78hmyyr63192n
select /*+ no_index(t1 idx_t1) */ * from t1 where id=3   6y0gdy21h3sp5

SQL> select plan_hash_value from v$sql where sql_id='78hmyyr63192n';

PLAN_HASH_VALUE
---------------
     1369807930

SQL> select plan_hash_value from v$sql where sql_id='6y0gdy21h3sp5';

PLAN_HASH_VALUE
---------------
     3617692013

2.4.执行coe_xfr_sql_profile.sql脚本，过程中需要输入原目标SQL（select /+ no_index(t1 idx_t1) / * from t1 where id=3）的sql_id和plan_hash_value

SQL>@/home/oracle/sqlt/utl/coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: 6y0gdy21h3sp5

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     3617692013        .018

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 3617692013

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID	       : "6y0gdy21h3sp5"
PLAN_HASH_VALUE: "3617692013"

SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3  	 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3  	 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;

Execute coe_xfr_sql_profile_6y0gdy21h3sp5_3617692013.sql
on TARGET system in order to create a custom SQL Profile
with plan 3617692013 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

2.5.再次执行coe_xfr_sql_profile.sql脚本，过程中需要输入改写后SQL（select /+ index(t1 idx_t1) / * from t1 where id=3）的sql_id和plan_hash_value

SQL>@/home/oracle/sqlt/utl/coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: 78hmyyr63192n


PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     1369807930        .004

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 1369807930

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID	       : "78hmyyr63192n"
PLAN_HASH_VALUE: "1369807930"

SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3  	 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3  	 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;

Execute coe_xfr_sql_profile_78hmyyr63192n_1369807930.sql
on TARGET system in order to create a custom SQL Profile
with plan 1369807930 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

注：章节2.4、2.5生成的两个脚本，默认在/home/oracle目录下。

2.6.将原SQL的Hint部分替换为改写后SQL的Hint部分

-- there is no need to edit or re-align unmodified pieces.
wa(q'[select /*+ no_index(t1 idx_t1) */ * from t1 where id=3]');
DBMS_LOB.CLOSE(sql_txt);
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
q'[DB_VERSION('11.2.0.4')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."ID"))]',
q'[END_OUTLINE_DATA]');

force_match => TRUE

2.7.替换完成后，执行coe_xfr_sql_profile_6y0gdy21h3sp5_3617692013.sql脚本

SQL>@/home/oracle/coe_xfr_sql_profile_6y0gdy21h3sp5_3617692013.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_6y0gdy21h3sp5_3617692013.sql 11.4.4.4 2023/04/03 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM   carlos.sierra@oracle.com
SQL>REM
SQL>REM SCRIPT
SQL>REM   coe_xfr_sql_profile_6y0gdy21h3sp5_3617692013.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM   This script is generated by coe_xfr_sql_profile.sql
SQL>REM   It contains the SQL*Plus commands to create a custom
SQL>REM   SQL Profile for SQL_ID 6y0gdy21h3sp5 based on plan hash
SQL>REM   value 3617692013.
SQL>REM   The custom SQL Profile to be created by this script
SQL>REM   will affect plans for SQL commands with signature
SQL>REM   matching the one for SQL Text below.
SQL>REM   Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM   None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM   SQL> START coe_xfr_sql_profile_6y0gdy21h3sp5_3617692013.sql;
SQL>REM
SQL>REM NOTES
SQL>REM   1. Should be run as SYSTEM or SYSDBA.
SQL>REM   2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM   3. SOURCE and TARGET systems can be the same or similar.
SQL>REM   4. To drop this custom SQL Profile after it has been created:
SQL>REM	 EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_6y0gdy21h3sp5_3617692013');
SQL>REM   5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM	 for the Oracle Tuning Pack.
SQL>REM   6. If you modified a SQL putting Hints in order to produce a desired
SQL>REM	 Plan, you can remove the artifical Hints from SQL Text pieces below.
SQL>REM	 By doing so you can create a custom SQL Profile for the original
SQL>REM	 SQL but with the Plan captured from the modified SQL (with Hints).
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>VAR signaturef NUMBER;
SQL>REM
SQL>DECLARE
  2  sql_txt CLOB;
  3  h	     SYS.SQLPROF_ATTR;
  4  PROCEDURE wa (p_line IN VARCHAR2) IS
  5  BEGIN
  6  DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
  7  END wa;
  8  BEGIN
  9  DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
 10  DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
 11  -- SQL Text pieces below do not have to be of same length.
 12  -- So if you edit SQL Text (i.e. removing temporary Hints),
 13  -- there is no need to edit or re-align unmodified pieces.
 14  wa(q'[select /*+ no_index(t1 idx_t1) */ * from t1 where id=3]');
 15  DBMS_LOB.CLOSE(sql_txt);
 16  h := SYS.SQLPROF_ATTR(
 17  q'[BEGIN_OUTLINE_DATA]',
 18  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
 19  q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
 20  q'[DB_VERSION('11.2.0.4')]',
 21  q'[ALL_ROWS]',
 22  q'[OUTLINE_LEAF(@"SEL$1")]',
 23  q'[INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."ID"))]',
 24  q'[END_OUTLINE_DATA]');
 25  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
 26  :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
 27  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
 28  sql_text	 => sql_txt,
 29  profile	 => h,
 30  name	 => 'coe_6y0gdy21h3sp5_3617692013',
 31  description => 'coe 6y0gdy21h3sp5 3617692013 '||:signature||' '||:signaturef||'',
 32  category	 => 'DEFAULT',
 33  validate	 => TRUE,
 34  replace	 => TRUE,
 35  force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
 36  DBMS_LOB.FREETEMPORARY(sql_txt);
 37  END;
 38  /

PL/SQL procedure successfully completed.

SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;

	    SIGNATURE
---------------------
 12630920156370535584

	   SIGNATUREF
---------------------
 10266511399854469173

... manual custom SQL Profile has been created

COE_XFR_SQL_PROFILE_6y0gdy21h3sp5_3617692013 completed

2.8.再次执行查询，查看执行计划

SQL>explain plan for select /*+ no_index(t1 idx_t1) */ * from t1 where id=3;

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation	     | Name   | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |	1     |	4     |	1   (0)    | 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |	1     |	4     |	1   (0)    | 00:00:01 |
---------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - access("ID"=3)

Note
-----
   - SQL profile "coe_6y0gdy21h3sp5_3617692013" used for this statement

也就是说，手动类型的SQL Profile也可以在不改变目标SQL的SQL文本的情况下更改其执行计划。
重要的是，手动类型的SQL Profile可以起到很好的稳定SQL执行计划的作用，这一点是自动类型SQL Profile所不具备的。

二、SPM（SQL Plan Management）

SQL Profile实际上是一种被动的技术手段，应用在那些执行计划已经发生了不好的变更的SQL上。
也就是说当SQL语句的执行计划已经出现了问题，通过创建SQL Profile进行解决、稳定这些SQL的执行计划。
这种方式并不能保证后续执行SQL的执行计划不会发生错误的变更。

SPM是一种主动稳定执行计划的手段，能够保证只有被验证过的执行计划才会被启用。
当启用SPM后，每一个SQL都会存在对应的SQL Plan Baseline，里面存储的就是SQL的执行计划。
如果一个SQL有多个执行计划，就会有多个SQL Plan Baseline，可以从视图dba_sql_plan_baselines中查看。
dba_sql_plan_baselines中ENABLED和ACCEPTED列描述一个SQL Plan Baseline所对应的执行计划是否被Oracle启用。
只有上述两列的值都为YES，所对应的执行计划才会被Oracle启用。
存在多个SQL Plan Baseline时，Oracle会从中选择成本值最小的一个，所对应的执行计划来作为该SQL的执行计划。

在Oracle 11g及其以上版本中，有以下两种方法可以产生目标SQL的SQL Plan Baseline：
（1）自动捕获。
（2）手工生成/批量导入。

1.自动捕获

参数optimizer_capture_sql_plan_baselines用于控制是否开启自动捕获。默认值为FALSE。
参数optimizer_use_sql_plan_baselines用于控制是否启用SQL Plan Baseline。默认值为TRUE。
以上两个参数都可以在会话和系统级别动态修改。

1.1.当前会话禁用SPM，并开启自动捕获

SQL> show parameter sql_plan

NAME				                    TYPE	 VALUE
------------------------------------ ----------- -----
optimizer_capture_sql_plan_baselines   boolean	 FALSE
optimizer_use_sql_plan_baselines       boolean	 TRUE

SQL> alter session set optimizer_use_sql_plan_baselines=false;

Session altered.

SQL> alter session set optimizer_capture_sql_plan_baselines=true;

Session altered.

1.2.创建测试表T2，并创建索引

SQL> create table t2 as select * from dba_objects;

Table created.

SQL> create index idx_t2 on t2(object_id);

Index created.

1.3.对表t2收集统计信息

SQL> exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'T2',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>2,cascade=>true,granularity=>'ALL');

PL/SQL procedure successfully completed.

1.4.执行查询，并查看执行计划

SQL> select object_id,object_name from t2 where object_id between 103 and 108;

 OBJECT_ID OBJECT_NAME
---------- ----------------
    103     MIGRATE$
    104     DEPENDENCY$
    105     ACCESS$
    106     I_DEPENDENCY1
    107     I_DEPENDENCY2
    108     I_ACCESS1

SQL> explain plan for select object_id,object_name from t2 where object_id between 103 and 108;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2008370210

--------------------------------------------------------------------------------------
| Id  | Operation		            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	        |	     |	   7 |	 210 |	   3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |	   7 |	 210 |	   3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	        | IDX_T2 |	   7 |	     |	   2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------

   2 - access("OBJECT_ID">=103 AND "OBJECT_ID"<=108)

此SQL的执行计划走的是IDX_T2索引范围扫描。

--此时没有捕获到SQL Plan Baseline
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';

no rows selected

1.5.目标SQL重复执行后，Oracle针对执行计划（对索引IDX_T2的索引范围扫描）生成了一个SQL Plan Baseline

SQL> select object_id,object_name from t2 where object_id between 103 and 108;

SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';

SQL_HANDLE		       PLAN_NAME		                ORIGIN	    ENA ACC SQL_TEXT
--------------------- ------------------------------ -------------- --- --- -------------------------------------------------------------------------
SQL_ac526b1e4be74880  SQL_PLAN_asnmb3t5yfk4024c6dbb6  AUTO-CAPTURE  YES YES select object_id,object_name from t2 where object_id between 103 and 108

注：dba_sql_plan_baselines视图常用列解释
SQL_HANDLE：字符串形式的唯一SQL标识符作为搜索关键字
PLAN_NAME：计划名称
ORIGIN：如何创建的计划基线，四种方式，（1）MANUAL-LOAD（2）AUTO-CAPTURE（3）MANUAL-SQLTUNE（4）AUTO-SQLTUNE
ENABLED：指示计划基线是否启用
ACCEPTED：指示是否接受计划基线
SQL_TEXT：SQL文本信息

1.6.修改索引聚簇因子值，使其执行计划走全表扫描

SQL> exec dbms_stats.set_index_stats(ownname => 'TEST',indname => 'IDX_T2',clstfct => 24000000,no_invalidate => false);

PL/SQL procedure successfully completed.

SQL> select index_name,clustering_factor from dba_indexes where index_name='IDX_T2';

INDEX_NAME CLUSTERING_FACTOR
---------- -----------------
  IDX_T2	   24000000

1.7.执行查询，并查看执行计划

SQL> explain plan for select object_id,object_name from t2 where object_id between 103 and 108;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation	      | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	     |     7 |   210 |   345   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T2	 |     7 |   210 |   345   (1)| 00:00:05 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------

   1 - filter("OBJECT_ID"<=108 AND "OBJECT_ID">=103)

1.8.产生新的执行计划，并自动捕获产生一个新的SQL Plan Baseline

SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';

SQL_HANDLE		       PLAN_NAME		                 ORIGIN	    ENA ACC SQL_TEXT
--------------------- ------------------------------ -------------- --- --- -------------------------------------------------------------------------
SQL_ac526b1e4be74880  SQL_PLAN_asnmb3t5yfk4024c6dbb6  AUTO-CAPTURE  YES YES select object_id,object_name from t2 where object_id between 103 and 108
SQL_ac526b1e4be74880  SQL_PLAN_asnmb3t5yfk40b860bcf2  AUTO-CAPTURE  YES NO  select object_id,object_name from t2 where object_id between 103 and 108

1.9.当前会话禁用自动捕获，并开启SPM

SQL> alter session set optimizer_capture_sql_plan_baselines=false;

Session altered.

SQL> alter session set optimizer_use_sql_plan_baselines=true;

Session altered.

1.10.IDX_T2索引的聚簇因子等条件都没有改变，查看执行计划

SQL> select index_name,clustering_factor from dba_indexes where index_name='IDX_T2';

INDEX_NAME CLUSTERING_FACTOR
---------- -----------------
  IDX_T2	   24000000

SQL> explain plan for select object_id,object_name from t2 where object_id between 103 and 108;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2008370210

--------------------------------------------------------------------------------------
| Id  | Operation		            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	        |	     |	   7 |	 210 |	1860   (0)| 00:00:23 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |	   7 |	 210 |	1860   (0)| 00:00:23 |
|*  2 |   INDEX RANGE SCAN	        | IDX_T2 |	   7 |	     |	   2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------

   2 - access("OBJECT_ID">=103 AND "OBJECT_ID"<=108)

Note
-----
   - SQL plan baseline "SQL_PLAN_asnmb3t5yfk4024c6dbb6" used for this statement

从上述执行计划可以看到，走的是索引范围扫描，也就是SQL_PLAN_asnmb3t5yfk4024c6dbb6这个计划基线（存在IDX_T2索引）。
也就是说Oracle会使用ENABLED、ACCEPTED均为YES的SQL plan baseline作为执行计划。
测试结果可以看出SPM可以固定执行计划。

1.11.启用全表扫描的执行计划

可以使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE和DBMS_SPM.ALTER_SQL_PLAN_BASELINE达到启用目标SQL新执行计划的目的。

1.11.1使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE将目标SQL新执行计划所对应的SQL plan baseline（SQL_PLAN_asnmb3t5yfk40b860bcf2）的ACCEPTED值更改为YES

SQL> var temp varchar2(1000);
SQL> exec :temp :=dbms_spm.evolve_sql_plan_baseline(sql_handle => 'SQL_ac526b1e4be74880',plan_name => 'SQL_PLAN_asnmb3t5yfk40b860bcf2',verify => 'NO',commit => 'YES');

PL/SQL procedure successfully completed.

--查看ACCEPTED值更改是否成功
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';

SQL_HANDLE		       PLAN_NAME		                ORIGIN	    ENA ACC SQL_TEXT
--------------------- ------------------------------ -------------- --- --- -------------------------------------------------------------------------
SQL_ac526b1e4be74880  SQL_PLAN_asnmb3t5yfk4024c6dbb6  AUTO-CAPTURE  YES YES select object_id,object_name from t2 where object_id between 103 and 108
SQL_ac526b1e4be74880  SQL_PLAN_asnmb3t5yfk40b860bcf2  AUTO-CAPTURE  YES YES select object_id,object_name from t2 where object_id between 103 and 108

1.11.2.使用DBMS_SPM.ALTER_SQL_PLAN_BASELINE将索引IDX_T2执行计划所对应的SQL plan baseline（SQL_PLAN_asnmb3t5yfk4024c6dbb6）的ENABLED值更改为NO

SQL> exec :temp :=dbms_spm.alter_sql_plan_baseline(sql_handle => 'SQL_ac526b1e4be74880',plan_name => 'SQL_PLAN_asnmb3t5yfk4024c6dbb6',attribute_name => 'ENABLED',attribute_value => 'NO');

PL/SQL procedure successfully completed.

SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';

SQL_HANDLE		       PLAN_NAME		                 ORIGIN	    ENA ACC SQL_TEXT
--------------------- ------------------------------ -------------- --- --- -------------------------------------------------------------------------
SQL_ac526b1e4be74880  SQL_PLAN_asnmb3t5yfk4024c6dbb6  AUTO-CAPTURE  NO  YES select object_id,object_name from t2 where object_id between 103 and 108
SQL_ac526b1e4be74880  SQL_PLAN_asnmb3t5yfk40b860bcf2  AUTO-CAPTURE  YES YES select object_id,object_name from t2 where object_id between 103 and 108

--此时的执行计划就使用了全表扫描
SQL> explain plan for select object_id,object_name from t2 where object_id between 103 and 108;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation	      | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	     |     7 |   210 |   345   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T2	 |     7 |   210 |   345   (1)| 00:00:05 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

   1 - filter("OBJECT_ID"<=108 AND "OBJECT_ID">=103)

Note
-----
   - SQL plan baseline "SQL_PLAN_asnmb3t5yfk40b860bcf2" used for this statement

以此验证了我们可以在目标SQL的多个执行计划中进行切换，所以SPM确实是既能够主动的稳定执行计划，又保留了继续使用新的执行计划的机会。
并且可以很容易的启用新的执行计划。

2.手工生成SQL Plan Baseline

使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE手工生成其初始执行计划所对应的SQL plan baseline。

2.1.模拟错误的执行计划

SQL> select /*+ no_index(t2 idx_t2) */object_name,object_id from t2 where object_id=4;

OBJECT_NAME	OBJECT_ID
----------- ---------
TAB$		    4

SQL> explain plan for select /*+ no_index(t2 idx_t2) */object_name,object_id from t2 where object_id=4;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation	      | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	     |     1 |    30 |   345   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T2	 |     1 |    30 |   345   (1)| 00:00:05 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------

   1 - filter("OBJECT_ID"=4)

SQL> select SQL_ID from v$sqlarea where sql_text='select /*+ no_index(t2 idx_t2) */object_name,object_id from t2 where object_id=4';

SQL_ID
-------------
76td2qkxcrn9v

2.2.使用所对应的sql_id和Plan hash value，手工生成SQL Plan Baseline

SQL> var temp number
SQL> exec :temp :=dbms_spm.load_plans_from_cursor_cache(sql_id =>'76td2qkxcrn9v',plan_hash_value => 1513984157);

PL/SQL procedure successfully completed.

SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(t2 idx_t2) */object_name%';

SQL_HANDLE		       PLAN_NAME		                ORIGIN	   ENA ACC SQL_TEXT
--------------------- ------------------------------ ------------- --- --- --------------------------------------------------------------------------------
SQL_75b06ae056223f5f  SQL_PLAN_7bc3aw1b24guzb860bcf2  MANUAL-LOAD  YES YES select /*+ no_index(t2 idx_t2) */object_name,object_id from t2 where object_id=4

2.3.改写SQL，使其强制走索引

SQL> select /*+ index(t2 idx_t2) */object_name,object_id from t2 where object_id=4;

OBJECT_NAME	OBJECT_ID
----------- ---------
TAB$		    4

SQL> explain plan for select /*+ index(t2 idx_t2) */object_name,object_id from t2 where object_id=4;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 2008370210

-------------------------------------------------------------------------------------------
| Id  | Operation		                 | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	             |	      |	   1  |	  30  |	 279   (0) | 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | T2     |	   1  |	  30  |	 279   (0) | 00:00:04 |
|*  2 |   INDEX RANGE SCAN	             | IDX_T2 |	   1  |	      |	   1   (0) | 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------

   2 - access("OBJECT_ID"=4)

2.4.使用改写SQL所对应的sql_id和Plan hash value，以及原SQL Plan Baseline的sql_handle值，生成新的SQL Plan Baseline

SQL> select SQL_ID from v$sqlarea where sql_text='select /*+index(t2 idx_t2) */object_name,object_id from t2 where object_id=4';

SQL_ID
-------------
96ct19dgmkpax

SQL> exec :temp :=dbms_spm.load_plans_from_cursor_cache(sql_id =>'96ct19dgmkpax',plan_hash_value => 2008370210,sql_handle => 'SQL_75b06ae056223f5f');

PL/SQL procedure successfully completed.

SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(t2 idx_t2) */object_name%';

SQL_HANDLE		       PLAN_NAME		                ORIGIN	    ENA ACC SQL_TEXT
--------------------- ------------------------------ -------------- --- --- --------------------------------------------------------------------------------
SQL_75b06ae056223f5f  SQL_PLAN_7bc3aw1b24guz24c6dbb6  MANUAL-LOAD   YES YES select /*+ no_index(t2 idx_t2) */object_name,object_id from t2 where object_id=4
SQL_75b06ae056223f5f  SQL_PLAN_7bc3aw1b24guzb860bcf2  MANUAL-LOAD   YES YES select /*+ no_index(t2 idx_t2) */object_name,object_id from t2 where object_id=4

2.5.删除原有的执行计划，也就是全表扫描所对应的SQL Plan Baseline

SQL> exec :temp :=dbms_spm.drop_sql_plan_baseline(sql_handle =>'SQL_75b06ae056223f5f',plan_name => 'SQL_PLAN_7bc3aw1b24guzb860bcf2');

PL/SQL procedure successfully completed.

SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(t2 idx_t2) */object_name%';

SQL_HANDLE		       PLAN_NAME		                 ORIGIN	    ENA ACC SQL_TEXT
--------------------- ------------------------------ -------------- --- --- --------------------------------------------------------------------------------
SQL_75b06ae056223f5f  SQL_PLAN_7bc3aw1b24guz24c6dbb6   MANUAL-LOAD  YES YES select /*+ no_index(t2 idx_t2) */object_name,object_id from t2 where object_id=4

2.6.执行查询，并查看执行计划

SQL> explain plan for select /*+ no_index(t2 idx_t2) */object_name,object_id from t2 where object_id=4;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2008370210

--------------------------------------------------------------------------------------
| Id  | Operation		            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	        |	     |	   1 |	  30 |	 279   (0)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |	   1 |	  30 |	 279   (0)| 00:00:04 |
|*  2 |   INDEX RANGE SCAN	        | IDX_T2 |	   1 |	     |	   1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------

   2 - access("OBJECT_ID"=4)

Note
-----
   - SQL plan baseline "SQL_PLAN_7bc3aw1b24guz24c6dbb6" used for this statement

此时手工生成SQL plan baseline就完成了在不改变目标SQL的情况下，更改其执行计划的目的。


参考：崔华 著 《基于Oracle的SQL优化》

天工造物局

北京人形旗下天工造物具身智能开源社区，聚焦具身天工与慧思开物两大平台

更多推荐

deck.gl TripsLayer 出租车轨迹动画示例实战：从示例运行到着色器原理

TripsLayer 是 deck.gl 中用于渲染**随时间流动的轨迹动画**（如出租车行程、物流路径、迁徙路线）的核心图层。本文以仓库中的最小独立示例 [examples/website/trips](https://link.gitcode.com/i/1442af8f442f4cff39ff546e050fceb5) 为骨架，带你完成从安装依赖、启动应用、理解曼哈顿出租车数据格式，到深入

天工造物局

Foundry Anvil 中 eth_simulateV1 的 Blob 交易与 Blob Gas 字段规范化：原理、实现与测试验证

本篇技术指南以 [anvil-simulate-blob-transactions.md](https://link.gitcode.com/i/3d2ebe227262de169335eb290af80da8) 这条 changelog 条目为起点，深入剖析 Foundry Anvil 对 `eth_simulateV1` RPC 返回结果中 blob（EIP-4844）交易与 blob gas

天工造物局

LMCache CLI 实战：用 `lmcache kvcache clear` 管理运行中服务的 KV Cache 状态

本文是一份针对 LMCache 官方 CLI 命令 `lmcache kvcache` 的完整技术指南。该命令用于在**运行中的 LMCache 服务（MP HTTP server）**上管理 KV cache 状态，当前提供 `clear` 子命令，用于清空 L1（CPU 内存）层中缓存的全部 KV 数据。读完本文，你将掌握该命令的完整参数体系、退出码语义、终端/JSON/静默三种输出模式，并能

天工造物局

所有评论(0)

查看更多评论

Floating warm sun

@loveLAxin

已为社区贡献2条内容

Oracle 固定SQL执行计划的方法

Floating warm sun

一、SQL Profile

1.自动(Automatic)类型的SQL Profile

1.1.创建测试表T1

1.2.创建索引

1.3.对t1表收集统计信息

1.4.使查询不走索引，模拟错误的执行计划

1.5.通过使用sql调优顾问，对sql生成自动类型的SQL Profile

1.5.1首先创建一个自动调优任务

1.5.2.执行自动调优任务

1.5.3.查看自动调优任务的结果

1.5.4.接受并使用上面的SQL Profile

1.5.5.再次执行查询，查看SQL Profile效果

1.5.6.修改where条件 where id=2 测试SQL profile固定执行计划是否可用

1.5.7.重新执行dbms_sqltune.accept_sql_profile，添加参数 force_match=true

1.6.删除SQL profile

2.手动(Manual)类型的SQL Profile

2.1.查看当前执行计划

2.2.强制索引后的执行计划

2.3.查看上述两个SQL的哈希值

2.4.执行coe_xfr_sql_profile.sql脚本，过程中需要输入原目标SQL（select /*+ no_index(t1 idx_t1) */ * from t1 where id=3）的sql_id和plan_hash_value

2.5.再次执行coe_xfr_sql_profile.sql脚本，过程中需要输入改写后SQL（select /*+ index(t1 idx_t1) */ * from t1 where id=3）的sql_id和plan_hash_value

2.6.将原SQL的Hint部分替换为改写后SQL的Hint部分

2.7.替换完成后，执行coe_xfr_sql_profile_6y0gdy21h3sp5_3617692013.sql脚本

2.8.再次执行查询，查看执行计划

二、SPM（SQL Plan Management）

1.自动捕获

1.1.当前会话禁用SPM，并开启自动捕获

1.2.创建测试表T2，并创建索引

1.3.对表t2收集统计信息

1.4.执行查询，并查看执行计划

1.5.目标SQL重复执行后，Oracle针对执行计划（对索引IDX_T2的索引范围扫描）生成了一个SQL Plan Baseline

1.6.修改索引聚簇因子值，使其执行计划走全表扫描

1.7.执行查询，并查看执行计划

1.8.产生新的执行计划，并自动捕获产生一个新的SQL Plan Baseline

1.9.当前会话禁用自动捕获，并开启SPM

1.10.IDX_T2索引的聚簇因子等条件都没有改变，查看执行计划

1.11.启用全表扫描的执行计划

1.11.1使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE将目标SQL新执行计划所对应的SQL plan baseline（SQL_PLAN_asnmb3t5yfk40b860bcf2）的ACCEPTED值更改为YES

1.11.2.使用DBMS_SPM.ALTER_SQL_PLAN_BASELINE将索引IDX_T2执行计划所对应的SQL plan baseline（SQL_PLAN_asnmb3t5yfk4024c6dbb6）的ENABLED值更改为NO

2.手工生成SQL Plan Baseline

2.1.模拟错误的执行计划

2.2.使用所对应的sql_id和Plan hash value，手工生成SQL Plan Baseline

2.3.改写SQL，使其强制走索引

2.4.使用改写SQL所对应的sql_id和Plan hash value，以及原SQL Plan Baseline的sql_handle值，生成新的SQL Plan Baseline

2.5.删除原有的执行计划，也就是全表扫描所对应的SQL Plan Baseline

2.6.执行查询，并查看执行计划

所有评论(0)

温馨提示：您尚未绑定手机号

Floating warm sun

2.4.执行coe_xfr_sql_profile.sql脚本，过程中需要输入原目标SQL（select /+ no_index(t1 idx_t1) / * from t1 where id=3）的sql_id和plan_hash_value

2.5.再次执行coe_xfr_sql_profile.sql脚本，过程中需要输入改写后SQL（select /+ index(t1 idx_t1) / * from t1 where id=3）的sql_id和plan_hash_value