Hi,
We are migrating our database from 11g to 12c. The database version is 12.1.0.2.0 - 64bit. There is a Materialized view that refreshes in 1 minute(via DBMS_SNAPSHOT.REFRESH) in 11g where as this is taking around 60 mins to refresh in 12c. As we investigated,the execution plan of SELECT statement used in Materialized view definition is same in both 11g and 12c and also the elapsed time to execute the SELECT statement is same in both the environments. However, the execution plan changes in 12c when the View is refreshed in 12c but not in 11g. Wondering if this is something do with some parameter? Apology for not able to mention the code here due to company policy but the Materialized view definition part is as mentioned below.
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
BUILD IMMEDIATE USING INDEX REFRESH FORCE ON DEMAND USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE Thanks.
Regards;
Gopal