Query runs fine in pre-prod databases but too slow in production

A SQL query runs fine in pre-prod/ test databases but too slow in production database. What could be the root cause?

Before answer to the above question, you may go though below question and answers to find your answers.

Q.1) Do you have the same number of extents in both databases ?
-- Maybe the segments in the "test" database were reorganized during import/export for example.

Q.2) How busy is your production? Do you have a lot of read waits on prod compare to test?
-- Then you can proceed for further investigation.

Q.3) Do you have more Contentions? 
-- Then check the undo header. if this value to the total request is more than 1% then try to increase the number of rbs.

Q.4) Is your queries sort intensive?
-- Then increasing the parameter SORT_AREA_SIZE will help.
Eventually, i've solved the problem by increasing the parameter SORT_AREA_SIZE from 65536 (default) to 1048576.
After the increasing,the query ran fast. But, you may ask, How can it happen? May the environments are the same.
Then probably there were other users using the system and also there were a lot of disk i/o happening to your temp tablespace. You can specify more temp tablespaces/ tempfiles based on RAC nodes in RAC environments.

Note: no. of active nodes = no. of tempfiles in a temp tablespace

Q.5) Have you verified "Selectivity", "Clustering", and "Histograms" of the used tables in both PROD and TEST?
-- Use below links to get clues..
Click here : Selectivity, Clustering, and Histograms