Printable View
I have the same query, and same instance. one is on Development and one is on production.
For some reason, the query was running very fast on development. On the production instance, the query ran very slow and it maxed out my temp tablespace even I set it to 4G, the data never come back
what could be the reason and How can I attack this problems???
thanks
show your query plz.
Same query, same instance. Fine.
Same volume of data ?
Same query execution plan ?
Same stats available ?
Does the explain plan change from development to production? Is it possible that because your have less test data that your development machine has better stats than production? It there a difference in versions of Oracle? You don't mention hardware or OS versions for either production or development. These are all factors that could make a difference. Without more information I can only guess.
You must be doing a lot of sorting in production when you run this query. I think that that is the only conclusion that I can make. but I can't tell you why.
Besides what suggested:
* compare the ratios of data valume and
the size of temp tablespace of your dev and prod databases
* compare temporary tablespace type: temp or perm tablespace
* OS level monitoring