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.

Oracle on ASM

I discovered an interesting little by product of running Oracle on ASM.. Our SGA was sized a little small on a non-production box.  All of a sudden the Users started seeing some I/O errors.

Error: Selecting clients for minimum billing ORA-01115: IO error reading block
from file (block # )


ORA-01110: data file 49: '+DATA/orcl/data_128k_dt01.dbf'
ORA-15081: failed to submit an I/O operation to a disk
ORA-15081: failed to submit an I/O operation to a disk

Of course I looked at the sytem logs to find out if there was the dreaded corruption.. but nothing shows up.. I look in the alert log and I see

ORA-04031: unable to allocate 3240 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","FileOpenBlock")



ERROR: error ORA-4031 caught in ASM I/O path

 
 
Hmm.. looks like Oracle not being able to get to the ASM path causes I/O error messages.

SCAN (single client access name)

If you didn't know it, there is a new feature in 11.2 Grid called SCAN.  Here is a link to a little more information http://www.orafaq.com/node/2369 .

The reason I thought I should mention this, is that scan is a new concept you have to deal with when you upgrade/install 11.2 grid.. There is no getting around it in the installation.  It is possible to disable it however once you install it.

My suggestion on using it, is to move slowly.. There is still a lot of old clients out there that have hard time with scan, so you might find that your client won't connect the "new way"..  It is also very persnickity,.. I've seen some installations where the DNS resolution (files,dns), has thrown it off too.. For a new application, you can work through issues as you get ready to deploy, but for existing apps I would recommend you plan some time in your upgrade schedule to work through issues (or plan on disabling it).

Just  a word to the wise :)

Concurrency and parallelism

I've had a lot of discussions with some very "seasoned" professions on how to handle a high level concurrency. Most of these professions point to the new features of 11gr2 and and parallization..

True 11gr2 has added a new way of handling degree of parallization.

There are some new parameters

parallel_degree_policy
PARALLEL_MIN_TIME_THRESHOLD

These control how parallism is handled.. They can be used to actually create a funnel to ensure the system isn't flooded.

The problem is all this, is that parallelism has a price.. Take a small efficient query, and turn on these parameters.. Guess what happens when you ramp up and run 500 of the same query concurrently ? You see much lower throughput (I've seen as much as 10x lower throughput). Why ?? The overhead of parallel query can be quite high, and can consume more time than even CPU in your AWR report.

Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
enq: JX - SQL statement queue            71       7,815 1.E+05   99.3 Scheduler 
DB CPU                                               96           1.2           
PX Nsq: PQ load info query               46           9    201     .1 Other     
enq: RD - RAC load                       90           2     20     .0 Other     
PX Deq: reap credit                 152,105           1      0     .0 Other

The moral of the story is, parallism is good for longer queries.. For shorter queries your milege may vary

Adaptive Direct Path Reads

This is one of my favorite topics, as it keeps coming up.

Well as you read the title, you are probably going huh ?? Until you upgrade to 11g, you probably just think direct reads are for reading temp only.. Well it all changes with 11g. Full table scans of large tables turn from "db scattered read" to "direct path read"..


What does this mean ?? well the good news is it runs about 4 times faster than the old fasion reads.. How does it do this ?? It bypasses the SGA.. Is this good ? probably.. especially for full table scans.. it doesn't force anything out of your buffer cache, and you get faster reads. The only concern I would have is if you wanted to read a lot of data into memory, I don't know how to burn things into cache. I tried to disable it to do timings, but no luck. Even with the optimizer set back to 10g it still does direct path reads.

Here is some great information on it.

http://afatkulin.blogspot.com/2009/01/11g-adaptive-direct-path-reads-what-is.html

http://shallahamer-orapub.blogspot.com/2010/01/mystery-surrounding-11g-and-direct.html

I've had some bad experiences with Direct path reads, and concurrency. If you think about lots and lots concurrent sessions, doing direct path reads you start to imagine how this can reek havoc. None of these sessions share the results of the read, and they all independently read disk blocks.. Eventually you run out of runway for I/O.

Happy tuning.

previous blog posts

If anyone is interested in my previous blog posts, you can find them at

http://www.unyoug.com/forums/viewforum.php?f=1

Infiniband coming to a town near you

As the Exadata takes off (I've heard it is REALLY taking off), more and more vendors are opening their eyes to the bottleneck of I/O.

You are going to see some other solutions come to the market that are infiniband based. You are also going to see more solutions like the Storage Cell's.. Just look at the IBM XIV ! they have storage cells just like the exadata with large caches. The concept is catching on..

But what does the Exadata have besides the obvious ? It has the ability to parallelize the I/O at the storage level.. I'm sure you going yea. I knew that.. but think about it.

a) Exadata.. run a non-parallel query that does a FTS on a 5tb table. You will marshall all the resources of the I/O from a single query on a single NODE

b) XXXXXX.. Run a non-parallel query, and you only will be able to marshall all the I/O that the single CPU can handle.

Sure you can build an Exadata like solution, but in order to utilze the power of the storage/infiniband subsystem you need to parallelize across multiple CPU's.. This uses a lot of CPU's, and parallization might not be the best plan for all queries.

In my mind, this is big bonus of the exadata.. Parallize or not, you can do FTS's at 20.8g/s

Exadata and indexes

This has been a very interesting topic around my shop.. Some people say that you can get rid of all you indexes, some people say no..

Well first lets look at why you have indexes and rule out those as removal candidates.

1) Indexes that support Primary keys. Gotta keep those right ?

2) Indexes that support RI to avoid locking. OLTP ? Gotta keep those.

For a lot of OLTP applications, just the 2 above criteria is enough to keep most of your indexes in play. But what about everything else.

Here is what I've been seeing. The exadata can scan like crazy, but there is a limit (20.8 on a full rack, do the math for your configuration). If you have a FTS on a table containing 50G, you can see that you utilizing ALL I/O for almost 3 seconds. if you have any concurrency, you can imagin what happens.

So in my mind the answer is to keep indexes where they can significantly limit the data access.

Concurrency.

Now that I've had a few beers, and few cups of coffee, I've had time to arrange brain cells in the right trays.. This is what I've found on Concurency with a table doing a FTS.

First.. single query.. 33g. 1/2 rack does 10.4g/second as advertised.. the single query doing a FTS runs in 3.3 seconds (or so).

Now scale up to 10 processes.. The 10 processes all scour 33.g gig apiece, the time goes up. The secret is to cut down the I/O requests at the DB layer to limit the data scoured.

Concurrency on the Exadata

Now that I have some benchmarks, I'm starting to delve into some testing to find out how it scales up.. I started with a large table 200+ million rows.

My base query did a FTS and returned one row of data.


1 execution runs in 3 seconds (DOP 32).

Once I scale up to 100 simultaneus executions, it runs longer, but I can't figure out the average execution time (parallel query skews the numbers).

In looking at the resource usage for both the database nodes and the storage nodes, I found the database nodes are almost Idle, and the storage nodes (7 of them) are producing about 10g of data/second. The cpu usage is about 7% user and 30% wait. When looking at the AWR information, all the time is still going to I/O waits. 399 seconds out of the 444 seconds are I/O wait times.. It appears that the Exadata does fantastic for a single query.. Once you execute that single query 100 times simultaneously, the times start to slow down.

I'm going to do more experients to see how I can get it to scale up nicer :)

Exadata storage Software

How else should I spend a Friday night, other than drinking hard cider, and running performance numbers on the Exadata storage software.

This is my dive into the storage software and WOW is it impressive.. I am selecting from a 200+ million row table (no indexes).. Without storage software it takes 3 minutes to scan the whole table.. really impressive. Then with the storage indexing it takes 30 seconds to come back with a distinct column value. 6 times faster..

Then I was really impressed when I used a Unique key lookup.. No index, it took 8 seconds to find the data, compared to 189 seconds. 23x faster with the Storage software.

Next I made the table parallel 64.. Now it comes back in 3 seconds (no storage software), and 1 second with storage software.. Unbelievable numbers.

One of the first things I noticed is that the Exadata makes you rethink your redo log sizes. When loading data a lot of my waits are waiting on the redo to flush out because it is so small.

All in all the storage software looks pretty impressive.

Rear view of an Exadata

Why HCC is exadata only

First the Physics.

The SAS drives spinning at 15k rpm's can produce 200m of data/second.Hypbrid Columnar compression get's on average 50x compression rate.200m x 50x = 10g of data PER DISK is read.

There are 100+ disks that can be read. This causes 2 issues.

a) The data is actually compressed/uncompressed at the storage tier. All the CPU's in the storage servers are utilized to make this happen.. Only the exadata can take advantage of the storage CPU's through the storage software

b) The data that is uncompressed is huge.. The disk can return 20.8g of data per second, but if you do get 50x compression, you are now trying to work with 1tb of disk/second..

Even if you are running infiniband, the system can't handle this volume of data.. The predicate elimination, and column eliminate will limit the data returned from the storage tier, making the processes of the data possible.
Without the storage software along with the CPU's at the storage level uncompressing data AND eliminating data, it is impossible to process the volume of data produced from HCC.

DBreplay AKA RAT (real application testing)

I have been spending a lot of time lately working with DBReplay.. This is an AWESOME arrow to have in your quiver to test upgrading to 11g.

I have made some intersting observations I want to share..

• When you start your replay, remember that your cache is "cold", if you started your capture with a warm cache, it will take a bit for the cache to catch up.
• If you started your capture during processing, you will see a lot of divergence.. This is normal
• A single query taking longer can make a huge impact on the replay (I will explain in detail below).
• Replaying twice in a row will not have the same results, but the results should be withing 5% of each other.

So while does a single query make sunch a huge impact ??? It all has to do with how the replay synch all the workload. You may have 100 or more different sessions all acting independently, but Oracle keeps it all in synch by SCN number.. This is good right ? Well, yes, but it can affect the replay. What happens if a query that usually takes .04 seconds, and is followed by an update, takes 5 minutes ? Well the replay gets held up by 5 minutes, because the SCN doesn't move until the query is finished.. Multiply that by12 executions, and you've lost an hour out of your replay.. WOW.

The best suggestion I have is to look at the DB time, the CPU time, and the reads. You may find that "overall" the replay used less DB time, even though it took longer to replay.

Direct path Reads

I am in process of tracking down as much as I can about the 11g new feature called "adaptive direct path reads". Direct path reads are block reads that bypass the SGA, and go directly into the PGA. I'm still trying to figure out if they are good.

From what I can figure out, they kick in for Full Table Scans (FTS), and only in certain cases.. You can turn them off with event 10949. They are affected by the size of the buffer cache, how big the table is, and how many blocks for the table are already in memory. Oracle has a "secret sauce" to determine when to kick in. I haven't had any luck finding the "secret sauce", just information from others on what the ingredients are, not the amounts of each.

There are advantages to them

• They are much faster than conventional db file sequential read
• They won't age blocks out of memory that you might want to keep

Disadvantages

• If the same query is executed again, the blocks must be read from disk again.
• Running a FTS on a table in multiple sessions won't share blocks, and can cause a large amount of I/O.

I also found that an object is much more likely to be using direct path reads immediately after Startup, since there are no blocks currently cached (thanks Jonathon Lewis for this tidbit!)

Here is as much additional information as I can find on this topic

• http://oraclue.com/2009/07/17/direct-path-reads-and-serial-table-scans-in-11g/
• http://afatkulin.blogspot.com/2009/01/11g-adaptive-direct-path-reads-what-is.html
• http://dioncho.wordpress.com/2009/07/21/disabling-direct-path-read-for-the-serial-full-table-scan-11g/

I also stated asking some Exadata experts, on how Direct path reads relate to exadata. It seems that with Exadata, with Caching at both the DB layer, and the storage layer, along with the infiniband connection to storage, Direct path reads are not an issue. Exadata will cache at enough layers, that a Direct path read from the flashcache on the storage is fast enough to make it worthwhile.. It's almost as is Exadata was made to best utilize direct path reads, and other storage configurations will suffer from bottlenecking... Coincidence ?? you decide.

I will let all know how I make out with them.