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.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.
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 value9and a different plan for bind value10. 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.
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 :-)
