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.