Skip to Content

Full table scan increased in EWA report

Nov 25, 2016 at 03:47 AM


avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Fidel Vales
Nov 25, 2016 at 05:01 PM


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?)

10 |10000 characters needed characters left characters exceeded
sundar chelladurai Nov 28, 2016 at 08:49 AM

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.

10 |10000 characters needed characters left characters exceeded
Stefan Koehler Nov 28, 2016 at 04:44 PM

Hey Sundar,

do you face any performance issues after upgrade? If not - you have no problem: "Compulsive Tuning Disorder".



P.S.: IMHO EWA is for trash can in 99% of the cases.

10 |10000 characters needed characters left characters exceeded