Oracle: Difference in Execution Plans Between Databases
I am comparing the queries of my development base and production base.
They are both Oracle 9i, but almost every single query has a completely different execution plan depending on the database.
All tables / indexes are the same, but the dev database contains about 1/10 rows for each table.
In production, the query execution plan he chooses for most queries differs from design, and the cost is several times more than 1000 times. Production queries also do not seem to use the correct indexes for queries in some cases (full table access).
I recently ran the dbms_utility.analyze schema on both databases in the hopes that CBO comes up with something.
Is there some other basic oracle configuration that might be causing this?
I am a developer mainly, so this kind of DBA analysis is rather confusing at first.
a source to share
1) The first thing I would like to check is the database options equivalent for Prod and Dev. If one of the parameters influencing the Cost Optimizer's decisions differs from the others, all bids are disabled. You can see the parameter in the v $ parameters view;
2) Having up-to-date object statistics is great, but remember the big difference you pointed out - Dev has 10% Prod lines. This series of lines counts towards how the CBO decides the best way to execute the query. Given the large difference in row counts, I would not expect the plans to be the same.
Depending on the circumstances, the optimizer may select Full Table Scan a table with 20,000 rows (Dev), where it may decide that the index is a lower value on a table with 200,000 rows (Prod). (Numbers are for demonstration only, CBO uses calculating algorithms to determine what is for FTS and for indexing, not absolute values.)
3) The system statistics also take into account the explanation plans. It is a set of statistics that represent the characteristics of the processor and I / O disk. If your hardware is different on both systems, I would expect your system statistics to be different, and this may affect plans. Nice discussion from Jonathan Lewis here You can view system statistics via the sys.aux_stats $ view.
Now I'm not sure why different plans are bad for you ... if the statistics are up to date and the parameters are set correctly, you should be getting decent performance from any system no matter what the size difference is ...
but it is possible to export statistics from your Prod system and load them into your Dev system. This will make your Prod stats available to your Dev database.
Check the Oracle documentation for the DBMS_STATS package, specifically the procedures EXPORT_SCHEMA_STATS, EXPORT_SYSTEM_STATS, IMPORT_SCHEMA_STATS, IMPORT_SYSTEM_STATS. Keep in mind that you may need to disable work orders at 10pm 10g / 11g ... or you can investigate the post-import lock statistics so they don't get updated by nightly jobs.
a source to share