Oracle: V$SQL

Showing some statistics of an SQL statement

select
   sql.executions, -- sql.avoided_executions,
   sql.fetches,
   sql.parse_calls,
   sql.last_active_time, -- sql.first_load_time,    sql.last_load_time,
   sql.rows_processed,
   sql.buffer_gets,
   sql.direct_reads , sql.disk_reads, sql.physical_read_bytes , sql.physical_read_requests,
   sql.direct_writes,                 sql.physical_write_bytes, sql.physical_write_requests,
   round(sql.runtime_mem       / 1024/1024, 1)  runtime_mem_mb,
   round(sql.persistent_mem    / 1024/1024, 1)  persistent_mem_mb,
   round(sql.sharable_mem      / 1024/1024, 1)  sharable_mem_mb,
   round(sql.typecheck_mem     / 1024/1024, 1)  typecheck_mem_mb,
   round(sql.cpu_time          / 1e6      , 1)  cpu_time_s,
   round(sql.plsql_exec_time   / 1e6      , 1)  plsql_exec_time_s,
   round(sql.java_exec_time    / 1e6      , 1)  java_exec_time_s,
   round(sql.elapsed_time      / 1e6      , 1)  elapsed_time_s,
   round(sql.user_io_wait_time / 1e6      , 1)  user_io_wait_time,
   sql.result_cache,
   sql.sorts,
   sql.users_executing,
-- sql.users_opening,
   sql.application_wait_time,
-- sql.address address, sql.child_address,
-- sql.child_latch                         child_latch,
   sql.child_number                        child_number,
   sql.cluster_wait_time                   cluster_wait_time,
   sql.command_type                        command_type,
   sql.concurrency_wait_time               concurrency_wait_time,
   sql.con_id                              con_id,
-- sql.ddl_no_invalidate                   ddl_no_invalidate,
-- sql.end_of_fetch_count                  end_of_fetch_count,
-- sql.im_scans,
-- sql.im_scan_bytes_inmemory,
-- sql.im_scan_bytes_uncompressed,
-- sql.invalidations,
   sql.action, -- sql.action_hash,
-- sql.io_cell_offload_eligible_bytes,
-- sql.io_cell_offload_returned_bytes,
-- sql.io_cell_uncompressed_bytes,
-- sql.io_interconnect_bytes,
-- sql.is_bind_aware,
-- sql.is_bind_sensitive,
-- sql.is_obsolete,                      -- see v$sql_reoptimization_hints
   sql.is_reoptimizable,
-- sql.is_resolved_adaptive_plan,
-- sql.is_rolling_invalid,
-- sql.is_rolling_refresh_invalid,
   sql.is_shareable,
   sql.kept_versions,
   sql.literal_hash_value,
   sql.loaded_versions,
   sql.loads,
   sql.locked_total,
-- sql.module, sql.module_hash,
-- sql.object_status,
-- sql.open_versions,
-- sql.optimized_phy_read_requests,
   sql.optimizer_cost,
-- sql.optimizer_env, sql.optimizer_env_hash_value,
   sql.optimizer_mode,
   sql.outline_category,
   sql.outline_sid,
   sql.parsing_schema_name, -- sql.parsing_schema_id, sql.parsing_user_id,
-- sql.pinned_total                        pinned_total,
-- sql.plan_hash_value, sql.full_plan_hash_value,
-- sql.program_id,
-- sql.program_line#,
   sql.px_servers_executions,
-- sql.remote,
-- sql.serializable_aborts,
-- sql.service, sql.service_hash,
-- sql.sqltype,
   sql.sql_fulltext, -- sql.sql_text,
   sql.sql_id
-- sql.hash_value, sql.old_hash_value,
-- sql.exact_matching_signature, sql.force_matching_signature,
-- sql.sql_patch,
-- sql.sql_plan_baseline,
-- sql.sql_profile,
-- sql.sql_quarantine,
-- sql.type_chk_heap                       type_chk_heap
-- sql.bind_data                           bind_data,
-- sql.con_id                              con_id
from
   v$sql sql
where
   lower(sql.sql_text) like '%tq84%'
-- sql_id = '8y1axh4277fm4'
order by
   sql.last_active_time desc
;

Showing an SQL's execution plan

See also