By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.