Sometimes, it’s useful to see what’s currently going on in your Oracle Database. This query is meant to help with this task, that haunts every DBA from time to time. It’s just using v$sql and v$sql_plan, so no need for Enterprise Edition and Diagnostics Pack.
- find top buffer getters per execution from v$sql
- add most costly object access from corresponding execution plan (from all cursors in question), using v$sql_plan
- shows possible duplicates of the latter with same cost in a list aggregate, to reduce number of lines
- shows the percentage of how much cost impact the most expensive object access has. related to the overall cost of the execution plan.
- does not show plans without SQL and no SQL without plan
- has a limited view on object impact: Accessing objects is rated, but not that they cause bigger join costs etc.