SQL profiles and Dynamic Sampling

I've been playing some more with profiles, and I came up with a reproduciple test case.

1) Start with the OE schema in the database

2) Create a GTT table in the schema

CREATE GLOBAL TEMPORARY TABLE GTT_PRODUCTS

(PRODUCT_ID NUMBER(6),LANGUAGE_ID VARCHAR2(3 BYTE)
) ON COMMIT PRESERVE ROWS NOCACHE;

3) run this query

select * from oe.product_descriptions a,

                    oe.gtt_products b
where a.product_id=b.product_id
     and a.language_id=b.language_id
    and a.product_id in
   (select c.product_id from oe.product_descriptions c,
                                          oe.gtt_products d
     where c.product_id=d.product_id
          and c.language_id=d.language_id)
        and b.product_id in
           (select c.product_id from oe.product_descriptions c,
                                                 oe.gtt_products d
               where c.product_id=d.product_id
                  and c.language_id=d.language_id);

You will see the following




Plan hash value: 2790750670------------------------------------------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT     |      |      | | 7 (100)| |
| 1 | NESTED LOOPS SEMI    |      | 1    | 314 | 7 (0)| 00:00:01 |
| 2 | NESTED LOOPS         |      | 1    | 312 | 5 (0)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN |      | 1 | 29 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL    | GTT_PRODUCTS | 1 | 16 | 2 (0)| 00:00:01 |
| 5 | BUFFER SORT          |      | 1 | 13 | 2 (0)| 00:00:01 |
| 6 | VIEW                 | VW_NSO_1 | 1 | 13 | 2 (0)| 00:00:01 |
| 7 | HASH UNIQUE          |      | 1 | 23 | | |
| 8 | NESTED LOOPS         |      | 1 | 23 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL    | GTT_PRODUCTS | 1 | 16 | 2 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN  | PRD_DESC_PK | 1 | 7 | 0 (0)| |
| 11 | TABLE ACCESS BY INDEX ROWID| PRODUCT_DESCRIPTIONS | 1 | 283 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN  | PRD_DESC_PK | 1 | | 0 (0)| |
| 13 | VIEW PUSHED PREDICATE      | VW_NSO_2 | 1 | 2 | 2 (0)| 00:00:01 |
| 14 | NESTED LOOPS        |      | 1 | 23 | 2 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL  | GTT_PRODUCTS | 1 | 16 | 2 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN  | PRD_DESC_PK | 1 | 7 | 0 (0)| |
------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------

10 - access("C"."PRODUCT_ID"="D"."PRODUCT_ID" AND "C"."LANGUAGE_ID"="D"."LANGUAGE_ID")
12 - access("A"."PRODUCT_ID"="PRODUCT_ID" AND "A"."LANGUAGE_ID"="B"."LANGUAGE_ID")
filter("A"."PRODUCT_ID"="B"."PRODUCT_ID")
15 - filter("D"."PRODUCT_ID"="B"."PRODUCT_ID")
16 - access("C"."PRODUCT_ID"="B"."PRODUCT_ID" AND "C"."LANGUAGE_ID"="D"."LANGUAGE_ID")
filter("C"."PRODUCT_ID"="D"."PRODUCT_ID")

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

Now create a profile to keep this plan locked in






VAR signature NUMBER;
REM
DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
select * from oe.product_descriptions a,
oe.gtt_products b
where a.product_id=b.product_id
and a.language_id=b.language_id
and a.product_id in
(select c.product_id from oe.product_descriptions c,
oe.gtt_products d
where c.product_id=d.product_id
and c.language_id=d.language_id)
and b.product_id in
(select c.product_id from oe.product_descriptions c,
oe.gtt_products d
where c.product_id=d.product_id
and c.language_id=d.language_id)
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.1')]',
q'[DB_VERSION('11.2.0.1')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$2AD7F9D9")]',
q'[PUSH_PRED(@"SEL$CC7EC59E" "VW_NSO_2"@"SEL$CC7EC59E" 5)]',
q'[OUTLINE_LEAF(@"SEL$683B0107")]',
q'[OUTLINE_LEAF(@"SEL$CC7EC59E")]',
q'[UNNEST(@"SEL$2")]',
q'[UNNEST(@"SEL$3")]',
q'[OUTLINE(@"SEL$291F8F59")]',
q'[OUTLINE(@"SEL$CC7EC59E")]',
q'[UNNEST(@"SEL$2")]',
q'[UNNEST(@"SEL$3")]',
q'[OUTLINE(@"SEL$2")]',
q'[OUTLINE(@"SEL$1")]',
q'[OUTLINE(@"SEL$3")]',
q'[FULL(@"SEL$CC7EC59E" "B"@"SEL$1")]',
q'[NO_ACCESS(@"SEL$CC7EC59E" "VW_NSO_1"@"SEL$CC7EC59E")]',
q'[FULL(@"SEL$CC7EC59E" "A"@"SEL$1" "PRODUCT_DESCRIPTIONS")]',
q'[NO_ACCESS(@"SEL$CC7EC59E" "VW_NSO_2"@"SEL$CC7EC59E")]',
q'[LEADING(@"SEL$CC7EC59E" "B"@"SEL$1" "VW_NSO_1"@"SEL$CC7EC59E" "A"@"SEL$1" "VW_NSO_2"@"SEL$CC7EC59E")]',
q'[USE_MERGE(@"SEL$CC7EC59E" "VW_NSO_1"@"SEL$CC7EC59E")]',
q'[USE_NL(@"SEL$CC7EC59E" "A"@"SEL$1")]',
q'[USE_NL(@"SEL$CC7EC59E" "VW_NSO_2"@"SEL$CC7EC59E")]',
q'[FULL(@"SEL$2AD7F9D9" "D"@"SEL$3")]',
q'[FULL(@"SEL$2AD7F9D9" "C"@"SEL$3")]',
q'[LEADING(@"SEL$2AD7F9D9" "D"@"SEL$3" "C"@"SEL$3")]',
q'[USE_NL(@"SEL$2AD7F9D9" "C"@"SEL$3")]',
q'[FULL(@"SEL$683B0107" "D"@"SEL$2")]',
q'[FULL(@"SEL$683B0107" "C"@"SEL$2")]',
q'[LEADING(@"SEL$683B0107" "D"@"SEL$2" "C"@"SEL$2")]',
q'[USE_NL(@"SEL$683B0107" "C"@"SEL$2")]',
q'[USE_HASH_AGGREGATION(@"SEL$683B0107")]',
q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'coe_877zbrarkvw1a_2790750670',
description => 'coe 877zbrarkvw1a 2790750670 '||:signature||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END;
/

Now run the query again, and look at the cost .. WOW. Same query. 8 Million instead of 7.





Plan hash value: 2790750670

------------------------------------------------------------------------------------------------------
| Id | Operation                       | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |            | | | 8784K(100)| |
| 1 | NESTED LOOPS SEMI |           | 231K| 69M| 8784K (1)| 29:16:50 |
| 2 | NESTED LOOPS           |           | 231K| 68M| 8088K (1)| 26:57:42 |
| 3 | MERGE JOIN CARTESIAN |    | 66M| 1845M| 241K (2)| 00:48:17 |
| 4 | TABLE ACCESS FULL | GTT_PRODUCTS | 8168 | 127K| 29 (0)| 00:00:01 |
| 5 | BUFFER SORT              |           | 8168 | 103K| 241K (2)| 00:48:17 |
| 6 | VIEW                             | VW_NSO_1 | 8168 | 103K| 30 (4)| 00:00:01 |
| 7 | HASH UNIQUE            |           | 288 | 183K| | |
| 8 | NESTED LOOPS          |           | 8168 | 183K| 30 (4)| 00:00:01 |
| 9 | TABLE ACCESS FULL | GTT_PRODUCTS | 8168 | 127K| 29 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PRD_DESC_PK | 1 | 7 | 0 (0)| |
| 11 | TABLE ACCESS BY INDEX ROWID| PRODUCT_DESCRIPTIONS | 1 | 283 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PRD_DESC_PK | 1 | | 0 (0)| |
| 13 | VIEW PUSHED PREDICATE | VW_NSO_2 | 1 | 2 | 3 (0)| 00:00:01 |
| 14 | NESTED LOOPS             | | 1 | 23 | 3 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | GTT_PRODUCTS | 3 | 48 | 3 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PRD_DESC_PK | 1 | 7 | 0 (0)| |
------------------------------------------------------------------------------------------------------

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

10 - access("C"."PRODUCT_ID"="D"."PRODUCT_ID" AND "C"."LANGUAGE_ID"="D"."LANGUAGE_ID")
12 - access("A"."PRODUCT_ID"="PRODUCT_ID" AND "A"."LANGUAGE_ID"="B"."LANGUAGE_ID")
filter("A"."PRODUCT_ID"="B"."PRODUCT_ID")
15 - filter("D"."PRODUCT_ID"="B"."PRODUCT_ID")
16 - access("C"."PRODUCT_ID"="B"."PRODUCT_ID" AND "C"."LANGUAGE_ID"="D"."LANGUAGE_ID")
filter("C"."PRODUCT_ID"="D"."PRODUCT_ID")

Note
-----
- SQL profile coe_877zbrarkvw1a_2790750670 used for this statement