-- Demo case: Researching statistics feedback on 12.2 -- Date: 23-12-2016 -- References: http://blog.dbi-services.com/sql-plan-directives-in-12cr2-behavior-is-different-than-in-12cr1/ -- http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf -- Demo run on Oracle version: 12.2.0.1.0 (EE Extreme Perf) -- Setup data drop table DEMO_TABLE purge; create table DEMO_TABLE as select mod(rownum,2) a, mod(rownum,2) b, mod(rownum,2) c, mod(rownum,2) d from dual connect by level <=1000; alter session set optimizer_adaptive_statistics=false; alter system flush shared_pool; -- Run test case with disabled optimizer_adaptive_statistics -- 1st run select * from DEMO_TABLE where a=0 and b=0 and c=0 and d=0; select sql_id, child_number, is_reoptimizable, executions, rows_processed from v$sql where sql_id='g8yr3md0sxrqa'; -- 2nd run select * from DEMO_TABLE where a=0 and b=0 and c=0 and d=0; select sql_id, child_number, is_reoptimizable, executions, rows_processed from v$sql where sql_id='g8yr3md0sxrqa'; -- 3rd run select * from DEMO_TABLE where a=0 and b=0 and c=0 and d=0; select sql_id, child_number, is_reoptimizable, executions, rows_processed from v$sql where sql_id='g8yr3md0sxrqa'; -- Run same test case with enabled optimizer_adaptive_statistics alter session set optimizer_adaptive_statistics=true; alter system flush shared_pool; -- 1st run select * from DEMO_TABLE where a=0 and b=0 and c=0 and d=0; select sql_id, child_number, is_reoptimizable, executions, rows_processed from v$sql where sql_id='g8yr3md0sxrqa'; -- 2nd run select * from DEMO_TABLE where a=0 and b=0 and c=0 and d=0; select sql_id, child_number, is_reoptimizable, executions, rows_processed from v$sql where sql_id='g8yr3md0sxrqa'; -- 3rd run select * from DEMO_TABLE where a=0 and b=0 and c=0 and d=0; select sql_id, child_number, is_reoptimizable, executions, rows_processed from v$sql where sql_id='g8yr3md0sxrqa';