What is an Explain Plan?
An explain plan is a representation of the access path that is taken when an SQL is executed within Oracle.
The optimal access path for an SQL is determined by the database optimizer component. With the Rule Based Optimizer (RBO) it uses a set of heuristics to determine access path. With the Cost Based Optimizer (CBO) we use statistics to analyze the relative costs of accessing objects.
Since the Cost Based optimizer relies on actual data volume statistics to determine the access path, to generate an accurate Explain Plan using the cost based optimizer requires a database set up with the proper statistics of a real high volume data environment.
Note:
A cost based optimizer Explain Plan generated on an inadequate database, would be totally inaccurate and misleading!
Obviously, our development database does not qualify as an optimal environment of cost based optimizations. Since the Rule Based optimizer is not data dependant it would provide a more reliable Explain Plan for this database.
Note:
An efficient rule based Explain Plan does not guarantee an efficient cost based one when the SQL is finally executed on the real target database. However, a poor rule based Explain Plan would most probably remain such on a database with a higher volume of data.
Note:
When the SQL is complicated and mainly designed to process high volume tables it is recommended to also analyze its Explain Plan on an appropriate high volume database.