indirect partition pruning

The nice and important thing to do when going to a conference is - beside consuming all the great presentations - talk to other attendees and exchange experiences, current issues and probable solutions. 
I am very happy I attended POUG2022! This conference is one of the best in my opinion! 

During one of the discussions I claimed Oracle can - under specific circumstances - do a partition pruning, even when the partition key isn't somewhere defined as a filter. 

It's probably easiest to show it by a simple example.

First some data to play with

CREATE TABLE RAW_DATA
   (	"ID"      NUMBER NOT NULL ENABLE, 
       	"SNAP_ID" NUMBER NOT NULL ENABLE, 
	"EVENT_TIME"  DATE   NOT NULL ENABLE, 
	"PAD1"        VARCHAR2(4000 BYTE)
   );
   
insert into RAW_DATA
select rownum
     , 1+trunc(rownum/99)
     , sysdate - (rownum/99)
     , rpad( to_char(rownum), 500, '*')
from dual
connect by level <= 10000;
commit;

CREATE TABLE SNAP 
   (	"SNAP_ID"       NUMBER NOT NULL ENABLE, 
	"BEGIN_INTERVAL_TIME" DATE NOT NULL ENABLE, 
	"END_INTERVAL_TIME"   DATE NOT NULL ENABLE, 
	 CONSTRAINT "SNAP__PK" PRIMARY KEY ("SNAP_ID") ENABLE, 
	 CONSTRAINT "SNAP__BEGIN_U" UNIQUE ("BEGIN_INTERVAL_TIME") ENABLE, 
	 CONSTRAINT "SNAP__END_U" UNIQUE ("END_INTERVAL_TIME") ENABLE, 
	 CONSTRAINT "SNAP__TIME_ASC" CHECK (begin_interval_time < end_interval_time) ENABLE --> -- for HTML parser
   )
; 

INSERT INTO SNAP
SELECT SNAP_ID, min(event_time), max(event_time) 
from RAW_DATA
group by SNAP_ID;

commit; -- 102 rows inserted

CREATE TABLE PART
   (	"ID"     NUMBER NOT NULL ENABLE, 
	"SNAP_ID"    NUMBER NOT NULL ENABLE, 
	"EVENT_TIME" DATE   NOT NULL ENABLE, 
	"PAD1" VARCHAR2(4000 BYTE) NOT NULL ENABLE, 
	 CONSTRAINT "PART_PK" PRIMARY KEY ("ID")  ENABLE
   ) 
  PARTITION BY LIST ("SNAP_ID") AUTOMATIC 
 (PARTITION "P1"  VALUES (1) SEGMENT CREATION DEFERRED )
 ;
 
select Partition_Name from user_tab_partitions where table_name='PART';

PARTITION_NAME                                                                                                                  
-------------- 
P1
SYS_P529
SYS_P530
...
SYS_P627
SYS_P628
SYS_P629

102 rows selected. 

The partitions are not that big, the data quite boring, but it's sufficient data to play around. 

Now I want to query a row from PART for a specific time-range. as the field EVENT_TIME is not the partition key, the simple query goes like this:

-- SQL1 
SELECT /*+ gather_plan_statistics */ p.* 
from PART p 
WHERE EVENT_TIME between sysdate-(1+1/98) and sysdate-1
;


Plan hash value: 2974113857

------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |       |       |      1 |00:00:00.01 |    7007 |
|*  1 |  FILTER             |      |      1 |        |       |       |      1 |00:00:00.01 |    7007 |
|   2 |   PARTITION LIST ALL|      |      1 |      1 |     1 |   102 |      1 |00:00:00.01 |    7007 |
|*  3 |    TABLE ACCESS FULL| PART |    102 |      1 |     1 |   102 |      1 |00:00:00.01 |    7007 |
------------------------------------------------------------------------------------------------------

In this simple query all partitions are scanned (Pstart => 1 to Pstop => 102). 
Let's see if we can use SNAP to make the query more efficient: 

The simplest query makes it even more expensive: 

-- SQL2
select p.* 
from snap sn, part p
where sn.snap_id = p.snap_id
  and p.event_time between sysdate-(1+1/98) and sysdate-1 ;

Plan hash value: 2574539144

------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |       |       | 27817 (100)|          |       |       |
|*  1 |  FILTER                   |          |       |       |            |          |       |       |
|   2 |   NESTED LOOPS            |          |     1 |  2050 | 27817   (1)| 00:00:02 |       |       |
|   3 |    INDEX FAST FULL SCAN   | SNAP__PK |   102 |  1326 |     2   (0)| 00:00:01 |       |       |
|   4 |    PARTITION LIST ITERATOR|          |     1 |  2037 |   273   (1)| 00:00:01 |   KEY |   KEY |
|*  5 |     TABLE ACCESS FULL     | PART     |     1 |  2037 |   273   (1)| 00:00:01 |   KEY |   KEY |
------------------------------------------------------------------------------------------------------

But we are missing something:

alter table PART
  add constraint PART__SNAP_FK foreign key("SNAP_ID") references "SNAP"("SNAP_ID")
;

-- rerun SQL2 

Plan hash value: 2974113857

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        | 27818 (100)|       |       |      1 |00:00:00.01 |    7007 |
|*  1 |  FILTER             |      |      1 |        |            |       |       |      1 |00:00:00.01 |    7007 |
|   2 |   PARTITION LIST ALL|      |      1 |      1 | 27818   (1)|     1 |   102 |      1 |00:00:00.01 |    7007 |
|*  3 |    TABLE ACCESS FULL| PART |    102 |      1 | 27818   (1)|     1 |   102 |      1 |00:00:00.01 |    7007 |
-------------------------------------------------------------------------------------------------------------------

Now we are back at the original plan - but no progress is made.
Next try: provide more information about the correlation between SNAP_IDs and dates in PART and SNAP:

select /*+ gather_plan_statistics */ p.* 
from snap sn, part p
where sn.snap_id = p.snap_id
  and p.event_time between sysdate-(1+1/98) and sysdate-1 
  and p.event_time between sn.begin_interval_time and sn.end_interval_time
;


Plan hash value: 3865632887

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        | 27819 (100)|       |       |      1 |00:00:00.01 |      74 |
|*  1 |  FILTER                       |          |      1 |        |            |       |       |      1 |00:00:00.01 |      74 |
|   2 |   NESTED LOOPS                |          |      1 |      1 | 27819   (1)|       |       |      1 |00:00:00.01 |      74 |
|   3 |    NESTED LOOPS               |          |      1 |      1 | 27819   (1)|       |       |      1 |00:00:00.01 |      73 |
|   4 |     PARTITION LIST SUBQUERY   |          |      1 |      1 | 27818   (1)|KEY(SQ)|KEY(SQ)|      1 |00:00:00.01 |      72 |
|*  5 |      TABLE ACCESS FULL        | PART     |      1 |      1 | 27818   (1)|KEY(SQ)|KEY(SQ)|      1 |00:00:00.01 |      70 |
|*  6 |     INDEX UNIQUE SCAN         | SNAP__PK |      1 |      1 |     0   (0)|       |       |      1 |00:00:00.01 |       1 |
|*  7 |    TABLE ACCESS BY INDEX ROWID| SNAP     |      1 |      1 |     1   (0)|       |       |      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SYSDATE@!-1>=SYSDATE@!-1.01020408163265306122448979591836734694)
   5 - filter(("P"."EVENT_TIME">=SYSDATE@!-1.01020408163265306122448979591836734694 AND
              "P"."EVENT_TIME"<=SYSDATE@!-1))
   6 - access("SN"."SNAP_ID"="P"."SNAP_ID")
   7 - filter(("P"."EVENT_TIME">="SN"."BEGIN_INTERVAL_TIME" AND "P"."EVENT_TIME"<="SN"."END_INTERVAL_TIME"
              AND "SN"."END_INTERVAL_TIME">=SYSDATE@!-1.01020408163265306122448979591836734694 AND
              "SN"."BEGIN_INTERVAL_TIME"<=SYSDATE@!-1))
-- >

D'OH! Now the costs increased - by the additional effort shows less Buffers! And also Pstart and Pstop show thePartitions begin and end at KEY(SQ) (no 1 .. 102 anymore). 

That's what I tried to achieve: indirect partition pruning based on a key stored in another table. 

Unfortunately the nasty 
and p.event_time between sn.begin_interval_time and sn.end_interval_time
is required as it can't be defined as an ASSERTION - even it is defined in SQL-92 already which is 30 years old. 
If you would like to see ASSERTIONs in Oracle, please upvote this Idea!

materialized WITH query

Sometimes I have to improve a SQL query where the same (or similar) subquery is used several times within the whole statement. This leads to many times the tables needs to be visited, even for the same rows.
It also makes the query hard to read.

A real life example I had to deal with this week is something like

SELECT *
FROM ( SELECT columns, aggregate functions
       FROM some tables
       INNER JOIN
       ( SELECT IDa, 0 as IDb, col1
         FROM T1
         UNION 
         SELECT 0, IDb, col col1
         FROM T2 )
       ON some joins
       INNER JOIN
       ( SELECT IDa, 0 as IDb, col2
         FROM T1
         UNION 
         SELECT 0, IDb, col col2
         FROM T2 )
       ON some joins
       INNER JOIN
       ( SELECT IDa, 0 as IDb, col3
         FROM T1
         UNION 
         SELECT 0, IDb, col col3
         FROM T2 )
       ON some joins
       INNER JOIN
       ( SELECT IDa, 0 as IDb, col4
         FROM T1
         UNION 
         SELECT 0, IDb, col col4
         FROM T2 )
       ON some joins
       WHERE some filters
       GROUP BY columns)
WHERE more filters

In this case it's quite visible there are 4 INNER JOINs to the same UNION of 2 tables, only the columns differ.

Fig 1 original PLAN - begin

The optimizer tried it's best and the beginning of the plan looked like Fig1. 3 more iterations with SORT - VIEW - SORT - UNION ALL follow. It's amazing the cost is so low, But even with higher cost there is not much the optimizer could do.


So my idea was to put it into a WITH clause and replace the INNER JOIN select with it.

The WITH clause is

WITH my_inner as (
SELECT IDa, 0 as IDb, col1, col2, col3, col4
FROM T1
UNION ALL
SELECT   0, IDb,      col1, col2, col3, col4
FROM T2
)

 

And when replacing the first INNER JOIN

INNER JOIN
       ( SELECT * from my_inner )

Fig 2 changed PLAN - 1 replacement

It seems the optimizer did not like this as the cost increased.


But I continued with the next replacement:

Fig 3 changed PLAN - 2 replacements

The optimizer "understands" it can created a temporary object (the one which begins with SYS_TEMP_) and then re-uses it later.

With all 4 replacements, the Plan is different now:

Fig 4 changed PLAN - 4 replacements

The cost is still higher than in the original plan, but it's very likely the statement is faster than the original one.
In this case, there was no need to add the MATERIALIZED hint - it was done automatically, for very good reasons.

I like this optimization as it both, improves the readability of the query AND it's performance!

no peek in PL/SQL

Connor McDonald wrote a blog about differences between bind peeking and SYS_CONTEXT in SQL queries. This even led to an proposal in Database Ideas: CBO should peek at SYS_CONTEXT values just like bind values (feel free to vote and comment, if you like it)
As I have a friend who really loves SYS_CONTEXT; I showed him the blog.

In the following discussion he stated (more/less
)

I should not have this problem at all, as I'm not using SYS_CONTEXT directly, but wrapped in a package like select * from xyz where a = pkg.get_ctxt('BLAH');

As a proud member of BAAG, I want clarify this should.

The preparation of my environment is nothing specific and quite close to Connors:

create table t ( x varchar2(10), y char(100));

insert into t
select 'a', rownum
from dual
/

insert into t
select 'b', rownum
from dual
connect by level <= 100000
/

commit;

create index ix on t ( x ) ;

exec dbms_stats.gather_table_stats('','T',method_Opt=>'for all columns size 5');

drop context blah;
create context blah using my_package;

create or replace PACKAGE MY_PACKAGE AS 

  procedure my_proc(p_val varchar2); 
  function get_sys_context return varchar2;
  function get_variable return varchar2;

END MY_PACKAGE;

create or replace package body MY_PACKAGE AS 
  my_var varchar2(10);
  procedure my_proc(p_val varchar2)  is
    begin
        my_var := p_val;
        sys.dbms_session.set_context('BLAH','ATTRIB',p_val);
    end my_proc;
    
  function get_sys_context return varchar2 is
    begin
      return sys_context('BLAH','ATTRIB');
    end get_sys_context;

  function get_variable return varchar2 is
    begin
      return my_var;
    end get_variable;

end MY_PACKAGE;

Now the real testcase starts:

exec my_package.my_proc('a');

select my_package.get_sys_context from dual;
select my_package.get_variable from dual;

Just shows everything works as expected:
PL/SQL procedure successfully completed.

GET_SYS_CONTEXT
---------------
a

GET_VARIABLE
---------------
a


So let's check the optimizers opinnion:

select /*+ gather_plan_statistics */ count(y)
from t
where x = my_package.get_sys_context;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

COUNT(Y)
----------
1


SQL_ID  2mgq2mrzd26j6, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from t where x = 
my_package.get_sys_context

Plan hash value: 2966233522

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.38 |    1507 |   1505 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.38 |    1507 |   1505 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |  50001 |      1 |00:00:00.38 |    1507 |   1505 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("X"="MY_PACKAGE"."GET_SYS_CONTEXT"())

It seems optimizer doesn't like SYS_CONTEXT wrapped in a package.

select /*+ gather_plan_statistics */ count(y)
from t
where x = my_package.get_variable;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

COUNT(Y)
----------
1


SQL_ID  4ytd1pqx1yr09, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from t where x = 
my_package.get_variable

Plan hash value: 2966233522

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.16 |    1507 |   1504 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.16 |    1507 |   1504 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |  50001 |      1 |00:00:00.16 |    1507 |   1504 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("X"="MY_PACKAGE"."GET_VARIABLE"())

The optimizer doesn't like the any pl/sql package function, regardless where the value comes from.

To bring it down to an even simpler construction:

CREATE OR REPLACE FUNCTION RETURN_BIND 
(
  BIND_IN IN VARCHAR2 
) RETURN VARCHAR2 AS 
BEGIN
  RETURN BIND_IN;
END RETURN_BIND;

variable b1 varchar2(10)
exec :b1 := 'a';

select RETURN_BIND(:b1) from dual;

select /*+ gather_plan_statistics */ count(y)
from t
where x = RETURN_BIND(:b1);

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

COUNT(Y)
----------
         1


SQL_ID  84y4gdn5tmsb0, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(y) from t where x = 
RETURN_BIND(:b1)
 
Plan hash value: 2966233522
 
----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.18 |    1507 |   1505 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.18 |    1507 |   1505 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |  50001 |      1 |00:00:00.18 |    1507 |   1505 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("X"="RETURN_BIND"(:B1))

I hope this is enough evidence to claim, there is no bind peeking at the result of any PL/SQL function.

With this in mind, if you care for performance and your data might not be even distributed, take special care if you filter by the return value of any PL/SQL function (or SYS_CONTEXT).

This all was done in an unpatched 12.1 sandbox.

No Adaptive Cursor Sharing for collections

Recently I was hunting a performance problem at work.

A developer read about adaptive cursor sharing and therefore guessed, the optimizer would know about the number of rows when he passes a collection for a table(:bind) function. I can totally understand it, as there where no limitations in the statement (except the 14 binds):

The adaptive cursor sharing feature enables a single statement that contains bind variables to use multiple execution plans. Cursor sharing is "adaptive" because the cursor adapts its behavior so that the database does not always use the same plan for each execution or bind variable value.
For appropriate queries, the database monitors data accessed over time for different bind values, ensuring the optimal choice of cursor for a specific bind value. For example, the optimizer might choose one plan for bind value 9 and a different plan for bind value 10. Cursor sharing is "adaptive" because the cursor adapts its behavior so that the same plan is not always used for each execution or bind variable value.
Adaptive cursor sharing is enabled for the database by default and cannot be disabled. Note that adaptive cursor sharing does not apply to SQL statements containing more than 14 bind variables.

There is no limitation about where the bind peeking is done (and where not). Based on my findings I'd limit it to "only in filtration". But I accept any better wording.

Now let's go down to the testcase (all tested in 11.2.0.3):
To prepare a nice environment I did:

DROP TYPE T_COLLECTION_TEST;
/

DROP TYPE O_COLLECTION_TEST;
/

DROP PACKAGE COLLECTION_TEST;
/
*/

CREATE TYPE O_COLLECTION_TEST
AS
  OBJECT
  (
    RN             NUMBER,
    OWNER          VARCHAR2(30),
    OBJECT_NAME    VARCHAR2(30),
    SUBOBJECT_NAME VARCHAR2(30),
    OBJECT_ID      NUMBER,
    DATA_OBJECT_ID NUMBER,
    OBJECT_TYPE    VARCHAR2(19),
    CREATED        DATE,
    LAST_DDL_TIME  DATE,
    TIMESTAMP      VARCHAR2(19),
    STATUS         VARCHAR2(7),
    TEMPORARY      VARCHAR2(1),
    GENERATED      VARCHAR2(1),
    SECONDARY      VARCHAR2(1),
    NAMESPACE      NUMBER,
    EDITION_NAME   VARCHAR2(30));
  /
CREATE TYPE T_COLLECTION_TEST
IS
  TABLE OF O_COLLECTION_TEST;
  /

DROP TABLE AOT ;
CREATE TABLE AOT AS 
      select rownum rn, v.*
      from (SELECT * FROM ALL_OBJECTS
      UNION ALL
      SELECT * FROM ALL_OBJECTS
      UNION ALL
      SELECT * FROM ALL_OBJECTS
   UNION ALL
      SELECT * FROM ALL_OBJECTS
   UNION ALL
      SELECT * FROM ALL_OBJECTS) v
;

create unique index iAOT on aot(rn);

exec dbms_stats.gather_table_stats(null,'AOT');



CREATE OR REPLACE
PACKAGE COLLECTION_TEST
IS
  G_COLLECTION_SIZE NUMBER := 0;
  R_CT T_COLLECTION_TEST := T_COLLECTION_TEST();
  FUNCTION RUN(
      P_COLLECTION_SIZE IN NUMBER)
    RETURN T_COLLECTION_TEST;
END; 
/

CREATE OR REPLACE
PACKAGE BODY COLLECTION_TEST
IS
FUNCTION RUN(
    P_COLLECTION_SIZE IN NUMBER)
  RETURN T_COLLECTION_TEST
IS
BEGIN
  IF G_COLLECTION_SIZE <> P_COLLECTION_SIZE THEN
    G_COLLECTION_SIZE  := P_COLLECTION_SIZE;
    SELECT O_COLLECTION_TEST(RN, OWNER , OBJECT_NAME , SUBOBJECT_NAME , OBJECT_ID , DATA_OBJECT_ID , OBJECT_TYPE , CREATED , LAST_DDL_TIME , TIMESTAMP , STATUS , TEMPORARY , GENERATED , SECONDARY , NAMESPACE , EDITION_NAME ) BULK COLLECT
    INTO R_CT
    FROM AOT WHERE rn <= G_COLLECTION_SIZE;
  END IF;
  RETURN R_CT;
END;
END;
/

That's enough for a small testcase. In fact I merged 2 tests into one run:
first: does the optimizer cares for a table(:bind) function
second: does the cardinality hint helps?

So the actual test run is:

DECLARE
  n NUMBER := 1;
  i NUMBER := 0;
  c T_COLLECTION_TEST;
TYPE tab_ao_tab
IS
  TABLE OF VARCHAR2(100);
  ao_tab tab_ao_tab;
  idx NUMBER;
  x   NUMBER := 0;
BEGIN
  EXECUTE immediate 'ALTER SYSTEM FLUSH SHARED_POOL';
  DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>TRUE);
  FOR i IN 1..6
  LOOP
    dbms_output.put_line(i);
    n := power(10,i);
    c := collection_test.run(n);
    dbms_output.put_line(n);
    DBMS_APPLICATION_INFO.set_client_info(client_info => 'Number: '|| n);
    dbms_output.put_line('test1 ');
    EXECUTE immediate 'select /*+ gather_plan_statistics dynamic_sampling(t 10) */                      
t.owner || ''.'' || ao.object_name                     
from table(:c) t,   AOT ao                      
where t.rn = ao.rn' bulk collect INTO ao_tab USING c;
    FOR indx IN ao_tab.FIRST..ao_tab.LAST
    LOOP
      x := n + 1;
    END LOOP;
    dbms_output.put_line('x: ' || x);
    dbms_output.put_line('test2 ');
    dbms_output.put_line('cardinality1 ');
    EXECUTE immediate 'select /*+ gather_plan_statistics cardinality(t ' || n || ') */                      
t.owner || ''.'' || ao.object_name                     
from table(:c) t,   AOT ao                      
where t.rn = ao.rn' bulk collect INTO ao_tab USING c;
    FOR indx IN ao_tab.FIRST..ao_tab.LAST
    LOOP
      x := x + 1;
    END LOOP;
    dbms_output.put_line('x: ' || x);
  END LOOP;
  DBMS_MONITOR.session_trace_disable;
END;
/

If you want to try this at home, there is no need to create a sql-trace file. I'm just so used to it.
Now my big question is, how many cursors this testcase creates and what's their execution plan is.

select sql_id, child_number, executions, plan_hash_value, substr(sql_text,1,56) text
from v$sql 
where lower(sql_text) like 'select /*+ gather_plan_statistics%'
order by executions desc, plan_hash_value asc, text asc;

With a result (on my system):

SQL_ID         CN  EX PLAN_HASH_VALUE TEXT                                                   
------------- --- --- --------------- --------------------------------------------------------
9741g8bfng454   0   6      1690681298 select /*+ gather_plan_statistics dynamic_sampling(t 10) 
dr4yxf89n0g2k   0   1      1690681298 select /*+ gather_plan_statistics cardinality(t 10) */   
14yffxq80k21d   0   1      1690681298 select /*+ gather_plan_statistics cardinality(t 100) */  
52vt0cr9ghnh4   0   1      1690681298 select /*+ gather_plan_statistics cardinality(t 1000) */ 
2d6328126t9gz   0   1      3714863083 select /*+ gather_plan_statistics cardinality(t 10000) * 
du8ds9qupzzcn   0   1      3714863083 select /*+ gather_plan_statistics cardinality(t 100000)  
9t4zm8nkpr8dj   0   1      3714863083 select /*+ gather_plan_statistics cardinality(t 1000000)

The statements with cardinality hint show a proper flip in the execution plan from 1000 to 10000 rows.
Unfortunately the statement with dynamic_sampling does not follow this pattern. The first execution plan sticks:

------------------------------------------------------------------------------------------------------                                                                                                                                                                                                       
| Id  | Operation                           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                                                                                                                                                                                       
------------------------------------------------------------------------------------------------------                                                                                                                                                                                                       
|   0 | SELECT STATEMENT                    |      |      1 |        |     10 |00:00:00.01 |      19 |                                                                                                                                                                                                       
|   1 |  NESTED LOOPS                       |      |      1 |        |     10 |00:00:00.01 |      19 |                                                                                                                                                                                                       
|   2 |   NESTED LOOPS                      |      |      1 |     10 |     10 |00:00:00.01 |       9 |                                                                                                                                                                                                       
|   3 |    COLLECTION ITERATOR PICKLER FETCH|      |      1 |     10 |     10 |00:00:00.01 |       0 |                                                                                                                                                                                                       
|*  4 |    INDEX UNIQUE SCAN                | IAOT |     10 |      1 |     10 |00:00:00.01 |       9 |                                                                                                                                                                                                       
|   5 |   TABLE ACCESS BY INDEX ROWID       | AOT  |     10 |      1 |     10 |00:00:00.01 |      10 |                                                                                                                                                                                                       
------------------------------------------------------------------------------------------------------   

And all the following executions obey this execution plan. Without thinking or checking.
But the plan can change:  Doing the same testcase with 6..1 the plan is now stable at

------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                 
| Id  | Operation                          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|                                                                                                                                                 
------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                 
|   0 | SELECT STATEMENT                   |      |      1 |        |    340K|00:00:01.88 |    5101 |   6810 |   6810 |       |       |          |         |                                                                                                                                                 
|*  1 |  HASH JOIN                         |      |      1 |    340K|    340K|00:00:01.88 |    5101 |   6810 |   6810 |    47M|  4032K| 3769K (1)|   58368 |                                                                                                                                                 
|   2 |   COLLECTION ITERATOR PICKLER FETCH|      |      1 |    340K|    340K|00:00:00.13 |       0 |      0 |      0 |       |       |          |         |                                                                                                                                                 
|   3 |   TABLE ACCESS FULL                | AOT  |      1 |    340K|    340K|00:00:00.09 |    5101 |      0 |      0 |       |       |          |         |                                                                                                                                                 
------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                 

I hope you can see, even in 11.2.0.3 it's like rain on monday morning in Washington DC. Dynamic Sampling jumps in, but it's not adaptive.
What's the conclusion here? 
If you use a collection in a table function, and the expected numbers in that collection varies by dimensions, take care! My solution is a cardinality hint, but that might be complicated if you are using a framework.
I don't know any 'cheap' solution. 
Maybe anyone wants to try this in 12c. I did not focus there yet. It's still 2013 :-) 

Invalid SQL if hint is missing?

Recently Charles Hooper posted a blog An Invalid, or Do You Just Not Want to Work. There he asked under which circumstances a SQL should be considered invalid? I tried to discuss this question in the comments there and I am still thinking about an other kind of answer. But today I'd like to show a statement, which is invalid without a hint!

here we go:
(everything 11.2.0.1EE 64-bit on Linux)
I was asked to provide a list of all views which have a hint (how ironic) in it. After some test I provided this one:

select /*+ no_merge(vv) */ owner, view_name
from (select v.owner, v.view_name
from dba_views v, dba_objects o
where v.owner = o.owner
AND  v.view_name = o.object_name
AND o.object_type='VIEW'
AND o.status='VALID'
AND v.owner ='PSFT'  ) vv
where    dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%/*+%'

without the no_merge hint I get this error stack:

ERROR at line 9:
ORA-31603: object "AAB_TEST" of type VIEW not found in schema "PSFT"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3912
ORA-06512: at "SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1

I did some explain plan with and without hints, so I got these 2 outputs:

SQL_ID bwsx4fryyu6u9, child number 0
-------------------------------------
select /*+ no_merge(vv) */ owner, view_name from (select v.owner,
v.view_name    from dba_views v, dba_objects o   where v.owner =
o.owner    AND v.view_name = o.object_name    AND o.object_type='VIEW'
   AND o.status='VALID'    AND v.owner ='PSFT' ) vv where
dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%/*+%'

Plan hash value: 3436948868

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |    1 |       |     14 |00:01:33.65 |  1088K|       |       |   |
|*  1 |  VIEW          |      |    1 |     1 |     14 |00:01:33.65 |  1088K|       |       |   |
|*  2 |   FILTER         |      |    1 |       |   8802 |00:00:00.28 |  7432 |       |       |   |
|   3 |    NESTED LOOPS         |      |    1 |     1 |   8802 |00:00:00.25 |  7432 |       |       |   |
|   4 |     NESTED LOOPS        |      |    1 |     1 |   8802 |00:00:00.21 |  2469 |       |       |   |
|*  5 |      HASH JOIN         |      |    1 |     1 |   8802 |00:00:00.15 |  2464 |  1152K|  1152K| 1616K (0)|
|   6 |       VIEW         | DBA_OBJECTS |    1 |    26 |   8802 |00:00:00.07 |  2239 |       |       |   |
|   7 |        UNION-ALL        |      |    1 |       |   8802 |00:00:00.07 |  2239 |       |       |   |
|*  8 |  TABLE ACCESS BY INDEX ROWID   | SUM$     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|*  9 |   INDEX UNIQUE SCAN       | I_SUM$_1    |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 10 |  FILTER         |      |    1 |       |   8802 |00:00:00.06 |  2239 |       |       |   |
|  11 |   NESTED LOOPS        |      |    1 |     1 |   8802 |00:00:00.05 |  2239 |       |       |   |
|  12 |    NESTED LOOPS        |      |    1 |     1 |   8802 |00:00:00.03 |  2236 |       |       |   |
|  13 |     TABLE ACCESS BY INDEX ROWID| USER$     |    1 |     1 |      1 |00:00:00.01 |     2 |       |       |   |
|* 14 |      INDEX UNIQUE SCAN       | I_USER1     |    1 |     1 |      1 |00:00:00.01 |     1 |       |       |   |
|* 15 |     TABLE ACCESS BY INDEX ROWID| OBJ$     |    1 |     1 |   8802 |00:00:00.03 |  2234 |       |       |   |
|* 16 |      INDEX RANGE SCAN       | I_OBJ5     |    1 |    25 |   8803 |00:00:00.01 |   222 |       |       |   |
|* 17 |    INDEX RANGE SCAN       | I_USER2     | 8802 |     1 |   8802 |00:00:00.02 |     3 |       |       |   |
|* 18 |   TABLE ACCESS BY INDEX ROWID  | IND$     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 19 |    INDEX UNIQUE SCAN       | I_IND1     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|  20 |   NESTED LOOPS        |      |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 21 |    INDEX FULL SCAN       | I_USER2     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 22 |    INDEX RANGE SCAN       | I_OBJ4     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 23 |  FILTER         |      |    1 |       |      0 |00:00:00.01 |     0 |       |       |   |
|  24 |   NESTED LOOPS        |      |    0 |     2 |      0 |00:00:00.01 |     0 |       |       |   |
|  25 |    TABLE ACCESS BY INDEX ROWID | USER$     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 26 |     INDEX UNIQUE SCAN       | I_USER1     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 27 |    INDEX RANGE SCAN       | I_LINK1     |    0 |     2 |      0 |00:00:00.01 |     0 |       |       |   |
|  28 |       NESTED LOOPS        |      |    1 |  2546 |  25853 |00:00:00.03 |   225 |       |       |   |
|  29 |        TABLE ACCESS BY INDEX ROWID    | USER$     |    1 |     1 |      1 |00:00:00.01 |     2 |       |       |   |
|* 30 |  INDEX UNIQUE SCAN       | I_USER1     |    1 |     1 |      1 |00:00:00.01 |     1 |       |       |   |
|* 31 |        INDEX RANGE SCAN        | I_OBJ5     |    1 |  2546 |  25853 |00:00:00.01 |   223 |       |       |   |
|* 32 |      INDEX RANGE SCAN        | I_USER2     | 8802 |     1 |   8802 |00:00:00.05 |     5 |       |       |   |
|* 33 |     INDEX UNIQUE SCAN        | I_VIEW1     | 8802 |     1 |   8802 |00:00:00.03 |  4963 |       |       |   |
|  34 |    NESTED LOOPS         |      |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 35 |     INDEX FULL SCAN        | I_USER2     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 36 |     INDEX RANGE SCAN        | I_OBJ4     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DBMS_METADATA"."GET_DDL"('VIEW',"VV"."VIEW_NAME","VV"."OWNER") LIKE '%/*+%')
   2 - filter((("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10
       AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
       "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#") AND
       ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND
       "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR  IS NOT NULL))))
   5 - access("O"."NAME"="O"."OBJECT_NAME")
   8 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
   9 - access("S"."OBJ#"=:B1)
  10 - filter(((("O"."TYPE#"<>1 AND "O"."TYPE#"<>10) OR ("O"."TYPE#"=1 AND =1)) AND (("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND
       "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
       "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR
       (INTERNAL_FUNCTION("O"."TYPE#") AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR
       ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR  IS NOT NULL)))))
  14 - access("U"."NAME"='PSFT')
  15 - filter((DECODE("O"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID' AND BITAND("O"."FLAGS",128)=0))
  16 - access("O"."SPARE3"="U"."USER#" AND "O"."LINKNAME" IS NULL)
       filter(("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND
       DECODE("O"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,
       'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX
       PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA
       RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB
       SUBPARTITION',42,NVL(,'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
       GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATIO
       N CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE
       GROUP',82,'MINING MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD
       PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW'))
  17 - access("O"."OWNER#"="U"."USER#")
  18 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
       "I"."TYPE#"=9))
  19 - access("I"."OBJ#"=:B1)
  21 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
       filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
  22 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  23 - filter(NULL IS NOT NULL)
  26 - access("U"."NAME"='PSFT')
  27 - access("L"."OWNER#"="U"."USER#")
  30 - access("U"."NAME"='PSFT')
  31 - access("O"."SPARE3"="U"."USER#")
  32 - access("O"."OWNER#"="U"."USER#")
  33 - access("O"."OBJ#"="V"."OBJ#")
  35 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
       filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
  36 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")


100 rows selected.

and

Plan hash value: 739349040

----------------------------------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |     |  1 |   107 | 66   (2)| 00:00:02 |
|*  1 |  FILTER         |     |    |    |  |    |
|   2 |   NESTED LOOPS        |     |  1 |   107 | 66   (2)| 00:00:02 |
|   3 |    NESTED LOOPS        |     |  1 |   102 | 66   (2)| 00:00:02 |
|*  4 |     HASH JOIN        |     |  1 | 80 | 65   (2)| 00:00:01 |
|   5 |      VIEW        | DBA_OBJECTS | 26 |   624 | 40   (0)| 00:00:01 |
|   6 |       UNION-ALL        |     |    |    |  |    |
|*  7 |        TABLE ACCESS BY INDEX ROWID   | SUM$    |  1 | 26 |  0   (0)| 00:00:01 |
|*  8 |  INDEX UNIQUE SCAN      | I_SUM$_1    |  1 |    |  0   (0)| 00:00:01 |
|*  9 |        FILTER        |     |    |    |  |    |
|  10 |  NESTED LOOPS       |     |  1 |   121 | 40   (0)| 00:00:01 |
|  11 |   NESTED LOOPS       |     |  1 | 99 | 39   (0)| 00:00:01 |
|  12 |    TABLE ACCESS BY INDEX ROWID| USER$    |  1 | 17 |  1   (0)| 00:00:01 |
|* 13 |     INDEX UNIQUE SCAN      | I_USER1    |  1 |    |  0   (0)| 00:00:01 |
|* 14 |    TABLE ACCESS BY INDEX ROWID| OBJ$    |  1 | 82 | 38   (0)| 00:00:01 |
|* 15 |     INDEX RANGE SCAN      | I_OBJ5    | 25 |    | 23   (0)| 00:00:01 |
|* 16 |   INDEX RANGE SCAN      | I_USER2    |  1 | 22 |  1   (0)| 00:00:01 |
|* 17 |  TABLE ACCESS BY INDEX ROWID  | IND$    |  1 |  8 |  2   (0)| 00:00:01 |
|* 18 |   INDEX UNIQUE SCAN      | I_IND1    |  1 |    |  1   (0)| 00:00:01 |
|  19 |  NESTED LOOPS       |     |  1 | 29 |  2   (0)| 00:00:01 |
|* 20 |   INDEX FULL SCAN      | I_USER2    |  1 | 20 |  1   (0)| 00:00:01 |
|* 21 |   INDEX RANGE SCAN      | I_OBJ4    |  1 |  9 |  1   (0)| 00:00:01 |
|* 22 |        FILTER        |     |    |    |  |    |
|  23 |  NESTED LOOPS       |     |  2 | 54 |  2   (0)| 00:00:01 |
|  24 |   TABLE ACCESS BY INDEX ROWID | USER$    |  1 | 17 |  1   (0)| 00:00:01 |
|* 25 |    INDEX UNIQUE SCAN      | I_USER1    |  1 |    |  0   (0)| 00:00:01 |
|* 26 |   INDEX RANGE SCAN      | I_LINK1    |  2 | 20 |  1   (0)| 00:00:01 |
|  27 |      NESTED LOOPS       |     |   127 |  7112 | 24   (0)| 00:00:01 |
|  28 |       TABLE ACCESS BY INDEX ROWID    | USER$    |  1 | 17 |  1   (0)| 00:00:01 |
|* 29 |        INDEX UNIQUE SCAN      | I_USER1    |  1 |    |  0   (0)| 00:00:01 |
|* 30 |       INDEX RANGE SCAN       | I_OBJ5    |   127 |  4953 | 23   (0)| 00:00:01 |
|* 31 |     INDEX RANGE SCAN       | I_USER2    |  1 | 22 |  1   (0)| 00:00:01 |
|* 32 |    INDEX UNIQUE SCAN       | I_VIEW1    |  1 |  5 |  0   (0)| 00:00:01 |
|  33 |   NESTED LOOPS        |     |  1 | 29 |  2   (0)| 00:00:01 |
|* 34 |    INDEX FULL SCAN       | I_USER2    |  1 | 20 |  1   (0)| 00:00:01 |
|* 35 |    INDEX RANGE SCAN       | I_OBJ4    |  1 |  9 |  1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5947ACE7
   5 - SET$1     / O@SEL$2
   6 - SET$1
   7 - SEL$8     / S@SEL$8
   8 - SEL$8     / S@SEL$8
   9 - SEL$DA86A24D
  12 - SEL$DA86A24D / U@SEL$7
  13 - SEL$DA86A24D / U@SEL$7
  14 - SEL$DA86A24D / O@SEL$9
  15 - SEL$DA86A24D / O@SEL$9
  16 - SEL$DA86A24D / U@SEL$9
  17 - SEL$12     / I@SEL$12
  18 - SEL$12     / I@SEL$12
  19 - SEL$11
  20 - SEL$11     / U2@SEL$11
  21 - SEL$11     / O2@SEL$11
  22 - SEL$13
  24 - SEL$13     / U@SEL$13
  25 - SEL$13     / U@SEL$13
  26 - SEL$13     / L@SEL$13
  28 - SEL$5947ACE7 / U@SEL$3
  29 - SEL$5947ACE7 / U@SEL$3
  30 - SEL$5947ACE7 / O@SEL$4
  31 - SEL$5947ACE7 / U@SEL$4
  32 - SEL$5947ACE7 / V@SEL$3
  33 - SEL$6
  34 - SEL$6     / U2@SEL$6
  35 - SEL$6     / O2@SEL$6

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
       "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
       "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
       "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
       "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
       "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
       (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
       "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
       EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
       "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND
       "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
   4 - access("O"."NAME"="O"."OBJECT_NAME")
   7 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
   8 - access("S"."OBJ#"=:B1)
   9 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM
       "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3
       OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND
       ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9
       AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND
       "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR
       BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR
       "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR
       "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
       (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
       "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
       EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
       "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 AND
       "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))
  13 - access("U"."NAME"='PSFT')
  14 - filter(DECODE("O"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID' AND
       BITAND("O"."FLAGS",128)=0)
  15 - access("O"."SPARE3"="U"."USER#" AND "O"."LINKNAME" IS NULL)
       filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND
       "O"."LINKNAME" IS NULL AND DECODE("O"."TYPE#",0,'NEXT
       OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'F
       UNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE
       PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA
       SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE
       SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL(
       (SELECT 'REWRITE EQUIVALENCE' FROM SYS."SUM$" "S" WHERE "S"."OBJ#"=:B1 AND
       BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED
       VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
       GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA
       DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION
       CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER
       GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING
       MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE
       FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW')
  16 - access("O"."OWNER#"="U"."USER#")
  17 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
       "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
  18 - access("I"."OBJ#"=:B1)
  20 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
       ion_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
       ion_id')))
  21 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  22 - filter(NULL IS NOT NULL)
  25 - access("U"."NAME"='PSFT')
  26 - access("L"."OWNER#"="U"."USER#")
  29 - access("U"."NAME"='PSFT')
  30 - access("O"."SPARE3"="U"."USER#")
       filter("DBMS_METADATA"."GET_DDL"('VIEW',"O"."NAME","U"."NAME") LIKE '%/*+%')
  31 - access("O"."OWNER#"="U"."USER#")
  32 - access("O"."OBJ#"="V"."OBJ#")
  34 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
       ion_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
       ion_id')))
  35 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "O"."NAME"[VARCHAR2,30], "U"."NAME"[VARCHAR2,30]
   2 - (#keys=0) "O"."NAME"[VARCHAR2,30], "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22],
       "O"."TYPE#"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22],
       "U"."SPARE2"[NUMBER,22]
   3 - (#keys=0) "O"."NAME"[VARCHAR2,30], "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22],
       "O"."TYPE#"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22],
       "U"."SPARE2"[NUMBER,22]
   4 - (#keys=1) "O"."NAME"[VARCHAR2,30], "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22],
       "O"."OWNER#"[NUMBER,22], "O"."TYPE#"[NUMBER,22]
   5 - "O"."OBJECT_NAME"[VARCHAR2,128]
   6 - STRDEF[30], STRDEF[128], STRDEF[19], STRDEF[7]
   8 - "S".ROWID[ROWID,10]
   9 - "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],
       "O"."TYPE#"[NUMBER,22], "O"."STATUS"[NUMBER,22]
  10 - (#keys=0) "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],
       "O"."TYPE#"[NUMBER,22], "O"."STATUS"[NUMBER,22], "U"."TYPE#"[NUMBER,22],
       "U"."SPARE1"[NUMBER,22], "U"."SPARE2"[NUMBER,22]
  11 - (#keys=0) "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22],
       "O"."NAME"[VARCHAR2,30], "O"."TYPE#"[NUMBER,22], "O"."STATUS"[NUMBER,22]
  12 - "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,30]
  13 - "U".ROWID[ROWID,10], "U"."NAME"[VARCHAR2,30]
  14 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],
       "O"."TYPE#"[NUMBER,22], "O"."STATUS"[NUMBER,22]
  15 - "SYS_ALIAS_4".ROWID[ROWID,10], "O"."NAME"[VARCHAR2,30], "O"."TYPE#"[NUMBER,22],
       "O"."OWNER#"[NUMBER,22], "O"."OBJ#"[NUMBER,22]
  16 - "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "U"."SPARE2"[NUMBER,22]
  18 - "I".ROWID[ROWID,10]
  19 - (#keys=0)
  20 - "U2"."USER#"[NUMBER,22]
  22 - "U"."NAME"[VARCHAR2,30], "L"."NAME"[VARCHAR2,128]
  23 - (#keys=0) "U"."NAME"[VARCHAR2,30], "L"."NAME"[VARCHAR2,128]
  24 - "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,30]
  25 - "U".ROWID[ROWID,10], "U"."NAME"[VARCHAR2,30]
  26 - "L"."NAME"[VARCHAR2,128]
  27 - (#keys=0) "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22],
       "O"."NAME"[VARCHAR2,30], "O"."TYPE#"[NUMBER,22]
  28 - "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,30]
  29 - "U".ROWID[ROWID,10], "U"."NAME"[VARCHAR2,30]
  30 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],
       "O"."TYPE#"[NUMBER,22]
  31 - "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "U"."SPARE2"[NUMBER,22]
  33 - (#keys=0)
  34 - "U2"."USER#"[NUMBER,22]

195 rows selected.

I did not came down what's really wrong in the 2nd case. But it is some of these effects where I'd blame the database more than the SQL for invalidity.

Detuning soft parses

During our seminar Oracle 10g Performance analysis with Doug Burns the question arises how to force the optimizer to do a hard-parse all the teme, even when prepared statements and bind variables are used. The question was about a 10.2.0.4 DWH env.

For 11g and some backports I found DBMS_SHARED_POOL.PURGE which just purges the cursor (of course, you have to know it first; but that's no problem for one who complain about bind peeking in 10g).
And just for the records there are some Notes
Note:457309.1 - How To Flush an Object out the Library Cache [SGA]
Note:751876.1 - DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4
and other blogs also: Flushing a single cursor

But my initial idea was tha classic art or de-tuning:
Sometimes we complain about situatiions, where the statement is literally the same, but the optimizer creates several childs by hard-parsing. So let's use this bahaviour this time: Just change one of the many related optimizer environments.
1og Optimizer Environment Views will be a good start, but I assume (not tested!) even a NLS_SORT might help for the start.
Just grab one environment parameter which provides a wide range of values with little to no affect. So first bitmap_merge_area_size came into my mind, but I'm not sure if it will have no side effects, at least in a DWH env.

At the end, the common disclaimer: I have not tested any of the methods described here, so be warned ;-)

ANY translated to filter(("col1"= OR "col2=))

I had to tweak a sequence of statements, in pseudo-code it was something like this:

select col1 into v1
from tab1
where col2=<condition1>;
delete from tab2
where col3 = v1;
delete from tab2
where col4 = v1;

My first step was to avoid the variable v1 and put the select into the deletes as a subselect.
My 2nd step was to merge the 2 deletes into one, combining the 2 selerated conditions using OR.
this gave an execution plan similar to this (I use the test-table from my previous post and a really simple statement):
delete
from berx1
where type=(select 1 from dual)
or dim=(select 1 from dual);


-------------------------------------
| Id  | Operation           | Name  |
-------------------------------------
|   1 |  DELETE             | BERX1 |
|*  2 |   TABLE ACCESS FULL | BERX1 |
|   3 |    FAST DUAL        |       |
|   4 |     FAST DUAL       |       |
-------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("TYPE"= OR "DIM"=))

The next idea was to avoid running the same subselect twice:
My first thought was to use the subquery_factoring_clause (WITH), but it's only useable in SELECT statements. So I loitered through the documentation and stumbled across the ANY keyword. After some ests I came to this statement:
delete
from berx1
where (select 1 from dual) = any (type, dim);

------------------------------------
| Id | Operation | Name |
------------------------------------
| 1 | DELETE | BERX1 |
|* 2 | TABLE ACCESS FULL| BERX1 |
| 3 | FAST DUAL | |
------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("TYPE"= OR "DIM"=))

Anything more to say? Only one subselect, even the filter is the same.
I tested this behaviour with 11.1.0.6 and 10.2.0.4 - both are the same.

Oracle Performance books

I got 2 new books:
Cost-Based Oracle Fundamentals by Jonathan Lewis (ISBN:1-59059-636-6)
and
Troubleshooting Oracle Performance by Christian Antognini (ISBN:1-59059-917-9)
Both are worth reading.
I will try to read and understand both of them.
Maybe I will reproduce some of their tests and will try to do my own, and maybe maybe I will post the results here ;-)