Problem solve
Get help with specific problems with your technologies, process and projects.
Query takes longer in Oracle 10.2 than Oracle 9
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. Why?
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
Step 2 of 2:
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 was last published in July 2006
Dig Deeper on Oracle database installation, upgrades and patches
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments