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.

Exclusions:

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

The Code

showing current expensive queries plus most expensive object from execution plan:

select sa.sql_id,
  sp.child_number,
  sp.plan_hash_value,
  sa.parsing_schema_name SQL_PARSED_BY,
  sa.module,
  sa.action,
  sa.buffer_gets,
  sa.BG_PER_EXEC,
  sp.object_owner,
  --sp.object_name, -- this is the group factor
  sa.optimizer_cost,
  sp.cost COST_PER_EXP_OBJECT,
  round((sp.cost/sa.optimizer_cost)*100,0) OBJECT_COST_RELATION,
  listagg(OBJECT_NAME,', ') within group (order by object_name) MOST_EXPENSIVE_OBJECTS,
  sa.sql_text
 from
   (select sql_id,
     child_number,
     parsing_schema_name,
     module,action,
     buffer_gets,
     round(buffer_gets/nullif(executions,0),0) BG_PER_EXEC,
     optimizer_cost,
     sql_text
    from v$sql
    where parsing_schema_name <>'SYS'
   ) sa,
   (select sql_id, child_number, plan_hash_value, object_owner, object_name,cost,
     rank() over (partition by sql_id,child_number order by cost desc nulls last) costrank
     -- result set partitioned by sql and child to avoid duplicates 
     -- when same sql is executed by multiple users
    from v$sql_plan
    where (operation like '%INDEX%' or operation like '%TABLE%' or operation like '%MAT%')
     --and options='FULL' -- would limit to full table scans / full object scans
     and object_owner <>'SYS'
   ) sp
 where sa.sql_id=sp.sql_id
   and sa.child_number=sp.child_number
   and costrank=1 -- only use top costly object of each partition
 group by sa.sql_id,
   sp.child_number,
   sp.plan_hash_value,
   sa.parsing_schema_name,
   sa.module,
   sa.action,
   sa.buffer_gets,
   sa.BG_PER_EXEC,
   sp.object_owner,
   --sp.object_name, -- this is the group factor
   sa.optimizer_cost,
   sp.cost,
   round((sp.cost/sa.optimizer_cost)*100,0),
   sa.sql_text
 order by BG_PER_EXEC desc nulls last
 ;

Let me explain

what this SQL query does, what’s the thinking behind:

• inner join v$sql with v$sql_plan by sql_id and child_number
• exclude SQL parsed by SYS (v$sql)
• exclude objects owned by SYS (v$sql_plan)
• limit result to accessing indexes, tables and materialized views, to avoid getting joins and other mechanics operations in the result
• partition the v$sql_plan result by sql_id and child number, to get a ranking of the most expensive execution plan operation for this cursor (pre-filtered by the last bullet in this list).
• Partition is sorted descending to have the most expensive operation on top
• limit results to only the top row from the query partitions (costrank=1)
• list-aggregate duplicate objects (coming from same cost in a plan), and that’s also the reason for the GROUP BY operation

Hope this helps in some situations – if you have comments or questions, just let me know.

Take care
Martin Klier