Tuesday, June 22, 2021

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)

 In this Document

Goal
Solution
 Default Behavior
 Using the Force_Match parameter of DBMS_SQLTUNE.ACCEPT_SQL_PROFILE
References


APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

GOAL

This document provides information on the following topics:

  1. To create SQL Profiles for queries using literals where different literal values are passed for every execution.
  2. Use this SQL Profile for every execution of this SQL whatever the literal is changed to.


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.

SOLUTION

Default Behavior

Firstly, lets look at the default behavior of a 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):

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> 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:

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.

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> 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.)

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.

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

No comments:

Post a Comment

Database Options/Management Packs Usage Reporting for Oracle Databases 11.2 and later (Doc ID 1317265.1)

  Database Options/Management Packs Usage Report You can determine whether an option is currently in use in a database by running options_pa...