2013-11-24

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 DCDynamic 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 :-)