Monday, December 27, 2010

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;

No comments:

Post a Comment