97 lines
2.9 KiB
SQL
97 lines
2.9 KiB
SQL
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
|
|
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
|
|
|
|
-- Author: Tanel Poder (http://tanelpoder.com | @tanelpoder )
|
|
-- Purpose: A temporary script/hack to display exadata-specific metrics along normal SQL stuff from V$SQL
|
|
|
|
col sql_sql_text head SQL_TEXT format a150 word_wrap
|
|
col sql_child_number head "CH#" for 999
|
|
col offl_attempted_mb HEAD OFFLOAD_MB FOR A14 JUST RIGHT
|
|
|
|
prompt Show SQL text, child cursors and execution stats for SQLID &1 child &2
|
|
|
|
select
|
|
hash_value,
|
|
plan_hash_value,
|
|
child_number sql_child_number,
|
|
sql_text sql_sql_text
|
|
from
|
|
v$sql
|
|
where
|
|
sql_id = ('&1')
|
|
and child_number like '&2'
|
|
order by
|
|
sql_id,
|
|
hash_value,
|
|
child_number
|
|
/
|
|
|
|
select
|
|
child_number sql_child_number,
|
|
address parent_handle,
|
|
child_address object_handle,
|
|
plan_hash_value plan_hash,
|
|
parse_calls parses,
|
|
loads h_parses,
|
|
executions,
|
|
fetches,
|
|
rows_processed,
|
|
rows_processed/nullif(fetches,0) rows_per_fetch,
|
|
cpu_time/1000000 cpu_sec,
|
|
cpu_time/NULLIF(executions,0)/1000000 cpu_sec_exec,
|
|
elapsed_time/1000000 ela_sec,
|
|
buffer_gets LIOS,
|
|
disk_reads PIOS,
|
|
sorts
|
|
-- address,
|
|
-- sharable_mem,
|
|
-- persistent_mem,
|
|
-- runtime_mem,
|
|
-- , PHYSICAL_READ_REQUESTS
|
|
-- , PHYSICAL_READ_BYTES
|
|
-- , PHYSICAL_WRITE_REQUESTS
|
|
-- , PHYSICAL_WRITE_BYTES
|
|
-- , OPTIMIZED_PHY_READ_REQUESTS
|
|
-- , IO_CELL_OFFLOAD_ELIGIBLE_BYTES
|
|
-- , IO_INTERCONNECT_BYTES
|
|
-- , IO_CELL_UNCOMPRESSED_BYTES
|
|
-- , IO_CELL_OFFLOAD_RETURNED_BYTES
|
|
, users_executing
|
|
from
|
|
v$sql
|
|
where
|
|
sql_id = ('&1')
|
|
and child_number like '&2'
|
|
order by
|
|
sql_id,
|
|
hash_value,
|
|
child_number
|
|
/
|
|
|
|
select
|
|
child_number sql_child_number
|
|
, plan_hash_value plan_hash
|
|
, LPAD(CASE WHEN io_cell_offload_eligible_bytes > 0 THEN TO_CHAR(ROUND(io_cell_offload_eligible_bytes / 1048576)) ELSE 'NOT ATTEMPTED' END, 14) offl_attempted_mb
|
|
-- , ROUND(io_cell_offload_eligible_bytes / 1048576) offl_attempted_mb
|
|
, ROUND((1-(io_cell_offload_returned_bytes/NULLIF(io_cell_offload_eligible_bytes,0)))*100) scan_offl_saving
|
|
, ROUND(io_interconnect_bytes / 1048576) tot_ic_xfer_mb
|
|
, ROUND((1-(io_interconnect_bytes/NULLIF(physical_read_bytes,0)))*100) tot_ic_xfer_saving
|
|
, ROUND(physical_read_bytes / NULLIF(executions,0) / 1048576) avg_mb_rd_exec
|
|
, ROUND(physical_read_bytes / NULLIF(physical_read_requests,0) / 1024 ) avg_kb_rd_io
|
|
, ROUND(physical_write_bytes / NULLIF(executions,0) / 1048576) avg_mb_wr_exec
|
|
, ROUND(physical_write_bytes / NULLIF(physical_write_requests,0) / 1024 ) avg_kb_wr_io
|
|
, ROUND(optimized_phy_read_requests / NULLIF(physical_read_requests,0) * 100) pct_optim
|
|
-- , io_cell_uncompressed_bytes
|
|
from
|
|
v$sql
|
|
where
|
|
sql_id = ('&1')
|
|
and child_number like '&2'
|
|
order by
|
|
sql_id,
|
|
hash_value,
|
|
child_number
|
|
/
|
|
--@pr
|
|
|