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

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.

Please create a username to comment.