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..."

fi

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

AWR 中 top sql 的資訊獲取 - 分析

相關文章