Archive for March, 2016

Oracle SQL showing current expensive queries plus most expensive object from execution plan

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.

Key features:

  • 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.

Read more…

By Martin Klier in Oracle,Performance  .::. (Add your comment)

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.