OWNER OBJECT_ID DATA_OBJECT_ID OBJECT_NAME OBJECT_TYPE
------------------------------ ---------- -------------- ------------------------------ -------------------
SYS 9409 DBA_HIST_SQLSTAT VIEW
PUBLIC 9410 DBA_HIST_SQLSTAT SYNONYM
------------------------------ --------------------------------------------------------------------------------
DBA_HIST_SQLSTAT select sql.snap_id, sql.dbid, sql.instance_number,
and sn.instance_number = sql.instance_number
那么我们就可以直接从这些数据字典历史表里去查看所需要的信息而不用每次都重新生成一个awr报告。
当然实现的过程也略微费了一些周折，把脚本稍一加工，就成了shell版本。
sqlplus -s $DB_CONN_STR@$SH_DB_SID < break on db_name
set pages 50
set linesize 100
col elapsed_time format a10
col per_total format a10
prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~
select d.dbid dbid
, d.name db_name
, i.instance_number inst_num
, i.instance_name inst_name
from v\$database d,
v\$instance i;
select snap_id,sql_id,EXECUTIONS_DELTA,max_elapsed elapsed_time,per_total||'%' per_total from
(select distinct snap_id,sql_id,EXECUTIONS_DELTA,trunc(max(ELAPSED_TIME_DELTA) OVER (PARTITION BY snap_id,sql_id )/1000000,0)||'s' max_elapsed,
trunc((max(ELAPSED_TIME_DELTA) OVER (PARTITION BY snap_id,sql_id))/(SUM(ELAPSED_TIME_DELTA) OVER (PARTITION BY snap_id )),2)*100 per_total
from dba_hist_sqlstat where snap_id=$1
order by 5 desc
) where rownum<=5;
输出的内容结果如下，和html的格式比起来，也还是很清晰的,只需要输入结束的快照号即可。
$ ksh showsnapsql.sh 57584
Current Instance
~~~~~~~~~~~~~~~~
DBID DB_NAME INST_NUM INST_NAME
---------- --------- ---------- ----------------
2534640677 TESTDB2 1 xxxxx
SNAP_ID SQL_ID EXECUTIONS_DELTA ELAPSED_TI PER_TOTAL
---------- ------------- ---------------- ---------- ----------
57494 dfb15m5s2uwmc 0 3601s 48%
57494 8tmf11fvxy09j 21 1612s 21%
57494 cy55p6nrd31db 20 1593s 21%
57494 29tdwfv5d9s4f 20 298s 4%
57494 c7k4g2urpu1sc 0 175s 2%
这个时候就可以轻松抓取到问题sql，直接来判定是否需要更多的信息。
--------------------------------
2016年12月30日更新
可以使用更新后的分析函数，效果要好一些。输入起始和结束的快照号 比如sh showsnapsql.sh 100 101
sqlplus -s $DB_CONN_STR@$SH_DB_SID < break on db_name
set pages 50
set linesize 100
col elapsed_time format 99999999.99
col cpu_time format 99999999.99
col per_total format a10
prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~
select d.dbid dbid
, d.name db_name
, i.instance_number inst_num
, i.instance_name inst_name
from v\$database d,
v\$instance i;
select * from (
select distinct snap_id,sql_id,EXECUTIONS_DELTA,round(ELAPSED_TIME_DELTA/1000000,2) Elapsed_time,round(ratio_to_report(ELAPSED_TIME_DELTA) over() *100,2)||'%' per_total
from dba_hist_sqlstat where snap_id=$2
order by 4 desc)
where rownum<=10;
select * from (
select distinct snap_id,sql_id,EXECUTIONS_DELTA,round(CPU_TIME_DELTA/1000000,2) CPU_time,round(ratio_to_report(CPU_TIME_DELTA) over() *100,2)||'%' per_total
from dba_hist_sqlstat where snap_id=$2
order by 4 desc)
where rownum<=10;
EOF