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?

  • You've probably not gathered statistics on one of the databases
    – Philᵀᴹ
    Commented Apr 3, 2014 at 14:02
  • No, the stats are gathered for every table in the schema regularly.
    – Yuriy
    Commented Apr 3, 2014 at 14:09
  • Compare all your parameters (including any hidden parameter you might have touched), and compare the stats on the affected tables.
    – Mat
    Commented Apr 3, 2014 at 14:12
  • If you are using bind variables and histograms (enabled by default), I'd first of all blame bind peeking. Try disabling automatic histogram collection for the tables/columns involved.
    – a1ex07
    Commented Apr 6, 2014 at 21:11

1 Answer 1


There are other parameters that can influence execution plan, like PGA size, number of CPUs, disk I/O speed, different sample sizes when you are gathering statistics, index modifications, different system load, different block sizes and so on and on.

Just too many to check them all.

You could try sqlTXplain or sql Health Check Oracle's tools to find the root cause of this problem.

Run it for the same query on both databases and compare parameters that you will get(sqlTXplain compare method).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.