暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / oracle固定执行计划

oracle固定执行计划

原创 Leo 2023-06-18
1529

文档课题：oracle固定执行计划.

本文介绍oracle固定执行计划的2种方法，outline，SQL Profile，其中SQL Profile是重点。

1、outline

1.1、相关概念

开发环境中SQL的执行无问题，但在生产环境执行效率却异常缓慢。若此时更改SQL需重新修改及编译源程序，成本非常高。此时可使用outline在不改变原应用程序的情况下更改特定SQL的执行计划。

outline的原理是将调好的SQL执行计划(一系列HINT)存储起来，然后将该执行计划所对应的SQL用目前系统效率低下的SQL替代，从而达到系统每次执行该SQL时都使用已存储的执行计划。

当发现低效SQL，可使用hint对其优化；对于SQL代码可以修改的场景，直接修改SQL代码加上hint即可；对于SQL代码不可修改的情况，Oracle提供outline功能为SQL修改hint。

使用 outLine步骤：

A、生成新SQL和老SQL的2个outline

B、交换两个SQL的提示信息

C、ON LOGON触发器设定session的CATEGORY(自定义类别）

1.2、outline测试过程

1.2.1、数据准备

LEO@orcl150> select * from v$version;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

 

LEO@orcl150> col username for a15

LEO@orcl150> col granted_role for a15

LEO@orcl150> select * from user_role_privs;

 

USERNAME        GRANTED_ROLE    ADM DEF OS_

--------------- --------------- --- --- ---

LEO             CONNECT         NO  YES NO

LEO             DBA             NO  YES NO

LEO@orcl150> select * from session_privs where privilege like '%OUTLINE%';

 

PRIVILEGE

----------------------------------------

CREATE ANY OUTLINE

ALTER ANY OUTLINE

DROP ANY OUTLINE

 

SYS@orcl150> grant all on ol$hints to leo;

 

Grant succeeded.

 

SYS@orcl150> conn leo/leo;

Connected.

 

LEO@orcl150> create table tb_leo_20230305 as select * from dba_tables;

 

Table created.

 

LEO@orcl150> create index idx_tb_leo_20230305 on tb_leo_20230305(table_name);

 

Index created.

 

LEO@orcl150> set autotrace on

LEO@orcl150> select owner from tb_leo_20230305 where table_name='TB_LEO_20230305';

 

no rows selected

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3703795454

 

---------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                     |     1 |    34 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TB_LEO_20230305     |     1 |    34 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_TB_LEO_20230305 |     1 |       |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("TABLE_NAME"='TB_LEO_20230305')

 

Note

-----

   - dynamic sampling used for this statement (level=2)

 

 

Statistics

----------------------------------------------------------

         11  recursive calls

          0  db block gets

         71  consistent gets

          1  physical reads

          0  redo size

        333  bytes sent via SQL*Net to client

        513  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

0         rows processed

LEO@orcl150> select /*+full(TB_LEO_20230305)*/ owner from tb_leo_20230305 where table_name='TB_LEO_20230305';

 

no rows selected

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2133804736

 

-------------------------------------------------------------------------------------

| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                 |     1 |    34 |    31   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TB_LEO_20230305 |     1 |    34 |    31   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("TABLE_NAME"='TB_LEO_20230305')

 

Note

-----

   - dynamic sampling used for this statement (level=2)

 

 

Statistics

----------------------------------------------------------

          7  recursive calls

          0  db block gets

        168  consistent gets

          0  physical reads

          0  redo size

        333  bytes sent via SQL*Net to client

        513  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

0         rows processed

LEO@orcl150> set autotrace off;

1.2.2、创建outline

--分别给索引扫描与全表扫描创建各自的outline

LEO@orcl150> create or replace outline tb_leo_20230305_l on select owner from tb_leo_20230305 where table_name='TB_LEO_20230305';

 

Outline created.

 

LEO@orcl150> create or replace outline tb_leo_20230305_m on select /*+full(TB_LEO_20230305)*/ owner from tb_leo_20230305 where table_name='TB_LEO_20230305';

 

Outline created.

1.2.3、确认outline

LEO@orcl150> col sql_text for a80

LEO@orcl150> set long 2000

LEO@orcl150> select name,used,sql_text from dba_outlines where name like '%TB_LEO_20230305%';

 

NAME                 USED   SQL_TEXT

-------------------- ------ ----------------------------------------------------------------------------------------------------

TB_LEO_20230305_L    UNUSED select owner from tb_leo_20230305 where table_name='TB_LEO_20230305'

TB_LEO_20230305_M    UNUSED select /*+full(TB_LEO_20230305)*/ owner from tb_leo_20230305 where table_name='TB_LEO_20230305'

1.2.4、互换执行计划

--更新outln.ol$表，达到执行计划的互换.

LEO@orcl150> update outln.ol$ set ol_name=decode(ol_name,'TB_LEO_20230305_M','TB_LEO_20230305_L','TB_LEO_20230305_L','TB_LEO_20230305_M') where ol_name in ('TB_LEO_20230305_L','TB_LEO_20230305_M');

 

2 rows updated.

 

LEO@orcl150> commit;

 

Commit complete.

 

LEO@orcl150> select name,used,sql_text from dba_outlines where name like '%TB_LEO_20230305%';

 

NAME                 USED   SQL_TEXT

-------------------- ------ ----------------------------------------------------------------------------------------------------

TB_LEO_20230305_L    UNUSED select /*+full(TB_LEO_20230305)*/ owner from tb_leo_20230305 where table_name='TB_LEO_20230305'

TB_LEO_20230305_M    UNUSED select owner from tb_leo_20230305 where table_name='TB_LEO_20230305'

LEO@orcl150> col hint for a90

LEO@orcl150> select name,hint from dba_outline_hints where join_pos=1 and name like '%TB_LEO_20230305%';

 

NAME                 HINT

-------------------- ------------------------------------------------------------------------------------------

TB_LEO_20230305_L    INDEX_RS_ASC(@"SEL$1" "TB_LEO_20230305"@"SEL$1" ("TB_LEO_20230305"."TABLE_NAME"))

TB_LEO_20230305_M    FULL(@"SEL$1" "TB_LEO_20230305"@"SEL$1")

1.2.5、互换后验证

--执行计划互换后，对sql语句进行验证

LEO@orcl150> set autotrace on

LEO@orcl150> alter system set use_stored_outlines=true;

 

System altered.

 

LEO@orcl150> select owner from tb_leo_20230305 where table_name='TB_LEO_20230305';

 

no rows selected

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2133804736

 

-------------------------------------------------------------------------------------

| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                 |    88 |  2992 |    31   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TB_LEO_20230305 |    88 |  2992 |    31   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("TABLE_NAME"='TB_LEO_20230305')

 

Note

-----

   - outline "TB_LEO_20230305_M" used for this statement

 

 

Statistics

----------------------------------------------------------

         36  recursive calls

        148  db block gets

        124  consistent gets

          1  physical reads

        624  redo size

        333  bytes sent via SQL*Net to client

        513  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

0         rows processed

 

说明：如上所示，执行计划互换后，原本应走索引范围扫描变成全表扫描。

LEO@orcl150> select /*+full(TB_LEO_20230305)*/ owner from tb_leo_20230305 where table_name='TB_LEO_20230305';

 

no rows selected

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3703795454

 

---------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                     |    88 |  2992 |     7   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TB_LEO_20230305     |    88 |  2992 |     7   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_TB_LEO_20230305 |    35 |       |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("TABLE_NAME"='TB_LEO_20230305')

 

Note

-----

   - outline "TB_LEO_20230305_L" used for this statement

 

 

Statistics

----------------------------------------------------------

         34  recursive calls

        147  db block gets

         23  consistent gets

          0  physical reads

        584  redo size

        333  bytes sent via SQL*Net to client

        513  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

          0  rows processed

说明：如上所示，执行计划互换后，原本应该是全表扫描的变成索引范围扫描.

2、sql profile

2.1、相关概念

oracle 11g后续版本参数use_stored_outlines已不存在。意味着不能使用create outline方式来为一个sql创建hint，以便使用store outline固定执行计划。SQL Profile就是为某一SQL语句提供除系统统计信息、对象（表和索引等）统计信息之外的其他信息(比如运行环境、额外的更准确的统计信息)，以帮助优化器为SQL语句选择更适合的执行计划。Outlines能实现的功能SQL Profiles完全能够实现，而且SQL Profiles具有Outlines不具备的优点，最重要的有以下两点： 

A、SQL Profiles更容易生成、更改和控制；

B、SQL Profiles在对SQL语句的支持上做得更好，适用范围更广。 

10g之前有outlines，10g之后sql profile作为新特性之一出现。如果针对非绑定变量的sql，outlines会力不从心。sql profile最大的优点是在不修改sql语句和会话运行环境的情况下去优化sql的执行效率，适合无法在应用程序中修改sql语句的场景.

SQL Profile对以下类型语句有效：

SELECT语句；

UPDATE语句；

INSERT语句（仅当使用SELECT子句时有效）；

DELETE语句；

CREATE语句（仅当使用SELECT子句时有效）；

MERGE语句（仅当作UPDATE和INSERT操作时有效）。

另外，使用SQL Profile还必须有CREATE ANY SQL PROFILE、DROP ANY SQL PROFILE和ALTER ANY SQL PROFILE等系统权限。

2.2、SQL Profile测试

说明：有两种生成SQL Profile的方法，分别为手动和STA。

2.2.1、手工创建SQL Profile 方法1

2.2.1.1、准备数据

--手工创建SQL Profile

创建测试表，根据DBA_OBJECTS创建，OBJECT_ID上有索引。

LEO@orcl150> select * from v$version;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

LEO@orcl150> drop table tb_leo_20230305 purge;

 

Table dropped.

 

LEO@orcl150> create table tb_leo_20230305 as select * from dba_objects;

 

Table created.

 

LEO@orcl150> create index ind_tb_leo_id on tb_leo_20230305(object_id);

 

Index created.

--查看sql的默认执行计划，走的索引，通过指定outline可以获取系统为sql生成的hint

LEO@orcl150> explain plan for select * from tb_leo_20230305 where object_id=:a;

 

Explained.

LEO@orcl150> select * from table(dbms_xplan.display(null,null,'outline'));

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

Plan hash value: 3587901987

 

-----------------------------------------------------------------------------------------------

| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                 |   815 |   164K|     7   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TB_LEO_20230305 |   815 |   164K|     7   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IND_TB_LEO_ID   |   326 |       |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------

 

Outline Data

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

  /*+

      BEGIN_OUTLINE_DATA

      INDEX_RS_ASC(@"SEL$1" "TB_LEO_20230305"@"SEL$1" ("TB_LEO_20230305"."OBJECT_ID"))

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      DB_VERSION('11.2.0.4')

      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

  */

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("OBJECT_ID"=TO_NUMBER(:A))

 

Note

   - dynamic sampling used for this statement (level=2)

 

32 rows selected.

2.2.1.2、生成全表扫描hint

--如果想让其走全表扫描，首先获取全表扫描hint。

LEO@orcl150> explain plan for select /*+ full(TB_LEO_20230305) */ * from tb_leo_20230305 where object_id=:a;

 

Explained.

LEO@orcl150> select * from table(dbms_xplan.display(null,null,'outline'));

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

Plan hash value: 2133804736

 

-------------------------------------------------------------------------------------

| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                 |   815 |   164K|   345   (1)| 00:00:05 |

|*  1 |  TABLE ACCESS FULL| TB_LEO_20230305 |   815 |   164K|   345   (1)| 00:00:05 |

-------------------------------------------------------------------------------------

 

Outline Data

-------------

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

 

  /*+

      BEGIN_OUTLINE_DATA

      FULL(@"SEL$1" "TB_LEO_20230305"@"SEL$1")

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      DB_VERSION('11.2.0.4')

      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("OBJECT_ID"=TO_NUMBER(:A))

 

Note

-----

   - dynamic sampling used for this statement (level=2)

 

31 rows selected.

说明：可以看到全面扫描的hint已经生成。

2.2.1.3、创建sql profile

--根据生成的hint信息，选取必要的数据，创建sql profile。

LEO@orcl150> declare v_hints sys.sqlprof_attr;

  2  begin

  3  v_hints := sys.sqlprof_attr('FULL(@"SEL$1" "TB_LEO_20230305"@"SEL$1")');   --从上面outline data部分获取到hint

  4  dbms_sqltune.import_sql_profile('select * from TB_LEO_20230305 where object_id= :a',  --sql语句部分

  5  v_hints,

  6  'TB_LEO_20230305',   --profile的名字

  7  force_match => true);

  8  end;

  9  /

 

PL/SQL procedure successfully completed.

2.2.1.4、验证执行计划

--查看是否生效。

LEO@orcl150> explain plan for select * from tb_leo_20230305 where object_id=:a;

 

Explained.

 

LEO@orcl150> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

Plan hash value: 2133804736

 

-------------------------------------------------------------------------------------

| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                 |   815 |   164K|   345   (1)| 00:00:05 |

|*  1 |  TABLE ACCESS FULL| TB_LEO_20230305 |   815 |   164K|   345   (1)| 00:00:05 |

-------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

 

   1 - filter("OBJECT_ID"=TO_NUMBER(:A))

 

Note

-----

   - dynamic sampling used for this statement (level=2)

   - SQL profile "TB_LEO_20230305" used for this statement

 

18 rows selected.

LEO@orcl150> SELECT b.name, d.sql_text, extractvalue(value(h), '.') as hints

  2    FROM dba_sql_profiles d,

  3         SYS.SQLOBJ$DATA  a,

  4         SYS.SQLOBJ$ b,

  5         TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA),'/outline_data/hint'))) h

  6  where a.signature=b.signature

  7    and a.category=b.category

  8    and a.obj_type=b.obj_type

  9    and a.plan_id=b.plan_id

 10    and a.signature=d.signature

 11    and d.name='TB_LEO_20230305';

 

NAME                 SQL_TEXT                                           HINTS

-------------------- -------------------------------------------------- --------------------------------------------------

TB_LEO_20230305      select * from TB_LEO_20230305 where object_id= :a  FULL(@"SEL$1" "TB_LEO_20230305"@"SEL$1")

说明：如上所示，原本应该走索引范围扫描的语句，使用sql profile修改执行计划后变为全表扫描.

2.2.2、手工创建SQL Profile 方法2

使用coe_xfr_sql_profile.sql脚本生成sqlprof_attr数据，比较麻烦的是sqlprof_attr('FULL(t1@SEL$1)')格式的编辑。Mos Note 215187.1中sqlt.zip的目录utl提供脚本coe_xfr_sql_profile.sql可以生成这些信息。

说明：建议使用该方法固定执行计划.

2.2.2.1、数据准备

LEO@orcl150> create table scott.test as select * from dba_objects;

 

Table created.

 

LEO@orcl150> create index scott.idx_test_01 on scott.test(object_id);

 

Index created.

 

LEO@orcl150> exec dbms_stats.gather_table_stats('scott','test',cascade=>true);

 

PL/SQL procedure successfully completed.

 

LEO@orcl150> update scott.test set object_id=10 where object_id>10;

 

86419 rows updated.

 

LEO@orcl150> commit;

 

Commit complete.

LEO@orcl150> select object_id,count(*) from scott.test group by object_id;

 

 OBJECT_ID   COUNT(*)

---------- ----------

         6          1

         2          1

         5          1

         4          1

         8          1

         3          1

         7          1

        10      86420

         9          1

 

9 rows selected.

2.2.2.2、执行查询

--查询1

--执行查询，发现执行计划走的是索引范围扫描。

LEO@orcl150> set autot traceonly explain stat

LEO@orcl150> select * from scott.test where object_id=10;

 

86420 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2317948335

 

-------------------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |     1 |    98 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     1 |    98 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST_01 |     1 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("OBJECT_ID"=10)

 

 

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

      14454  consistent gets

        905  physical reads

     110392  redo size

    9773775  bytes sent via SQL*Net to client

      63895  bytes received via SQL*Net from client

       5763  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      86420  rows processed

 

说明：实际上此时表中大部分行的object_id已被更新为10，走索引已然是不合理的.

--查询2，加全表扫描hint进行查询

LEO@orcl150> select /*+ full(test)*/ * from scott.test where object_id=10;

 

86420 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1357081020

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    98 |   345   (1)| 00:00:05 |

|*  1 |  TABLE ACCESS FULL| TEST |     1 |    98 |   345   (1)| 00:00:05 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("OBJECT_ID"=10)

 

 

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

       6908  consistent gets

          0  physical reads

          0  redo size

    4056770  bytes sent via SQL*Net to client

      63895  bytes received via SQL*Net from client

       5763  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      86420  rows processed

2.2.2.3、查sql_id，plan_hash_value

--查询如上两个select语句的sql_id,plan_hash_value。

LEO@orcl150> set autot off

LEO@orcl150> col sql_text format a100

LEO@orcl150> col sql_text for a50

LEO@orcl150> select sql_text,sql_id,plan_hash_value from v$sql where sql_text like 'select * from scott.test where object_id=10%'

 

SQL_TEXT                                           SQL_ID        PLAN_HASH_VALUE

-------------------------------------------------- ------------- ---------------

select * from scott.test where object_id=10        cpk9jsg2qt52r      2317948335

LEO@orcl150> col sql_text for a70

LEO@orcl150> select sql_text,sql_id,plan_hash_value from v$sql where sql_text like 'select /*+ full(test)*/ * from scott.test where object_id=10%'

 

SQL_TEXT                                                               SQL_ID        PLAN_HASH_VALUE

---------------------------------------------------------------------- ------------- ---------------

select /*+ full(test)*/ * from scott.test where object_id=10           g82ru63zvt2yb      1357081020

2.2.2.4、处理coe_xfr_sql_profile.sql

将coe_xfr_sql_profile.sql放在$ORACLE_HOME/rdbms/admin.

2.2.2.5、产生COE_XFR_SQL_PROFILE

说明：运用SQL_ID和PLAN_HASH_VALUE值，对如上两个SQL生成COE_XFR_SQL_PROFILE文件.

--SQL语句1：

[oracle@leo-oel150 admin]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 5 22:59:56 2023

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SYS@orcl150> @coe_xfr_sql_profile.sql cpk9jsg2qt52r 2317948335

 

Parameter 1:

SQL_ID (required)

 

 

 

PLAN_HASH_VALUE AVG_ET_SECS

--------------- -----------

     2317948335        .089

 

Parameter 2:

PLAN_HASH_VALUE (required)

 

 

Values passed to coe_xfr_sql_profile:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID         : "cpk9jsg2qt52r"

PLAN_HASH_VALUE: "2317948335"

 

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_cpk9jsg2qt52r_2317948335.sql

on TARGET system in order to create a custom SQL Profile

with plan 2317948335 linked to adjusted sql_text.

 

 

COE_XFR_SQL_PROFILE completed.

 

--SQL语句2：

SQL>@coe_xfr_sql_profile.sql g82ru63zvt2yb 1357081020

 

Parameter 1:

SQL_ID (required)

 

 

 

PLAN_HASH_VALUE AVG_ET_SECS

--------------- -----------

     1357081020        .045

 

Parameter 2:

PLAN_HASH_VALUE (required)

 

 

Values passed to coe_xfr_sql_profile:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID         : "g82ru63zvt2yb"

PLAN_HASH_VALUE: "1357081020"

 

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_g82ru63zvt2yb_1357081020.sql

on TARGET system in order to create a custom SQL Profile

with plan 1357081020 linked to adjusted sql_text.

 

 

COE_XFR_SQL_PROFILE completed.

2.2.2.6、修改SYS.SQLPROF_ATTR内容

说明：将coe_xfr_sql_profile_cpk9jsg2qt52r_2317948335.sql文件中的SYS.SQLPROF_ATTR部分更改为coe_xfr_sql_profile_g82ru63zvt2yb_1357081020.sql中的SYS.SQLPROF_ATTR部分。

2.2.2.6.1、原文件内容

---coe_xfr_sql_profile_cpk9jsg2qt52r_2317948335.sql中的SYS.SQLPROF_ATTR

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_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_ID"))]',

q'[END_OUTLINE_DATA]');

---coe_xfr_sql_profile_g82ru63zvt2yb_1357081020.sql中产生的SYS.SQLPROF_ATTR

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'[FULL(@"SEL$1" "TEST"@"SEL$1")]',

q'[END_OUTLINE_DATA]');

2.2.2.6.2、系统层面修改

[oracle@leo-oel150 admin]$ vi coe_xfr_sql_profile_cpk9jsg2qt52r_2317948335.sql

…… (替换为全表扫描的SQLPROF_ATTR)

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'[FULL(@"SEL$1" "TEST"@"SEL$1")]',

q'[END_OUTLINE_DATA]');

……

2.2.2.7、执行修改后的COE_XFR_SQL_PROFILE

--修改后执行替换过SYS.SQLPROF_ATTR的SQL，coe_xfr_sql_profile_cpk9jsg2qt52r_2317948335.sql

[oracle@leo-oel150 admin]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 5 23:16:07 2023

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SYS@orcl150> @coe_xfr_sql_profile_cpk9jsg2qt52r_2317948335.sql

SYS@orcl150> REM

SYS@orcl150> REM $Header: 215187.1 coe_xfr_sql_profile_cpk9jsg2qt52r_2317948335.sql 11.4.4.4 2023/03/05 carlos.sierra $

SYS@orcl150> REM

SYS@orcl150> REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.

SYS@orcl150> REM

SYS@orcl150> REM AUTHOR

SYS@orcl150> REM   carlos.sierra@oracle.com

SYS@orcl150> REM

SYS@orcl150> REM SCRIPT

SYS@orcl150> REM   coe_xfr_sql_profile_cpk9jsg2qt52r_2317948335.sql

SYS@orcl150> REM

SYS@orcl150> REM DESCRIPTION

SYS@orcl150> REM   This script is generated by coe_xfr_sql_profile.sql

SYS@orcl150> REM   It contains the SQL*Plus commands to create a custom

SYS@orcl150> REM   SQL Profile for SQL_ID cpk9jsg2qt52r based on plan hash

SYS@orcl150> REM   value 2317948335.

SYS@orcl150> REM   The custom SQL Profile to be created by this script

SYS@orcl150> REM   will affect plans for SQL commands with signature

SYS@orcl150> REM   matching the one for SQL Text below.

SYS@orcl150> REM   Review SQL Text and adjust accordingly.

SYS@orcl150> REM

SYS@orcl150> REM PARAMETERS

SYS@orcl150> REM   None.

SYS@orcl150> REM

SYS@orcl150> REM EXAMPLE

SYS@orcl150> REM   SQL> START coe_xfr_sql_profile_cpk9jsg2qt52r_2317948335.sql;

SYS@orcl150> REM

SYS@orcl150> REM NOTES

SYS@orcl150> REM   1. Should be run as SYSTEM or SYSDBA.

SYS@orcl150> REM   2. User must have CREATE ANY SQL PROFILE privilege.

SYS@orcl150> REM   3. SOURCE and TARGET systems can be the same or similar.

SYS@orcl150> REM   4. To drop this custom SQL Profile after it has been created:

SYS@orcl150> REM         EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_cpk9jsg2qt52r_2317948335');

SYS@orcl150> REM   5. Be aware that using DBMS_SQLTUNE requires a license

SYS@orcl150> REM         for the Oracle Tuning Pack.

SYS@orcl150> REM   6. If you modified a SQL putting Hints in order to produce a desired

SYS@orcl150> REM         Plan, you can remove the artifical Hints from SQL Text pieces below.

SYS@orcl150> REM         By doing so you can create a custom SQL Profile for the original

SYS@orcl150> REM         SQL but with the Plan captured from the modified SQL (with Hints).

SYS@orcl150> REM

SYS@orcl150> WHENEVER SQLERROR EXIT SQL.SQLCODE;

SYS@orcl150> REM

SYS@orcl150> VAR signature NUMBER;

SYS@orcl150> VAR signaturef NUMBER;

SYS@orcl150> REM

SYS@orcl150> 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 * from scott.test where object_id=10]');

 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'[FULL(@"SEL$1" "TEST"@"SEL$1")]',

 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_cpk9jsg2qt52r_2317948335',

 31  description => 'coe cpk9jsg2qt52r 2317948335 '||:signature||' '||:signaturef||'',

 32  category    => 'DEFAULT',

 33  validate    => TRUE,

 34  replace     => TRUE,

 35  force_match => FALSE /* 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.

 

SYS@orcl150> WHENEVER SQLERROR CONTINUE

SYS@orcl150> SET ECHO OFF;

 

            SIGNATURE

---------------------

 10910590721604799112

 

 

           SIGNATUREF

---------------------

 15966118871002195466

 

 

... manual custom SQL Profile has been created

 

 

COE_XFR_SQL_PROFILE_cpk9jsg2qt52r_2317948335 completed

2.2.2.8、查看最新SQL Profile

--查看产生的sql profile。

SYS@orcl150> set line 200

SYS@orcl150> r

  1  SELECT b.name, d.sql_text, extractvalue(value(h), '.') as hints

  2      FROM dba_sql_profiles d,

  3           SYS.SQLOBJ$DATA  a,

  4           SYS.SQLOBJ$ b,

  5           TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA),'/outline_data/hint'))) h

  6    where a.signature=b.signature

  7      and a.category=b.category

  8      and a.obj_type=b.obj_type

  9      and a.plan_id=b.plan_id

 10      and a.signature=d.signature

 11*     and d.name='coe_cpk9jsg2qt52r_2317948335'

 

NAME                           SQL_TEXT                                           HINTS

------------------------------ -------------------------------------------------- --------------------------------------------------

coe_cpk9jsg2qt52r_2317948335   select * from scott.test where object_id=10        BEGIN_OUTLINE_DATA

coe_cpk9jsg2qt52r_2317948335   select * from scott.test where object_id=10        IGNORE_OPTIM_EMBEDDED_HINTS

coe_cpk9jsg2qt52r_2317948335   select * from scott.test where object_id=10        OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

coe_cpk9jsg2qt52r_2317948335   select * from scott.test where object_id=10        DB_VERSION('11.2.0.4')

coe_cpk9jsg2qt52r_2317948335   select * from scott.test where object_id=10        ALL_ROWS

coe_cpk9jsg2qt52r_2317948335   select * from scott.test where object_id=10        OUTLINE_LEAF(@"SEL$1")

coe_cpk9jsg2qt52r_2317948335   select * from scott.test where object_id=10        FULL(@"SEL$1" "TEST"@"SEL$1")

coe_cpk9jsg2qt52r_2317948335   select * from scott.test where object_id=10        END_OUTLINE_DATA

 

8 rows selected.

2.2.2.9、执行计划验证

--验证SQL Profile是否生效

SYS@orcl150> set autot traceonly explain stat

SYS@orcl150> select * from scott.test where object_id=10;

 

86420 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1357081020

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      | 86404 |  8100K|   345   (1)| 00:00:05 |

|*  1 |  TABLE ACCESS FULL| TEST | 86404 |  8100K|   345   (1)| 00:00:05 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("OBJECT_ID"=10)

 

Note

-----

   - SQL profile "coe_cpk9jsg2qt52r_2317948335" used for this statement

 

 

Statistics

----------------------------------------------------------

         28  recursive calls

          0  db block gets

       6925  consistent gets

         87  physical reads

          0  redo size

    4056770  bytes sent via SQL*Net to client

      63895  bytes received via SQL*Net from client

       5763  SQL*Net roundtrips to/from client

          3  sorts (memory)

          0  sorts (disk)

      86420  rows processed

说明：如上所示，原语句在不加hint的情况下也走全表扫描。

注意事项:

A、该测试只是为了演示通过coe_xfr_sql_profile.sql手动实现加hint，实际上述语句的处理最佳方法是重新收集scott.test的统计信息.

B、当一条sql既有sql profile又有stored outline时，优化器优先选择stored outline.

C、force_match参数,TRUE:FORCE (match even when different literals in SQL)，FALSE:EXACT (similar to CURSOR_SHARING).

D、通过sql profile手动加hint的方法很简单，而为sql添加最合理的hint才是关键.

E、测试完后，可以通过exec dbms_sqltune.drop_sql_profile(name =>'coe_cpk9jsg2qt52r_3384190782' );删除该sql profile.

F、执行coe_xfr_sql_profile.sql脚本时需要用户对当前目录有生成文件的权限，建议当前目录是/tmp。

2.2.3、自动创建SQL Profile

2.2.3.1、相关概念

使用STA自动生成SQL Profile，STA会对语句进行分析，采用最优的优化策略，并给出优化后的执行计划，且可以按照STA给出的建议重写语句。但有时可能无法重写语句(比如在生产环境或语句在包中）。此时就可以利用sql profile，将优化策略存储在profile中，Oracle在构建SQL语句的执行计划时就不会使用已有的统计信息，而是使用profile的策略生成新的查询计划。

2.2.3.2、数据准备

SYS@orcl150> grant create any sql profile to leo;

 

Grant succeeded.

 

SYS@orcl150> grant drop any sql profile to leo;

 

Grant succeeded.

 

SYS@orcl150> grant alter any sql profile to leo;

 

Grant succeeded.

 

SYS@orcl150> conn leo/leo;

Connected.

LEO@orcl150> select * from v$version;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

LEO@orcl150> create table tb_leo_20230305_01 as select * from dba_objects;

 

Table created.

 

LEO@orcl150> create index tb_leo_20230305_01_idx on tb_leo_20230305_01(object_id);

 

Index created.

 

LEO@orcl150> exec dbms_stats.gather_table_stats('leo','TB_LEO_20230305_01',cascade=>true,degree=>4);

 

PL/SQL procedure successfully completed.

LEO@orcl150> set autot on

LEO@orcl150> select /*+no_index(tb_leo_20230305_01 tb_leo_20230305_01_idx)*/ count(*) from tb_leo_20230305_01 where object_id=100;

 

  COUNT(*)

----------

         1

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 65431150

 

-----------------------------------------------------------------------------------------

| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                    |     1 |     5 |   345   (1)| 00:00:05 |

|   1 |  SORT AGGREGATE    |                    |     1 |     5 |            |          |

|*  2 |   TABLE ACCESS FULL| TB_LEO_20230305_01 |     1 |     5 |   345   (1)| 00:00:05 |

-----------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("OBJECT_ID"=100)

 

 

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

       1236  consistent gets

       1234  physical reads

          0  redo size

        526  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

1         rows processed

LEO@orcl150> set autot off

LEO@orcl150> col sql_text for a100

LEO@orcl150> select v.sql_id,v.sql_text from v$sql v where v.sql_text like '%no_index(tb_leo_20230305_01%' and v.sql_text not like '%v$sql%';

 

SQL_ID        SQL_TEXT

------------- ----------------------------------------------------------------------------------------------------

bvx7dwxdhc2j5 EXPLAIN PLAN SET STATEMENT_ID='PLUS340381' FOR select /*+no_index(tb_leo_20230305_01 tb_leo_20230305

              _01_idx)*/ count(*) from tb_leo_20230305_01 where object_id=100

 

bkms28pu7749m select /*+no_index(tb_leo_20230305_01 tb_leo_20230305_01_idx)*/ count(*) from tb_leo_20230305_01 whe

              re object_id=100

2.2.3.3、创建优化任务

方法1：

LEO@orcl150> declare

  2    my_task_name varchar2(30);

  3    my_sqltext   clob;

  4  begin

  5    my_sqltext   := 'select /*+no_index(tb_leo_20230305_01 tb_leo_20230305_01_idx)*/ count(*) from tb_leo_20230305_01 where object_id=100';

  6    my_task_name := dbms_sqltune.create_tuning_task(sql_text    => my_sqltext,

  7                                                    user_name   => 'LEO',

  8                                                    scope       => 'COMPREHENSIVE',

  9                                                    time_limit  => 60,

 10                                                    task_name   => 'sql_profile_test',

 11                                                    description => 'Task to tune a query on a specified table');

 12    dbms_sqltune.execute_tuning_task(task_name => 'sql_profile_test');

 13  end;

 14  /

 

PL/SQL procedure successfully completed.

方法2：

使用sqlid来生成优化任务，如下：

LEO@orcl150> declare

  2    a_tuning_task varchar2(30);

  3  begin

  4    a_tuning_task := dbms_sqltune.create_tuning_task(sql_id    => 'bkms28pu7749m',

  5                                             task_name => 'sql_profile_test_sqlid');

  6    dbms_sqltune.execute_tuning_task(a_tuning_task);

  7  end;

  8  /

 

PL/SQL procedure successfully completed.

2.2.3.4、查看优化建议

LEO@orcl150> set autot off

LEO@orcl150> set long 10000

LEO@orcl150> set longchunksize 1000

LEO@orcl150> set linesize 100

LEO@orcl150> select dbms_sqltune.report_tuning_task('sql_profile_test') from dual;

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')

----------------------------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name   : sql_profile_test

Tuning Task Owner  : LEO

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 60

Completion Status  : COMPLETED

Started at         : 03/06/2023 10:24:30

Completed at       : 03/06/2023 10:24:30

 

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')

----------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------

Schema Name: LEO

SQL ID     : 3ghugnm7mr3v0

SQL Text   : select /*+no_index(tb_leo_20230305_01 tb_leo_20230305_01_idx)*/

             count(*) from tb_leo_20230305_01 where object_id=100

 

-------------------------------------------------------------------------------

FINDINGS SECTION (1 finding)

-------------------------------------------------------------------------------

 

1- SQL Profile Finding (see explain plans section below)

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')

----------------------------------------------------------------------------------------------------

--------------------------------------------------------

  A potentially better execution plan was found for this statement.

 

  Recommendation (estimated benefit: 99.83%)

  ------------------------------------------

  - Consider accepting the recommended SQL profile.

    execute dbms_sqltune.accept_sql_profile(task_name => 'sql_profile_test',

            task_owner => 'LEO', replace => TRUE);

 

  Validation results

  ------------------

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')

----------------------------------------------------------------------------------------------------

  The SQL profile was tested by executing both its plan and the original plan

  and measuring their respective execution statistics. A plan may have been

  only partially executed if the other could be run to completion in less time.

 

                           Original Plan  With SQL Profile  % Improved

                           -------------  ----------------  ----------

  Completion Status:            COMPLETE          COMPLETE

  Elapsed Time (s):             .003286           .000017      99.48 %

  CPU Time (s):                 .003286           .000017      99.48 %

  User I/O Time (s):            .000116                 0        100 %

  Buffer Gets:                     1238                 2      99.83 %

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')

----------------------------------------------------------------------------------------------------

  Physical Read Requests:            15                 0        100 %

  Physical Write Requests:            0                 0

  Physical Read Bytes:           376832                 0        100 %

  Physical Write Bytes:               0                 0

  Rows Processed:                     1                 1

  Fetches:                            1                 1

  Executions:                         1                 1

 

  Notes

  -----

  1. Statistics for the original plan were averaged over 10 executions.

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')

----------------------------------------------------------------------------------------------------

  2. Statistics for the SQL profile plan were averaged over 10 executions.

 

-------------------------------------------------------------------------------

EXPLAIN PLANS SECTION

-------------------------------------------------------------------------------

 

1- Original With Adjusted Cost

------------------------------

Plan hash value: 65431150

 

-----------------------------------------------------------------------------------------

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')

----------------------------------------------------------------------------------------------------

| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                    |     1 |     5 |   345   (1)| 00:00:05 |

|   1 |  SORT AGGREGATE    |                    |     1 |     5 |            |          |

|*  2 |   TABLE ACCESS FULL| TB_LEO_20230305_01 |     1 |     5 |   345   (1)| 00:00:05 |

-----------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("OBJECT_ID"=100)

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')

----------------------------------------------------------------------------------------------------

 

2- Using SQL Profile

--------------------

Plan hash value: 1195562460

 

--------------------------------------------------------------------------------------------

| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                        |     1 |     5 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |                        |     1 |     5 |            |          |

|*  2 |   INDEX RANGE SCAN| TB_LEO_20230305_01_IDX |     1 |     5 |     1   (0)| 00:00:01 |

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')

----------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("OBJECT_ID"=100)

 

-------------------------------------------------------------------------------

 

说明：可以看到优化器给出性能更高的执行计划。

2.2.3.5、接受profile

--决定接受该建议，使用最新执行计划，且不重写SQL语句.

LEO@orcl150> execute dbms_sqltune.accept_sql_profile(task_name => 'sql_profile_test',task_owner => 'LEO',replace => TRUE);

 

PL/SQL procedure successfully completed.

注意：task_name需与前面创建优化任务时的task_name对应.

说明：采用包DBMS_SQLTUNE的函数：ACCEPT_SQL_PROFILE，其中参数task_name即创建的优化建议任务的名称，可以是任意合法名称。该函数还有其他参数，下面是该函数原型：

DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (

   task_name    IN  VARCHAR2,

   object_id    IN  NUMBER   := NULL,

   name         IN  VARCHAR2 := NULL,

   description  IN  VARCHAR2 := NULL,

   category     IN  VARCHAR2 := NULL;

   task_owner   IN VARCHAR2  := NULL,

   replace      IN BOOLEAN   := FALSE,

   force_match  IN BOOLEAN   := FALSE)

RETURN VARCHAR2;

Description是profile的描述信息；task_owner是优化建议任务的所有者；replace为TRUE时，如果profile已经存在，就代替它；force_match为TURE时，表示与语句强制匹配，即强制使用绑定变量，和系统参数cursor_sharing设置为FORCE时类似。为FALSE时，与cursor_sharing设置为EXACT时类似，即完全匹配。

特别是category参数，可以通过设置该参数制定特定会话使用该profile。10g中每个会话都有一个新参数SQLTUNE_CATEGORY，默认值是DEFAULT。而在调用该函数时，如果没有指定此参数，其值也是DEFAULT，而如果给这个profile指定了一个其它的CATEGORY值，如FOR_TUNING，那么只有会话参数SQLTUNE_CATEGORY也为FOR_TUNING时，才会使用这个porfile。为什么说这个参数很有用呢？试想这样的环境：在一个生产系统上利用STA调优一条语句，STA已经给出了优化建议，但是又不敢贸然实施它给出的建议（毕竟它只是机器嘛，不能完全信任），你就可以创建一个有特殊CATEGORY的profile，然后在你自己的会话中制定SQLTUNE_CATEGORY为这个特殊的CATEGORY，那就既可以看优化建议的实际效果又不影响生产环境。

2.2.3.6、查看创建的profile

--通过视图DBA_SQL_PROFILES查看已经创建的profile。

LEO @orcl150> set autot off

LEO @orcl150> col SQL_TEXT for a35

LEO @orcl150> r

  1  SELECT e.task_name, b.name, d.sql_text, extractvalue(value(h), '.') as hints

  2      FROM dba_sql_profiles d,

  3          dba_advisor_tasks e,

  4           SYS.SQLOBJ$DATA  a,

  5           SYS.SQLOBJ$ b,

  6           TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA),'/outline_data/hint'))) h

  7    where a.signature=b.signature

  8      and a.category=b.category

  9      and a.obj_type=b.obj_type

 10      and a.plan_id=b.plan_id

 11      and a.signature=d.signature

 12     and d.task_id=e.task_id

 13      and d.name='SYS_SQLPROF_0186b6080db30001'

 14*

 

TASK_NAME         NAME                           SQL_TEXT                            HINTS

----------------- ------------------------------ ----------------------------------- -----------------------------------

sql_profile_test  SYS_SQLPROF_0186b6080db30001   select /*+no_index(tb_leo_2023030 OPTIMIZER_FEATURES_ENABLE(default)

                                                 5_01 tb_leo_20230305_01_idx)*/ co

                                                 unt(*) from tb_leo_20230305_01 wh

                                                 ere object_id=100

 

sql_profile_test  SYS_SQLPROF_0186b6080db30001   select /*+no_index(tb_leo_2023030 IGNORE_OPTIM_EMBEDDED_HINTS

                                                 5_01 tb_leo_20230305_01_idx)*/ co

                                                 unt(*) from tb_leo_20230305_01 wh

                                                 ere object_id=100

 

注意：SYS_SQLPROF_0186b6080db30001来自后文profile效果查看时显示的参数.

2.2.3.7、查看profile效果

2.2.3.7.1、sqlplus查看

LEO@orcl150> set autot on

LEO@orcl150> select /*+no_index(tb_leo_20230305_01 tb_leo_20230305_01_idx)*/ count(*) from tb_leo_20230305_01 where object_id=100;

 

  COUNT(*)

----------

         1

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1195562460

 

--------------------------------------------------------------------------------------------

| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                        |     1 |     5 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |                        |     1 |     5 |            |          |

|*  2 |   INDEX RANGE SCAN| TB_LEO_20230305_01_IDX |     1 |     5 |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("OBJECT_ID"=100)

 

Note

-----

   - SQL profile "SYS_SQLPROF_0186b6080db30001" used for this statement

 

 

Statistics

----------------------------------------------------------

          8  recursive calls

          0  db block gets

          7  consistent gets

          1  physical reads

          0  redo size

        526  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

说明：该语句采用profile的数据，执行计划中的附加信息表明该语句采用’SYS_SQLPROF_0186b6080db30001’ profile，而不是根据对象上的统计数据来生成的查询计划。

上述方法主要依赖sql tuning advisor，如果它无法生成想要的执行计划。还可以通过sql profile手动把hint加进去。复杂SQL的hint可以采用脚本coe_xfr_sql_profile.sql来产生原语句的outline data和加hint语句的outline data，然后替换对应的SYS.SQLPROF_ATTR，最后执行生成的sql。

2.2.3.7.2、PL/SQL查看

使用PLSQL DEVELOPER 14查看执行计划，如下所示：


参考文档：https://blog.51cto.com/lhrbest/3246884

固定执行计划
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论