SGA Trace - How to sort by explain plan COST

Toad for Oracle - Beta
1

Using SGA-Trace drop down. One can search by CPU Time, Elapse time, Buffer gets, physical gets etc .... Its there is option to sort by 'Cost' number of all the SQL explain plans

2

Sorry, there's no way to do that, but here's a SQL that will do it. You can just run it in the editor. If you get any error, you may be using an older version of Oracle than I am (19c) and you'd need to comment out some of these columns.

Note: this SQL isn't perfect - you'll get multiple rows for most queries. It needs to be smarter about joining v$sql_plan and v$sqlarea so it only returns the max cost for each sql_id. But it'll give you what you are looking for.

SELECT distinct vp.cost,
       vs.sql_text, vs.sharable_mem, vs.persistent_mem, vs.runtime_mem, vs.sorts, vs.executions,
       vs.parse_calls, vs.module, substr(vs.action, 1, 64) as action, vs.buffer_gets, vs.disk_reads, vs.version_count,
       vs.loaded_versions, vs.open_versions, vs.users_opening, vs.loads, vs.users_executing,
       vs.invalidations, vs.serializable_aborts, vs.command_type,
       vs.first_load_time,
       rawtohex(vs.address) address, vs.hash_value hash_value, vs.parsing_user_id,
       rows_processed, optimizer_mode
       ,vs.is_obsolete, vs.elapsed_time, vs.cpu_time
       ,vs.Child_latch, vs.fetches
       ,vs.program_id,vs.java_exec_time,vs.plsql_exec_time,vs.user_io_wait_time,
       vs.cluster_wait_time, vs.concurrency_wait_time, vs.application_wait_time,
       vs.direct_writes,  vs.end_of_fetch_count, vs.sql_id
       ,vs.outline_category, vs.sql_profile, vs.last_active_time
       ,vs.is_bind_sensitive, vs.is_bind_aware, vs.sql_plan_baseline
       ,vs.sql_patch, vs.typecheck_mem, vs.io_cell_offload_eligible_bytes, vs.io_interconnect_bytes, vs.physical_read_requests, vs.physical_read_bytes
       ,vs.physical_write_requests, vs.physical_write_bytes, vs.optimized_phy_read_requests, vs.locked_total, vs.pinned_total, vs.io_cell_uncompressed_bytes
       ,vs.io_cell_offload_returned_bytes
       ,vs.con_id, vs.is_reoptimizable, vs.is_resolved_adaptive_plan
       ,vs.direct_reads
FROM   v$sqlarea vs, v$sql_plan vp
WHERE  vs.sql_text not like '%/*("Toad, Exclude Me!")*/%'
AND    vs.parsing_user_id > 0
and    vs.sql_id = vp.sql_id
order by  vp.cost desc nulls last
3

That is awesome…

Many thanks John… works great..

1 Like