Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
You've probably not gathered statistics on one of the databases –  Phil Apr 3 at 14:02
    
No, the stats are gathered for every table in the schema regularly. –  Yuriy Apr 3 at 14:09
    
Compare all your parameters (including any hidden parameter you might have touched), and compare the stats on the affected tables. –  Mat Apr 3 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 Apr 6 at 21:11

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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