How to utilize AWR beyond what grid gives you

Like many of you I use AWR reports all the time to see what is happening in the database, and especially to compare between different points in time.. Whenever the users tell me the system is "running slow", I always try to find a comparison time.. "what other day recently has a similar workload as today", and run an AWR Comparison between the time periods. Through Grid (or dbconsole), this can be frustrating for a RAC database. Sometimes you want to compare between nodes for the same period, or different periods across nodes. This is only the beginning.. I also want to compare across databases.

As you can guess the reports available through the grid don't give you these choices. But guess what ? DBReplay gives you these choices. In order to support DBReplay, Oracle has added some pretty nifty features into AWR.

First a non-dbreplay feature, the "colored SQL".

As many of you know, Oracle only saves the top 50 SQL of interest. Of course you can change the number of top sql saved with the MODIFY_SNAPSHOT_SETINGS procedure.

dbms_workload_repository.modify_snapshot_settings(
retention IN NUMBER DEFAULT NULL,
interval IN NUMBER DEFAULT NULL,
topnsql IN NUMBER DEFAULT NULL,
dbid IN NUMBER DEFAULT );


The problem is, what happens if you are interested in a SQL that isn't in the top sql statements ? If you have a very active system, you might find that the sql isn't in the top 50, or even the 100. So what is there to do ? "color the sql". Use this procedure to mark sql to gather information about the SQL_ID every snapshot even if it isn't a top sql. Here is the description

Routine to add a colored SQL ID. If an SQL ID is colored, it will always be captured in every snapshot, independent of its level of activities (i.e. does not have to be a TOP SQL). Capturing will occur if the SQL is found in the cursor cache at snapshot time

dbms_workload_repository.add_colored_sql(
sql_id IN VARCHAR2,
dbid IN NUMBER DEFAULT NULL);

Now onto my original problem.

How do I do comparisons against instances or even database ? Well when I took a good look at the AWR_DIFF_REPORT_HTML procedure, I noticed that you can call it with multiple parameters (like instance ID and dbid).

dbms_workload_repository.awr_diff_report_html(
dbid1 IN NUMBER,
inst_num1 IN NUMBER,
bid1 IN NUMBER,
eid1 IN NUMBER,
dbid2 IN NUMBER,
inst_num2 IN NUMBER,
bid2 IN NUMBER,
eid2 IN NUMBER)
RETURN awrrpt_html_type_table PIPELINE;

If you run awr_diff_report with the 2 different instances you compare them against each other. Now how do you do database ? Look at metallink note 872733.1 (yes I still call it metalink). This note tells you how to export the AWR information into a file, and import it into a new database.

Here is my plan to handle all this.

1) Create a centralized repository of all AWR information for all database in the infrastructure..

2) Follow the metalink note to export/import data into the centeralized repository

3) In the centralized repository create a table mapping DBID to source database.

4) Configure APEX (application Express), to pick DBID's, Instances, and snapshot ranges to run a comparison report. Display the report in APEX as a webpage.

This is my summer project, and I will let everyone know how it goes.

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;

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.