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.
This Content Component encountered an error
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.