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