Monday, June 30, 2008

Execution plan for the queries in the statspack report and queries in top sql

Most of the time when I go through statspack report, I also wanted to view the execution plan for queries listed in the report.
Similarly many times I wanted to view the top N expensive queries and it's plan in report. It is possible to navigate to the plan from top sql in Oracle Enterprise Manager. However you have to do it one by one. also you may not get the result in report form.
The following two scripts can be used for these purposes.
stats_plan.sql
topsql_plan.sql
Note: Report for stats_plan needs to be run immediately after taking the snap otherwise you may not get all queries listed.

No comments: