Configuring an Exadata (part I)

I am getting ready to do a presentation on "real world Experiences" with the exadata, and I am still in process of getting the Exadata set up. I figured I would share some of the pain we are having getting the configuration checklist ready for ACS (advanced Customer Support).. This service comes with your Exadata, and the ACS folks configure the box by installing the OS, and the database software. They even set up a starter database. This is where the fun begins. The exadata is a beast of a box. Remember in a full rack there is 8 database nodes, and 14 storage servers. Switches, power supplies.. And to support all this it needs lots and lots of IP's, and lots and lots of "names" for these pieces. When setting up an Exadata you get 4 characters for a name. For many datacenters, it is hard to get descriptive in those 4 characters, and fitting it all into 4 characters might not follow your standards. This is very rigid because of the "one" script.. If you've never heard that term, it is the "one" script that installs everything. Think of it as the silent install for the whole Exadata box.. Everything is driven off of it.. Server names, Cell disk names.. EVERYTHING. If you to add it up, there are probably 200+ items in an exadata that need to be named with this script (thus the reason why the 4 character name is so important).

Same with the database. Oracle crates a simple UTF8 database. Nothing more. And they create dbcontrol within the Exadata, nothing more.

If you are prepared it should be pretty simple to set up.

I think this is where the difference lies between the Exadata and a true appliance. An appliance, you wheel it in, hook it up and run.. An Exadata, you need to give some thought to how to set it up. The Exadata gives you a lot of flexibility (you can run multiple applications and section them off across nodes), you can create indexes for OLTP type transactions, but with that flexibility comes more complexity. It's a trade off.

Continued in part 2

DBMS_FILE_TRANFER.COPY_FILE and locking

I was working on copying an RMAN backup from an NFS mount into ASM to do a restore of a database.. I figured.. A great opportunity to use DBMS_FILE_TRANSER to copy it in !!
I copied the first file into ASM, and deleted off the mount. I then copied over my next file (I only had room for 1). BAM. I'm out of space. I look at the file system, and the space is used up (with a 'df -k'), but I couldn't file the file using up the space. I thought.. maybe dbms_file_transfer doesn't release the file handle from the OS? I shut down the database, and my space returns.

I haven't had time to play with it, but beware that using DBMS_FILE_TRANSFER holds the file open, so deleting it doesn't remove the space. I'm not sure how to release the handle.

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;