实用oracle脚本

wait_event

SELECT inst_id,EVENT, SUM(DECODE(WAIT_TIME, 0, 0, 1)) "Prev", SUM(DECODE(WAIT_TIME, 0, 1, 0)) "Curr", COUNT(*) "Tot" , sum(SECONDS_IN_WAIT) SECONDS_IN_WAIT
FROM GV$SESSION_WAIT
WHERE event NOT
IN ('smon timer','pmon timer','rdbms ipc message','SQL*Net message from client','gcs remote message')
AND event NOT LIKE '%idle%'
AND event NOT LIKE '%Idle%'
AND event NOT LIKE '%Streams AQ%'
GROUP BY inst_id,EVENT
ORDER BY 1,5 desc;

 

session_by_XX

--根据等待事件查会话
SELECT /*+rule */ sid, s.serial#, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, BLOCKING_INSTANCE||'.'||blocking_session b_sess FROM v$session s, v$process p WHERE event='&event_name' AND s.paddr = p.addr order by 6;

--根据用户查会话
SELECT /*+rule */ sid, s.serial#, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, BLOCKING_INSTANCE||'.'||blocking_session b_sess FROM v$session s, v$process p WHERE s.username='&user_name' AND s.paddr = p.addr order by 6

--根据SQL_ID查会话
SELECT /*+rule */ sid, s.serial#, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, BLOCKING_INSTANCE||'.'||blocking_session b_sess FROM v$session s, v$process p WHERE s.sql_id='&sql_id' AND s.paddr = p.addr order by 6

--根据会话ID查会话详情
SELECT s.sid, s.serial#, spid, event, sql_id, PREV_SQL_ID, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, module,blocking_session b_sess,logon_time FROM v$session s, v$process p WHERE sid = '&sid' AND s.paddr = p.addr;

--查询阻塞会话
select count(*),blocking_session from v$session where blocking_session is not null group by blocking_session;

--查询会话的对象信息
col OBJECT_NAME for a30
select owner,object_name,subobject_name,object_type from dba_objects where object_id=&oid;

 

kill_session

--杀某个SID会话
SELECT /*+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE sid='&sid' AND s.paddr = p.addr order by 1;

--根据SQL_ID杀会话
SELECT /*+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE sql_id='&sql_id' AND s.paddr = p.addr order by 1;

--根据等待事件杀会话
SELECT /*+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE event='&event' AND s.paddr = p.addr order by 1;

--根据用户杀会话
SELECT /*+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE username='&username' AND s.paddr = p.addr order by 1;

--kill所有LOCAL=NO进程
ps -ef|grep LOCAL=NO|grep $ORACLE_SID|grep -v grep|awk '{print $2}' |xargs kill -9


lock

--查询某个会话的锁
select /*+rule*/SESSION_ID,OBJECT_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS,LOCKED_MODE from gv$locked_object where session_id=&sid;

--查询TMTX锁
select /*+rule*/* from v$lock where ctime >100 and type in ('TX','TM') order by 3,9;

--查询数据库中的锁
select /*+rule*/s.sid,p.spid,l.type,round(max(l.ctime)/60,0) lock_min,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name from v$session s, v$process p,v$lock l,v$locked_object o,dba_objects b where  o.SESSION_ID=s.sid and s.sid=l.sid and o.OBJECT_ID=b.OBJECT_ID and s.paddr = p.addr and l.ctime >100 and l.type in ('TX','TM','FB') group by s.sid,p.spid,l.type,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name order by 9,1,3,4;
    

active_session

 --活动会话的sql语句

select /*+rule */ distinct ses.SID, ses.sql_hash_value, ses.USERNAME, pro.SPID "OS PID", substr(stx.sql_text,1,200)
from V$SESSION ses
    ,V$SQL stx
    ,V$PROCESS pro 
where ses.paddr = pro.addr 
and ses.status = 'ACTIVE' 
and stx.hash_value = ses.sql_hash_value ;

--活动会话的等待事件
prompt Active session with wait
select  /*+rule */ sw.event,sw.wait_time,s.username,s.sid,s.serial#,s.SQL_HASH_VALUE  
from v$session s, v$session_wait sw  
where s.sid=sw.sid  
and s.USERNAME is not null 
and s.status = 'ACTIVE'; 

running_job

--查看运行的JOB并中断运行
select sid,job from dba_jobs_running;  
select sid,serial# from v$session where sid='&sid';
alter system kill session '&sid,&serial';
exec dbms_job.broken(&job,true);


sess_temp_undo

 --temp

SELECT b.tablespace,
     ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
     a.sid||','||a.serial# AS sid_serial,
     NVL(a.username, '(oracle)') AS  username,
     a.program 
FROM   v$session a,
       v$sort_usage b,
       v$parameter p WHERE  p.name  = 'db_block_size'
 AND    a.saddr = b.session_addr 
ORDER BY b.tablespace, b.blocks;  

--undo

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) AS sid_serial, 
       NVL(s.username, '(oracle)') AS username, 
       s.program, 
       r.name undoseg, 
       t.used_ublk * TO_NUMBER(x.value)/1024||'K' AS undo 
FROM   v$rollname    r, 
       v$session     s,
       v$transaction t,
       v$parameter   x 
WHERE  s.taddr = t.addr 
AND    r.usn   = t.xidusn(+) 
AND    x.name  = 'db_block_size';

active_sess_2

--判断活跃会话1
select count(*) ACTIVE_SESSION_COUNT,sum(last_call_et) TOTAL_ACTIVE_TIME ,max(last_call_et) MAX_ACTIVE_TIME,
nvl(event,'==grouping==')event, nvl(sql_id,'==grouping==') sql_id
from v$session
where status = 'ACTIVE' and
not ( type = 'BACKGROUND' and state='WAITING' and wait_class='Idle' )
group by cube(event,sql_id)
having count(*)>1 or (grouping(event)+grouping(sql_id)=0)
order by 1
/

--判断活跃会话2（PL/SQL只考虑当前SQL)
select count(*) ACTIVE_SESSION_COUNT ,sum(sysdate-sql_exec_start)*86400 TOTAL_ACTIVE_TIME ,
max(sysdate-sql_exec_start)*86400 MAX_ACTIVE_TIME,
nvl(event,'==grouping==')event, nvl(sql_id,'==grouping==') sql_id
from v$session
where status = 'ACTIVE' and
not ( type = 'BACKGROUND' and state='WAITING' and wait_class='Idle' )
group by cube(event,sql_id)
having count(*)>1 or (grouping(event)+grouping(sql_id)=0)
order by 1
/

--找到会话对应PL/SQL 对象
select p.object_name||'.'||p.procedure_name plsql_name--,...
from v$session s , dba_procedures p
where status = 'ACTIVE' and
not ( type = 'BACKGROUND' and state='WAITING' and wait_class='Idle' )
and s.plsql_object_id = p.object_id (+)
and s.plsql_subprogram_id= p.subprogram_id (+);

--找到会话对应的等待对象
select o.owner||'.'||o.object_name waiting_object_name
from v$session s , dba_objects o
where s.status = 'ACTIVE' and
not ( s.type = 'BACKGROUND' and state='WAITING' and wait_class='Idle' )
and s.row_wait_obj# = o.object_id (+);

GetDBUptime

SELECT TO_CHAR (startup_time, 'DD-MON-YYYY HH24:MI:SS') started_at,
           TRUNC (SYSDATE - (startup_time))
       || ' day(s), ' || TRUNC (  24 * ((SYSDATE - startup_time) -
       TRUNC (SYSDATE - startup_time)))
       || ' hour(s), '|| MOD (TRUNC (  1440 * (  (SYSDATE - startup_time) -
       TRUNC (SYSDATE - startup_time))),60)
       || ' minute(s), '|| MOD (TRUNC (  86400 * (  (SYSDATE - startup_time) -
       TRUNC (SYSDATE - startup_time))),60)
       || ' seconds' uptime
FROM v$instance;


tbs_used_percent

SELECT  SUBSTR(a.TABLESPACE_NAME,1,30) TablespaceName,
    round(SUM(a.bytes/1024/1024/1024),2)  AS "Totle_size(G)",
    round(SUM(NVL(b.free_space1/1024/1024/1024,0)),2) AS "Free_space(G)",
    round(SUM(a.bytes/1024/1024/1024),2)-round(SUM(NVL(b.free_space1/1024/1024/1024,0)),2)  AS "Used_space(G)",
    ROUND((SUM(a.bytes/1024/1024/1024)-SUM(NVL(b.free_space1/1024/1024/1024,0))) *100/SUM(a.bytes/1024/1024/1024),2) AS "Used_percent%",
    round(SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024/1024),2)                                                                     AS "Max_size(G)",
    ROUND((SUM(a.bytes/1024/1024/1024)-SUM(NVL(b.free_space1/1024/1024/1024,0)))*100/SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024/1024),2) AS "Max_percent%"
  FROM dba_data_files a,
    (SELECT SUM(NVL(bytes,0)) free_space1,
      file_id
    FROM dba_free_space
  GROUP BY file_id
   ) b 
WHERE a.file_id = b.file_id(+)
 GROUP BY a.TABLESPACE_NAME
ORDER BY "Used_percent%" desc;
    
segment_size

select owner,segment_name,segment_type,tablespace_name,sum(bytes)/1024/1024 from dba_segments where segment_name=upper('&seg_name') group by owner,segment_name,segment_type,tablespace_name;
    
tbs_free

select TABLESPACE_NAME,round(sum(bytes)/1024/1024/1024,2) free_g from dba_free_space group by TABLESPACE_NAME order by 2 desc;
    
temp_tbs

SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024*1024),2) USED_SPACE,ROUND(SUM(BYTES_FREE)/(1024*1024*1024),2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME;

select TABLESPACE_NAME,file_name from dba_temp_files;

asm_check

--查看磁盘
set linesize 160
col name for a20 
col path for a50 
col FAILGROUP for a20
select NAME,PATH,FAILGROUP,TOTAL_MB,FREE_MB,STATE from v$asm_disk_stat order by 1;

--查看磁盘组
set linesize 160
col name for a20
select NAME,STATE,TYPE,TOTAL_MB,FREE_MB from v$asm_diskgroup;

--查看ASM Operation
set linesize 160
select * from gv$asm_operation; 
    

user_create_ddl

--获取创建用户脚本及权限

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', true);
SELECT (
 CASE
   WHEN ((SELECT COUNT(*) FROM dba_users WHERE username = '&&Username') > 0)
   THEN dbms_metadata.get_ddl ('USER', '&&Username')
   ELSE to_clob (' -- Note: User not found!')
 END ) extracted_ddl
FROM dual
UNION ALL
SELECT (
 CASE
   WHEN ((SELECT COUNT(*) FROM dba_ts_quotas WHERE username = '&&Username') > 0)
   THEN dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '&&Username')
   ELSE to_clob (' -- Note: No TS Quotas found!')
 END )
FROM dual
UNION ALL
SELECT (
 CASE
   WHEN ((SELECT COUNT(*) FROM dba_role_privs WHERE grantee = '&&Username') > 0)
   THEN dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&&Username')
   ELSE to_clob (' -- Note: No granted Roles found!')
 END )
FROM dual
UNION ALL
SELECT (
 CASE
   WHEN ((SELECT COUNT(*) FROM dba_sys_privs WHERE grantee = '&&Username') > 0)
   THEN dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&&Username')
   ELSE to_clob (' -- Note: No System Privileges found!')
 END )
FROM dual
UNION ALL
SELECT (
 CASE
   WHEN ((SELECT COUNT(*) FROM dba_tab_privs WHERE grantee = '&&Username') > 0)
   THEN dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&&Username')
   ELSE to_clob (' -- Note: No Object Privileges found!')
 END )
FROM dual
    
table_stat

--表相关的统计信息
--包含分区、索引、索引字段
--先替换掉下面define值
define owner=STEVEN
define table_name=AWEN_OGG_TEST
--先替换掉上面define值
set linesize 160
col DATA_TYPE for a15
set pagesize 10000
col COLUMN_NAME for a30
col col for a30
select TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, CHAIN_CNT, AVG_ROW_LEN, GLOBAL_STATS, SAMPLE_SIZE, to_char(t.last_analyzed,'MM-DD-YYYY') from dba_tables t where owner = upper('&owner') and table_name = upper('&table_name');
select COLUMN_NAME, DATA_TYPE, NUM_DISTINCT, DENSITY, NUM_BUCKETS, NUM_NULLS, SAMPLE_SIZE, to_char(t.last_analyzed,'MM-DD-YYYY') from dba_tab_columns t where owner = upper('&owner') and table_name = upper('&table_name');
select INDEX_NAME, BLEVEL BLev, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, CLUSTERING_FACTOR, to_char(t.last_analyzed,'MM-DD-YYYY') from dba_indexes t where table_name = upper('&table_name') and table_owner = upper('&owner');
select /*+ first_rows use_nl(i,t)*/ i.INDEX_NAME, i.COLUMN_NAME, i.COLUMN_POSITION, decode(t.DATA_TYPE, 'NUMBER',t.DATA_TYPE||'('|| decode(t.DATA_PRECISION, null,t.DATA_LENGTH||')', t.DATA_PRECISION||','||t.DATA_SCALE||')'), 'DATE',t.DATA_TYPE, 'LONG',t.DATA_TYPE, 'LONG RAW',t.DATA_TYPE, 'ROWID',t.DATA_TYPE, 'MLSLABEL',t.DATA_TYPE, t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '|| decode(t.nullable, 'N','NOT NULL', 'n','NOT NULL', NULL) col from dba_ind_columns i, dba_tab_columns t where i.index_owner=t.owner and i.table_name = upper('&table_name') and i.index_owner = upper('&owner') and i.table_name = t.table_name and i.column_name = t.column_name order by index_name,column_position;

--收集统计信息
exec dbms_stats.gather_table_stats('STEVEN','AWEN_OGG_TEST',degree=>10,cascade=> TRUE,no_invalidate=>false);

 

db_time

--查询DB Time
SELECT TO_CHAR(a.end_interval_time,'yyyymmdd hh24'),
SUM (a.db_time) inst1_m,
SUM (b.db_time) inst2_m
FROM
(SELECT pre_snap_id,
snap_id,
end_interval_time,
ROUND((value - pre_value) / 1000000 / 60) db_time
FROM
(SELECT a.snap_id,
end_interval_time,
lag(a.snap_id) over(order by a.snap_id) pre_snap_id,
value,
lag(value) over(order by a.snap_id) pre_value
FROM dba_hist_sys_time_model a,
dba_hist_snapshot b
WHERE stat_name = 'DB time'
AND a.dbid = b.dbid
AND a.snap_id = b.snap_id
AND a.instance_number=b.instance_number
AND a.dbid =
(SELECT dbid FROM v$database
)
AND a.instance_number = 1
)
WHERE pre_snap_id IS NOT NULL
AND end_interval_time>sysdate-30
ORDER BY snap_id DESC
) a,
(SELECT pre_snap_id,
snap_id,
end_interval_time,
ROUND((value - pre_value) / 1000000 / 60) db_time
FROM
(SELECT a.snap_id,
end_interval_time,
lag(a.snap_id) over(order by a.snap_id) pre_snap_id,
value,
lag(value) over(order by a.snap_id) pre_value
FROM dba_hist_sys_time_model a,
dba_hist_snapshot b
WHERE stat_name = 'DB time'
AND a.dbid = b.dbid
AND a.snap_id = b.snap_id
AND a.instance_number=b.instance_number
AND a.dbid =
(SELECT dbid FROM v$database
)
AND a.instance_number = 2
)
WHERE pre_snap_id IS NOT NULL
AND end_interval_time>sysdate-30
ORDER BY snap_id DESC
) b
WHERE a.snap_id=b.snap_id(+)
GROUP BY TO_CHAR(a.end_interval_time,'yyyymmdd hh24')
ORDER BY TO_CHAR(a.end_interval_time,'yyyymmdd hh24');

log_switch

select to_char(first_time,'YYYY-mm-dd')       LOG_DATE, to_char(first_time,'HH24')       LOG_HOUR, count(*)                         SWITCHES  from v$loghist  group by to_char(first_time,'YYYY-mm-dd') , to_char(first_time,'HH24')  order by 1,2;

sess_longops

select sid,
       opname,
       target,
       sofar,
       totalwork,
       units,
       (totalwork-sofar)/time_remaining bps,
       time_remaining,
       sofar/totalwork*100 fertig
from   v$session_longops
where  time_remaining > 0;
    
sql_plan

--explain查看SQL执行计划
EXPLAIN PLAN FOR select count(*) from steven.AWEN_OGG_TEST;
select * from table(dbms_xplan.display());

--查看AWR和CURSOR中的执行计划
select * from table(dbms_xplan.display_awr('&sqlid'));
select * from table(dbms_xplan.display_cursor('&sqlid'));

--查看内存中的执行计划
select '| Operation |Object Name | Rows | Bytes| Cost |'
as "Explain Plan in library cache:" from dual
union all
select rpad('| '||substr(lpad(' ',1*(depth-1))||operation||
decode(options, null,'',' '||options), 1, 35), 36, ' ')||'|'||
rpad(decode(id, 0, '----------------------------',
substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)
||' ',1, 30)), 31, ' ')||'|'|| lpad(decode(cardinality,null,' ',
decode(sign(cardinality-1000), -1, cardinality||' ',
decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
lpad(decode(bytes,null,' ',
decode(sign(bytes-1024), -1, bytes||' ',
decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
lpad(decode(cost,null,' ', decode(sign(cost-10000000), -1, cost||' ',
decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"
from v$sql_plan sp
where sp.hash_value=&hash_value or sp.sql_id='&sqlid';

--查看历史执行计划
select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss') TIMESTAMP
from dba_hist_sql_plan
where SQL_ID='&sqlid' order by TIMESTAMP;

 

table_index

--查看表的索引
select col.table_owner "table_owner",
idx.table_name "table_name",
col.index_owner "index_owner",
idx.index_name "index_name",
uniqueness "uniqueness",
status,
column_name "column_name",
column_position
from dba_ind_columns col, dba_indexes idx
where col.index_name = idx.index_name
and col.table_name = idx.table_name and col.table_owner = idx.table_owner
and col.table_owner='&owner'
and col.table_name='&table_name')
order by idx.table_type,
idx.table_name,
idx.index_name,
col.table_owner,
column_position;

TOP30_SQL

--查询占资源TOP 30SQL，可根据不同列排名修改排序值，查询指定时间区间
select * from( select st.sql_id,st.PLAN_HASH_VALUE,ss.begin_interval_time,st.module,CPU_Time_delta CPU_Time, 
nvl(executions_delta, 0) exe_num,trunc(elapsed_time_delta / 1000000) exe_time,
trunc((elapsed_time_delta / decode(nvl(executions_delta, 0), 0, 1, executions_delta)) / 1000000) avg_exe_time,--平均执行时间
buffer_gets_delta lg_read,trunc((buffer_gets_delta /decode(nvl(executions_delta, 0), 0, 1, executions_delta))) avg_lg_read, --平均逻辑读
st.disk_reads_delta wl_read,trunc((disk_reads_delta /decode(nvl(executions_delta, 0), 0, 1, executions_delta))) avg_wl_read, --平均物理读
st.physical_read_bytes_delta/1024 wl_readKB, sql_text
from dba_hist_sqlstat st
inner join dba_hist_snapshot ss on st.snap_id=ss.snap_id and st.instance_number=ss.instance_number
inner join dba_hist_sqltext sx on st.sql_id=sx.sql_id
where  begin_interval_time between to_date('20190510 08','yyyymmdd hh24') and to_date('20190510 18','yyyymmdd hh24')
order by 6 desc) where rownum<10;


show_bind_value

select * from 
(select sql_id,
        name,
        datatype_string,
        case datatype
          when 180 then --TIMESTAMP
           to_char(ANYDATA.accesstimestamp(t.value_anydata),
                   'YYYY/MM/DD HH24:MI:SS')
          else
           t.value_string
        end as bind_value,
        last_captured
   from gv$sql_bind_capture t
  where sql_id = '&sql_id'
  order by last_captured desc,name)
  where rownum<=100
  order by last_captured,name;

select *
from (select sql_id,
        name,
        datatype_string,
        case datatype
          when 180 then --TIMESTAMP
           to_char(ANYDATA.accesstimestamp(t.value_anydata),
                   'YYYY/MM/DD HH24:MI:SS')
          else
           t.value_string
        end as bind_value,
        last_captured
   from dba_hist_sqlbind t
  where sql_id = '&sql_id'
  order by last_captured desc,name)
  where rownum<=100
  order by last_captured,name;
    

show_sql_stat

--查询SQL历史执行性能消耗情况，默认sysdate-7，可修改
select 'GV$' flag,
       0 snap_id,
       inst_id,
       plan_hash_value phv,
       executions execs,
       disk_reads reads,
       disk_reads / decode(executions, NULL, 1, 0, 1, executions) reads_per,
       buffer_gets gets,
       buffer_gets / decode(executions, NULL, 1, 0, 1, executions) gets_per,
       rows_processed,
       rows_processed / decode(executions, NULL, 1, 0, 1, executions) rows_per,
       elapsed_time/1000 elap_ms,
       (elapsed_time/1000) / decode(executions, NULL, 1, 0, 1, executions) elap_per_ms
  from gv$sql
 where sql_id='&sql_id'
 union all
select to_char(sht.begin_interval_time,'dd hh24:mi')||'--'||to_char(sht.end_interval_time,'hh24:mi') flag,
               sta.snap_id,
               sta.instance_number inst,
               sta.plan_hash_value phv,
               sta.executions_delta execs,
               sta.disk_reads_delta reads,
               sta.disk_reads_delta /
               decode(sta.executions_delta, NULL, 1, 0, 1, sta.executions_delta) reads_per,
               sta.buffer_gets_delta gets,
               sta.buffer_gets_delta /
               decode(sta.executions_delta, NULL, 1, 0, 1, sta.executions_delta) gets_per,
               sta.rows_processed_delta,
               sta.rows_processed_delta /
               decode(sta.executions_delta, NULL, 1, 0, 1, sta.executions_delta) rows_per,
               sta.elapsed_time_delta/1000 elap_ms,
               (sta.elapsed_time_delta/1000) /
               decode(sta.executions_delta, NULL, 1, 0, 1, sta.executions_delta) elap_per_ms
          from dba_hist_sqlstat sta,dba_hist_snapshot sht
         where 1=1
            and sta.instance_number=sht.instance_number
            and sta.snap_id=sht.snap_id
            and sht.begin_interval_time>= sysdate-7
            and sta.sql_id='&sql_id'
 order by 1,2;

本文参考摘自墨天轮