How To Use SQL Profiles for Queries Using Different Literals Using the Force_Match Parameter of DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (Doc ID 1253696.1)
GOAL
目标
This document provides information on the following topics:
本文档提供有关以下主题的信息：
-
To create SQL Profiles for queries using literals where different literal values are passed for every execution.
创建使用文字的查询的SQL Profiles，其中每次执行都传递不同的文字值。
-
Use this SQL Profile for every execution of this SQL whatever the literal is changed to.
无论将字面值更改为什么，都将此SQL配置文件用于此SQL的每次执行。
Default behavior:
By default, if a SQL Profile is created for a sql containing literal values, then the profile would be generated solely for the query with whatever literals were used while running the tuning advisor. If the same SQL was executed with only a change in literals, then that profile would not be used for this query since the changing literal would make the SQL deemed to be a new query. Since profiles are identified by the SQL that they are recorded against, the profile would not be used for this “new” SQL.
默认行为：
默认情况下，如果为包含文字值的sql创建了一个SQL Profile，则将为查询生成单独的配置文件，并在运行优化顾问时使用任何文字。如果执行相同的SQL仅改变了文字，则该配置文件将不会用于此查询，因为更改的文字会使SQL视为新查询。由于概要文件由记录它们的SQL标识，因此该概要文件不会用于此“新” SQL。
SOLUTION
解决
Default Behavior
默认行为
Firstly, lets look at the default behavior of a SQL Profile.
首先，让我们看一下SQL Profile的默认行为。
Example:
Create a table, populate it with some values, add an index and gather statistics:
创建一个表，用一些值填充它，添加索引并收集统计信息：
SQL> create table test (n number );
Table created.
declare
begin
for i in 1 .. 10000
loop
insert into test values(i);
commit;
end loop;
end;
PL/SQL procedure successfully completed.
create index test_idx on test(n);
Index created.
analyze table test estimate statistics (OR use dbms_stats)
Table analyzed.
Run a test query against it with a literal predicate (n=1):
使用文字谓词（n = 1）对其执行测试查询：
select /*+ no_index(test test_idx) */ * from test where n=1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=13)
1 0 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=6 Card=1 Bytes=13)
Now, use SQL Tuning Advisor to create a profile for the query:
现在，使用SQL Tuning Advisor为查询创建一个配置文件：
SQL> DECLARE
2 my_task_name VARCHAR2(30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=1';
6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
7 sql_text=> my_sqltext,
8 user_name => 'SCOTT',
9 scope => 'COMPREHENSIVE',
10 time_limit => 60,
11 task_name => 'my_sql_tuning_task_2',
12 description => 'Task to tune a query on a specified table');
13 END;
14 /
PL/SQL procedure successfully completed.
SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2');
PL/SQL procedure successfully completed.
SQL> set long 2000
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_2') from DUAL;
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
--------------------------------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 84.03%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', replace => TRUE);
If we accept the profile:
如果我们接受这个profile
SQL> execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', replace => TRUE);
PL/SQL procedure successfully completed.
Then we now have a profile for that SQL statement.
然后，我们现在有了该SQL语句的配置文件。
If we execute the sql with various literals, the SQL Profile will only be used for the query with the specific literals used when it was created:
如果我们使用各种文字执行sql，则SQL配置文件将仅用于具有创建时使用的特定文字的查询：
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;
N
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 2882402178
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 3 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=1)
Note
-----
- SQL profile "SYS_SQLPROF_014af9c017890000" used for this statement
In this example the profile is used, but if we change the literal to a different value, then the profile would not be used and a different plan may be chosen (as in this case).
在此示例中，使用了概要文件，但是如果我们将文字更改为其他值，则将不使用概要文件，并且可以选择其他计划（在这种情况下）。
SQL> select /*+ no_index(test test_idx) */ * from test where n=2;
N
----------
2
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 6 (0) | 00:00:01 |
|* 1 | TABLE ACCESS FULL | TEST | 1 | 3 | 6 (0) | 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=2)
As you can see, there is now no message about a profile being used for this statement.
如您所见，现在没有任何消息说明此概要文件正在使用。
Using the
Force_Match
parameter of
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE
By default DBMS_SQLTUNE.ACCEPT_SQL_PROFILE executes with the force_match parameter set to false. When set to true, this is analogous to the matching algorithm used by the FORCE option of the cursor_sharing parameter in that it forces literals in the statement to be converted to binds and then the statement can be shared when different literals are supplied. It also causes SQL profiles to target all SQL statements which have the same text after normalizing all literal values into bind variables. (Note that if a combination of literal values and bind values is used in a SQL statement, no bind transformation occurs.)
默认情况下，执行DBMS_SQLTUNE.ACCEPT_SQL_PROFILE并将force_match参数设置为false。设置为true时，这类似于cursor_sharing参数的FORCE选项使用的匹配算法，因为它强制将语句中的文字转换为绑定，然后在提供不同的文字时可以共享该语句。在将所有文字值标准化为绑定变量之后，这还会导致SQL配置文件以所有具有相同文本的SQL语句为目标。（请注意，如果在SQL语句中使用文字值和绑定值的组合，则不会发生绑定转换。）
If we recreate the SQL profile with this option, it will be used whatever literals are supplied , since it has internally replaced the literals with binds.
如果我们使用此选项重新创建SQL配置文件，则无论提供了什么文字，都将使用它，因为它已在内部替换了带绑定的文字。
What follows is an example illustrating this behavior:
以下是说明此行为的示例：
SQL> execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', replace => TRUE, force_match=>true);
PL/SQL procedure successfully completed.
Now even if the literals are changed, the SQL profile gets used.
现在，即使文字被更改，也将使用SQL配置文件。
SQL> select /*+ no_index(test test_idx) */ * from test where n=10;
N
----------
10
Execution Plan
----------------------------------------------------------
Plan hash value: 2882402178
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0) | 00:00:01 |
|* 1 | INDEX RANGE SCAN | TEST_IDX | 1 | 3 | 1 (0) | 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=10)
Note
-----
- SQL profile "SYS_SQLPROF_014af9c167e84001" used for this statement
Now that force_match is set to true the SQL Profile is used.
现在，将force_match设置为true，将使用SQL Profile。
SQL profiles created with FORCE_MATCH enabled does not recognize the addition of a comment to the SQL statement. 在
启用FORCE_MATCH的情况下创建的SQL配置文件无法识别对SQL语句添加的注释。
If a comment is added to the SQL statement, then the SQL profile will not match to it.
如果在SQL语句中添加了注释，则SQL配置文件将与之不匹配。
To overcome the problem,
Patch 9488694
can be applied so that the comments are ignored and the SQL profiles will match the signature and get used.
为了解决该问题，可以应用
补丁9488694
，
以便忽略注释，并且SQL配置文件将与签名匹配并被使用。