标签：20230305 SQL LEO SYS orcl150 固定 sql oracle 执行
文档课题：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 [email protected]
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
标签：20230305,
SQL,
LEO,
SYS,
orcl150,
固定,
sql,
oracle,
执行
From： https://blog.51cto.com/u_12991611/6508330