You Asked
Dear Team,
Greetings !!!
I am having a situation where our modified and tuned code works fine in Development instance.(PL SQL package)
But the same code as it is, with all the hints, indexes and queries takes a long time time to execute on another instance (TEST).
The operations involve all the DML like INSERT, UPDATE , DELETE for various tables.
The code is the SAME in both.
However TEST gives a much slower performance than DEV.
Is there any way of how to diagnose this?
I"ll not be able to share the exact code as it is in a restricted environment.
The problem what I have stated is the main issue.
Regards
Kalyana
and Chris said...
There's practically an endless list of reasons you could end up with different performance. It's likely to be down to one of these:
- Different data volumes (e.g. few rows in dev, millions in test)
- Optimizer differences (freshness of stats, presence absence of SQL profiles/baselines, ...)
- The impact of going from a single user dev instance to multi-user test instance
- Different hardware or configuration settings
The first thing to do is figure out exactly which statements are slower in test. You can do this by tracing your code using a SQL trace or the PL/SQL hierarchical profiler. I give an overview of these in this video:
with all the hints
I strongly advise you to remove all the hints, then compare performance. There's a high chance of data differences between dev and test. Meaning these may give optimal plans in dev, but prevent the optimizer choosing a plan better suited to the data in test.
Is this answer out of date? If it is, please let us know via a Comment