54 lines
2.6 KiB
MySQL
54 lines
2.6 KiB
MySQL
|
COLUMN iname HEADING "Instance" FORMAT A8
|
||
|
|
COLUMN snap_id HEADING "Snap|Id" FORMAT 9999999
|
||
|
|
COLUMN endsnaptime HEADING "End|snaphot|time" FORMAT A11
|
||
|
|
COLUMN sql_id HEADING "SQL_ID" FORMAT A13
|
||
|
|
COLUMN plan_hash_value HEADING "Plan|hash|value"
|
||
|
|
COLUMN executions_delta HEADING "#Ex" FORMAT 9999999
|
||
|
|
COLUMN end_of_fetch_count_delta HEADING "#EOF" FORMAT 9999999
|
||
|
|
COLUMN buffer_gets_delta HEADING "Buffer|gets"
|
||
|
|
COLUMN bufferperexec HEADING "Buffer|gets|/exec" FORMAT 9999999999
|
||
|
|
COLUMN optimizer_cost HEADING "0ptimizer|cost"
|
||
|
|
COLUMN rows_processed_delta HEADING "#Rows"
|
||
|
|
COLUMN sql_profile HEADING "SQL|Prof" FORMAT A4
|
||
|
|
COLUMN elapsed_time_delta HEADING "Elapsed|time|/exec|(sec)" FORMAT 99999
|
||
|
|
COLUMN cpu_time_delta HEADING "CPU|time|/exec|(sec)" FORMAT 9999
|
||
|
|
COLUMN iowait_delta HEADING "IO|time|/exec|(sec)" FORMAT 99999
|
||
|
|
COLUMN px_servers_execs_delta HEADING "Px" FORMAT 9999
|
||
|
|
COLUMN disk_reads_delta HEADING "Disk|reads"
|
||
|
|
COLUMN io_offload_elig_bytes_delta HEADING "Elig|Mb" FORMAT 999999
|
||
|
|
COLUMN io_interconnect_bytes_delta HEADING "Inter|Mb" FORMAT 999999
|
||
|
|
|
||
|
|
select
|
||
|
|
i.instance_name iname,
|
||
|
|
snap_id,
|
||
|
|
to_char(end_interval_time,'dd/mm hh24:mi') endsnaptime,
|
||
|
|
sql_id,
|
||
|
|
plan_hash_value,
|
||
|
|
optimizer_cost,
|
||
|
|
executions_delta,
|
||
|
|
end_of_fetch_count_delta,
|
||
|
|
buffer_gets_delta,
|
||
|
|
buffer_gets_delta/executions_delta bufferperexec,
|
||
|
|
decode (sql_profile,null, '',substr(sql_profile,1,4)) sql_profile,
|
||
|
|
px_servers_execs_delta,
|
||
|
|
disk_reads_delta,
|
||
|
|
round(elapsed_time_delta/1000000) elapsed_time_delta,
|
||
|
|
round(cpu_time_delta/1000000) cpu_time_delta,
|
||
|
|
round(iowait_delta/1000000) iowait_delta,
|
||
|
|
io_offload_elig_bytes_delta/1024/1024 io_offload_elig_bytes_delta,
|
||
|
|
io_interconnect_bytes_delta/1024/1024 io_interconnect_bytes_delta,
|
||
|
|
rows_processed_delta
|
||
|
|
from
|
||
|
|
dba_hist_sqlstat sqlstat
|
||
|
|
join dba_hist_snapshot snap using (snap_id,instance_number)
|
||
|
|
join gv$instance i using (instance_number)
|
||
|
|
where
|
||
|
|
snap.begin_interval_time>=&&2
|
||
|
|
and snap.end_interval_time<=&&3
|
||
|
|
and force_matching_signature='&&1'
|
||
|
|
and executions_delta>0
|
||
|
|
order by
|
||
|
|
snap_id asc
|
||
|
|
/
|
||
|
|
|