The aim of the STATISTICS COLLECTOR row source operation, which is used in adaptive plans, is to buffer all data produced by its child operation until it is known whether the inflection point is crossed. It goes without saying that buffering requires memory and, therefore, Oracle Database has to limit the amount of memory that can be allocated for that purpose. As a result, in some situations no adaptive plans can be used because according to the query optimizer estimations too much memory is required.

A special case of the previous limitation is when the row source operation under the STATISTICS COLLECTOR operation produces a LOB. In fact, when a LOB is detected, independently of the amount of data that is expected to be produced, the adaptive plan is bypassed by the query optimizer.

Let’s have a look to an example:

  • Create the test table T1
SQL> CREATE TABLE t1 (id, n, pad)
  2  AS
  3  SELECT rownum, rownum, lpad('*',100,'*')
  4  FROM dual
  5  CONNECT BY level <= 10000;

SQL> INSERT INTO t1
  2  SELECT 10000+rownum, 666, lpad('*',100,'*')
  3  FROM dual
  4  CONNECT BY level <= 150;

SQL> COMMIT;

SQL> ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (id);

SQL> execute dbms_stats.gather_table_stats(user,'t1')
  • Create the test table T2
SQL> CREATE TABLE t2 (id, n, pad)
  2  AS
  3  SELECT rownum, rownum, lpad('*',100,'*')
  4  FROM dual
  5  CONNECT BY level <= 10000;

SQL> ALTER TABLE t2 ADD CONSTRAINT t2_pk PRIMARY KEY (id);

SQL> execute dbms_stats.gather_table_stats(user,'t2')
  • The query optimizer selects an adaptive plan when T1 and T2 are joined
SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM t1, t2
  4  WHERE t1.id = t2.id
  5  AND t1.n = 666;

SQL> SELECT * FROM table(dbms_xplan.display(format=>'basic +predicate +note +adaptive'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 1837274416

-------------------------------------------------
|   Id  | Operation                     | Name  |
-------------------------------------------------
|     0 | SELECT STATEMENT              |       |
|- *  1 |  HASH JOIN                    |       |
|     2 |   NESTED LOOPS                |       |
|     3 |    NESTED LOOPS               |       |
|-    4 |     STATISTICS COLLECTOR      |       |
|  *  5 |      TABLE ACCESS FULL        | T1    |
|  *  6 |     INDEX UNIQUE SCAN         | T2_PK |
|     7 |    TABLE ACCESS BY INDEX ROWID| T2    |
|-    8 |   TABLE ACCESS FULL           | T2    |
-------------------------------------------------

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

   1 - access("T1"."ID"="T2"."ID")
   5 - filter("T1"."N"=666)
   6 - access("T1"."ID"="T2"."ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)
  • Create the test table T1L which contains the same data as table T1; notice that a CLOB is used instead of a VARCHAR2
SQL> CREATE TABLE t1l
  2  AS
  3  SELECT id, n, to_clob(pad) AS pad
  4  FROM t1;

SQL> ALTER TABLE t1l ADD CONSTRAINT t1l_pk PRIMARY KEY (id);

SQL> execute dbms_stats.gather_table_stats(user,'t1l')
  • The query optimizer does not selects an adaptive plan when T1L and T2 are joined
SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM t1l, t2
  4  WHERE t1l.id = t2.id
  5  AND t1l.n = 666;

SQL> SELECT * FROM table(dbms_xplan.display(format=>'basic +predicate +note +adaptive'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 810895618

----------------------------------------------
| Id  | Operation                    | Name  |
----------------------------------------------
|   0 | SELECT STATEMENT             |       |
|   1 |  NESTED LOOPS                |       |
|   2 |   NESTED LOOPS               |       |
|*  3 |    TABLE ACCESS FULL         | T1L   |
|*  4 |    INDEX UNIQUE SCAN         | T2_PK |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2    |
----------------------------------------------

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

   3 - filter("T1L"."N"=666)
   4 - access("T1L"."ID"="T2"."ID")

Note that when the query optimizer detects the limitation just described, in the 10053 trace it writes the following information:

AP: Adaptive joins bypassed for table T2 @ SEL$1 due to LOB-producing table under statistics collector

The script I wrote to check this behaviour can be downloaded here.