Unanswered: Oracle 12c not able to pick up latest stats
I am new to this community and so not aware of the rules of this forum.
I am in middle of a problem since last few months and thought of seeking your expert advice.
We recently have migrated to oracle 12c at one of our client location who was earlier using Oracle 11g.
There is a performance degradation since moving to Oracle 12c as compared to Oracle 11g for same set of data and process.
While optimising the performance, what i have seen is there is a process which creates new entries in few of the tables in schema. The package which has lot of open cursors reads information from these tables and writes in corresponding table of same schema when package is executed. However, this process is slow if gather_schema_stats (granuality = GLobal) is not used after entry is made in the new tables and before the package gets executed.
For eg: I have tested the below scenarios and below are the results:
1. Run Gather stats on the schema - > Process to create new entries in table -> Run Package for new entries in table
Outcome - Package execution time is 4.5 hours
2. Process to create new entries in table -> Run Gather stats on the schema - > Run Package for new entries in table
Outcome - Package execution time is 9 minutes
We also have used optimiser dynamic sampling and in_memory features but still issue exists.
The problem is we cannot use gather_schema_stats in between the process and it has to be run only once either before or after the complete process.
We also tried to replace gather_schema_stats with gather_stats for only tables and indexes but no effect.
gather_schema_stats is a very time consuming activity and cannot be used.