Using SQL Profiles to Tackle High Parse Time and CPU Consumption

The Challenge of Dynamic SQL with Literals   

The following example is taken from a PeopleSoft General Ledger system. The SQL was generated by the nVision reporting tool (some literal values have been obfuscated).

SELECT L4.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT) 
FROM PS_XX_SUM_XXXXX_VW A, PSTREESELECT10 L4, PSTREESELECT10 L2 
WHERE A.LEDGER='X_UKMGT' 
AND A.FISCAL_YEAR=2022 AND A.ACCOUNTING_PERIOD=1 
AND L4.SELECTOR_NUM=415 AND A.CHARTFIELD3=L4.RANGE_FROM_10 
AND L2.SELECTOR_NUM=416 AND A.ACCOUNT=L2.RANGE_FROM_10 
AND (A.DEPTID BETWEEN '10000' AND '18999' OR
A.DEPTID BETWEEN '20000' AND '29149' OR A.DEPTID='29156' OR
A.DEPTID='29158' OR A.DEPTID BETWEEN '29165' AND '29999' OR A.DEPTID
BETWEEN '30000' AND '39022' OR A.DEPTID BETWEEN '39023' AND '39999' OR
A.DEPTID BETWEEN '40000' AND '49999' OR A.DEPTID BETWEEN '50000' AND
'59999' OR A.DEPTID BETWEEN '60000' AND '69999' OR A.DEPTID BETWEEN
'70000' AND '79999' OR A.DEPTID BETWEEN '80000' AND '89999' OR
A.DEPTID='29150' OR A.DEPTID=' ') 
AND A.CHARTFIELD1='0120413' 
AND A.CURRENCY_CD='GBP' 
GROUP BY L4.TREE_NODE_NUM

Plan hash value: 1653134809

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                            |                    |       |       |    27 (100)|          |       |       |        |      |            |
|   1 |  PX COORDINATOR                                             |                    |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)                                       | :TQ10006           |     1 |    29 |    27  (63)| 00:00:01 |       |       |  Q1,06 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY                                            |                    |     1 |    29 |    27  (63)| 00:00:01 |       |       |  Q1,06 | PCWP |            |
|   4 |     PX RECEIVE                                              |                    |     1 |    29 |    27  (63)| 00:00:01 |       |       |  Q1,06 | PCWP |            |
|   5 |      PX SEND HASH                                           | :TQ10005           |     1 |    29 |    27  (63)| 00:00:01 |       |       |  Q1,05 | P->P | HASH       |
|   6 |       HASH GROUP BY                                         |                    |     1 |    29 |    27  (63)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
|   7 |        HASH JOIN                                            |                    |     1 |    29 |    27  (63)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
|   8 |         JOIN FILTER CREATE                                  | :BF0000            |     1 |    16 |    25  (68)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
|   9 |          PX RECEIVE                                         |                    |     1 |    16 |    25  (68)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
|  10 |           PX SEND HYBRID HASH                               | :TQ10003           |     1 |    16 |    25  (68)| 00:00:01 |       |       |  Q1,03 | P->P | HYBRID HASH|
|  11 |            STATISTICS COLLECTOR                             |                    |       |       |            |          |       |       |  Q1,03 | PCWC |            |
|  12 |             VIEW                                            | VW_GBC_10          |     1 |    16 |    25  (68)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  13 |              HASH GROUP BY                                  |                    |     1 |    67 |    25  (68)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  14 |               PX RECEIVE                                    |                    |     1 |    67 |    25  (68)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  15 |                PX SEND HASH                                 | :TQ10002           |     1 |    67 |    25  (68)| 00:00:01 |       |       |  Q1,02 | P->P | HASH       |
|  16 |                 HASH GROUP BY                               |                    |     1 |    67 |    25  (68)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  17 |                  HASH JOIN                                  |                    |    60 |  4020 |    24  (67)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  18 |                   JOIN FILTER CREATE                        | :BF0001            |    60 |  3120 |    22  (73)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  19 |                    PX RECEIVE                               |                    |    60 |  3120 |    22  (73)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  20 |                     PX SEND HYBRID HASH                     | :TQ10000           |    60 |  3120 |    22  (73)| 00:00:01 |       |       |  Q1,00 | P->P | HYBRID HASH|
|  21 |                      STATISTICS COLLECTOR                   |                    |       |       |            |          |       |       |  Q1,00 | PCWC |            |
|  22 |                       PX BLOCK ITERATOR                     |                    |    60 |  3120 |    22  (73)| 00:00:01 |    29 |    29 |  Q1,00 | PCWC |            |
|  23 |                        MAT_VIEW REWRITE ACCESS INMEMORY FULL| PS_XX_SUM_XXXXX_MV |    60 |  3120 |    22  (73)| 00:00:01 |    29 |    29 |  Q1,00 | PCWP |            |
|  24 |                   PX RECEIVE                                |                    |   306 |  4590 |     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  25 |                    PX SEND HYBRID HASH                      | :TQ10001           |   306 |  4590 |     2   (0)| 00:00:01 |       |       |  Q1,01 | P->P | HYBRID HASH|
|  26 |                     JOIN FILTER USE                         | :BF0001            |   306 |  4590 |     2   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|  27 |                      PX BLOCK ITERATOR                      |                    |   306 |  4590 |     2   (0)| 00:00:01 |   416 |   416 |  Q1,01 | PCWC |            |
|  28 |                       TABLE ACCESS STORAGE FULL             | PSTREESELECT10     |   306 |  4590 |     2   (0)| 00:00:01 |   416 |   416 |  Q1,01 | PCWP |            |
|  29 |         PX RECEIVE                                          |                    |   202 |  2626 |     2   (0)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
|  30 |          PX SEND HYBRID HASH                                | :TQ10004           |   202 |  2626 |     2   (0)| 00:00:01 |       |       |  Q1,04 | P->P | HYBRID HASH|
|  31 |           JOIN FILTER USE                                   | :BF0000            |   202 |  2626 |     2   (0)| 00:00:01 |       |       |  Q1,04 | PCWP |            |
|  32 |            PX BLOCK ITERATOR                                |                    |   202 |  2626 |     2   (0)| 00:00:01 |   415 |   415 |  Q1,04 | PCWC |            |
|  33 |             TABLE ACCESS STORAGE FULL                       | PSTREESELECT10     |   202 |  2626 |     2   (0)| 00:00:01 |   415 |   415 |  Q1,04 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$6240F0FF
  12 - SEL$B80655F7 / VW_GBC_10@SEL$9C8D6CC0
  13 - SEL$B80655F7
  23 - SEL$B80655F7 / PS_XX_SUM_XXXXX_MV@SEL$CAD4EEF6
  28 - SEL$B80655F7 / L2@SEL$1
  33 - SEL$6240F0FF / L4@SEL$1
… 

In my example, ASH sampled 276 different SQL IDs.  Each one was only executed once.  There may have been more statements, but ASH only persists one sample every 10s. Cumulatively, they consumed 2843 seconds of DB time in SQL hard parse.

                                                                                                                                          Plan
                                                                                          SQL Plan        Force Matching    SQL   Plan   Parse
 # OPRID        RUNCNTLID              ACTION                           SQL_ID          Hash Value             Signature    IDs  Execs    Secs Table Name
-- ------------ ---------------------- -------------------------------- ------------- ------------ --------------------- ------ ------ ------- ------------------ 
 1 NVISION      NVS_RPTBOOK_99         PI=9984520:UKGL999I:12345        01g5hvs91k4hn   1653134809   1995330195085985689    276    276    2843 PS_XX_SUM_XXXXX_MV 
… 

This is one of at least 276 different SQL statements that all have the same force-matching signature. The statements are essentially the same but differ in some of their literal values. That means that the database has to treat each one as a different SQL statement that must be fully parsed separately. 

SQL Parse involves checking the statement is syntactically correct, and that the user has permission to access the objects, then during the SQL optimization stage the optimizer decides how to execute the statement before it moves to row source generation. 

If the statement has been parsed previously and is still in the shared pool, Oracle can skip the optimization and row source generation stages. This is often called soft parse. 

SQL Optimization 

During the optimization stage, the optimizer calculates the 'cost' of different possible execution plans. Depending upon the SQL, the optimizer considers different table join orders, different table join methods, and different SQL transformations. The optimizer cost is an estimation of the time that it will take to execute a particular plan. The unit of cost is roughly equivalent to the duration of a single block read. More expensive plans are abandoned as they become more expensive than the cheapest known plan so far. Thus the 'cost-based' optimizer produces the cheapest plan. However, the process of optimization consumes time and CPU. 

If I write SQL that is executed many times with bind variables rather than literals, then I should avoid some hard parses and the associated CPU consumption. Oracle has always recommended using bind variables rather than literals to improve performance as well as protect against SQL injection. However, there are many applications that still use literals, particularly in dynamically generated SQL. Every statement has to be hard parsed, and the cumulative CPU consumption can start to become significant. PeopleSoft is one such application that does this in some areas of the product, but it is by no means an isolated example. 

Oracle produced a feature called Cursor Sharing. Literals in statements are automatically converted to bind variables. It can be very effective. It does reduce SQL parse, but can sometimes also produce undesirable side effects where the execution plan may not change as the bind variable values change. 

Hints 

Hints are directives to the optimizer. They tell it to do something or more generally not to do something else. If I were to add some optimizer hints to a statement that will produce the same, or a similar, execution plan, then the optimizer should do less work, consume less CPU, and less time coming to the same or similar conclusion. 

 For example, if I add a LEADING hint to force the optimizer to start with a particular object, that will reduce the number of join orders to be considered. 

• A two-table query has 2 possible join orders; a LEADING hint will reduce it to 1. 
• A three-table query has 6 possible join orders; a LEADING hint on a single table will reduce it to 2. 

Often, it is not possible to add hints directly to the code in the application because it is all dynamically generated inside a package, or it may not be desirable to alter third-party code. In my example, the SQL was generated by compiled code within the nVision reporting tool that I cannot alter. I can't use a SQL Patch because I would need a patch for every SQL_ID and I can't predicate the SQL_IDs. Instead, I can create a force-matching SQL profile that will match every statement with the same force-matching signature. 

N.B. SQL Profiles require the SQL Tuning pack licence. 

Example SQL Profile 

I don't have to use the full outline of hints from the execution plan, I have chosen to apply just a few. 

• LEADING(L2): I want the query to start with the dimension table PSTREESELECT10. This will result in a change to the execution plan 
• REWRITE: PS_XX_SUM_XXXXX_MV is a materialized view built on the view PS_XX_SUM_XXXXX_VW of an underlying summary ledger. Rewriting the SQL to use the materialized view is a cost-based decision. Oracle usually decides to rewrite it to use the materialized view, but I want to ensure that this always happens with this hint. 
• NO_PARALLEL: This query selects only a single accounting period, so it is only scanning a single partition, therefore I don't want to invoke a parallel query. 
• PX_JOIN_FILTER(PS_XX_SUM_XXXXX_MV@SEL$CAD4EEF6): The dimension table is equijoined to the fact table. Therefore, it is a good candidate for using a Bloom filter on the look-up fact table. This doesn't always happen naturally on this statement. I have had to use the query block name taken from the execution plan of the rewritten statement.  The query block name is stable, it is a hash value based on the object name and the operation.
  

The profile is then created with DBMS_SQLTUNE.IMPORT_SQL_PROFILE.

set serveroutput on
DECLARE
  l_sql_text CLOB;
  l_signature NUMBER;
  h       SYS.SQLPROF_ATTR;
…
BEGIN
…
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[NO_PARALLEL]',
q'[LEADING(L2)]',
q'[PX_JOIN_FILTER(PS_XX_SUM_XXXXX_MV@SEL$CAD4EEF6)]',
q'[REWRITE]',
q'[END_OUTLINE_DATA]');

l_signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(l_sql_text);

DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text    => l_sql_text,
profile     => h,
name        => 'NVS_UKGL999I_FUNC_ACEXP1',
category    => 'DEFAULT',
validate    => TRUE,
replace     => TRUE,
force_match => TRUE);
…
END;
/

This is the execution plan with the SQL Profile. The note confirms that a SQL profile was used. The hint report shows the hints from the SQL Profile.  

Note that the SELECTOR_NUM and CHARTFIELD1 predicates have changed.

SELECT L4.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT) 
FROM PS_XX_SUM_XXXXX_VW A, PSTREESELECT10 L4, PSTREESELECT10 L2 
WHERE A.LEDGER='X_UKMGT' 
AND A.FISCAL_YEAR=2023 AND A.ACCOUNTING_PERIOD=1 
AND L4.SELECTOR_NUM=433 AND A.CHARTFIELD3=L4.RANGE_FROM_10 
AND L2.SELECTOR_NUM=434 AND A.ACCOUNT=L2.RANGE_FROM_10 
AND (A.DEPTID BETWEEN '10000' AND '18999' OR
A.DEPTID BETWEEN '20000' AND '29149' OR A.DEPTID='29156' OR
A.DEPTID='29158' OR A.DEPTID BETWEEN '29165' AND '29999' OR A.DEPTID
BETWEEN '30000' AND '39022' OR A.DEPTID BETWEEN '39023' AND '39999' OR
A.DEPTID BETWEEN '40000' AND '49999' OR A.DEPTID BETWEEN '50000' AND
'59999' OR A.DEPTID BETWEEN '60000' AND '69999' OR A.DEPTID BETWEEN
'70000' AND '79999' OR A.DEPTID BETWEEN '80000' AND '89999' OR
A.DEPTID='29150' OR A.DEPTID=' ') 
AND A.CHARTFIELD1='0051001' 
AND A.CURRENCY_CD='GBP' 
GROUP BY L4.TREE_NODE_NUM

Plan hash value: 3033847137

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                    |       |       |   214 (100)|          |       |       |
|   1 |  SORT GROUP BY                             |                    |     5 |   400 |   214  (62)| 00:00:01 |       |       |
|   2 |   HASH JOIN                                |                    |  2347 |   183K|   213  (62)| 00:00:01 |       |       |
|   3 |    HASH JOIN                               |                    |  2347 |   153K|   210  (63)| 00:00:01 |       |       |
|   4 |     JOIN FILTER CREATE                     | :BF0000            |   306 |  4590 |     3   (0)| 00:00:01 |       |       |
|   5 |      PARTITION RANGE SINGLE                |                    |   306 |  4590 |     3   (0)| 00:00:01 |   434 |   434 |
|   6 |       TABLE ACCESS STORAGE FULL            | PSTREESELECT10     |   306 |  4590 |     3   (0)| 00:00:01 |   434 |   434 |
|   7 |     JOIN FILTER USE                        | :BF0000            | 26468 |  1344K|   206  (64)| 00:00:01 |       |       |
|   8 |      PARTITION RANGE SINGLE                |                    | 26468 |  1344K|   206  (64)| 00:00:01 |    42 |    42 |
|   9 |       MAT_VIEW REWRITE ACCESS INMEMORY FULL| PS_XX_SUM_XXXXX_MV | 26468 |  1344K|   206  (64)| 00:00:01 |    42 |    42 |
|  10 |    PARTITION RANGE SINGLE                  |                    |   202 |  2626 |     3   (0)| 00:00:01 |   433 |   433 |
|  11 |     TABLE ACCESS STORAGE FULL              | PSTREESELECT10     |   202 |  2626 |     3   (0)| 00:00:01 |   433 |   433 |
---------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$38F8C49D
   6 - SEL$38F8C49D / L2@SEL$1
   9 - SEL$38F8C49D / PS_XX_SUM_XXXXX_MV@SEL$CAD4EEF6
  11 - SEL$38F8C49D / L4@SEL$1

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4 (U - Unused (1))
---------------------------------------------------------------------------

   0 -  STATEMENT
         U -  NO_PARALLEL
…
   1 -  SEL$38F8C49D
           -  LEADING(L2)
           -  REWRITE

   9 -  SEL$38F8C49D / PS_XX_SUM_XXXXX_MV@SEL$CAD4EEF6
           -  PX_JOIN_FILTER(PS_XX_SUM_XXXXX_MV@SEL$CAD4EEF6)

Note
-----
…
   - SQL profile "NVS_UKGL999I_FUNC_ACEXP1" used for this statement

• The new execution plan does indeed start with the dimension table 
• The query was rewritten to use the materialized view 
• A Bloom filter was used on the materialized view that is now the fact table
• The NO_PARALLEL hint wasn't used because Oracle chose not to parallelise this statement anyway.

                                                                                 Plan
                                                                                          SQL Plan        Force Matching    SQL   Plan   Parse
 # OPRID        RUNCNTLID              ACTION                           SQL_ID          Hash Value             Signature    IDs  Execs    Secs Table Name
-- ------------ ---------------------- -------------------------------- ------------- ------------ --------------------- ------ ------ ------- ------------------ 
…
 1 NVISION      NVS_RPTBOOK_99         PI=9984933:UKGL278I:12345        03nwc4yy1r1r7   3033847137   1995330195085985689    138    138    1428 PS_XX_SUM_XXXXX_MV

Now just 1428s is spent on parse time. We only found 138 SQL IDs, but that is just because there are fewer ASH samples because it is taking less time. 

In this case, adding these hints with a SQL Profile has halved the time spent parsing this set of SQL statements.

Alter SQL Profiles from Exact to Force Matching

You can use DBMS_SQLTUNE.ALTER_SQL_PROFILE to change the status, name, description, or category of a SQL profile, but you can't alter it from exact to force matching.  Instead, you would have to recreate it.  That is easy if you have the script that you used to create it in the first place.  There is another way.

Oracle support note How to Move SQL Profiles from One Database to Another (Including to Higher Versions) (Doc ID 457531.1) describes a process to export SQL profiles to a staging table that can be imported into another database.  This provides an opportunity to alter a profile by updating the data in the staging table.  There are two columns in the staging table that have to be updated.

• SQLFLAGS must be updated from 0 (indicating an exact match profile) to 1 (indicating a force match profile)
• SIGNATURE must be recalculated as a force matching signature using DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE.

Demonstration

I am going to create a small table with a unique index.  

CREATE TABLE t (a not null, b) AS 
SELECT rownum, ceil(sqrt(rownum)) FROM dual connect by level <= 100;
create unique index t_idx on t(a);
exec dbms_stats.gather_table_stats(user,'T');

ttitle off
select * from dba_sql_profiles where name like 'my%sql_profile%';
explain plan for SELECT * FROM t WHERE a = 42;
ttitle 'Default Execution plan without profiles (index scan)'
select * from table(dbms_xplan.display(null,null,'ADVANCED +ADAPTIVE -PROJECTION'));

Without any SQL profiles, when I query by the unique key I get a unique index scan.

Plan hash value: 2929955852

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     6 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |     6 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T_IDX |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Now I am going to create two SQL profiles.  I have deliberately put the same SQL text into both SQL Profiles.

• my_sql_profile is exact matching
• my_sql_profile_force is force matching.

DECLARE
signature INTEGER;
sql_txt CLOB;
h       SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
SELECT * FROM t WHERE a = 54
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[FULL(@"SEL$1" "T"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text    => sql_txt,
profile     => h,
name        => 'my_sql_profile',
category    => 'DEFAULT',
validate    => TRUE,
replace     => TRUE,
force_match => FALSE 
);
END;
/

DECLARE
signature INTEGER;
sql_txt CLOB;
h       SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
SELECT * FROM t WHERE a = 54
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[FULL(@"SEL$1" "T"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text    => sql_txt,
profile     => h,
name        => 'my_sql_profile_force',
category    => 'DEFAULT',
validate    => TRUE,
replace     => TRUE,
force_match => TRUE 
);
END;
/
ttitle off
select * from dba_sql_profiles where name like 'my%sql_profile%';

NAME                           CATEGORY               SIGNATURE SQL_TEXT                                                                         CREATED
------------------------------ ---------- --------------------- -------------------------------------------------------------------------------- ------------------------------
LAST_MODIFIED                  DESCRIPTION          TYPE    STATUS   FOR    TASK_ID TASK_EXEC_NAME       TASK_OBJ_ID TASK_FND_ID TASK_REC_ID TASK_CON_DBID
------------------------------ -------------------- ------- -------- --- ---------- -------------------- ----------- ----------- ----------- -------------
my_sql_profile                 DEFAULT      9394869341287877934                                                                                  31-JUL-21 10.47.34.243454
                                                                SELECT * FROM t WHERE a = 54
31-JUL-21 10.47.34.000000                           MANUAL  ENABLED  NO

my_sql_profile_force           DEFAULT     11431056000319719221                                                                                  31-JUL-21 10.47.34.502721
                                                                SELECT * FROM t WHERE a = 54
31-JUL-21 10.47.34.000000                           MANUAL  ENABLED  YES

The force match profile works if the literal value is different from that in the profiles.

explain plan for SELECT * FROM t WHERE a = 42;
select * from table(dbms_xplan.display(null,null,'ADVANCED +ADAPTIVE -PROJECTION'));

Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     6 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     6 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"=42)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------

   0 -  STATEMENT
           -  IGNORE_OPTIM_EMBEDDED_HINTS

   1 -  SEL$1 / T@SEL$1
           -  FULL(@"SEL$1" "T"@"SEL$1")

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

The exact match profile takes precedence of the force match profile.

explain plan for SELECT * FROM t WHERE a = 54;
select * from table(dbms_xplan.display(null,null,'ADVANCED +ADAPTIVE -PROJECTION'));

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     6 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     6 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"=54)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------

   0 -  STATEMENT
           -  IGNORE_OPTIM_EMBEDDED_HINTS

   1 -  SEL$1 / T@SEL$1
           -  FULL(@"SEL$1" "T"@"SEL$1")

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

I am now going to follow the process to export the SQL Profiles to a staging table, and subsequently reimport them.

exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE',schema_name=>user);
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'my_sql_profile');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'my_sql_profile_force');

There is a row in the staging table for each profile and you can see the differences between them.

select signature, sql_handle, obj_name, obj_type, sql_text, sqlflags from STAGE;

            SIGNATURE SQL_HANDLE                     OBJ_NAME
--------------------- ------------------------------ ---------------------------------------------------------------------
OBJ_TYPE                       SQL_TEXT                                                                           SQLFLAGS
------------------------------ -------------------------------------------------------------------------------- ----------
  9394869341287877934 SQL_826147e3c6ac0d2e           my_sql_profile
SQL_PROFILE                                                                                                              0
                               SELECT * FROM t WHERE a = 54

 11431056000319719221 SQL_9ea344de32a78735           my_sql_profile_force
SQL_PROFILE                                                                                                              1
                               SELECT * FROM t WHERE a = 54

I will update the staging table using this PL/SQL loop (because SQL doesn't recognise TRUE as a boolean constant).

DECLARE
  l_sig INTEGER;
BEGIN
  FOR i IN (
    SELECT rowid, stage.* FROM stage WHERE sqlflags = 0 FOR UPDATE
  ) LOOP
    l_sig := dbms_sqltune.sqltext_to_signature(i.sql_text,TRUE);
    UPDATE stage
    SET    signature = l_sig
    ,      sqlflags = 1
    WHERE  sqlflags = 0
    AND    rowid = i.rowid;
  END LOOP;
END;
/

And now the profiles look the same.

select signature, sql_handle, obj_name, obj_type, sql_text, sqlflags from STAGE;

            SIGNATURE SQL_HANDLE                     OBJ_NAME
--------------------- ------------------------------ ---------------------------------------------------------------------
OBJ_TYPE                       SQL_TEXT                                                                           SQLFLAGS
------------------------------ -------------------------------------------------------------------------------- ----------
 11431056000319719221 SQL_826147e3c6ac0d2e           my_sql_profile
SQL_PROFILE                                                                                                              1
                               SELECT * FROM t WHERE a = 54

 11431056000319719221 SQL_9ea344de32a78735           my_sql_profile_force
SQL_PROFILE                                                                                                              1
                               SELECT * FROM t WHERE a = 54

But I can't just reimport my_sql_profile from the staging replacing the one in the database because I will get ORA-13841: SQL profile named my_sql_profile already exists for a different signature/category pair. To avoid this error I must either drop the profile or rename it.  

I am going to rename the existing exact matching profile, and also disable it and move it to another category to stop it from matching my statement in preference to the force matching profile (see previous post Clashing SQL Profiles - Exact Matching Profiles Take Precedence Over Force Matching Profiles), and thus I can go back to it later if needed.

I will drop my example force matching profile.  I no longer need that.

Then, I can reimport the profile from the staging table.

exec dbms_sqltune.alter_sql_profile(name=>'my_sql_profile', attribute_name=>'NAME',value=>'my_old_sql_profile');
exec dbms_sqltune.alter_sql_profile(name=>'my_old_sql_profile', attribute_name=>'CATEGORY',value=>'DO_NOT_USE');
exec dbms_sqltune.alter_sql_profile(name=>'my_old_sql_profile', attribute_name=>'STATUS',value=>'DISABLED');
exec dbms_sqltune.drop_sql_profile('my_sql_profile_force',TRUE);
EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(profile_name => 'my_sql_profile', replace => TRUE, staging_table_name => 'STAGE');

I can see in the SQL profile table that my SQL profile is now force matching, and it has a different signature to the old one that is exact matching.

ttitle off
select * from dba_sql_profiles where name like 'my%sql_profile%';

NAME                           CATEGORY               SIGNATURE SQL_TEXT                                                                         CREATED
------------------------------ ---------- --------------------- -------------------------------------------------------------------------------- ------------------------------
LAST_MODIFIED                  DESCRIPTION          TYPE    STATUS   FOR    TASK_ID TASK_EXEC_NAME       TASK_OBJ_ID TASK_FND_ID TASK_REC_ID TASK_CON_DBID
------------------------------ -------------------- ------- -------- --- ---------- -------------------- ----------- ----------- ----------- -------------
my_old_sql_profile             DO_NOT_USE   9394869341287877934                                                                                  31-JUL-21 10.54.58.694037
                                                                SELECT * FROM t WHERE a = 54
31-JUL-21 10.55.00.000000                           MANUAL  DISABLED NO

my_sql_profile                 DEFAULT     11431056000319719221                                                                                  31-JUL-21 10.55.01.005377
                                                                SELECT * FROM t WHERE a = 54
31-JUL-21 10.55.01.000000                           MANUAL  ENABLED  YES

Both my queries now match the new force matching version of the profile.

explain plan for SELECT * FROM t WHERE a = 42;
ttitle 'Execution plan with force match profile (full scan)'
select * from table(dbms_xplan.display(null,null,'ADVANCED +ADAPTIVE -PROJECTION'));

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     6 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     6 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"=42)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------

   0 -  STATEMENT
           -  IGNORE_OPTIM_EMBEDDED_HINTS

   1 -  SEL$1 / T@SEL$1
           -  FULL(@"SEL$1" "T"@"SEL$1")

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

explain plan for SELECT * FROM t WHERE a = 54;
select * from table(dbms_xplan.display(null,null,'ADVANCED +ADAPTIVE -PROJECTION'));

Plan hash value: 1601196873

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

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

   1 - filter("A"=54)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------

   0 -  STATEMENT
           -  IGNORE_OPTIM_EMBEDDED_HINTS

   1 -  SEL$1 / T@SEL$1
           -  FULL(@"SEL$1" "T"@"SEL$1")

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

The script used for this demonstration is available on GitHub

Detecting Clashing SQL Profiles

In my last post, I discussed the possible undesirable consequences of force and exact matching SQL profiles on statements with the same force matching signature.  The question is how do you detect such profiles?

I have created three profiles on very similar SQL statements that only differ in the literal value of a predicate.  One of them is force matching, the others are exact matching.  The signature reported by DBA_SQL_PROFILES is the force matching signature for force matching profiles, and the exact matching signature for exact matching profiles.

select * from dba_sql_profiles;

NAME                           CATEGORY               SIGNATURE SQL_TEXT                                           CREATED
------------------------------ ---------- --------------------- -------------------------------------------------- ------------------------------
LAST_MODIFIED                  DESCRIPTION          TYPE    STATUS   FOR    TASK_ID TASK_EXEC_NAME       TASK_OBJ_ID TASK_FND_ID TASK_REC_ID TASK_CON_DBID
------------------------------ -------------------- ------- -------- --- ---------- -------------------- ----------- ----------- ----------- -------------
my_sql_profile_force           DEFAULT     11431056000319719221                                                    16:09:33 01/08/2021
                                                                SELECT * FROM t WHERE a = 54
16:09:33 01/08/2021                                 MANUAL  ENABLED  YES

my_sql_profile_24              DEFAULT     12140764948557749245                                                    16:09:33 01/08/2021
                                                                SELECT * FROM t
                                                                WHERE a = 24
16:09:33 01/08/2021                                 MANUAL  ENABLED  NO

my_sql_profile_42              DEFAULT     14843900676141266266                                                    16:09:33 01/08/2021
                                                                SELECT * FROM t WHERE a = 42
16:09:33 01/08/2021                                 MANUAL  ENABLED  NO

In order to be able to compare the profiles, I need to calculate the force matching signature for the exact matching profiles using DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE.  I can't use the Boolean constant TRUE parameter in SQL.  Instead, I have used a PL/SQL function in a with clause.

REM dup_sql_profiles1.sql 
WITH function sig(p_sql_text CLOB, p_number INTEGER) RETURN NUMBER IS
 l_sig NUMBER;
BEGIN
 IF p_number > 0 THEN 
  l_sig := dbms_sqltune.sqltext_to_signature(p_sql_text,TRUE);
 ELSIF p_number = 0 THEN 
  l_sig := dbms_sqltune.sqltext_to_signature(p_sql_text,FALSE);
 END IF;
 RETURN l_sig;
END;
x as (
select CASE WHEN force_matching = 'NO'  THEN signature ELSE sig(sql_text, 0) END exact_sig
,      CASE WHEN force_matching = 'YES' THEN signature ELSE sig(sql_text, 1) END force_sig
,      p.*
from   dba_sql_profiles p
where  (status = 'ENABLED' or force_matching = 'NO')
), y as (
select x.*
, row_number() over (partition by category, force_sig order by force_matching desc, exact_sig nulls first) profile#
, count(*) over (partition by category, force_sig) num_profiles
from x
)
select profile#, num_profiles, force_sig, exact_sig, name, created, category, status, force_matching, sql_text
from y
where num_profiles > 1
order by force_sig, force_matching desc, exact_sig
/

 We can see these three profiles are grouped together.  The force matching signature calculated on the exact matching profiles is the same as the signature on the force matching profile.  Now I can start to make some decisions about whether I should retain the exact matching profiles or remove them and just use the force matching profile.

Prof   Num        Force Matching        Exact Matching
   # Profs             Signature             Signature NAME                           CREATED                      CATEGORY             STATUS   FOR
---- ----- --------------------- --------------------- ------------------------------ ---------------------------- -------------------- -------- ---
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------
   1     3  11431056000319719221                       my_sql_profile_force           16:35:36 01/08/2021          DEFAULT              ENABLED  YES

SELECT * FROM t WHERE a = 54

   2     3                        12140764948557749245 my_sql_profile_24              16:35:36 01/08/2021          DEFAULT              ENABLED  NO

SELECT * FROM t
WHERE a = 24

   3     3                        14843900676141266266 my_sql_profile_42              16:35:36 01/08/2021          DEFAULT              ENABLED  NO

SELECT * FROM t WHERE a = 42

The SQL statements in this example are absurdly simple.  In real life that is rarely the case.  Sometimes it can be a struggle to see where two complex statements differ.

In the next query, I compare enabled force matching SQL profiles to any exact matching profiles in the same category with the same force matching signature.  The full query is on GitHub.

REM dup_sql_profiles2.sql
WITH function sig(p_sql_text CLOB, p_number INTEGER) RETURN NUMBER IS
…
END sig;
function norm(p_queryin CLOB) RETURN CLOB IS
…
END norm;
function str_diff(p_str1 CLOB, p_str2 CLOB) RETURN NUMBER IS
…
END str_diff;
x as (
select CASE WHEN force_matching = 'NO'  THEN signature ELSE sig(sql_text, 0) END exact_sig
,      CASE WHEN force_matching = 'YES' THEN signature ELSE sig(sql_text, 1) END force_sig
,      p.*
from   dba_sql_profiles p
), y as (
select f.force_matching, f.force_sig, f.name force_name, f.created force_created, f.status force_status
,      e.force_matching exact_matching, e.exact_sig, e.name exact_name
,      e.created exact_created, e.status exact_status, e.category
,      norm(e.sql_text) esql_text, norm(f.sql_text) fsql_text
from   x e
,      x f
where  f.force_matching = 'YES'
and    e.force_matching = 'NO'
and    e.force_sig = f.force_sig
and    e.category = f.category
and    e.name != f.name
and    f.status = 'ENABLED'
), z as (
select y.*
,      str_diff(fsql_Text, esql_text) diff_len
from y
)
select force_matching, force_Sig, force_name, force_created, force_status
,      exact_matching, exact_sig, exact_name, exact_Created, exact_status
, substr(fsql_text,1,diff_len) common_text
, substr(fsql_text,diff_len+1) fdiff_text, substr(esql_text,diff_len+1) ediff_text
from z
order by force_sig
/

I have shown the common part of both statements, from the start to the first difference, and then also how the rest of each statement continues.

It is not enough to simply compare two statements character by character.  Both the force and exact matching signatures are "calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings".  However, neither the normalised SQL, nor the normalisation mechanism is exposed by Oracle.  Therefore, in this query, I have included my own rudimentary normalisation function (based on an idea from AskTOM) that I apply first and a string comparison function.  You can see that normalisation has eliminated the line feed in from the statement in my_sql_profile_24.

Now I can see my two exact matching profiles match my force matching profile.  I can see the common part of the SQL up to the literal value, and the different parts of the text are just the literal value.  

           Force Matching Force                          Force                        Force               Exact Matching Exact                          Exact                        Exact
FOR             Signature Name                           Created Date                 Status   EXA             Signature Name                           Created Date                 Status
--- --------------------- ------------------------------ ---------------------------- -------- --- --------------------- ------------------------------ ---------------------------- --------
Common Text
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Force Text                                                                                          Exact Text
--------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------
YES  11431056000319719221 my_sql_profile_force           16:35:36 01/08/2021          ENABLED  NO   12140764948557749245 my_sql_profile_24              16:35:36 01/08/2021          ENABLED
SELECT * FROM T WHERE A =
54                                                                                                  24

                                                                                      ENABLED  NO   14843900676141266266 my_sql_profile_42              16:35:36 01/08/2021          ENABLED
SELECT * FROM T WHERE A =
54

Both the queries mentioned in this blog are available on GitHub.

Clashing SQL Profiles - Exact Matching Profiles Take Precedence Over Force Matching Profiles

Sometimes, you reach a point in performance tuning, where you use a SQL Baseline, or SQL Patch, or SQL Profile to stabilise an execution plan.  These methods all effectively inject a hint or set of hints into a statement to produce the desired execution plan.  Baselines and Patches will only exactly match a SQL ID and therefore a SQL statement.  However, a SQL Profile can optionally do force matching so that it applies to "all SQL statements that have the same text after the literal values in the WHERE clause have been replaced by bind variables.  

This setting may be useful for applications that use only literal values because it enables SQL with text differing only in its literal values to share a SQL profile. If both literal values and bind variables are in the SQL text, or if force_match is set to false (default), then the literal values in the WHERE clause are not replaced by bind variables." [Oracle Database SQL Tuning Guide]

I often work with PeopleSoft, whose batch processes often dynamically generate SQL with literal values.  Therefore, I usually create force matching profiles when I need to control an execution plan.  However, sometimes I come across situations where some exact matching (i.e. not force matching) profiles have been created (often by production DBAs using the tuning advisor) on different statements that have the same force matching signature, and then maybe a force matching profile has also been applied.

Note: SQL Profiles require the Tuning Pack licence.

Where both exact and force matching profiles apply to a SQL statement, the exact matching profile will take precedence over the force matching profile, and even if disabled it will prevent the force matching profile from being applied.

I will demonstrate this with a simple test.  I will create a table with a couple of indexes, collect statistics, and generate an execution plan for a query.  I am using explain plan for command to force a parse of the statement every time.

CREATE TABLE t (a not null, b) AS 
SELECT rownum, ceil(sqrt(rownum)) FROM dual CONNECT BY LEVEL <= 100;
CREATE UNIQUE INDEX t_idx on t(a);
CREATE INDEX t_idx2 on t(b,a);
EXEC dbms_stats.gather_table_stats(user,'T');

Without Any SQL Profiles

Without any profiles in place, I get a skip scan of T_IDX2, and there is no note in the execution plan.

EXPLAIN PLAN FOR SELECT * FROM t WHERE a = 42;
SELECT * FROM table(dbms_xplan.display(null,null,'ADVANCED +ADAPTIVE -PROJECTION'));

Plan hash value: 3418618943
---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |     6 |     1   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | T_IDX2 |     1 |     6 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
…
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      INDEX_SS(@"SEL$1" "T"@"SEL$1" ("T"."B" "T"."A"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"=42)
       filter("A"=42)
…

Force Matching Profile

Now I will create an exact matching SQL profile on the query that will force the use of the unique index.  The query is the same except the literal value is different (it is 54 instead of 42).

DECLARE
signature INTEGER;
sql_txt CLOB;
h       SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
SELECT * FROM t WHERE a = 54
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[FULL(@"SEL$1" "T"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text    => sql_txt,
profile     => h,
name        => 'clashing_profile_test_force',
category    => 'DEFAULT',
validate    => TRUE,
replace     => TRUE,
force_match => TRUE 
);
END;
/

I only have a force-matching profile. 

                                                                   Execution plan with force matching profile (full scan)

NAME                           CATEGORY               SIGNATURE SQL_TEXT                                                                         CREATED
------------------------------ ---------- --------------------- -------------------------------------------------------------------------------- ------------------------------
LAST_MODIFIED                  DESCRIPTION          TYPE    STATUS   FOR    TASK_ID TASK_EXEC_NAME       TASK_OBJ_ID TASK_FND_ID TASK_REC_ID TASK_CON_DBID
------------------------------ -------------------- ------- -------- --- ---------- -------------------- ----------- ----------- ----------- -------------
clashing_profile_test_force    DEFAULT     11431056000319719221                                                                                  27-JUL-21 01.35.43.854691 PM
                                                                SELECT * FROM t WHERE a = 54
27-JUL-21 01.35.43.000000 PM                        MANUAL  ENABLED  YES

The execution plan uses the full plan as specified by the profile, there is a note confirming that the profile was matched and used, and the full hint was listed in the hint report.

EXPLAIN PLAN FOR SELECT * FROM t WHERE a = 42;
SELECT * FROM table(dbms_xplan.display(null,null,'ADVANCED +ADAPTIVE -PROJECTION'));

Plan hash value: 1601196873

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     1 |     6 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| T    |     1 |     6 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------
…
Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "T"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   1 - storage("A"=42)
       filter("A"=42)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------

   0 -  STATEMENT
           -  IGNORE_OPTIM_EMBEDDED_HINTS

   1 -  SEL$1 / T@SEL$1
           -  FULL(@"SEL$1" "T"@"SEL$1")

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

Exact Matching Profile 

I will now add an exact matching profile

DECLARE
signature INTEGER;
sql_txt CLOB;
h       SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
SELECT * FROM t WHERE a = 42
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[INDEX(@"SEL$1" "T"@"SEL$1" ("T"."A"))]',
q'[END_OUTLINE_DATA]');
signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text    => sql_txt,
profile     => h,
name        => 'clashing_profile_test_exact',
category    => 'DEFAULT',
validate    => TRUE,
replace     => TRUE,
force_match => FALSE 
);
END;
/

I can see I now have two SQL Profiles; one force matched, and one exact matched.

                                                                    Execution plan with force matching profile (unique index lookup)

NAME                           CATEGORY               SIGNATURE SQL_TEXT                                                                         CREATED
------------------------------ ---------- --------------------- -------------------------------------------------------------------------------- ------------------------------
LAST_MODIFIED                  DESCRIPTION          TYPE    STATUS   FOR    TASK_ID TASK_EXEC_NAME       TASK_OBJ_ID TASK_FND_ID TASK_REC_ID TASK_CON_DBID
------------------------------ -------------------- ------- -------- --- ---------- -------------------- ----------- ----------- ----------- -------------
clashing_profile_test_exact    DEFAULT     14843900676141266266                                                                                  27-JUL-21 01.35.46.825697 PM
                                                                SELECT * FROM t WHERE a = 42
27-JUL-21 01.35.46.000000 PM                        MANUAL  ENABLED  NO

clashing_profile_test_force    DEFAULT     11431056000319719221                                                                                  27-JUL-21 01.35.43.854691 PM
                                                                SELECT * FROM t WHERE a = 54
27-JUL-21 01.35.43.000000 PM                        MANUAL  ENABLED  YES

The execution plan has changed to the unique index scan. The index hint from the profile appears hints report. The note at the bottom of the plan shows the exact matching profile has been used, taking precedence over the force matching profile.

EXPLAIN PLAN FOR SELECT * FROM t WHERE a = 42;
SELECT * FROM table(dbms_xplan.display(null,null,'ADVANCED +ADAPTIVE -PROJECTION'));

Plan hash value: 2929955852

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     6 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |     6 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T_IDX |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
…
Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."A"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   2 - access("A"=42)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------

   0 -  STATEMENT
           -  IGNORE_OPTIM_EMBEDDED_HINTS

   1 -  SEL$1 / T@SEL$1
           -  INDEX(@"SEL$1" "T"@"SEL$1" ("T"."A"))

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

Different Query

If I run the query with a different literal value, the plan changes back to the full scan, and the note reports the force matching profile was used

EXPLAIN PLAN FOR SELECT * FROM t WHERE a = 54;
SELECT * FROM table(dbms_xplan.display(null,null,'ADVANCED +ADAPTIVE -PROJECTION'));

Plan hash value: 1601196873

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     1 |     6 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| T    |     1 |     6 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------
…
Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "T"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   1 - storage("A"=54)
       filter("A"=54)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------

   0 -  STATEMENT
           -  IGNORE_OPTIM_EMBEDDED_HINTS

   1 -  SEL$1 / T@SEL$1
           -  FULL(@"SEL$1" "T"@"SEL$1")

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

Disable Exact Matching SQL Profile

I will now disable the exact matching profile.

exec dbms_sqltune.alter_sql_profile(name=>'clashing_profile_test_exact', attribute_name=>'STATUS',value=>'DISABLED');
SELECT * FROM dba_sql_profiles where name like 'clashing%';

                                                                Disable Exact Profile - Execution plan with no profile (skip scan) - Odd

NAME                           CATEGORY               SIGNATURE SQL_TEXT                                                                         CREATED
------------------------------ ---------- --------------------- -------------------------------------------------------------------------------- ------------------------------
LAST_MODIFIED                  DESCRIPTION          TYPE    STATUS   FOR    TASK_ID TASK_EXEC_NAME       TASK_OBJ_ID TASK_FND_ID TASK_REC_ID TASK_CON_DBID
------------------------------ -------------------- ------- -------- --- ---------- -------------------- ----------- ----------- ----------- -------------
clashing_profile_test_exact    DEFAULT     14843900676141266266                                                                                  27-JUL-21 01.35.46.825697 PM
                                                                SELECT * FROM t WHERE a = 42
27-JUL-21 01.35.52.000000 PM                        MANUAL  DISABLED NO

clashing_profile_test_force    DEFAULT     11431056000319719221                                                                                  27-JUL-21 01.35.43.854691 PM
                                                                SELECT * FROM t WHERE a = 54
27-JUL-21 01.35.43.000000 PM                        MANUAL  ENABLED  YES

I expected the profile to switch back to the force matching profile, but instead it goes back to the original plan using the skip scan with no profile at all. So the disabled exact matching profile prevents the force matching profile from matching the statement, and then doesn't get applied to the statement either! There is no note in the execution plan and no hint report.

EXPLAIN PLAN FOR SELECT * FROM t WHERE a = 42;
SELECT * FROM table(dbms_xplan.display(null,null,'ADVANCED +ADAPTIVE -PROJECTION'));

Plan hash value: 3418618943

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |     6 |     1   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | T_IDX2 |     1 |     6 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
…
Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      INDEX_SS(@"SEL$1" "T"@"SEL$1" ("T"."B" "T"."A"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   1 - access("A"=42)
       filter("A"=42)

Alter Category of Exact Matching SQL Profile

I could have dropped the SQL Profile, but I might want to retain it for documentation and in case I need to reinstate it. So instead I will move it to a different category.

exec dbms_sqltune.alter_sql_profile(name=>'clashing_profile_test_exact', attribute_name=>'CATEGORY',value=>'DO_NOT_USE');
SELECT * FROM dba_sql_profiles where name like 'clashing%';

                                                      Change Category of Exact Profile - Execution plan with force matching profile (full scan)

NAME                           CATEGORY               SIGNATURE SQL_TEXT                                                                         CREATED
------------------------------ ---------- --------------------- -------------------------------------------------------------------------------- ------------------------------
LAST_MODIFIED                  DESCRIPTION          TYPE    STATUS   FOR    TASK_ID TASK_EXEC_NAME       TASK_OBJ_ID TASK_FND_ID TASK_REC_ID TASK_CON_DBID
------------------------------ -------------------- ------- -------- --- ---------- -------------------- ----------- ----------- ----------- -------------
clashing_profile_test_exact    DO_NOT_USE  14843900676141266266                                                                                  27-JUL-21 02.57.11.343291 PM
                                                                SELECT * FROM t WHERE a = 42
27-JUL-21 02.57.19.000000 PM                        MANUAL  DISABLED NO

clashing_profile_test_force    DEFAULT     11431056000319719221                                                                                  27-JUL-21 02.57.08.390801 PM
                                                                SELECT * FROM t WHERE a = 54
27-JUL-21 02.57.08.000000 PM                        MANUAL  ENABLED  YES

And now the execution plan goes back to the force matching profile and the unique index lookup.

EXPLAIN PLAN FOR SELECT * FROM t WHERE a = 42;
SELECT * FROM table(dbms_xplan.display(null,null,'ADVANCED +ADAPTIVE -PROJECTION'));

Plan hash value: 1601196873

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |     1 |     6 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| T    |     1 |     6 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------
…
Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "T"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   1 - storage("A"=42)
       filter("A"=42)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------

   0 -  STATEMENT
           -  IGNORE_OPTIM_EMBEDDED_HINTS

   1 -  SEL$1 / T@SEL$1
           -  FULL(@"SEL$1" "T"@"SEL$1")

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

Conclusion

An exact matching profile will be matched to a SQL statement before a force matching SQL statement, even if it is disabled, in which case neither profile will be applied.

If you have exact matching SQL profiles that provide the same hints to produce the same execution plan on various similar SQL statements that have the same force matching signature (i.e. they only differ in their literal values), and you wish to replace them with a single force matching profile, then rather than disable the exact matching profiles you should either drop them or if you prefer to retain them for documentation then alter them to a different category. 

• The script GitHub used in this blog to demonstrate this behaviour is available on GitHub.  They were run in Oracle 19.9 for this post.
• The script disabled_profiles_category.sql moves all disabled profiles from the category DEFAULT to DO_NOT_USE.

In a subsequent post, I will show how to detect conflicting SQL profiles.

Profiling Execution Plans

In my previous blog post, I demonstrated how to identify the active lines in an adaptive execution plan on DBA_HIST_SQL_PLAN so that I could profile behaviour. This post demonstrates a practical application of that technique. This statement comes out of the PeopleSoft Financials Multi-currency Processing. A process extracts groups of rows in elimination sets into a working storage table and joins that table to the ledger table for further processing. The number of rows extracted from the elimination set selector table (PS_ELIM_CF_SEL2001 in this case) can vary in a single process from a few to a few thousand. However, the process does regather optimizer statistics each time.

SQL_ID fwcdxjy41j23n
--------------------
SELECT L.BUSINESS_UNIT,L.ACCOUNT,L.DEPTID,L.PRODUCT,L.BUDGET_REF,L.AFFIL
IATE,L.CHARTFIELD1,L.BOOK_CODE,L.GL_ADJUST_TYPE,L.DATE_CODE,SUM(L.POSTED
_TOTAL_AMT) FROM PS_ELIM_CF_SEL2001 S, PS_LEDGER L WHERE
S.ELIMINATION_SET='UK_OTHER' AND S.TREE_NODE_NUM=1234567890 AND
S.PROCESS_INSTANCE=0001234567 AND L.FISCAL_YEAR=2018 AND
L.LEDGER=S.LEDGER AND L.BUSINESS_UNIT=S.BUSINESS_UNIT AND
L.AFFILIATE=S.AFFILIATE AND L.ACCOUNT=S.ACCOUNT AND L.CURRENCY_CD='GBP'
AND L.STATISTICS_CODE=' ' AND (L.ACCOUNTING_PERIOD<=001 AND
((L.ACCOUNTING_PERIOD>=0 AND S.BALANCE_FWD_SW='Y') OR
L.ACCOUNTING_PERIOD>=1 AND S.BALANCE_FWD_SW='N')) GROUP BY
L.BUSINESS_UNIT,L.ACCOUNT,L.DEPTID,L.PRODUCT,L.BUDGET_REF,L.AFFILIATE,L.
CHARTFIELD1,L.BOOK_CODE,L.GL_ADJUST_TYPE,L.DATE_CODE

The changing literal values mean that every SQL statement has a different SQL_ID. SQL Baselines are not viable here. No one plan produces optimal performance, so a traditional SQL profile proscribing a particular plan is not the answer. Oracle moves between three adaptive plans depending at least partially upon the number of rows in the elimination table.

Plan hash value: 42743070
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                    |       |       |   237K(100)|       |          |       |
|   1 |  SORT GROUP BY                         |                    |    13 |  1716 |   237K  (1)| 00:00:10 |       |       |
|-  2 |   HASH JOIN                            |                    |    13 |  1716 |   237K  (1)| 00:00:10 |       |       |
|   3 |    NESTED LOOPS                        |                    |    13 |  1716 |   237K  (1)| 00:00:10 |       |       |
|-  4 |     STATISTICS COLLECTOR               |                    |       |       |            |       |          |       |
|   5 |      TABLE ACCESS STORAGE FULL         | PS_ELIM_CF_SEL2001 |    13 |   728 |   138   (2)| 00:00:01 |       |       |
|   6 |     PARTITION RANGE OR                 |                    |     1 |    76 | 18249   (1)| 00:00:01 |KEY(OR)|KEY(OR)|
|   7 |      PARTITION LIST ITERATOR           |                    |     1 |    76 | 18249   (1)| 00:00:01 |   KEY |   KEY |
|   8 |       TABLE ACCESS BY LOCAL INDEX ROWID| PS_LEDGER          |     1 |    76 | 18249   (1)| 00:00:01 |   KEY |   KEY |
|   9 |        INDEX RANGE SCAN                | PSCLEDGER          |     1 |       | 18228   (1)| 00:00:01 |   KEY |   KEY |
|- 10 |    PARTITION RANGE OR                  |                    |     1 |    76 | 18249   (1)| 00:00:01 |KEY(OR)|KEY(OR)|
|- 11 |     PARTITION LIST JOIN-FILTER         |                    |     1 |    76 | 18249   (1)| 00:00:01 |:BF0000|:BF0000|
|- 12 |      TABLE ACCESS STORAGE FULL         | PS_LEDGER          |     1 |    76 | 18249   (1)| 00:00:01 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3968216348
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                    |       |       |  1679K(100)|       |          |       |
|   1 |  SORT GROUP BY                         |                    |    92 | 12236 |  1679K  (1)| 00:01:06 |       |       |
|   2 |   HASH JOIN                            |                    |    92 | 12236 |  1679K  (1)| 00:01:06 |       |       |
|-  3 |    NESTED LOOPS                        |                    |    92 | 12236 |  1679K  (1)| 00:01:06 |       |       |
|-  4 |     STATISTICS COLLECTOR               |                    |       |       |            |       |          |       |
|   5 |      TABLE ACCESS STORAGE FULL         | PS_ELIM_CF_SEL2001 |    92 |  5244 |   138   (2)| 00:00:01 |       |       |
|-  6 |     PARTITION RANGE OR                 |                    |     1 |    76 | 18249   (1)| 00:00:01 |KEY(OR)|KEY(OR)|
|-  7 |      PARTITION LIST ITERATOR           |                    |     1 |    76 | 18249   (1)| 00:00:01 |   KEY |   KEY |
|-  8 |       TABLE ACCESS BY LOCAL INDEX ROWID| PS_LEDGER          |     1 |    76 | 18249   (1)| 00:00:01 |   KEY |   KEY |
|-  9 |        INDEX RANGE SCAN                | PSCLEDGER          |     1 |       | 18228   (1)| 00:00:01 |   KEY |   KEY |
|  10 |    PARTITION RANGE OR                  |                    |     1 |    76 | 18249   (1)| 00:00:01 |KEY(OR)|KEY(OR)|
|  11 |     PARTITION LIST JOIN-FILTER         |                    |     1 |    76 | 18249   (1)| 00:00:01 |:BF0000|:BF0000|
|  12 |      TABLE ACCESS STORAGE FULL         | PS_LEDGER          |     1 |    76 | 18249   (1)| 00:00:01 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------------------------------------

Plan hash value: 386495123
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                    |       |       |  1763K(100)|       |          |       |
|   1 |  SORT GROUP BY                          |                    |     1 |   132 |  1763K  (2)| 00:01:09 |       |       |
|   2 |   HASH JOIN                             |                    |  2105 |   271K|  1763K  (2)| 00:01:09 |       |       |
|   3 |    PART JOIN FILTER CREATE              | :BF0000            |  2105 |   271K|  1763K  (2)| 00:01:09 |       |       |
|-  4 |     NESTED LOOPS                        |                    |  2105 |   271K|  1763K  (2)| 00:01:09 |       |       |
|-  5 |      STATISTICS COLLECTOR               |                    |       |       |            |       |          |       |
|   6 |       TABLE ACCESS STORAGE FULL         | PS_ELIM_CF_SEL2001 |  2113 |   115K|   138   (2)| 00:00:01 |       |       |
|-  7 |      PARTITION RANGE OR                 |                    |     1 |    76 |  1763K  (2)| 00:01:09 |KEY(OR)|KEY(OR)|
|-  8 |       PARTITION LIST ITERATOR           |                    |     1 |    76 |  1763K  (2)| 00:01:09 |   KEY |   KEY |
|-  9 |        TABLE ACCESS BY LOCAL INDEX ROWID| PS_LEDGER          |     1 |    76 |  1763K  (2)| 00:01:09 |   KEY |   KEY |
|- 10 |         INDEX RANGE SCAN                | PSCLEDGER          |       |       |            |       |      KEY |   KEY |
|  11 |    PARTITION RANGE OR                   |                    |    24M|  1743M|  1763K  (2)| 00:01:09 |KEY(OR)|KEY(OR)|
|  12 |     PARTITION LIST JOIN-FILTER          |                    |    24M|  1743M|  1763K  (2)| 00:01:09 |:BF0000|:BF0000|
|  13 |      TABLE ACCESS STORAGE FULL          | PS_LEDGER          |    24M|  1743M|  1763K  (2)| 00:01:09 |   KEY |   KEY |
------------------------------------------------------------------------------------------------------------------------------

Only a small proportion of statements run by the process are captured during AWR snapshots. With PeopleSoft, because there is so much dynamically generated non-sharable SQL, I generally recommend reducing the snapshot interval to 15 minutes and collecting the top 100 statements (the default is hourly and the top 30 statements), and even so we only have a limited number of samples with the larger statements more likely to be sampled.
We are already using SQL Profiles to apply optimizer parameters to this statement so I can identify entries in DBA_HIST_SQLSTAT by the matched profile, though I could also use the FORCE_MATCHING_SIGNATURE. I will start by extracting the SQL statistics for all matching statements into a temporary working storage table, including the OTHER_XML from the SQL Plan

drop table my_stats purge;
create table my_stats as 
with s as (
 select s.dbid, s.sql_id, s.plan_hash_value, s.sql_profile, s.action
 ,      max(s.optimizer_cost) optimizer_cost
 ,      max(s.PX_SERVERS_EXECS_DELTA) px_servers
 ,      sum(ELAPSED_TIME_DELTA)/1e6 elapsed_time
 from   dba_hist_snapshot x
 ,      dba_hist_sqlstat s
 WHERE  s.SNAP_id = X.SNAP_id
 AND    s.dbid = x.dbid
 AND    s.instance_number = x.instance_number
 AND    s.module = 'GLPOCONS'
 and    s.sql_profile LIKE 'GLPOCONS_AD%B2%'
 and    s.plan_hash_value > 0
 group by s.dbid, s.sql_id, s.plan_hash_value, s.sql_profile, s.action
)
SELECT s.*
,      xmltype(other_xml) other_xml
FROM   s, dba_hist_sql_plan p
WHERE  p.dbid = s.dbid
and    p.sql_id = s.sql_id
and    p.plan_hash_value = s.plan_hash_value
and    p.other_xml IS NOT NULL
and    p.id = 1
/
select count(*) from my_stats 
/

I found it was necessary to break this up into two stages.  Otherwise, I got ORA-32036: unsupported case for inlining of query name in WITH clause. This could be a manifestation of Bug 19684504 - ORA-32036 executing WITH statement with XML (Doc ID 19684504.8)

set pages 99 long 10
break on sql_profile skip 1
column sql_profile     format a17
column op              format 999
column dis             format 999
column par             format 999
column dep             format 999
column skp             format 999
column plan_hash_value format 9999999999 heading 'PHV'
column px_servers      format 9999       heading 'PX|Serv'
column ecost           format 9999       heading 'Elim|Cost'
column lcost           format 99999999   heading 'Ledger|Cost'
column ecard           format 99999      heading 'Elim|Card'
column lcard           format 99999999   heading 'Ledger|Card'
column optimizer_cost  format 99999999   heading 'Opt|Cost'
column elapsed_time    format 9999.99    heading 'Elapsed|Time'
column object_name     format a18 
column elim_obj        format a7         heading 'Bloom?' 
column epoptions       format a14        heading 'Join|Options'
column epoperation     format a16        heading 'Join|Operation'
column eoptions        format a14        heading 'Elim|Options'
column loptions        format a20        heading 'Ledger|Options'
column options         format a20
spool dmk
with y as (
select /*+MATERIALIZE*/ x.*
,      xt.*
,      p.object_type, p.object_name
,      p.operation, p.options, p.cost, p.cardinality
from   my_stats x
,      xmltable('/other_xml/display_map/row' PASSING x.other_xml
       COLUMNS "OP" NUMBER path '@op',
               "DIS" NUMBER path '@dis',
               "PAR" NUMBER path '@par',
               "DEP" NUMBER path '@dep',
               "SKP" NUMBER path '@skp'
       ) xt
,      dba_hist_sql_plan p
where  xt.skp = 0
and    p.dbid = x.dbid
and    p.sql_id = x.sql_id
and    p.plan_hash_Value = x.plan_hash_value
and    p.id = xt.op
)
select x.sql_profile, x.sql_id, x.plan_hash_value, x.px_servers
,      e.object_name, e.options eoptions, e.cardinality ecard, e.cost ecost
,      l.options loptions, l.cardinality lcard, l.cost lcost
,      ep.operation epoperation, ep.object_name elim_obj
,      x.optimizer_cost, x.elapsed_time
From   my_stats x
,      y e
,      y l
,      y ep
WHERE  e.dbid = x.dbid
And    e.sql_id = x.sql_id
And    e.plan_hash_Value = x.plan_hash_value
And    e.object_name like 'PS_ELIM_CF_SEL%'
And    e.object_type = 'TABLE'
AND    l.dbid = x.dbid
And    l.sql_id = x.sql_id
And    l.plan_hash_Value = x.plan_hash_value
And    l.object_name = 'PS_LEDGER'
And    l.object_type = 'TABLE'
And    ep.dbid = x.dbid
And    ep.sql_id = x.sql_id
And    ep.plan_hash_Value = x.plan_hash_value
And    ep.dis = e.par /*parent of display line*/
ORDER by x.sql_profile, e.cardinality, x.optimizer_cost, x.elapsed_time
/
spool off

The SQL query to profile the plan will be different for each set of plans that I want to investigate. The query showing in this blog was developed for the plan I am investigating here. I am interested in three aspects of the execution plan, the access method for each of the two tables, and the method by which they are joined. So, I have to join each SQL stats sub-query (X) back to the execution plan sub-query (Y) three times, selecting a different row from the plan each time.

• E: table access of PS_ELIM_CF_SEL% table, either by a FULL scan or it might be by ROWID if an index was used.
• L: table access of PS_LEDGER table. This will either be a FULL scan or by ROWID if an index was used.
• EP: parent of table access of PS_ELIM_CF_SEL%. This will either be a HASH JOIN or the creation of the Bloom Filter (:BF000). If I order the result by the cardinality of the PS_ELIM_CF_SEL% I can see how and when the plan changes as the number of rows retrieved from it (the cardinality) change.

                                               PX                    Elim             Elim  Elim Ledger                  Ledger    Ledger Join                           Opt Elapsed
SQL_PROFILE       SQL_ID                PHV  Serv OBJECT_NAME        Options          Card  Cost Options                   Card      Cost Operation        Bloom?       Cost    Time
----------------- ------------- ----------- ----- ------------------ -------------- ------ ----- -------------------- --------- --------- ---------------- ------- --------- -------
GLPOCONS_ADPB2001 b7hy0jfppdvn2    42743070     0 PS_ELIM_CF_SEL2001 STORAGE FULL      13   138 BY LOCAL INDEX ROWID         1     18249 NESTED LOOPS                237379      .40
…
                  9yz896xyn5aky    42743070     0 PS_ELIM_CF_SEL2001 STORAGE FULL      51   138 BY LOCAL INDEX ROWID         1     18249 NESTED LOOPS                930851     2.62
                  cbnz1jumm0x23    42743070     0 PS_ELIM_CF_SEL2001 STORAGE FULL      52   138 BY LOCAL INDEX ROWID         1     18249 NESTED LOOPS                949101      .66
…
                  cmajgmuyc3hc3    42743070     0 PS_ELIM_CF_SEL2001 STORAGE FULL      92   138 BY LOCAL INDEX ROWID         1     18249 NESTED LOOPS               1679071    21.89
                  3sbkyfwy6xk02  3968216348     0 PS_ELIM_CF_SEL2001 STORAGE FULL      92   138 STORAGE FULL                 1     18249 HASH JOIN                  1679071    51.95
                  27pxj6qu49zzr  3968216348     0 PS_ELIM_CF_SEL2001 STORAGE FULL      92   138 STORAGE FULL                 1     18249 HASH JOIN                  1679071    52.49
                  8nfbzan3hbzdg  3968216348     0 PS_ELIM_CF_SEL2001 STORAGE FULL      92   138 STORAGE FULL                 1     18249 HASH JOIN                  1679071    67.49
                  827sykfucq01t  3968216348     0 PS_ELIM_CF_SEL2001 STORAGE FULL      93   138 STORAGE FULL                 1     18249 HASH JOIN                  1697321    87.65
                  fmd3r4848nypk  3968216348     0 PS_ELIM_CF_SEL2001 STORAGE FULL      93   138 STORAGE FULL                 1     18249 HASH JOIN                  1697321    89.39
                  34h7j9rqf8y73  2660510816     0 PS_ELIM_CF_SEL2001 STORAGE FULL      97   138 BY LOCAL INDEX ROWID         1   1763685 NESTED LOOPS               1763970     1.54
                  0cfqyuz79qd09   386495123     0 PS_ELIM_CF_SEL2001 STORAGE FULL      98   138 STORAGE FULL          24054654   1763685 PART JOIN FILTER :BF0000   1763970    21.32
                  ff5hf8zpwhmd2   386495123     0 PS_ELIM_CF_SEL2001 STORAGE FULL      98   138 STORAGE FULL          24054654   1763685 PART JOIN FILTER :BF0000   1763970    26.64
                  9j0m5gq5aqssz   386495123     0 PS_ELIM_CF_SEL2001 STORAGE FULL      99   138 STORAGE FULL          24054654   1763685 PART JOIN FILTER :BF0000   1763970    34.28
                  bzbqrc7bw768q    42743070     0 PS_ELIM_CF_SEL2001 STORAGE FULL     102   138 BY LOCAL INDEX ROWID         1     18249 NESTED LOOPS               1861564      .76
                  bjjx0fxmtqzzt    42743070     0 PS_ELIM_CF_SEL2001 STORAGE FULL     102   138 BY LOCAL INDEX ROWID         1     18249 NESTED LOOPS               1861564     3.66
                  aqcxt7par2a22  2660510816     0 PS_ELIM_CF_SEL2001 STORAGE FULL     104   138 BY LOCAL INDEX ROWID         1   1763685 NESTED LOOPS               1763970     9.57
                  4cfsq52xzftpz   386495123     0 PS_ELIM_CF_SEL2001 STORAGE FULL     104   138 STORAGE FULL          24054654   1763685 PART JOIN FILTER :BF0000   1763970    25.39
                  76hq9zbfrm599   386495123     0 PS_ELIM_CF_SEL2001 STORAGE FULL     104   138 STORAGE FULL          24054654   1763685 PART JOIN FILTER :BF0000   1763970    26.11
                  2b8ck8kpyrvhg   386495123     0 PS_ELIM_CF_SEL2001 STORAGE FULL     104   138 STORAGE FULL          24054654   1763685 PART JOIN FILTER :BF0000   1763970    35.71
                  6gtu9tauhp55y   386495123     0 PS_ELIM_CF_SEL2001 STORAGE FULL     104   138 STORAGE FULL          24054654   1763685 PART JOIN FILTER :BF0000   1763970    39.77
…
                  88kyxqx2qbjxg   386495123     0 PS_ELIM_CF_SEL2001 STORAGE FULL    1021   138 STORAGE FULL          24054654   1763685 PART JOIN FILTER :BF0000   1763970    21.73
…
                  dh39mtw8rga5s   386495123     0 PS_ELIM_CF_SEL2001 STORAGE FULL    2113   138 STORAGE FULL          24054654   1763685 PART JOIN FILTER :BF0000   1763970    40.26

I can see that the optimizer goes backwards and forwards between different plans at different times as the cardinality increases. Nevertheless, I can make some informed decisions about how to adjust the optimizer parameters for this statement with hints introduced by a SQL Profile.

• I can change the point where the optimizer switches from to the full scan and bloom filter to a lower cardinality by increasing the cost of the index with a higher value optimizer_index_cost_adj.
• I can enable automatic parallelism with parallel_degree_policy, scale the calculated degree of parallelism with parallel_degree_level, but limit the degree of parallelism with parallel_degree_limit. The threshold for parallelism can be set with parallel_min_time_threshold. The parallelism threshold can be set at a slightly lower cardinality than the switch to the full scan.
• The settings can be tested using EXPLAIN PLAN FOR with the cardinality hint to set the number of rows returned from the PS_ELIM_CF_SEL% table

explain plan for
SELECT /*+cardinality(s 300)
NO_EXPAND 
OPT_PARAM('parallel_degree_policy','AUTO')
OPT_PARAM('parallel_min_time_threshold',220)
OPT_PARAM('parallel_degree_limit',40)
OPT_PARAM('optimizer_index_cost_adj',10912)
*/
L.BUSINESS_UNIT,L.ACCOUNT,L.DEPTID,L.PRODUCT,L.BUDGET_REF
…
/
set echo off trimspool on pages 999 lines 200 autotrace off
select * from table(dbms_xplan.display(null,null,'ADVANCED +ADAPTIVE -PROJECTION -PREDICATE -OUTLINE'));

When satisfactory values have been determined, they can be passed back into the SQL Profile.

…
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[OPT_PARAM('parallel_degree_policy','AUTO')]',
q'[OPT_PARAM('parallel_min_time_threshold',60)]',
q'[OPT_PARAM('parallel_degree_limit',40)]',
q'[OPT_PARAM('optimizer_index_cost_adj',10912)]',
q'[NO_EXPAND]',
…
q'[END_OUTLINE_DATA]');
…
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text    => sql_txt,
profile     => h,
name        => 'GLPOCONS_'||i.id,
…
replace     => TRUE,
force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ 
);
…

Hints, Patches, Force Matching and SQL Profiles

Sometimes, when all else fails, it is necessary to add hints to a piece of SQL in order to make the optimizer use a particular execution plan. We might directly add the hint to the code. However, even if it is possible to access the code directly, that may not be a good idea. In the future, if we need to change the hint, possibly due to a change in optimizer behaviour on database upgrade, then we would again need to make a code change.
Instead, we could look at Oracle's plan stability technologies.  There are 4 plan stability features in the database. They are different, but conceptually they all involve associating a set of hints with a statement that will be picked up by the optimizer at runtime.

• SQL Outlines can be collected by the database at runtime. They can exactly match a SQL text or force match. However, they are deprecated from 11g. 
• SQL Baselines can be collected by the database at runtime either on request or automatically. They feed real experience of previous executions of a statement back into subsequent executions. They match by SQL_ID. 
• SQL Patches are a mechanism for manually inserting specific hints into a specific SQL ID 
• SQL Profiles are also a mechanism for inserting specific hints into a specific SQL statement, but can also do forced matching. We are probably most used to creating profiles with Carlos Sierra's coe_xfr_sql_profile.sql script (part of Oracle support's SQLTXPLAIN), but as this blog will show that is not the only option. However, they require the database tuning pack to be licenced, which is not the case with the other mechanisms. 

There are several posts on Oracle's optimizer team that blog explains how to use SQL patches to inject a hint or disable an embedded hint.

• SQL Plan Management (Part 4 of 4) User Interfaces and Other Features (Feb 2009): Maria Colgan described the DBMS_SPM package, and how baselines are captured, evolved and can be accepted. 
• Using SQL Patch to add hints to a packaged application (June 2017): Nigel Bayliss describes the new DBMS_SQLDIAG API in Oracle 12.2 to add SQL patches to specific SQL statements, but everything works the same in the internal package as described in Maria's earlier blogs: 
• Using SQL Patch to add hints to a packaged application (Feb 2012) describes adding patches with the DBMS_SQLDIAG_INTERNAL package. 
• Additional Information on SQL Patches (Mar 2012)  

However, the SQL patches work by attaching to a specific SQL_ID. If your application has literal values that change instead of bind variables you will not be able to inject hints with SQL patches.
The following example SQL was generated by PeopleSoft GL reporting tool, nVision. The code is dynamically generated by the tool, and there is no way to add a hint directly. The generated code contains literal values, in particular, the SELECTOR_NUM will be different for every execution. Therefore the SQL_ID will be different for every execution. Therefore, it is not possible to use a SQL Patch to inject a hint.

SELECT L2.TREE_NODE_NUM,L3.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT) 
FROM PS_XX_SUM_CONSOL_VW A, PSTREESELECT05 L2, PSTREESELECT10 L3 
WHERE A.LEDGER='S_USMGT' 
AND A.FISCAL_YEAR=2017 
AND A.ACCOUNTING_PERIOD BETWEEN 0 AND 12 
AND (A.DEPTID BETWEEN 'A0000' AND 'A8999' OR A.DEPTID BETWEEN 'B0000' AND 'B9149' 
OR A.DEPTID='B9156' OR A.DEPTID='B9158' OR A.DEPTID BETWEEN 'B9165' AND 'B9999' 
OR A.DEPTID BETWEEN 'C0000' AND 'C9999' OR A.DEPTID BETWEEN 'D0000' AND 'D9999' 
OR A.DEPTID BETWEEN 'G0000' AND 'G9999' OR A.DEPTID BETWEEN 'H0000' AND 'H9999' 
OR A.DEPTID='B9150' OR A.DEPTID=' ') 
AND L2.SELECTOR_NUM=10228 
AND A.BUSINESS_UNIT=L2.RANGE_FROM_05 
AND L3.SELECTOR_NUM=10231 
AND A.ACCOUNT=L3.RANGE_FROM_10 
AND A.CHARTFIELD1='0012345' 
AND A.CURRENCY_CD='GBP' 
GROUP BY L2.TREE_NODE_NUM,L3.TREE_NODE_NUM
/

If only the literal values differ, then the SQL statements will have the same force matching signature and one SQL profile will match all similar statements. Although, statements will not force match if the number of predicates changes. Nonetheless, I can inject hints with a SQL profile. This is the plan I get initially without a profile. It doesn't perform well, and it is not the plan I want.

Plan hash value: 808840077
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                   |       |       | 10408 (100)|          |       |       |
|   1 |  HASH GROUP BY                                |                   |   517 | 50666 | 10408   (1)| 00:00:01 |       |       |
|   2 |   HASH JOIN                                   |                   |   517 | 50666 | 10407   (1)| 00:00:01 |       |       |
|   3 |    PARTITION RANGE SINGLE                     |                   |   731 | 13158 |     3   (0)| 00:00:01 | 10228 | 10228 |
|   4 |     INDEX FAST FULL SCAN                      | PSAPSTREESELECT05 |   731 | 13158 |     3   (0)| 00:00:01 | 10228 | 10228 |
|   5 |    HASH JOIN                                  |                   |   518 | 41440 | 10404   (1)| 00:00:01 |       |       |
|   6 |     PARTITION RANGE SINGLE                    |                   |   249 |  5727 |     2   (0)| 00:00:01 | 10231 | 10231 |
|   7 |      INDEX FAST FULL SCAN                     | PSAPSTREESELECT10 |   249 |  5727 |     2   (0)| 00:00:01 | 10231 | 10231 |
|   8 |     PARTITION RANGE ITERATOR                  |                   |  7785 |   433K| 10402   (1)| 00:00:01 |    28 |    40 |
|   9 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PS_X_LEDGER_ACCTS |  7785 |   433K| 10402   (1)| 00:00:01 |    28 |    40 |
|  10 |       SORT CLUSTER BY ROWID BATCHED           |                   |  5373 |       |  5177   (1)| 00:00:01 |       |       |
|  11 |        INDEX SKIP SCAN                        | PS_X_LEDGER_ACCTS |  5373 |       |  5177   (1)| 00:00:01 |    28 |    40 |
-----------------------------------------------------------------------------------------------------------------------------------

These are the hints I want to introduce. I want to force materialize view rewrite, invoke parallelism if the statement is estimated to run for at least 2 seconds, and use a Bloom filter on the materialized view.

SELECT /*+OPT_PARAM('parallel_degree_policy','AUTO') OPT_PARAM('parallel_min_time_threshold',2) 
OPT_PARAM('parallel_degree_limit',4) REWRITE PX_JOIN_FILTER(PS_XX_SUM_GCNSL_MV)*/…

This application produces many statements that I may want to control with a profile, but with forced matching this comes down to a not unmanageable number.  I have created a data-driven framework to create the profiles. I have created working storage table into which I will populate it with details of each force matching signature for which I want to create a profile.

CREATE TABLE dmk_fms_profiles
(force_matching_signature    NUMBER NOT NULL
,sql_id                      VARCHAR2(13) 
,plan_hash_value             NUMBER
,module                      VARCHAR2(64)
,report_id                   VARCHAR2(32) /*Application Specific*/
,tree_list                   CLOB         /*Application Specific*/
,sql_profile_name            VARCHAR2(30)
,parallel_min_time_threshold NUMBER
,parallel_degree_limit       NUMBER
,other_hints                 CLOB
,delete_profile              VARCHAR2(1)
,sql_text                    CLOB
,CONSTRAINT dmk_fms_profiles_pk PRIMARY KEY (force_matching_signature)
,CONSTRAINT dmk_fms_profiles_u1 UNIQUE (sql_id)
,CONSTRAINT dmk_fms_profiles_u2 UNIQUE (sql_profile_name)
)
/

Using conditional parallelism with the PARALLEL_MIN_TIME_THRESHOLD, but limited with PARALLEL_DEGREE_LIMIT is a frequently used tactic with this application, so I have specified columns for those hints, otherwise, hints are injected via a string. I identified the problematic SQL by analysis with ASH, and hence I also obtained the FORCE_MATCHING_SIGNATURE for them. The metadata is keyed by FORCE_MATCHING_SIGNATURE. I am specifying an additional hint to force materialized view rewrite and use a Bloom filter on the ledger table. I have specified a meaningful name for the SQL profile.

INSERT INTO dmk_fms_profiles (force_matching_signature, parallel_min_time_threshold, parallel_degree_limit, other_hints, sql_profile_name) 
VALUES (16625752171077499412, 1, 4, 'REWRITE PX_JOIN_FILTER(PS_FT_SUM_GCNSL_MV)', 'NVS_GBGL123I_BU_CONSOL_ACCOUNT');
COMMIT;

Profiles are created on the text of a SQL rather than the SQL_ID or FORCE_MATCHING_SIGNATURE directly. Therefore the SQL_TEXT must be extracted from the AWR, so this method also requires that the statement has been captured by an AWR snapshot.

UPDATE dmk_fms_profiles a
SET    (module, action, sql_id, plan_hash_value, sql_text)
=      (SELECT s.module, s.action, s.sql_id, s.plan_hash_value, t.sql_text
        FROM   dba_hist_sqlstat s
        ,      dba_hist_sqltext t
        WHERE  t.dbid = s.dbid
        AND    t.sql_id = s.sql_id
        AND    s.force_matching_signature = a.force_matching_signature
        AND    s.snap_id = (
   SELECT MAX(s1.snap_id)
  FROM   dba_hist_sqlstat s1
  WHERE  s1.force_matching_signature = a.force_matching_signature
  AND    s1.module = 'RPTBOOK'     /*Application Specific*/
   AND    s1.action LIKE 'PI=%:%:%' /*Application Specific*/)
  AND    s.module = 'RPTBOOK'             /*Application Specific*/
  AND    s.action LIKE 'PI=%:%:%'         /*Application Specific*/
  AND    ROWNUM = 1)
WHERE sql_id IS NULL
/

MERGE INTO dmk_fms_profiles u
USING (
SELECT a.sql_id, a.force_matching_signature, p.name
FROM   dmk_fms_profiles a
,      dba_sql_profiles p
WHERE  p.signature = a.force_matching_signature
) s
ON (s.force_matching_signature = u.force_matching_signature)
WHEN MATCHED THEN UPDATE
SET u.sql_profile_name = s.name
/

Columns REPORT_ID and TREE_LIST contain application specific information extracted from the application instrumentation and logging. They only mean something in the context of the application.

/*Application Specific - extract report ID from ACTION*/
UPDATE dmk_fms_profiles a
SET    report_id = substr(regexp_substr(s.action,':([A-Za-z0-9_-])+',1,1),2)
WHERE  report_id IS NULL
AND    action IS NOT NULL
/

Now I can produce a simple report of the metadata in order to see what profiles should be created.

column sql_text word_wrapped on format a110
column module format a8
column report_id heading 'nVision|Report ID'
column tree_list word_wrapped on format a20
column plan_hash_value             heading 'SQL Plan|Hash Value'         format 9999999999
column parallel_min_time_threshold heading 'Parallel|Min Time|Threshold' format 999
column parallel_degree_limit       heading 'Parallel|Degree|Limit'       format 999
set long 500
SELECT * FROM dmk_fms_profiles
/

                                          SQL Plan                                                                           
FORCE_MATCHING_SIGNATURE SQL_ID         Hash Value MODULE   ACTION                                                           
------------------------ ------------- ----------- -------- ---------------------------------------------------------------- 
                                                                                      Parallel Parallel
nVision                                                                               Min Time   Degree
Report ID                        TREE_LIST            SQL_PROFILE_NAME               Threshold    Limit D
-------------------------------- -------------------- ------------------------------ --------- -------- -
OTHER_HINTS
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
    12803175998948432502 5pzxhha3392cs   988048519 RPTBOOK  PI=3186222:USGL233I:10008                                        
USGL233I                         BU_GAAP_CONSOL,      NVS_GBGL123I_BU_CONSOL_ACCOUNT                   1        4
                                 GAAP_ACCOUNT
REWRITE PX_JOIN_FILTER(PS_XX_SUM_GCNSL_MV)
SELECT L2.TREE_NODE_NUM,A.ACCOUNT,SUM(A.POSTED_TOTAL_AMT) FROM PS_LEDGER A, PSTREESELECT05 L2, PSTREESELECT10 L3
WHERE A.LEDGER='S_GBMGT' AND A.FISCAL_YEAR=2017 AND A.ACCOUNTING_PERIOD BETWEEN 0 AND 12 AND (A.DEPTID BETWEEN
'A0000' AND 'A8999' OR A.DEPTID BETWEEN 'B0000' AND 'B9149' OR A.DEPTID='B9156' OR A.DEPTID='B9158' OR A.DEPTID
BETWEEN 'B9165' AND 'B9999' OR A.DEPTID BETWEEN 'C0000' AND 'C9999' OR A.DEPTID BETWEEN 'D0000' AND 'D9999' OR
A.DEPTID BETWEEN 'G0000' AND 'G9999' OR A.DE

Next, this PL/SQL block will create or recreate SQL profiles from the metadata. The various hints can be concatenated into a single string and passed as a parameter to SQLPROF_ATTR. The SQL text is passed as a parameter when the profile is created.

set serveroutput on
DECLARE
  l_signature NUMBER;
  h       SYS.SQLPROF_ATTR;
  e_no_sql_profile EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_no_sql_profile, -13833);
  l_description CLOB;
BEGIN

FOR i IN (
  SELECT f.*, s.name
  FROM   dmk_fms_profiles f
    LEFT OUTER JOIN dba_sql_profiles s
    ON f.force_matching_signature = s.signature
) LOOP

  BEGIN 
    IF i.name IS NOT NULL AND i.delete_profile = 'Y' THEN
      dbms_sqltune.drop_sql_profile(name => i.name);
    END IF;
    EXCEPTION WHEN e_no_sql_profile THEN NULL;
  END;

  IF i.delete_profile = 'Y' THEN 
    NULL;
  ELSIF i.sql_text IS NOT NULL THEN
    h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
CASE WHEN i.parallel_min_time_threshold>=0 THEN 'OPT_PARAM(''parallel_degree_policy'',''AUTO'') ' END||
CASE WHEN i.parallel_degree_limit      >=0 THEN 'OPT_PARAM(''parallel_degree_limit'','      ||i.parallel_degree_limit      ||') ' END||
CASE WHEN i.parallel_min_time_threshold>=0 THEN 'OPT_PARAM(''parallel_min_time_threshold'','||i.parallel_min_time_threshold||') ' END||
i.other_hints,
q'[END_OUTLINE_DATA]');

    l_signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(i.sql_text);
    l_description := 'coe nVision '||i.report_id||' '||i.tree_list||' '||i.force_matching_signature||'='||l_signature;
    dbms_output.put_line(i.sql_profile_name||' '||l_description);

    DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text    => i.sql_text,
profile     => h,
name        => i.sql_profile_name,
description => l_description,
category    => 'DEFAULT',
validate    => TRUE,
replace     => TRUE,
force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );

  END IF;
END LOOP;
END;
/

I can verify that the profile has been created, and the hints that it contains, thus:

SELECT profile_name,
       xmltype(comp_data) as xmlval
FROM   dmk_fms_profiles p
,      dbmshsxp_sql_profile_attr  x
WHERE  x.profile_name = p.sql_profile_name
AND    p.status = 'ENABLED'
ORDER BY 1
/

PROFILE_NAME                                                                                                                                                                                            
------------------------------
XMLVAL                                                                                                                                                                                                  
------------------------------------------------------------------------------------------------
NVS_GBGL123I_BU_CONSOL_ACCOUNT
<![CDATA[BEGIN_OUTLINE_DATA]]>                                                                                                                                                           
  <![CDATA[OPT_PARAM('parallel_degree_policy','AUTO') OPT_PARAM('parallel_degree_limit',4) OPT_PARAM('parallel_min_time_threshold',1) REWRITE PX_JOIN_FILTER(PS_FT_SUM_GCNSL_MV)]]>                                           
  <![CDATA[END_OUTLINE_DATA]]>

And now when the application runs, I get the plan that I wanted.

• The query runs in parallel.
• The SQL is rewritten to use materialized view.
• There are no indexes so it must full scan it.
• It generates a bloom filter from PSTREESELECT10 and applies it to the materialized view.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                         | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |                    |       |       |  2219 (100)|          |       |       |        |      |            |
|   1 |  PX COORDINATOR                                   |                    |       |       |         |             |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)                             | :TQ10004           |   111 |  9879 |  2219   (6)| 00:00:01 |       |       |  Q1,04 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY                                  |                    |   111 |  9879 |  2219   (6)| 00:00:01 |       |       |  Q1,04 | PCWP |            |
|   4 |     PX RECEIVE                                    |                    |   111 |  9879 |  2219   (6)| 00:00:01 |       |       |  Q1,04 | PCWP |            |
|   5 |      PX SEND HASH                                 | :TQ10003           |   111 |  9879 |  2219   (6)| 00:00:01 |       |       |  Q1,03 | P->P | HASH       |
|   6 |       HASH GROUP BY                               |                    |   111 |  9879 |  2219   (6)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|   7 |        HASH JOIN                                  |                    |   536 | 47704 |  2218   (6)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|   8 |         PX RECEIVE                                |                    |   536 | 38056 |  2215   (6)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|   9 |          PX SEND HYBRID HASH                      | :TQ10002           |   536 | 38056 |  2215   (6)| 00:00:01 |       |       |  Q1,02 | P->P | HYBRID HASH|
|  10 |           STATISTICS COLLECTOR                    |                    |       |       |         |             |       |       |  Q1,02 | PCWC |            |
|  11 |            HASH JOIN                              |                    |   536 | 38056 |  2215   (6)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  12 |             BUFFER SORT                           |                    |       |       |         |             |       |       |  Q1,02 | PCWC |            |
|  13 |              JOIN FILTER CREATE                   | :BF0000            |   236 |  3776 |     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  14 |               PX RECEIVE                          |                    |   236 |  3776 |     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  15 |                PX SEND BROADCAST                  | :TQ10000           |   236 |  3776 |     2   (0)| 00:00:01 |       |       |        | S->P | BROADCAST  |
|  16 |                 PARTITION RANGE SINGLE            |                    |   236 |  3776 |     2   (0)| 00:00:01 | 36774 | 36774 |        |      |            |
|  17 |                  INDEX FAST FULL SCAN             | PSAPSTREESELECT10  |   236 |  3776 |     2   (0)| 00:00:01 | 36774 | 36774 |        |      |            |
|  18 |             JOIN FILTER USE                       | :BF0000            |  8859 |   475K|  2213   (6)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  19 |              PX BLOCK ITERATOR                    |                    |  8859 |   475K|  2213   (6)| 00:00:01 |    29 |    41 |  Q1,02 | PCWC |            |
|  20 |               MAT_VIEW REWRITE ACCESS STORAGE FULL| PS_XX_SUM_GCNSL_MV |  8859 |   475K|  2213   (6)| 00:00:01 |    29 |    41 |  Q1,02 | PCWP |      
|  21 |         BUFFER SORT                               |                    |       |       |         |             |       |       |  Q1,03 | PCWC |            |
|  22 |          PX RECEIVE                               |                    |   731 | 13158 |     3   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  23 |           PX SEND HYBRID HASH                     | :TQ10001           |   731 | 13158 |     3   (0)| 00:00:01 |       |       |        | S->P | HYBRID HASH|
|  24 |            PARTITION RANGE SINGLE                 |                    |   731 | 13158 |     3   (0)| 00:00:01 | 36773 | 36773 |        |      |            |
|  25 |             INDEX FAST FULL SCAN                  | PSAPSTREESELECT05  |   731 | 13158 |     3   (0)| 00:00:01 | 36773 | 36773 |        |      |            |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

Conclusion SQL 

Profiles can be used in much the same way as SQL Patches to introduce hints into application SQL without changing the code, the difference being that SQL Profiles can force match SQL. However, SQL Profiles do require the Tuning pack to be licenced, whereas SQL Patches do not. Some hints do not work because the profile is applied to late in the parse process. For example, if you use cursor sharing then the CURSOR_SHARING_EXACT hint will not take effect.