1、查询oracle快照
select t.snap_id as snap_id,
to_char(t.startup_time, 'yyyy-mm-dd hh24:mi:ss') as "SQL第一次执行时间",
to_char(t.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') as "快照开始时间",
to_char(t.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') as "快照结束时间"
from DBA_HIST_SNAPSHOT t
order by snap_id desc;
2、执行以下SQL,查询S执行时间最长的前10条SQL语句
select *
from (select round((t2.elapsed_time_delta / 1000000), 2) as "elapsed_time(s)",
t2.executions_delta as "executions",
round(((t2.elapsed_time_delta / 1000000) /
t2.executions_delta),
2) as "Elapsed Time per Exec (s)",
round((t2.cpu_time_delta / t2.elapsed_time_delta) * 100, 2) as "%CPU",
--round(t1.user_io_wait_time / t2.elapsed_time_delta * 100, 2) as "%IO",
t1.sql_id as sql_id,
t1.module as "sql module",
t1.sql_text as sql_text,
t1.sql_fulltext as sql_fulltext,
to_char(t3.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') as "begin time",
to_char(t3.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') as "end time"
from v$sqlarea t1, DBA_HIST_SQLSTAT t2, DBA_HIST_SNAPSHOT t3
where t1.sql_id = t2.sql_id
and t2.snap_id = t3.snap_id
and t2.executions_delta is not null
and t2.executions_delta <> 0
and t2.elapsed_time_delta is not null
and t2.elapsed_time_delta <> 0
-- and t1.parsing_schema_name in ('FASP_PLATFORM')
and t2.snap_id = &snap_id
order by t2.elapsed_time_delta desc)
where rownum <= 10;
select t.snap_id as snap_id,
to_char(t.startup_time, 'yyyy-mm-dd hh24:mi:ss') as "SQL第一次执行时间",
to_char(t.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') as "快照开始时间",
to_char(t.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') as "快照结束时间"
from DBA_HIST_SNAPSHOT t
order by snap_id desc;
2、执行以下SQL,查询S执行时间最长的前10条SQL语句
select *
from (select round((t2.elapsed_time_delta / 1000000), 2) as "elapsed_time(s)",
t2.executions_delta as "executions",
round(((t2.elapsed_time_delta / 1000000) /
t2.executions_delta),
2) as "Elapsed Time per Exec (s)",
round((t2.cpu_time_delta / t2.elapsed_time_delta) * 100, 2) as "%CPU",
--round(t1.user_io_wait_time / t2.elapsed_time_delta * 100, 2) as "%IO",
t1.sql_id as sql_id,
t1.module as "sql module",
t1.sql_text as sql_text,
t1.sql_fulltext as sql_fulltext,
to_char(t3.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') as "begin time",
to_char(t3.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') as "end time"
from v$sqlarea t1, DBA_HIST_SQLSTAT t2, DBA_HIST_SNAPSHOT t3
where t1.sql_id = t2.sql_id
and t2.snap_id = t3.snap_id
and t2.executions_delta is not null
and t2.executions_delta <> 0
and t2.elapsed_time_delta is not null
and t2.elapsed_time_delta <> 0
-- and t1.parsing_schema_name in ('FASP_PLATFORM')
and t2.snap_id = &snap_id
order by t2.elapsed_time_delta desc)
where rownum <= 10;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29612373/viewspace-2102745/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29612373/viewspace-2102745/