AWR 中 top sql 的信息获取

1. 查看数据库AWR报告的生成频率.

select * from dba_hist_wr_control;

2. 查看AWR报告中sql_id 及 sql_text.

select * from dba_hist_sqlstat;

select * from dba_hist_sql_summary;

3. 查看AWR报告中某一sql_id 的执行计划.

select * from table(dbms_xplan.display_awr('66gs90fyynks7'));

select dbms_xplan.display_awr('66gs90fyynks7') from dual;

4. 后期分析待续.....................

工作时间段内以 15min 为粒度进行 awr 报告抽取（不包含周六、日，对于工作日不包含 18:00—08:00 的非工作时间）

+++++++++++++++++++++

#!/usr/bin/ksh

##############paramter######################
startdate=$1' 00:00:01'
enddate=$2' 23:59:59'
reporttype=$3
reportformat='text'
###################env######################
oraclehome=`echo $ORACLE_HOME`

#############################################
dbname=`sqlplus -s "/ as sysdba" <set head off
set echo off
set feedback off
set linesize 120 ;
set pagesize 0;
set heading off;
select name from v\\$database;
quit;
EOF
`
dbid=`sqlplus -s "/ as sysdba" <set head off
set echo off
set feedback off
set linesize 120 ;
set pagesize 0;
set heading off;
select dbid from v\\$database;
quit;
EOF
`

instid=`sqlplus -s "/ as sysdba" <set head off
set echo off
set feedback off
set linesize 120 ;
set pagesize 0;
set heading off;
select instance_number from v\\$instance;
quit;
EOF `

databaseid=$dbid
instanceid=`echo $instid|tr -d ' '`

echo "from database--&gt"$dbname" database id---&gt"$dbid" instance id---&gt"$instanceid
echo "report type---&gt"$reporttype"|"$reporttype" snapshot from---&gt"$startdate" to---&gt"$enddate

if [ $reporttype = 'AWR' ];
then

export NLS_LANG=american_america.AL32UTF8
echo "generate the awr report sql...."
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <awrreport.sql
set head off
set echo off
set feedback off
set linesize 120 ;
set pagesize 0;
set heading off;
select * from (
select snap_id as snaped,
       lag(snap_id, 1) OVER(ORDER BY snap_id) as snapst,
       to_char(end_interval_time, 'yyyymmddhh24miss') as snaped1,
       lag(to_char(end_interval_time, 'yyyymmddhh24miss'), 1) OVER(ORDER BY to_char(end_interval_time, 'yyyymmddhh24miss')) as snaped2
from dba_hist_snapshot a
where a.begin_interval_time >=to_date('$startdate', 'yyyy-mm-dd hh24:mi:ss')
   and a.begin_interval_time <=to_date('$enddate', 'yyyy-mm-dd hh24:mi:ss')
   and rtrim(ltrim(to_char(a.begin_interval_time,'day','NLS_DATE_LANGUAGE=AMERICAN'))) not in ('saturday','sunday')
   and to_char(a.begin_interval_time,'HH24') >=8
   and to_char(a.end_interval_time,'HH24') <=18
   and a.DBID='$dbid'
   and a.INSTANCE_NUMBER='$instanceid'
) where snapst is not null AND substr(snaped1,1,8)=substr(snaped2,1,8) and substr(snaped1,9,2)||substr(snaped2,9,2) not in ('0900','0830','0908','0808','0800')
order by snapst;
quit
EOF

export NLS_LANG=american_america.AL32UTF8
cat awrreport.sql | while read line
do
   endd=`echo $line | awk ' { print $1 } '`
   startd=`echo $line | awk ' { print $2 } '`
   endt=`echo $line | awk ' { print $3 } '`
   startt=`echo $line | awk ' { print $4 } '`
   awrrp="awrrpt_"$startt"_"$endt".txt"
   instid=`echo $instanceid`

export NLS_LANG=american_america.AL32UTF8
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <@?/rdbms/admin/awrrpti.sql;
$reportformat
$databaseid
$instid
1
$startd
$endd
$awrrp
quit
EOF

done

echo "generate the awr report finish,please check..."
exit

else

echo "error!!!please check the input parameters..."

+++++++++++++++++++++++++++++++++++++++++

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22578826/viewspace-746931/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22578826/viewspace-746931/