|
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; select * from dba_hist_snapshot x ; select * from v$database; select * from v$instance; 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 |