Profiles, GTT's and how changing the underlying view can cause FTS's

We are using a GTT, multiple times, in a view..


1) Just the query

---------------------------------------------------------------------------------------------------------               
| Id  | Operation                        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |               
---------------------------------------------------------------------------------------------------------               
|   0 | SELECT STATEMENT                 |                      |       |       |    10 (100)|          |               
|   1 |  NESTED LOOPS                    |                      |    30 |  9600 |    10   (0)| 00:00:01 |               
|   2 |   MERGE JOIN CARTESIAN           |                      |     1 |   316 |     9   (0)| 00:00:01 |               
|   3 |    NESTED LOOPS SEMI             |                      |     1 |   303 |     7   (0)| 00:00:01 |               
|   4 |     NESTED LOOPS SEMI            |                      |     1 |   301 |     5   (0)| 00:00:01 |               
|   5 |      NESTED LOOPS                |                      |     1 |   299 |     3   (0)| 00:00:01 |               
|   6 |       TABLE ACCESS FULL          | GTT_PRODUCTS         |     1 |    16 |     2   (0)| 00:00:01 |               
|   7 |       TABLE ACCESS BY INDEX ROWID| PRODUCT_DESCRIPTIONS |     1 |   283 |     1   (0)| 00:00:01 |               
|*  8 |        INDEX UNIQUE SCAN         | PRD_DESC_PK          |     1 |       |     0   (0)|          |               
|   9 |      VIEW PUSHED PREDICATE       | VW_NSO_2             |     1 |     2 |     2   (0)| 00:00:01 |               
|  10 |       NESTED LOOPS               |                      |     1 |    23 |     2   (0)| 00:00:01 |               
|* 11 |        TABLE ACCESS FULL         | GTT_PRODUCTS         |     1 |    16 |     2   (0)| 00:00:01 |               
|* 12 |        INDEX UNIQUE SCAN         | PRD_DESC_PK          |     1 |     7 |     0   (0)|          |               
|  13 |     VIEW PUSHED PREDICATE        | VW_NSO_1             |     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)|          |               
|  17 |    BUFFER SORT                   |                      |     1 |    13 |     7   (0)| 00:00:01 |               
|  18 |     SORT UNIQUE                  |                      |     1 |    13 |     2   (0)| 00:00:01 |               
|  19 |      TABLE ACCESS FULL           | GTT_PRODUCTS         |     1 |    13 |     2   (0)| 00:00:01 |               
|* 20 |   INDEX RANGE SCAN               | PRD_DESC_PK          |    30 |   120 |     1   (0)| 00:00:01 |               
---------------------------------------------------------------------------------------------------------               

2) with a profile (built from the query), note it is the same exact plan with a HUGE cost.

---------------------------------------------------------------------------------------------------------               
| Id  | Operation                        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |               
---------------------------------------------------------------------------------------------------------               
|   0 | SELECT STATEMENT                 |                      |       |       |    67M(100)|          |               
|   1 |  NESTED LOOPS                    |                      |  2001M|   596G|    67M  (1)|223:23:27 |               
|   2 |   MERGE JOIN CARTESIAN           |                      |    66M|    19G|   271K  (1)| 00:54:21 |               
|   3 |    NESTED LOOPS SEMI             |                      |  8168 |  2416K| 50046   (1)| 00:10:01 |               
|   4 |     NESTED LOOPS SEMI            |                      |  8168 |  2400K| 25518   (1)| 00:05:07 |               
|   5 |      NESTED LOOPS                |                      |  8168 |  2384K|   990   (1)| 00:00:12 |               
|   6 |       TABLE ACCESS FULL          | GTT_PRODUCTS         |  8168 |   127K|    29   (0)| 00:00:01 |               
|   7 |       TABLE ACCESS BY INDEX ROWID| PRODUCT_DESCRIPTIONS |     1 |   283 |     1   (0)| 00:00:01 |               
|*  8 |        INDEX UNIQUE SCAN         | PRD_DESC_PK          |     1 |       |     0   (0)|          |               
|   9 |      VIEW PUSHED PREDICATE       | VW_NSO_2             |     1 |     2 |     3   (0)| 00:00:01 |               
|  10 |       NESTED LOOPS               |                      |     1 |    23 |     3   (0)| 00:00:01 |               
|* 11 |        TABLE ACCESS FULL         | GTT_PRODUCTS         |     3 |    48 |     3   (0)| 00:00:01 |               
|* 12 |        INDEX UNIQUE SCAN         | PRD_DESC_PK          |     1 |     7 |     0   (0)|          |               
|  13 |     VIEW PUSHED PREDICATE        | VW_NSO_1             |     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)|          |               
|  17 |    BUFFER SORT                   |                      |  8168 |   103K|   271K  (1)| 00:54:21 |               
|  18 |     SORT UNIQUE                  |                      |  8168 |   103K|    27   (0)| 00:00:01 |               
|  19 |      TABLE ACCESS FULL           | GTT_PRODUCTS         |  8168 |   103K|    27   (0)| 00:00:01 |               
|* 20 |   INDEX RANGE SCAN               | PRD_DESC_PK          |    30 |   120 |     1   (0)| 00:00:01 |               
---------------------------------------------------------------------------------------------------------      

3) With a profile (built from the query), and a change to the view

------------------------------------------------------------------------------------------------------------            
| Id  | Operation                   | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |            
------------------------------------------------------------------------------------------------------------            
|   0 | SELECT STATEMENT            |                      |       |       |       | 72496 (100)|          |            
|   1 |  NESTED LOOPS               |                      |   245K|   483M|       | 72496   (1)| 00:14:30 |            
|   2 |   FAST DUAL                 |                      |     1 |       |       |     2   (0)| 00:00:01 |            
|   3 |   VIEW                      |                      |   245K|   483M|       | 72494   (1)| 00:14:30 |            
|*  4 |    HASH JOIN                |                      |   245K|    75M|  2712K| 72494   (1)| 00:14:30 |            
|*  5 |     HASH JOIN SEMI          |                      |  8640 |  2607K|  2600K|   271   (1)| 00:00:04 |            
|   6 |      MERGE JOIN CARTESIAN   |                      |  8640 |  2497K|       |   105   (1)| 00:00:02 |            
|   7 |       VIEW                  | VW_NSO_2             |     1 |    13 |       |     2   (0)| 00:00:01 |            
|   8 |        SORT UNIQUE          |                      |     1 |    23 |       |            |          |            
|   9 |         NESTED LOOPS        |                      |     1 |    23 |       |     2   (0)| 00:00:01 |            
|  10 |          TABLE ACCESS FULL  | GTT_PRODUCTS         |     1 |    16 |       |     2   (0)| 00:00:01 |            
|* 11 |          INDEX UNIQUE SCAN  | PRD_DESC_PK          |     1 |     7 |       |     0   (0)|          |            
|  12 |       BUFFER SORT           |                      |  8640 |  2387K|       |   105   (1)| 00:00:02 |            
|  13 |        TABLE ACCESS FULL    | PRODUCT_DESCRIPTIONS |  8640 |  2387K|       |   102   (0)| 00:00:02 |            
|  14 |      VIEW                   | VW_NSO_1             |  8168 |   103K|       |    30   (4)| 00:00:01 |            
|  15 |       NESTED LOOPS          |                      |  8168 |   183K|       |    30   (4)| 00:00:01 |            
|  16 |        TABLE ACCESS FULL    | GTT_PRODUCTS         |  8168 |   127K|       |    29   (0)| 00:00:01 |            
|* 17 |        INDEX UNIQUE SCAN    | PRD_DESC_PK          |     1 |     7 |       |     0   (0)|          |            
|  18 |     VIEW                    |                      |   245K|  3828K|       | 71765   (1)| 00:14:22 |            
|* 19 |      FILTER                 |                      |       |       |       |            |          |            
|  20 |       MERGE JOIN CARTESIAN  |                      |    70M|  1346M|       | 71188   (1)| 00:14:15 |            
|  21 |        TABLE ACCESS FULL    | GTT_PRODUCTS         |  8168 |   127K|       |    29   (0)| 00:00:01 |            
|  22 |        BUFFER SORT          |                      |  8640 | 34560 |       | 71159   (1)| 00:14:14 |            
|  23 |         INDEX FAST FULL SCAN| PRD_DESC_PK          |  8640 | 34560 |       |     9   (0)| 00:00:01 |            
|* 24 |       TABLE ACCESS FULL     | GTT_PRODUCTS         |     1 |    13 |       |     2   (0)| 00:00:01 |            
------------------------------------------------------------------------------------------------------------ 

*************************************************************\

4) Now with a Baseline

---------------------------------------------------------------------------------------------------------               
| Id  | Operation                        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |               
---------------------------------------------------------------------------------------------------------               
|   0 | SELECT STATEMENT                 |                      |       |       |    10 (100)|          |               
|   1 |  NESTED LOOPS                    |                      |    30 |  9600 |    10   (0)| 00:00:01 |               
|   2 |   MERGE JOIN CARTESIAN           |                      |     1 |   316 |     9   (0)| 00:00:01 |               
|   3 |    NESTED LOOPS SEMI             |                      |     1 |   303 |     7   (0)| 00:00:01 |               
|   4 |     NESTED LOOPS SEMI            |                      |     1 |   301 |     5   (0)| 00:00:01 |               
|   5 |      NESTED LOOPS                |                      |     1 |   299 |     3   (0)| 00:00:01 |               
|   6 |       TABLE ACCESS FULL          | GTT_PRODUCTS         |     1 |    16 |     2   (0)| 00:00:01 |               
|   7 |       TABLE ACCESS BY INDEX ROWID| PRODUCT_DESCRIPTIONS |     1 |   283 |     1   (0)| 00:00:01 |               
|*  8 |        INDEX UNIQUE SCAN         | PRD_DESC_PK          |     1 |       |     0   (0)|          |               
|   9 |      VIEW PUSHED PREDICATE       | VW_NSO_2             |     1 |     2 |     2   (0)| 00:00:01 |               
|  10 |       NESTED LOOPS               |                      |     1 |    23 |     2   (0)| 00:00:01 |               
|* 11 |        TABLE ACCESS FULL         | GTT_PRODUCTS         |     1 |    16 |     2   (0)| 00:00:01 |               
|* 12 |        INDEX UNIQUE SCAN         | PRD_DESC_PK          |     1 |     7 |     0   (0)|          |               
|  13 |     VIEW PUSHED PREDICATE        | VW_NSO_1             |     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)|          |               
|  17 |    BUFFER SORT                   |                      |     1 |    13 |     7   (0)| 00:00:01 |               
|  18 |     SORT UNIQUE                  |                      |     1 |    13 |     2   (0)| 00:00:01 |               
|  19 |      TABLE ACCESS FULL           | GTT_PRODUCTS         |     1 |    13 |     2   (0)| 00:00:01 |               
|* 20 |   INDEX RANGE SCAN               | PRD_DESC_PK          |    30 |   120 |     1   (0)| 00:00:01 |               
--------------------------------------------------------------------------------------------------------- 

5) Now with a baseline built on the profile (then the profile is dropped).

---------------------------------------------------------------------------------------------------------               
| Id  | Operation                        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |               
---------------------------------------------------------------------------------------------------------               
|   0 | SELECT STATEMENT                 |                      |       |       |    67M(100)|          |               
|   1 |  NESTED LOOPS                    |                      |  2001M|   590G|    67M  (1)|223:23:27 |               
|   2 |   MERGE JOIN CARTESIAN           |                      |    66M|    19G|   271K  (1)| 00:54:21 |               
|   3 |    NESTED LOOPS SEMI             |                      |  8168 |  2400K| 50046   (1)| 00:10:01 |               
|   4 |     NESTED LOOPS SEMI            |                      |  8168 |  2384K| 25518   (1)| 00:05:07 |               
|   5 |      NESTED LOOPS                |                      |  8168 |  2369K|   990   (1)| 00:00:12 |               
|   6 |       TABLE ACCESS FULL          | GTT_PRODUCTS         |  8168 |   127K|    29   (0)| 00:00:01 |               
|   7 |       TABLE ACCESS BY INDEX ROWID| PRODUCT_DESCRIPTIONS |     1 |   281 |     1   (0)| 00:00:01 |               
|*  8 |        INDEX UNIQUE SCAN         | PRD_DESC_PK          |     1 |       |     0   (0)|          |               
|   9 |      VIEW PUSHED PREDICATE       | VW_NSO_2             |     1 |     2 |     3   (0)| 00:00:01 |               
|  10 |       NESTED LOOPS               |                      |     1 |    21 |     3   (0)| 00:00:01 |               
|* 11 |        TABLE ACCESS FULL         | GTT_PRODUCTS         |     3 |    48 |     3   (0)| 00:00:01 |               
|* 12 |        INDEX UNIQUE SCAN         | PRD_DESC_PK          |     1 |     5 |     0   (0)|          |               
|  13 |     VIEW PUSHED PREDICATE        | VW_NSO_1             |     1 |     2 |     3   (0)| 00:00:01 |               
|  14 |      NESTED LOOPS                |                      |     1 |    21 |     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 |     5 |     0   (0)|          |               
|  17 |    BUFFER SORT                   |                      |  8168 |   103K|   271K  (1)| 00:54:21 |               
|  18 |     SORT UNIQUE                  |                      |  8168 |   103K|    27   (0)| 00:00:01 |               
|  19 |      TABLE ACCESS FULL           | GTT_PRODUCTS         |  8168 |   103K|    27   (0)| 00:00:01 |               
|* 20 |   INDEX RANGE SCAN               | PRD_DESC_PK          |    30 |    90 |     1   (0)| 00:00:01 |               
---------------------------------------------------------------------------------------------------------               
                                                                                                                        
Predicate Information (identified by operation id):                                                                     
---------------------------------------------------                                                                     
                                                                                                                        
   8 - access("A"."PRODUCT_ID"="B"."PRODUCT_ID" AND "A"."LANGUAGE_ID"="B"."LANGUAGE_ID")                                
  11 - filter("D"."PRODUCT_ID"="B"."PRODUCT_ID")                                                                        
  12 - access("C"."PRODUCT_ID"="B"."PRODUCT_ID" AND "C"."LANGUAGE_ID"="D"."LANGUAGE_ID")                                
       filter("C"."PRODUCT_ID"="D"."PRODUCT_ID")                                                                        
  15 - filter("D"."PRODUCT_ID"="A"."PRODUCT_ID")                                                                        
  16 - access("C"."PRODUCT_ID"="A"."PRODUCT_ID" AND "C"."LANGUAGE_ID"="D"."LANGUAGE_ID")                                
       filter("C"."PRODUCT_ID"="D"."PRODUCT_ID")                                                                        
  20 - access("E"."PRODUCT_ID"="PRODUCT_ID")                                                                            
                                                                                                                        
Note                                                                                                                    
-----                                                                                                                   
   - SQL plan baseline SQL_PLAN_djp51r5ar8yracbfabd3a used for this statement  

Now the code snippet to make it all happen

drop TABLE oe.GTT_PRODUCTS;

CREATE GLOBAL TEMPORARY TABLE oe.GTT_PRODUCTS
(
  PRODUCT_ID   NUMBER(6),
  LANGUAGE_ID  VARCHAR2(3 BYTE)
)
ON COMMIT PRESERVE ROWS
NOCACHE;

create or replace view oe.profile_test as
select a.* from oe.product_descriptions a,
                    oe.gtt_products b
   join oe.product_descriptions e on (e.product_id in (select product_id from oe.gtt_products))
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);

insert into oe.gtt_products values (2449,'RU');

select * from oe.profile_test;


set pagesize 0
set linesize 120
spool no_profile.log

select * from table(dbms_xplan.display_cursor('7nsv5y0mnnq5m',null,'typical +peeked_binds')); 

spool off;


VAR signature NUMBER;
REM
DECLARE
sql_txt CLOB;
h       SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
select * from oe.profile_test
]';
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'[OPT_PARAM('optimizer_dynamic_sampling' 4)]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$13AC9F9F")]',
q'[PUSH_PRED(@"SEL$1C4CCF65" "VW_NSO_2"@"SEL$1C4CCF65" 5)]',
q'[OUTLINE_LEAF(@"SEL$24A0192D")]',
q'[PUSH_PRED(@"SEL$1C4CCF65" "VW_NSO_1"@"SEL$1C4CCF65" 4)]',
q'[OUTLINE_LEAF(@"SEL$1C4CCF65")]',
q'[UNNEST(@"SEL$3")]',
q'[UNNEST(@"SEL$5")]',
q'[UNNEST(@"SEL$6")]',
q'[OUTLINE(@"SEL$CE1D94FA")]',
q'[OUTLINE(@"SEL$1C4CCF65")]',
q'[UNNEST(@"SEL$3")]',
q'[UNNEST(@"SEL$5")]',
q'[UNNEST(@"SEL$6")]',
q'[OUTLINE(@"SEL$F495C89B")]',
q'[OUTLINE(@"SEL$110C15E8")]',
q'[MERGE(@"SEL$5BF935F8")]',
q'[OUTLINE(@"SEL$3")]',
q'[OUTLINE(@"SEL$5")]',
q'[OUTLINE(@"SEL$6")]',
q'[OUTLINE(@"SEL$1")]',
q'[OUTLINE(@"SEL$5BF935F8")]',
q'[MERGE(@"SEL$2")]',
q'[OUTLINE(@"SEL$4")]',
q'[OUTLINE(@"SEL$2")]',
q'[FULL(@"SEL$1C4CCF65" "B"@"SEL$2")]',
q'[INDEX_RS_ASC(@"SEL$1C4CCF65" "A"@"SEL$4" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))]',
q'[NO_ACCESS(@"SEL$1C4CCF65" "VW_NSO_2"@"SEL$1C4CCF65")]',
q'[NO_ACCESS(@"SEL$1C4CCF65" "VW_NSO_1"@"SEL$1C4CCF65")]',
q'[FULL(@"SEL$1C4CCF65" "GTT_PRODUCTS"@"SEL$3")]',
q'[INDEX(@"SEL$1C4CCF65" "E"@"SEL$2" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))]',
q'[LEADING(@"SEL$1C4CCF65" "B"@"SEL$2" "A"@"SEL$4" "VW_NSO_2"@"SEL$1C4CCF65" "VW_NSO_1"@"SEL$1C4CCF65" "GTT_PRODUCTS"@"SEL$3" "E"@"SEL$2")]',
q'[USE_NL(@"SEL$1C4CCF65" "A"@"SEL$4")]',
q'[USE_NL(@"SEL$1C4CCF65" "VW_NSO_2"@"SEL$1C4CCF65")]',
q'[USE_NL(@"SEL$1C4CCF65" "VW_NSO_1"@"SEL$1C4CCF65")]',
q'[USE_MERGE(@"SEL$1C4CCF65" "GTT_PRODUCTS"@"SEL$3")]',
q'[USE_NL(@"SEL$1C4CCF65" "E"@"SEL$2")]',
q'[FULL(@"SEL$13AC9F9F" "D"@"SEL$6")]',
q'[INDEX(@"SEL$13AC9F9F" "C"@"SEL$6" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))]',
q'[LEADING(@"SEL$13AC9F9F" "D"@"SEL$6" "C"@"SEL$6")]',
q'[USE_NL(@"SEL$13AC9F9F" "C"@"SEL$6")]',
q'[FULL(@"SEL$24A0192D" "D"@"SEL$5")]',
q'[INDEX(@"SEL$24A0192D" "C"@"SEL$5" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))]',
q'[LEADING(@"SEL$24A0192D" "D"@"SEL$5" "C"@"SEL$5")]',
q'[USE_NL(@"SEL$24A0192D" "C"@"SEL$5")]',
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_7nsv5y0mnnq5m_1263200402',
description => 'coe 7nsv5y0mnnq5m 1263200402 '||: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;
/



alter system flush shared_pool;


select * from oe.profile_test;

set pagesize 0
set linesize 120
spool profile.log

select * from table(dbms_xplan.display_cursor('7nsv5y0mnnq5m',null,'typical +peeked_binds')); 
spool off;





create or replace view oe.profile_test as
select q.* from
(
select a.* from oe.product_descriptions a,
                    oe.gtt_products b
   join oe.product_descriptions e on (e.product_id in (select product_id from oe.gtt_products))
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)) q
,sys.dual r;


alter system flush shared_pool;


select * from oe.profile_test;

set pagesize 0
set linesize 120
spool new_view.log

select * from table(dbms_xplan.display_cursor('7nsv5y0mnnq5m',null,'typical +peeked_binds')); 
spool off;



BEGIN
  DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'coe_7nsv5y0mnnq5m_1263200402');
END;
/

select * from oe.profile_test;

set pagesize 0

set linesize 120

spool new_view.log



select * from table(dbms_xplan.display_cursor('7nsv5y0mnnq5m',null,'typical +peeked_binds'));

spool off;





DECLARE

my_plans pls_integer;

BEGIN

my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(

sql_id => '7nsv5y0mnnq5m');

END;

/

alter system flush shared_pool;





select * from oe.profile_test;



set pagesize 0

set linesize 120

spool baseline.log



select * from table(dbms_xplan.display_cursor('7nsv5y0mnnq5m',null,'typical +peeked_binds'));

spool off;

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.drop_sql_plan_baseline (
sql_handle => 'SYS_SQL_d8d4a1b955747aea');
END;
/

VAR signature NUMBER;
REM
DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
select * from oe.profile_test
]';
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'[OPT_PARAM('optimizer_dynamic_sampling' 4)]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$13AC9F9F")]',
q'[PUSH_PRED(@"SEL$1C4CCF65" "VW_NSO_2"@"SEL$1C4CCF65" 5)]',
q'[OUTLINE_LEAF(@"SEL$24A0192D")]',
q'[PUSH_PRED(@"SEL$1C4CCF65" "VW_NSO_1"@"SEL$1C4CCF65" 4)]',
q'[OUTLINE_LEAF(@"SEL$1C4CCF65")]',
q'[UNNEST(@"SEL$3")]',
q'[UNNEST(@"SEL$5")]',
q'[UNNEST(@"SEL$6")]',
q'[OUTLINE(@"SEL$CE1D94FA")]',
q'[OUTLINE(@"SEL$1C4CCF65")]',
q'[UNNEST(@"SEL$3")]',
q'[UNNEST(@"SEL$5")]',
q'[UNNEST(@"SEL$6")]',
q'[OUTLINE(@"SEL$F495C89B")]',
q'[OUTLINE(@"SEL$110C15E8")]',
q'[MERGE(@"SEL$5BF935F8")]',
q'[OUTLINE(@"SEL$3")]',
q'[OUTLINE(@"SEL$5")]',
q'[OUTLINE(@"SEL$6")]',
q'[OUTLINE(@"SEL$1")]',
q'[OUTLINE(@"SEL$5BF935F8")]',
q'[MERGE(@"SEL$2")]',
q'[OUTLINE(@"SEL$4")]',
q'[OUTLINE(@"SEL$2")]',
q'[FULL(@"SEL$1C4CCF65" "B"@"SEL$2")]',
q'[INDEX_RS_ASC(@"SEL$1C4CCF65" "A"@"SEL$4" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))]',
q'[NO_ACCESS(@"SEL$1C4CCF65" "VW_NSO_2"@"SEL$1C4CCF65")]',
q'[NO_ACCESS(@"SEL$1C4CCF65" "VW_NSO_1"@"SEL$1C4CCF65")]',
q'[FULL(@"SEL$1C4CCF65" "GTT_PRODUCTS"@"SEL$3")]',
q'[INDEX(@"SEL$1C4CCF65" "E"@"SEL$2" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))]',
q'[LEADING(@"SEL$1C4CCF65" "B"@"SEL$2" "A"@"SEL$4" "VW_NSO_2"@"SEL$1C4CCF65" "VW_NSO_1"@"SEL$1C4CCF65" "GTT_PRODUCTS"@"SEL$3" "E"@"SEL$2")]',
q'[USE_NL(@"SEL$1C4CCF65" "A"@"SEL$4")]',
q'[USE_NL(@"SEL$1C4CCF65" "VW_NSO_2"@"SEL$1C4CCF65")]',
q'[USE_NL(@"SEL$1C4CCF65" "VW_NSO_1"@"SEL$1C4CCF65")]',
q'[USE_MERGE(@"SEL$1C4CCF65" "GTT_PRODUCTS"@"SEL$3")]',
q'[USE_NL(@"SEL$1C4CCF65" "E"@"SEL$2")]',
q'[FULL(@"SEL$13AC9F9F" "D"@"SEL$6")]',
q'[INDEX(@"SEL$13AC9F9F" "C"@"SEL$6" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))]',
q'[LEADING(@"SEL$13AC9F9F" "D"@"SEL$6" "C"@"SEL$6")]',
q'[USE_NL(@"SEL$13AC9F9F" "C"@"SEL$6")]',
q'[FULL(@"SEL$24A0192D" "D"@"SEL$5")]',
q'[INDEX(@"SEL$24A0192D" "C"@"SEL$5" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))]',
q'[LEADING(@"SEL$24A0192D" "D"@"SEL$5" "C"@"SEL$5")]',
q'[USE_NL(@"SEL$24A0192D" "C"@"SEL$5")]',
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_7nsv5y0mnnq5m_1263200402',
description => 'coe 7nsv5y0mnnq5m 1263200402 '||: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;
/


alter system flush shared_pool;


select * from oe.profile_test;


DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '7nsv5y0mnnq5m');
END;
/
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'coe_7nsv5y0mnnq5m_1263200402');
END;
/
alter system flush shared_pool;


select * from oe.profile_test;

set pagesize 0


set linesize 120
spool baseline_on_dropped_profile.log

select * from table(dbms_xplan.display_cursor('7nsv5y0mnnq5m',null,'typical +peeked_binds'));
spool off;

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

How to view sql_profiles

I am using profiles for some very stubborn queries. I wanted to know what the profile really looks like.
It looks like Oracle has significantly changed the way it stores profiles with 11g.. Here is the query I am using to view what is out there for them.. Enjoy.

select name,hint,sql_text

from (
SELECT extractValue(value(h),'.') AS hint,
od.signature
FROM sys.sqlobj$data od, sys.sqlobj$ so,
table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
WHERE so.signature = od.signature
AND so.category = od.category
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id
) a,
dba_sql_profiles b
where a.signature=b.signature

SQL profiles

I have been spending a lot of time with SQL_PROFILES, and figuring out how my profile went stale (and subsequently the performance went from .04 seconds to 100 seconds).


First I know profiles can go bad because of cardinality. Kerry Osborne just did a great blog on this subject, and he talks about how profiles built by the SQL_ADVISOR use opt_estimate.  How to lock SQL_PROFILES created by SQL_ADVISOR. Here is a snippet of what you would see


OPT_ESTIMATE(@"SEL$5DA710D3", INDEX_FILTER, "F"@"SEL$1", IDX$$_1AA260002, SCALE_ROWS=8.883203639e-06)


The issues I've been having come from profiles created by SQLT.  SQLT, can create profiles for you from a previous SQL statement that runs properly.  The profile is named COE_{sql_id}_{plan_hash_value}. 


The first thing I noticed is that I use GTT (global temporary tables) a lot.. In fact during some of the queries that have profiles, the same GTT is called 17 times.  The GTT is primed  with only a single row and dynamic sampling is utilized (there are no stats on the table).  The second thing I noticed is that we have queries, that look simple (a join of 2 objects), but they really call views.  I see the profile is built on a query, not on the views (more later).


So where did I start ?  First I looked at the explain plan for a database that was using the plan I want.  I know that my GTT (tmp_gcc_id) has 1 row in it. 

Operation                                                                          Name                  cardinality
TABLE ACCESS FULL                                                   TMP_GCC_ID      1   

Then later in the plan

NESTED LOOPS                                                                                        179696
     MERGE JOIN CARTESIAN                                                                      8168
         TABLE ACCESS FULL                                         TMP_GCC_ID        8168
           TABLE ACCESS BY INDEX ROWID              order                           1
              INDEX RANGE SCAN                                    idx_order                  22


The  interesting thing is that the cardinality is 8168 for the GTT for the rest of Explain plan.. This dramitically inflluences the cost, and cardinality estimates throughout.  Oracle however does use the profile and keeps the plan to be what I want it to be.  Now I look at the database where the profile is causing performance issues.
Operation                                                                    Name                  cardinality
TABLE ACCESS  FULL                                       TMP_GCC_ID         1




then later in the plan


As far as ADG (active data guard), baselines can't be created. They require a sql_tuning_set which gets created in the database.


Baselines have long way to go to make them as portable as profiles, and profiles have a long way to go to make them as stable as baselines.  I hope there is migration path from profiles to baselines, but I haven't heard any rumblings yet.NESTED LOOPS                                                                                        238,125
     MERGE JOIN CARTESIAN                                                                      8168
         TABLE ACCESS FULL                                         TMP_GCC_ID        8168
           TABLE ACCESS FULL                                     ORDER            534,285
    

I think my problem with the profile causing a FTS on a large table is 2 fold.


GTT  -- SInce I am using a GTT, with dynamic sampling, oracle is only using dynamic sampling the first time it encounters the GTT in the query (from what I can gather).. All the subsequent subselects utilizing the GTT default to a cardinality of 8168, and follow that through..
This works fine when the profile is recognized, and working.. Once the profile starts to fail, the bad cardinality drives FTS where they shouldn't be.

PROFILES on Queries with Underlying views -  My query that I built the profile on has an underlying view that does most of the work.. The view was modified, and the order of the tables accessed was changed.  This was enough to throw off the profile.. The profile is built on the SEL$ names that oracle assigns to each section of the query.  Since the query didn't change, the profile was used.  And since the SEL$ names no longer matched the new view, the profile couldn't be properly utilized.  Since the profile wasn't properly utilzed, oracle defaulted to figuring out the best plan.. GTT's cause issues because they didn't dynamic sample every time.  The default cardinality of the GTT (8168) was enough to cause full table scans.

The moral of the story is that profiles can go bad 2 ways.

1) if they use any sort of cardinality hint (like Kerry mentions).

2) You change an underlying object (like a view), that doesn't change the query text.

Now the next topic is Baselines.  I tried to use baselines (to save myself all this trouble with profiles), and I had no luck.. We have multiple databases with the same schema, but different login ID's.  In order to utilize baselines you have to create SQL_TUNING_SETS.   Creating a SQL_TUNING_SET is much more difficult than just creating a SQL_PROFILE from an existing plan.  The other issue I have had is that we already have profiles on our queries that cause us trouble.. Creating a baselines, relies on the profile.. Create a baseline, remove the profile, and the baseline fails.,. This dependency to the profile makes it very difficult to replace our current profiles.

Lastly baselines have a column called "parsing_schema_name".. I love the idea that you can move baselines between databases (and promote them as part of your code), but we don't have the same parsing_schema_name in all our database.. I try to import the baseline, and it fails because the parsing_schema_name doesn't exist in the database.