Hello Experts
We have observed our EWA report,
Report indicates that no. of full table scans has increased by 30% since one week compare to previous weeks. We have done the oracle upgrade from 11g to 12c during this period.
What are the factors influenced to increase the no.of.full table scan. I suspect the below reasons and investigated, but no luck.
1. Missing index
2. Statistics is out of date
Hi,
There ist enough information to give you a proper answer. But (unfortunately) I see a misconception in your "reasons".
a lot of people "blame" "old" statistics. The statistics do not have to be "new", keep in mind that a couple of minutes after they have been calculated they are already "old".
Statistics have to be acurate and representative, and some times "false".
Usually the reason is lack of index.
In any case, you must
1.- find out what queries are doing those table scans
2.- find out if the queries make sense (if you need 99% of the rows in the table a FTS makes sense, if you want one row, it does not make sense)
3.- if an index is desired (second case), does an index already exist?
a.- no, then create one
b.- yes, then analyze why it is not taken. This step is the one a lot of people misses. it might be due to statistcs (why?), to a bug (which one?)
Hello Fidel Vales
Thanks for the info
Can you help me to find the list of tables which are in part of FTS. Is there any query available to identify.
So I can check the query and index accordingly.
Hey Sundar,
do you face any performance issues after upgrade? If not - you have no problem: "Compulsive Tuning Disorder".
Regards
Stefan
P.S.: IMHO EWA is for trash can in 99% of the cases.