A SQL often runs with dramatically different speed in a production than in a non-production database. Dig deeper and you see the execution plan is different between Prod and Non-prod. People often ask the DBA, Why does the same SQL run with different execution plans in the two databases? To answer that, we must first look at how Non-prod is created or refreshed from Prod. Companies create a Non-prod database from the Prod in one of three ways:

(1) physically clone Prod to Non-prod;
(2) export data from Prod to a dump file and import the dump into Non-prod;
(3) use storage-level technology, such as split mirror

The first two are much more common. When the database is small, either is fine. When the size is, say over 500 GB, (1) is much preferred over (2) for reasons of refresh speed. But some development or testing teams insist on (2) because they have complicated configurations and local data in the Non-prod database, which would be destroyed by (1) and they would have to go through a long process to re-configure Non-prod before it can be used. If your Non-prod is refreshed by method (2), here’s how you can answer the common question about different SQL performance:

Non-prod sure has exactly the same data as the Prod regardless of how you refresh the data. But a Non-prod refreshed by (2) differs from the Prod in

(A) sizes of the tables and indexes, because importing data from the dump file makes the data very compact, thus reducing the segment sizes and eliminating almost all holes in the segments;
(B) data order in the tables relative to the data pointers in the indexes (which is reflected in index clustering factor in Oracle or correlation metric in PostgreSQL)

These differences in physical attributes can be big enough to flip the decision of the cost based optimizer (or SQL query planner) if two competing execution plans have very close calculated costs. Changing the Non-prod refresh method to (1) can eliminate these differences, although other, much minor factors such as number of CPU cores and amount of system memory may still differ between the two environments. But they exert much less influence on the optimizer’s decision.[note1]

As you see, a Non-prod database refreshed by method (2) is prone to generating different, often poorly performing, SQL execution plans. If you are stuck with (2) for business reasons, you are not to blame and should focus on tuning the SQLs locally in Non-prod, including, in case of Oracle, creating SQL plan baselines in Prod and migrate them to Non-prod (sometimes a SQL profile for better cost estimates will also do), using SQL patch to alter the plan, and even modifying the SQLs inside a view. In fact, I just did exactly that today: in spite of the developer’s objection, I added a hint to the text of a view in Non-prod and her job ran for 11 minutes instead of 60 to 90 minutes as usual. She’s so happy as to not voice the objection any more. This change sure means every time I refresh Non-prod, I’ll have to remember to re-create this view. But that’s not a big deal.

In short, if you’re a DBA and people ask you why the SQL runs differently between Prod and Non-prod, request to refresh Non-prod by (1). If that’s not allowed, you already have a good answer for them, and they are to blame![note2]

___________________________________

[note1] The factors that influence the optimizer’s decision, in my opinion, form this hierarchy:
(i) data (number of rows in a table, whether they’re skewed, etc.), index (presence or absence), stats (how they are collected); this has the greatest influence on the optimizer;
(ii) physical attributes (table/index segment size, data arrangement in the segment); this has a small but non-negligible influence;
(iii) server hardware (CPU cores, memory, I/O speed); this has the least influence

[note2] If Non-prod is already refreshed by method (1), or (3), it’s very likely the same SQL runs with the same execution plan as in Prod. If ever the plan differs, check whether the stats are collected exactly the same way.

August 2026
(originally posted on Medium)