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?
Best Answer
Concurrency, locking and latches may play a part. I assume the production server is doing something other than wait for this particular query?
Is the memory policy set to auto? Perhaps the production server has allocated it's SGA pool differently.
It's also a question of what data is actually in in the shared pool and buffer cache. The test server may have more of the relevant data in the buffer, not being flushed out by other production queries.
Then there is hardware configuration. Something simple like write back cache could make a huge difference.
Fun as it is, lets not waste all our time on guesses. Trace the query with full timings and see what's really going on :)