-- Execute it in SH Schema (Oracle 12c)

DROP TABLE MYTABLE;
--Example 1
CREATE TABLE MYTABLE AS
SELECT DECODE(mod(rownum,2),0,'N','Y') Counter1 ,
       DECODE(mod(rownum,2),0,'Y','N') Counter2,
	   a.*
FROM ALL_OBJECTS a;

--Select Data from the table
SELECT * FROM MYTABLE;

-- Create Index on Both the columns
CREATE INDEX idxxx1 on MYTABLE(Counter1,Counter2);

-- Generate the STATS(Table, Column and Histogram)
EXEC  DBMS_STATS.GATHER_TABLE_STATS (USER,'MYTABLE' , METHOD_OPT => 'for all columns size 254' );

-- Check the STATS Data
SELECT * FROM USER_TAB_STATISTICS WHERE TABLE_NAME = 'MYTABLE';
SELECT * FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME = 'MYTABLE';

--Query 1
SELECT /*+ GATHER_PLAN_STATISTICS */ count(*) FROM MYTABLE 
WHERE  Counter1 = 'Y' AND Counter2 = 'Y'; 
-- Check the Execution Plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST'));

--Query 2
SELECT /*+ GATHER_PLAN_STATISTICS dynamic_sampling(0) */ count(*) FROM MYTABLE 
WHERE  Counter1 = 'Y' AND Counter2 = 'Y';  
-- Check the Execution Plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST'));

--Query 3
SELECT /*+ GATHER_PLAN_STATISTICS dynamic_sampling(1) */ count(*) FROM MYTABLE 
WHERE  Counter1 = 'Y' AND Counter2 = 'Y'; 
-- Check the Execution Plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST'));

--Query 4
SELECT /*+ GATHER_PLAN_STATISTICS dynamic_sampling(2) */ count(*) FROM MYTABLE 
WHERE  Counter1 = 'Y' AND Counter2 = 'Y'; 
-- Check the Execution Plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST'));

--Query 5
SELECT /*+ GATHER_PLAN_STATISTICS dynamic_sampling(3) */ count(*) FROM MYTABLE 
WHERE  Counter1 = 'Y' AND Counter2 = 'Y'; 
-- Check the Execution Plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST'));

--Query 6
SELECT /*+ GATHER_PLAN_STATISTICS dynamic_sampling(4) */ count(*) FROM MYTABLE 
WHERE  Counter1 = 'Y' AND Counter2 = 'Y'; 
-- Check the Execution Plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST'));

--Query 7
SELECT /*+ GATHER_PLAN_STATISTICS dynamic_sampling(5) */ count(*) FROM MYTABLE 
WHERE  Counter1 = 'Y' AND Counter2 = 'Y'; 
-- Check the Execution Plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST'));

--Query 8
SELECT /*+ GATHER_PLAN_STATISTICS dynamic_sampling(6) */ count(*) FROM MYTABLE 
WHERE  Counter1 = 'Y' AND Counter2 = 'Y'; 
-- Check the Execution Plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST'));

--Query 9
SELECT /*+ GATHER_PLAN_STATISTICS dynamic_sampling(7) */ count(*) FROM MYTABLE 
WHERE  Counter1 = 'Y' AND Counter2 = 'Y'; 
-- Check the Execution Plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST'));

--Query 10
SELECT /*+ GATHER_PLAN_STATISTICS dynamic_sampling(8) */ count(*) FROM MYTABLE 
WHERE  Counter1 = 'Y' AND Counter2 = 'Y'; 
-- Check the Execution Plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST'));

--Query 11
SELECT /*+ GATHER_PLAN_STATISTICS dynamic_sampling(9) */ count(*) FROM MYTABLE 
WHERE  Counter1 = 'Y' AND Counter2 = 'Y'; 
-- Check the Execution Plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST'));

--Query 12
SELECT /*+ GATHER_PLAN_STATISTICS dynamic_sampling(10) */ count(*) FROM MYTABLE 
WHERE  Counter1 = 'Y' AND Counter2 = 'Y'; 
-- Check the Execution Plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST'));

--Query 13
SELECT /*+ GATHER_PLAN_STATISTICS dynamic_sampling(11) */ count(*) FROM MYTABLE 
WHERE  Counter1 = 'Y' AND Counter2 = 'Y'; 
-- Check the Execution Plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST'));

