Can anyone tell me when we should use gather_plan_statistics hint or when we should use explain plan. What is the actual purpose of gather_plan_statistics hint.
Asked
Active
Viewed 398 times
2 Answers
4
Yes, it looks confusing but is quite simple:
- Explain plan looks at a query and creates an theoretical execution plan.
- Gather_plan_statistics instructs the optimizer to keep notes during the actual execution, to be able to compare the theory with reality.
See this question for details.
wolφi
- 8,091
- 2
- 35
- 64
1
An even better option is the generate a SQL Monitor report. SQL's are automatically monitored, so no need to re-run with the hint. The SQL Monitor report provides far more information than gether_plan_statistics. Finally, the stats in gather_plan_statistics are not reliable when executing parallel queries.
BobC
- 4,208
- 1
- 12
- 15