Ask the Expert

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. 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?

    Requires Free Membership to View

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 first published in July 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: