54 lines
2.5 KiB
SQL
54 lines
2.5 KiB
SQL
@@myheader.sql
|
|
|
|
COLUMN iname HEADING "Instance" FORMAT A8
|
|
COLUMN snap_id HEADING "Snap|Id" FORMAT 9999999
|
|
COLUMN endsnaptime HEADING "End|snaphot|time" FORMAT A11
|
|
COLUMN plan_hash_value HEADING "Plan|hash|value"
|
|
COLUMN executions_delta HEADING "#Ex" FORMAT 999
|
|
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 999
|
|
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,
|
|
sqlstat.snap_id,
|
|
to_char(end_interval_time,'dd/mm hh24:mi') endsnaptime,
|
|
plan_hash_value,
|
|
optimizer_cost,
|
|
executions_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 on (sqlstat.snap_id=snap.snap_id) and (sqlstat.INSTANCE_NUMBER=snap.INSTANCE_NUMBER)
|
|
join gv$instance i on sqlstat.instance_number=i.instance_number
|
|
where
|
|
snap.begin_interval_time>=&&2
|
|
and snap.end_interval_time<=&&3
|
|
and sql_id='&&1'
|
|
and executions_delta>0
|
|
order by
|
|
sqlstat.snap_id
|
|
/
|
|
|
|
|
|
@@myfooter.sql
|