Q
Problem solve Get help with specific problems with your technologies, process and projects.

Trouble executing a query

When I execute a query (hard parse) from SQL *Plus it takes nearly 30 minutes to complete. The same query that I tried four to five times -- after restarting Oracle server each time -- varies in execution times, such as 10 seconds, 9 seconds, 20 seconds, etc. The most surprising thing is that I am the only person at this time using the Oracle 9i server in this environment.

I have tried to execute the query by providing hints for the index, and it worked fine. Now each time it executes in a two to three seconds in the same scenario. Can you tell me why this happens?

The answer is to enable extended tracing, using DBMS_SUPPORT. Trace several executions of the query, over various circumstances (such as just after restarting the server), and analyze the trace results to find out what is contributing to the query's elapsed time.
This was last published in March 2005

Dig Deeper on Oracle database performance problems and tuning

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close