My application has a SQL that takes ~ 30 min to run in the Oracle server in production. It takes about the same in a test Oracle server.
For some reason, in another Oracle server, it runs much faster: only 5 min or so!
These timings are really for the SQL only (no application processing overhead). I got them from Oracle Enterprise Manager. Moreover, they are consistent, i.e. if you rerun the SQL again, you get about the same timings.
Hardware, Oracle version (10g), Data are the same in these three servers. Even the SQL execution plans are exactly the same.
What makes the SQL run so much faster in that test environment?
The SQL is a MERGE:
MERGE /*+ USE_NL(DORMANT_POINTS) */ INTO MKT_CURVE_POINT DORMANT_POINTS USING (SELECT // big select ) ACTIVE_POINTS ON ( // .. ) WHEN MATCHED THEN UPDATE SET DORMANT_POINTS.ACTIVE_PARENT_PRICE = ACTIVE_POINTS.ACTIVE_PARENT_PRICE WHERE DORMANT_POINTS.ACTIVE_PARENT_PRICE <> ACTIVE_POINTS.ACTIVE_PARENT_PRICE;
I suspect that this is something about caching. I noticed a high number of buffer gets as compared with physical reads in the server that runs the SQL fast. That ratio is lower in the server that runs it slowly.
What can explain this huge performance difference?