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