By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
We have a complex query that took 2 seconds to execute in Oracle 9 with the "ordered" hint. The same query takes 8 seconds in Oracle 10.2. Without the hint, it takes 12 seconds executed the first time and 0.2 seconds the second time in 10.2. If I leave the hint it takes 8 seconds regardless of how many times it executes. Why? Also, I know there is a way to cache an often used table. Is there a way to do this for a query? Or is it just going to be based on the most recently used (MRU) strategy?
I don't know offhand why your query performs this way; you should trace the query using DBMS_MONITOR, then format the output with TKPROF and examine the plan and statistics. These should reveal the components of the elapsed time to execute the query.