I have a query joining a number of long tables (primary key in one table, indexed column in another) which results in different plans on different copies of the same database.
The structure of copies including indices is the same and the data is 99+% the same (copies are made within a week from each other so some of them hold a bit more or fewer rows, the difference is totally insignificant for that particular query).
The problem is that on some copies Oracle applies the indices as expected while on another ones there is always a full table scan even if you specify a hint.
What factors can be influencing the plan apart from the data structure itself? For example, are there any dynamic ones such available RAM or anything like that? In other words, what should I check after I make sure that the DB structure of the "good" copy (which uses the indices) is identical to the "bad" one (which uses full scans)?
Or is it nonsense and different plans mean differences in data structure which I simply haven't noticed for some reason?