I have a query which takes indefinite time, taking almost 60% of CPU time for 3 hours then simply hangs the PLSQL process. What I do is:
- Get "explain plan" for the query
- I noticed a "table scan" in the "explain plan" graph
- I added an index for the specific column
- I refreshed statistics for this table
I now get "explain plan" but the figures are the same and no execution optimization.